How to generate a date for the given time?

Question

Using VB.NET and SQL Server 2005

I want to generate a date according to the given Outtime,

Am entering the value like

ID Date Intime Outtime Holiday

001 23-02-2009 08:00:00 17:00:00 no
001 24-02-2009 17:00:00 08:00:00 no
001 25-02-2009 10:00:00 16:00:00 no
001 26-02-2009 18:00:00 20:00:00 no
001 27-02-2009                   yes

Expected Output

Table1

ID Date Intime Outtime DateIn Dateout

001 23-02-2009 08:00:00 17:00:00 23-02-2009 23-02-2009
001 24-02-2009 17:00:00 08:00:00 24-02-2009 25-02-2009
001 25-02-2009 10:00:00 16:00:00 25-02-2009 25-02-2009
001 26-02-2009 18:00:00 20:00:00 26-02-2009 27-02-2009
001 27-02-2009     -        -        -         -

…,

From the above table, DateIn column value should display same date column value, But Dateout column should display according to the Outtime.

In a first row – Intime is 08:00:00 and Outtime is 17:00:00, So Dateout should display the same date
In a second row – Intime is 17:00:00 and Outtime is 08:00:00, So DateOut should display the next day date.
In a third row – Intime is 10:00:00 and Outtime is 16:00:00, so Dateout should display the same date
In a fourth row – Intime is 18:00:00 and Outtime is 21:00:00, so Dateout should display the next day date.
In a fifth row – Holiday column value is yes, so it should not display any value in DateIn and DateOut

Dateout column should compare the Intime and Outtime value, if Outtime is less than Intime, Dateout column should display the next day date.

If Holiday = Yes then It should display as a blank column

For the above condition, How to make a sql query?

Whether it is possible in vb.net, which is faster sql query or vb.net code?

Need SQL Query or VB.NET code for the above condition.

Answer 1

You could use a CASE statement to add a day when OutTime is smaller than InTime, or display a blank date when it's a holiday:

select case 
    when holiday = 'yes' then ''
    when OutTime < InTime then DateAdd(day,1,[Date])
    else [Date]
    end as DateOut

Answer 2

I'll suggest:

SELECT t.id,
       t.date_column,
       t.intime,
       t.outtime,
       t.date AS datein,
       CASE
         WHEN t.outime < t.intime THEN
           DATEADD(dd, 1, t.date)
         ELSE
           t.date
       END AS dateout
  FROM TABLE t
 WHERE t.holiday = 'no'
UNION ALL
SELECT h.id,
       h.date_column,
       NULL,
       NULL,
       NULL,
       NULL
  FROM TABLE h
 WHERE h.holiday = 'yes'
ORDER BY date_column

...but there isn't any way to know if the outtime is really spanning into the next day (or more). IE: intime of 1800 and outtime of 2100 could mean the next day.

Answer 3

This seems to work (note that my dates are in US format, since that's where I am :-) ):

create table times
   (
   Id             integer,
   stDate         datetime,
   InTime         datetime,
   OutTime        datetime,
   Holiday        varchar(3)
   )
go

insert into times values (001, '02-23-2009', '08:00:00', '17:00:00', 'no')
insert into times values (001, '02-24-2009', '17:00:00', '08:00:00', 'no')
insert into times values (001, '02-25-2009', '10:00:00', '16:00:00', 'no')
insert into times values (001, '02-26-2009', '21:00:00', '20:00:00', 'no')
insert into times values (001, '02-27-2009', null, null, 'yes')
go

select * from times
go

select
   t.Id,
   stDate,
   InTime,
   OutTime,
   case
      when Holiday = 'no' then stDate
      else null
   end       DateIn,
   case
      when InTime > OutTime and Holiday = 'no' then stDate + 1
      when InTime < OutTime and Holiday = 'no' then stDate
      else null
   end       DateOut
from
   times t

Results:

Id         stDate                  InTime                  OutTime                 DateIn                  DateOut                 
---------- ----------------------- ----------------------- ----------------------- ----------------------- ----------------------- 
1          2009-02-23 00:00:00.000 1900-01-01 08:00:00.000 1900-01-01 17:00:00.000 2009-02-23 00:00:00.000 2009-02-23 00:00:00.000 
1          2009-02-24 00:00:00.000 1900-01-01 17:00:00.000 1900-01-01 08:00:00.000 2009-02-24 00:00:00.000 2009-02-25 00:00:00.000 
1          2009-02-25 00:00:00.000 1900-01-01 10:00:00.000 1900-01-01 16:00:00.000 2009-02-25 00:00:00.000 2009-02-25 00:00:00.000 
1          2009-02-26 00:00:00.000 1900-01-01 21:00:00.000 1900-01-01 20:00:00.000 2009-02-26 00:00:00.000 2009-02-27 00:00:00.000 
1          2009-02-27 00:00:00.000 NULL                    NULL                    NULL                    NULL                    

5 Row(s) affected

Ron