I basically have two tables I'm trying to match up and then sum from one...
Table 1: tproviderschedule (basically looks like this)It is a log for peoples travel schedules..Can be a range of dates or just one date..
Name ID travelstartdate travelenddate etc....
__________________________________________________________
Table 2: Provider_Availability (a log of what people input for their hours available by date)
Name ID Date(one day at a time) Hours(for the day)
____________________________________________________________
So you'll notice, I'll need to sum all of the hours from provider_availability where the traveldates start and end and everything between from travelstartdate, encompasses dates from provider_availability.
Right now the query works great for just itineraries sum hours when it is just one day. I can't get it to sum up any itineraries where the start and end are a range of dates..
select b.login as CreatedBy,d.Fname,d.lname as Lname,a.providerid as ProviderID,sum(e.available)as Unused,convert(varchar,a.Scheduledate,101)as TravelStart,convert(varchar,a.ScheduledateEnd,101) as TravelEnd,a.County,a.State,convert(varchar,a.CreatedDate,101)as CreatedDate,convert(varchar,a.LastEditedDate,101) as LastEditedDate,a.LastEditedBy,a.Description,a.Zipcode
from tproviderschedule a with(nolock)
left outer join users b with(nolock)
on a.createdby = b.user_id
left outer join RH c with(nolock)
on a.providerid = c.masExamNum
left outer join R d with(nolock)
on c.resourceid = d.id
left outer join Provider_Availability e with(nolock)
on e.masexamnum = a.providerid
and convert(varchar,a.Scheduledate,101) = (e.app_date)
and convert(varchar,a.ScheduledateEnd,101) = (e.app_date)
where a.scheduledateend > getdate()
group by b.login,d.Fname,d.lname ,a.providerid ,a.Scheduledate,a.ScheduledateEnd,
a.County,a.State,a.CreatedDate,a.LastEditedDate,a.LastEditedBy,a.Description,a.Zipcode
Table 1: tproviderschedule (basically looks like this)It is a log for peoples travel schedules..Can be a range of dates or just one date..
Name ID travelstartdate travelenddate etc....
__________________________________________________________
Table 2: Provider_Availability (a log of what people input for their hours available by date)
Name ID Date(one day at a time) Hours(for the day)
____________________________________________________________
So you'll notice, I'll need to sum all of the hours from provider_availability where the traveldates start and end and everything between from travelstartdate, encompasses dates from provider_availability.
Right now the query works great for just itineraries sum hours when it is just one day. I can't get it to sum up any itineraries where the start and end are a range of dates..
select b.login as CreatedBy,d.Fname,d.lname as Lname,a.providerid as ProviderID,sum(e.available)as Unused,convert(varchar,a.Scheduledate,101)as TravelStart,convert(varchar,a.ScheduledateEnd,101) as TravelEnd,a.County,a.State,convert(varchar,a.CreatedDate,101)as CreatedDate,convert(varchar,a.LastEditedDate,101) as LastEditedDate,a.LastEditedBy,a.Description,a.Zipcode
from tproviderschedule a with(nolock)
left outer join users b with(nolock)
on a.createdby = b.user_id
left outer join RH c with(nolock)
on a.providerid = c.masExamNum
left outer join R d with(nolock)
on c.resourceid = d.id
left outer join Provider_Availability e with(nolock)
on e.masexamnum = a.providerid
and convert(varchar,a.Scheduledate,101) = (e.app_date)
and convert(varchar,a.ScheduledateEnd,101) = (e.app_date)
where a.scheduledateend > getdate()
group by b.login,d.Fname,d.lname ,a.providerid ,a.Scheduledate,a.ScheduledateEnd,
a.County,a.State,a.CreatedDate,a.LastEditedDate,a.LastEditedBy,a.Description,a.Zipcode
