I have table
employees with columns
birth date etc. What I want to have is a table that for each row in employees table have range of dates, for example:
surname0 , day()+0 surname0 , day()+1 surname0 , day()+2 ................. surname0 , day()+30 surname1 , day()+0 surname1 , day()+1 surname1 , day()+2 ................. surname1 , day()+30 ................. ................. surname100 , day()+0 surname100 , day()+1 surname100 , day()+2 ................. surname100 , day()+30
I have no idea how to do that.
The main idea is to have list of employees and the number of their activities per day. In case that particular employee has no task for some specific day to have "0". I have a table
tasks, with columns
teams (consist of several surnames separated by commas),
description etc. So comparing that table with the one mentioned at the beginning with range of dates per each surname, I could get number of
activities per day per surname. On other way using smth like this
select distinct surname, day, count(surname) over (partition by day, surname) from employees left outer join tasks on team like '%'+surname+'%'
I dont get rows with "0" zero values for all employees that have no tasks for particular day, that is the reason why I want to have that table with range of days and combining that table with tasks table I can easily get list of all employees for following 30 days and number of their tasks.
I work on MS SQL 2005 server