CODEWITHSUNDEEP
sql-server
user41829
user41829

Reputation: 105

T-SQL Query using table rows

So I have a table of min and max dates like below:

num    mindate     maxdate
1      x/xx/xxxx   x/xx/xxxx
2      x/xx/xxxx   x/xx/xxxx
3      x/xx/xxxx   x/xx/xxxx
...

What I need to do is now use all the values from min and max in a query of data for multiple employees pulling all the data between all those dates for every employee.

For instance the end table would look like:

emp#     empname     sickdays      mindate     maxdate 
0        emp1        1             x/xx/xxxx   x/xx/xxxx
0        emp1        0             x/xx/xxxx   x/xx/xxxx
1        emp2        1             x/xx/xxxx   x/xx/xxxx
1        emp2        0             x/xx/xxxx   x/xx/xxxx  
...

Can anyone point me in a directions to even begin to tackle this, I've never dealt with doing a (for each x do query y next) and I'm not sure what to even look up for something like this, any help would be awesome!

EDIT FOR CLAIRITY

The employee data will be coming from 2 separate tables and combined into one to get the employees number, name, sick day count and anything else I need. The issue is I need to run the query for EVERY employee for EVERY date of the table to get the number of sick days between each mindate and maxdate.

In other words each employee will every multiple rows of sick days for each date range.

So one employee would look like:

emp#     empname     sickdays      mindate     maxdate 
1        emp1        1             1/1/2016    1/7/2016
1        emp1        0             1/8/2016    1/14/2016
1        emp2        3             1/15/2016   1/21/2016
1        emp2        0             1/22/2016   1/29/2016
...

Upvotes: 1

Views: 50

Answers (2)

Adam Porad
Adam Porad

Reputation: 14471

Assuming there are two tables Employee and EmployeeSickTime that are looks like this

Employee
--------------------------
EmpId int NOT NULL,
EmpName varchar(100)

and 

EmployeeSickTime
--------------------------
EmpId int NOT NULL,
SickDate datetime NOT NULL

and the given table of date ranges

ReportRanges
--------------------------
num int NOT NULL,
MinDate datetime NOT NULL,
MaxDate datetime NOT NULL

Then this query will provide the results that you described. I created a working example on SQL Fiddle with sample data that you can check out.

SELECT e.EmpId, e.EmpName, SUM(CASE WHEN est.EmpId IS NOT NULL THEN 1 ELSE 0 END) as SickDays, r.MinDate, r.MaxDate
FROM Employee e
LEFT OUTER JOIN EmployeeSickTime est ON e.EmpId=est.EmpId
LEFT OUTER JOIN ReportRange r ON est.SickDate BETWEEN r.MinDate AND r.MaxDate
WHERE r.MinDate IS NOT NULL
GROUP BY e.EmpId, e.EmpName, r.MinDate, r.MaxDate
ORDER BY e.EmpId;

Upvotes: 1

Bruce Dunwiddie
Bruce Dunwiddie

Reputation: 2908

SELECT
e.employee_id,
COUNT(s.employee_id) sickdays,
r.mindate,
r.maxdate
FROM
employee e
CROSS JOIN report_dates r
LEFT OUTER JOIN sick_day s ON
s.employee_id = s.employee_id AND
s.effective_date BETWEEN r.mindate AND r.maxdate
GROUP BY
e.employee_id,
r.mindate,
r.maxdate;

Upvotes: 1

Related Questions