its easy to combine and collect data between two dates but my problem is quet difficult and amazing as well. i have to collect data from 4 differet days which are in sequence means first from 6am to 6pm second day 6am to 6pm thirdday from 1800 to forthday 6am and from forth day 1800 to fifthday 6am meam collection of data for one shift. presenly i am calculating data from this query but only for one day either day or night. and seperatly. but on four pages but i want to collect all out put on one page i know its not difficult for my expert brothers who will hepl me indeed.....................eric my presend query is this................. ( @ShiftDate Datetime, @Shift_D_4_Day_N_4_Night char(1), @Shift_0_1_29 int -- @EndDate Datetime ) AS SELECT TOP (100) PERCENT COUNT(*) AS Quantity, dbo.System.Type, dbo.Room.RoomID, dbo.GetLineLU(RoomID)As RoomWrkIDID, dbo.GetDayDate(dbo.Room.Datestamp) As "Day_Date", dbo.GetShift(dbo.Room.Datestamp) AS "Date|D/N" FROM dbo.Room INNER JOIN dbo.System ON dbo.Room.RefNum = dbo.System.RefNum WHERE ( dbo.Room.Datestamp BETWEEN (Case when @Shift_D_4_Day_N_4_Night = 'D' Then CONVERT(Datetime, @ShiftDate+'06:00:00', 102) when @Shift_D_4_Day_N_4_Night = 'N' Then CONVERT(Datetime, @ShiftDate+'18:00:00', 102) Else CONVERT(Datetime, @ShiftDate+'06:00:00', 102) end ) AND ( Case when @Shift_D_4_Day_N_4_Night = 'D' Then CONVERT(datetime, @ShiftDate + '18:00:00', 102) when @Shift_D_4_Day_N_4_Night = 'N' Then DATEADD(day,+1,(CONVERT(Datetime, @ShiftDate+'06:00:00', 102))) Else DATEADD(day,+@Shift_0_1_29,(CONVERT(Datetime, @ShiftDate+'06:00:00', 102))) end) ) GROUP BY dbo.Room.RoomID,dbo.GetLineLU(RoomID), dbo.System.Type, dbo.GetShift(dbo.Room.Datestamp),dbo.GetDayDate(dbo.Room.Datestamp) HAVING (dbo.Room.RoomID BETWEEN 1 AND 26) Order by CAST(dbo.GetDayDate(dbo.Room.Datestamp) as int), dbo.Room.RoomID Return any solution for collecting data from 4 dates as explain before................ thanks withxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx in advance......

If you are using SQL 2008 try with seperate fields as @ShiftDate date @ShiftTime timemy blog http://karlberan.wordpress.com/

I think so you did not go completly my question normal queries return continues time flow data from this date and time to that date and time but i have to get data in selected times from different date like date1 have to get data from time 6:00:00 to 18:00:00 then date1+1day get data from time 6:00:00 to 18:00:00 then Date1+2 get data from 18:00:00 to 06:00:00 nextday (which will calulate by function i have have done already) then date1+3 from time 18:00:00 to 06:00:00. this is the flow of data my query already geting data with date1+1 automaticaly when press N as night you can see in query up. doing day add to date1 as but need to pull data during these time span on one sheet and do work on chart thats you know easy to calculate.........so now think about this?

i have run the above query under where clause like this but did not return any value but executed i dont know whats going wrong WHERE ( dbo.Room.Datestamp BETWEEN CONVERT(Datetime, @ShiftDate+'06:00:00', 102) and CONVERT(Datetime, @ShiftDate+'18:00:00', 102) and dbo.Room.Datestamp BETWEEN DATEADD(day,+1,(CONVERT(Datetime, @ShiftDate+'06:00:00', 102))) and DATEADD(day,+1,(CONVERT(Datetime, @ShiftDate+'18:00:00', 102))) ) any view in this regards getting data between two dates and two different time spanes eric

Hi to every body finally after palying with sql server i am succeeded to get the data from 4 days or one shift of having 2 days and 2 night work this query work basically like this first day it get data from morning 6 to evening 6 or 6am to 6pm second day it get data from mornning 6 to evening 6 or 6am to 6pm thirdday it get data from 6evenning to forth day 6am mean 12 hours of which shift start 3rd day evenning and finish 4th day mornning Forth day it start again 18hrs evening and finish 5th day morning 6am. so this query will collect data only 12 hour first day and skip next 12hors again get 12hour and again skip 24 hours and collect data again for 12 hours again it will skip 12 hours and get data for next 12 hours and finally gives you the result query is this. ( @ShiftDate Datetime, -- @Shift_D_4_Day_N_4_Night char(1), -- @Shift_0_1_29 int -- @EndDate Datetime ) AS SET NOCOUNT ON; Declare @date1 datetime Declare @date2 datetime Declare @date3 datetime Declare @date4 datetime Declare @date5 datetime Declare @date6 datetime Declare @date7 datetime Declare @date8 datetime set @date1 = CONVERT(Datetime, @ShiftDate+'06:00:00', 102) set @date2 = CONVERT(Datetime, @ShiftDate+'18:00:00', 102) set @date3 = DATEADD(day,+1,(CONVERT(Datetime, @ShiftDate+'06:00:00', 102))) set @date4 = DATEADD(day,+1,(CONVERT(Datetime, @ShiftDate+'18:00:00', 102))) set @date5 = DATEADD(day,+2,(CONVERT(Datetime, @ShiftDate+'18:00:00', 102))) set @date6 = DATEADD(day,+3,(CONVERT(Datetime, @ShiftDate+'06:00:00', 102))) set @date7 = DATEADD(day,+3,(CONVERT(Datetime, @ShiftDate+'18:00:00', 102))) set @date8 = DATEADD(day,+4,(CONVERT(Datetime, @ShiftDate+'06:00:00', 102))) SELECT TOP (100) PERCENT COUNT(*) AS Quantity, dbo.System.Type, dbo.Room.RoomID, dbo.GetLineLU(RoomID)As RoomWrkIDID, dbo.GetDayDate(dbo.Room.Datestamp) As "Day_Date", dbo.GetShift(dbo.Room.Datestamp) AS "Date|D/N" FROM dbo.Room INNER JOIN dbo.System ON dbo.Room.RefNum = dbo.System.RefNum WHERE ( dbo.ModuleTrx.Datestamp between @date1 and @date2 or dbo.ModuleTrx.Datestamp between @date3 and @date4 or dbo.ModuleTrx.Datestamp between @date5 and @date6 or dbo.ModuleTrx.Datestamp between @date7 and @date8 ) GROUP BY dbo.Room.RoomID,dbo.GetLineLU(RoomID), dbo.System.Type, dbo.GetShift(dbo.Room.Datestamp),dbo.GetDayDate(dbo.Room.Datestamp) HAVING (dbo.Room.RoomID BETWEEN 1 AND 26) Order by CAST(dbo.GetDayDate(dbo.Room.Datestamp) as int), dbo.Room.RoomID Return this is said to be a use of or operator in where clause. hope will enjoy all and have a fun as well. some time a little thing make big trouble and insist to think a lot but i spend 2 days completly to find our this little solution but hope full to be help full to other fellows as well. in this query also used to scaller functions which return day or night and date of the calender month as getshift and getdaydate as are define separatly Eric ericcsico

the result of this query is like this 171 itemA 1 A 1 1 D 164 itemA 2 A 1 1 D 165 itemA 3 A 1 1 D 161 itemA 4 A 1 1 D 155 itemA 5 B 1 1 D 150 itemA 6 B 1 1 D 165 itemA 7 B 1 1 D 201 itemA 8 B 1 1 D 254 itemA 9 C 1 1 D 261 itemA 10 C 1 1 D 162 itemB 11 D 1 1 D 155 itemB 12 D 1 1 D 144 itemB 13 D 1 1 D 147 itemB 14 D 1 1 D 159 itemB 16 M 1 1 D 159 itemA 1 A 2 2 D 169 itemA 2 A 2 2 D 133 itemA 3 A 2 2 D 168 itemA 4 A 2 2 D 117 itemA 5 B 2 2 D 161 itemA 6 B 2 2 D 161 itemA 7 B 2 2 D 173 itemA 8 B 2 2 D 251 itemA 9 C 2 2 D 234 itemA 10 C 2 2 D 168 itemC 11 D 2 2 D 167 itemC 12 D 2 2 D 174 itemC 13 D 2 2 D 174 itemC 14 D 2 2 D 19 itemA 15 C 2 2 D 101 itemA 16 M 2 2 D 54 itemC 16 M 2 2 D 173 itemA 1 A 3 3 N 173 itemA 2 A 3 3 N 174 itemA 3 A 3 3 N 173 itemA 4 A 3 3 N 169 itemA 5 B 3 3 N 172 itemA 6 B 3 3 N 176 itemA 7 B 3 3 N 175 itemA 8 B 3 3 N 283 itemA 9 C 3 3 N 261 itemA 10 C 3 3 N 173 itemC 11 D 3 3 N 173 itemC 12 D 3 3 N 173 itemC 13 D 3 3 N 172 itemC 14 D 3 3 N 59 itemA 15 C 3 3 N 77 itemA 16 M 3 3 N 44 itemC 16 M 3 3 N 169 itemA 1 A 4 4 N 159 itemA 2 A 4 4 N 147 itemA 3 A 4 4 N 151 itemA 4 A 4 4 N 144 itemA 5 B 4 4 N 164 itemA 6 B 4 4 N 173 itemA 7 B 4 4 N 176 itemA 8 B 4 4 N 275 itemA 9 C 4 4 N 225 itemA 10 C 4 4 N 28 itemD 11 D 4 4 N 100 itemB 11 D 4 4 N 131 itemB 12 D 4 4 N 26 itemD 12 D 4 4 N 124 itemB 13 D 4 4 N 24 itemD 13 D 4 4 N 23 itemD 14 D 4 4 N 125 itemB 14 D 4 4 N 59 itemA 15 C 4 4 N 45 itemB 16 M 4 4 N 121 itemA 16 M 4 4 N