Week TO Date ,Month To Date, YYYTD,Fiscal to Date and End of the week ,End of Fiscal year function

Transact-SQL
1 
Hello Experts !

I need support to get the function to add ... The sample table creates the date and select statement required results .Suppose in the second column we have WkDate which starts from Thrusday to wednesday works ok .

  1. Second column i have WkDatelast required last date of the week , the week started from Thursday and end with Wednesday ( required every date of Wednesday retrieve in this column).
  2. Third column Week to Date Suppose i have a date 2020-10-29 00:00:00.000 which is Thrusday and i want to run sql statement till 2020-10-30 00:00:00.000 so the date 2020-10-30 00:00:00.000 retrive in this column that the statement run till 2020-10-30 00:00:00.000 ( called Week to date)... Please help to solve all the comment out columns.

---Drop TABLE Dates

CREATE TABLE Dates (
  START_DATETIME DATETIME,
  PRIMARY KEY (START_DATETIME)
)
DECLARE @dIncr DATETIME = CONVERT(datetime, '2019-01-1 00:00:00.000')
DECLARE @dEnd DATETIME = CONVERT(datetime, '2020-10-31 00:00:00.000')

WHILE ( @dIncr < @dEnd )
BEGIN
  INSERT INTO Dates (START_DATETIME) VALUES( @dIncr )
  SELECT @dIncr = DATEADD(DAY, 1, @dIncr )
END


SELECT 
        [Date]             = START_DATETIME  --ISNULL make this column NOT NULL
 /*  Refelect Date Periods */       
        ,WKDate            = DATEADD(dd,DATEDIFF(dd,3,START_DATETIME)/7*7,3)--- starts from Thrusday to Wednesday     
 --     ,WKDateLast        =                                                --- End on  Wednesday 
 --     ,WeekToDate        =                                                ---starts from Thrusday Week to date
   
        
        ,WKDate2           = DATEADD(day, DATEDIFF(day, 0, START_DATETIME) - (DATEPART(weekday, START_DATETIME + @@DATEFIRST - 1 ) - 1), 0) --- starts from Monday to Sunday  
 --     ,WKDate2Last       =                             -------- End date  Sunday
  --    ,WeekToDate2        =                                                ---starts from Monday Week to date
       
        ,MMDate            = DATEADD(mm,DATEDIFF(mm,0,START_DATETIME),0) ---First date of the month
        ,MMDateLast        = DATEADD(DD,-1,DATEADD(MM,DATEDIFF(MM,0,START_DATETIME)+1,0))   ---Last date of the month
        ,MonthToDate       =                                  ---Last from Ist date of the month to date       
        
        
        ,QQDate            = DATEADD(qq,DATEDIFF(qq,0,START_DATETIME),0)  ---First date of the Quater
        ,QQDateLast        = DATEADD(d, -1, DATEADD(q, DATEDIFF(q, 0, START_DATETIME) +1, 0))  ---Last date of the Quater     
---     ,QuaterToDate      =                              ---First date of the Quater  to Date     
        
        
        ,YYDate            = DATEADD(yy,DATEDIFF(yy,0,START_DATETIME),0) --First date of  Year
        ,YYDateLast        = DATEADD (dd, -1, DATEADD(yy, DATEDIFF(yy, 0, START_DATETIME) +1, 0)) --Last date of  Year
        ,YearToDate        =                        --First date of  Year to date        
        
        ,CumDate           = DATEADD(dd, DATEDIFF(dd, 0, START_DATETIME), 0) 
            
        ,fiscalDate        = DATEADD(dd,0, DATEDIFF(dd,0, DATEADD( mm,-(((12 + DATEPART(m, START_DATETIME)) - 7)%12), START_DATETIME ) - datePart(d,DATEADD( mm, -(((12 + DATEPART(m, START_DATETIME)) - 7)%12),START_DATETIME ))+1 ) )  --- First date of the Fiscal Year
 --     ,fiscalDateEnd     =                                      --- End date of the Fiscal Year
--      ,fiscalToDate      =    ----- First date of the Fiscal Year to date

from Dates
2

will this work?

  ,WKDateLast        =  DateAdd(day, 7,   DATEADD(dd,DATEDIFF(dd,3,START_DATETIME)/7*7,3))--- End on  Wednesday 
  ,WeekToDate        =    DateAdd(day, 1,   DATEADD(dd,DATEDIFF(dd,3,START_DATETIME)/7*7,3))  ---starts from Thrusday Week to date
3

Sir results doesnt reteriving OK ..

Your kind support to fill all comment out columns like monthToDate, YearToDate so on

image
image1517×528 462 KB

4

Try this for WkDateLast, I don't understand what you are looking for in WeekToDate

,WKDateLast = DateAdd(day, 6, DATEADD(dd,DATEDIFF(dd,3,START_DATETIME)/7*7,3))--- End on Wednesday

5

Hi Mike01,
Thank you for your kind response!
Actually i am trying retrieve the data for report base on From date ( Thursday) first date of the Week(Chinese) to date , Suppose Week start from Thursday 7th Jan 2021 and i want to run the report for on date 11th Jan 2021 so it provide me the sum from Thursday to Monday 11th Jan 2021 if i use function WKDateLast will reterive the results from Thursday to Wednesday and thats why required WeekToDate to handle the Sql statement . Hope i able to understand ...

WeekToDate I am trying like below please correct me if i am wrong or suggest how to handle it.

WeekToDate=CASE WHEN [Start date] BETWEEN DATEADD(dd,DATEDIFF(dd,3,[Start date])/7*7,3) AND [Start date] THEN [Start date] END