SQL Table Grouping

[milo xml]

Morning all. I need to select certain data from a table:

PI JobID FormID ShiftID StartEvent SW SG End Event EG EW
3 9249 36208 6241 2010-08-14 10:00:15.610 0 0 2010-08-14 10:14:52.000 143 109
3 9249 36208 6241 2010-08-14 10:14:52.217 109 143 2010-08-14 10:15:04.000 150 109
3 9249 36208 6241 2010-08-14 10:15:04.763 109 150 2010-08-14 10:15:07.000 150 109
3 9249 36208 6241 2010-08-14 10:15:09.820 109 150 2010-08-14 10:29:15.000 150 221
3 9249 36208 6241 2010-08-14 10:29:15.570 221 150 2010-08-14 10:53:09.000 376 300
3 9249 36208 6241 2010-08-14 10:53:09.240 300 376 2010-08-14 11:01:18.000 4294 1824
3 9249 36208 6241 2010-08-14 11:01:18.553 1824 4294 2010-08-14 11:02:06.000 4294 1942
3 9249 36208 6241 2010-08-14 11:02:06.363 1942 4294 2010-08-14 11:02:14.000 4294 1920

Out of this I need to get the start values and the end values, ignoring everything in between, essentially reducing it to this:

PI JobID FormID ShiftID StartEvent SW SG End Event EG EW
3 9249 36208 6241 2010-08-14 10:00:15.610 0 0 2010-08-14 11:02:14.000 4294 1920

Any help will be much appreciated. -edited to show that min and max values on SW, SG, EG, and EW may not necessarily work

[David Mujica]

Try this ... select PI,JobID,FormID,ShiftID,Min(StartEvent),Max(End Event) from someTable group by PI,JobID,FormID,ShiftID

[milo xml]

Thanks for the reply. That works for the times, but doesn't cover SW, SG, EG, and EW.

[Paul Conrad]

You're missing the SW, SG, EW, and EG fields he needed :doh:

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

[Paul Conrad]

Here is a fix up on David's query:

select PI,JobID,FormID,ShiftID,min(SW) as SW,min(SG) as SG,max(EG) as EG,
max(EW) as EW,Min(StartEvent) as StartEvent,Max(EndEvent) as EndEvent
from someTable
group by PI,JobID,FormID,ShiftID

Hope this helps out.

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

[milo xml]

Thanks for the reply. That's basically what I have now for my query. Unfortunately min and max only work on the timestamps. I need to select the specific values tied to those timestamps as there might be higher or lower values in the rest of the data. I updated the sample data to reflect that.

[Paul Conrad]

The query ought to still be able to work well with the updated data. It does have a Max function for both the EW and EG fields as well.

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

[Paul Conrad]

I am suspecting you probably need a select subquery to get the last row that you had updated the sample dataset with, because my query doesn't give you the 1920 in the EW column. The subquery might be one in which you get the minimum timestamp and the maximum timestamp, then the actual main query pulls the other information out of the subquery. essentially, David's query would be the subquery. Just a thought. You may want to do this as a stored procedure instead.

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

[milo xml]

Roger. Thanks for all your help. I wanted an challenge, I got one.

[Paul Conrad]

milo-xml wrote:

I wanted an challenge, I got one

Yes, you did. I like trying my best to help with a challenging task.

"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus

[Blue_Boy]

One of the ways:

select min(dt.[pi]),min(dt.jobid),min(dt.formid),min(dt.shiftid), min(dt.startevent),
(
select top 1 (dt.sw)
from datatable dt
order by dt.endevent

)as sw,
(
select top 1 (dt.sg)
from datatable dt
order by dt.endevent

)as sg,
(
select top 1 (dt.endevent)
from datatable dt
order by dt.endevent desc

) as endevent,
(
select top 1 (dt.eg)
from datatable dt
order by dt.endevent desc

) as eg,
(
select top 1 (dt.ew)
from datatable dt
order by dt.endevent desc

) as ew
from datatable dt

group by dt.[pi],dt.jobid,dt.shiftid

---

I Love T-SQL "VB.NET is developed with C#.NET" If my post helps you kindly save my time by voting my post.

[i j russell]

Try this [not tested but it should work!]

SELECT t1.PI,
t1.JobID,
t1.FormID,
t1.ShiftID,
t2.StartEvent,
t2.SW,
t2.SG,
t3.EndEvent,
t3.EG,
t3.EW
FROM
(
SELECT DISTINCT PI, JobID, FormID, ShiftID
FROM [table]
) t1
CROSS APPLY
(
SELECT TOP 1 StartEvent, SW, SG
FROM [table]
WHERE PI = t1.PI
AND JobID = t1.JobID
AND FormID = t1.FormID
AND ShiftID = t1.ShiftID
ORDER BY StartEvent ASC
) t2
CROSS APPLY
(
SELECT TOP 1 EndEvent, EG, EW
FROM [table]
WHERE PI = t1.PI
AND JobID = t1.JobID
AND FormID = t1.FormID
AND ShiftID = t1.ShiftID
ORDER BY EndEvent DESC
) t3

[milo xml]

This might have worked great if I wasn't working on a SQL2k server...... Thanks for your time.

[i j russell]

Wow, I didn't think that anyone would still be using Sql Server 2000. It's five versions old and all support ends for it in April 2013 (see http://support.microsoft.com/lifecycle/?LN=en-us&x=14&y=13&c2=1044[^]).

[milo xml]

Preaching to the choir my friend.

[Niladri_Biswas]

Hope this may help (Sql Server 2000+)

Declare @t table(PI int, JobID int, FormID int, ShiftID int, StartEvent datetime, SW int,SG int, EndEvent datetime,EG int,EW int)
Insert Into @t
Select 3,9249,36208,6241,'2010-08-14 10:00:15.610',0,0,'2010-08-14 10:14:52.000',143,109 Union All
Select 3,9249,36208,6241,'2010-08-14 10:14:52.217',109,143,'2010-08-14 10:15:04.000',150,109 Union All
Select 3,9249,36208,6241,'2010-08-14 10:15:04.763',109,150,'2010-08-14 10:15:07.000',150,109 Union All
Select 3,9249,36208,6241,'2010-08-14 10:15:09.820',109,150,'2010-08-14 10:29:15.000', 150,221 Union All
Select 3,9249,36208,6241,'2010-08-14 10:29:15.570', 221,150,'2010-08-14 10:53:09.000',376,300 Union All
Select 3,9249,36208,6241,'2010-08-14 10:53:09.240',300,376,'2010-08-14 11:01:18.000',4294,1824 Union All
Select 3,9249,36208,6241,'2010-08-14 11:01:18.553',1824,4294 ,'2010-08-14 11:02:06.000',4294,1942 Union All
Select 3,9249,6208,6241,'2010-08-14 11:02:06.363',1942,4294,'2010-08-14 11:02:14.000',4294,1920

Select X.*,Y.EndEvent,Y.EG,Y.EW
From( Select Top 1 PI,JobID,FormID,ShiftID,StartEvent,SW,SG
From @t
Order By StartEvent)X
Join ( Select Top 1 PI,EndEvent,EG,EW
From @t
Order By EndEvent DESC)Y
On X.PI = Y.PI

Result

PI JobID FormID ShiftID StartEvent SW SG EndEvent EG EW
3 9249 36208 6241 2010-08-14 10:00:15.610 0 0 2010-08-14 11:02:14.000 4294 1920

Niladri Biswas (Code Project MVP 2012)

[__TR__]

Hi, Here is one approach. Hope this helps.

CREATE TABLE #Temp
(
ID INT IDENTITY(1,1),
[PI] INT,
JobID INT,
FormID INT,
ShiftID INT,
StartEvent DATETIME,
SW INT,
SG INT,
EndEvent DATETIME,
EG INT,
EW INT
)

INSERT INTO #Temp
SELECT 3, 9249, 36208, 6241, '2010-08-14 10:00:15.610', 0, 0, '2010-08-14 10:14:52.000', 143, 109 UNION
SELECT 3, 9249, 36208, 6241, '2010-08-14 10:14:52.217', 109, 143, '2010-08-14 10:15:04.000', 150, 109 UNION
SELECT 3, 9249, 36208, 6241, '2010-08-14 10:15:04.763', 109, 150, '2010-08-14 10:15:07.000', 150, 109 UNION
SELECT 3, 9249, 36208, 6241, '2010-08-14 10:15:09.820', 109, 150, '2010-08-14 10:29:15.000', 150, 221 UNION
SELECT 3, 9249, 36208, 6241, '2010-08-14 10:29:15.570', 221, 150, '2010-08-14 10:53:09.000', 376, 300 UNION
SELECT 3, 9249, 36208, 6241, '2010-08-14 10:53:09.240', 300, 376, '2010-08-14 11:01:18.000', 4294, 1824 UNION
SELECT 3, 9249, 36208, 6241, '2010-08-14 11:01:18.553', 1824, 4294, '2010-08-14 11:02:06.000', 4294, 1942 UNION
SELECT 3, 9249, 36208, 6241, '2010-08-14 11:02:06.363', 1942, 4294, '2010-08-14 11:02:14.000', 4294, 1920

SELECT T1.*, T2.EndEvent, T2.EG, T2.EW FROM
(
SELECT T.[PI], T.JobID, T.FormID, T.ShiftID, T.StartEvent, T.SW, T.SG FROM #Temp T
INNER JOIN
(
SELECT MIN(ID) AS MinID, [PI], JobID, FormID, ShiftID FROM #Temp
GROUP BY [PI], JobID, FormID, ShiftID
) X ON T.ID = X.MinID
) T1
INNER JOIN
(
SELECT T.[PI], T.JobID, T.FormID, T.ShiftID, T.EndEvent, T.EG, T.EW FROM #Temp T
INNER JOIN
(
SELECT MAX(ID) AS MaxID, [PI], JobID, FormID, ShiftID FROM #Temp
GROUP BY [PI], JobID, FormID, ShiftID
) X ON T.ID = X.MaxID
) T2 ON T1.[PI] = T2.[PI] AND T1.JobID = T2.JobID AND T1.FormID = T2.FormID AND T1.ShiftID = T2.ShiftID

DROP TABLE #Temp

[milo xml]

Thanks all for your help. Took a little from all the answers. I got the data in the format I want, but not sure it's the most efficient way to do it. First I created a view that had my job data in it(I wasn't sure how to it another way...)

SELECT dbo.tblFormEvent.PressIndex, dbo.tblFormEvent.JobID, dbo.tblFormEvent.FormID, dbo.tblFormEvent.ShiftID, dbo.tblShift.ClockNumber, MIN(dbo.tblFormEvent.StartEvent) AS StartEvent, MAX(dbo.tblFormEvent.EndEvent) AS EndEvent
FROM dbo.tblFormEvent
INNER JOIN dbo.tblShift ON dbo.tblFormEvent.PressIndex = dbo.tblShift.PressIndex AND dbo.tblFormEvent.ShiftID = dbo.tblShift.ShiftID
GROUP BY dbo.tblFormEvent.PressIndex, dbo.tblFormEvent.JobID, dbo.tblFormEvent.FormID, dbo.tblFormEvent.ShiftID, dbo.tblShift.ClockNumber

(Forgot I needed the other data from another table as well...) Then I used that to query the count values from the main table using joins.

SELECT dbo.View_1.PressIndex, dbo.View_1.JobID, dbo.View_1.FormID, dbo.View_1.ShiftID, dbo.View_1.ClockNumber, dbo.View_1.StartEvent, tblFormEvent_1.StartWaste, tblFormEvent_1.StartGross, dbo.View_1.EndEvent, dbo.tblFormEvent.EndGross, dbo.tblFormEvent.EndWaste
FROM dbo.View_1 LEFT OUTER JOIN dbo.tblFormEvent ON dbo.View_1.PressIndex = dbo.tblFormEvent.PressIndex AND dbo.View_1.JobID = dbo.tblFormEvent.JobID AND dbo.View_1.FormID = dbo.tblFormEvent.FormID AND dbo.View_1.ShiftID = dbo.tblFormEvent.ShiftID AND dbo.View_1.EndEvent = dbo.tblFormEvent.EndEvent LEFT OUTER JOIN dbo.tblFormEvent AS tblFormEvent_1 ON dbo.View_1.StartEvent = tblFormEvent_1.StartEvent AND dbo.View_1.PressIndex = tblFormEvent_1.PressIndex AND dbo.View_1.JobID = tblFormEvent_1.JobID AND dbo.View_1.FormID = tblFormEvent_1.FormID AND dbo.View_1.ShiftID = tblFormEvent_1.ShiftID
ORDER BY dbo.View_1.PressIndex, dbo.View_1.StartEvent

Works, but I have serious doubts about the efficiency. It'll give me something to improve.

[Michael Potter]

Not really knowing the structure of your keys - something like this should work.

SELECT
s.PI,
s.JobID,
s.FormID,
s.ShiftID,
s.StartEvent,
s.SW,
s.SG,
e.EndEvent,
e.EG,
e.EW
FROM
[BaseTable] s
INNER JOIN
[BaseTable] e
ON (s.PI = e.PI AND
s.JobId = e.JobId AND
s.FormId = e.FormId AND
s.ShiftId = e.ShiftId AND
e.EndEvent = (SELECT
MAX(EndEvent)
FROM
[BaseTable]
WHERE
s.PI = PI AND
s.JobId = JobId AND
s.FormId = FormId AND
s.ShiftId = ShiftId))
WHERE
s.StartEvent = (SELECT
MIN(StartEvent)
FROM
[BaseTable]
WHERE
s.PI = PI AND
s.JobId = JobId AND
s.FormId = FormId AND
s.ShiftId = ShiftId)