CODEWITHSUNDEEP
sqlsql-server
user3582213
user3582213

Reputation: 13

Sharing Parameters Between Subqueries

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             |

Upvotes: 0

Views: 104

Answers (2)

Skyl3lazer
Skyl3lazer

Reputation: 378

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.

Upvotes: 0

Dave.Gugg
Dave.Gugg

Reputation: 6781

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

Upvotes: 1

Related Questions