SQL 2012 Query Syntax
I have the following query. The way it is written currently I have a column for each Customer. I was wondering if perhaps there is an easier way to write this. Basically so that I have two columns Customer and Net open Sales for 2017?
SELECT DISTINCT
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER IN ( 'AMA0010', 'AMA9010', 'AMA9011' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS Amazon2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER IN ( 'BJS0010', 'BJS9010' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS BJ2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER IN ( 'MOD0010', 'MOD9010' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS Burlington2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER IN ( 'COS0010', 'COS0015', 'COS0020',
'COS0025', 'COS0030', 'COS0035' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS Costco2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER IN ( 'DIL0010', 'DIL0015', 'DIL9010',
'DIL9015' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS Dillards2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER IN ( 'MAD0010', 'SEV0010', 'DEC0010' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS EcomDirect2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER IN ( 'JCP0001', 'JCP0002', 'JCP0003',
'JCP9001', 'JCP9002', 'JCP9010' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS JCPenny2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER IN ( 'KOH0010', 'KOH0015' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS KOHLS2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER IN ( 'MAC0020', 'MAC0025', 'MAC0035',
'MAC0040', 'MAC0050', 'MAC0060',
'MAC0070', 'MAC0075', 'MAC0076',
'MAC0080', 'MAC0085', 'MAC0090',
'MAC0095', 'MAC0096', 'MAC1095',
'MAC2095', 'MAC2595', 'MAC5095',
'MAC8095', 'MAC9020', 'MAC9090',
'MAC9095' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS Macys2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER IN ( 'MAR9100' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS MarMaxx2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER IN ( 'MEI0010', 'MEI0015', 'MEI9010' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS Meijer2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER IN ( 'NOR0025', 'NOR0030', 'NOR0035',
'NOR0040', 'NOR0045' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS Nordstrom2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER IN ( 'ROS0040' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS Ross2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER IN ( 'SHO0020' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS Shopko2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER IN ( 'SRI0010', 'SRI0015', 'SRI9010' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS SRI2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER IN ( 'WAL0010', 'WAL0015' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS WALMART2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER IN ( 'ZUL0010', 'ZUL0020', 'ZUL9010',
'ZUL9011' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS Zuilly2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER NOT IN ( 'AMA0010', 'AMA9010', 'AMA9011',
'BJS0010', 'BJS9010', 'MOD0010',
'MOD9010', 'COS0010', 'COS0015',
'COS0020', 'COS0025', 'COS0030',
'COS0035', 'DIL0010', 'DIL0015',
'DIL9010', 'DIL9015', 'MAD0010',
'SEV0010', 'DEC0010', 'JCP0001',
'JCP0002', 'JCP0003', 'JCP9001',
'JCP9002', 'JCP9010', 'KOH0010',
'KOH0015', 'MAC0020', 'MAC0025',
'MAC0035', 'MAC0040', 'MAC0050',
'MAC0060', 'MAC0070', 'MAC0075',
'MAC0076', 'MAC0080', 'MAC0085',
'MAC0090', 'MAC0095', 'MAC0096',
'MAC1095', 'MAC2095', 'MAC2595',
'MAC5095', 'MAC8095', 'MAC9020',
'MAC9090', 'MAC9095', 'MAR9100',
'MEI0010', 'MEI0015', 'MEI9010',
'NOR0025', 'NOR0030', 'NOR0035',
'NOR0040', 'NOR0045', 'ROS0040',
'SHO0020', 'SRI0010', 'SRI0015',
'SRI9010', 'WAL0010', 'WAL0015',
'ZUL0010', 'ZUL0020', 'ZUL9010',
'ZUL9011', 'ZUL0010', 'ZUL0020',
'ZUL9010', 'ZUL9011' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS Other2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( ( CUSTOMER NOT IN ( 'WAL0010', 'WAL0015' )) )
AND ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS SubTotal2017 ,
( SELECT SUM(price * total_qty)
FROM [KLL Open Orders - Header & Detail]
WHERE ( (division = 'KLL') )
AND ( (end_date > = DATEADD(DD, -30, GETDATE())) )
AND ( ( ORD_STATUS = 'O' ) )
) AS Total2017
FROM [dataKLL].[dbo].[KLL Open Orders - Header & Detail]SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
You can use a sub-query where you'll get the total per customer and then for each set of customers, built the subtotals that you want:
SELECT
SUM(CASE
WHEN CUSTOMER IN ('AMA0010', 'AMA9010', 'AMA9011') THEN T.Total
ELSE 0
END) AS Amazon2017,
SUM(CASE
WHEN CUSTOMER IN ('BJS0010', 'BJS9010') THEN T.Total
ELSE 0
END) AS BJ2017,
(...),
SUM(CASE
WHEN CUSTOMER NOT IN ('WAL0010', 'WAL0015') THEN T.Total
ELSE 0
END) AS SubTotal2017 ,
SUM(T.Total) AS Total2017
FROM (SELECT CUSTOMER, SUM(price * total_qty) Total
FROM [KLL Open Orders - Header & Detail]
GROUP BY CUSTOMER) AS T
this query does not make sense to me!
it returns same results many times!!!
my suggestion
remove distinct and FROM part
and delete
it returns same results many times!!!
my suggestion
remove distinct and FROM part
SELECT DISTINCT
>>>
SELECTand delete
FROM [dataKLL].[dbo].[KLL Open Orders - Header & Detail]ASKER
Sorry for my delay. Thank you both.
do you have a grouping table in which map the CUSTOMER with the GROUPING (like Amazon, BJ, Burlington, etc) you want? is this grouping based on city or country, etc?