is this query possible?

• manssourianm

  Mr or Mrs. 500

  Points: 556

  More actions

  July 30, 2015 at 11:33 am

  #319422

  Hi,

  I work for a major public transit agency, I'm trying to create this report that needs a certain functionality. I've created a test example.

  I have this table:

  select '01' as a1, 'a' as a2 union all

  select '02' as a1, '' as a2 union all

  select '03' as a1, '' as a2 union all

  select '04' as a1, 'a' as a2 union all

  select '05' as a1, '' as a2 union all

  select '06' as a1, 'a' as a2 union all

  select '07' as a1, '' as a2 union all

  select '08' as a1, '' as a2 union all

  select '09' as a1, 'a' as a2 union all

  select '10' as a1, 'a' as a2 union all

  select '11' as a1, 'a' as a2 union all

  select '12' as a1, '' as a2 union all

  select '13' as a1, '' as a2 union all

  select '14' as a1, '' as a2 union all

  select '15' as a1, '' as a2 union all

  select '16' as a1, '' as a2 union all

  select '17' as a1, 'a' as a2 union all

  select '18' as a1, 'a' as a2

  and I'd like to write a query to end up with this table:

  select '01' as a1, 'a' as a2, '01' as a3 union all

  select '02' as a1, '' as a2, null as a3 union all

  select '03' as a1, '' as a2, null as a3 union all

  select '04' as a1, 'a' as a2, '02' as a3 union all

  select '05' as a1, '' as a2, null as a3 union all

  select '06' as a1, 'a' as a2, '05' as a3 union all

  select '07' as a1, '' as a2, null as a3 union all

  select '08' as a1, '' as a2, null as a3 union all

  select '09' as a1, 'a' as a2, '07' as a3 union all

  select '10' as a1, 'a' as a2, '10' as a3 union all

  select '11' as a1, 'a' as a2, '11' as a3 union all

  select '12' as a1, '' as a2, null as a3 union all

  select '13' as a1, '' as a2, null as a3 union all

  select '14' as a1, '' as a2, null as a3 union all

  select '15' as a1, '' as a2, null as a3 union all

  select '16' as a1, '' as a2, null as a3 union all

  select '17' as a1, 'a' as a2, '12' as a3 union all

  select '18' as a1, 'a' as a2, '18' as a3

  So I want to create column A3, it checks for the last non-null column in A2, and puts the value from A1 into the new A3 column.

  I've tried combining case statements with the lag function, but it starts getting hairy. Any suggestions?

  Thanks,

  martin

• Luis Cazares

  SSC Guru

  Points: 183698

  More actions

  July 30, 2015 at 12:06 pm

  #1816254

  Here's one option. Maybe you were very close.

  I know this is a 2008 forum, but you mention that you're using LAG (which was introduced on 2012).

  WITH SampleData AS(

  select '01' as a1, 'a' as a2 union all

  select '02' as a1, '' as a2 union all

  select '03' as a1, '' as a2 union all

  select '04' as a1, 'a' as a2 union all

  select '05' as a1, '' as a2 union all

  select '06' as a1, 'a' as a2 union all

  select '07' as a1, '' as a2 union all

  select '08' as a1, '' as a2 union all

  select '09' as a1, 'a' as a2 union all

  select '10' as a1, 'a' as a2 union all

  select '11' as a1, 'a' as a2 union all

  select '12' as a1, '' as a2 union all

  select '13' as a1, '' as a2 union all

  select '14' as a1, '' as a2 union all

  select '15' as a1, '' as a2 union all

  select '16' as a1, '' as a2 union all

  select '17' as a1, 'a' as a2 union all

  select '18' as a1, 'a' as a2

  ),

  PreviousVals AS(

  SELECT *,

  CASE WHEN a2 = 'a' THEN LAG(a1,1, a1) OVER(PARTITION BY a2 ORDER BY a1) END a3

  FROM SampleData

  )

  SELECT a1,

  a2,

  CASE WHEN a1 <> a3 THEN RIGHT( a3 + 101, 2) ELSE a3 END a3

  FROM PreviousVals

  ORDER BY a1

  Luis C.
  General Disclaimer:
  Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

  How to post data/code on a forum to get the best help: Option 1 / Option 2

• manssourianm

  Mr or Mrs. 500

  Points: 556

  More actions

  July 30, 2015 at 12:38 pm

  #1816281

  thank you, that's pretty clever. I was close, but probably would not have gotten the answer on my own. and I'm actually working in oracle on this specific query, but this is the only forum I am a member of. so I had to do minor tweaks to your solution, but it is exactly what I was looking for. thanks!

• souLTower

  SSCarpal Tunnel

  Points: 4026

  More actions

  July 31, 2015 at 5:23 am

  #1816395

  It's not too hairy. I don't know how it would perform against a large data set. This was an interesting exercise.

  SELECT * into #TMP FROM (

  select '01' as a1, 'a' as a2 union all

  select '02' as a1, '' as a2 union all

  select '03' as a1, '' as a2 union all

  select '04' as a1, 'a' as a2 union all

  select '05' as a1, '' as a2 union all

  select '06' as a1, 'a' as a2 union all

  select '07' as a1, '' as a2 union all

  select '08' as a1, '' as a2 union all

  select '09' as a1, 'a' as a2 union all

  select '10' as a1, 'a' as a2 union all

  select '11' as a1, 'a' as a2 union all

  select '12' as a1, '' as a2 union all

  select '13' as a1, '' as a2 union all

  select '14' as a1, '' as a2 union all

  select '15' as a1, '' as a2 union all

  select '16' as a1, '' as a2 union all

  select '17' as a1, 'a' as a2 union all

  select '18' as a1, 'a' as a2) D

  SELECT T.A1, T.A2,

  CASE WHEN T.A2 = '' THEN NULL

  ELSE

  ISNULL((select MIN(X.A1) from #TMP X WHERE X.A2 = '' AND X.A1 < T.A1 AND X.A1 >

  (SELECT TOP 1 D.A1 FROM #TMP D WHERE D.A2 = 'a' AND D.A1 < T.A1 ORDER BY D.A1 DESC)), T.A1) END AS A3

  FROM #TMP T ORDER BY T.A1 ASC