Only return sum from last dates in month 1

[zemp]

SQL Server 2000, SP4

I have one table that has entries based on a date. I am trying to get a sum of entries based on the last date within a month and only that date. Here is what I have so far.

SELECT     TOP 100 PERCENT ClientNumber, SUM(CurrentLives) AS Lives, CurrentDate, CurrentMonth
FROM         dbo.DrugLives
GROUP BY ClientNumber, CurrentDate, CurrentMonth
HAVING      (ClientNumber = '0801')
ORDER BY CurrentDate DESC

The table has two runs of data for one month. One run on the 15th and one run on the 29th. I can get a sum for both dates however I would like to only see the sum for the run on the 29th on the month. The last run done during that specific month. Then do that for each month within the range (quarter, year, etc.).

What am I missing? Can anyone point me in the right direction?

zemp

 

[gmmastros]

I would suggest getting the CurrentDate that you want to process, and then use it in the where clause.

Declare @ReportDate DateTime

Select @ReportDate = Max(CurrentDate)
From   DrugLives

SELECT   TOP 100 PERCENT ClientNumber, SUM(CurrentLives) AS Lives, CurrentDate, CurrentMonth
FROM     dbo.DrugLives
Where    CurrentDate = @ReportDate
GROUP BY ClientNumber, CurrentDate, CurrentMonth
HAVING   (ClientNumber = '0801')
ORDER BY CurrentDate DESC

This may not be perfect (because I don't necessarily understand the structure of your data), but hopefully you get the idea.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[sunila7]

Does this fix the issue?

SELECT     TOP 100 PERCENT ClientNumber, SUM(CASE CurrentDate WHEN 29 THEN CurrentLives ELSE 0 END) AS Lives, CurrentDate, CurrentMonth
FROM         dbo.DrugLives
GROUP BY ClientNumber, CurrentDate, CurrentMonth
HAVING      (ClientNumber = '0801')
ORDER BY CurrentDate DESC

Sunil

 

[AlexCuse]

It is hard to say without seeing sample data, but I imagine what you need to do is join to a subquery that will only include the max date for each month? That way you are showing your same result, but only showing the rows where the date = max date for current month.

Something like this:

SELECT     TOP 100 PERCENT ClientNumber, SUM(CurrentLives) AS Lives, CurrentDate, CurrentMonth
FROM         dbo.DrugLives
INNER JOIN
(
select max(CurrentDate) as CurrentDate
from dbo.DrugLives
group by CurrentMonth
) b
on dbo.DrugLives.CurrentDate = b.CurrentDate
GROUP BY ClientNumber, CurrentDate, CurrentMonth
HAVING      (ClientNumber = '0801')
ORDER BY CurrentDate DESC

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[zemp]

Alex, wow... bang on. I'd give you two stars if I could. Thank you very much.

Also thanks to all who took the time.

Just to clarify and for anyone else who looks at the thread later. Here is a sample of the data in the table (relevant fields only). The table holds data for more than just two months and for many clients.

ClientName Group Lives CurrentDate CurrentMonth
---------- ----- ----- ----------- ------------
0801 001 50 10/15/2006 10-2006
0801 002 50 10/15/2006 10-2006
0801 003 50 10/15/2006 10-2006
0801 001 52 10/29/2006 10-2006
0801 002 50 10/29/2006 10-2006
0801 003 56 10/29/2006 10-2006
0801 001 50 11/15/2006 11-2006
0801 002 55 11/15/2006 11-2006
0801 003 50 11/15/2006 11-2006
0801 001 48 11/29/2006 11-2006
0801 002 53 11/29/2006 11-2006
0801 003 47 11/29/2006 11-2006
...

Here is a sample of the result I had from my original query.

ClientName Lives CurrentDate CurrentMonth
---------- ----- ----------- ------------
0801 150 10/15/2006 10-2006
0801 158 10/29/2006 10-2006
0801 155 11/13/2006 11-2006
0801 148 11/24/2006 11-2006
...

With Alex's query the result was,

ClientName Lives CurrentDate CurrentMonth
---------- ----- ----------- ------------
0801 158 10/29/2006 10-2006
0801 148 11/24/2006 11-2006
...

and that was just what I needed. Only the data from the last date (or run, or count) of the month grouped by client and month.

zemp