question

balanarasimhac-1495 avatar image
0 Votes"
balanarasimhac-1495 asked ·

How to get one week data in SQL

Hi Team,
Have code bellow like but am not getting proper week start and end dates. Please help on same
SELECT DATEADD(DAY, -1 - DATEPART(WEEKDAY, [Report Generated On]), [Report Generated On]) AS StartDate
, DATEADD(DAY, 5 - DATEPART(WEEKDAY, [Report Generated On]), [Report Generated On]) AS EndDate
,DATEPART(ww,[Report Generated On]) WeekNumber
,[Status]
,COUNT([MF No]) BookingCount
FROM tblName
where [Report Generated On] >='2021-02-19'
AND [Report Generated On] <'2021-02-26'
group by DATEADD(DAY, -1 - DATEPART(WEEKDAY, [Report Generated On]), [Report Generated On])
, DATEADD(DAY, 5 - DATEPART(WEEKDAY, [Report Generated On]), [Report Generated On])
,DATEPART(ww,[Report Generated On])
,[Status]

My Results:
72770-image.png


sql-server-transact-sql
image.png (9.4 KiB)
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

1 Answer

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered ·

Hi @balanarasimhac-1495,

For this type of problem we recommend that you post CREATE TABLE statements for your tables together with INSERT statements with sample data, enough to illustrate all angles of the problem. We also need to see the expected result of the sample.

What is your expected first day of one week? Sunday?Monday? Another day?

Sunday is the default one. If you would like to set aother weekday as first day of one week, you could add SET DATEFIRST.

72834-untitled.png

Your query could be as below:

   set datefirst 7  --default,set Sunday as first day of week, you could change this value accordingly 
    
 SELECT DATEADD(DAY, 1 - DATEPART(WEEKDAY, [Report Generated On]), [Report Generated On]) AS StartDate
 , DATEADD(DAY, 7 - DATEPART(WEEKDAY, [Report Generated On]), [Report Generated On]) AS EndDate
 ,DATEPART(ww,[Report Generated On]) WeekNumber
 ,[Status]
 ,COUNT([MF No]) BookingCount
 FROM tblName
 where [Report Generated On] >='2021-02-19'
 AND [Report Generated On] <'2021-02-26'
 group by DATEADD(DAY, 1 - DATEPART(WEEKDAY, [Report Generated On]), [Report Generated On])
 , DATEADD(DAY, 7 - DATEPART(WEEKDAY, [Report Generated On]), [Report Generated On])
 ,DATEPART(ww,[Report Generated On])
 ,[Status]

Best regards
Melissa


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


untitled.png (18.2 KiB)
·
10 |1000 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.