compare 2 data - 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)

compare 2 data

Author

Topic

papershop
Starting Member

27 Posts

Posted - 2011-08-16 : 03:59:59

Dear All

i want to know how to compare data in sql

excample

i have table sales, and from that table i have 2 data

1. sales of january
2. sales of february

how to compare that 2 data so i want to know if the sales is in february increase or decrease?

sales january sales february ....

200000 250000 25%

@papershop

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-16 : 04:04:47

is this related to the previous pivot query ?

you already have the month in column, you can just do a subtraction and find the percentage


select ([2] - [1]) * 100 / [1]

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

papershop
Starting Member

27 Posts

Posted - 2011-08-16 : 04:48:37

Dear KH

what if i want to use 2 pivot table, how to compare it?

@papershop

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-16 : 04:58:00

example ?

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

papershop
Starting Member

27 Posts

Posted - 2011-08-16 : 05:37:46

pivot 1

Product jan Feb Mar Q1
CLA 73317114 90430808 100737041 264484963
CLA 515792529 598023640 675107795 1788923964
FLA 338355550 317834370 375733079 1031922999
SOF 111701271 102083344 85799527 299584142
SOF 398396294 303032842 376225772 1077654908

pivot 2
Product Apr Mei Jun Q2
CLA 1721476112 1597995290 1511631098 4831102500
CLA 193049420 170871059 153738518 517658997
FLA 790845206 784176449 775543978 2350565633
SOF 1563477004 1690718485 1801549486 5055744975
SOF 270777386 360663759 297491175 928932320

from that 2 pivot i want to 2 the diffrent between Q1 and Q2 how much is in percentage?

@papershop

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-16 : 05:44:28

INNER JOIN the 2


SELECT *
FROM
(
    < 1st pivot query >
) pv1
INNER JOIN
(
    < 2nd pivot query >
) pv2   ON pv1.Product = pv2.Product

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

papershop
Starting Member

27 Posts

Posted - 2011-08-16 : 05:53:07

i use store procedure to inner join but it still wrong

this is my sintax

@year int,
@awal int,
@akhir int,
@awal2 int,
@akhir2 int

as
select * from (
select line, familyname, isnull(cast([1] as bigint),0) as Jan ,isnull(cast([2] as bigint),0) as Feb ,isnull(cast([3] as bigint),0) as Mar,
isnull(cast([1] as bigint),0)+isnull(cast([2]as bigint),0)+isnull(cast([3] as bigint),0) as Q1,
isnull(cast([4] as bigint),0) as Apr, isnull(cast([5] as bigint),0) as Mei, isnull(cast([6] as bigint),0) as Jun,
isnull(cast([4] as bigint),0)+isnull(cast([5] as bigint),0)+isnull(cast([6] as bigint),0) as Q2,
isnull(cast([7] as bigint),0) as Jul,isnull(cast(

as bigint),0) as Aug,isnull(cast([9] as bigint),0) as Sep,
isnull(cast([7] as bigint),0)+isnull(cast(

as bigint),0)+isnull(cast([9] as bigint),0) as Q3,
isnull(cast([10] as bigint),0) as Okt, isnull(cast([11] as bigint),0) as Nov, isnull(cast([12] as bigint),0) as Dec,
isnull(cast([10] as bigint),0)+isnull(cast([11] as bigint),0)+isnull(cast([12] as bigint),0) as Q4
from (
select line, _month, familyname, valuenet from dbo.v_fact_sales
WHERE (DATEPART(m, inv_date) >=@awal) AND (DATEPART(m, inv_date) <=@akhir) and (_Year = @year))up
pivot (sum(valuenet) for _month in ([1],[2],[3],[4],[5],[6],[7],

,[9],[10],[11],[12])) as pvt1
inner join
(select line, familyname, isnull(cast([1] as bigint),0) as Jan ,isnull(cast([2] as bigint),0) as Feb ,isnull(cast([3] as bigint),0) as Mar,
isnull(cast([1] as bigint),0)+isnull(cast([2]as bigint),0)+isnull(cast([3] as bigint),0) as Q1,
isnull(cast([4] as bigint),0) as Apr, isnull(cast([5] as bigint),0) as Mei, isnull(cast([6] as bigint),0) as Jun,
isnull(cast([4] as bigint),0)+isnull(cast([5] as bigint),0)+isnull(cast([6] as bigint),0) as Q2,
isnull(cast([7] as bigint),0) as Jul,isnull(cast(

as bigint),0) as Aug,isnull(cast([9] as bigint),0) as Sep,
isnull(cast([7] as bigint),0)+isnull(cast(

as bigint),0)+isnull(cast([9] as bigint),0) as Q3,
isnull(cast([10] as bigint),0) as Okt, isnull(cast([11] as bigint),0) as Nov, isnull(cast([12] as bigint),0) as Dec,
isnull(cast([10] as bigint),0)+isnull(cast([11] as bigint),0)+isnull(cast([12] as bigint),0) as Q4
from (
select line, _month, familyname, valuenet from dbo.v_fact_sales
WHERE (DATEPART(m, inv_date) >=@awal2) AND (DATEPART(m, inv_date) <=@akhir2) and (_Year = @year))up
pivot (sum(valuenet) for _month in ([1],[2],[3],[4],[5],[6],[7],

,[9],[10],[11],[12]) )as pvt2
on pvt1.line = pvt2.line

@papershop

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-16 : 06:02:34

are you trying to compare Jan of pivot query 1 with Jan of pivot query 2 ?

It doesn't make sense to me as both of the query are for the same year (@year)

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

papershop
Starting Member

27 Posts

Posted - 2011-08-16 : 06:06:44

no i mean i want to compare the quarter in a year
example... quarter1(jan, feb and march) with quarter2(apr, mei, june)

@papershop

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-16 : 06:08:09

quote:
Originally posted by papershop

no i mean i want to compare the quarter in a year
example... quarter1(jan, feb and march) with quarter2(apr, mei, june)

@papershop

then why do you need 2 pivot for that ? A single pivot query will do

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-16 : 06:26:58

[code]
select line, familyname,
Jan, Feb, Mar, Q1,
Apr, Mei, Jun, Q2,
Jul, Aug, Sep, Q3,
Okt, Nov, Dec, Q4,
(Q2 - Q1) * 100.0 / Q1 as [Q2-Q1%]
from
(
select line,
familyname,
isnull(cast([1] as bigint),0) as Jan ,
isnull(cast([2] as bigint),0) as Feb ,
isnull(cast([3] as bigint),0) as Mar,
isnull(cast([1] as bigint),0)+isnull(cast([2]as bigint),0)+isnull(cast([3] as bigint),0) as Q1,
isnull(cast([4] as bigint),0) as Apr,
isnull(cast([5] as bigint),0) as Mei,
isnull(cast([6] as bigint),0) as Jun,
isnull(cast([4] as bigint),0)+isnull(cast([5] as bigint),0)+isnull(cast([6] as bigint),0) as Q2,
isnull(cast([7] as bigint),0) as Jul,
isnull(cast(

as bigint),0) as Aug,
isnull(cast([9] as bigint),0) as Sep,
isnull(cast([7] as bigint),0)+isnull(cast(

as bigint),0)+isnull(cast([9] as bigint),0) as Q3,
isnull(cast([10] as bigint),0) as Okt,
isnull(cast([11] as bigint),0) as Nov,
isnull(cast([12] as bigint),0) as Dec,
isnull(cast([10] as bigint),0)+isnull(cast([11] as bigint),0)+isnull(cast([12] as bigint),0) as Q4
from
(
select line, _month, familyname, valuenet
from dbo.v_fact_sales
WHERE (DATEPART(m, inv_date) >=@awal)
AND (DATEPART(m, inv_date) <=@akhir)
and (_Year = @year)
) up
pivot
(
sum(valuenet)
for _month in ([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12])
) as pvt
) a
[/code]

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-16 : 06:29:53

do you have index on the _month column why not just use the _month column ?


select 	line, _month, familyname, valuenet 
from 	dbo.v_fact_sales
WHERE 	(DATEPART(m, inv_date) >=@awal) 
AND 	(DATEPART(m, inv_date) <=@akhir) 
WHERE   _month between @awal and  @akhir
and 	(_Year = @year)

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources