We help IT Professionals succeed at work.
Private
Troubleshooting Question

SQL Query with Count

48 Views
Last Modified: 2020-09-28
Hello,
The query gives results in correct format, however the count does show for the current week .
Also if the week_before parameter is changed to more than 3 it gives wrong dates.
declare @weeks_before int =3

;WITH cte_starting_date AS ( 
      SELECT DATEADD(DAY, (-DATEDIFF(DAY, 0, GETDATE()) % 7) - (@weeks_before * 7), CAST(GETDATE() AS date)) AS starting_date 
) 
SELECT DATEPART(week, t1.[Date]) AS WeekNo, 
       @weeks_before + DATEPART(week, csd.starting_date) - DATEPART(week, t1.[Date]) AS weeks_before, 
       DATEADD(day, -7 * (DATEPART(week, t1.[Date]) - DATEPART(week, csd.starting_date)), csd.starting_date) AS wk_starting_date,
       DATEADD(day, 6 - 7 * (DATEPART(week, t1.[Date]) - DATEPART(week, csd.starting_date)), csd.starting_date) AS wk_ending_date, 
       COUNT(*) AS Cnt 
  FROM dbo.CORR t1 
 CROSS JOIN cte_starting_date csd 
 WHERE t1.date >= csd.starting_date AND t1.date < DATEADD(DAY, 7 * (@weeks_before + 1), csd.starting_date) 
 GROUP BY DATEPART(week, t1.[Date]), 
          @weeks_before + DATEPART(week, csd.starting_date) - DATEPART(week, t1.[Date]), 
          DATEADD(day, -7 * (DATEPART(week, t1.[Date]) - DATEPART(week, csd.starting_date)), csd.starting_date), 
          DATEADD(day, 6 - 7 * (DATEPART(week, t1.[Date]) - DATEPART(week, csd.starting_date)), csd.starting_date) ORDER BY 2 DESC
The Result from above query when 
declare @weeks_before int =3

WeekNoweeks_beforewk_starting_datewk_ending_dateCnt
3732020-09-072020-09-132
3822020-08-312020-09-062
3912020-08-242020-08-302

Expected Result when 
Declare @weeks_before int =6

WeekNoweeks_beforewk_starting_datewk_ending_dateCnt
34624-08-202030-08-20203
36431-08-202006-09-20204
37307-09-202013-09-20202
38214-09-202020-09-20202
39121-09-202027-09-20202
40028-09-202004-10-20207

Comment
Watch Question

ste5anSenior Developer
CERTIFIED EXPERT

Commented:
While you can do the calculus as CTE or APPLY, just do it in the declaration instead. Then use the CTE to filter and do the row based calculus. Something like:

DECLARE @weeks_before INT = 3;
DECLARE @starting_date DATE = DATEADD(DAY, ( -DATEDIFF(DAY, 0, GETDATE()) % 7 ) - ( @weeks_before * 7 ), CAST(GETDATE() AS DATE));

WITH Data
AS ( SELECT DATEPART(WEEK, t1.[Date]) AS WeekNo ,
            @weeks_before + DATEPART(WEEK, @starting_date) - DATEPART(WEEK, t1.[Date]) AS weeks_before ,
            DATEADD(DAY, -7 * ( DATEPART(WEEK, t1.[Date]) - DATEPART(WEEK, @starting_date)), @starting_date) AS wk_starting_date ,
            DATEADD(DAY, 6 - 7 * ( DATEPART(WEEK, t1.[Date]) - DATEPART(WEEK, @starting_date)), @starting_date) AS wk_ending_date
     FROM   dbo.CORR t1
     WHERE  t1.date >= @starting_date
            AND t1.date < DATEADD(DAY, 7 * ( @weeks_before + 1 ), @starting_date))
SELECT   D.WeekNo ,
         D.weeks_before ,
         D.wk_starting_date ,
         D.wk_ending_date ,
         COUNT(*)
FROM     Data D
GROUP BY D.WeekNo ,
         D.weeks_before ,
         D.wk_starting_date ,
         D.wk_ending_date
ORDER BY 2 DESC;

Open in new window

Look into the results of the rearranged CTE. it seems your calculus is not quite correct. Or at least I don't understand your applied formulas.

Author

Commented:
Thanks! Trying brb

Author

Commented:
Hi,
If I change the weeks before =6 (DECLARE @weeks_before INT = 6; ) then the current result from the query you suggested is
WeekNoweeks_beforewk_starting_datewk_ending_date(No column name)
3462020-08-172020-08-233
3642020-08-032020-08-094
3732020-07-272020-08-022
3822020-07-202020-07-262
3912020-07-132020-07-192
Which should be actually : Expected Reult: 
WeekNoweeks_beforewk_starting_datewk_ending_dateCnt
34624-08-202030-08-20203
36431-08-202006-09-20204
37307-09-202013-09-20202
38214-09-202020-09-20202
39121-09-202027-09-20202
40028-09-202004-10-20207
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
Or at least I don't understand your applied formulas.
This was a hint.. can you explain your logic.

How do you want to filter?
What do you need to calculate?

DECLARE @weeks_before INT = 3;
DECLARE @starting_date DATE = DATEADD(DAY, ( -DATEDIFF(DAY, 0, GETDATE()) % 7 ) - ( @weeks_before * 7 ), CAST(GETDATE() AS DATE));


SELECT DATEPART(WEEK, t1.[Date]) AS WeekNo
FROM   dbo.CORR t1
WHERE  t1.date >= @starting_date
       AND t1.date < DATEADD(DAY, 7 * ( @weeks_before + 1 ), @starting_date);

Open in new window

The problem is: You're doing apply @weeks_before twice, once in the calculation of  @starting_date and then again in the end date in the WHERE condition.

Adjust the end date calulcation:

DECLARE @weeks_before INT = 3;
DECLARE @start_date DATE = DATEADD(DAY, ( -DATEDIFF(DAY, 0, GETDATE()) % 7 ) - ( @weeks_before * 7 ), CAST(GETDATE() AS DATE));
DECLARE @end_date DATE = DATEADD(DAY, 7, @start_date);

SELECT GETDATE() ,
       @weeks_before ,
       @start_date ,
       @end_date;

SELECT DATEPART(WEEK, t1.[Date]) AS WeekNo
FROM   dbo.CORR t1
WHERE  t1.date >= @start_date
       AND t1.date < @end_date;

Open in new window

Then apply it to the rearranged CTE.

Author

Commented:
Hi,

How do you want to filter? Filter on dates by week 
What do you need to calculate? Calculate the Count of entries in the week range.


Author

Commented:
Is it possible to give the full amended query ? 
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
I need a precise and complete explanation. Not just to obvious. Basically, what are your requirements?

Author

Commented:
Thanks. I am trying to get count of entries in every week for a input parameter of how many weeks back we want to see the count.
Week starts on Monday and ends on Sunday . Hence the range like 28-September-2020 till 4-October-2020.
6  weeks before will be :
wk_starting_datewk_ending_date
24-08-202030-08-2020
31-08-202006-09-2020
07-09-202013-09-2020
14-09-202020-09-2020
21-09-202027-09-2020
28-09-202004-10-2020



Author

Commented:
Do you need more explanation or is the above information fine? The original query is below but, does not give count on the week range format:
DECLARE  @weeks_before int = 6

;WITH cte_starting_date AS (
    SELECT DATEADD(DAY, (-DATEDIFF(DAY, 0, GETDATE()) % 7) - (@weeks_before * 7), CAST(GETDATE() AS date)) AS starting_date
)
SELECT CAST(t1.[Date] AS date) AS 'Date' ,t1.RequestedBy AS 'Requested By', t1.GuestName AS 'Guest Name'  /*, ...*/
    , COUNT(t1.[RequestedBy]) OVER(PARTITION BY t1.[Date]) AS 'Weekly Count'
FROM dbo.CORRESPONDENCE t1
CROSS JOIN cte_starting_date csd
WHERE t1.date >= csd.starting_date AND t1.date < DATEADD(DAY, 7 * (@weeks_before + 1), csd.starting_date)
ORDER BY [Date] DESC


Author

Commented:
The expected Result from the query is :
WeekNoweeks_beforewk_starting_datewk_ending_dateCount
3462020-08-172020-08-233
3642020-08-032020-08-094
3732020-07-272020-08-022
3822020-07-202020-07-262
3912020-07-132020-07-192
ste5anSenior Developer
CERTIFIED EXPERT

Commented:
You need a count per week.
You need it only for X weeks back from now?

SET DATEFIRST 7; -- Sunday.

DECLARE @weeks_before INT = 3;
DECLARE @start_date DATE = GETDATE();
DECLARE @start_week_date DATE = DATEADD(WEEK, -@weeks_before, DATEADD(DAY, 1 - DATEPART(WEEKDAY, GETDATE()), @start_date));
DECLARE @end_week_date DATE = DATEADD(DAY, 7 - DATEPART(WEEKDAY, GETDATE()), @start_date);

SELECT GETDATE() ,
       @weeks_before ,
       @start_date ,
       @start_week_date ,
       @end_week_date;

SELECT DATEPART(WEEK, t1.[Date]) AS WeekNo
FROM   dbo.CORR t1
WHERE  t1.date >= @start_week_date
       AND t1.date < @end_week_date;

Open in new window

Author

Commented:
Thanks,
Lets consider 6 weeks before :
You need a count per week --- Yes,exctly
You need it only for X weeks back from now?  X weeks from this week
example 6 weeks before will be:

WeekNoweeks_beforewk_starting_datewk_ending_dateCnt
34624-08-202030-08-20203
36431-08-202006-09-20204
37307-09-202013-09-20202
38214-09-202020-09-20202
39121-09-202027-09-20202
40028-09-202004-10-20207



Author

Commented:
The result of your query: 

(No column name)WeekBeforeStartDateStartWeek DateEndWeek Date

2020-09-28 12:46:12.33762020-09-282020-08-162020-10-03
 End Week Date should - 2020-10-04


 
WeekNo
36
34
34
34
36
36
37
38
39
36
39
38
37
Don't know why 34,36 are repeating? 

Author

Commented:
This gives correct result based on your query; SET DATEFIRST 7; -- Sunday.

DECLARE @weeks_before INT = 6;
DECLARE @start_date DATE = GETDATE();
DECLARE @start_week_date DATE = DATEADD(WEEK, -@weeks_before, DATEADD(DAY, 2 - DATEPART(WEEKDAY, GETDATE()), @start_date));
DECLARE @end_week_date DATE = DATEADD(DAY, 8 - DATEPART(WEEKDAY, GETDATE()), @start_date);

SELECT GETDATE() ,
       @weeks_before as 'WeekBefore' ,
       @start_date as 'StartDate' ,
       @start_week_date as 'StartWeek Date',
       @end_week_date as 'EndWeek Date';

 
(No column name)           WeekBefore       StartDate             StartWeek Date EndWeek Date
2020-09-28 12:50:59.510              6             2020-09-28         2020-08-17         2020-10-04

CERTIFIED EXPERT

Commented:
This should give correct results:
DECLARE @weeks_before int = 6

;WITH cte_starting_date AS ( 
      SELECT DATEADD(DAY, (-DATEDIFF(DAY, 0, GETDATE()) % 7) - (@weeks_before * 7), CAST(GETDATE() AS date)) AS starting_date, 
             CAST(DATEADD(DAY, (-DATEDIFF(DAY, 0, GETDATE()) % 7) - (@weeks_before * 7), CAST(GETDATE() AS smalldatetime)) AS int) AS startingDayNo
) 
SELECT @weeks_before - CAST(t1.[Date] AS int)/7 + csd.startingDayNo/7 AS weeks_before, 
       DATEADD(day, 7 * (CAST(t1.[Date] AS int)/7 - csd.startingDayNo/7), csd.starting_date) AS wk_starting_date,
       DATEADD(day, 6 + 7 * (CAST(t1.[Date] AS int)/7 - csd.startingDayNo/7), csd.starting_date) AS wk_ending_date, 
       COUNT(*) AS Cnt 
  FROM dbo.Orders t1 WITH (NOLOCK)
 CROSS JOIN cte_starting_date csd 
 WHERE t1.[Date] >= csd.starting_date AND t1.[Date] < DATEADD(DAY, 7 * (@weeks_before + 1), csd.starting_date) 
 GROUP BY @weeks_before - CAST(t1.[Date] AS int)/7 + csd.startingDayNo/7, 
          DATEADD(day, 7 * (CAST(t1.[Date] AS int)/7 - csd.startingDayNo/7), csd.starting_date), 
          DATEADD(day, 6 + 7 * (CAST(t1.[Date] AS int)/7 - csd.startingDayNo/7), csd.starting_date)
ORDER BY 3 DESC
Note: This solution expects the SET DATEFIRST 7

Author

Commented:
Thanks, on it, be right back!

Author

Commented:
Got this exception:
Msg 529, Level 16, State 2, Line 14
Explicit conversion from data type date to int is not allowed.

Completion time: 2020-09-28T13:24:03.9726692+01:00

Author

Commented:
DECLARE @weeks_before int = 6

;WITH cte_starting_date AS ( 
      SELECT DATEADD(DAY, (-DATEDIFF(DAY, 0, GETDATE()) % 7) - (@weeks_before * 7), CAST(GETDATE() AS date)) AS starting_date, 
             CAST(DATEADD(DAY, (-DATEDIFF(DAY, 0, GETDATE()) % 7) - (@weeks_before * 7), CAST(GETDATE() AS smalldatetime)) AS int) AS startingDayNo
) 
SELECT @weeks_before - CAST(t1.[Date] AS int)/7 + csd.startingDayNo/7 AS weeks_before, 
       DATEADD(day, 7 * (CAST(t1.[Date] AS int)/7 - csd.startingDayNo/7), csd.starting_date) AS wk_starting_date,
       DATEADD(day, 6 + 7 * (CAST(t1.[Date] AS int)/7 - csd.startingDayNo/7), csd.starting_date) AS wk_ending_date, 
       COUNT(*) AS Cnt 
  FROM dbo.CORRESPONDENCE t1 WITH (NOLOCK)
 CROSS JOIN cte_starting_date csd 
 WHERE t1.[Date] >= csd.starting_date AND t1.[Date] < DATEADD(DAY, 7 * (@weeks_before + 1), csd.starting_date) 
 GROUP BY @weeks_before - CAST(t1.[Date] AS int)/7 + csd.startingDayNo/7, 
          DATEADD(day, 7 * (CAST(t1.[Date] AS int)/7 - csd.startingDayNo/7), csd.starting_date), 
          DATEADD(day, 6 + 7 * (CAST(t1.[Date] AS int)/7 - csd.startingDayNo/7), csd.starting_date)
