Query that accumulate detail values

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

General SQL Server Forums

New to SQL Server Programming

Query that accumulate detail values

Author

Topic

jggtz
Starting Member

32 Posts

Posted - 2008-05-24 : 20:54:51

How would be a query that read detailed sales from several dates and accumulated them in a table that has months as fields

MSSQL 2005

Table : DETAIL
Customer_Date___________Value
1________01-Jan-2008____1,234.00
2________01-Feb-2008____3,456.00
3________01-Mar-2008______500.00
4________01-Apr-2008____1,567.00
1________01-Apr-2008____2,123.00

Table : TOTAL
Year__Customer_Jan______Feb______Mar_____Apr ...
2008__1________1,234.00_0________0_______2,123.00
2008__2________0________3,456.00_0_______0
2008__3________0________0________500.00__0
2008__4________0________0________0_______1,567.00

Thanks for your help

JG

pravin14u
Posting Yak Master

246 Posts

Posted - 2008-05-25 : 00:50:48

use PIVOT if you are using SQL 2005

Prakash.P
The secret to creativity is knowing how to hide your sources!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-25 : 00:59:44

SQL 2000

INSERT INTO TOTAL
SELECT YEAR(Date),
Customer,
SUM(CASE WHEN MONTH(Date)=1 THEN Value ELSE 0 END ) AS Jan,
SUM(CASE WHEN MONTH(Date)=2 THEN Value ELSE 0 END ) AS Feb,
SUM(CASE WHEN MONTH(Date)=3 THEN Value ELSE 0 END ) AS Mar,
SUM(CASE WHEN MONTH(Date)=4 THEN Value ELSE 0 END ) AS Apr,
SUM(CASE WHEN MONTH(Date)=5 THEN Value ELSE 0 END ) AS May,
SUM(CASE WHEN MONTH(Date)=6 THEN Value ELSE 0 END ) AS June,
....
SUM(CASE WHEN MONTH(Date)=12 THEN Value ELSE 0 END ) AS Dec,
FROM DETAIL
GROUP BY YEAR(Date),Customer

SQL 2005

SELECT Year,Customer,SUM([1]),SUM([2]),SUM([3]),SUM([4]),SUM([5]),SUM([6]),SUM([7]),SUM(),SUM([9]),SUM([10]),SUM([11]),SUM([12])
FROM
(SELECT YEAR(Date) AS [Year],MONTH(Date) AS [Month],Date,Customer,[Value] 
FROM DETAIL)m
PIVOT(SUM(Value) FOR [Month] IN  ([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12]))p
GROUP BY Year,Customer

EDIT: Missed FROM & ,

jggtz
Starting Member

32 Posts

Posted - 2008-05-25 : 01:40:11

Thank You very much
I'm learning in thinking sql after many years of traditional ADO methods

ranganath
Posting Yak Master

209 Posts

Posted - 2008-05-26 : 01:01:18

Hi

Try with this

Declare @Temp Table (id int identity(1,1), CustomerId int, Date varchar(100), Val Varchar(100))
Insert into @Temp
Select 1,'01-Jan-2008','1,234.00' Union All
Select 2,'01-Feb-2008','3,456.00 ' Union All
Select 3,'01-Mar-2008','500.00' Union All
Select 4,'01-Apr-2008','1,567.00' Union All
Select 1,'01-Apr-2008','2,123.00' Union All
Select 4,'01-May-2008','2,123.00' Union All
Select 5,'01-June-2008','2,123.00' Union All
Select 6,'01-june-2008','2,000.00'

Select Id, CustomerId, Left(Replace(Date,Left(date,charIndex('-' , date)),''),charIndex('-' , Replace(date,Left(date,charIndex('-' , date)),''))-1) as Mon , Substring(Date, 8,4) as Yr, Val into #Temp
From @Temp

--Select * From #Temp

Declare @Sql Varchar(8000)
Set @sql = ''
DEclare @str Varchar(8000)
Set @str = ''

Select @Sql = @Sql + ', Min (Case when mon = ''' + mon + ''' then Val end ) AS "'+ Mon +'"'
From (Select distinct Mon From #Temp )A

--Select @Sql

Select @str = 'Select CustomerId,yr,val '+@Sql +' From #Temp Group By CustomerId,yr,val '
Exec(@str )

Drop Table #Temp

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-05-26 : 02:21:39

quote:
Originally posted by ranganath

Hi

Try with this

Declare @Temp Table (CustomerId int, Date varchar(100), Val Varchar(100))
Insert into @Temp
Select 1,'01-Jan-2008','1,234.00' Union All
Select 2,'01-Feb-2008','3,456.00 ' Union All
Select 3,'01-Mar-2008','500.00' Union All
Select 4,'01-Apr-2008','1,567.00' Union All
Select 1,'01-Apr-2008','2,123.00'

Select CustomerId, Substring(Date, 4,3) as Mon , Substring(Date, 8,4) as Yr, Val into #Temp
From @Temp

Select * From #Temp

Declare @Sql Varchar(8000)
Set @sql = ''
DEclare @str Varchar(8000)
Set @str = ''

Select @Sql = @Sql + ', Min (Case when Cast(mon as varchar(100)) = ''' + Cast(mon as varchar(100)) + ''' then Val end ) AS "'+ Mon +'"'
From (Select distinct Mon From #Temp)A
Select @Sql

Select @str = 'Select CustomerId,yr,val '+@Sql +' From #Temp Group By CustomerId,yr,val '
Exec(@str )

Drop Table #Temp

Do you really need to use dynamic sql here? the columns are static always from Jan to Dec.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources