Better way for Subqueries in SQL? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Better way for Subqueries in SQL?

Author

Topic

bjstyl2
Starting Member

12 Posts

Posted - 2015-03-27 : 08:13:37

Any thoughts on how I can make this any more efficient? The SQL works, just trying to see if there is a better way of accomplishing the same result.

select sku.dept, sku.style,
(select count(rec_num) from recline r where r.brand = sku.brand and r.style = sku.style and r.quan > 0 and r.dte >= '1/1/2015' and r.dte <= '1/2/2015') as Date1_Ttl_Transactions,
(select sum(r.quan) from recline r where r.brand = sku.brand and r.style = sku.style and r.quan > 0 and r.dte >= '1/1/2015' and r.dte <= '1/2/2015') as Date1_Ttl_Sls_Units,
(select sum(r.extended) from recline r where r.brand = sku.brand and r.style = sku.style and  r.quan > 0 and r.dte >= '1/1/2015' and r.dte <= '1/2/2015') as Date1_Ttl_Sls_Dollars,
(select sum(r.cost) from recline r where r.brand = sku.brand and r.style = sku.style and  r.quan > 0 and r.dte >= '1/1/2015' and r.dte <= '1/2/2015') as Date1_Sales_Cost,
(select count(rec_num) from recline r where r.brand = sku.brand and r.style = sku.style and r.quan > 1 and r.dte >= '1/1/2015' and r.dte <= '1/2/2015') as Date1_Ttl_MultUnits,
(select count(rec_num) from recline r where r.brand = sku.brand and r.style = sku.style and r.quan > 0 and r.dte >= '2/1/2015' and r.dte <= '2/2/2015') as Date2_Ttl_Transactions,
(select sum(r.quan) from recline r where r.brand = sku.brand and r.style = sku.style and r.quan > 0 and r.dte >= '2/1/2015' and r.dte <= '2/2/2015') as Date2_Ttl_Sls_Units,
(select sum(r.extended) from recline r where r.brand = sku.brand and r.style = sku.style and  r.quan > 0 and r.dte >= '2/1/2015' and r.dte <= '2/2/2015') as Date2_Ttl_Sls_Dollars,
(select sum(r.cost) from recline r where r.brand = sku.brand and r.style = sku.style and  r.quan > 0 and r.dte >= '2/1/2015' and r.dte <= '2/2/2015') as Date2_Sales_Cost,
(select count(rec_num) from recline r where r.brand = sku.brand and r.style = sku.style and r.quan > 1 and r.dte >= '2/1/2015' and r.dte <= '2/2/2015') as Date2_Ttl_MultUnits,
(select count(rec_num) from recline r where r.brand = sku.brand and r.style = sku.style and r.quan > 0 and r.dte >= '3/1/2015' and r.dte <= '3/2/2015') as Date3_Ttl_Transactions,
(select sum(r.quan) from recline r where r.brand = sku.brand and r.style = sku.style and r.quan > 0 and r.dte >= '3/1/2015' and r.dte <= '3/2/2015') as Date3_Ttl_Sls_Units,
(select sum(r.extended) from recline r where r.brand = sku.brand and r.style = sku.style and  r.quan > 0 and r.dte >= '3/1/2015' and r.dte <= '3/2/2015') as Date3_Ttl_Sls_Dollars,
(select sum(r.cost) from recline r where r.brand = sku.brand and r.style = sku.style and  r.quan > 0 and r.dte >= '3/1/2015' and r.dte <= '3/2/2015') as Date3_Sales_Cost,
(select count(rec_num) from recline r where r.brand = sku.brand and r.style = sku.style and r.quan > 1 and r.dte >= '3/1/2015' and r.dte <= '3/2/2015') as Date3_Ttl_MultUnits
from sku left outer join recline r 
on (sku.brand = r.brand and sku.style = r.style)
where sku.style = '1.3 OZ'
group by sku.dept, sku.style

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-27 : 08:26:41

perhaps:


WITH recline
AS (
	SELECT count(rec_num)
	FROM recline r
	WHERE r.brand = sku.brand
		AND r.style = sku.style
		AND r.quan > 1
		AND r.dte >= '3/1/2015'
		AND r.dte <= '3/2/2015'
	)
SELECT count(rec_num) AS Date1_Ttl_Transactions
	,sum(r.quan) AS Date1_Ttl_Sls_Units
	,... etc
FROM sku
LEFT OUTER JOIN recline r ON (
		sku.brand = r.brand
		AND sku.style = r.style
		)
WHERE sku.style = '1.3 OZ'
GROUP BY 
	  sku.dept
	, sku.style

bjstyl2
Starting Member

12 Posts

Posted - 2015-03-27 : 08:35:53

Could I have multiple date ranges as columns though?

So first 4 columns would be date range 1
Next 4 columns would be date range 2, etc.

Thanks for the prompt reply.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-27 : 09:12:58

not sure what you mean, post some sample output

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-27 : 09:55:17

[code]-- SwePeso
SELECT s.Dept,
s.Style,
SUM(CASE WHEN r.quan > 0 AND r.dte >= '1/1/2015' AND r.dte <= '1/2/2015' AND r.recnum IS NOT NULL THEN 1 ELSE 0 END) AS Date1_Ttl_Transactions,
SUM(CASE WHEN r.quan > 0 AND r.dte >= '1/1/2015' AND r.dte <= '1/2/2015' THEN r.quan ELSE 0 END) AS Date1_Ttl_Sls_Units,
SUM(CASE WHEN r.quan > 0 AND r.dte >= '1/1/2015' AND r.dte <= '1/2/2015' THEN r.extended ELSE 0 END) AS Date1_Ttl_Sls_Dollars,
SUM(CASE WHEN r.quan > 0 AND r.dte >= '1/1/2015' AND r.dte <= '1/2/2015' THEN r.cost ELSE 0 END) AS Date1_Sales_Cost,
SUM(CASE WHEN r.quan > 1 AND r.dte >= '1/1/2015' AND r.dte <= '1/2/2015' AND r.recnum IS NOT NULL THEN 1 ELSE 0 END) AS Date1_Ttl_MultUnits,
SUM(CASE WHEN r.quan > 0 AND r.dte >= '2/1/2015' AND r.dte <= '2/2/2015' AND r.recnum IS NOT NULL THEN 1 ELSE 0 END) AS Date2_Ttl_Transactions,
SUM(CASE WHEN r.quan > 0 AND r.dte >= '2/1/2015' AND r.dte <= '2/2/2015' THEN r.quan ELSE 0 END) AS Date2_Ttl_Sls_Units,
SUM(CASE WHEN r.quan > 0 AND r.dte >= '2/1/2015' AND r.dte <= '2/2/2015' THEN r.extended ELSE 0 END) AS Date2_Ttl_Sls_Dollars,
SUM(CASE WHEN r.quan > 0 AND r.dte >= '2/1/2015' AND r.dte <= '2/2/2015' THEN r.cost ELSE 0 END) AS Date2_Sales_Cost,
SUM(CASE WHEN r.quan > 1 AND r.dte >= '2/1/2015' AND r.dte <= '2/2/2015' AND r.recnum IS NOT NULL THEN 1 ELSE 0 END) AS Date2_Ttl_MultUnits,
SUM(CASE WHEN r.quan > 0 AND r.dte >= '3/1/2015' AND r.dte <= '3/2/2015' AND r.recnum IS NOT NULL THEN 1 ELSE 0 END) AS Date3_Ttl_Transactions,
SUM(CASE WHEN r.quan > 0 AND r.dte >= '3/1/2015' AND r.dte <= '3/2/2015' THEN r.quan ELSE 0 END) AS Date3_Ttl_Sls_Units,
SUM(CASE WHEN r.quan > 0 AND r.dte >= '3/1/2015' AND r.dte <= '3/2/2015' THEN r.extended ELSE 0 END) AS Date3_Ttl_Sls_Dollars,
SUM(CASE WHEN r.quan > 0 AND r.dte >= '3/1/2015' AND r.dte <= '3/2/2015' THEN r.cost ELSE 0 END) AS Date3_Sales_Cost,
SUM(CASE WHEN r.quan > 1 AND r.dte >= '3/1/2015' AND r.dte <= '3/2/2015' AND r.rec_num IS NOT NULL THEN 1 ELSE 0 END) AS Date3_Ttl_MultUnits
FROM dbo.sku AS s
LEFT JOIN dbo.Recline AS r ON r.Brand = s.Brand
AND r.Style = s.Style
AND r.quan > 0
AND r.dte >= '1/1/2015'
AND r.dte <= '3/2/2015'
WHERE s.Style = '1.3 OZ'
GROUP BY s.Dept,
s.Style;[/code]

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2015-03-27 : 10:07:05

Another approach

-- SwePeso
SELECT		s.Dept,
		s.Style,
		SUM(ISNULL(r.Date1_Ttl_Transactions, 0)) AS Date1_Ttl_Transactions,
		SUM(ISNULL(r.Date1_Ttl_Sls_Units, 0)) AS Date1_Ttl_Sls_Units,
		SUM(ISNULL(r.Date1_Ttl_Sls_Dollars, 0)) AS Date1_Ttl_Sls_Dollars,
		SUM(ISNULL(r.Date1_Sales_Cost, 0)) AS Date1_Sales_Cost,
		SUM(ISNULL(r.Date1_Ttl_MultUnits, 0)) AS Date1_Ttl_MultUnits,
		SUM(ISNULL(r.Date2_Ttl_Transactions, 0)) AS Date2_Ttl_Transactions,
		SUM(ISNULL(r.Date2_Ttl_Sls_Units, 0)) AS Date2_Ttl_Sls_Units,
		SUM(ISNULL(r.Date2_Ttl_Sls_Dollars, 0)) AS Date2_Ttl_Sls_Dollars,
		SUM(ISNULL(r.Date2_Sales_Cost, 0)) AS Date2_Sales_Cost,
		SUM(ISNULL(r.Date2_Ttl_MultUnits, 0))AS Date2_Ttl_MultUnits,
		SUM(ISNULL(r.Date3_Ttl_Transactions, 0)) AS Date3_Ttl_Transactions,
		SUM(ISNULL(r.Date3_Ttl_Sls_Units, 0)) AS Date3_Ttl_Sls_Units,
		SUM(ISNULL(r.Date3_Ttl_Sls_Dollars, 0)) AS Date3_Ttl_Sls_Dollars,
		SUM(ISNULL(r.Date3_Sales_Cost, 0)) AS Date3_Sales_Cost,
		SUM(ISNULL(r.Date3_Ttl_MultUnits, 0)) AS Date3_Ttl_MultUnits
FROM		dbo.sku AS s
LEFT JOIN	(
			SELECT		Brand,
					Style
					SUM(CASE WHEN quan > 0 AND dte >= '1/1/2015' AND dte <= '1/2/2015' AND recnum IS NOT NULL THEN 1 ELSE 0 END) AS Date1_Ttl_Transactions,
					SUM(CASE WHEN quan > 0 AND dte >= '1/1/2015' AND dte <= '1/2/2015' THEN quan ELSE 0 END) AS Date1_Ttl_Sls_Units,
					SUM(CASE WHEN quan > 0 AND dte >= '1/1/2015' AND dte <= '1/2/2015' THEN extended ELSE 0 END) AS Date1_Ttl_Sls_Dollars,
					SUM(CASE WHEN quan > 0 AND dte >= '1/1/2015' AND dte <= '1/2/2015' THEN cost ELSE 0 END) AS Date1_Sales_Cost,
					SUM(CASE WHEN quan > 1 AND dte >= '1/1/2015' AND dte <= '1/2/2015' AND recnum IS NOT NULL THEN 1 ELSE 0 END) AS Date1_Ttl_MultUnits,
					SUM(CASE WHEN quan > 0 AND dte >= '2/1/2015' AND dte <= '2/2/2015' AND recnum IS NOT NULL THEN 1 ELSE 0 END) AS Date2_Ttl_Transactions,
					SUM(CASE WHEN quan > 0 AND dte >= '2/1/2015' AND dte <= '2/2/2015' THEN quan ELSE 0 END) AS Date2_Ttl_Sls_Units,
					SUM(CASE WHEN quan > 0 AND dte >= '2/1/2015' AND dte <= '2/2/2015' THEN extended ELSE 0 END) AS Date2_Ttl_Sls_Dollars,
					SUM(CASE WHEN quan > 0 AND dte >= '2/1/2015' AND dte <= '2/2/2015' THEN cost ELSE 0 END) AS Date2_Sales_Cost,
					SUM(CASE WHEN quan > 1 AND dte >= '2/1/2015' AND dte <= '2/2/2015' AND recnum IS NOT NULL THEN 1 ELSE 0 END) AS Date2_Ttl_MultUnits,
					SUM(CASE WHEN quan > 0 AND dte >= '3/1/2015' AND dte <= '3/2/2015' AND recnum IS NOT NULL THEN 1 ELSE 0 END) AS Date3_Ttl_Transactions,
					SUM(CASE WHEN quan > 0 AND dte >= '3/1/2015' AND dte <= '3/2/2015' THEN quan ELSE 0 END) AS Date3_Ttl_Sls_Units,
					SUM(CASE WHEN quan > 0 AND dte >= '3/1/2015' AND dte <= '3/2/2015' THEN extended ELSE 0 END) AS Date3_Ttl_Sls_Dollars,
					SUM(CASE WHEN quan > 0 AND dte >= '3/1/2015' AND dte <= '3/2/2015' THEN cost ELSE 0 END) AS Date3_Sales_Cost,
					SUM(CASE WHEN quan > 1 AND dte >= '3/1/2015' AND dte <= '3/2/2015' AND rec_num IS NOT NULL THEN 1 ELSE 0 END) AS Date3_Ttl_MultUnits
			FROM		dbo.Recline
			WHERE		quan > 0
					AND dte >= '1/1/2015'
					AND dte >= '3/2/2015'
					AND Style = '1.3 OZ'
			GROUP BY	Brand,
					Style
		) AS r ON r.Brand = s.Brand
			AND r.Style = s.Style
WHERE		s.Style = '1.3 OZ'
GROUP BY	s.Dept,
		s.Style;

Microsoft SQL Server MVP, MCT, MCSE, MCSA, MCP, MCITP, MCTS, MCDBA

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-27 : 10:08:15

A bit simpler:


WITH recline
AS (
    SELECT count(rec_num), dte
    FROM recline r
    WHERE r.brand = sku.brand
        AND r.style = sku.style
        AND r.quan > 1
        AND r.dte >= '1/1/2015'
        AND r.dte <= '3/2/2015'
    )
SELECT 
     count(case when dte between '1/1/2015' and '1/2/2015' then 1 end) as AS Date1_Ttl_Transactions
    ,sum(case when dte between '1/1/2015' and '1/2/2015' then r.quan end) AS Date1_Ttl_Sls_Units
    ,... etc
FROM sku
LEFT OUTER JOIN recline r ON (
        sku.brand = r.brand
        AND sku.style = r.style
        )
WHERE sku.style = '1.3 OZ'
GROUP BY 
      sku.dept
    , sku.style

Kristen
Test

22859 Posts

Posted - 2015-03-27 : 15:34:52

quote:
Originally posted by bjstyl2

r.dte <= '1/2/2015'

There are all sorts of situations where SQL will either interpret that as 01-February or 02-January ... best not to use that style of string date constant at all in SQL.

'20150201' (01-February) or '20150102' (02-January) will be treated by SQL unambiguously, regardless of what the server Locale is, the User's Language choice, and everything else. Use that 'YYYYMMDD' format instead ... please!

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-03-27 : 15:39:00

@Kristen, too true! And then if these are really datetime columns (the OP didn't say so far), we really want dte >= start and dte < end date + 1 day

Kristen
Test

22859 Posts

Posted - 2015-03-27 : 16:14:20

Oh yes

I missed the "<= endpoint". I normally charge for that one

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2015-03-27 : 18:30:02

If these are really DATE columns...

dte between [start] and [end]

Now THAT'S pedantic!

I would rather be the man who bought the Brooklyn Bridge than the one who sold it. -Will Rogers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources