Struggling with what should be a simple query!

Question

Struggling with what should be a simple query!

scout7-500377

SSC-Addicted

Points: 408
More actions
December 2, 2014 at 3:31 am

#301581

Hi, I am struggling to find an efficient solution to the following problem;
I have a history table of user events, I need to be able select a list of events grouped by user and event where a user does not, and has never had, an event greater the event passed in the parameter. For example;
UserID Status Order Date Created Row Number
100 1 1 2014-11-20 1
100 2 2 2014-11-21 2
100 3 3 2014-11-22 3
100 4 4 2014-11-23 4
100 5 5 2014-11-24 5
200 1 1 2014-11-20 1
200 2 2 2014-11-21 2
200 3 3 2014-11-22 3
What I need to do is to be able to select all status changes that occurred between 2014-11-20 and 2014-11-23 that have a status of 3 BUT that do not have status higher than 3 regardless of the date.
This should be simple I am sure but I just can't figure out a cleaner solution than using temp tables.
Anybody have any suggestions for a clean solution to this? Would I be better off going the CTE route?

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

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

Neil Burton SSC-Insane Points: 22974 More actions · Answer 1

If you can post some sample data and examples of what you've already tried it'll be easier to see want you want and give you an answer. Have a look at the article in my sig about how best to do that.

On two occasions I have been asked, "Pray, Mr. Babbage, if you put into the machine wrong figures, will the right answers come out?" ... I am not able rightly to apprehend the kind of confusion of ideas that could provoke such a question.
—Charles Babbage, Passages from the Life of a Philosopher

How to post a question to get the most help http://www.sqlservercentral.com/articles/Best+Practices/61537

imex Ten Centuries Points: 1234 More actions · Answer 2

Hi,

Try something like this:

with CTE_User as

(

select

UserID

from MyTable

group by

UserID

having

max(Status) = 3

)

select

t.*

from CTE_User as c

inner join MyTable as t

on t.UserID = c.UserID

where

t.DateCreated between @DateBegin an @DateEnd

Hope this helps.

https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos

David Burrows SSC Guru Points: 65141 More actions · Answer 3

imex (12/2/2014)
Hi,
Try something like this:
with CTE_User as
(
select
UserID
from MyTable
group by
UserID
having
max(Status) = 3
)
select
t.*
from CTE_User as c
inner join MyTable as t
on t.UserID = c.UserID
where
t.DateCreated between @DateBegin an @DateEnd
Hope this helps.

Should be max(Status) <= 3

in case some users have only status 1 or 2!!!

Far away is close at hand in the images of elsewhere.
Anon.