Database

turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-19-2014
03:27 PM

03-19-2014
03:27 PM

4 REPLIES

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-19-2014
03:33 PM

03-19-2014
03:33 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-20-2014
02:03 AM

03-20-2014
02:03 AM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-20-2014
09:34 AM

03-20-2014
09:34 AM

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

there is union between 2 select statement .

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Get Direct Link
- Email to a Friend
- Report Inappropriate Content

03-20-2014
10:23 AM

03-20-2014
10:23 AM

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.