Date in 1 table <=> from/to date of another Table

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

General SQL Server Forums

New to SQL Server Programming

Date in 1 table <=> from/to date of another Table

Author

Topic

flamblaster
Constraint Violating Yak Guru

384 Posts

Posted - 2008-12-02 : 02:30:48

Folks, I would like to see if anyone has suggestions for the following issue I have:

EX:
Tables: Work
Absent

Fields: Work.EmployeeId
Work.WorkName
Work.ServiceDate (this is data type int - format = YYYYMMDD)
Absent.EmployeeId
Absent.AbsenceType
Absent.FromDate (this is data type int - format = YYYYMMDD)
Absent.ToDate (this is data type int - format = YYYYMMDD)

[Date] is a parameter style in the interface I use.

DATA:

Work.EmployeeId*****Work.WorkName*****Work.ServiceDate
10*******************Shuttle***********20081201
10*******************Loop**************20081202
10*******************North*************20081203
10*******************Shuttle***********20081204

Absent.EmployeeId*****Absent.AbsenceType*****Absent.FromDate*****Absent.ToDate
100*******************Sick*******************20081130************0
110*******************Vacation***************20081204************20081210

The following query Works very well if I only need to see one day at a time:

Select EmployeeId, WorkName as 'Work or Absence'
FROM Work
Where ServiceDate=[Date]

UNION

Select EmployeeId, AbsenceType as 'Work or Absence'
From Absent
Where FromDate <= [Date] AND (ToDate >= [Date] OR ToDate=0 OR ToDate IS NULL)

If in the parameter, [Date] I select 20081201, these are the
RESULTS:

EmployeeId*****Work or Absence
10*************Shuttle
100************Sick

What I would like to be able to do is run a similar query but by date range of From and To
to return the following results:

For Example, If From=20081130
and To=20081205:

EmployeeId*****Work or Absence*****Date of Work or Absence
10*************Shuttle*************20081201
10*************Loop****************20081202
10*************North***************20081203
10*************Shuttle*************20081204
100************Sick****************20081130
100************Sick****************20081201
100************Sick****************20081202
100************Sick****************20081203
100************Sick****************20081204
100************Sick****************20081205
110************Vacation************20081204
110************Vacation************20081205

I've tried to modify the query, but to no avail. There is no relationship between the work and absent tables
besides employeeId. I have tried casting the Work.ServiceDate, Absent.FromDate and Absent.ToDate columns to datatype datetime but I still am not
sure how a DateDiff or a DateAdd function would help in this situation. Maybe get the difference between the From/To date then use a case expression to separate each day in between?

As I usually post, I can not create functions, drop tables, declare cursor with the query writer I have access to.

If anyone has suggestions, I'd be very appreciative.

Thanks!
Craig

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 02:45:30

Select EmployeeId, WorkName as 'Work or Absence',CAST([ServiceDate] AS datetime)
FROM Work
Where ServiceDate>=@startDate
and ServiceDate<DATEADD(dd,1,@EndDate)

UNION

Select EmployeeId, AbsenceType as 'Work or Absence',
DATEADD(dd,v.number,CAST(a.FromDate AS datetime))
From Absent a
CROSS JOIN master..spt_values v
Where v.type='p'
AND a.FromDate >= @StartDate
AND DATEADD(dd,v.number,a.FromDate)<=CASE WHEN @EndDate<CAST(a.ToDate AS datetime) OR a.ToDate=0 THEN @EndDate ELSE CAST(a.ToDate AS datetime) END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-02 : 03:39:06

please disregard my prev post. try below soln



DECLARE @Work table
(
EmployeeId int,
WorkName varchar(20),
ServiceDate int
)

DECLARE @Absent table
(
EmployeeId int,
AbsenceType varchar(20),
FromDate int,
ToDate int
)

DECLARE @Startdate datetime,@enddate datetime
select @Startdate='20081130',@enddate='20081205'


INSERT INTO @Work
select 10,'Shuttle',20081201 union all
select 10,'Loop',20081202 union all
select 10,'North',20081203 union all
select 10,'Shuttle',20081204


INSERT INTO @Absent
--Absent.EmployeeId*****Absent.AbsenceType*****Absent.FromDate*****Absent.ToDate
SELECT 100,'Sick',20081130,0 union all
SELECT 110,'Vacation',20081204,20081210


Select EmployeeId, WorkName as 'Work or Absence',cast(convert(varchar(20),[ServiceDate]) AS datetime)
FROM @Work
Where cast(convert(varchar(20),[ServiceDate]) AS datetime)>=@startDate
and cast(convert(varchar(20),[ServiceDate]) AS datetime)<DATEADD(dd,1,@EndDate)

UNION

Select EmployeeId, AbsenceType as 'Work or Absence',
DATEADD(dd,v.number,cast(convert(varchar(20),a.FromDate) AS datetime))
From @Absent a
CROSS JOIN master..spt_values v
Where v.type='p'
AND cast(convert(varchar(20),a.FromDate) AS datetime) >= @StartDate 
AND DATEADD(dd,v.number,cast(convert(varchar(20),a.FromDate) AS datetime))<=CASE WHEN a.ToDate=0 OR @EndDate<cast(convert(varchar(20),a.ToDate) AS datetime)  THEN @EndDate ELSE cast(convert(varchar(20),a.ToDate) AS datetime) END

output
------------------------------------------
EmployeeId	Work or Absence	date
10	Loop	2008-12-02 00:00:00.000
10	North	2008-12-03 00:00:00.000
10	Shuttle	2008-12-01 00:00:00.000
10	Shuttle	2008-12-04 00:00:00.000
100	Sick	2008-11-30 00:00:00.000
100	Sick	2008-12-01 00:00:00.000
100	Sick	2008-12-02 00:00:00.000
100	Sick	2008-12-03 00:00:00.000
100	Sick	2008-12-04 00:00:00.000
100	Sick	2008-12-05 00:00:00.000
110	Vacation	2008-12-04 00:00:00.000
110	Vacation	2008-12-05 00:00:00.000

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources