SQL Server Performance Forum – Threads Archive
Sub Query/Dynamic query
Hi.. I have a table for storing time entries. Table structure is given below TaskID/AssociateID/WeekStartDate/MonHrs/TueHrs/WedHrs/ThuHrs/FriHrs/satHrs/sunHrs1001 A1A 04-02-2007 8 8 8 8 8 0 0
1001 A1B 04-09-2007 8 8 8 8 8 0 0
1001 A1C 04-16-2007 8 8 8 8 8 0 0
1001 A1D 04-23-2007 8 8 8 8 8 0 0 I need to query the data from this time table for the period
04/02/2007 to 04/05/2007
Like wise, I need to query time entered for any daterange. It has to give the exact time. can any one assist me how to write the query in this case.
CREATE A View Like SELECT TaskID, AssociateID,WeekStartDate, MonHrs as Hrs
UNION ALL
SELECT TaskID, AssociateID,WeekStartDate+1, TueHrs as Hrs
UNION ALL
SELECT TaskID, AssociateID,WeekStartDate+2, WedHrs as Hrs
UNION ALL
SELECT TaskID, AssociateID,WeekStartDate+3, ThuHrs as Hrs
UNION ALL
SELECT TaskID, AssociateID,WeekStartDate+4, FriHrs as Hrs
UNION ALL
SELECT TaskID, AssociateID,WeekStartDate+5, SatHrs as Hrs
UNION ALL
SELECT TaskID, AssociateID,WeekStartDate+6, SunHrs as Hrs
Now you can query this view for any info.
Roji. P. Thomas
SQL Server MVP
http://toponewithties.blogspot.com
Thank you Roji !!!
]]>