Prev: Question about index usage in a query
Next: 1 query; 2 different actual execution plan
From: Peso on 4 Dec 2009 10:31
What to do with this sample data?

INSERT INTO Foo VALUES(0, 'Y', 'N', 'Y', 'Y');

Should it be returned as "ACD"?


"NetNewbie" <NetNewbie(a)discussions.microsoft.com> wrote in message
news:FA801DF3-5807-43DD-AA51-3BC91DEE1AE0(a)microsoft.com...
> Hi, I have a table(T1) which has 7 columns out of which 4 columns are of
> char(1) data which either have Y or N.
>
> Now the task is I need to update a column in a different table based on
> foll. conditions.
>
> treating each of the 4 columns C1, C2, C3, c4 as A, B, C, D in that order
> if
> the data in the 4 columns is
> - YNNN, then return A
> - YNYN then return AC
> - YNNY then return AD
> - YYYY then return AD (max and min) A being the max and D beign the min.
> - YYYN then return AC (max and min)
>
> I hope I made my question clear. What is the best way to achieve this in
> T-SQL if it can be without writing a long series of CASE statements
>
> The data in T! has been loaded from a flatfile. I can re-load these 4
> single
> char columns into 1 column of char(4) if that makes the task easy. To
> start
> with i loaded into 4 columns.
>
> Thank you for your time

From: Plamen Ratchev on 4 Dec 2009 10:48
Seems it should be 'AD' as the way I understand the requirements it is the MIN and MAX appearance of Y in the columns
(like in the cases where keycol is 2 and 3).

--
Plamen Ratchev
http://www.SQLStudio.com
From: NetNewbie on 4 Dec 2009 11:15
The output for that (YNYY) should be AD

"Peso" wrote:

> What to do with this sample data?
>
> INSERT INTO Foo VALUES(0, 'Y', 'N', 'Y', 'Y');
>
> Should it be returned as "ACD"?
>
>
> "NetNewbie" <NetNewbie(a)discussions.microsoft.com> wrote in message
> news:FA801DF3-5807-43DD-AA51-3BC91DEE1AE0(a)microsoft.com...
> > Hi, I have a table(T1) which has 7 columns out of which 4 columns are of
> > char(1) data which either have Y or N.
> >
> > Now the task is I need to update a column in a different table based on
> > foll. conditions.
> >
> > treating each of the 4 columns C1, C2, C3, c4 as A, B, C, D in that order
> > if
> > the data in the 4 columns is
> > - YNNN, then return A
> > - YNYN then return AC
> > - YNNY then return AD
> > - YYYY then return AD (max and min) A being the max and D beign the min.
> > - YYYN then return AC (max and min)
> >
> > I hope I made my question clear. What is the best way to achieve this in
> > T-SQL if it can be without writing a long series of CASE statements
> >
> > The data in T! has been loaded from a flatfile. I can re-load these 4
> > single
> > char columns into 1 column of char(4) if that makes the task easy. To
> > start
> > with i loaded into 4 columns.
> >
> > Thank you for your time
>
> .
>
First  |  Prev  | 
Pages: 1 2
Prev: Question about index usage in a query
Next: 1 query; 2 different actual execution plan