Creating a unique value for a certain period & staff
-
I'm trying to build some business logic for my ETL which gives a unique ID (Integer in this case) groups for a particular Date period related to a Staff and I'm stuck on how to achieve it. Only way I can think of is using case statements as I will have so many of them but I'm sure there is a better way.
I've cut down all my code and just generated the test data in 2 temp tables for this purpose.
CREATE TABLE #dateresult (StartDatePeriod DATE, EndDatePeriod DATE)
CREATE TABLE #stagingtable (StartDatePeriod DATETIME, EndDatePeriod DATETIME, StaffID INT, MeetingID INT)
INSERT INTO #dateresult(StartDatePeriod, EndDatePeriod)
VALUES
('2021-10-25 00:00:00.000', '2021-11-01 00:00:00.000'),
('2021-11-01 00:00:00.000', '2021-11-08 00:00:00.000'),
('2021-11-08 00:00:00.000', '2021-11-15 00:00:00.000')
INSERT INTO #stagingtable(StartDatePeriod,EndDatePeriod,StaffID,MeetingID)
VALUES
('2021-10-26 08:30:00.000','2021-10-26 08:40:00.000',1035627,NULL),
('2021-10-27 14:00:00.000','2021-10-27 14:45:00.000',1035627,NULL),
('2021-10-27 14:50:00.000','2021-10-27 15:35:00.000',1035627,NULL),
('2021-11-05 12:15:00.000','2021-11-05 13:00:00.000',1035627,NULL),
('2021-11-05 14:00:00.000','2021-11-05 14:45:00.000',1035627,NULL),
('2021-11-08 08:30:00.000','2021-11-08 08:40:00.000',1035627,NULL),
('2021-11-08 09:30:00.000','2021-11-08 10:15:00.000',1035627,NULL),
('2021-11-10 08:30:00.000','2021-11-10 08:40:00.000',1035627,NULL),
('2021-11-10 08:40:00.000','2021-11-10 09:25:00.000',1035627,NULL),
('2021-11-12 14:50:00.000','2021-11-12 15:35:00.000',1035627,NULL)the output I'm trying to achieve here is for Staff 1035627:
- statedate >= 2021-10-25 and < 2021-11-01 it should be MeetingID = 1
- statedate >= 2021-11-01 and < 2021-11-08 it should be MeetingID = 2
- statedate >= 2021-11-08 and < 2021-11-15 it should be MeetingID = 3
I was thinking it needs to be case statements, but then also using RANK to provide the Unique values.
My business logic will be more complex as I need to break it down via Days in that week period to group but I just want some assistance in helping me get started
Thanks in Advance
-
This looks like ROW_NUMBER() combined with a simple JOIN.
If you show what you have tried someone may help you.
-
I agree with Ken. With the addition of MeetingId, the problem seems trivial:
SELECT *
,Meeting_Id = ROW_NUMBER() OVER (ORDER BY d.StartDatePeriod)
FROM #dateresult d;The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin -
I think I may not have explained it correctly or I am overthinking.
I can't join the 2 tables since there is no reference between the 2. The #dateresult table is just for reference. it's just holding dates.
The #stagingtable is my output for what staff has a meeting for particular date time.
I need to do a check between the 2 and if the actual datetime from #Stagingtable falls in that period from #dateresult then do the rownumber / grouping ID.
I'm just working on some code, I'll post what I have shortly.
-
so you're looking for a date where there are no scheduling conflicts? (the span of the meeting doesn't overlap/conflict with any meetings of any of the invited participants)?
Sorry, it's just that I'm not sure what the question is.
-
I'm looking to group the meetings based on their start/end time for which falls in that week period.
I've attached a small screenshot which shows Group 1 and Group 2 for example.
Group 1 for this staff these meetings group in that period. while the others are group2
edit: I guess if i was able to do a join then i can write logic, but I'm struggling to work out how to write it with no join.
-
Just adding, I can do an Exists Check to ensure that the meeting exists but I can't group it which is where I'm stuck.
AND EXISTS (
SELECT *
FROM #dateresults dr
WHERE etl.StartDateTime >= dr.n
AND etl.EndDateTime < dr.WeekPeriod
) -
Was having a blank moment, I've got it just now trying to work out next stages regarding the grouping but least I've made progress.
The JOIN I did was the below which brought back my results.
INNER JOIN #dateresults dr
ON etl.StartDateTime >= dr.n
AND etl.EndDateTime < dr.WeekPeriodThen did it as a nested query to get the ROWNUMBER right.
Still tweaking this to now group it correctly, but will post the code shortly.
-
Using the test data provided by the OP...
WITH cteWeekAgg AS
(
SELECT StaffID
,WeekStart = DATEADD(dd,DATEDIFF(dd,0,StartDatePeriod)/7*7,0) --Always the Monday of the containing week.
,EventCount = COUNT(*) --Comment this out if you really don't want it.
FROM #StagingTable
GROUP BY StaffID, DATEDIFF(dd,0,StartDatePeriod)/7
)
SELECT StaffID
,WeekStart = CONVERT(DATE,WeekStart) --Inclusive
,WeekEnd = CONVERT(DATE,DATEADD(dd,7,WeekStart)) --Exclusive
,EventCount --Comment this out if you really don't want it.
,MeetingID = ROW_NUMBER() OVER (PARTITION BY StaffID ORDER BY WeekStart)
FROM cteWeekAgg
ORDER BY StaffID, WeekStart
;Results:
Caveats:
- Nothing was stated about the MeetingID numbering for other StaffIDs. Should the "1" simply be the first week that a StaffID appears or do they need to be a consistent number across StaffIDs for any given WeekStartDate?
- Nothing was stated about what to do if there's a gap for the weeks for any given StaffID.
Both issues can be fixed but didn't want to spend the time if that's not what's desired.
As a bit of a sidebar and considering that that same StaffID can have more than one event per week, I'm a bit bewildered as to why the "counting column" is labeled as "Meeting ID".
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally) -
Thanks Jeff, I think I've confused everyone with the questions, but the solution I've done is as below. Hopefully makes a bit more sense.
Re: the MeetingID this is basically a grouping of meetings within a recurring time frame. So any Mondays which fall within the 1st time frame are grouped, while the 2nd grouping. Its now working as expected.
Thanks for everyone's help - much appreciated.
IF OBJECT_ID(N'tempdb..#TempWeekBreakDown') IS NOT NULL
BEGIN
DROP TABLE #TempWeekBreakDown
END
GO
DECLARE @StartOfTheWeek DATETIME = (SELECT DATEADD(WEEK,DATEDIFF(WEEK, 0, StartDateTime), 0) FROM [ETL].[Configuration])
DECLARE @WeekPeriod DATETIME = (SELECT DATEADD(DAY,14,@StartOfTheWeek))
DECLARE @EndOfTerm DATETIME = (SELECT EndDateTIme FROM [ETL].[Configuration] WHERE IsActive = 1 )
;WITH cte_WeekBreakdown (StartOfWeek, EndOfWeekPeriod, EndOfTerm)
AS (
SELECT
[StartOfWeek] = @StartOfTheWeek,
[EndOfWeekPeriod] = DATEADD(DAY,14,@StartOfTheWeek),
[EndOfTerm] = @EndOfTerm
UNION ALL
SELECT
[StartOfWeek] = StartOfWeek + 14,
[EndOfWeekPeriod] = DATEADD(DAY,14,StartOfWeek + 14),
[EndOfTerm] = @EndOfTerm
FROM
cte_WeekBreakdown
WHERE
StartOfWeek <= EndOfTerm
)
SELECT [StartOfWeek],[EndOfWeekPeriod],[EndOfTerm]
INTO #TempWeekBreakDown
FROM cte_WeekBreakdown
WHERE StartOfWeek < EndOfTerm
GO
-- Insert new staff meetings from ETL to target
INSERT INTO [dbo].[TimeTable] (
[StartDateTime],
[EndDateTime],
[Subject],
[StaffID],
[Room],
[AppointmentID],
[Email],
[EventType],
[CreatedDateTime],
[RecurringMeetingID]
)
SELECT
resultset.[StartDateTime],
resultset.[EndDateTime],
resultset.[Subject],
resultset.[StaffID],
resultset.[Room],
resultset.[AppointmentID],
resultset.[Email],
[EventType] = 'I',
[CreatedDateTime] = GETDATE(),
RecurringMeetingID = DENSE_RANK() OVER(PARTITION BY resultset.staffID ORDER BY resultset.staffID, resultset.whatdayInt, resultset.StartTime, resultset.StartOfWeek, resultset.EndOfWeekPeriod ASC)
FROM (
SELECT
etl.[StartDateTime],
etl.[EndDateTime],
etl.[Subject],
etl.[StaffID],
etl.[Room],
etl.[AppointmentID],
etl.[Email],
[EventType] = 'I',
[CreatedDateTime] = GETDATE(),
wbd.StartOfWeek,
wbd.EndOfWeekPeriod,
WhatDayINT = DATEPART(WEEKDAY,etl.startdatetime),
StartDate = CONVERT(DATE,etl.StartDateTime,120),
StartTime = CONVERT(time,etl.StartDateTime,120),
newAppointmentID = SUBSTRING(etl.AppointmentID,11,20)
FROM
[ETL].[TimeTableSTG] etl
LEFT JOIN [dbo].[TimeTable] tt
ON etl.AppointmentID = tt.AppointmentID
INNER JOIN #TempWeekBreakDown wbd
ON etl.StartDateTime >= wbd.StartOfWeek
AND etl.EndDateTime < wbd.EndOfWeekPeriod
WHERE
tt.StartDateTime IS NULL
AND
NOT EXISTS (
SELECT *
FROM [ETL].[ExclusionDate] ed
WHERE CONVERT(DATE,etl.StartDateTime,120) >= ed.StartDateTime
AND CONVERT(DATE,etl.EndDateTime,120) <= ed.EndDateTime
AND ed.IsActive = 1
)
) resultset
ORDER BY
resultset.StartDateTime ASC -
Thanks for your feedback on what you've done. Can you provide expanded test data to demonstrate what you've done?
And, you've not avoided any joins like you said you wanted to do. 😀 That could probably still be done with only a little SQL prestidigitation. 😀
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply