May 11, 2016 at 4:37 pm
Hey,
I am trying to write a query where I group my data by a string all my parameters are integers or date/time so i can use max,sum... functions to gather the appropiate date, but I need to somehow group by the string and then lookup the field associated with the max scheduled start time when comparing the two grouped rows, below is the code,
Code:
SELECT
[ID] = Coalesce([Tech],'')+ Coalesce('-'+convert(nvarchar(30),max([DOS]),120),''),
[Tech],
[DOS] = max([dos]),
[Cases] = COUNT([PID]),
[Workable_Hours]= sum([ORTime]/60),
[Calculated_Start_Time] = (Dateadd(hour, -1, min([Scheduled_Start]))),
[Scheduled_Start] = min([Scheduled_Start]),
[Min Start] = min([Convert_hookup_time]),
[Max End] = max([Convert_unhook_time]),
[Hospital] = ( I need to find the value from field hospital, associated with the min scheduled time, when grouped by tech)
FROM Database
WHERE [DOS] = (Dateadd (week, -30, CONVERT (date, GETDATE()))) AND ([Cancelled] <> 'Yes')AND ([Deleted] <> 'Yes') AND ([Closed] = 'Yes')
Group by
[Tech]
May 12, 2016 at 6:46 am
rgoolio32 (5/11/2016)
Hey,I am trying to write a query where I group my data by a string all my parameters are integers or date/time so i can use max,sum... functions to gather the appropiate date, but I need to somehow group by the string and then lookup the field associated with the max scheduled start time when comparing the two grouped rows, below is the code,
Code:
SELECT
[ID] = Coalesce([Tech],'')+ Coalesce('-'+convert(nvarchar(30),max([DOS]),120),''),
[Tech],
[DOS] = max([dos]),
[Cases] = COUNT([PID]),
[Workable_Hours]= sum([ORTime]/60),
[Calculated_Start_Time] = (Dateadd(hour, -1, min([Scheduled_Start]))),
[Scheduled_Start] = min([Scheduled_Start]),
[Min Start] = min([Convert_hookup_time]),
[Max End] = max([Convert_unhook_time]),
[Hospital] = ( I need to find the value from field hospital, associated with the min scheduled time, when grouped by tech)
FROM Database
WHERE [DOS] = (Dateadd (week, -30, CONVERT (date, GETDATE()))) AND ([Cancelled] <> 'Yes')AND ([Deleted] <> 'Yes') AND ([Closed] = 'Yes')
Group by
[Tech]
Just add the [Hospital] column to the group by clause...
Group by
[Tech],[Hospital]
May 12, 2016 at 2:21 pm
The old-school way of doing this would involve nested queries and joining back to your table multiple times.
Windowed functions make this so much easier:
SELECT
[ID] = Coalesce(X.[Tech],'')+ Coalesce('-'+convert(nvarchar(30),max(X.[DOS]),120),''),
X.[Tech],
[DOS] = max(X.[dos]),
[Cases] = COUNT(X.[PID]),
[Workable_Hours]= sum(X.[ORTime]/60),
[Calculated_Start_Time] = (Dateadd(hour, -1, min(X.[Scheduled_Start]))),
[Scheduled_Start] = min(X.[Scheduled_Start]),
[Min Start] = min(X.[Convert_hookup_time]),
[Max End] = max(X.[Convert_unhook_time]),
[Hospital] = MAX(CASE Ranking WHEN 1 THEN X.[Hospital] ELSE Null END)
FROM
(
SELECT
ROW_NUMBER() OVER (PARTITION BY [Tech] ORDER BY [Scheduled_Start] ASC) AS Ranking,
[DOS],
[PID],
[ORTime],
[Scheduled_Start],
[Convert_hookup_time],
[hospital]
FROM
Database
WHERE
[DOS] = (Dateadd (week, -30, CONVERT (date, GETDATE()))) AND ([Cancelled] <> 'Yes')AND ([Deleted] <> 'Yes') AND ([Closed] = 'Yes')
) X
GROUP BY
X.[Tech]
The inner query selects your data along with a "Ranking" column.
The "Ranking" column contains a sequence number, numbering the records within each group of Tech values sequentially by the Scheduled_Start date. The Scheduled_Start date is sequenced ascending, so the oldest date gets a Ranking value of 1.
The outer query then aggregates the results like you were before, but for the hospital value it uses a case statement to null out any hospital values in the grouping for rows with a Ranking other than 1. That way, only the row with the Ranking of 1 has a non-null Hospital value. The MAX() function ignores nulls, so the hospital value from the row Ranked 1 (with the minimum Schedule_Date) gets returned.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply