Get count of the query result

You may try this:

;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, 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])

Select allOpen in new window

Just remember to use appropriate SET DATEFIRST for your environment. More info: https://docs.microsoft.com/en-us/sql/t-sql/functions/datepart-transact-sql?view=sql-server-ver15

Thanks, trying and be right back

Hi pcelba ,
Thanks , but is it possible to have columns like Week  from-to and Count  in ascending order ?
At the moment it is looking like:
WeekNo   Cnt
38             2
39             6
37             3

It will be nice to have like:
weeks_before   starting_date   ending_date
6                         10-08-2020       16-08-2020
5                         17-08-2020        23-08-2020
4                         24-08-2020        30-08-2020
3                         31-08-2020        06-09-2020
2                         07-09-2020        13-09-2020
1                         14-09-2020        20-09-2020
0                         21-09-2020        27-09-2020


Thanks

SELECT *, DATEADD(DAY, ((weeks_before + 1) * 7) -1, starting_date) AS ending_date
FROM ( VALUES(0),(1),(2),(3),(4),(5),(6) ) AS test_data(weeks_before)
CROSS APPLY (
    SELECT DATEADD(DAY, (-DATEDIFF(DAY, 0, GETDATE()) % 7) - (weeks_before * 7), CAST(GETDATE() AS date)) AS starting_date
) AS csd
ORDER BY starting_date

Select allOpen in new window

The query works but, it does not give the count , any suggestion

;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, 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]) 

Select allOpen in new window

This query seems to be equal to my query which works for you. So what is the difference?

The query I posted doesnot give the count but, the format of the ouput is perfect. 

Thanks, will try and brb

Thanks , can the weeksbefore be sorted in descending order ?

Just add
 ORDER BY 2 DESC

Thanks a lot! All sorted!

Hello pcelba ,

Just a quick query about your solution. The current week count is not obtained.
WeekNo   weeks_before   wk_starting_date   wk_ending_date   Cnt
     34                      6       2020-08-17         2020-08-23            3
    36                       4       2020-08-03          2020-08-09           4    38                        2      2020-07-20          2020-07-26            2
   39                        1       2020-07-13          2020-07-19            2
 
Also, it is working till DECLARE  @weeks_before int = 3 but, greater than 3 it gives wrong dates. 

Above results are really not correct... so I've updated the query and it should also work over the end of year:

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

Select allOpen in new window

Have posted on the new thread 

https://www.experts-exchange.com/questions/29195930/SQL-Query-with-Count.html?anchor=a43166512&notificationFollowed=259554718#a43166512

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 3 DESC

Select allOpen in new window

Great, trying..