Database

turn on suggestions

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

Showing results for

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- 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.

Copyright © 2004-2015 Teradata Corporation. Your use of this Teradata website is governed by the Privacy Policy and the Terms of Use, including your rights to materials on this website, the rights you grant to your submissions to this website, and your responsibilities regarding your conduct on this website.

The Privacy Policy and Terms of Use for this Teradata website changed effective September 8, 2016.