Node JS DATABASE CONNECT

 var express = require('express');

var app = express();

app.get('/', function (req, res) {

   

    var sql = require("mssql");

    // config for your database

    var config = {

        user: 'USER',

        password: 'USER',

        server: 'SERVER IP', 

port:1333,

options:{encrypt:false},

        database: 'BONDHON' 

    };

    // connect to your database

    sql.connect(config, function (err) {

    

        if (err) console.log(err);

        // create Request object

        var request = new sql.Request();

           

        // query to the database and get the records

        request.query('select * from fn_items() where arno=\'8010240\'', function (err, recordset) {

            

            if (err) console.log(err)

            // send records as a response

            res.send(recordset);

            

        });

    });

});

var server = app.listen(5000, function () {

    console.log('Server is running..');

});

Get first and last day of previous month (with timestamp) in SQL Server

 First Day Of Current Week.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),0),106)

Last Day Of Current Week.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),6),106)

First Day Of Last week.

select CONVERT(varchar,DATEADD(week,datediff(week,7,getdate()),0),106)

Last Day Of Last Week.

select CONVERT(varchar,dateadd(week,datediff(week,7,getdate()),6),106)

First Day Of Next Week.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),7),106)

Last Day Of Next Week.

select CONVERT(varchar,dateadd(week,datediff(week,0,getdate()),13),106)

First Day Of Current Month.

select CONVERT(varchar,dateadd(d,-(day(getdate()-1)),getdate()),106)

Last Day Of Current Month.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()))),dateadd(m,1,getdate())),106)

In this Example Works on Only date is 31. and remaining days are not.

First Day Of Last Month.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,-1,getdate()-2))),dateadd(m,-1,getdate()-1)),106)

Last Day Of Last Month.

select CONVERT(varchar,dateadd(d,-(day(getdate())),getdate()),106)

First Day Of Next Month.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,1,getdate()-1))),dateadd(m,1,getdate())),106)

Last Day Of Next Month.

select CONVERT(varchar,dateadd(d,-(day(dateadd(m,2,getdate()))),DATEADD(m,2,getdate())),106)

First Day Of Current Year.

select CONVERT(varchar,dateadd(year,datediff(year,0,getdate()),0),106)

Last Day Of Current Year.

select CONVERT(varchar,dateadd(ms,-2,dateadd(year,0,dateadd(year,datediff(year,0,getdate())+1,0))),106)

Row wise data view as Colomn wise using PIVOT Column

drop table  AUTOREP_SIZE_RATIO

CREATE TABLE [dbo].AUTOREP_SIZE_RATIO(

[RATIO_ID] [bigint] NOT NULL,

[RATIO_NO] [int] NOT NULL,

[CATID] [int] NULL,

[SUB_ID] [int] NULL,

[SIZE_ID] [int] NULL,

[QTY] [int] NULL

) ON [PRIMARY]

GO

insert into  AUTOREP_SIZE_RATIO(

RATIO_ID, RATIO_NO, CATID, SUB_ID, SIZE_ID, QTY)values

(1000,1,100,101,1043,1),

(1000,1,100,101,1044,1),

(1001,2,100,101,1042,2),

(1001,2,100,101,1043,2),

(1001,2,100,101,1044,2),

(1002,3,100,101,1042,3),

(1002,3,100,101,1043,3),

(1002,3,100,101,1044,3),

(1002,3,100,101,1045,3)

DECLARE  @CATID nvarchar(max)=100,

DECLARE  @SUB_ID nvarchar(max)=101

DECLARE @DynamicPivotQuery AS NVARCHAR(MAX),

@PivotColumnNames AS NVARCHAR(MAX),

@PivotSelectColumnNames AS NVARCHAR(MAX)

--Get distinct values of the PIVOT Column

SELECT @PivotColumnNames= ISNULL(@PivotColumnNames + ',','')

+ QUOTENAME(size)

FROM (SELECT DISTINCT AM.[DESCRIPTION] size

                     FROM [dbo].[AUTOREP_SIZE_RATIO] SR  LEFT JOIN ARTICLEMATRIX AM ON AM.ARTMID=SR.SIZE_ID

where SR. CATID= @CATID and SR. SUB_ID= @SUB_ID) AS cat

--select @PivotColumnNames

SELECT @PivotSelectColumnNames 

= ISNULL(@PivotSelectColumnNames + ',','')

+ 'ISNULL(' + QUOTENAME(size) + ', 0) AS '

+ QUOTENAME(size) 

FROM (SELECT DISTINCT AM.[DESCRIPTION] size

                     FROM [dbo].[AUTOREP_SIZE_RATIO] SR  LEFT JOIN ARTICLEMATRIX AM ON AM.ARTMID=SR.SIZE_ID

where SR. CATID= @CATID and SR. SUB_ID= @SUB_ID) AS cat order by size

--select @PivotSelectColumnNames

SET @DynamicPivotQuery = 

N'SELECT RATIO_ID,RATIO_NO,

' + @PivotSelectColumnNames +' from

(SELECT RATIO_ID,RATIO_NO,qty,DESCRIPTION size

                     FROM [dbo].[AUTOREP_SIZE_RATIO] SR  LEFT JOIN ARTICLEMATRIX AM ON AM.ARTMID=SR.SIZE_ID

where SR. CATID= '+@CATID+' and SR. SUB_ID= '+@SUB_ID+')D 

pivot(sum(QTY) for size in (' + @PivotColumnNames + ')) as pvt' ;

--Execute the Dynamic Pivot Query

EXEC sp_executesql @DynamicPivotQuery

 

Create DEMO DATE

 

declare @StartDT SMALLDATETIME='2017-05-01', 

@EndDT SMALLDATETIME='2017-05-30',

 @BUNIT_ID INT=10084

-- '2021-05-01','2021-05-31', 10084

;WITH DateRange(DateData) AS 

(

SELECT @StartDT as Date

UNION ALL

SELECT DATEADD(d,1,DateData)

FROM DateRange 

WHERE DateData < @EndDT

)

    SELECT [DateData],@BUNIT_ID [BunitID] INTO #DATEX

    FROM DateRange

    OPTION (MAXRECURSION 0);

Why do we have to write SET FMTONLY OFF in stored procedures when using Entity Framework ?

IF 1=0 

BEGIN

SET FMTONLY OFF

END 

-----------------------------------------------------

Having IF(0=1) SET FMTONLY OFF seems like a risky thing to casually do in stored procedures read in by entity framework.

Entity Framework is the only source of this flag being set as a standard practice that I'm aware of (presumably other ORM's may use it).

the purpose (as I understand it) is to provide a way to get a procedures return schema without actually touching any data. (some stored procedures you don't want to execute just to update an orm's object model.

so unless you have a table that is counting the number of times your EF model has been updated (which might be interesting academically)

the safest way to use ftmonly with entity framework (in my mind) is.. under the following circumstances

1. if the procedure in question is complex and confuses EF (EF reads the first returned schema, flow logic ignored)
2. let EF set the flag for you. (I clear it below to exit early)
3. use always false logic (which would be ignored when FTMONLY is on - interpret this as EF is trying to read schema)

4. at the beginning of the complex procedure do the following

   if(0=1)  -- if FMTONLY is on this if condition is ignored
   begin
       -- this loop will only be entered if fmtonly is on (ie EF schema read)
       select 
           column1
           ,column2
           ...
           ,columnX
       from whateverA
           cross join whateverB
           ...
           cross join whateverQ
       -- joins don't matter but they might make it easier to get the column definitions
       -- and names you desire.   the important thing here is generating the proper 
       -- return schema... which is as complex as whatever you are trying to return
       where 1=0
   
       set FMTONLY off -- do this so that you can now force an early return since EF
       -- usually only wants the first data set schema...  other orms might
       -- do something different
       return  -- this will be ignored if FMTONLY is still on
   
   end

SQL Server CURSOR

 What is a database cursor ?

A database cursor is an object that enables traversal over the rows of a result set. It allows you to process individual row returned by a query.

SQL Server cursor life cycle

These are steps for using a cursor:

DECLARE 

    @Name as NVARCHAR(MAX), 

    @EMP_ID as InT;

DECLARE My_Test CURSOR

FOR SELECT 

        Name, 

        EMP_ID

    FROM 

        EMPLOYEE_MASTER;

OPEN My_Test;

FETCH NEXT FROM My_Test INTO 

    @Name, 

    @EMP_ID;

WHILE @@FETCH_STATUS = 0

    BEGIN

        PRINT @Name +' ' + CAST(@EMP_ID AS varchar);

        

FETCH NEXT FROM My_Test INTO 

            @Name, 

            @EMP_ID;

    END;

CLOSE My_Test;

DEALLOCATE My_Test;

How to remove same data in a table row by javascript.

<table id="tbl" border="1" cellpadding="3" cellspacing="0">
                                        <tr><th>Category</th><th>Product</th><th>Size</th><th>Price</th><th>Shipping</th></tr>
                                        <tr><td>Category-1</td><td>Product-1</td><td>Big</td><td>102</td><td>Free</td></tr>
                                        <tr><td>Category-1</td><td>Product-1</td><td>Big</td><td>132</td><td>Free</td></tr>
                                        <tr><td>Category-1</td><td>Product-2</td><td>Big</td><td>130</td><td>Free</td></tr>
                                        <tr><td>Category-1</td><td>Product-2</td><td>Small</td><td>100</td><td>Free</td></tr>
                                        <tr><td>Category-2</td><td>Product-3</td><td>Big</td><td>130</td><td>Free</td></tr>
                                        <tr><td>Category-2</td><td>Product-3</td><td>Big</td><td>100</td><td>Free</td></tr>
                                        <tr><td>Category-2</td><td>Product-3</td><td>Small</td><td>100</td><td>10</td></tr>
                                        <tr><td>Category-2</td><td>Product-4</td><td>Big</td><td>150</td><td>10</td></tr>
                                        <tr><td>Category-3</td><td>Product-5</td><td>Big</td><td>150</td><td>10</td></tr>
                                        <tr><td>Category-3</td><td>Product-5</td><td>Small</td><td>120</td><td>10</td></tr>
                                        <tr><td>Category-3</td><td>Product-5</td><td>Big</td><td>120</td><td>Free</td></tr>
                                        <tr><td>Category-4</td><td>Product-6</td><td>Big</td><td>120</td><td>10</td></tr>
                                        <tr><td>Category-4</td><td>Product-6</td><td>Small</td><td>120</td><td>10</td></tr>
                                    </table>

-----------------------------------------------------------------------------

 $(function () {
                  
                    function groupTable($rows, startIndex, total) {
                        if (total === 0) {
                            return;
                        }
                        var i, currentIndex = startIndex, count = 1, lst = [];
                        var tds = $rows.find('td:eq(' + currentIndex + ')');
                        var ctrl = $(tds[0]);
                        lst.push($rows[0]);
                        for (i = 1; i <= tds.length; i++) {
                            if (ctrl.text() == $(tds[i]).text()) {
                                count++;
                                $(tds[i]).addClass('deleted');
                                lst.push($rows[i]);
                            }
                            else {
                                if (count > 1) {
                                    ctrl.attr('rowspan', count);
                                    groupTable($(lst), startIndex + 1, total - 1)
                                }
                                count = 1;
                                lst = [];
                                ctrl = $(tds[i]);
                                lst.push($rows[i]);
                            }
                        }
                    }
                    groupTable($('#tbl tr:has(td)'), 0, 3);
                    $('#tbl .deleted').remove();

                });