Compare between record and record

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

Compare between record and record

Author

Topic

pamyral_279
Posting Yak Master

143 Posts

Posted - 2012-01-12 : 07:43:14

Hi everybody,
I have problem when implement comparing record in database.My problems are described as :
Structure of database:

Table1
ID-----Week----------Cat1_View-----Cat2_View--------Cat3_View

1------22/02/2011-------5300---------32346----------163466
2------15/02/2011-------8300---------6386-----------14576
3------8/02/2011--------500----------4546-----------484848
4------1/02/2011--------3600---------35346----------6636

When i compare any two records in table1 , such as : week 15/02/2011 and 22/02/2011 .I want to have a table format as :

--------------15/02/2011 22/02/2011)---Result1-------------------Result2

Cat1_view----8300---------5300--------(Result:5300-8300=-3000)----(5300-8300/8300=36.14%)
Cat2_view----6386---------32346-------(Result:32346-6386=25960)---(32346-6386/6386=406.51%)
Cat3_view----14576--------163466------148890 ---------------------1021%

Any help would be appreciated on this format !
Thank you very much !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-12 : 12:32:53

you need to first UNPIVOT it and then apply pivot on Week field

something like


SELECT Cat,[15/02/2011],[22/02/2011]
FROM
(
SELECT Week,Cat,Val
FROM Table
UNPIVOT (Val FOR Cat IN (Cat1_View,Cat2_View,Cat3_View))u
WHERE Week IN ('15/02/2011','22/02/2011')
)m
PIVOT(SUM(Val) FOR Week IN ([15/02/2011],[22/02/2011]))p

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

pamyral_279
Posting Yak Master

143 Posts

Posted - 2012-01-12 : 20:47:57

Can you show me the complete sql statement ?
So, Can you change [15/02/2011] and [22/02/2011] into variables, because I want it become as input parameter !
Thank you very much!

pamyral_279
Posting Yak Master

143 Posts

Posted - 2012-01-13 : 02:05:26

Anyone help me ?
Thank you !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 03:14:34

quote:
Originally posted by pamyral_279

Can you show me the complete sql statement ?
So, Can you change [15/02/2011] and [22/02/2011] into variables, because I want it become as input parameter !
Thank you very much!

if you want to make it dynamic use

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 03:15:34

if you just want dates to be variable driven use


SELECT Cat,[15/02/2011],[22/02/2011]
FROM
(
SELECT Week,Cat,Val
FROM Table
UNPIVOT (Val FOR Cat IN (Cat1_View,Cat2_View,Cat3_View))u
WHERE Week IN (@Date1,@date2)
)m
PIVOT(SUM(Val) FOR Week IN ([15/02/2011],[22/02/2011]))p

@Date1 and @Date2 are variable through which you pass date values

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

pamyral_279
Posting Yak Master

143 Posts

Posted - 2012-01-13 : 04:48:40

Thank visakh16 very much,but my result when i run your code :
Cat1_View 8300 5300
Cat2_View 6386 32346
Cat3_View 14576 163466

That is not my desire result !
Can you edit your code ? I'm quite novel !

I want to have procedure like to :
create procedure CompareRecord
(
@Date1 Date,
@Date2 Date
)As
...

Thank you again !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 04:52:29

i thought rest was straightforward for you


SELECT Cat,[15/02/2011],[22/02/2011],[22/02/2011]-[15/02/2011] AS Result1,
([22/02/2011]-[15/02/2011])*100.0/[15/02/2011] AS Result2
FROM
(
SELECT Week,Cat,Val
FROM Table
UNPIVOT (Val FOR Cat IN (Cat1_View,Cat2_View,Cat3_View))u
WHERE Week IN ('15/02/2011','22/02/2011')
)m
PIVOT(SUM(Val) FOR Week IN ([15/02/2011],[22/02/2011]))p

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

pamyral_279
Posting Yak Master

143 Posts

Posted - 2012-01-13 : 06:44:50

Hi visakh16, you help me a alot.
But I can not fix value 15/02/2011 and 22/02/2011 as your code.
I want to change it @date1 and @date2.
Two values will be inputed from interface of users !
Have you other idea ?

Thank you !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-13 : 09:44:54

quote:
Originally posted by pamyral_279

Hi visakh16, you help me a alot.
But I can not fix value 15/02/2011 and 22/02/2011 as your code.
I want to change it @date1 and @date2.
Two values will be inputed from interface of users !
Have you other idea ?

Thank you !

then please follow the logic in link posted

ie

http://beyondrelational.com/blogs/madhivanan/archive/2008/08/27/dynamic-pivot-in-sql-server-2005.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources