Let's say that I have a database that has customer name, invoice date, and
amount in it. If an entry exists in this database, then the company owes us
money. I am trying to put together a report that, for each company, details
how much is owed that is 30 days out from a given date, how much is owed
that is 60 days out, 90 days out, and greater than that.
The first statement would look a lot like this:
SELECT name, sold, SUM(owed) AS AmountOwed
FROM InvOwed
WHERE (CONVERT(datetime, invdate) >= CONVERT(datetime, '4-mar-2004')) AND
(DATEADD(d, - 30, '4-mar-2004') <= CONVERT(datetime, invdate))
GROUP BY name, sold
ORDER BY name
The second statement looks like this:
SELECT name, sold, SUM(owed) AS AmountOwed
FROM InvOwed
WHERE (CONVERT(datetime, invdate) <= DATEADD(d, - 31, '4-mar-2004')) AND
(DATEADD(d, - 60, '4-mar-2004') <= CONVERT(datetime, invdate))
GROUP BY name, sold
ORDER BY name
And so on. But, then I have to cut-and-paste this into Excel, and massage
it a bit to get
Customer Amount30DaysDue Amount60DaysDue Amount90DaysDue
Is there any way to get all these in one SQL statement so I don't have to
massage the data?
Thank you.
JoshuaTry,
SELECT
[name],
sold,
SUM(case when invdate >= dateadd(day, -30, convert(char(8), getdate(),
112)) then owed end) AS Amount30DaysDue,
SUM(case when invdate >= dateadd(day, -60, convert(char(8), getdate(),
112)) and invdate < dateadd(day, -30, convert(char(8), getdate(), 112)) then
owed end) AS Amount60DaysDue,
SUM(case when invdate >= dateadd(day, -90, convert(char(8), getdate(),
112)) and invdate < dateadd(day, -60, convert(char(8), getdate(), 112)) then
owed end) AS Amount90DaysDue,
FROM
InvOwed
GROUP BY name, sold
ORDER BY name
go
AMB
"Joshua Campbell" wrote:
> Let's say that I have a database that has customer name, invoice date, and
> amount in it. If an entry exists in this database, then the company owes
us
> money. I am trying to put together a report that, for each company, detai
ls
> how much is owed that is 30 days out from a given date, how much is owed
> that is 60 days out, 90 days out, and greater than that.
> The first statement would look a lot like this:
> SELECT name, sold, SUM(owed) AS AmountOwed
> FROM InvOwed
> WHERE (CONVERT(datetime, invdate) >= CONVERT(datetime, '4-mar-2004')) AND
> (DATEADD(d, - 30, '4-mar-2004') <= CONVERT(datetime, invdate))
> GROUP BY name, sold
> ORDER BY name
> The second statement looks like this:
> SELECT name, sold, SUM(owed) AS AmountOwed
> FROM InvOwed
> WHERE (CONVERT(datetime, invdate) <= DATEADD(d, - 31, '4-mar-2004')) AND
> (DATEADD(d, - 60, '4-mar-2004') <= CONVERT(datetime, invdate))
> GROUP BY name, sold
> ORDER BY name
> And so on. But, then I have to cut-and-paste this into Excel, and massage
> it a bit to get
> Customer Amount30DaysDue Amount60DaysDue Amount90DaysDue
>
> Is there any way to get all these in one SQL statement so I don't have to
> massage the data?
> Thank you.
> Joshua
>
>
>|||Try:
select name,sold,sum(case when datediff(d,invdate,'20040304') between 1 and
30 then owed else 0 end) [30day],
sum(case when datediff(d,invdate,'20040304') between 31 and 60 then owed
else 0 end) [60day],
sum(case when datediff(d,invdate,'20040304') between 61 and 90 then owed
else 0 end) [90day]
from InvOwed
group by name,sold
-oj
"Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
news:%231oNyuUOFHA.3144@.tk2msftngp13.phx.gbl...
> Let's say that I have a database that has customer name, invoice date, and
> amount in it. If an entry exists in this database, then the company owes
> us
> money. I am trying to put together a report that, for each company,
> details
> how much is owed that is 30 days out from a given date, how much is owed
> that is 60 days out, 90 days out, and greater than that.
> The first statement would look a lot like this:
> SELECT name, sold, SUM(owed) AS AmountOwed
> FROM InvOwed
> WHERE (CONVERT(datetime, invdate) >= CONVERT(datetime, '4-mar-2004')) AND
> (DATEADD(d, - 30, '4-mar-2004') <= CONVERT(datetime, invdate))
> GROUP BY name, sold
> ORDER BY name
> The second statement looks like this:
> SELECT name, sold, SUM(owed) AS AmountOwed
> FROM InvOwed
> WHERE (CONVERT(datetime, invdate) <= DATEADD(d, - 31, '4-mar-2004')) AND
> (DATEADD(d, - 60, '4-mar-2004') <= CONVERT(datetime, invdate))
> GROUP BY name, sold
> ORDER BY name
> And so on. But, then I have to cut-and-paste this into Excel, and massage
> it a bit to get
> Customer Amount30DaysDue Amount60DaysDue Amount90DaysDue
>
> Is there any way to get all these in one SQL statement so I don't have to
> massage the data?
> Thank you.
> Joshua
>
>|||I didn't know you could use case like that. Excellent. Thank you very
much!
"oj" <nospam_ojngo@.home.com> wrote in message
news:eoXDR5UOFHA.2468@.tk2msftngp13.phx.gbl...
> Try:
> select name,sold,sum(case when datediff(d,invdate,'20040304') between 1
and
> 30 then owed else 0 end) [30day],
> sum(case when datediff(d,invdate,'20040304') between 31 and 60 then owed
> else 0 end) [60day],
> sum(case when datediff(d,invdate,'20040304') between 61 and 90 then owed
> else 0 end) [90day]
> from InvOwed
> group by name,sold
> --
> -oj
>
> "Joshua Campbell" <Joshua.Campbell@.nospam.nospam> wrote in message
> news:%231oNyuUOFHA.3144@.tk2msftngp13.phx.gbl...
and
owes
AND
massage
to
>
