How to use a Table Variable with this specific T-SQL query?

Question

I am using SQL Server 2012 and I have the following T-SQL query running against a table on my database. The query runs fine but my issue is that I want to get an output for several specific dates. Instead of running the query multiple times (by changing the value of the @Date variable each time), I would like it to store the @Date values somewhere and implement the logic in my Query. How can I do this?

My original Query stands as follows:

DECLARE @Date date;
SET @Date = '20180630';

SELECT @Date, COUNT(*) AS Employees
FROM RavEmpID
WHERE DateOfEntry <= @Date
  AND (DateLeft > @Date
   OR  DateLeft IS NULL);

From the following post on StackOverflow (SQL Server store multiple values in sql variable), I understand that I need a Table Variable to store the @Date values I need.

So, I created a Table Variable (called TableVariable) on my database. The TableVariable table has only one column named Date as follows:

 Date
 2015-11-30
 2015-12-31
 2016-01-31
 2016-02-29
 ...

My new T-SQL now stands as follows:

SELECT b.[Date], COUNT(*) AS Employees

FROM RavEmpID a

left join TableVariable b on b.[Date] = a.[DateLeft]

WHERE a.DateOfEntry <= b.[Date]
  AND (a.DateLeft > b.[Date]
   OR  a.DateLeft IS NULL)

GROUP BY b.[Date]

When running this query, I have zero output. What am I doing wrong here?

I think may be the issue lies in the JOIN as there might not be any Dates in the TableVariable which match the DateLeft in the RavEmpID table. If I remove the JOIN, how do I tackle this issue? — user3115933, Aug 19 '18 at 06:45

Gordon Linoff · Accepted Answer · 2018-08-19T17:01:56.290

You need a few changes to your query:

Move all your original WHERE conditions into the ON clause
Make the date table the first table in the LEFT JOIN because you want to keep all those dates
Change the ``COUNT()` so it counts matches

The resulting query:

SELECT d.[Date], COUNT(r.DateOfEntry) AS Employees
FROM TableVariable d LEFT JOIN
     RavEmpID r
     ON r.DateOfEntry <= d.[Date] AND
        (r.DateLeft > d.[Date] OR r.DateLeft IS NULL)
GROUP BY d.[Date]
GROUP BY d.[Date];

Note that I also changed the table aliases from arbitrary letters to abbreviations for the table names. That makes the query much easier to read.

Thanks a ton! Exactly what I needed. I think you need to change the b.[Date] into d.[Date] in the first line. — user3115933, Aug 19 '18 at 16:16

score 0 · Answer 2 · answered Aug 19 '18 at 06:52

0

Your join is base on

 b.[Date] = a.[DateLeft]

But your where clause is base on

 a.DateLeft > b.[Date]

So there is no logic to get records. try this:

SELECT b.[Date], COUNT(*) AS Employees

FROM RavEmpID a

inner join TableVariable b on b.[Date] = a.[DateLeft]

WHERE a.DateOfEntry <= b.[Date]

answered Aug 19 '18 at 06:52

Ali Eshghi

1,131
1
13
30

It does give me an output for each dates in the TableVariable table but the figures are not correct. In fact, I need it to give me the total number of employees as at the dates in the TableVariable table. For example, for Date 2017-11-30, I need the total number of employees who have not yet left. ie: their DateLeft is > 2017-11-30 – user3115933 Aug 19 '18 at 07:22

How to use a Table Variable with this specific T-SQL query?

2 Answers2