Sharing Parameters Between Subqueries

Question

I'm joining data from two different subqueries for the same date range. I have a working query right now which specifies the date range in each subquery. Ideally I'd like to not be updating the dates in two different spots each time I run the query for a new date range, so I'm looking for a way to enter date parameters in one spot and pass them to both subqueries. I've tried nesting one query inside the other, and I've tried removing the date ranges from the subqueries completely, and paring down the results outside of the subqueries, but both methods resulted in a serious performance hit.

Here's a simplified version of my query:

SELECT qVolTbl.QUEUEID,
       qVolTbl.Queue_Volume,
       qVolTbl.CAMPAIGNID,
       camVolTbl.Campaign_Volume
FROM   (
       SELECT QHISTORY.QUEUEID,
              SUM(QHISTORY.CALLVOLUME) AS Queue_Volume,
              QHISTORY.CAMPAIGNID
       FROM   QHISTORY
       WHERE  QHISTORY.DATE BETWEEN CONVERT(DATETIME, '01-NOV-2014')
                                AND CONVERT(DATETIME, '30-NOV-2014')
       GROUP  BY QHISTORY.QUEUEID,
                 QHISTORY.CAMPAIGNID
       ) qVolTbl

       LEFT JOIN (
                 SELECT SP.CAMPAIGNID,
                        SUM(QHISTORY.CALLVOLUME) as Campaign_Volume
                 FROM   QHISTORY
                 WHERE  QHISTORY.DATE BETWEEN CONVERT(DATETIME, '01-NOV-2014')
                                          AND CONVERT(DATETIME, '30-NOV-2014')
                 GROUP  BY SP.CAMPAIGNID
                 ) camVolTbl
              ON camVolTbl.CAMPAIGNID = qVolTbl.CAMPAIGNID

More details of what I'm working on, if necessary:

I'm querying volume data from a hierarchy where "Volume" comes in on "Queues," which are grouped by "Campaigns." I want results consisting of total volume for each queue on the same row as total volume for the campaign the queue belongs to like this:

| QUEUEID | QUEUE_VOLUME | CAMPAIGNID | CAMPAIGN_VOLUME |
---------------------------------------------------------
| 001     | 200          | 001        | 500             |
| 002     | 300          | 001        | 500             |
| 003     | 100          | 002        | 350             |
| 004     | 250          | 002        | 350             |

why not declare the date variables and use them in both the queries — radar
– radar, Commented Dec 10, 2014 at 19:21

Dave.Gugg · Accepted Answer · 2014-12-10 19:56:12Z

1

This is how you'd construct what radar suggested:

DECLARE @startdate DATETIME = '01-NOV-2014',@enddate DATETIME = '30-NOV-2014'

SELECT qVolTbl.QUEUEID,
       qVolTbl.Queue_Volume,
       qVolTbl.CAMPAIGNID,
       camVolTbl.Campaign_Volume
FROM   (
       SELECT QHISTORY.QUEUEID,
              SUM(QHISTORY.CALLVOLUME) AS Queue_Volume,
              QHISTORY.CAMPAIGNID
       FROM   QHISTORY
       WHERE  QHISTORY.DATE BETWEEN @startdate
                                AND @enddate
       GROUP  BY QHISTORY.QUEUEID,
                 QHISTORY.CAMPAIGNID
       ) qVolTbl

       LEFT JOIN (
                 SELECT SP.CAMPAIGNID,
                        SUM(QHISTORY.CALLVOLUME) as Campaign_Volume
                 FROM   QHISTORY
                 WHERE  QHISTORY.DATE BETWEEN @startdate
                                          AND @enddate
                 GROUP  BY SP.CAMPAIGNID
                 ) camVolTbl
              ON camVolTbl.CAMPAIGNID = qVolTbl.CAMPAIGNID

answered Dec 10, 2014 at 19:56

Dave.Gugg

6,8013 gold badges26 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jeremy Over a year ago

Just a fair warning to the OP if s/he has not used dates before. The query will NOT include any records on the 30th, because a plain date is midnight on that day. If you wish to include all times for the 30th, add 1 day to your end range, or compare just the date portions.

Dave.Gugg Over a year ago

And by 30th, you mean @enddate. It'd be a good idea to include 23:59:59.999 to the end of the @enddate declaration.

Jeremy Over a year ago

Yup, that's what I meant. adding the time on there works too.

user3582213 Over a year ago

Thanks, I hadn't used variables before in SQL, it seems like an obvious solution now... Also good call out on the end date, I had already accounted for that actually in my actual query, with DATEADD(n, 1439, @enddate).

Skyl3lazer · Accepted Answer · 2014-12-10 19:57:57Z

0

You could do a subselect with the date as a fake column, and just not select that column in the top level so it isn't visible. That said, using variables here is probably smarter.

answered Dec 10, 2014 at 19:57

Skyl3lazer

3782 silver badges11 bronze badges

Collectives™ on Stack Overflow

Sharing Parameters Between Subqueries

2 Answers 2

4 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related