Week range to single day

ok guys, i want to hear what you would do in my current situation. i am currently fixing a crystal report, which its source is a stored procedure in SQL Server 2000. The problem here is this.

if the user files a overtime for a week, it would save it per day, well, kinda like this.

idno overtime from overtime to
19960072 2012-07-03 07:00:00 2012-07-06 08:00:00

well, the above statement is shortened, it should have the shift start and end of an employee, time in and time out of an employee and many many more.


this is the sql query used in the report.


select A.*,
L.leave_cde,
L.applied,
L.wpay,
L.reason,
L.dtfrom as [leave_dtfrom],
L.dtto as [leave_dtto],

convert(SMALLDATETIME,convert(varchar,O.otdtfrom,1 01) + ' ' + convert(varchar,O.otstart,108)) as [otdtfrom],
convert(SMALLDATETIME,convert(varchar,O.otdtto,101 ) + ' ' + convert(varchar,O.otend,108)) as [otdtto],
convert(SMALLDATETIME,convert(varchar,O.advdtfrom, 101) + ' ' + convert(varchar,O.advotstart,108)) as [advdtfrom],
convert(SMALLDATETIME,convert(varchar,O.advdtto,10 1) + ' ' + convert(varchar,O.advotend,108)) as [advdtto],
convert(SMALLDATETIME,convert(varchar,O.spldatefro m,101) + ' ' + convert(varchar,O.splotstart,108)) as [spldatefrom],
convert(SMALLDATETIME,convert(varchar,O.spldateto, 101) + ' ' + convert(varchar,O.splotend,108)) as [spldateto],

convert(smalldatetime,D.dyn_value) as [holidaydate],
D.dyn_key as [holidaytype],
D.dyn_name as [holidayname],
E.resigned,
case when AL.al_month is not null then 1 else 0 end as [al]
into #tempA
from attendance_table A
left join leavebalances L on
L.emp_cde=A.emp_cde
and A.ref_no=L.leaveref_no
and cast(convert(varchar,A.shift_from,101) as smalldatetime) between L.dtfrom and L.dtto
left join overtime O on
O.emp_cde=A.emp_cde
and A.otrefno=O.refno
and ( (cast(convert(varchar,A.shift_from,101) as smalldatetime) between O.otdtfrom and O.otdtto)
or (cast(convert(varchar,A.shift_from,101) as smalldatetime) between O.advdtfrom and O.advdtto)
or (cast(convert(varchar,A.shift_from,101) as smalldatetime) between O.spldatefrom and O.spldateto)
)
left join dyntable D on
D.dyn_key like '%holiday%'
and cast(convert(varchar,A.shift_from,101) as smalldatetime)=convert(smalldatetime,D.dyn_value)
left join employee E on
E.emp_cde=A.emp_cde
left join al_maintenance_table AL on
cast(convert(varchar,A.shift_from,101) as smalldatetime) =
CONVERT(smalldatetime, convert(varchar,AL.al_month) + '/' + convert(varchar,AL.al_day) + '/' + convert(varchar,AL.al_year)
)
where --A.div_cde='XO0' and
--A.dept_cde='A10' and
E.div_cde not in ('XO5','OJ0','CL0') and
E.rankdesc not in ('M','') and
E.hired <= @datefrom and
cast(convert(varchar,A.shift_from,101) as smalldatetime) between @datefrom and @dateto
order by A.shift_from

to test it, i used this variables

declare @datefrom as smalldatetime
declare @dateto as smalldatetime

set @datefrom='7/1/2012'
set @dateto='7/31/2012'


what i would like to do is something like this:


idno overtime from overtime to
19960072 2012-07-03 07:00:00 2012-07-03 08:00:00
19960072 2012-07-04 07:00:00 2012-07-04 08:00:00
19960072 2012-07-05 07:00:00 2012-07-05 08:00:00
19960072 2012-07-06 07:00:00 2012-07-06 08:00:00

to make it simple, i would like to have a query which, from a date range eg: 2012-07-03 07:00:00 2012-07-06 08:00:00
to something like the above statement. thanks for any help which you could provide me

Do you think guys this is too difficult? i think so too hahaha so i want to know any answers... please... or do you guys need some other information?