March 22, 2016 at 3:17 am
I currenlty have a stored procedure which puts together a table which quite a clot of complicated rules (trying to get away from existing solution in VBA). I am stuck at the last bit and not sure if this is possible in SQL so would like some advice please.
My table looks like this....
Person ID, Name, StartDate, EndDate, Location, Live, Start, Finisher
-----------------------------------------------------------------------------------------------------
17, Joe B, 18/02/13, 29/03/13, UK, 0, New, -
17, Joe B, 01/01/14, 04/04/14, UK, 0, - , -
17, Joe B, 04/01/16, 25/12/16, UK, 1, - , Finisher
34, Sam A, 27/08/13, 14/08/15, USA, 0, New, Finisher
34, Sam A, 09/11/15, 25/12/15, USA, 0, New, Finisher
Basically the Start and Finisher columns are the end result I need.
I can achieve this in Excel using this formula....assuming columns as A through to H and the first person starts on row 2 under the header;
START =IF(A2=A1,IF(E2=E1,IF(C2-D1>19,"New",""),"New"),"New")
FINISHER =IF(A2=A3,IF(E2=E3,IF(C3-D2>19,"FINISHER",""),"FINISHER"),"FINISHER")
If I have the first 5 columns in a table in SQL and sorted the same way, how would i achieve this?
Thanks,
Kris
March 22, 2016 at 3:24 am
Data in a table doesn't have any inherent order. You need a column, or combination of columns, on which you can sort. The LAG and LEAD functions will get you the values from the previous or next rows for a given sort order.
John
March 22, 2016 at 6:45 am
John Mitchell-245523 (3/22/2016)
Data in a table doesn't have any inherent order. You need a column, or combination of columns, on which you can sort. The LAG and LEAD functions will get you the values from the previous or next rows for a given sort order.John
Hi John,
I've had a look at that as I've never heard of LAG and LEAD before and look great. Unfortunatley we're using 2008 until a few weeks time so can't use them which is disapointing as our compatability level is 100 and need 110 after googling.
March 22, 2016 at 6:52 am
Well, you did post in the SQL Server 2012 - T-SQL forum! If you're on 2008 then you'll need to resort to self-joins in order to get values from previous or next columns. The ROW_NUMBER function is likely to be of assistance to you here.
John
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply