set@StartDate= DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE())-1, 0)--First day of previous month

set@EndDate  =DATEADD(MONTH, DATEDIFF(MONTH, -1, GETDATE())-1, -1) --Last Day of previous month

    end

else

    begin

set@StartDate=dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7)-7,'17530101') -- Date greater than or equal to Monday of last week

set@EndDate  =dateadd(dd,((datediff(dd,'17530101',getdate())/7)*7)-2,'17530101') -- Date before Saturday of last week

WITH Vals AS (

   SELECT [STATE]

      ,[Fname]

      ,[Lname]

      ,[EMAIL]

      , 1 DisplayOrder

      , 1 as Name_Count

       FROM  [ssi_tbl_NameAddress]

        UNION ALL   

        SELECT  [STATE] 

                ,'' AS Fname 

                ,'' AS lname

                ,'' as email  

                ,2 DisplayOrder

                , count(*) as Name_Count

        FROM    [ssi_tbl_NameAddress]

        GROUP BY [STATE]

)

SELECT [STATE]

      ,[Fname]

      ,[Lname]

      ,[EMAIL]

      ,Name_Count

FROM    Vals

ORDER BY  [STATE],DisplayOrder

with  sum()

DECLARE @Sales TABLE(

        CUS_ID  INT,

        PRODUCT VARCHAR(20),

        AMOUNT FLOAT

)

INSERT INTO @Sales SELECT 12,'A', 2.50 

INSERT INTO @Sales SELECT 12,'B', 5.80 

INSERT INTO @Sales SELECT 24,'A', 10.00 

INSERT INTO @Sales SELECT 24,'B', 30.00

;WITH Vals AS (

        SELECT  cus_id, 

                product, 

                amount,

                1 DisplayOrder,

                SUM(amount) OVER(PARTITION BY cus_id) OrderTotal

        FROM    @Sales 

        UNION ALL   

        SELECT  cus_id, 

                'Total' AS product, 

                SUM(amount),

                2 DisplayOrder,

                SUM(amount)

        FROM    @Sales 

        GROUP BY cus_id

)

SELECT  cus_id,

        product,

        amount

FROM    Vals

ORDER BY  OrderTotal DESC,cus_id,DisplayOrder, product 

DELETE FROM [ssi_tbl_NameAddress]

WHERE  control NOT IN (SELECT MAX(control)

                  FROM   [ssi_tbl_NameAddress]

                  GROUP  BY Lname,fname 

                  /*Even if ID is not null-able SQL Server treats MAX(ID) as potentially

                    nullable. Because of semantics of NOT IN (NULL) including the clause

                    below can simplify the plan*/

                  HAVING MAX(control) IS NOT NULL) 

USE [LENDER_LOAN_SERVICE]
GO

/****** Object:  UserDefinedFunction [dbo].[uf_ssi_GetHMDARaceString]    Script Date: 02/11/2016 09:30:40 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE  FUNCTION [dbo].[uf_ssi_GetHMDARaceString]( @LoanID int , @BRW varchar(50))
RETURNS nchar(5)
AS
BEGIN
DECLARE @Event_ID varchar(100)
DECLARE  @Return varchar(500);
 
--set @LoanID ='1344'
DECLARE my_cursor CURSOR FOR
SELECT [_Type] FROM  [HMDA_RACE] where  [loanGeneral_Id] =@LoanID and [BorrowerID] = @BRW
OPEN my_cursor
FETCH NEXT FROM my_cursor
INTO @Event_ID
set  @Return =  ''
--print @Event_ID
WHILE @@FETCH_STATUS = 0
BEGIN
     if @Event_ID = 'AmericanIndianOrAlaskaNative'    set  @Return =   @Return+ '1'
     if @Event_ID =  'Asian'  set  @Return =   @Return+ '2'
     if @Event_ID = 'BlackOrAfricanAmerican' set  @Return =   @Return+ '3'
     if @Event_ID = 'NativeHawaiianOrOtherPacificIslander' set  @Return =   @Return+'4'
     if @Event_ID = 'White' set  @Return =   @Return+'5'
     --else set  @Return =   @Return + '-'
    --print @Event_ID
    --print @Return
    FETCH NEXT FROM my_cursor
    INTO @Event_ID
END
CLOSE my_cursor
DEALLOCATE my_cursor
 if (SELECT [RaceNationalOriginRefusalIndicator] FROM  [GOVERNMENT_MONITORING] where [loanGeneral_Id] =@LoanID and [BorrowerID] = @BRW)=1 set @Return='6'
RETURN substring(@Return+'     ',1,5)

END;


GO

--First of Current Month
--First of Current Month
select    DATEADD(month, DATEDIFF(month, 0, GETDATE()), 0) as Firstday                                              
    ,dateadd(day, -1, dateadd(month, 1, dateadd(day, 1 - day(GETDATE()), GETDATE()))) as lastday

A SQL query walks into a bar. He approaches two tables and says, Mind if I join you?

Round half to even

A tie-breaking rule that is less biased is round half to even, namely:

• If the fraction of y is 0.5, then q is the even integer nearest to y.

Thus, for example, +23.5 becomes +24, as does +24.5; while −23.5 becomes −24, as does −24.5.

This method treats positive and negative values symmetrically, and is therefore free of sign bias. More importantly, for reasonable distributions of y values, the expected (average) value of the rounded numbers is the same as that of the original numbers. However, this rule will introduce a towards-zero bias when y − 0.5 is even, and a towards-infinity bias for when it is odd.

This variant of the round-to-nearest method is also called unbiased rounding, convergent rounding, statistician's rounding, Dutch rounding, Gaussian rounding, odd–even rounding,^[3] or bankers' rounding.

This is the default rounding mode used in IEEE 754 computing functions and operators.

private static long GetTotalFreeSpace(string sDriveName)
       {
           long nRet = -1;
           foreach (DriveInfo drive in DriveInfo.GetDrives())
           {
               if (drive.IsReady && drive.Name == sDriveName)
               {
                   nRet = drive.TotalFreeSpace;
               }
           }
           return nRet;
       }
 
       private static long GetTotalFreeSpace(string sDriveName, string sUnits)
       {
           long nDenominator = 1;
           switch (sUnits)
           {
               case "K":
                   nDenominator = 1024;
                   break;
               case "M":
                   nDenominator = 1048576;
                   break;
               case "G":
                   nDenominator = 1073741824;
                   break;
               case "T":
                   nDenominator = 1099511627776;
                   break;
               default:
                   break;
           }
 
 
           long nRet = -1;
           foreach (DriveInfo drive in DriveInfo.GetDrives())
           {
               if (drive.IsReady && drive.Name == sDriveName)
               {
                   nRet =(long) clsCommonStatics.ZeroDouble ((double) drive.TotalFreeSpace, (double)nDenominator) ;
               }
           }
           return nRet;
       }

/****** Object:  StoredProcedure [dbo].[sp_ssi_InsertGenerator]    Script Date: 12/18/2015 14:00:45 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER PROC [dbo].[sp_ssi_InsertGenerator]

(

@schemaName varchar(100)

,@tableName varchar(100)

,@IncludePrimaryKeyIdentity bit=0

,@IncludeNonPrimaryKeyIdentity bit=1

,@IncludeIfNotExists bit=0

) 

AS

DECLARE @string nvarchar(MAX) --for storing the first half of INSERT statement

DECLARE @stringData nvarchar(MAX) --for storing the data (VALUES) related statement

DECLARE @dataType nvarchar(1000) --data types returned for respective columns

DECLARE @IfNotExistsString nvarchar(MAX) --for storing the NOT EXISTS string and be concatenated on each loop

DECLARE @IdentityExists bit -- flag to see if need to turn on IDENTITY_INSERT"

--Declare a cursor to retrieve column specific information for the specified table

DECLARE cursCol CURSOR FAST_FORWARD FOR 

SELECT column_name,data_type 

FROM information_schema.columns 

WHERE table_name = @tableName AND Table_Schema=@schemaName

AND column_name NOT IN ('InsertedOn')--Used to exclude any columns that may be standard to exclude.  For Example a default timestamp field.

OPEN cursCol

SET @string='INSERT ['+@schemaName+'].['+@tableName+']('

SET @stringData=''

SET @IfNotExistsString='''IF NOT EXISTS(SELECT 1 FROM ['+@schemaName+'].['+@tableName+'] WHERE '

DECLARE @colName nvarchar(50)

FETCH NEXT FROM cursCol INTO @colName,@dataType

IF @@fetch_status<>0

begin

print 'Table '+@schemaName+'.'+@tableName+' not found, processing skipped.'

close curscol

deallocate curscol

return

END

WHILE @@FETCH_STATUS=0

BEGIN

IF (

(

--IF NOT IDENTITY THEN PROCESS

EXISTS

(

SELECT * FROM sys.columns a

INNER JOIN sys.tables b on a.object_id=b.object_id

--LEFT JOIN sys.index_columns c ON c.object_id=b.object_id and a.column_id=c.column_id

--LEFT JOIN sys.key_constraints d ON c.object_id=d.parent_object_id

--AND c.index_id = d.unique_index_id 

WHERE 

a.Is_Identity<>1

AND b.object_id=Object_id(@schemaName+'.'+@tableName)

AND a.name=@colName

)

)

OR 

(

--IF PRIMARY KEY AND @IncludePrimaryKeyIdentity =1 THEN PROCESS IT

EXISTS

(

SELECT * FROM sys.columns a

INNER JOIN sys.tables b on a.object_id=b.object_id

INNER JOIN sys.index_columns c ON c.object_id=b.object_id and a.column_id=c.column_id

INNER JOIN sys.key_constraints d ON c.object_id=d.parent_object_id

AND c.index_id = d.unique_index_id 

WHERE a.Is_Identity=1

AND b.object_id=Object_id(@schemaName+'.'+@tableName)

AND a.name=@colName

) 

AND @IncludePrimaryKeyIdentity=1

)

OR 

(

--IF IDENTITY COLUMN (Not-Primary key) and @IncludeNonPrimaryKeyIdentity=1 THEN PROCESS IT

EXISTS

(

SELECT * FROM sys.columns a

INNER JOIN sys.tables b on a.object_id=b.object_id

LEFT JOIN sys.index_columns c ON c.object_id=b.object_id and a.column_id=c.column_id

LEFT JOIN sys.key_constraints d ON c.object_id=d.parent_object_id

AND c.index_id = d.unique_index_id 

WHERE d.parent_object_id IS NULL

AND a.Is_Identity=1

AND b.object_id=Object_id(@schemaName+'.'+@tableName)

AND a.name=@colName

) 

AND @IncludeNonPrimaryKeyIdentity=1

)

)

BEGIN

IF (

(

--IF PRIMARY KEY AND @IncludePrimaryKeyIdentity =1 THEN PROCESS IT

EXISTS

(

SELECT * FROM sys.columns a

INNER JOIN sys.tables b on a.object_id=b.object_id

INNER JOIN sys.index_columns c ON c.object_id=b.object_id and a.column_id=c.column_id

INNER JOIN sys.key_constraints d ON c.object_id=d.parent_object_id

AND c.index_id = d.unique_index_id 

WHERE a.Is_Identity=1

AND b.object_id=Object_id(@schemaName+'.'+@tableName)

AND a.name=@colName

) 

AND @IncludePrimaryKeyIdentity=1

)

OR 

(

--IF IDENTITY COLUMN (Not-Primary key) and @IncludeNonPrimaryKeyIdentity=1 THEN PROCESS IT

EXISTS

(

SELECT * FROM sys.columns a

INNER JOIN sys.tables b on a.object_id=b.object_id

LEFT JOIN sys.index_columns c ON c.object_id=b.object_id and a.column_id=c.column_id

LEFT JOIN sys.key_constraints d ON c.object_id=d.parent_object_id

AND c.index_id = d.unique_index_id 

WHERE d.parent_object_id IS NULL

AND a.Is_Identity=1

AND b.object_id=Object_id(@schemaName+'.'+@tableName)

AND a.name=@colName

) 

AND @IncludeNonPrimaryKeyIdentity=1

)

)

BEGIN

SET @IdentityExists=1

END

IF @dataType in ('varchar','char','nchar','nvarchar')

BEGIN

SET @stringData=@stringData+''''+'''+isnull('''''+'''''+REPLACE(['+@colName+'],'''''''','''''''''''')+'''''+''''',''NULL'')+'',''+'

SET @IfNotExistsString=@IfNotExistsString+'['+@colName+']='+'''+isnull('''''+'''''+REPLACE(['+@colName+'],'''''''','''''''''''')+'''''+''''',''NULL'')+'' and ''+'''

--PRINT @stringData

END

ELSE 

IF @dataType IN ('datetime','smalldatetime','date','datetime2','time')

BEGIN

SET @stringData=@stringData+'''convert(datetime,'+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+',121)+'''''+''''',''NULL'')+'',121),''+'

 SET @IfNotExistsString=@IfNotExistsString+'['+@colName+']='+'''+isnull('''''+'''''+CONVERT(VARCHAR(30),['+@colName+'],121)+'''''+''''',''NULL'')+'' and ''+'''

END

ELSE 

IF @dataType IN ('tinyint','smallint','int','money','bit','decimal','numeric','smallmoney','bigint') 

BEGIN

SET @stringData=@stringData+''''+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'',''+'

SET @IfNotExistsString=@IfNotExistsString+'['+@colName+']='+'''+isnull('''''+'''''+convert(varchar(200),'+@colName+')+'''''+''''',''NULL'')+'' and ''+'''

END

ELSE

RAISERROR ('There is a datatype present in the table that is not accounted for in the procedure',16,1)

--BUILD COLUMN LIST

SET @string=@string+'['+@colName+'],'

END

FETCH NEXT FROM cursCol INTO @colName,@dataType

END

CLOSE cursCol

DEALLOCATE cursCol

--REMOVE ENDING "AND" FROM LINE AND ADD ENDING PARENTHESIS TO CLOSE OUT "IF NOT EXISTS" STATEMENT

SET @IfNotExistsString = LEFT(@IfNotExistsString,LEN(@IfNotExistsString)-10)

SET @IfNotExistsString = @IfNotExistsString +'+'')'''

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

DECLARE @Query nvarchar(4000)

IF @IncludeIfNotExists = 1 AND @IdentityExists=1

BEGIN

SET @query ='SELECT ''SET IDENTITY_INSERT ['+@schemaName+'].['+@tableName+'] ON'' UNION ALL SELECT  REPLACE('+@IfNotExistsString+'+'''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'',''=NULL'','' IS NULL'')+CHAR(13)+CHAR(10) FROM ['+@schemaName+'].['+@tableName+'] UNION ALL SELECT ''SET IDENTITY_INSERT ['+@schemaName+'].['+@tableName+'] OFF'''

END

ELSE IF @IncludeIfNotExists = 1

BEGIN 

SET @query ='SELECT  REPLACE('+@IfNotExistsString+'+'''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'',''=NULL'','' IS NULL'')+CHAR(13)+CHAR(10) FROM ['+@schemaName+'].['+@tableName+']'

END

IF @IncludeIfNotExists = 0 AND @IdentityExists=1

BEGIN

SET @query ='SELECT ''SET IDENTITY_INSERT ['+@schemaName+'].['+@tableName+'] ON'' UNION ALL SELECT  '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM ['+@schemaName+'].['+@tableName+'] UNION ALL SELECT ''SET IDENTITY_INSERT ['+@schemaName+'].['+@tableName+'] OFF'''

END

ELSE IF @IncludeIfNotExists = 0

BEGIN

SET @query ='SELECT  '''+substring(@string,0,len(@string)) + ') VALUES(''+ ' + substring(@stringData,0,len(@stringData)-2)+'''+'')'' FROM ['+@schemaName+'].['+@tableName+']'

END

--PRINT @query

BEGIN TRY

exec sp_executesql @query

END TRY

BEGIN CATCH

RAISERROR ('Unexpected error occured while trying to generate the insert script.',16,1)

PRINT 'Error Message: '+ERROR_MESSAGE()

PRINT 'Error Line: '+CONVERT(VARCHAR(20),ERROR_LINE())

PRINT 'Error Number: '+CONVERT(VARCHAR(20),ERROR_NUMBER())

END CATCH

GO

ALTER TABLE [tbl_ssi_Location] ADD [AddedBy] [varchar](50) NULL

ALTER TABLE [tbl_ssi_Location] ADD [AddedDate] [datetime] NULL

ALTER TABLE [tbl_ssi_Location] ADD [UpdateBy] [varchar](50) NULL

ALTER TABLE [tbl_ssi_Location] ADD [UpdateDate] [datetime] NULL