count by week by start/end date
-
I'm trying to show a count by week but I am unsure of how to find dates between years? How do I show the Year, Week, and count for the example below? This ID_NUM should show 1 for every week for the entire year. I've shown what I've tried so far below. Thanks.
I'd want it to look like below. I've taken out some weeks to make it smaller for posting but it should have the entire year.
with test as
(
SELECT DISTINCT ID_NUM
,EFFDAT
,EXPDAT
,DATEPART(MONTH,EFFDAT) AS effmonth
,DATEPART(WEEK,EFFDAT) AS EFFWEEK
,DATEPART(year,EFFDAT) AS effyear
,DATEPART(MONTH,EXPDAT) AS expmonth
,DATEPART(WEEK,EXPDAT) AS EXPWEEK
,DATEPART(year,EXPDAT) as expyear
FROM AUTHS
WHERE EXPDAT >= '2019-01-01'
AND SVCCLS IN ('IDR','IDD','IPD','BED','IPA','IPC','IPP','IPW')
)
,cte as
(
select effyear
,min(effweek) week
,max(expweek) max_week
from test
group by effyear
union all
select effyear
,week + 1
,max_week
from cte
where week < max_week --and effyear = effyear
)
--select * from cte
select c.effyear, c.week, count(distinct t.id_num) cnt
from cte c
left join test t on c.week between t.effweek and t.expweek and c.effyear = t.effyear
group by c.effyear,c.week
order by c.effyear,c.week?- This topic was modified 4 years, 8 months ago by smattiko83.
- This topic was modified 4 years, 8 months ago by smattiko83.
-
I'm not quite clear what you mean. The ID is 1 for every row? Or do you mean there should be a row for every week of the year, regardless of if there is other data?
This gets you weeks for a few years.
WITH myTally(n)
AS
(SELECT n = ROW_NUMBER() OVER (ORDER BY (SELECT null))
FROM (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) a(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) b(n)
CROSS JOIN (VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10)) c(n)
)
, cteDates (DateValue) as
( SELECT DATEADD(WEEK, n, '1-1-2019') AS Datevalue
FROM Mytally
)
SELECT YEAR(cteDates.DateValue) AS DateYear
, DATEPART(WEEK, cteDates.DateValue) AS Dateweek
, cteDates.DateValue
FROM cteDates
ORDER BY cteDates.DateValue -
Re-reading this... are you trying to do a running total, or just a normal total? If you want a running total, I'd use a windowing function.
SUM([numeric column]) OVER (PARTITION BY date ROWS BETWEEN UNBOUNDED AND CURRENT ROW)
or similar.
-
You could add a date dimension table to your model. Week count for the year/month/quarter and many others are already in the table, you would have to join to it with your date. Its a two step process, create table then populate.
https://www.codeproject.com/Articles/647950/Create-and-Populate-Date-Dimension-for-Data-Wareho
-
I have a date calendar table. I'm just not sure how to use it to find the weeks between a date range.
-
Post the definition of your Calendar table. Are you familiar with dimension tables in data warehouse designs? Normally a Calendar dimension will be something like this:
CREATE TABLE Calendar (
CalendarDate DATE PRIMARY KEY,
Year INT,
MonthNumber TINYINT,
DayOfMonth TINYINT,
WeekNumberOfYear TINYINT,
MonthName VARCHAR(8),
…);Then you can join that to your data/fact table and summarize any way you want.
---- new stuff ---
Here's the data I started with:
---- new stuff ---
Here's the data I started with:
CREATE TABLE MyData (
ID_NUM INT PRIMARY KEY,
EFF_DATE DATE NOT NULL,
EXP_DATE DATE NOT NULL
);
GO
INSERT INTO MyData VALUES
(526,'5/21/2019','5/21/2020')
,(527,'6/1/2019','1/1/2020')
,(528,'5/1/2019','6/15/2019');Needed something to do, so I sorted this out (I think)… I used someone's dynamic Calendar function from here. (Can't remember whose), but here's the solution I came up with... First is the Calendar function I borrowed:
CREATE FUNCTION [dbo].[GenerateCalendar]
(
@FromDate DATETIME
,@NoDays INT
)
-- Generates a calendar table with sequential day numbering (@FromDate = SeqNo 1).
-- See RETURNS table (comments) for meaning of each column.
-- Notes: 1) Max for NoDays is 65536, which runs in just over 2 seconds.
--
-- Example calls to generate the calendar:
-- 1) Forward for 365 days starting today:
-- DECLARE @Date DATETIME
-- SELECT @Date = GETDATE()
-- SELECT *
-- FROM dbo.GenerateCalendar(@Date, 365)
-- ORDER BY SeqNo;
-- 2) Backwards for 365 days back starting today:
-- DECLARE @Date DATETIME
-- SELECT @Date = GETDATE()
-- SELECT *
-- FROM dbo.GenerateCalendar(@Date, -365)
-- ORDER BY SeqNo;
-- 3) For only the FromDate:
-- DECLARE @Date DATETIME
-- SELECT @Date = GETDATE()
-- SELECT *
-- FROM dbo.GenerateCalendar(@Date, 1);
-- 4) Including only the last week days of each month:
-- Note: Seq no in this case are as if all dates were generated
-- DECLARE @Date DATETIME
-- SELECT @Date = GETDATE()
-- SELECT *
-- FROM dbo.GenerateCalendar(@Date, 365)
-- WHERE Last = 1 ORDER BY SeqNo;
RETURNS TABLE WITH SCHEMABINDING AS
RETURN
--===== High speed code provided courtesy of SQL MVP Jeff Moden (idea by Dwain Camps)
--===== Generate sequence numbers from 1 to 65536 (credit to SQL MVP Itzik Ben-Gen)
WITH E1(N) AS (SELECT 1 UNION ALL SELECT 1), --2 rows
E2(N) AS (SELECT 1 FROM E1 a, E1 b), --4 rows
E4(N) AS (SELECT 1 FROM E2 a, E2 b), --16 rows
E8(N) AS (SELECT 1 FROM E4 a, E4 b), --256 rows
E16(N) AS (SELECT 1 FROM E8 a, E8 b), --65536 rows
cteTally(N) AS (
SELECT TOP (ABS(@NoDays)) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) FROM E16)
-- [SeqNo]=Sequential day number (@FromDate always=1) forward or backwards
SELECT [SeqNo] = t.N,
-- [Date]=Date (with 00:00:00.000 for the time component)
[Date] = dt.DT,
-- [Year]=Four digit year
[Year] = dp.YY,
-- [YrNN]=Two digit year
[YrNN] = dp.YY % 100,
-- [YYYYMM]=Integer YYYYMM (year * 100 + month)
[YYYYMM] = dp.YY * 100 + dp.MM,
-- [BuddhaYr]=Year in Buddhist calendar
[BuddhaYr] = dp.YY + 543,
-- [Month]=Month (as an INT)
[Month] = dp.MM,
-- [Day]=Day (as an INT)
[Day] = dp.DD,
-- [WkDNo]=Week day number (based on @@DATEFIRST)
[WkDNo] = DATEPART(dw,dt.DT),
-- Next 3 columns dependent on language setting so may not work for non-English
-- [WkDName]=Full name of the week day, e.g., Monday, Tuesday, etc.
[WkDName] = CONVERT(NCHAR(9),dp.DW),
-- [WkDName2]=Two characters for the week day, e.g., Mo, Tu, etc.
[WkDName2] = CONVERT(NCHAR(2),dp.DW),
-- [WkDName3]=Three characters for the week day, e.g., Mon, Tue, etc.
[WkDName3] = CONVERT(NCHAR(3),dp.DW),
-- [JulDay]=Julian day (day number of the year)
[JulDay] = dp.DY,
-- [JulWk]=Week number of the year
[JulWk] = dp.DY/7+1,
-- [WkNo]=Week number
[WkNo] = dp.DD/7+1,
-- [Qtr]=Quarter number (of the year)
[Qtr] = DATEPART(qq,dt.Dt),
-- [Last]=Number the weeks for the month in reverse
[Last] = (DATEPART(dd,dp.LDtOfMo)-dp.DD)/7+1,
-- [LdOfMo]=Last day of the month
[LdOfMo] = DATEPART(dd,dp.LDtOfMo),
-- [LDtOfMo]=Last day of the month as a DATETIME
[LDtOfMo] = dp.LDtOfMo
FROM cteTally t
CROSS APPLY
( --=== Create the date
SELECT DT = DATEADD(dd,(t.N-1)*SIGN(@NoDays),@FromDate)
) dt
CROSS APPLY
( --=== Create the other parts from the date above using a "cCA"
-- (Cascading CROSS APPLY (cCA), courtesy of Chris Morris)
SELECT YY = DATEPART(yy,dt.DT),
MM = DATEPART(mm,dt.DT),
DD = DATEPART(dd,dt.DT),
DW = DATENAME(dw,dt.DT),
Dy = DATEPART(dy,dt.DT),
LDtOfMo = DATEADD(mm,DATEDIFF(mm,-1,dt.DT),-1)
) dp;
GONow I can use the Calendar function to generate just the dates I need, and summarize:
DECLARE @StartDate DATE, @EndDate DATE, @NumDays INT;
SELECT @StartDate = MIN(Eff_Date)
,@EndDate = MAX(Exp_Date)
,@NumDays = DATEDIFF(day,@StartDate,@EndDate) + 1
FROM MyData;
SELECT wks.[Year]
, wks.JulWk
, COUNT(*) AS CountPerWk
FROM
(SELECT
gc.[Year]
-- ,gc.Day
,gc.JulWk
-- ,gc.Month
,md.ID_NUM
-- ,md.EFF_DATE
-- ,md.EXP_DATE
FROM MyData md
OUTER APPLY SCRIDB.dbo.GenerateCalendar(@StartDate,@NumDays) gc
WHERE gc.Date BETWEEN md.EFF_DATE AND md.EXP_DATE) wks
GROUP BY wks.[Year]
,wks.JulWk
ORDER BY wks.[Year]
,wks.JulWk;-- this section just gets the start/end points of the date range we need
DECLARE @StartDate DATE, @EndDate DATE, @NumDays INT;
SELECT @StartDate = MIN(Eff_Date)
,@EndDate = MAX(Exp_Date)
,@NumDays = DATEDIFF(day,@StartDate,@EndDate) + 1
FROM MyData;
-- this returns the solution, using the calendar dates from above
SELECT wks.[Year]
, wks.JulWk
, COUNT(*) AS CountPerWk
FROM
(SELECT
gc.[Year]
-- ,gc.Day
,gc.JulWk
-- ,gc.Month
,md.ID_NUM
-- ,md.EFF_DATE
-- ,md.EXP_DATE
FROM MyData md
OUTER APPLY SCRIDB.dbo.GenerateCalendar(@StartDate,@NumDays) gc
WHERE gc.Date BETWEEN md.EFF_DATE AND md.EXP_DATE) wks
GROUP BY wks.[Year]
,wks.JulWk
ORDER BY wks.[Year]
,wks.JulWk;Result:
2019 18 5
2019 19 7
2019 20 7
2019 21 13
2019 22 16
2019 23 21
2019 24 20
2019 25 14
2019 26 14
2019 27 14
2019 28 14
2019 29 14
2019 30 14
2019 31 14
2019 32 14
2019 33 14
2019 34 14
2019 35 14
2019 36 14
2019 37 14
2019 38 14
2019 39 14
2019 40 14
2019 41 14
2019 42 14
2019 43 14
2019 44 14
2019 45 14
2019 46 14
2019 47 14
2019 48 14
2019 49 14
2019 50 14
2019 51 14
2019 52 14
2019 53 4
2020 1 7
2020 2 7
2020 3 7
2020 4 7
2020 5 7
2020 6 7
2020 7 7
2020 8 7
2020 9 7
2020 10 7
2020 11 7
2020 12 7
2020 13 7
2020 14 7
2020 15 7
2020 16 7
2020 17 7
2020 18 7
2020 19 7
2020 20 7
2020 21 3
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply