February 24, 2012 at 9:48 am
Hello all,
I was trying to figure out any other way to achieve this out put.
Here is the sample data:
CREATE TABLE #tbl
(
prog varchar(6),
ordno int,
pcode varchar(6),
strtdt date,
fnshdt date
)
INSERT INTO #tbl
select
'AAAAAA', 1111, 'STRT', '01/01/2010', '05/01/2012'
UNION ALL
select
'AAAAAA', 2222, 'COMP', '01/01/2010', '12/31/2012'
UNION ALL
select
'BBBBBB', 1212, 'STRT', '01/01/2011', '05/01/2011'
UNION ALL
select
'BBBBBB', 2121, 'COMP', '01/01/2011', '12/31/2011'
UNION ALL
select
'CCCCCC', 2323, 'STRT', '01/01/2009', '05/01/2011'
UNION ALL
select
'CCCCCC', 4545, 'COMP', '01/01/2011', '12/31/2015'
select * from #tbl
I came up with this query:
select
prog
,MAX(stord)so
,MAX(fnord)fo
,MAX(stdt)sd
,MAX(fndt)fd
from
(select
prog
,case when pcode = 'STRT' then ordno end stord
,case when pcode = 'COMP' then ordno end fnord
,case when pcode = 'STRT' then strtdt end stdt
,case when pcode = 'COMP' then fnshdt end fndt
from #tbl) a
group by
prog
drop table #tbl
The output what I want to get is:
One record per line with Prog, start ordno, finish ordno and if the pcode is "STRT" then strtdt
else if pcode is "COMP" then fnshdt.
Thanks in advance!
February 24, 2012 at 10:28 am
Not sure I quite followed you, but looking at the query you've written, is this all you're after:
SELECT STRT.prog ,
STRT.ordno AS so ,
COMP.ordno AS fo ,
STRT.strtdt ,
COMP.fnshdt
FROM #tbl STRT
INNER JOIN #tbl COMP ON strt.prog = comp.prog
AND comp.pcode = 'COMP'
WHERE STRT.pcode = 'STRT'
This relies on there only being one STRT and COMP for each prog...
February 24, 2012 at 7:11 pm
Have you considered doing this?
SELECT prog
,MIN(ordno) AS so
,MAX(ordno) AS fo
,MIN(strtdt) AS strtdt
,MAX(fnshdt) AS fnshdt
FROM #tbl
GROUP BY prog
It returns the same result set as the previous two solutions. Also, solution #1 and #3 (mine) tie for the best query plan. The plan results I saw are 27%/45%/27%.
If you're worried about the query looking complex, perhaps mine looks simpler.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
February 24, 2012 at 8:42 pm
Thank you Howard!
You are correct, I was looking for the same output you query provided.
February 24, 2012 at 8:45 pm
Thank you dwain!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply