CODEWITHSUNDEEP
sqlsql-servert-sqltable-variable
user3115933
user3115933

Reputation: 4463

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

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?

Upvotes: 0

Views: 90

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

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.

Upvotes: 1

Ali Eshghi
Ali Eshghi

Reputation: 1243

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]

Upvotes: 0

Related Questions