need sql help..

Database
Enthusiast

need sql help..

 
4 REPLIES
Enthusiast

Re: need sql help..

hi,

i have sql with union all condition .this sql brings Previous year and last year data.

But i need difference of previous year and last year.

ex: i need difference of Count(distinct CARDNUMBER) as Shoppers - Count(distinct CARDNUMBER) as Shoppers .

Count(distinct CARDNUMBER) as Shoppers   this is coming from second select statement i.e previous year  and

Count(distinct CARDNUMBER) as Shoppers    this is coming from first select statemetn i.e this year.

Please can any one give me correct syntax how to achieve it.

here the orginal sql:

Select YR, Cat, Shoppers, Trans, Price, Units, Cost, Adj

From

(

SELECT                'TY' as YR, CAT_SEGMENT as Cat, Count(distinct CARDNUMBER) as Shoppers, Sum(TRANSCOUNT) as Trans, Sum(EXTENDEDPRICE) as Price, Sum(UNITS) as Units, Sum(EXTENDEDCOST) as Cost,

                                Sum(ADJUSTMENTAMOUNT) as Adj

FROM    xyz

Where

businessdate = '2014-01-21'

and state in ('AZ','CA','WA')

and INVLD_CARDNUM_FLG = 'N'

and TransCount is not Null

and TransCount <> 0

Group By 1, 2

UNION ALL

SELECT                'PY' as YR, PY_CAT_SEGMENT as Cat, Count(distinct CARDNUMBER) as Shoppers, Sum(PY_TRANSCOUNT) as Trans, Sum(PY_EXTENDEDPRICE) as Price, Sum(PY_UNITS) as Units, Sum(PY_COST) as Cost,

                                Sum(PY_ADJUSTMENTAMOUNT) as Adj

FROM    xyz

Where

businessdate = '2014-01-21'

and state  in ('AZ','CA','WA')

and INVLD_CARDNUM_FLG = 'N'

and PY_TRANSCOUNT is not Null

and PY_TRANSCOUNT <> 0

Group By 1, 2

) a

Enthusiast

Re: need sql help..

Select YR, Cat, Shoppers-Shoppers1, Trans, Price, Units, Cost, Adj

from

(

SELECT                'TY' as YR, CAT_SEGMENT as Cat, Count(distinct CARDNUMBER) as Shoppers, Sum(TRANSCOUNT) as Trans, Sum(EXTENDEDPRICE) as Price, Sum(UNITS) as Units, Sum(EXTENDEDCOST) as Cost,

                                Sum(ADJUSTMENTAMOUNT) as Adj

,

SELECT                'PY' as YR, PY_CAT_SEGMENT as Cat, Count(distinct CARDNUMBER) as Shoppers1, Sum(PY_TRANSCOUNT) as Trans, Sum(PY_EXTENDEDPRICE) as Price, Sum(PY_UNITS) as Units, Sum(PY_COST) as Cost,

                                Sum(PY_ADJUSTMENTAMOUNT) as Adj

FROM    xyz

Where

businessdate = '2014-01-21'

and state  in ('AZ','CA','WA')

and INVLD_CARDNUM_FLG = 'N'

and PY_TRANSCOUNT is not Null

and PY_TRANSCOUNT <> 0

Group By 1, 2

FROM    xyz

Where

businessdate = '2014-01-21'

and state in ('AZ','CA','WA')

and INVLD_CARDNUM_FLG = 'N'

and TransCount is not Null

and TransCount <> 0

Group By 1, 2

) a

===

Can you try this. It will give you result as one record and then, you can find the difference in the outer query. I have not tested this, but you can give it a try and check. Also you can check something called as MDIFF - moving difference. This is a olap function that gives difference between values in different records.

Enthusiast

Re: need sql help..

Its not working. throwing errror message."expected soemthing b/w',' and select statment "

there is union between 2 select statement .

Enthusiast

Re: need sql help..

sel a.cat,(a.shoppers-b.shoppers),(a.trans-b.trans),(a.price-b.price),(a.units-b.units),(a.cost-b.cost),(a.adj-b.adj)
from
(SELECT 'TY' as YR, CAT_SEGMENT as Cat, Count(distinct CARDNUMBER) as Shoppers, Sum(TRANSCOUNT) as Trans, Sum(EXTENDEDPRICE) as Price, Sum(UNITS) as Units, Sum(EXTENDEDCOST) as Cost,
Sum(ADJUSTMENTAMOUNT) as Adj
FROM xyz
Where
businessdate = '2014-01-21'
and state in ('AZ','CA','WA')
and INVLD_CARDNUM_FLG = 'N'
and TransCount is not Null
and TransCount <> 0
Group By 1, 2)a
join
( SELECT 'PR' as YR, CAT_SEGMENT as Cat, Count(distinct CARDNUMBER) as Shoppers, Sum(TRANSCOUNT) as Trans, Sum(EXTENDEDPRICE) as Price, Sum(UNITS) as Units, Sum(EXTENDEDCOST) as Cost,
Sum(ADJUSTMENTAMOUNT) as Adj
FROM xyz
Where
businessdate = '2014-01-21'
and state in ('AZ','CA','WA')
and INVLD_CARDNUM_FLG = 'N'
and TransCount is not Null
and TransCount <> 0
Group By 1, 2)b
on
a.cat=b.cat

Category_segment seems to be the only distinguishing factor. The above query  gives the difference for the currentyear and the previous year for all the attributes for any(all)particular category_segments.