question

skizzly avatar image
skizzly asked

Compare values on different row of same table

Hi all, I have the following code: CREATE TABLE testTable (ID INT IDENTITY(1,1) PRIMARY KEY , PRI INT , Employee VARCHAR(MAX) , Start_Date DATE , NetworkCode VARCHAR(MAX) , Activity1 INT , Activity VARCHAR(MAX) , Type VARCHAR(MAX) , Offset INT , Length INT , Minutes INT ) INSERT INTO testTable VALUES(770001,'bocb','06/01/2015','1234ABC',0010,'SOUTH','COVERAGE',0,420) INSERT INTO testTable VALUES(770001,'bocb','07/01/2015','1234ABC',0010,'MIDS','COVERAGE',0,480) INSERT INTO testTable VALUES(700199,'tomc','03/03/2015','1234ABC',0010,'FIS', 'COVERAGE',0,510) INSERT INTO testTable VALUES(700199,'tomc','03/03/2015',' ',' ','DETOO','LEAVE',255,510) INSERT INTO testTable VALUES(700199,'tomc', '04/03/2015','',' ','TRDMO','LEAVE',0,180) INSERT INTO testTable VALUES(700199,'tomc', '04/03/2015','1234ABC',0010,'FIS','COVERAGE',0,510) INSERT INTO testTable VALUES(401193,'frobc','08/04/2015',' ',0010,'MEEMS','LEAVE',0,60) INSERT INTO testTable VALUES(401193, 'frobc','08/04/2015','1234ABC',0010, 'SWA', 'COVERAGE',0,420) What I want to achieve is to update the "minutes" column where the dates are the same. The value of the "minutes" column would be the length of the "LEAVE" row subtract the "COVERAGE" row. Can this be achieved in a set based operation? Thanks in advance
row
4 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Kev Riley avatar image Kev Riley ♦♦ commented ·
And there is only 1 LEAVE and COVERAGE per day?
0 Likes 0 ·
skizzly avatar image skizzly commented ·
hi thanks for getting back. No it is variable how many Activites can done on a day. The source table has 104k rows. A group by has shown that the maximum number of activities on any on day is three. Thanks in advance
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
So how do you tie the 2 events together within a day - is it by PRI alone, or with Employee too?
0 Likes 0 ·
skizzly avatar image skizzly commented ·
Hi its with the PRI and the date of the event ... appreciate the help
0 Likes 0 ·

1 Answer

·
Kev Riley avatar image
Kev Riley answered
Is this giving the correct values? select *, Leave.Length - Coverage.Length as [Minutes] from dbo.testTable testTable1 outer apply (select Length from dbo.testTable testTable2 where testTable2.type = 'LEAVE' and testTable1.PRI = testTable2.PRI and testTable1.Start_Date = testTable2.Start_Date) Leave outer apply (select Length from dbo.testTable testTable2 where testTable2.type = 'COVERAGE' and testTable1.PRI = testTable2.PRI and testTable1.Start_Date = testTable2.Start_Date) Coverage
5 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

skizzly avatar image skizzly commented ·
Hi Kev, really good steer, Can explain why i get two Length columns? ID PRI Employee Start_Date NetworkCode Activity1 Activity Type Offset Length Minutes Length Length Minutes 1 770001 bocb 2015-01-06 00:00:00.000 1234ABC 10 SOUTH COVERAGE 0 420 0 NULL 420 NULL 2 770001 bocb 2015-01-07 00:00:00.000 1234ABC 10 MIDS COVERAGE 0 480 0 NULL 480 NULL 3 700199 tomc 2015-03-03 00:00:00.000 1234ABC 10 FIS COVERAGE 0 510 0 510 510 0 4 700199 tomc 2015-03-03 00:00:00.000 0 DETOO LEAVE 255 510 0 510 510 0 5 700199 tomc 2015-03-04 00:00:00.000 0 TRDMO LEAVE 0 180 0 180 510 -330 6 700199 tomc 2015-03-04 00:00:00.000 1234ABC 10 FIS COVERAGE 0 510 0 180 510 -330 7 401193 frobc 2015-04-08 00:00:00.000 10 MEEMS LEAVE 0 60 0 60 420 -360 8 401193 frobc 2015-04-08 00:00:00.000 1234ABC 10 SWA COVERAGE 0 420 0 60 420 -360 Apologies for newbie questions, but that's what i am.... Also, will this method be applicable for multiple tasks in a day? Thanks
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
There should be 3 length columns - the last 2 are the ones from the OUTER APPLYs, I just left them in to show you the workings of the query. Multiple tasks per day should be fine - it is using date and PRI to uniquely identify the task
0 Likes 0 ·
skizzly avatar image skizzly commented ·
Sorry to be so slow, how do i remove the additional columns????
0 Likes 0 ·
Kev Riley avatar image Kev Riley ♦♦ commented ·
It's only because I specified '*@ as the column selection - so change the column selection from select *, Leave.Length - Coverage.Length as [Minutes] to select testable.*, Leave.Length - Coverage.Length as [Minutes] or whatever columns you need. I thought you where using this to update the minutes value?
0 Likes 0 ·
skizzly avatar image skizzly commented ·
Thanks Kev, i dont want the output to be shown to end user, just needed the columns set up in the table definition. Now that i know how to use will tinker with the syntax you provided. I do want to update the minutes column, but this output will be for a report and i dont need to store the values in the mintues table, as it will be a derived column. I tinkeered with the syntax : select PRI, Employee,Activity1, Activity, Start_Date, Leave.offset - testTable1.offset as [Minutes] from dbo.testTable testTable1 outer apply (select Offset from dbo.testTable testTable2 where testTable1.PRI = testTable2.PRI and testTable1.Start_Date = testTable2.Start_Date) Leave But i now get 14 rows... when i only inserted 8 rows... how can this be amended? Thanks in advance Kev
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.