Showing week dates ranges as a part of select in SQL

Question

I have a query to fetch the count on a daily basis where the startdate and enddate are parameters passed from the application and I used a sample dates here to demonstrate the query

SELECT  
    CAST(DateCreated AS DATE) AS Date, 
    COUNT(0) AS Total  
FROM 
    SessionTable
WHERE  
    DateCreated >= '10/02/2015'  
    AND CONVERT(DATE, DateCreated) <= '10/30/2015' 
GROUP BY
    CAST(DateCreated AS DATE) 
ORDER BY
    CAST(DateCreated AS DATE)

This will return the result in format

Date Total

Now I decided to reduce the number of records by converting date-wise count to week-wise count and I use the query

SELECT 
    DATEPART(wk, DateCreated) AS WeekNumber, 
    COUNT(0) AS WeekCount 
FROM 
    SessionTable
WHERE  
    DateCreated >= '10/02/2015'  
    AND CONVERT(DATE, DateCreated) <= '10/30/2015' 
GROUP BY 
    DATEPART(wk, DateCreated)

This returns the result in the format and is working fine

WeekNumber  WeekCount
-------------------------
  40           2298
  41          13998
  42          19454
  43          19923

To make the result set more clear i am planning to add the begin date and end date of each week as well to the above result set , but struggled to do so

My expected result is

    WeekNumber BeginDate  EndDate WeekCount

To fetch the weekBegin and End Date How do you get the "week start date" and "week end date" from week number in SQL Server? suggestion from this can be used and i written the query like

         SELECT 
    DATEPART(wk, DateCreated) AS WeekNumber, 
    DATEADD(dd, -(DATEPART(dw, DateCreated)-1), DateCreated) as [WeekStart],
    DATEADD(dd, 7-(DATEPART(dw, DateCreated)), DateCreated)  as [WeekEnd],
    COUNT(0) AS WeekCount 
    FROM 
    Session
    WHERE  
    DateCreated >= '10/02/2015'  
    AND CONVERT(DATE, DateCreated) <= '10/30/2015' 
    GROUP BY 
    DATEPART(wk, DateCreated),
    DATEADD(dd, -(DATEPART(dw, DateCreated)-1), DateCreated),
    DATEADD(dd, 7-(DATEPART(dw, DateCreated)), DateCreated)

But this returned my resultset with a count as 1 for each day something like below

    WeekNumber  WeekStart       WeekEnd                     WeekCount
    44  2015-10-25 18:02:26.527 2015-10-31 18:02:26.527 1
    44  2015-10-25 10:53:16.057 2015-10-31 10:53:16.057 1
    42  2015-10-11 11:23:57.253 2015-10-17 11:23:57.253 1
    40  2015-09-27 09:04:39.063 2015-10-03 09:04:39.063 1
    44  2015-10-25 10:44:38.573 2015-10-31 10:44:38.573 1
    44  2015-10-25 14:33:31.803 2015-10-31 14:33:31.803 1
    44  2015-10-25 08:18:28.020 2015-10-31 08:18:28.020 1
    44  2015-10-25 12:19:21.270 2015-10-31 12:19:21.270 1

So how can I add BeginDate and EndDate values to each row from query correctly?

Take a look here, there are several solutions to this problem: https://stackoverflow.com/questions/1267126/how-do-you-get-the-week-start-date-and-week-end-date-from-week-number-in-sql — Egbert, Aug 23 '17 at 06:50
Possible duplicate of [How do you get the "week start date" and "week end date" from week number in SQL Server?](https://stackoverflow.com/questions/1267126/how-do-you-get-the-week-start-date-and-week-end-date-from-week-number-in-sql) — Egbert, Aug 23 '17 at 07:20
@Egbert The given thread is helpful to get an answer on how to add StarDate and Enddate of weeks , but i am struggling to integrate them with count queries and thats why i have raised question — Sebastian, Aug 23 '17 at 07:30
@Egbert i have edited my question with proper issues i am facing now. So please help to remove duplicate status if possible — Sebastian, Aug 23 '17 at 08:14

srinioracle · Answer 1 · 2017-08-23T09:15:20.833

Try the below code:

SELECT 
DATEPART(wk, DateCreated) AS WeekNumber, 
DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + year(datecreated)) + (DATEPART(wk, DateCreated)-1), 6) AS StartOfWeek,
DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + year(datecreated)) + (DATEPART(wk, DateCreated)-1), 5) AS EndOfWeek
COUNT(0) AS WeekCount 
FROM 
SessionTable
WHERE  
DateCreated >= '10/02/2015'  
AND CONVERT(DATE, DateCreated) <= '10/30/2015' 
GROUP BY 
DATEPART(wk, DateCreated)

Correction:

You should convert the startofweek and endofweek to date datatype and then add the same in the group by clause. The same is as follows:

SELECT 
DATEPART(wk, DateCreated) AS WeekNumber, 
CAST(DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + year(datecreated)) + (DATEPART(wk, DateCreated)-1), 6) AS DATE) AS StartOfWeek,
CAST(DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + year(datecreated)) + (DATEPART(wk, DateCreated)-1), 5) AS DATE)  AS EndOfWeek
COUNT(0) AS WeekCount 
FROM SessionTable
WHERE DateCreated >= '10/02/2015' AND CONVERT(DATE, DateCreated) <= '10/30/2015' 
GROUP BY DATEPART(wk, DateCreated),
CAST(DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + year(datecreated)) + (DATEPART(wk, DateCreated)-1), 6) AS DATE),
CAST(DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + year(datecreated)) + (DATEPART(wk, DateCreated)-1), 5) AS DATE)

Update 2:

SELECT 
DATEPART(wk, DateCreated) AS WeekNumber, 
CAST(DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + cast(year(datecreated) as varchar)) + (DATEPART(wk, DateCreated)-1), 6) AS DATE) AS StartOfWeek,
CAST(DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + cast(year(datecreated) as varchar)) + (DATEPART(wk, DateCreated)-1), 5) AS DATE)  AS EndOfWeek
COUNT(0) AS WeekCount 
FROM SessionTable
WHERE DateCreated >= '10/02/2015' AND CONVERT(DATE, DateCreated) <= '10/30/2015' 
GROUP BY DATEPART(wk, DateCreated),
CAST(DATEADD(wk, DATEDIFF(wk, 6, '1/1/' + cast(year(datecreated) as varchar)) + (DATEPART(wk, DateCreated)-1), 6) AS DATE),
CAST(DATEADD(wk, DATEDIFF(wk, 5, '1/1/' + cast(year(datecreated) as varchar)) + (DATEPART(wk, DateCreated)-1), 5) AS DATE)

This should work now.

will get a syntax error saying thart Column '.DateCreated' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. — Sebastian, Aug 23 '17 at 07:29
Now issue is Conversion failed when converting the varchar value '1/1/' to data type int. BTWN any suggestions as per my edit on question — Sebastian, Aug 23 '17 at 08:45
After some research of my own and based on recommendations from answers and comments in this thread now i am getting week start date and end date. But i can see 3 different approaches [https://pastebin.com/pviVj3UM] . Anyone can explain which will works best way in all scenarios like date in the beggining of year etc — Sebastian, Aug 23 '17 at 09:24

score 1 · Answer 2 · answered Aug 23 '17 at 09:09

If you modifiy your query like this, you should get the desired results. The problem was that you grouped by DateCreated including the time. The Cast to Date solves this problem.

    SELECT 
    DATEPART(wk, DateCreated) AS WeekNumber, 
    DATEADD(dd, -(DATEPART(dw, CAST(DateCreated AS Date))-1), CAST(DateCreated AS Date)) as [WeekStart],
    DATEADD(dd, 7-(DATEPART(dw, CAST(DateCreated AS Date))), CAST(DateCreated AS Date))  as [WeekEnd],
    COUNT(0) AS WeekCount 
    FROM 
    Session
    WHERE  
    DateCreated >= '10/02/2015'  
    AND CONVERT(DATE, DateCreated) <= '10/30/2015' 
    GROUP BY 
    DATEPART(wk, DateCreated),
    DATEADD(dd, -(DATEPART(dw, CAST(DateCreated AS Date))-1), CAST(DateCreated AS Date)),
    DATEADD(dd, 7-(DATEPART(dw, CAST(DateCreated AS Date))), CAST(DateCreated AS Date))

After some research of my own and based on recommendations from answers and comments in this thread now i am getting week start date and end date. But i can see 3 different approaches [https://pastebin.com/pviVj3UM] . Anyone can explain which will works best way in all scenarios like date in the beggining of year etc — Sebastian, Aug 23 '17 at 09:23

score 0 · Answer 3 · answered Aug 23 '17 at 05:59

0

I dont know how to implement in sql.But you can try this:

Enumerable.Range(0, 7)
    .Select(i => "DateTime")
    .ToArray()

answered Aug 23 '17 at 05:59

Ömer SÖNMEZ

49
4

Showing week dates ranges as a part of select in SQL

3 Answers3