please solve this t-sql query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

please solve this t-sql query

Author

Topic

ourtime545
Starting Member

11 Posts

Posted - 2011-05-02 : 17:18:28

Consider this as an audit table, in which the ticket status keeps
changing for all passengers:

Passenger ID Passenger Name Ticket Status Operation Date

1 Robert Pending Anydate
1 Robert Waiting List Anydate
1 Robert Confirmation Anydate
2 John Pending Anydate
2 John Confirmation Anydate
3 James Confirmation Anydate
4 Eric Waiting List Anydate
5 Joseph Confirmation Anydate

I want query in which I can pull, following result:
• If ticket status= 'pending', then I want ‘pending’ value row only irrespective of any other status for particular passenger. For eg.
Robert and john has pending in its ticket status so in result I want

1, Robert, Pending, Date(Just one row out of three)
2, john, pending, date(Just one out of two rows)

• If ticket status='waiting' list and if doesn’t have pending then I want 'waiting list' status.

Example:
4,eric, waiting list, date

• If ticket status= 'confirmation' then I want ‘confirmation’ if it doesn’t have pending, then I want confirmation as value.

Example:
5, joseph, confirmation, date

So final result for above table I want is:
1, Robert, Pending, Date(Just one row out of three)
2, john, pending, date(Just one out of two rows)
3,james, confirmation, date
4,eric, waiting list, date
5, joseph, confirmation, date

So above 5 rows should be displayed because there are 5 customers

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-02 : 17:41:57

Show us what you have so far as you appear to just be pasting in your homework without showing any effort.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

ourtime545
Starting Member

11 Posts

Posted - 2011-05-03 : 08:57:44

with cte
as (
SELECT DISTINCT row_id, passenger_id, passenger_name, ticket_status, operation_date from Production.dbo.audit
where ticket_status in ('Pending','Waiting List','Confirmation')
AND (ticket_status = 'Occurred')--and CONVERT(VARCHAR, OPERATION_DT,101) between '02/01/2011' and '04/21/2011'
)

select Passenger_id, passenger_name, ticket_status, operation_date
from cte
WHERE cte.ROW_ID IN
(select cte.ROW_ID from cte
where ticket_status= case when ticket_status='Pending' and ticket_status='Confirmation' and ticket_status='Waiting List' then 'Pending'
when ticket_status='Confirmed' and ticket_status <>'Pending' then 'Confirmed'
when ticket_status='Waiting List' and ticket_status <>'Pending' then 'Waiting List'
else null
end
GROUP BY passenger_id, passenger_name

ourtime545
Starting Member

11 Posts

Posted - 2011-05-03 : 09:01:28

In past I had to pick most recent operation date then i used to use max(operation_dt) and i used to pick one from three.

But in this case i need to pick based on their status which is nt date, so how can i pick that. The logic which i used and have posted here is giving null value to me. and 0 rows are returned

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-03 : 09:09:12

maybe
select *
from
(
select *, seq = ROW_NUMBER() over (partition by passenger_id order by case when status= 'pending' then 1 when status='waiting' then 2 else 3 end, date desc)
from Production.dbo.audit
) a
where seq = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ourtime545
Starting Member

11 Posts

Posted - 2011-05-03 : 09:42:51

I got the logic, I think I will be able to do this. Thanks for the logic. Actually i am little bit weak in using 'Partition by'

Thank you for your help. I am really impressed with this website and looking forward to help others with watever knowledge i have though I am a rookie.

Thanks

ourtime545
Starting Member

11 Posts

Posted - 2011-05-03 : 15:57:10

This logic just works if there is waiting in the status. if there is no waiting and confirmation in the status then i want confirmation to be seen. how can i do that

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-03 : 16:07:47

The order by numbers the rows in order of pending, waiting, everything else (confirmation)
If there is a pending it will be given seq = 1
No pending but wating then waiting is given seq = 1
No pending or waiting, only confirmation then that is given seq = 1

Only the row with seq = 1 is returned - that will be the row in priotity order pending, waiting, confirmation.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

ourtime545
Starting Member

11 Posts

Posted - 2011-05-03 : 16:42:41

Thanks a lot . . @nigelrivett

I got it. Was confused a bit but now its all clear and I got what i wanted. .. You are indeed a genius . .

Thanks once again . .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources