Sum through time periods 1

[Khanson82]

Right now I'm getting the sum of unused hours for a time period.. I am wanting to change it to where I get a sum of unused hours for that time period if it is in the future.. If today is the first day of the itineray or somewhere in the middle of it, then I want unused hours from tomorrow-the end of the itinerary period.
Any ideas?





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,
g.county as SomeZipsIn,
g.state as State
from tproviderschedule a with(nolock)
left outer join users b with(nolock) on a.createdby = b.user_id
left outer join IC.resource_hiring c with(nolock) on a.providerid = c.masExamNum
left outer join IC.resource d with(nolock)on c.resourceid = d.id
left outer join IC.zips f with(nolock) on f.zip= left(a.zipcode,5)
left outer join IC.counties g with(nolock) on g.id=f.countyid
left outer join IS.Provider_Availability e with(nolock) on e.masexamnum = a.providerid
where convert(datetime, e.app_date,101) between convert(datetime, a.Scheduledate,101) and convert(datetime, a.ScheduledateEnd,101)
and convert(datetime,a.scheduledateend,101) >= convert(varchar, getdate(),101)
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,g.county,g.state

order by a.providerid

 

[markros]

I'm guessing you may need to adjust your WHERE clause. BTW, why you're using CONVERT function? What is the type of App_Date and ScheduleDate, ScheduleDateEnd fields?

PluralSight Learning Library

 

[drlex]

Not related to your question, but may affect your result set - Surely

...
from tproviderschedule a with(nolock)    
...
left outer join IS.Provider_Availability e 
...

together with

...
where  convert(datetime, e.app_date,101) between 
...

makes it an inner join between tables 'a' and 'e'?

soi là, soi carré

 

[Khanson82]

The innner join will be ok in this case...

Also those fields are date time and the time was just messing up the results, since the date is the only thing needed.

I know its in the where clause, but it seems like an if then else statement and sql server doens't seem to like anything I'm trying..

 

[Rhys666]

If these fields are datetime datatypes, remove the converts around them, they're doing nothing;
[ul]
[li]e.app_date[/li]
[li]a.Scheduledate[/li]
[li]a.ScheduledateEnd[/li]
[/ul]

If you look into it the following code will yield a full datetime result not remove the time element...;

select convert(datetime, current_timestamp, 101)

Try amending the datetime converts;

SELECT CONVERT(DATETIME, CONVERT(VARCHAR(11), CURRENT_TIMESTAMP, 113))
SELECT DATEADD(MILLISECOND, -10, DATEADD(DAY, 1, CONVERT(DATETIME, CONVERT(VARCHAR(11), CURRENT_TIMESTAMP, 113))))

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[markros]

If you want to use date ranges and avoid time portion, use
>=@StartDate and DateFld < @EndDate

Take a look at Aaron Bertrand's blog explaining the problem

The ultimate guide to the datetime datatypes
Bad habits to kick : mis-handling date / range queries


PluralSight Learning Library

 

[Khanson82]

It's weird when I did this above without the converts, I didn't get the right results, so I added the converts and the addition and results were all correct.. I will def. try everything you all are referring to and let you know..

 

[Khanson82]

Sorry guys after looking at it..the convert was in place just for visual aspect, to only pull back the dates.. and not the time..It didn't affect any of my results..Since this is for a report, I could get rid of the Time in crystal, but just did it in the sql to limit the mods in Crystal.

 

[Khanson82]

Since I cleared up the convert part, did anyone have any recommendations for how to get what I was needing..Is it an if then statement or a case statement in the where clause? Thanks again!

 

[Rhys666]

Not sure that's what you want, it seems to me that it is your sum that is conditional, (as you only want the sum of available time from tomorrow going forward), and whats changing not your selection or records...

Rhys

"The trouble with having an open mind, of course, is that people will insist on coming along and trying to put things in it"
Terry Pratchett

 

[Khanson82]

I think you are correct. I only want the sum of the hours from tomorrow to "Travel End" if "appt_Date" is = or in between travel start and travel end..Other wise I want it how it is already..

 

[Khanson82]

I still haven't figured this one out, but I've just noticed that the issue with tables a e becoming an inner join instead of a left join is leaving out records now..
drlex,
How can I fix this? I have tried all types of different formats, but nothing. I'm sure it's something basic though.. Thanks again!

 

[drlex]

drlex,
How can I fix this? I have tried all types of different formats, but nothing. I'm sure it's something basic though.. Thanks again!

If you are referring to the left join becoming an inner join, you need to provide a NULL alternative to the outer table value

That is to say

where  convert(datetime, e.app_date,101) between  ...

becomes

where (e.app_date is NULL or convert(datetime, e.app_date,101) between ... )

soi là, soi carré

 

[Khanson82]

aww.. Thanks again!