Need some query help.

Question

Need some query help.

tgomez

Grasshopper

Points: 14
More actions
May 22, 2013 at 1:57 pm

#275390

I need to figure out how to do a group by on this query by number of days between start and end date. Can someone point me in the right directions. Thanks!
select plantfilenumber,
(select min(cc.lasteventdate) from tbltitlevault_history c inner join tbltitlevault cc on c.tvid = cc.tvid where c.tvid = a.tvid and c.eventid = '1') as startDate,
(select max(dd.lasteventdate) from tbltitlevault_history d inner join tbltitlevault dd on d.tvid = dd.tvid where d.tvid = a.tvid and d.eventid = '6') as endDate
from tbltitlevault a inner join tbltitlevault_history b
on a.tvid = b.tvid

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Evil Kraig F SSC Guru Points: 100851 More actions · Answer 1

Need more data. What are you trying to group it by? All you're showing us is what appears to be half a query with the MIN/MAX. What are you trying to organize it with?

- Craig Farrell

Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA

tgomez Grasshopper Points: 14 More actions · Answer 2

It's ok, I found another way to do it. Thanks!

select COUNT(tvid), case when

datediff(d, requested_date, completed_date) = 0 then 'same day' else case when datediff(d, requested_date, completed_date) = 1 then '1' else case when datediff(d, requested_date, completed_date) = 2 then '2' else case when datediff(d, requested_date, completed_date) = 3 then '3' else case when datediff(d, requested_date, completed_date) > 3 then 'more than 3'

end end end end end as x

from tblTitleVault where completed_date is not null and completed_date <> '1900-01-01 00:00:00'

and completed_date > requested_date

group by case when

datediff(d, requested_date, completed_date) = 0 then 'same day' else case when datediff(d, requested_date, completed_date) = 1 then '1' else case when datediff(d, requested_date, completed_date) = 2 then '2' else case when datediff(d, requested_date, completed_date) = 3 then '3' else case when datediff(d, requested_date, completed_date) > 3 then 'more than 3'

end end end end end

Jeff Moden SSC Guru Points: 1003857 More actions · Answer 3

Please see the first link in my signature line for future posts. It will help you get coded answers much more quickly because people like to test their solutions before posting the answers. Post with no readily consumable data are sometimes never answered because people gravitate towards those that have.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Phil Parkin SSC Guru Points: 246693 More actions · Answer 4

Also, for future info, I've reformatted this (thanks Redgate, but there are free alternatives) and put it in tags. See how much easier it is to read:

select count(tvid)

,case when datediff(d, requested_date, completed_date) = 0 then 'same day'

else case when datediff(d, requested_date, completed_date) = 1 then '1'

else case when datediff(d, requested_date, completed_date) = 2 then '2'

else case when datediff(d, requested_date, completed_date) = 3 then '3'

else case when datediff(d, requested_date, completed_date) > 3

then 'more than 3'

end

end as x

from tblTitleVault

where completed_date is not null

and completed_date <> '1900-01-01 00:00:00'

and completed_date > requested_date

group by case when datediff(d, requested_date, completed_date) = 0 then 'same day'

else case when datediff(d, requested_date, completed_date) = 1 then '1'

else case when datediff(d, requested_date, completed_date) = 2 then '2'

else case when datediff(d, requested_date, completed_date) = 3 then '3'

else case when datediff(d, requested_date, completed_date) > 3

then 'more than 3'

end

This highlighted an issue - this level of nesting made me recoil, being honest.:hehe:

Your case expressions can be simplified - something like this:

declare @requested_date date = '20130101'

declare @completed_date date = '20130101'

select case datediff(d, @requested_date, @completed_date)

when 0 then 'same day'

when 1 then '1'

when 2 then '2'

when 3 then '3'

else 'more than 3'

end as x

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.