How to find value between date and shift range

Hi

I am using ASP and ms-sql server 2000. I am getting value from user such as ID, StartDate, EndDate, StartShift, EndShift.

Basically when user entered the date range and Shift type, I need to compare the day name with the Table1 Coloumn like Mon and need to check the
shift type like AM, Which is MonAM and store the MonAM hours in the ShiftHours column on the Table3.


I have 2 tables. Table1 is -

ID(PK)      MONAM      MONPM      TUESAM      TUESPM      WEDAM      WEDPM      THUAM      THUPM      FRIAM      Dates
ID-1                  4                   3                   3            5            2          4                                              22/03/2010
ID-2                  2                   0                    3            5            2          0                    2            3      22/03/2010


In the Table2, I want to store the data based on the users input. Like -
if data entered for ID-1 is 22/03/2010 to 23/03/2010 (Date range), AM to PM (Shift range), Then on the 2nd table it will store data for ID-1 is :

Table2 -

ID(FK)      Dates            ShiftType                ShiftHours
ID-1          22/03/2010                    AM                       4
ID-1          22/03/2010                    PM                       3
ID-1          23/03/2010                    AM                       4
ID-1          23/03/2010                    PM                       5

if data entered for ID-2 is 22/03/2010 to 24/03/2010 , AM to AM Then on the third table it will store data for ID-2 is :
ID-2           22/03/2010                     AM                        2
ID-2           22/03/2010                     PM                        0
ID-2           23/03/2010                     AM                        3
ID-2           23/03/2010                     PM                        5
ID-2           24/03/2010                     AM                        2

Now I have a problem is that, my DATE FIELD always start From Monday like 22/03/2010. So When user input date range between 24/03/2010 to 26/03/2010 then i am getting no value. Because my tables date field always start from Monday which is 22/03/2010.

I put my code here. Please can anyone help me to solve this problem.

thanks in advance
declare @startdate smalldatetime
set @startdate = '24/03/2010'  
declare @Enddate smalldatetime  
set @Enddate = '26/03/2010'  
declare @StartShift varchar(10)  
set @StartShift = 'PM' 
declare @EndShift varchar(10)  
set @EndShift = 'AM'  
declare @ID varchar(50)  
Set @ID = 'AB'


create table #tshifts     
(shiftid int, shiftrange varchar(50))     
 
--populate shifts table 
insert #tshifts values (1, 'AM')     
insert #tshifts values(2, 'PM')  
insert #tshifts values(3, 'EVE')  
  
declare @ddate datetime           
declare @i1 int, @i2 int
 
set @ddate = @startdate

set @i1 = case @StartShift  
                when 'AM' then 1 
		when 'PM' then 2 
		when 'EVE' then 3 
        end
 
set @i2 = 3
     
while @ddate <= @Enddate    
begin        
	   
	if @ddate = @Enddate	
	begin	
		set @i2 = case @endshift 
				when 'AM' then 1 
		                when 'PM' then 2 
		                when 'EVE' then 3 
		end	
	end

	                                                         
        insert Table1    
        select @ID, @ddate, ShiftRange, case ShiftRange     
                                               when 'AM' then     
                                                        case datename(dw, @ddate)      
                                                                when 'Sunday' then SunAM     
                                                                when 'Monday' then MonAM     
                                                                when 'Tuesday' then TueAM     
                                                                when 'Wednesday' then WedAM     
                                                                when 'Thursday' then ThuAM     
                                                                when 'Friday' then FriAM     
                                                                when 'Saturday' then SatAM     
                                                        end     
                                                when 'PM' then     
                                                        case datename(dw, @ddate)      
                                                                when 'Sunday' then SunPM     
                                                                when 'Monday' then MonPM     
                                                                when 'Tuesday' then TuePM     
                                                                when 'Wednesday' then WedPM     
                                                                when 'Thursday' then ThuPM     
                                                                when 'Friday' then FriPM     
                                                                when 'Saturday' then SatPM     
                                                        end     
                                                when 'EVE' then     
                                                        case datename(dw, @ddate)      
                                                                when 'Sunday' then SunEVE     
                                                                when 'Monday' then MonEVE     
                                                                when 'Tuesday' then TueEVE     
                                                                when 'Wednesday' then WedEVE     
                                                                when 'Thursday' then ThuEVE     
                                                                when 'Friday' then FriEVE     
                                                                when 'Saturday' then SatEVE    
                                                 	end     
                                                end as ShiftHours     
     
        from Table2 a 
        	cross join (select * from #tshifts where (@ddate < @Enddate and shiftid >= @i1) or (@ddate = @Enddate and shiftid <= @i2)) b 
        where ID = @ID and (weekStarting between @Startdate and @Enddate)
        
       
	set @ddate = dateadd(dd, 1, @ddate)	
	if @ddate <= @Enddate 
                set @i1 = case when @i1 + 1 >= 2 then 1 else @i1 + 1 
	end        
          

end   
   
drop table #tshifts

Open in new window

Arif ChowdhuryAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Alfred A.Commented:
Hi,

I think you need to break your Table 1 into two Tables, One Main and One Date Details  for example

Something like this

Table1Main(ID(PK))

Table1Detail(DetailID(PK), ID(FK), MONAM , MONPM,  UESAM,   TUESPM,   WEDAM,   WEDPM,   THUAM,   THUPM,   FRIAM, Dates)

With this, you can then input different dates for ID-1 and not only 22/03/2010

I hope this helps.
0
Alfred A.Commented:
As an example from my previous post your data could be like this

Table1Main

ID(PK)      
ID-1                
ID-2                

Table1Detail

DetailID(PK)    ID(FK)    MONAM      MONPM      TUESAM      TUESPM      WEDAM      WEDPM      THUAM      THUPM      FRIAM      Dates
    1                 ID-1                  4                   3                   3            5            2          4                                              22/03/2010
    2                 ID-1                  2                   1                    3            5            2          1                    2            3       24/03/2010
    3                 ID-2                  4                   3                   3            5            2          4                                              22/03/2010
    4                 ID-2                  2                   1                    3            5            2          1                    2            3       24/03/2010
0
Arif ChowdhuryAuthor Commented:
its not possible do this without breaking the table. because i got lots of data in table1.

thanks
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Alfred A.Commented:
OK.  Can you then add another field in your Table1 that would handle the issue of your dates?
0
Alfred A.Commented:
Example:  (Note, DateID value can be anything as long as related to the primary key).

ID(PK)   DateID   MONAM      MONPM      TUESAM      TUESPM      WEDAM      WEDPM      THUAM      THUPM      FRIAM      Dates
ID-1      ID-1-1        4                   3                   3            5            2          4                                              22/03/2010
ID-1      ID-1-2        4                   3                   3            5            2          4                                              24/03/2010
ID-2      ID-2-1        2                   0                    3            5            2          0                    2            3      22/03/2010

You would just make Table 1 a bit denormalised unfortunately.
0
Alfred A.Commented:
Oh correction, I reversed something, it should be like this with the first two columns.

ID(PK)   DateID
ID-1-1      ID-1
ID-1-2      ID-1
ID-2-1      ID-2
0
Arif ChowdhuryAuthor Commented:
OK I done this.

Thanks
0
Arif ChowdhuryAuthor Commented:
Hi Alfred1:

But in my Table1 date field is always adding Monday as a Dates. But when i need to query that time I need to take startdate and enddate and shift, then i need to find the dates in table1. For example, if startdate is 23/03/2010 and enddate is 25/03/2010 and startshift = 'am' and endshift = 'pm' but dates field in my table1 is 22/03/2010.

Thanks
0
ralmadaCommented:
>>Now I have a problem is that, my DATE FIELD always start From Monday like 22/03/2010. So When user input date range between 24/03/2010 to 26/03/2010 then i am getting no value. Because my tables date field always start from Monday which is 22/03/2010.  <<
So you want to have the @startdate corrected so it always start on the Monday of that week? try like this:

declare @startdate smalldatetime 
set @startdate = '24/03/2010'   
declare @Enddate smalldatetime   
set @Enddate = '26/03/2010'   
declare @StartShift varchar(10)   
set @StartShift = 'PM'  
declare @EndShift varchar(10)   
set @EndShift = 'AM'   
declare @ID varchar(50)   
Set @ID = 'AB' 
 
 
create table #tshifts      
(shiftid int, shiftrange varchar(50))      
  
--populate shifts table  
insert #tshifts values (1, 'AM')      
insert #tshifts values(2, 'PM')   
insert #tshifts values(3, 'EVE')   
   
declare @ddate datetime            
declare @i1 int, @i2 int 
  
set @ddate = dateadd(d, 2-datepart(dw, @startdate), @startdate)
 
set @i1 = case @StartShift   
                when 'AM' then 1  
                when 'PM' then 2  
                when 'EVE' then 3  
        end 
  
set @i2 = 3 
      
while @ddate <= @Enddate     
begin         
            
        if @ddate = @Enddate     
        begin    
                set @i2 = case @endshift  
                                when 'AM' then 1  
                                when 'PM' then 2  
                                when 'EVE' then 3  
                end      
        end 
 
                                                                  
        insert Table1     
        select @ID, @ddate, ShiftRange, case ShiftRange      
                                               when 'AM' then      
                                                        case datename(dw, @ddate)       
                                                                when 'Sunday' then SunAM      
                                                                when 'Monday' then MonAM      
                                                                when 'Tuesday' then TueAM      
                                                                when 'Wednesday' then WedAM      
                                                                when 'Thursday' then ThuAM      
                                                                when 'Friday' then FriAM      
                                                                when 'Saturday' then SatAM      
                                                        end      
                                                when 'PM' then      
                                                        case datename(dw, @ddate)       
                                                                when 'Sunday' then SunPM      
                                                                when 'Monday' then MonPM      
                                                                when 'Tuesday' then TuePM      
                                                                when 'Wednesday' then WedPM      
                                                                when 'Thursday' then ThuPM      
                                                                when 'Friday' then FriPM      
                                                                when 'Saturday' then SatPM      
                                                        end      
                                                when 'EVE' then      
                                                        case datename(dw, @ddate)       
                                                                when 'Sunday' then SunEVE      
                                                                when 'Monday' then MonEVE      
                                                                when 'Tuesday' then TueEVE      
                                                                when 'Wednesday' then WedEVE      
                                                                when 'Thursday' then ThuEVE      
                                                                when 'Friday' then FriEVE      
                                                                when 'Saturday' then SatEVE     
                                                        end      
                                                end as ShiftHours      
      
        from Table2 a  
                cross join (select * from #tshifts where (@ddate < @Enddate and shiftid >= @i1) or (@ddate = @Enddate and shiftid <= @i2)) b  
        where ID = @ID and (weekStarting between dateadd(d, 2-datepart(dw, @startdate), @startdate) and @Enddate) 
         
        
        set @ddate = dateadd(dd, 1, @ddate)      
        if @ddate <= @Enddate  
                set @i1 = case when @i1 + 1 >= 2 then 1 else @i1 + 1  
        end         
           
 
end    
    
drop table #tshifts

Open in new window

0
ralmadaCommented:
sorry I missed the set datefirst 1 there at the begining
set datefirst 1 
declare @startdate smalldatetime  
set @startdate = '24/03/2010'    
declare @Enddate smalldatetime    
set @Enddate = '26/03/2010'    
declare @StartShift varchar(10)    
set @StartShift = 'PM'   
declare @EndShift varchar(10)    
set @EndShift = 'AM'    
declare @ID varchar(50)    
Set @ID = 'AB'  
  
  
create table #tshifts       
(shiftid int, shiftrange varchar(50))       
   
--populate shifts table   
insert #tshifts values (1, 'AM')       
insert #tshifts values(2, 'PM')    
insert #tshifts values(3, 'EVE')    
    
declare @ddate datetime             
declare @i1 int, @i2 int  
   
set @ddate = dateadd(d, 1-datepart(dw, @startdate), @startdate) 
  
set @i1 = case @StartShift    
                when 'AM' then 1   
                when 'PM' then 2   
                when 'EVE' then 3   
        end  
   
set @i2 = 3  
       
while @ddate <= @Enddate      
begin          
             
        if @ddate = @Enddate      
        begin     
                set @i2 = case @endshift   
                                when 'AM' then 1   
                                when 'PM' then 2   
                                when 'EVE' then 3   
                end       
        end  
  
                                                                   
        insert Table1      
        select @ID, @ddate, ShiftRange, case ShiftRange       
                                               when 'AM' then       
                                                        case datename(dw, @ddate)        
                                                                when 'Sunday' then SunAM       
                                                                when 'Monday' then MonAM       
                                                                when 'Tuesday' then TueAM       
                                                                when 'Wednesday' then WedAM       
                                                                when 'Thursday' then ThuAM       
                                                                when 'Friday' then FriAM       
                                                                when 'Saturday' then SatAM       
                                                        end       
                                                when 'PM' then       
                                                        case datename(dw, @ddate)        
                                                                when 'Sunday' then SunPM       
                                                                when 'Monday' then MonPM       
                                                                when 'Tuesday' then TuePM       
                                                                when 'Wednesday' then WedPM       
                                                                when 'Thursday' then ThuPM       
                                                                when 'Friday' then FriPM       
                                                                when 'Saturday' then SatPM       
                                                        end       
                                                when 'EVE' then       
                                                        case datename(dw, @ddate)        
                                                                when 'Sunday' then SunEVE       
                                                                when 'Monday' then MonEVE       
                                                                when 'Tuesday' then TueEVE       
                                                                when 'Wednesday' then WedEVE       
                                                                when 'Thursday' then ThuEVE       
                                                                when 'Friday' then FriEVE       
                                                                when 'Saturday' then SatEVE      
                                                        end       
                                                end as ShiftHours       
       
        from Table2 a   
                cross join (select * from #tshifts where (@ddate < @Enddate and shiftid >= @i1) or (@ddate = @Enddate and shiftid <= @i2)) b   
        where ID = @ID and (weekStarting between dateadd(d, 2-datepart(dw, @startdate), @startdate) and @Enddate)  
          
         
        set @ddate = dateadd(dd, 1, @ddate)       
        if @ddate <= @Enddate   
                set @i1 = case when @i1 + 1 >= 2 then 1 else @i1 + 1   
        end          
            
  
end     
     
drop table #tshifts

Open in new window

0
Arif ChowdhuryAuthor Commented:
Hi ralmada:

its not adding anything in the table

Thanks
0
ralmadaCommented:
sorry it should be
set datefirst 7
declare @startdate smalldatetime  
set @startdate = '24/03/2010'    
declare @Enddate smalldatetime    
set @Enddate = '26/03/2010'    
declare @StartShift varchar(10)    
set @StartShift = 'PM'   
declare @EndShift varchar(10)    
set @EndShift = 'AM'    
declare @ID varchar(50)    
Set @ID = 'AB'  
  
  
create table #tshifts       
(shiftid int, shiftrange varchar(50))       
   
--populate shifts table   
insert #tshifts values (1, 'AM')       
insert #tshifts values(2, 'PM')    
insert #tshifts values(3, 'EVE')    
    
declare @ddate datetime             
declare @i1 int, @i2 int  
   
set @ddate = dateadd(d, 2-datepart(dw, @startdate), @startdate) 
  
set @i1 = case @StartShift    
                when 'AM' then 1   
                when 'PM' then 2   
                when 'EVE' then 3   
        end  
   
set @i2 = 3  
       
while @ddate <= @Enddate      
begin          
             
        if @ddate = @Enddate      
        begin     
                set @i2 = case @endshift   
                                when 'AM' then 1   
                                when 'PM' then 2   
                                when 'EVE' then 3   
                end       
        end  
  
                                                                   
        insert Table1      
        select @ID, @ddate, ShiftRange, case ShiftRange       
                                               when 'AM' then       
                                                        case datename(dw, @ddate)        
                                                                when 'Sunday' then SunAM       
                                                                when 'Monday' then MonAM       
                                                                when 'Tuesday' then TueAM       
                                                                when 'Wednesday' then WedAM       
                                                                when 'Thursday' then ThuAM       
                                                                when 'Friday' then FriAM       
                                                                when 'Saturday' then SatAM       
                                                        end       
                                                when 'PM' then       
                                                        case datename(dw, @ddate)        
                                                                when 'Sunday' then SunPM       
                                                                when 'Monday' then MonPM       
                                                                when 'Tuesday' then TuePM       
                                                                when 'Wednesday' then WedPM       
                                                                when 'Thursday' then ThuPM       
                                                                when 'Friday' then FriPM       
                                                                when 'Saturday' then SatPM       
                                                        end       
                                                when 'EVE' then       
                                                        case datename(dw, @ddate)        
                                                                when 'Sunday' then SunEVE       
                                                                when 'Monday' then MonEVE       
                                                                when 'Tuesday' then TueEVE       
                                                                when 'Wednesday' then WedEVE       
                                                                when 'Thursday' then ThuEVE       
                                                                when 'Friday' then FriEVE       
                                                                when 'Saturday' then SatEVE      
                                                        end       
                                                end as ShiftHours       
       
        from Table2 a   
                cross join (select * from #tshifts where (@ddate < @Enddate and shiftid >= @i1) or (@ddate = @Enddate and shiftid <= @i2)) b   
        where ID = @ID and (weekStarting between dateadd(d, 2-datepart(dw, @startdate), @startdate) and @Enddate)  
          
         
        set @ddate = dateadd(dd, 1, @ddate)       
        if @ddate <= @Enddate   
                set @i1 = case when @i1 + 1 >= 2 then 1 else @i1 + 1   
        end          
            
  
end     
     
drop table #tshifts

Open in new window

0
Arif ChowdhuryAuthor Commented:
Hi ralmada:

Still its not adding anything in the table

Thanks
0
ralmadaCommented:
>.Still its not adding anything in the table <<
so when you do the below do you get 22/03/2010?

declare @startdate smalldatetime  
set @startdate = '24/03/2010'
select dateadd(d, 2-datepart(dw, @startdate), @startdate)

Open in new window

0
Arif ChowdhuryAuthor Commented:
When i run above code i got 2010-03-23 00:00:00
but i changed 2-datepart to 1-datepart and i got 2010-03-22 00:00:00

though it is not adding anything in the table.

Thanks in advance
0
ralmadaCommented:
did you change that in both lines 25 and 84 above?
0
Arif ChowdhuryAuthor Commented:
yes i changed.

Thanks
0
ralmadaCommented:
Honestly I don't understand what is the problem now. Can you please give more details on the example provided in order for me to understand where the issue might be?
So lets say that @startdate = 22/03/2010. Do you get values inserted in Table1?
Also I guess "weekStarting" is a column from Table2 correct?
0
Arif ChowdhuryAuthor Commented:
Sorry. I did mistake. Now its inserted data but if @startdate = 24/03/2010 than its inserted data from 22/03/2010. its not possible to insert data from 24/03/2010.

"weekStarting" is a column from Table1.

Many thanks in advance
0
ralmadaCommented:
yes it inserts data from 22/03/2010 because is the Monday from that week.  I'm not sure what exactly do you want then.
Weekstarting is a column from Table1? It has to be a column from Table2 as well otherwise the above query will not work at all.
0
Arif ChowdhuryAuthor Commented:
basically table2 where i am inserting data from table1.
0
Arif ChowdhuryAuthor Commented:
basically table2 where i am inserting data from table1 based on the users input.
0
ralmadaCommented:
Actually in the above query is the other way around. you are inserting data in table1 from table2
What I don't understand is what you mean by:
>>Now its inserted data but if @startdate = 24/03/2010 than its inserted data from 22/03/2010. its not possible to insert data from 24/03/2010.<<
Can you please clarify

 
0
Arif ChowdhuryAuthor Commented:
If my @startdate = 24/03/2010 than it is inserting data from 22/03/2010, 23/03/2010, 24/03/2010, 25/03/2010 and 26/03/2010. But i want to insert data from 24/03/2010 to 26/03/2010 not from 22/03/2010.

Thanks
0
Arif ChowdhuryAuthor Commented:
Hi ralmada:

If my @startdate = 24/03/2010 than it is inserting data from 22/03/2010, 23/03/2010, 24/03/2010, 25/03/2010 and 26/03/2010. But i want to insert data from 24/03/2010 to 26/03/2010 not from 22/03/2010.

Thanks
0
ralmadaCommented:
so what about like this?
set datefirst 7
declare @startdate smalldatetime  
set @startdate = '24/03/2010'    
declare @Enddate smalldatetime    
set @Enddate = '26/03/2010'    
declare @StartShift varchar(10)    
set @StartShift = 'PM'   
declare @EndShift varchar(10)    
set @EndShift = 'AM'    
declare @ID varchar(50)    
Set @ID = 'AB'  
  
  
create table #tshifts       
(shiftid int, shiftrange varchar(50))       
   
--populate shifts table   
insert #tshifts values (1, 'AM')       
insert #tshifts values(2, 'PM')    
insert #tshifts values(3, 'EVE')    
    
declare @ddate datetime             
declare @i1 int, @i2 int  
   
set @ddate = @startdate
  
set @i1 = case @StartShift    
                when 'AM' then 1   
                when 'PM' then 2   
                when 'EVE' then 3   
        end  
   
set @i2 = 3  
       
while @ddate <= @Enddate      
begin          
             
        if @ddate = @Enddate      
        begin     
                set @i2 = case @endshift   
                                when 'AM' then 1   
                                when 'PM' then 2   
                                when 'EVE' then 3   
                end       
        end  
  
                                                                   
        insert Table1      
        select @ID, @ddate, ShiftRange, case ShiftRange       
                                               when 'AM' then       
                                                        case datename(dw, @ddate)        
                                                                when 'Sunday' then SunAM       
                                                                when 'Monday' then MonAM       
                                                                when 'Tuesday' then TueAM       
                                                                when 'Wednesday' then WedAM       
                                                                when 'Thursday' then ThuAM       
                                                                when 'Friday' then FriAM       
                                                                when 'Saturday' then SatAM       
                                                        end       
                                                when 'PM' then       
                                                        case datename(dw, @ddate)        
                                                                when 'Sunday' then SunPM       
                                                                when 'Monday' then MonPM       
                                                                when 'Tuesday' then TuePM       
                                                                when 'Wednesday' then WedPM       
                                                                when 'Thursday' then ThuPM       
                                                                when 'Friday' then FriPM       
                                                                when 'Saturday' then SatPM       
                                                        end       
                                                when 'EVE' then       
                                                        case datename(dw, @ddate)        
                                                                when 'Sunday' then SunEVE       
                                                                when 'Monday' then MonEVE       
                                                                when 'Tuesday' then TueEVE       
                                                                when 'Wednesday' then WedEVE       
                                                                when 'Thursday' then ThuEVE       
                                                                when 'Friday' then FriEVE       
                                                                when 'Saturday' then SatEVE      
                                                        end       
                                                end as ShiftHours       
       
        from Table2 a   
                cross join (select * from #tshifts where (@ddate < @Enddate and shiftid >= @i1) or (@ddate = @Enddate and shiftid <= @i2)) b   
        where ID = @ID and (weekStarting between dateadd(d, 1-datepart(dw, @startdate), @startdate) and @Enddate)  
          
         
        set @ddate = dateadd(dd, 1, @ddate)       
        if @ddate <= @Enddate   
                set @i1 = case when @i1 + 1 >= 2 then 1 else @i1 + 1   
        end          
            
  
end     
     
drop table #tshifts

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Arif ChowdhuryAuthor Commented:
Many thanks for this great coding.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.