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?

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

select table_name, column_name from INFORMATION_SCHEMA.columns where column_name like '%control%'

private bool GotConnected2SQL(string sConnectionString)
     {
         bool bRet = false;
         SqlConnection mySQLConnection = new SqlConnection(sConnectionString);
         string SQLStatement = "SELECT control FROM [tbvl_ssi_control]";
         string sSql = string.Format(SQLStatement);
         SqlCommand mySQLCommand = mySQLConnection.CreateCommand();
         try
         {
             mySQLCommand.CommandText = sSql;
             SqlDataAdapter mySQLDataAdapter = new SqlDataAdapter();
             mySQLDataAdapter.SelectCommand = mySQLCommand;
             DataSet myDataSet = new DataSet();
 
             mySQLConnection.Open();
             Console.WriteLine("Retrieving rows from the Query");
             mySQLDataAdapter.Fill(myDataSet, "RECS");
             mySQLConnection.Close();
             DataTable myDataTable = myDataSet.Tables["RECS"];
             bRet = true;
         }
         catch (Exception ex)
         {
             MessageBox.Show(string.Format("", "SQL Connection Error: {0}", ex.Message), "SQL Connection Status", MessageBoxButtons.OK, MessageBoxIcon.Error);
             bRet = false;
         }
 
         return bRet;
     }

SELECT *

INTO [tbl_ssi_NamesYYMMDD]

FROM [tbl_ssi_Names]