April 30, 2010 at 1:55 pm
Hi Guys,
I have a pretty straight forward challenge, the solution is not coming in my head right now.
The Table looks like
Region Col1 Col2 Col3
SOUTH N N Y
SOUTH Y N N
The output i want is
Region Col1 Col2 Col3
SOUTH 0 1 0
SOUTH 0 0 1
Essentially if i find a 'N' in the 3 columns,i need to convert the highest number column occurence to a 1 and make the rest 0.
Any ideas would be appreciated to get me started.
April 30, 2010 at 2:04 pm
April 30, 2010 at 2:09 pm
Sounds like something hellish in the making. Your post indicates you want the leftmost column with a Y in to be 1 and all other columns on that row to be 0. You could manage this using ROW_NUMBER() in SQL 2005 and above as so:
SELECT
Region,
CASE WHEN (ROW_NUMBER() OVER (ORDER BY Col1 DESC))=1 THEN 1 ELSE 0 END AS Col1,
CASE WHEN (ROW_NUMBER() OVER (ORDER BY Col1 DESC))<>1 AND (ROW_NUMBER() OVER (ORDER BY Col2 DESC))=1 THEN 1 ELSE 0 END AS Col2,
CASE WHEN (ROW_NUMBER() OVER (ORDER BY Col1 DESC))<>1 AND (ROW_NUMBER() OVER (ORDER BY Col2 DESC)) <> 1 AND (ROW_NUMBER() OVER (ORDER BY Col3 DESC))=1 THEN 1 ELSE 0 END AS Col3
FROM
yourtable
(And your example seems to contradict your requorements, since it should give 0, 0, 1 for row 1 and 1,0,0 for row 2 judging by what you've said).
April 30, 2010 at 2:29 pm
I would use the "quirky update" (or 3-part-update):
DECLARE @tbl TABLE
(
Region CHAR(10),Col1 CHAR(1), Col2 CHAR(1),Col3 CHAR(1)
)
INSERT INTO @tbl
SELECT 'SOUTH','N','N','Y' UNION ALL
SELECT 'SOUTH','Y','N','N'
DECLARE @n CHAR(1)
SET @n='0'
UPDATE @tbl
SET
@n=col3=CASE WHEN @n='0' AND col3='N' THEN '1' ELSE '0' END,
@n=col2=CASE WHEN @n='0' AND col2='N' THEN '1' ELSE '0' END,
@n=col1=CASE WHEN @n='0' AND col1='N' THEN '1' ELSE '0' END
FROM @tbl
SELECT *
FROM @tbl
Edit: assuming, the data need to be manipulated rather than just displayed...
May 3, 2010 at 1:05 pm
Hi All,
Thanks for the replies however the 2 solutions above do not work. The Row_Number solution was giving me incorrect output and the second solution just by looking at it is wrong, it will give me 1 if all 3 cols are N.
The answer is long on what got me here but the simple fact is this is a 3 month rolling flag, When i read it i simply need to determine the highest month that has a 'N'. I cannot read 2 'N' as this will make the totals incorrect.
I've posted my solution here which works perfect if anyone is looking...this wont take care of NYN scenario but that is not possible in my data since a N will make all further months 'N'. However a simple 3rd step in each case statement will take care of that.
SELECT
CASE DAY30
WHEN 'Y'
THEN 0
WHEN 'N'
THEN CASE DAY60 WHEN 'N' THEN 0 ELSE 1 END
ELSE 0
END as Day30,
CASE DAY60
WHEN 'Y'
THEN 0
WHEN 'N'
THEN CASE DAY90 WHEN 'N' THEN 0 ELSE 1 END
ELSE 0
END as Day60,
CASE DAY90
WHEN 'Y'
THEN 0
WHEN 'N'
THEN 1
ELSE 0
END as Day90
FROM
myTable
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply