query a table multiple times for different date ranges

Question

I have a table with records dating back 3 years that is continuously updated with new records and back filled for prior weeks.

I would like to create a result set that looks like the example below, where the columns 1,2,3,4, etc represent a specific week of the year. As data gets inserted, I would be able to add more columns for each week. I would like to run each date range every time I run this report because data is often entered for prior dates, so prior weeks will change.

location	name	wk1_hours	wk2_hours	wk3_hours
USA	John S	56.39	14.01	73.55
USA	Alex P	24.36	22.47	NULL
USA	Christine H	NULL	78.27	42.37
Canada	John S	40.32	NULL	22.58
Canada	Eric B	53.00	3.44	27.84
Mexico	Charles T	10.58	8.48	NULL

This is the query I have so far, how do I apply multiple date range variables to this query and to separate columns?

declare @wk1start as datetime
declare @wk1end as datetime
set @wk1start = '2022/7/4 00:00:00'
set @wk1end = '2022/7/10 23:59:59'

select 
    location, 
    name, 
    cast(hours as numeric (36,2)) as wk1_hours
from table
where name is not null
    and date_of_service between @wk1start and @wk1end
group by 
    location

(I deleted my prior comment as I misread your post). To create a query that adapts to the number of weeks, you will need to create and execute dynamic SQL. As for calculating the week number, you might be able to use some built-in SQL date functions such as `DATEDIFF(week, reference_date, date_of_service)`, or you may need to create a separate week-range table up front. The first step would be to generate a query that produces results in the form {location, name, week_number, hours}. Next you can feed that to a `PIVOT` to get the final result. .... — T N, Dec 14 '22 at 22:41
... The trick is, the PIVOT requires an explicit list of values, which you don't have yet. That is where dynamic SQL is needed. You will need to clculate the number of weeks neeeded and the build a SQL statement having the correct number of cases. Once done, you can execute it using `EXEC`. Search for "dynamic SQL" or "dynamic SQL PIVOT" for existing examples. [This one](https://stackoverflow.com/questions/10404348/sql-server-dynamic-pivot-query) perhaps. — T N, Dec 14 '22 at 22:42
Thank you for your response, TN. Could you elaborate on the steps to create this? How will executing dynamic sql run through the weeks? — dtx780, Dec 14 '22 at 23:14
Will `table` always contain data for the past three years? Why not create your query to return the 156 or so weeks worth of data? The application that consumes the data won't need to deal with the number of columns changing from time to time. — HABO, Dec 15 '22 at 04:20
@dtx780 There are a few questions that you need to ask yourself first. (1) Is a week defined as Monday-Sunday, Sunday-Saturday, or Saturday-Friday? (2) What is your definition of week 1? (3) Will the weeks cross multiple years? (4) Is a week that crosses from one year to the next considered a single week? (5) How will you number weeks across multiple years. If using built-in week logic of `DATEPART()` or `DATEDIFF()`, you may need to use `SET DATEFIRST` to define your weekday range. ... — T N, Dec 15 '22 at 18:24
... Before you can build up your dynamic SQL, you will need to build a list of weeks to be covered. Two approaches come to mind. (1) You can define a calculation that calculates the week number for any given date, run that calculation on all of the dates in your data, and capture a distinct list of week numbers from the result. One disadvantage is that this may leave gaps in your week numbers if there are date gaps in your data. ... — T N, Dec 15 '22 at 18:25
... (2) You can query the minimum and maximum dates in your range, convert those to start and ending weeks, and use a date range generator to generate a complete list in between. See the answers in [this question](https://stackoverflow.com/questions/23290454/get-all-dates-between-two-dates-in-sql-server/23291758#23291758) for examples. You would need to adjust to generate weeks instead of days. Next, apply the techniques in the "dynamic SQL PIVOT" example I linked earlier. — T N, Dec 15 '22 at 18:25

Patrick Hurst · Answer 1 · 2022-12-14T23:01:14.757

First let's slap together some test data. Providing example DDL and DML is very helpful when you ask questions like this:

DECLARE @TABLE TABLE (Location NVARCHAR(50), Name NVARCHAR(50), Date DATE, Hours INT)
INSERT INTO @TABLE (Location, Name, Date, Hours) VALUES
('USA', 'John S', '2022-12-01', 10),('USA', 'John S', '2022-12-02', 10),('USA', 'John S', '2022-12-03', 10),('USA', 'John S', '2022-12-04', 10),('USA', 'John S', '2022-12-05', 10),
('USA', 'Alex P', '2022-12-01', 5),('USA', 'Alex P', '2022-12-02', 5),('USA', 'Alex P', '2022-12-03', 5),('USA', 'Alex P', '2022-12-04', 5),('USA', 'Alex P', '2022-12-05', 5),
('USA', 'Christine H', '2022-12-01', 5),('USA', 'Christine H', '2022-12-02', 5),('USA', 'Christine H', '2022-12-03', 5),('USA', 'Christine H', '2022-12-04', 5),('USA', 'Christine H', '2022-12-05', 5)

INSERT INTO @TABLE (Location, Name, Date, Hours) 
SELECT Location, Name, DATEADD(YEAR, -1, Date), Hours / 2
  FROM @TABLE
UNION ALL
SELECT Location, Name, DATEADD(YEAR, -2, Date), Hours / 2
  FROM @TABLE

I just threw some off the cuff values in the table, and then manipulated it to get a couple more years for the same dates.

You can use this data to mock up something similar to what you have as the desired output using some aggregation using the DATEPART function and then PIVOT like this:

SELECT a.Location, Week, a.Name, a.WeekOne, a.WeekTwo, a.WeekThree
  FROM (
SELECT Location, Name, DATEPART(WEEK,Date) AS Week,
SUM(CASE WHEN DATEPART(YEAR,Date) = 2020 THEN Hours END) OVER (PARTITION BY Location, Name, DATEPART(WEEK,Date) ORDER BY (SELECT 1)) AS WeekOne,
SUM(CASE WHEN DATEPART(YEAR,Date) = 2021 THEN Hours END) OVER (PARTITION BY Location, Name, DATEPART(WEEK,Date) ORDER BY (SELECT 1)) AS WeekTwo,
SUM(CASE WHEN DATEPART(YEAR,Date) = 2022 THEN Hours END) OVER (PARTITION BY Location, Name, DATEPART(WEEK,Date) ORDER BY (SELECT 1)) AS WeekThree
  FROM @TABLE
      ) a
 GROUP BY a.Location, a.Name, Week, a.WeekOne, a.WeekTwo, a.WeekThree

Location    Name        weekNumber  weekOne weekTwo weekThree
-------------------------------------------------------------
USA         Alex P      49          10      8       15
USA         Christine H 49          10      8       15
USA         John S      49          25      20      30

This does make some assumptions. You say 'specific week of the year' which I inferred to mean the ISO week number (1-53). If that's not the case, you can group by what ever definition you need instead.

If you're not a fan of PIVOT then you could instead have some fun with windowed functions:

SELECT a.Location, Week, a.Name, a.WeekOne, a.WeekTwo, a.WeekThree
  FROM (
SELECT Location, Name, DATEPART(WEEK,Date) AS Week,
SUM(CASE WHEN DATEPART(YEAR,Date) = 2020 THEN Hours END) OVER (PARTITION BY Location, Name, DATEPART(WEEK,Date) ORDER BY (SELECT 1)) AS WeekOne,
SUM(CASE WHEN DATEPART(YEAR,Date) = 2021 THEN Hours END) OVER (PARTITION BY Location, Name, DATEPART(WEEK,Date) ORDER BY (SELECT 1)) AS WeekTwo,
SUM(CASE WHEN DATEPART(YEAR,Date) = 2022 THEN Hours END) OVER (PARTITION BY Location, Name, DATEPART(WEEK,Date) ORDER BY (SELECT 1)) AS WeekThree
  FROM @TABLE
      ) a
 GROUP BY a.Location, a.Name, Week, a.WeekOne, a.WeekTwo, a.WeekThree

I think this is a little easier to read, and lends itself to using parameters better than the pivot method, but you're still stuck with manual columns.

Location    Week    Name        WeekOne WeekTwo WeekThree
---------------------------------------------------------
USA         49      Alex P      10      8       15
USA         50      Alex P      NULL    2       10
USA         49      Christine H 10      8       15
USA         50      Christine H NULL    2       10
USA         49      John S      25      20      30
USA         50      John S      NULL    5       20

query a table multiple times for different date ranges

1 Answers1