SQL 2012 Query Syntax

mburk1968
mburk1968 used Ask the Experts™
on
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]

Open in new window

Current_Output.JPG
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ryan ChongSoftware Team Lead

Commented:
can you show the fields in [KLL Open Orders - Header & Detail] ?

Basically so that I have two columns Customer and Net open Sales for 2017?

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?
Máté FarkasDatabase Developer and Administrator
Commented:
You can do it this way. The code is a bit simpler:
SELECT	SUM(CASE WHEN CUSTOMER IN ('AMA0010', 'AMA9010', 'AMA9011') THEN price * total_qty ELSE 0 END) AS Amazon2017,
		SUM(CASE WHEN CUSTOMER IN ('BJS0010', 'BJS9010') THEN price * total_qty ELSE 0 END) AS BJ2017,
		SUM(CASE WHEN CUSTOMER IN ('MOD0010', 'MOD9010') THEN price * total_qty ELSE 0 END) AS Burlington2017,
		SUM(CASE WHEN CUSTOMER IN ('COS0010', 'COS0015', 'COS0020', 'COS0025', 'COS0030', 'COS0035') THEN price * total_qty ELSE 0 END) AS Costco2017,
		SUM(CASE WHEN CUSTOMER IN ('COS0010', 'COS0015', 'COS0020', 'COS0025', 'COS0030', 'COS0035') THEN price * total_qty ELSE 0 END) AS Costco2017
		...
FROM [KLL Open Orders - Header & Detail]
WHERE division = 'KLL'
and end_date > = DATEADD(DD, -30, GETDATE())
AND ORD_STATUS = 'O'

Open in new window


But I recommend to create a new mapping table with 2 columns:
CREATE TABLE CustomerGroups(
        GroupName AS varchar(100) NOT NULL,
        CUSTORMER AS varchar(100) NOT NULL
)

Open in new window

and in this table you can enlist all the groups and customers.
Finally you can create a simply PIVOT query:
SELECT [Amazon2017], [BJ2017], [Burlington2017], [Costco2017] ... etc
FROM (
	SELECT hd.price * hd.total_qty AS TotalCost, cg.GroupName
	FROM [KLL Open Orders - Header & Detail] hd
	INNER JOIN CustomerGroups cg on hd.CUSTOMER = cg.CUSTOMER
	WHERE hd.division = 'KLL'
	AND hd.end_date > = DATEADD(DD, -30, GETDATE())
	AND hd.ORD_STATUS = 'O'
) t
PIVOT (SUM(TotalCost) FOR GroupName IN ([Amazon2017], [BJ2017], [Burlington2017], [Costco2017])) P

Open in new window

Senior Developer
Commented:
First of all: Does this query makes sense? Cause there is no correlation between the sub-queries and the outer queries...

Then you semm to filter for the same predicates in all sub-queries, thus use at least a CTE:

WITH Filtered
AS ( SELECT CUSTOMER ,
            price ,
            total_qty
     FROM   [KLL Open Orders - Header & Detailx]
     WHERE  division = 'KLL'
            AND end_date >= DATEADD(DD, -30, GETDATE())
            AND ORD_STATUS = 'O'
   )
	SELECT (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   WHERE  CUSTOMER IN ( 'AMA0010', 'AMA9010', 'AMA9011' )
		   ) AS Amazon2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   WHERE  CUSTOMER IN ( 'BJS0010', 'BJS9010' )
		   ) AS BJ2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   WHERE  CUSTOMER IN ( 'MOD0010', 'MOD9010' )
		   ) AS Burlington2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   WHERE  CUSTOMER IN ( 'COS0010', 'COS0015', 'COS0020', 'COS0025', 'COS0030', 'COS0035' )
		   ) AS Costco2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   WHERE  CUSTOMER IN ( 'DIL0010', 'DIL0015', 'DIL9010', 'DIL9015' )
		   ) AS Dillards2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   WHERE  CUSTOMER IN ( 'MAD0010', 'SEV0010', 'DEC0010' )
		   ) AS EcomDirect2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   WHERE  CUSTOMER IN ( 'JCP0001', 'JCP0002', 'JCP0003', 'JCP9001', 'JCP9002', 'JCP9010' )
		   ) AS JCPenny2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   WHERE  CUSTOMER IN ( 'KOH0010', 'KOH0015' )
		   ) AS KOHLS2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   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'
								  )
		   ) AS Macys2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   WHERE  CUSTOMER IN ( 'MAR9100' )
		   ) AS MarMaxx2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   WHERE  CUSTOMER IN ( 'MEI0010', 'MEI0015', 'MEI9010' )
		   ) AS Meijer2017 ,
           (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   WHERE  CUSTOMER IN ( 'NOR0025', 'NOR0030', 'NOR0035', 'NOR0040', 'NOR0045' )
		   ) AS Nordstrom2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   WHERE  CUSTOMER IN ( 'ROS0040' )
		   ) AS Ross2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   WHERE  CUSTOMER IN ( 'SHO0020' )
		   ) AS Shopko2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   WHERE  CUSTOMER IN ( 'SRI0010', 'SRI0015', 'SRI9010' )
		   ) AS SRI2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   WHERE  CUSTOMER IN ( 'WAL0010', 'WAL0015' )
		   ) AS WALMART2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   WHERE  CUSTOMER IN ( 'ZUL0010', 'ZUL0020', 'ZUL9010', 'ZUL9011' )
		   ) AS Zuilly2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   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'
									  )
		   ) AS Other2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
			   WHERE  CUSTOMER NOT IN ( 'WAL0010', 'WAL0015' )
		   ) AS SubTotal2017 ,
		   (   SELECT SUM(price * total_qty)
			   FROM   Filtered
		   ) AS Total2017;

Open in new window


As long as you need this pivoted result, there is no other choice. Otherwise you would use a grouping table. E.g.

WITH Filtered
AS ( SELECT CUSTOMER ,
            price ,
            total_qty
     FROM   [KLL Open Orders - Header & Detailx]
     WHERE  division = 'KLL'
            AND end_date >= DATEADD(DD, -30, GETDATE())
            AND ORD_STATUS = 'O'
   )
SELECT (   SELECT SUM(price * total_qty)
           FROM   Filtered
           WHERE  CUSTOMER IN ( 'AMA0010', 'AMA9010', 'AMA9011' )
       ) AS Amazon2017 ,
       (   SELECT SUM(price * total_qty)
           FROM   Filtered
           WHERE  CUSTOMER IN ( 'BJS0010', 'BJS9010' )
       ) AS BJ2017;

-- vs.
DECLARE @Groups TABLE
    (
        GroupName NVARCHAR(255) NOT NULL ,
        Customer NVARCHAR(255) NOT NULL
    );

INSERT INTO @Groups (   GroupName ,
                        Customer
                    )
VALUES ( N'Amazon2017', N'AMA0010' ) ,
    ( N'Amazon2017', N'AMA9010' ) ,
    ( N'Amazon2017', N'AMA9011' ) ,
    ( N'BJ2017', N'BJS0010' ) ,
    ( N'BJ2017', N'BJS9010' );

WITH Filtered
AS ( SELECT CUSTOMER ,
            price ,
            total_qty
     FROM   [KLL Open Orders - Header & Detailx]
     WHERE  division = 'KLL'
            AND end_date >= DATEADD(DD, -30, GETDATE())
            AND ORD_STATUS = 'O'
   )
SELECT   G.GroupName ,
         SUM(F.price * F.total_qty)
FROM     Filtered F
         INNER JOIN @Groups G ON G.Customer = F.Customer
GROUP BY G.GroupName;

Open in new window

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Vitor MontalvãoIT Engineer
Distinguished Expert 2017

Commented:
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

Open in new window

HainKurtSr. System Analyst

Commented:
this query does not make sense to me!
it returns same results many times!!!

my suggestion

remove distinct and FROM part

SELECT DISTINCT
>>>
SELECT

Open in new window


and delete

FROM    [dataKLL].[dbo].[KLL Open Orders - Header & Detail]

Open in new window

Author

Commented:
Sorry for my delay. Thank you both.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial