March 6, 2009 at 5:44 pm
SELECT MYDATE AS year, DATENAME(mm, MYDATE) AS month, SUM(number) AS totalnumber, SUM(amount)
AS totalamount
FROM MYTABLE
GROUP BY MYDATE, DATENAME(mm, MYDATE)
ORDER BY MYDATE, DATENAME(mm, MYDATE)
year month totalnumber totalamount
2004-08-26 10:39:00 August 2199 10757555.0000
2004-08-27 06:30:00 August 1823 9519337.0000
2004-08-28 06:30:00 August 1791 9062078.0000
2004-08-29 06:30:00 August 1930 9545910.0000
2004-08-30 06:30:00 August 2063 10222656.0000
2004-08-31 06:30:00 August 2063 10093961.0000
2004-09-01 06:30:00 September 2199 9949850.0000
2004-09-02 06:30:00 September 2238 9922120.0000
2004-09-03 06:30:00 September 2206 9417105.0000
2004-09-04 06:30:00 September 2157 9495735.0000
2004-09-05 06:30:00 September 2260 9636051.0000
2004-09-06 06:30:00 September 2858 10763838.0000
2004-09-07 06:30:00 September 2954 11209676.0000
2004-09-08 06:30:00 September 2567 10050040.0000
...........
..........
.........
Now I need to modify the above query so that totalnumber and totalamount gets added up if its the same month in month column and in the year column the last day of that corresponding month column is displayed along with the year
Eg:
2004-08-31 06:30:00 August 11869 59201497 (11869=2199+1823+1791+1930+2063+2063).
Last day of august is displayed.
2004-09-30 06:30:00 September 19439 80444415 (2199+2238+2206+2157+2260+2858+2954+2567) Last day of september is displayed.
Similarly the totalamount column is added up if its the same month .
I need a T-sql or a stored procedure where I pass a @startdate and an @enddate as parameters and retrieve the above mentioned column values.
Thanks
March 6, 2009 at 6:39 pm
Does this query give you what you want?
For each month, it returns the row with the most recent MYDATE value for that month, which is not necessarily the last day of the month.
SELECT
MAX(MYDATE) AS year,
DATENAME(mm, MYDATE) AS month,
SUM(number) AS totalnumber,
SUM(amount) AS totalamount
FROM dbo.MYTABLE
GROUP BY DATEADD(month, DATEDIFF(month, 0, MYDATE), 0), DATENAME(mm, MYDATE)
ORDER BY DATEADD(month, DATEDIFF(month, 0, MYDATE), 0)
If you want to return the results for a specified date range (@startDate, @endDate), simply include a WHERE clause.
SELECT
MAX(MYDATE) AS year,
DATENAME(mm, MYDATE) AS month,
SUM(number) AS totalnumber,
SUM(amount) AS totalamount
FROM dbo.MYTABLE
WHERE (MYDATE >= @startDate AND MYDATE < DATEADD(day, 1, @endDate))
GROUP BY DATEADD(month, DATEDIFF(month, 0, MYDATE), 0), DATENAME(mm, MYDATE)
ORDER BY DATEADD(month, DATEDIFF(month, 0, MYDATE), 0)
March 6, 2009 at 10:14 pm
metro17 (3/6/2009)
SELECT MYDATE AS year, DATENAME(mm, MYDATE) AS month, SUM(number) AS totalnumber, SUM(amount)AS totalamount
FROM MYTABLE
GROUP BY MYDATE, DATENAME(mm, MYDATE)
ORDER BY MYDATE, DATENAME(mm, MYDATE)
year month totalnumber totalamount
2004-08-26 10:39:00 August 2199 10757555.0000
2004-08-27 06:30:00 August 1823 9519337.0000
2004-08-28 06:30:00 August 1791 9062078.0000
2004-08-29 06:30:00 August 1930 9545910.0000
2004-08-30 06:30:00 August 2063 10222656.0000
2004-08-31 06:30:00 August 2063 10093961.0000
2004-09-01 06:30:00 September 2199 9949850.0000
2004-09-02 06:30:00 September 2238 9922120.0000
2004-09-03 06:30:00 September 2206 9417105.0000
2004-09-04 06:30:00 September 2157 9495735.0000
2004-09-05 06:30:00 September 2260 9636051.0000
2004-09-06 06:30:00 September 2858 10763838.0000
2004-09-07 06:30:00 September 2954 11209676.0000
2004-09-08 06:30:00 September 2567 10050040.0000
...........
..........
.........
Now I need to modify the above query so that totalnumber and totalamount gets added up if its the same month in month column and in the year column the last day of that corresponding month column is displayed along with the year
Eg:
2004-08-31 06:30:00 August 11869 59201497 (11869=2199+1823+1791+1930+2063+2063).
Last day of august is displayed.
2004-09-30 06:30:00 September 19439 80444415 (2199+2238+2206+2157+2260+2858+2954+2567) Last day of september is displayed.
Similarly the totalamount column is added up if its the same month .
I need a T-sql or a stored procedure where I pass a @startdate and an @enddate as parameters and retrieve the above mentioned column values.
Thanks
If you'll be kind enough to post the CREATE TABLE statement and some readily consumable data in the format described by the article located by the link in my signature below, I'll show you a really cool trick about this.
--Jeff Moden
Change is inevitable... Change for the better is not.
March 7, 2009 at 7:50 am
--Note your initial post is unclear my solution assumes that you always want the last day of each month to appear in the row even if that day does not appear in your table.
--------------------------------------------------------------------
-------CREATE FUNCTION TO AMKE COODE MORE READABLE-----------------
--------------------------------------------------------------------
if OBJECT_ID('dbo.floordate') is not null drop function floordate
go
create function dbo.floordate(@date smalldatetime)
returns smalldatetime
as
begin
declare @returndate smalldatetime
select @returndate = cast(floor(cast(@date as float))as smalldatetime)
return @returndate
end
go
--------------------------------------------------------------------
-------CREATE TEST DATA---------------------------------------------
--------------------------------------------------------------------
declare @test-2 table (id int identity, date smalldatetime, month varchar(14), totalnumber int, totalamount float)
insert @test-2
select '2004-08-26 10:39:00', 'August', 2199, 10757555.0000 union all
select '2004-08-27 06:30:00','August', 1823, 9519337.0000 union all
select '2004-08-28 06:30:00', 'August', 1791, 9062078.0000 union all
select '2004-08-29 06:30:00', 'August', 1930, 9545910.0000 union all
select '2004-08-30 06:30:00', 'August', 2063, 10222656.0000 union all
select '2004-08-31 06:30:00','August', 2063, 10093961.0000 union all
select '2004-09-01 06:30:00', 'September', 2199, 9949850.0000 union all
select '2004-09-02 06:30:00', 'September', 2238, 9922120.0000 union all
select '2004-09-03 06:30:00', 'September', 2206, 9417105.0000 union all
select '2004-09-04 06:30:00', 'September', 2157, 9495735.0000 union all
select '2004-09-05 06:30:00', 'September', 2260, 9636051.0000 union all
select '2004-09-06 06:30:00', 'September', 2858, 10763838.0000 union all
select '2004-09-07 06:30:00', 'September', 2954, 11209676.0000 union all
select '2004-09-08 06:30:00', 'September', 2954, 11209676.0000 union all
select '2004-09-30 06:30:00', 'September', 2567, 10050040.0000
--------------------------------------------------------------------
-------THE QUERY----------------------------------------------------
--------------------------------------------------------------------
select dateadd(mm,1,dateadd(dd,datepart(dd,dbo.floordate(date))*-1,dbo.floordate(date))) floordate, month ,
SUM(totalnumber) totalnumber, SUM(totalamount)totalamount
from @test-2
group by dateadd(mm,1,dateadd(dd,datepart(dd,dbo.floordate(date))*-1,dbo.floordate(date))),month
March 9, 2009 at 12:13 pm
Thank you,
It does return the required results.
March 9, 2009 at 12:34 pm
CREATE TABLE [dbo].[MYTABLE] (
[MYDATE] [smalldatetime] NULL ,
[MYDEPT] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MYNUMBER] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MYAMOUNT] [int] NULL ,
[MYTOTALAMOUNT] [money] NULL
) ON [PRIMARY]
GO
Thanks
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply