March 29, 2011 at 6:39 am
Hi
I have a dataset that at present contains 16cols of data as follows (incl the col headings 1-16):
12345678910111213141516
2010/11A084E86XT240X599X599X59
2004/05A084 S009 W080 Z043 W080W08
2006/07A084S099W080W080W08
2003/04A394 R220 Y608 Y608Y60
2005/06A870 G020 K567 T828 Y848 P968 Y848Y84
2006/07C712 I639 Y838 G419 K219 Z876 Y838Y83
2007/08E778G08XQ750Q049I460E872E870E86XD696R34XT455Y442R251Y442Y44
2006/07I270 A410 T827 Y838 Y838Y83
2005/06J14X R628 S008 W222 W222W22
2006/07J90XY834Q201Q210Q243Z924Y834Y83
2005/06A499 Z518 S099 W220 W220W22
2010/11D70XR509Y433C910Z511L270Y407Y433Y43
2006/07H050T781X590X590X59
2003/04J069 R560 T230 X199 T110 X599 X199X19
2004/05J069 S099 W180 W180W18
2007/08J22XR11XY409Y409Y40
2010/11J348Y831R633Q300Y831Y83
2005/06L97X T827 Y848 Z866 Y848Y84
2006/07P910R030Y428R21XY428Y42
2010/11R040W519Q314Z518W519W51
2005/06S000 W040 W040W04
2008/09S000W080W080W08
2009/10S000W082Z038W082W08
2010/11S000W100W100W10
2004/05S000 W220 R11X W220W22
2009/10S002W578W578W57
2007/08K561S0200W060W060W06
2007/08L030S911X599X599X59
2007/08L253X460X460X46
2005/06N44X K403 J980 I270 Y838 Z871 Y838Y83
2003/04Q234 T814 Y834 Y834Y83
2005/06S007 W060 Z043 W060W06
2004/05S008 T741 Y079 Y079Y07
2005/06S008 W040 W040W04
2007/08S008W070W070W07
What I need to do is to search across cols 2-14 and find the first ocurrence of a code in the range 'V000' to 'Y98%' and place ther result in another col (as shown in col 15), before then truncating this data to 3 chars in length as shown in col 16.
At present we do this in Excel with a macro, but I now need to place this data in Reporting Services and I'm having problems knowing quite where to start as I have a limited timescale.
Any suggestions will be gratefully received.
Cheers
-Rich
March 29, 2011 at 6:58 am
Depending on how and where the data is retrieved you could use sql CASE statement, ie
UPDATE ...
SET col15 = CASE
WHEN col2 >= 'V000' AND col2 < 'Y99 ' THEN col2
WHEN col3 >= 'V000' AND col3 < 'Y99 ' THEN col3
...
WHEN col14 >= 'V000' AND col2 < 'Y99 ' THEN col14
END
and second update
UPDATE ...
SET col16 = LEFT(col15,3)
Far away is close at hand in the images of elsewhere.
Anon.
March 29, 2011 at 7:09 am
Try with a case
SELECT CASE WHEN col2 BETWEEN X AND Y THEN col2
WHEN col3 BETWEEN X AND Y THEN col3
....
END as col15
oups bad reading.. It is the solution for a output and not update.
March 30, 2011 at 5:04 am
Hi guys
Cheers for the replies - worked a treat. I think my brain was on a 'go-slow' day yesterday - too many meetings, but you've helped a lot - I just had to read the data into a global temp table first and insert an extra column to hold the data before running the 'Update'.
Thanks again.
-Rich
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply