How to calculate Weekly aggregates from daily data

Transact-SQL
1

we have detail daily data in a table about members., trying to finding how many new members (by considering ID1, ID2, ID3 columns values) monthly totals.

so we want to find out when is the ID1, ID2, ID3 combination first exist and mark add count for monthly.

CREATE TABLE #MYSRC

(

ID_1 VARCHAR(10),

ID_2 int,

ID_3 VARCHAR(10),

Pay1 varchar(6),

Pay2 varchar(6),

Status varchar(10),

FileDate Date

)

--- SOME MORE detail INFO about data

--- WE HAVE ABOUT 2 YEARS WORTH OF DAILY FEEDS DATA LOADED TO ONETABLE(MYSRCHISTORY_members) last column FileDate shows what day is the file,

--- USUALLY EACH DAY FILE HAVE ABOUT 30K RECORDS, IN WHICH IT INCLUDES OLD RECORDS AS IS, NEW ENTRIES ADDED (by considering id1,id2,id3 combination) , FOR SOME MEMBERS IT MAY CHANGE OTHER COLUMN VALUES.

--- WE FOUND MEMBER BASED ON THREE id FIELDS, IF ID_1,2,3 CHANGES THEN IT IS CONSIDER AS NEW MEMBER.

--- HERE WOULD LIKE TO KNOW HOW MANY NEWMEMBERS WERE ADDED PER EACH MONTH (MONTHLY AGGREGATION)

---Inserts 5 records in that file, 3 old 2 new (in 2 one is just id change one is

INSERT INTO #MYSRC VALUES ('3120', 250, '3241', '120.75','0.00','EN-25', '09/20/2021') -- exist same set (id1,2,3) in earlier data, so not a new member for Sep

INSERT INTO #MYSRC VALUES ('1121', 251, '3242', null,'3.00','EN-25', '09/20/2021') -- exist same set (id1,2,3) in earlier data, so not a new member for Sep

INSERT INTO #MYSRC VALUES ('3122', 250, '3243', '20.75','0.00','EN-25', '09/20/2021') -- exist same set (id1,2,3) in earlier data, so not a new member for Sep

INSERT INTO #MYSRC VALUES ('7120', 250, '3241', '120.75','0.00','EN-50', '09/03/2021') --New RECORDS because of ID1 change,

INSERT INTO #MYSRC VALUES ('1121', 251, '3242', null,'3.00','EN-25', '09/03/2021') ---Old RECORDS/member

INSERT INTO #MYSRC VALUES ('1122', 250, '3243', '20.75','0.00','EN-25', '09/02/2021') --old record

INSERT INTO #MYSRC VALUES ('1123', 252, '3244', null,null,'EN-25', '09/02/2021') --old record/member

INSERT INTO #MYSRC VALUES ('1122', 27, '3243', '20.75','0.00','EN-25', '09/01/2021') --New Set of ID1,2,3 not exist in earlier data so count it as new member for SEP

INSERT INTO #MYSRC VALUES ('1123', 252, '3244', null,null,'EN-25', '09/01/2021') --New Set of ID1,2,3 not exist in earlier data so count it as new member for SEP

---- August Data below - FileDate (last column)

INSERT INTO #MYSRC VALUES ('3120', 250, '3241', '120.75','0.00','EN-25', '08/31/2021') -- exist same set (id1,2,3) in earlier data, so not a new member for Aug

INSERT INTO #MYSRC VALUES ('1121', 251, '3242', null,'3.00','EN-25', '08/31/2021') --- exist same set (id1,2,3) in earlier data, so not a new member for Aug

INSERT INTO #MYSRC VALUES ('3122', 250, '3243', '20.75','0.00','EN-25', '08/31/2021') --- exist same set (id1,2,3) in earlier data, so not a new member for Aug

INSERT INTO #MYSRC VALUES ('3120', 250, '777', '120.75','0.00','EN-25', '08/30/2021') --New Set of ID1,2,3 not exist in earlier data so count it as new member for Aug

INSERT INTO #MYSRC VALUES ('1121', 251, '3242', null,'3.00','EN-25', '08/30/2021') --- exist same set (id1,2,3) in earlier data, so not a new member for July

INSERT INTO #MYSRC VALUES ('3120', 250, '3241', '120.75','0.00','EN-25', '08/29/2021') -- exist same set (id1,2,3) in earlier data, so not a new member for July

INSERT INTO #MYSRC VALUES ('1121', 251, '3242', null,'3.00','EN-25', '08/29/2021') -- exist same set (id1,2,3) in earlier data, so not a new member for July

INSERT INTO #MYSRC VALUES ('3122', 250, '3243', '20.75','0.00','EN-25', '08/29/2021') -- exist same set (id1,2,3) in earlier data, so not a new member for July

INSERT INTO #MYSRC VALUES ('3120', 250, '888', '120.75','0.00','EN-25', '08/18/2021') ---same set (id1,2,3) not exist earlier, appeared first in Aug 2021. so count it

INSERT INTO #MYSRC VALUES ('1121', 251, '3242', null,'3.00','EN-25', '08/17/2021')--- exist same set (id1,2,3) in earlier data, so not a new member for Aug

INSERT INTO #MYSRC VALUES ('1121', 37, '3242', null,'3.00','FU-25', '08/11/2021') --- New Set of ID1,2,3 not exist in earlier data so count it as new member for AUG

INSERT INTO #MYSRC VALUES ('3122', 36, '100', '20.75','0.00','EN-25', '08/11/2021') ---New Set of ID1,2,3 not exist in earlier data so count it as new member for AUG

---- July Data below

INSERT INTO #MYSRC VALUES ('3120', 250, '3241', '120.75','0.00','EN-25', '07/31/2021') --- exist same set (id1,2,3) in earlier data, so not a new member for July

INSERT INTO #MYSRC VALUES ('1121', 251, '3242', null,'3.00','EN-25', '07/31/2021') --- exist same set (id1,2,3) in earlier data, so not a new member for July

INSERT INTO #MYSRC VALUES ('9122', 250, '3243', '20.75','0.00','EN-25', '07/31/2021') -- New Set of ID1,2,3 not exist in earlier data so count it as new member for July

INSERT INTO #MYSRC VALUES ('3120', 250, '777', '120.75','0.00','EN-25', '07/20/2021') -- New Set of ID1,2,3 not exist in earlier data so count it as new member for July

INSERT INTO #MYSRC VALUES ('1121', 251, '3242', null,'3.00','EN-25', '07/20/2021') --- exist same set (id1,2,3) in earlier data, so not a new member for July

INSERT INTO #MYSRC VALUES ('3120', 250, '3241', '120.75','0.00','FU-25', '07/10/2021') -- New Set of ID1,2,3 not exist in earlier data so count it as new member for July

INSERT INTO #MYSRC VALUES ('6121', 251, '3242', null,'3.00','FU-25', '07/10/2021') -- New Set of ID1,2,3 not exist in earlier data so count it as new member for July

INSERT INTO #MYSRC VALUES ('3122', 250, '3243', '20.75','0.00','EN-25', '07/10/2021') -- New Set of ID1,2,3 not exist in earlier data so count it as new member for July

INSERT INTO #MYSRC VALUES ('3120', 250, '888', '120.75','0.00','EN-25', '07/05/2021') --- exist same set (id1,2,3) in earlier data, so not a new member for July

INSERT INTO #MYSRC VALUES ('5121', 251, '3242', null,'3.00','FU-25', '07/05/2021')-- New Set of ID1,2,3 not exist in earlier data so count it as new member for July

INSERT INTO #MYSRC VALUES ('1121', 251, '3242', null,'3.00','EN-25', '07/01/2021') --- exist same set (id1,2,3) in earlier data, so not a new member for July

INSERT INTO #MYSRC VALUES ('4122', 250, '100', '20.75','0.00','EN-25', '07/01/2021') -- New Set of ID1,2,3 not exist in earlier data so count it as new member for July

---- Jun

INSERT INTO #MYSRC VALUES ('3120', 250, '888', '120.75','0.00','EN-25', '06/05/2021')

INSERT INTO #MYSRC VALUES ('1121', 251, '3242', null,'3.00','EN-25', '06/05/2021')

INSERT INTO #MYSRC VALUES ('1121', 251, '3242', null,'3.00','EN-25', '06/01/2021')

INSERT INTO #MYSRC VALUES ('3122', 250, '100', '20.75','0.00','EN-25', '06/01/2021') --- assume jun 2021 is Starting population

Expected Output(ideal purpose only no no need to match)
Entypecount column is first 2 characters match to EN then count other wise 0
FUtypecount column is first 2 characters match to FU then count other wise 0

weekDate, NewMembersCount, ENtypecount, FUtypeCount

07-Jun-2021, 0 , 0,0
14-Jun-2021, 4, 1, 3
21-Jun-2021, 0, 0, 0
28-Jun-2021, 5, 2, 3
05-Jul-2021, 7, 3, 4

Thank you

ASita

2

I have a lot of questions about what you are looking for. You say you are looking for monthly totals, but you show weekly totals. How do you break out your weeks, this seems to be Mondays. Your expected results don't match the data you provided. For Jun 7, there are 4 records and 1 has the same ID_1, ID_2 and ID_3 values, so that would indicate 3 new members, but you show 0 in your results. I took a shot at Saturday being the end of the week, but just guessing at what you want

select WeekEndDate,
	   Sum(case when NewMember > 1 then 0 else 1 end) as NewMember,
	   sum(EN_Count) as EN_Count,
	   sum(FU_Count) as FU_Count

  from (
Select DateAdd(Day,7-datepart(dw,filedate), filedate) as WeekEndDate,
		row_number() over (partition by id_1, id_2, id_3 order by filedate desc) as NewMember,
		case when left(Status,2) = 'EN' then 1 else 0 end as EN_Count,
		case when left(Status,2) = 'FU' then 1 else 0 end as FU_Count
 from #MYSRC) v
group by WeekEndDate
1 Like
3

Good Morning

Please see below query

DECLARE @startDate datetime;
DECLARE @endDate datetime;
SELECT @startDate = MIN(FileDate), @endDate = MAX(FileDate) FROM #MYSRC;

;WITH CTE_Week_Start_End_Per_WeekNumber AS (
SELECT YEAR(@startDate) AS [Year], DATEPART(WEEK, @startDate) AS WeekNumber, DATEADD(dd, -(DATEPART(dw, @startDate)-1), @startDate) AS [WeekStart], DATEADD(dd, 7-(DATEPART(dw, @startDate)), @startDate) AS [WeekEnd]
UNION ALL
SELECT YEAR([WeekStart]) AS [Year], WeekNumber + 1 AS WeekNumber, DATEADD(wk, 1, [WeekStart]) AS [WeekStart], DATEADD(wk, 1, [WeekEnd]) AS [WeekEnd]
FROM CTE_Week_Start_End_Per_WeekNumber
WHERE [WeekEnd] <= @endDate
),
CTE_New_Number AS (
SELECT *
FROM (
SELECT ID_1,
ID_2,
ID_3,
[Status],
Year(FileDate) AS [Year],
DATEPART(WEEK, FileDate) AS WeekNumber,
ROW_NUMBER() OVER(PARTITION BY ID_1, ID_2, ID_3 ORDER BY FileDate) AS Ranked
FROM #MYSRC
) AS t
WHERE t.Ranked = 1
)
SELECT c1.[Year], REPLACE(REPLACE(CONVERT(varchar(12), c1.WeekStart, 107), ' ', '-'), ',', '-') + ' To ' + REPLACE(REPLACE(CONVERT(varchar(12), c1.WeekEnd, 107), ' ', '-'), ',', '-') AS [Week],
ISNULL(c2.NewMembers, 0) AS NewMembers,
ISNULL(FUMembersCounts, 0) AS FUMembersCounts,
ISNULL(EUMembersCount, 0) AS EUMembersCount
FROM CTE_Week_Start_End_Per_WeekNumber AS c1
LEFT JOIN (
SELECT [Year],
WeekNumber,
COUNT(*) AS NewMembers,
SUM(CASE WHEN LEFT([Status], 2) = 'FU' THEN 1 ELSE 0 END) AS FUMembersCounts,
SUM(CASE WHEN LEFT([Status], 2) = 'EN' THEN 1 ELSE 0 END) AS EUMembersCount
FROM CTE_New_Number
GROUP BY [Year], WeekNumber
) AS c2 ON c1.[Year] = c2.[Year] AND c1.WeekNumber = c2.WeekNumber
ORDER BY c1.[Year] DESC, c1.WeekNumber DESC;

so here with this query i am getting error stating ""The Statement terminated the maximum recursion 100 has been exhausted before statement completion""

Can you please help? Thank you

4

Thank you for your help initially, please let me know if you need any clarification

5

I'm struggling to try to figure out what you are asking. The code you provided worked properly in SQL 2016 environment. The "Expected Output(ideal purpose only no no need to match)" is not helpful since don't know what you are expecting from the data you provided. here are the result from you data and query.

year Week NewMembers FUMembersCounts EUMembersCount
2021 May-30--2021 To Jun-05--2021 3 0 3
2021 Jun-06--2021 To Jun-12--2021 0 0 0
2021 Jun-13--2021 To Jun-19--2021 0 0 0
2021 Jun-20--2021 To Jun-26--2021 0 0 0
2021 Jun-27--2021 To Jul-03--2021 1 0 1
2021 Jul-04--2021 To Jul-10--2021 4 3 1
2021 Jul-11--2021 To Jul-17--2021 0 0 0
2021 Jul-18--2021 To Jul-24--2021 1 0 1
2021 Jul-25--2021 To Jul-31--2021 1 0 1
2021 Aug-01--2021 To Aug-07--2021 0 0 0
2021 Aug-08--2021 To Aug-14--2021 2 1 1
2021 Aug-15--2021 To Aug-21--2021 0 0 0
2021 Aug-22--2021 To Aug-28--2021 0 0 0
2021 Aug-29--2021 To Sep-04--2021 4 0 4
2021 Sep-05--2021 To Sep-11--2021 0 0 0
2021 Sep-12--2021 To Sep-18--2021 0 0 0
2021 Sep-19--2021 To Sep-25--2021 0 0 0