ORDER BY 3 DESC
CERTIFIED EXPERT

Commented:
OK, this should work
DECLARE @weeks_before int = 6

;WITH cte_starting_date AS ( 
      SELECT DATEADD(DAY, (-DATEDIFF(DAY, 0, GETDATE()) % 7) - (@weeks_before * 7), CAST(GETDATE() AS date)) AS starting_date, 
             CAST(DATEADD(DAY, (-DATEDIFF(DAY, 0, GETDATE()) % 7) - (@weeks_before * 7), CAST(GETDATE() AS smalldatetime)) AS int) AS startingDayNo
) 
SELECT @weeks_before - CAST(CAST(t1.[Date] AS datetime) AS int)/7 + csd.startingDayNo/7 AS weeks_before, 
       DATEADD(day, 7 * (CAST(CAST(t1.[Date] AS datetime) AS int)/7 - csd.startingDayNo/7), csd.starting_date) AS wk_starting_date,
       DATEADD(day, 6 + 7 * (CAST(CAST(t1.[Date] AS datetime) AS int)/7 - csd.startingDayNo/7), csd.starting_date) AS wk_ending_date, 
       COUNT(*) AS Cnt 
  FROM dbo.Orders t1 WITH (NOLOCK)
 CROSS JOIN cte_starting_date csd 
 WHERE t1.[Date] >= csd.starting_date AND t1.[Date] < DATEADD(DAY, 7 * (@weeks_before + 1), csd.starting_date) 
 GROUP BY @weeks_before - CAST(CAST(t1.[Date] AS datetime) AS int)/7 + csd.startingDayNo/7, 
          DATEADD(day, 7 * (CAST(CAST(t1.[Date] AS datetime) AS int)/7 - csd.startingDayNo/7), csd.starting_date), 
          DATEADD(day, 6 + 7 * (CAST(CAST(t1.[Date] AS datetime) AS int)/7 - csd.startingDayNo/7), csd.starting_date)
ORDER BY 1 DESC
Note: ORDER BY clause was updated

Author

Commented:
Thanks, It does work great, only it does not display current week  i.e from 28/09/2020 till 04/10/2020.
Please let me know if it is possible to add that as well?

Author

Commented:
weeks_beforewk_starting_datewk_ending_dateCnt
02020-09-282020-10-041
12020-09-212020-09-272
22020-09-142020-09-202
32020-09-072020-09-132
42020-08-312020-09-064
62020-08-172020-08-233
CERTIFIED EXPERT

Commented:
OK, I see the data in the current week.
I've just updated the ORDER BY clause to ORDER BY 1

Author

Commented:
Hi,
Yes, I have data in current week :>

Author

Commented:
Thanks ! They want to see what has happened in the current week and 6 weeks before.
So line '0' will be great! Thanks a lot!
CERTIFIED EXPERT

Commented:
So the results should be OK now.

Author

Commented:
Which query is for displaying '0' or current week ? 
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
The query gives :
weeks_beforewk_starting_datewk_ending_dateCnt
62020-08-172020-08-233
42020-08-312020-09-064
32020-09-072020-09-132
22020-09-142020-09-202
12020-09-212020-09-272

Author

Commented:
Will be great to have result like or expected result :

weeks_beforewk_starting_datewk_ending_dateCnt
62020-08-172020-08-233
42020-08-312020-09-064
32020-09-072020-09-132
22020-09-142020-09-202
12020-09-212020-09-272
02020-09-282020-10-045

CERTIFIED EXPERT

Commented:
Hmmm... My testing data show the current week:

Author

Commented:
Ahhh... let me try on the other database ....be right back 

Author

Commented:
Sincere apologies, it did work. The database I was trying had no data for the current week. Feel gutted.
But, thanks a million!!!Was really struggling !!
CERTIFIED EXPERT

Commented:
LOL, I am also looking for results where no data exists... Very often :-)

Author

Commented:
haha, happy ending because of you!
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.