Subquery to do this for report? - 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

Subquery to do this for report?

Author

Topic

zymotic
Starting Member

1 Post

Posted - 2010-05-08 : 22:10:32

Hi,

What I want to do is have a Maps column for each quarter in 2009 and 2010. Below query gives me this result:

PersonCOl 2ndQuart2009Col
Person1 476
Person2 504
Person3 63
Person4 4
Person5 99

select [user] as Officer,
sum(quantity) as Maps2ndQuarter2009
from dbo.mainjobentry
where month(date_received) in (7,8,9) and year(date_received) in (2009)
group by [user]

So that gives me my first Maps per quarter column but I don't know how to structure a query that will give me additional columns for the other quaters in 2009 and 2010 and include it in the result eg.

PersonCol 2ndQuart2009Col 3rdQuart2009Col
Person1 476 561
Person2 504 628
Person3 63 53
Person4 4 1
Person5 99 219

malpashaa
Constraint Violating Yak Guru

264 Posts

Posted - 2010-05-08 : 22:51:11

Try this:


SELECT [user] as Officer,
       SUM(CASE WHEN date_received >= '20090101' AND date_received < '20090401' THEN quantity ELSE 0 END) AS Maps1stQuarter2009,
       SUM(CASE WHEN date_received >= '20090401' AND date_received < '20090701' THEN quantity ELSE 0 END) AS Maps2ndQuarter2009,
       SUM(CASE WHEN date_received >= '20090701' AND date_received < '20091001' THEN quantity ELSE 0 END) AS Maps3rdQuarter2009,
       SUM(CASE WHEN date_received >= '20091001' AND date_received < '20100101' THEN quantity ELSE 0 END) AS Maps4thQuarter2009,
       SUM(CASE WHEN date_received >= '20100101' AND date_received < '20100401' THEN quantity ELSE 0 END) AS Maps1stQuarter2010,
       SUM(CASE WHEN date_received >= '20100401' AND date_received < '20100701' THEN quantity ELSE 0 END) AS Maps2ndQuarter2010,
       SUM(CASE WHEN date_received >= '20100701' AND date_received < '20101001' THEN quantity ELSE 0 END) AS Maps3rdQuarter2010,
       SUM(CASE WHEN date_received >= '20101001' AND date_received < '20110101' THEN quantity ELSE 0 END) AS Maps4thQuarter2010
  FROM dbo.mainjobentry 
 WHERE date_received >= '20090101'
   AND date_received < '20110101'
 GROUP BY [user]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-09 : 02:56:01

or go for PIVOT operator if you're using SQL 2005 or later based on DATEPART(qq,datefield)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-09 : 18:22:25

I believe this approach is faster than CASE expression if a proper index is used.


CREATE NONCLUSTERED INDEX IX_0 ON dbo.mainjobentry (date_received) INCLUDE ([user], quantity)
GO

;WITH Maps1stQuarter2009 AS
(SELECT [user], SUM(quantity) as total
  FROM dbo.mainjobentry 
  WHERE date_received >= '20090101' AND date_received < '20090401'
  GROUP BY [user]),
  Maps2ndQuarter2009 AS
(SELECT [user], SUM(quantity) as total
  FROM dbo.mainjobentry 
  WHERE date_received >= '20090401' AND date_received < '20090701'
  GROUP BY [user]),  
  Maps3rdQuarter2009 AS
(SELECT [user], SUM(quantity) as total
  FROM dbo.mainjobentry 
  WHERE date_received >= '20090701' AND date_received < '20091001'
  GROUP BY [user]),
  Maps4thQuarter2009 AS
(SELECT [user], SUM(quantity) as total
  FROM dbo.mainjobentry 
  WHERE date_received >= '20091001' AND date_received < '20100101'
  GROUP BY [user]), 
  Maps1stQuarter2010 AS
(SELECT [user], SUM(quantity) as total
  FROM dbo.mainjobentry 
  WHERE date_received >= '20100101' AND date_received < '20100401'
  GROUP BY [user]), 
  Maps2ndQuarter2010 AS
(SELECT [user], SUM(quantity) as total
  FROM dbo.mainjobentry 
  WHERE date_received >= '20100401' AND date_received < '20100701'
  GROUP BY [user]), 
  Maps3rdQuarter2010 AS
(SELECT [user], SUM(quantity) as total
  FROM dbo.mainjobentry 
  WHERE date_received >= '20100701' AND date_received < '20101001'
  GROUP BY [user]),
  Maps4thQuarter2010 AS
(SELECT [user], SUM(quantity) as total
  FROM dbo.mainjobentry 
  WHERE date_received >= '20101001' AND date_received < '20110101'
  GROUP BY [user])
  SELECT D.[user] AS Officer,
         COALESCE(t1.total, 0) AS Maps1stQuarter2009,
         COALESCE(t2.total, 0) AS Maps2ndQuarter2009,
         COALESCE(t3.total, 0) AS Maps3rdQuarter2009,
         COALESCE(t4.total, 0) AS Maps4thQuarter2009,
         COALESCE(t5.total, 0) AS Maps1stQuarter2010,
         COALESCE(t7.total, 0) AS Maps2ndQuarter2010,
         COALESCE(t7.total, 0) AS Maps3rdQuarter2010,
         COALESCE(t8.total, 0) AS Maps4thQuarter2010         
  FROM (SELECT DISTINCT [user]
         FROM dbo.mainjobentry)D
  LEFT OUTER JOIN Maps1stQuarter2009 t1 ON D.[user]=t1.[user]
  LEFT OUTER JOIN Maps2ndQuarter2009 t2 ON D.[user]=t1.[user]
  LEFT OUTER JOIN Maps3rdQuarter2009 t3 ON D.[user]=t1.[user]
  LEFT OUTER JOIN Maps4thQuarter2009 t4 ON D.[user]=t1.[user]
  LEFT OUTER JOIN Maps1stQuarter2010 t5 ON D.[user]=t1.[user]
  LEFT OUTER JOIN Maps2ndQuarter2010 t6 ON D.[user]=t1.[user]
  LEFT OUTER JOIN Maps3rdQuarter2010 t7 ON D.[user]=t1.[user]
  LEFT OUTER JOIN Maps4thQuarter2010 t8 ON D.[user]=t1.[user]

DBA in the making
Aged Yak Warrior

638 Posts

Posted - 2010-05-10 : 06:40:49

quote:
Originally posted by ms65g

I believe this approach is faster than CASE expression if a proper index is used.

I seriously doubt that. If you applied the same index to the CASE query, I think it would be much faster. It looks a lot less complex.

------------------------------------------------------------------------------------
Any and all code contained within this post comes with a 100% money back guarantee.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-10 : 13:15:14

quote:

Originally posted by ms65g

I believe this approach is faster than CASE expression if a proper index is used.


CREATE NONCLUSTERED INDEX IX_0 ON dbo.mainjobentry (date_received) INCLUDE ([user], quantity)
GO

;WITH Maps1stQuarter2009 AS
(SELECT [user], SUM(quantity) as total
  FROM dbo.mainjobentry 
  WHERE date_received >= '20090101' AND date_received < '20090401'
  GROUP BY [user]),
  Maps2ndQuarter2009 AS
(SELECT [user], SUM(quantity) as total
  FROM dbo.mainjobentry 
  WHERE date_received >= '20090401' AND date_received < '20090701'
  GROUP BY [user]),  
  Maps3rdQuarter2009 AS
(SELECT [user], SUM(quantity) as total
  FROM dbo.mainjobentry 
  WHERE date_received >= '20090701' AND date_received < '20091001'
  GROUP BY [user]),
  Maps4thQuarter2009 AS
(SELECT [user], SUM(quantity) as total
  FROM dbo.mainjobentry 
  WHERE date_received >= '20091001' AND date_received < '20100101'
  GROUP BY [user]), 
  Maps1stQuarter2010 AS
(SELECT [user], SUM(quantity) as total
  FROM dbo.mainjobentry 
  WHERE date_received >= '20100101' AND date_received < '20100401'
  GROUP BY [user]), 
  Maps2ndQuarter2010 AS
(SELECT [user], SUM(quantity) as total
  FROM dbo.mainjobentry 
  WHERE date_received >= '20100401' AND date_received < '20100701'
  GROUP BY [user]), 
  Maps3rdQuarter2010 AS
(SELECT [user], SUM(quantity) as total
  FROM dbo.mainjobentry 
  WHERE date_received >= '20100701' AND date_received < '20101001'
  GROUP BY [user]),
  Maps4thQuarter2010 AS
(SELECT [user], SUM(quantity) as total
  FROM dbo.mainjobentry 
  WHERE date_received >= '20101001' AND date_received < '20110101'
  GROUP BY [user])
  SELECT D.[user] AS Officer,
         COALESCE(t1.total, 0) AS Maps1stQuarter2009,
         COALESCE(t2.total, 0) AS Maps2ndQuarter2009,
         COALESCE(t3.total, 0) AS Maps3rdQuarter2009,
         COALESCE(t4.total, 0) AS Maps4thQuarter2009,
         COALESCE(t5.total, 0) AS Maps1stQuarter2010,
         COALESCE(t7.total, 0) AS Maps2ndQuarter2010,
         COALESCE(t7.total, 0) AS Maps3rdQuarter2010,
         COALESCE(t8.total, 0) AS Maps4thQuarter2010         
  FROM (SELECT DISTINCT [user]
         FROM dbo.mainjobentry)D
  LEFT OUTER JOIN Maps1stQuarter2009 t1 ON D.[user]=t1.[user]
  LEFT OUTER JOIN Maps2ndQuarter2009 t2 ON D.[user]=t1.[user]
  LEFT OUTER JOIN Maps3rdQuarter2009 t3 ON D.[user]=t1.[user]
  LEFT OUTER JOIN Maps4thQuarter2009 t4 ON D.[user]=t1.[user]
  LEFT OUTER JOIN Maps1stQuarter2010 t5 ON D.[user]=t1.[user]
  LEFT OUTER JOIN Maps2ndQuarter2010 t6 ON D.[user]=t1.[user]
  LEFT OUTER JOIN Maps3rdQuarter2010 t7 ON D.[user]=t1.[user]
  LEFT OUTER JOIN Maps4thQuarter2010 t8 ON D.[user]=t1.[user]

Have you tested that?
It would be interesting to see the result!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-10 : 14:43:51

quote:
Have you tested that?
It would be interesting to see the result!

I don't understand what you mean.
Can you tell me more clearly?

Result after executing query or result of testing performance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-10 : 14:53:22

quote:
Originally posted by ms65g

quote:
Have you tested that?
It would be interesting to see the result!

I don't understand what you mean.
Can you tell me more clearly?

Result after executing query or result of testing performance.

i mean have you tested your method against CASE method

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-05-10 : 15:05:20

quote:
Originally posted by visakh16

quote:
Originally posted by ms65g

quote:
Have you tested that?
It would be interesting to see the result!

I don't understand what you mean.
Can you tell me more clearly?

Result after executing query or result of testing performance.

i mean have you tested your method against CASE method

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

I have tested the multiple outer joins against CASE method with 100K data set.
In terms of Reads and Estimated Subtree Cost the CASE method is much better but in Elapse Time the outer joins is faster.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources