SQL Query - Summarise Data from 2nd table 2

[barnettjacob]

Hi, hoping someone can help me with a query I need to write to summarise the components of a retail transaction where the transaction details are in one table (saleheader) but the details are in the other (saleline).

My aim is a query that, for each transaction (reference1) on the saleheader table, returns the sum of sales for each Division (analysis1code) which is stored in the saleline table.

Reference1 is the link between the two tables.

My attempt (which doesn't come close to working) was:

select

reference1,
branchcode,
(select sum(case when analysis1code = 'baby' then (fcextendednetamount-fcextendedtaxamount) else 0 end) from eee..vwsaleline_gp) as 'H&T',
(select sum(case when analysis1code = 'fashion' then (fcextendednetamount-fcextendedtaxamount) else 0 end) from eee..vwsaleline_gp) as 'Apparel',
(select sum(case when analysis1code = 'giftware' then (fcextendednetamount-fcextendedtaxamount) else 0 end) from eee..vwsaleline_gp) as 'T&G'

from eee..vwsaleheader_gp

where saledate >= '12/01/11'

If anybody could help me out I would be eternally grateful!
Regards
Jacob

 

[r937]

SELECT hd.reference1
     , hd.branchcode
     , SUM(CASE WHEN ln.analysis1code = 'baby' 
                THEN ln.fcextendednetamount - ln.fcextendedtaxamount
                ELSE 0 END) AS 'H&T'
     , SUM(CASE WHEN analysis1code = 'fashion' 
                THEN ln.fcextendednetamount - ln.fcextendedtaxamount
                ELSE 0 END) AS 'Apparel'
     , SUM(CASE WHEN analysis1code = 'giftware'  
                THEN ln.fcextendednetamount - ln.fcextendedtaxamount
                ELSE 0 END) AS 'T&G'
  FROM eee..vwsaleheader_gp AS hd
INNER
  JOIN eee..vwsaleline_gp AS ln
 WHERE hd.saledate >= '12/01/11'

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[barnettjacob]

Cheers Rudi - I was trying to over complicate things!

Just had to add a couple of lines to get the thing to run (as below).

Thanks a million
Jacob

SELECT 

hd.reference1,
hd.branchcode,
SUM(CASE WHEN ln.analysis1code in ('mc h&t','baby','home')THEN ln.fcextendednetamount - ln.fcextendedtaxamount ELSE 0 END) AS 'H&T',
SUM(CASE WHEN analysis1code in ('mcclothing', 'fashion' ) THEN ln.fcextendednetamount - ln.fcextendedtaxamount ELSE 0 END) AS 'Apparel'
SUM(CASE WHEN analysis1code in ('elc', 'giftware')        THEN ln.fcextendednetamount - ln.fcextendedtaxamount ELSE 0 END) AS 'T&G'

FROM eee..vwsaleheader_gp AS hd
INNER
JOIN eee..vwsaleline_gp AS ln on hd.reference1 = ln.reference1
WHERE hd.saledate >= '12/29/11' and hd.saledate < '12/30/11'

group by hd.reference1,hd.branchcode

 

[r937]

glad it worked for you

fyi i don't think those date formats are compatible with ANSI SQL but then your database obviously knows how to understand them anyway

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[barnettjacob]

Rudi,

You wrote an article some years ago on an SQL for returning the top 10 ranked here:

http://www.evolt.org/node/131

I'm keen to do something similar but aggregating the sales by product to give me the top 200 sellers. I modified you code (below) in a way that seemed intuitive however it won't run citing an objection to the aggregation! FYI I'm using SQL Server 2008.

Any guidance would be most appreciated.
Regards
Jacob

SELECT

productcode,
sum(fcextendednetamount)


from eee..vwsaleline_gp s

WHERE 200 > (
SELECT COUNT(*)
FROM eee..vwsaleline_gp
WHERE sum(fcextendednetamount) > sum(s.fcextendednetamount))

 

[r937]

i would use windowing functions to do "top" logic in sql server now

that evolt article is getting pretty long in the tooth, eh

WITH product_net_amt AS (
SELECT productcode
     , sum_amt
     , ROW_NUMBER() OVER 
         ( ORDER BY sum_amt DESC ) AS row
  FROM ( SELECT productcode
              , SUM(fcextendednetamount) AS sum_amt
           FROM eee..vwsaleline_gp 
         GROUP
             BY productcode ) AS d
) 
SELECT * 
  FROM product_net_amt 
 WHERE row <= 200

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon

 

[barnettjacob]

Thats superb! Thanks Rudy!

 

[PHV]

And what about this old legacy SQL ?

SELECT productcode,sum_amt
FROM (
  SELECT productcode,SUM(fcextendednetamount) AS sum_amt
    FROM eee..vwsaleline_gp GROUP BY productcode
) S
WHERE 200 > (
  SELECT COUNT(*) FROM (
    SELECT productcode,SUM(fcextendednetamount)
      FROM eee..vwsaleline_gp GROUP BY productcode
    HAVING SUM(fcextendednetamount) > S.sum_amt) D
)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[r937]

looks ok to me, but i can't test it ;-)

nice job phv

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon