Better way to do this query

Is there a better way to do this query? I was hoping to do it with a CASE
and IF...
SELECT TOP 100 PERCENT CustomerNumber, InvoiceNumber, InvoiceDate, DocType,
AgingBucket,
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE
WHEN AgingBucket = 1 THEN
Amount
ELSE 0
END AS 'IsCurrent',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 2 THEN Amount
ELSE 0
END AS '30 to 60',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 3 THEN Amount
ELSE 0
END AS '60 to 90',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 4 THEN Amount
ELSE 0
END AS '90 to 120',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 5 THEN CURTRXAM
ELSE 0
END AS '120 to 150',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE AgingBucket
WHEN 6 THEN Amount
ELSE 0
END AS '150 to 180',
CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
*
CASE
WHEN AgingBucket = 7 THEN Amount
ELSE 0
END AS 'Over 180'
FROM OpenInvoicesWhy are you using SELECT TOP 100 Percent in this query? You don't need to
specify that when you want all results returned.
Using a "WHEN" is similar to using an "IF" statement. You can specify
several when's in the same column case statement to cover various scenarios,
much like using an IF statement for several scenarios.
Are you trying to create an aggrigated report for your buckets, or do you
want a detailed list of each customer and their respective buckets?
You can sum, or use any other aggrigate function on these case columns to
return a report type query, but I'm still by the "top 100 percent"
statement.
"JP" <jperlowin@.firstfleet.com> wrote in message
news:OJwKYMcHFHA.1860@.TK2MSFTNGP15.phx.gbl...
> Is there a better way to do this query? I was hoping to do it with a CASE
> and IF...
> SELECT TOP 100 PERCENT CustomerNumber, InvoiceNumber, InvoiceDate,
DocType,
> AgingBucket,
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE
> WHEN AgingBucket = 1 THEN
> Amount
> ELSE 0
> END AS 'IsCurrent',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 2 THEN Amount
> ELSE 0
> END AS '30 to 60',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 3 THEN Amount
> ELSE 0
> END AS '60 to 90',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 4 THEN Amount
> ELSE 0
> END AS '90 to 120',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 5 THEN CURTRXAM
> ELSE 0
> END AS '120 to 150',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE AgingBucket
> WHEN 6 THEN Amount
> ELSE 0
> END AS '150 to 180',
> CASE WHEN DocType IN (6,7,9) THEN -1 ELSE 1 END
> *
> CASE
> WHEN AgingBucket = 7 THEN Amount
> ELSE 0
> END AS 'Over 180'
> FROM OpenInvoices
>|||I'm using the TOP because query analyzer told me to, when I tried to create
a view based on the query. Originally I didn't use it.
I'm trying to get document level detail from this.
I have 7 CASE statements since I need 7 columns. Is there a way I can pivot
the data?
"SW" <simon.worth@.gmail.com> wrote in message
news:uKbt6acHFHA.2752@.TK2MSFTNGP12.phx.gbl...
> Why are you using SELECT TOP 100 Percent in this query? You don't need to
> specify that when you want all results returned.
> Using a "WHEN" is similar to using an "IF" statement. You can specify
> several when's in the same column case statement to cover various
scenarios,
> much like using an IF statement for several scenarios.
> Are you trying to create an aggrigated report for your buckets, or do you
> want a detailed list of each customer and their respective buckets?
> You can sum, or use any other aggrigate function on these case columns to
> return a report type query, but I'm still by the "top 100
percent"
> statement.
> "JP" <jperlowin@.firstfleet.com> wrote in message
> news:OJwKYMcHFHA.1860@.TK2MSFTNGP15.phx.gbl...
CASE
> DocType,
>|||Oh OK, I understand then, if you're doing it in a view with an order by,
then you need the top in there. That makes sense.
To pivot the results you can use the GROUP BY with CUBE and GROUP BY with
ROLLUP functions, but the data may not be displayed exactly as you want it.
There is a document on Books Online that goes into detail as well on
crosstab queries, but you would probably have to run it from a stored
procedure instead in order to accommodate the temp tables required. Here's
the link
mk:@.MSITStore:C:\Program%20Files\Microso
ft%20SQL%20Server\80\Tools\Books\acd
ata.chm::/ac_8_qd_14_04j7.htm
or here on MSDN
http://msdn.microsoft.com/library/d.../>
sz_0kro.asp
"JP" <jperlowin@.firstfleet.com> wrote in message
news:OsdDxtcHFHA.3648@.TK2MSFTNGP09.phx.gbl...
> I'm using the TOP because query analyzer told me to, when I tried to
create
> a view based on the query. Originally I didn't use it.
> I'm trying to get document level detail from this.
> I have 7 CASE statements since I need 7 columns. Is there a way I can
pivot
> the data?
> "SW" <simon.worth@.gmail.com> wrote in message
> news:uKbt6acHFHA.2752@.TK2MSFTNGP12.phx.gbl...
to
> scenarios,
you
to
> percent"
> CASE
>