December 16, 2003 at 12:49 pm
SELECT MAX(month_end),sum(num_units) from active_Units_Static where month_end >= '1/1/2002' and month_end <= '12/31/2002'
I want the sum of the month_end on the largest date. This select statement sums everything and not just the max records. IS it possible to get the sum of the max'd records. If not, then I could hard code the years by terminal number.
Matt
December 16, 2003 at 12:51 pm
Will something like this work for you:
SELECT MAX(month_end),sum(num_units) from active_Units_Static where month_end = (select max(month_end) from active_Units_Static)
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 16, 2003 at 1:08 pm
Hmm no, I need it per year by terminal summed up.Pretty much I just want a sum of the number as its part of a bigger query.
so if w/o the sum portion I get
termnum total Units
123 50
456 100
678 1
What I am looking for is:
total_units
151
Matt
Edited by - matt101 on 12/16/2003 1:13:41 PM
December 16, 2003 at 1:17 pm
Have you considered using datepart(yy,month_end) somewhere in your query, and/or the group by clause possible
Gregory A. Larsen, DBA
Contributor to 'The Best of SQLServerCentral.com 2002' book. Get a copy here: http:www.sqlservercentral.com/bestof/purchase.asp
Need SQL Server Examples check out my website at http://www.geocities.com/sqlserverexamples
Gregory A. Larsen, MVP
December 16, 2003 at 1:22 pm
Yes, I have I am only working with 1 year for now. Once I get the query working to actually show me the number I am looking for, I will be implementing that and it should work with ease but right now I am focusing on how to get the max date per terminal summed up which is the real issue at the moment.
Matt
December 16, 2003 at 1:35 pm
Matt, something like...
SELECT Terminal, Key_month_end, SUM(num_units)
FROM active_Units_Static A
JOIN (
SELECT Terminal, MAX(month_end) as Key_month_end
FROM active_Units_Static
where month_end >= '1/1/2002' and month_end <= '12/31/2002'
GROUP BY Terminal
) B
ON A.Terminal = B.Terminal
AND A.month_end = B.Key_month_end
GROUP BY A.Terminal, B.Key_month_end
If you want to group on an aggregate you may need to query the table twice (as my example).
Once you understand the BITs, all the pieces come together
December 16, 2003 at 1:45 pm
Wouldn't a classic group by work?
I don't know your data types - it would help next time if you show a create table script.
Assuming month_end as datetime (why would every day be called month_end?):
--note: it could be done in one select, but it's more clear & easier to modify when split
--create temp table
create table #t
(me tinyint,
yr int,
nu money
)
--select, group by date, get year in case needed in future
insert #t
select month(month_end), year(month_end), sum(num_units) from active_Units_Static
where year(month_end) = 1996
group by month(month_end),year(month_end)
--for last day of month
select dateadd(day,-1,dateadd(month,me,'01/01/' + cast(yr as char(4)))) as LastDay,
nu as [Sum of units]
from #t
order by LastDay
-- for last day with data
select max(month_end) as LastDay, nu as [Sum of units]
from #t t join active_Units_Static a on
t.yr = year(month_end) and t.me = month(month_end)
group by nu
order by LastDay
Hope that helps!
The only normal people are those you don't know well - Oscar Wilde
Data: Easy to spill, hard to clean up!
December 16, 2003 at 1:47 pm
Wow - the internet went down for a few minutes - and when my reply posts there's already 4 more!
Data: Easy to spill, hard to clean up!
December 16, 2003 at 2:04 pm
Thanks Thomas, that worked and now thats cool how that works.
Stubob: Thanks for your idea, its a bit long winded and no I cant just use a group by because the SUM portion sums everything between the date range and not just the max which is why posted here. I did take your Year(month_end) because it made my code slightly easier to read considering this is part of of a much larger select statement.
Matt
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply