count 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 2008 Forums

Transact-SQL (2008)

count sql

Author

Topic

papershop
Starting Member

27 Posts

Posted - 2011-08-17 : 23:29:27

Dear ALL

i have a sales table that fill with sales product every year...
if i want to make a analisys sales that combine sales between 2010 and 2011 what kind query i should use?

this is the excample tha data that i want

2010 2011 Analisys
Product Q1 Jan Feb Mar Q1 Q1'2010 vs Q1'2011
SOL 277138 109240 111557 86263 307061 11%
COR 968980 644832 639697 781575 2066105 113%
AML 5665807 55711 116916 86719 259347 -95%
PLA 7637478 341054 323098 364740 1028893 -87%

@papershop

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-17 : 23:47:32

[code]
-- The PIVOT way
select line, familyname,
[001] as JanY1,
[002] as FebY1,
. . .
[101] as JanY2,
[102] as FebY2,
. . .
from (
select line, familyname, _date = (_Year - @tahun_awal) * 100 + _month, valuenet
from dbo.v_fact_sales
where _Year >= @tahun_awal
and _Year <= @tahun_akhir
) v
pivot
(
sum(valuenet)
for _date in ( [001], [002], [003], [004], . . .
[101], [102], [103], [104], . . . )
) p

-- The CASE .. WHEN way
select line, familyname,
sum(case when _Year = 2010 and _month = 1 then valuenet else 0 end) as Jan2010,
sum(case when _Year = 2010 and _month = 2 then valuenet else 0 end) as Jan2010,
. . .
sum(case when _Year = 2011 and _month = 1 then valuenet else 0 end) as Jan2011,
sum(case when _Year = 2011 and _month = 2 then valuenet else 0 end) as Jan2010,
. . .
from dbo.v_fact_sales
where _Year >= @tahun_awal
and _Year <= @tahun_akhir
group by line, familyname
[/code]

KH
[spoiler]Time is always against us[/spoiler]

papershop
Starting Member

27 Posts

Posted - 2011-08-17 : 23:51:29

thanks KH

@papershop

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-18 : 04:19:47

will your year of consideration be static ie always 2011 and 2010?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources