multi-select sql query with date range

Question

I have this query where I get totals of different stats from an employee roster table.

SELECT A.rempid AS EmpId, 
       E.flname, 
       A.rdo_total, 
       B.grave_total, 
       C.sundays, 
       D.holidays 
FROM   (SELECT rempid, 
               Count(rshiftid)AS RDO_Total 
        FROM   rtmp1 
        WHERE  rshiftid = 2 
        GROUP  BY rempid 
        HAVING Count(rshiftid) > 0) A, 
       (SELECT rempid, 
               Count(rshiftid)AS Grave_Total 
        FROM   rtmp1 
        WHERE  rshiftid = 6 
        GROUP  BY rempid 
        HAVING Count(rshiftid) > 0)B, 
       (SELECT rempid, 
               Count(rshiftid) AS Sundays 
        FROM   rtmp1 
        WHERE  Datepart(dw, rdate) = 1 
               AND rshiftid > 2 
        GROUP  BY rempid 
        HAVING Count(rshiftid) > 0)C, 
       (SELECT rempid, 
               Count(rshiftid) AS Holidays 
        FROM   rtmp1 
        WHERE  rdate IN (SELECT pubhdt 
                         FROM   pubhol) 
               AND rshiftid > 2 
        GROUP  BY rempid 
        HAVING Count(rshiftid) > 0)D, 
       (SELECT empid, 
               [fname] + ' ' + [sname] AS flName 
        FROM   remp1)E 
WHERE  A.rempid = B.rempid 
       AND A.rempid = E.empid 
       AND A.rempid = C.rempid 
       AND A.rempid = D.rempid 
ORDER  BY A.rempid

I would like to add a date range into it, so that I can query the database within 2 dates. The rTmp1 table has a column called rDate. I was wondering what the best way to do this. I could add it to a stored procedure and add variable to each select query. Or is there a better way to run the query within a date range.

I could add it to a stored procedure and add variable to each select query - yes you certainly can! — Nir Alfasi
– Nir Alfasi, Commented Jan 7, 2013 at 21:17
@Jacco why not ? it's a good option! btw creating a temp table sounds to me like an over-kill unless he'll need the same result-set again. — Nir Alfasi
– Nir Alfasi, Commented Jan 7, 2013 at 22:36
@alfasin My apologies for misunderstanding your comment. I thought it was meant sarcastic, as I did/do not see how a sp would improve the solution. My temp table suggestion comes from the fact he is querying the table rtmp1 4 times and he will need the date range on all 4 of the queries. — Jacco
– Jacco, Commented Jan 8, 2013 at 7:05

Nir Alfasi · Accepted Answer · 2013-01-08 17:55:00Z

1

i think just add an additional where clause item similar to:

AND ( rDate > somedate AND rDate < someotherdate )

edited Jan 8, 2013 at 17:55

Nir Alfasi

53.6k11 gold badges94 silver badges138 bronze badges

answered Jan 7, 2013 at 21:28

Randy

16.7k2 gold badges40 silver badges56 bronze badges

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

2 Comments

Nir Alfasi Over a year ago

somedate and someotherdate are dynamic!

Randy Over a year ago

i intended that to be variables... so i probably should have added @ somedate and @ someotherdate - the point is that you only need one additional AND condition - not one in every subselect.

Community · Accepted Answer · 2017-04-13 12:42:38Z

0

Adding the date range to each query is the most direct solution.

Making it a stored procedure is something that can always be done with a query, but has nothing to do with this specific case.

If the number of records resulting from narrowing down your table to the specified date range is substantially less than the entire table, it might be an option to insert these records into a temporary table or a table variable and run your existing query on that table/resultset.

Though I do not have any data to test, you might consider the following query as it is more easy to read and might perform better. But you have to check the results for yourself and maybe do some adjustments.

DECLARE @startDate date = '12/01/2012'
DECLARE @endDate date = DATEADD(MONTH, 1, @startDate)

SELECT
  [e].[empid],
  [e].[fname] + ' ' + [e].[sname] AS [flName],
  SUM(CASE WHEN [t].[rshiftid] = 2 THEN 1 ELSE 0 END) AS [RDO_Total],
  SUM(CASE WHEN [t].[rshiftid] = 6 THEN 1 ELSE 0 END) AS [Grave_Total],
  SUM(CASE WHEN [t].[rshiftid] > 2 AND DATEPART(dw, [t].[rdate]) = 1 THEN 1 ELSE 0 END) AS [Sundays],
  SUM(CASE WHEN [t].[rshiftid] > 2 AND [h].[pubhdt] IS NOT NULL THEN 1 ELSE 0 END) AS [Holidays]
FROM [remp1] [e]
INNER JOIN [rtmp1] [t] ON [e].[empid] = [t].[rempid]
LEFT JOIN [pubhol] [h] ON [t].[rdate] = [h].[pubhdt]
WHERE [t].[rdate] BETWEEN @startDate AND @endDate
GROUP BY 
  [e].[empid],
  [e].[fname],
  [e].[sname]
ORDER BY [empid] ASC

edited Apr 13, 2017 at 12:42

CommunityBot

11 silver badge

answered Jan 7, 2013 at 21:31

Jacco

3,2701 gold badge22 silver badges30 bronze badges

2 Comments

AJ188 Over a year ago

thanks Jacco, that is exactly what I wanted, it also shows employees with 0 values which was also a problem with the my query.

Jacco Over a year ago

@AJ188 Glad to have answered your question. Thanks for marking it as the (preferred) solution. Now keep coming back to SO! ;-)

Collectives™ on Stack Overflow

multi-select sql query with date range

2 Answers 2

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related