All dates between the emp_start and emp_end dates

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

All dates between the emp_start and emp_end dates

Author

Topic

MrAdmore
Starting Member

3 Posts

Posted - 2012-06-24 : 10:38:04

Hi!

I hope somebody can help me! I have been looking around and I cant find the answer to this query.

I have a table with:

emp_id status emp_start emp_end
1 3 120604 120806
2 2 120801 121101

I want to find a query where the result is all dates between the emp_start and emp_end dates with the status mark.
Can somebody help me with this?

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-06-24 : 13:04:39

what are datatypes of emp_start and emp_end? seeing the format looks like its varchar


SELECT emp_id,status,[Date]
FROM (SELECT emp_id,
             status,
             convert(datetime,emp_start,21) AS emp_start,
             convert(datetime,emp_end,21) AS emp_end  
      FROM table) t
CROSS APPLY dbo.CalendarTable(t.emp_start,t.emp_end,0,0) f

CalendarTable can be found in below link

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

MrAdmore
Starting Member

3 Posts

Posted - 2012-06-25 : 01:44:20

Sorry visakh!

Thanks for your reply!
I have confused you with my dates!
The datatypes for emp_start and emp_end is datetime and table name is my_emp

CBrammer
Starting Member

7 Posts

Posted - 2012-06-25 : 10:07:16

I am looking to do the same thing, I am trying to get programs that fall between an start and end date for current year and previous year, so employer can track an employees result for health insurance and not sure how I'd go about this.

MrAdmore
Starting Member

3 Posts

Posted - 2012-06-25 : 10:26:51

Thanks visakh!! It worked perfectly!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources