multiple aggregates in SQL - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

multiple aggregates in SQL

Author

Topic

melissar
Starting Member

15 Posts

Posted - 2003-06-03 : 15:43:34

I have the following SQL
SELECT vis_cd_pmt_class, vis_client_id, SUM(bal_curr_amt) AS [0-60]
FROM T_Balances b JOIN T_Procedures ON proced_id = bal_proced_id
JOIN T_Visits ON vis_id = proced_vis_id
WHERE bal_dt =
(SELECT MAX(bal_dt)
FROM T_Balances
WHERE proced_id = bal_proced_id and vis_id = proced_vis_id)
AND proced_dt BETWEEN DATEDIFF(day, 180, GETDATE()) AND GETDATE()
GROUP BY vis_cd_pmt_class, vis_client_id

which works fine, but what I need is a SQL statement that will allow me to retrieve the information for various procedure dates, i.e. 0-60, 61-120, 121-180... in one SQL statement.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-03 : 15:59:20

something like

SELECT vis_cd_pmt_class, vis_client_id, SUM(bal_curr_amt) AS bal, dateadd(dd, d2*-1, GETDATE()), dateadd(dd, d1*-1, GETDATE())
FROM T_Balances b JOIN T_Procedures ON proced_id = bal_proced_id
JOIN T_Visits ON vis_id = proced_vis_id
join (select d1 = 0, d2 = 60 union select 60, 120 union select 120, 180 union ...) dt
WHERE bal_dt =
(SELECT MAX(bal_dt)
FROM T_Balances
WHERE proced_id = bal_proced_id and vis_id = proced_vis_id
)
AND proced_dt BETWEEN dateadd(dd, d2*-1, GETDATE()) AND dateadd(dd, d1*-1, GETDATE())
GROUP BY vis_cd_pmt_class, vis_client_id, dateadd(dd, d2*-1, GETDATE()), dateadd(dd, d1*-1, GETDATE())

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

melissar
Starting Member

15 Posts

Posted - 2003-06-03 : 16:06:30

Sorry, I don't think I was clear on that. The results that I need will be the sums of the balances for the time periods. So I will have a report that lists the sum for proced_dt 0-60, sum for proced_dt 61-120, sum for proced_dt 121-180...

It's basically a trial balance report listing outstanding balances for the specified time frame.

nr
SQLTeam MVY

12543 Posts

Posted - 2003-06-03 : 16:18:43

That's what it does.
the derived table holeds the number of days back from today for the time period and the result should give one row for each time period.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

melissar
Starting Member

15 Posts

Posted - 2003-06-03 : 16:38:31

Having a little trouble getting your sample to work. Is there any way to do that in one row?

melissar
Starting Member

15 Posts

Posted - 2003-06-05 : 10:12:28

Anyone else care to lend their expertise to my delimma? I can't seem to get the posted sample working.

Thanks

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-05 : 10:33:47

When you say "not working", what do you mean.

If it;s getting an error, then post the error.

If you're not getting the results you expect, then what do expect.

These are 2 very different problems.

Brett

8-)

melissar
Starting Member

15 Posts

Posted - 2003-06-05 : 10:51:24

There are syntax errors with the sample that was posted and I am not sure exactly what is going on in the join (select d1 = 0, d2 = 60 union select 60, 120 union select 120) dt Don't I need a ON statement here and if so what would I join on?

The result I need is a result set of aging balances for specific time periods, i.e. 0-60, 61-120, 121-180...

I think the sample he posted will give me the results but I can't get the query to execute.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-05 : 14:23:15

messilar --

this is not the exact answer, but it might lead you in the right direction.

Look at this:

First, look at this SQL statement, which returns all unpaid invoices and how many days overdue they are ("DaysOld") :

select customer,
DateDiff(dd, InvoiceDate, getdate()) as DaysOld,
InvoiceAmount
FROM
Invoices
WHERE
Paid = 0

Now, look at this:

Select customer,
SUM(case when DaysOld BETWEEN 0 and 30 THEN InvoiceAmount ELSE 0 END) as [0-30],
SUM(Case when DaysOLD BETWEEN 31 and 45 THEN InvoiceAmountELSE 0 END) as [31-45],
SUM(CASE when DaysOLD BETWEEN 46 and 60 THEN InvoiceAmountELSE 0 END) as [46-60],
SUM(CASE WHEN DaysOLD > 60 THEN InvoiceAmountELSE 0 END) as Over60,
SUM(InvoiceAmount) as Total
FROM
(... above SQL ...) A
GROUP BY Customer

Does that give you some ideas?

- Jeff

melissar
Starting Member

15 Posts

Posted - 2003-06-05 : 15:17:31

I think I can work with that solution. Unfortunately, the tables are a little more complex that Customer / Invoices, but I get the idea!

Thanks for the input and the new name - messilar

X002548
Not Just a Number

15586 Posts

Posted - 2003-06-05 : 16:02:32

quote:

...and the new name - messilar

Huh?

Brett

8-)

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2003-06-05 : 17:28:50

(I butchered her name pretty good!)

Sorry about that!

If you need more help, let me know. I usually find a simplified example that you can understand and then apply to a more complex real-world situation is the best way to learn.

Good luck, Melissa !

- Jeff

melissar
Starting Member

15 Posts

Posted - 2003-06-06 : 10:30:19

Thanks, Jeff. Your solution worked perfectly. Now I'm trying to get a total row at the end. COMPUTE isn't working yet and I don't need all the subtotal rows that CUBE and ROLLUP produce. Here's my final query if you have suggestions on the total issue. I just want one grand total row for each col.

SELECT code_desc AS [Financial Class], client_name AS [Client],
SUM(CASE WHEN DaysOld BETWEEN 0 AND 60 THEN balance ELSE 0 END) AS [0-60],
SUM(CASE WHEN DaysOld BETWEEN 61 AND 120 THEN balance ELSE 0 END) AS [60-120],
SUM(CASE WHEN DaysOld BETWEEN 121 AND 180 THEN balance ELSE 0 END) AS [121-180],
SUM(CASE WHEN DaysOld BETWEEN 181 AND 240 THEN balance ELSE 0 END) AS [181-240],
SUM(CASE WHEN DaysOld BETWEEN 241 AND 300 THEN balance ELSE 0 END) AS [241-300],
SUM(CASE WHEN DaysOld BETWEEN 301 AND 360 THEN balance ELSE 0 END) AS [301-360],
SUM(CASE WHEN DaysOld BETWEEN 361 AND 420 THEN balance ELSE 0 END) AS [361-420],
SUM(CASE WHEN DaysOld BETWEEN 421 AND 480 THEN balance ELSE 0 END) AS [421-480],
SUM(CASE WHEN DaysOld BETWEEN 481 AND 540 THEN balance ELSE 0 END) AS [481-540],
SUM(CASE WHEN DaysOld BETWEEN 541 AND 600 THEN balance ELSE 0 END) AS [541-600],
SUM(CASE WHEN DaysOld BETWEEN 601 AND 660 THEN balance ELSE 0 END) AS [601-660],
SUM(CASE WHEN DaysOld BETWEEN 661 AND 720 THEN balance ELSE 0 END) AS [661-720],
SUM(CASE WHEN DaysOld BETWEEN 721 AND 800 THEN balance ELSE 0 END) AS [721-800],
SUM(CASE WHEN DaysOld BETWEEN 801 AND 900 THEN balance ELSE 0 END) AS [801-900],
SUM(CASE WHEN DaysOld > 901 THEN balance ELSE 0 END) AS [900+]
FROM (SELECT vis_cd_pmt_class, vis_client_id, datediff(dd, vis_date, getdate()) AS DaysOld, balance
FROM T_Visits v1 JOIN (SELECT SUM(bal_curr_amt) AS balance, v2.vis_id
FROM T_Visits v2 JOIN T_Patients ON vis_pat_id = pat_id
JOIN T_Procedures ON vis_id = proced_vis_id
JOIN T_Balances ON proced_id = bal_proced_id
WHERE bal_dt = (SELECT MAX(bal_dt)
FROM T_Balances
WHERE proced_id = bal_proced_id and vis_id = proced_vis_id)
GROUP BY v2.vis_id) AS v2 ON v1.vis_id = v2.vis_id
WHERE balance <>0) AS a
JOIN T_Codes ON vis_cd_pmt_class = code_id
JOIN T_Clients ON client_id = vis_client_id
GROUP BY code_desc, client_name

Thanks again for your help!

messilar

ToddV
Posting Yak Master

218 Posts

Posted - 2003-06-06 : 11:18:05

Use WITH ROLLUP

Exclude the subtotal rows you do not need like this

HAVING (GROUPING(code_desc) = 0 OR GROUPING(client_name)=1)

You might need to play with this last part a bit. I never get it right on the first try.

melissar
Starting Member

15 Posts

Posted - 2003-06-06 : 11:35:10

Excellent! Using 1 instead of 0 in the GROUPING statement produced the desired results.

Thanks everyone for your input!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources