Author |
Topic |
sabercats
Starting Member
7 Posts |
Posted - 2013-01-03 : 17:14:47
|
How do you write query to collect data which has not checkout yet?SELECT I.ReservationId ,E.Name ,I.Date ,I.Type ,U.NameFROM [dbo].[CheckInOut] I,[dbo].[Users] U, [dbo].[Locations] EWHERE I.ReservationId!=0 AND I.LocationId=E.Id AND U.Id=I.UserIdORDER BY I.ReservationId ResultReservationId Name Date Type Name321 clyde 2012-12-21 21:20:33.000 CheckIn Tim905 niagara 2012-12-17 21:56:28.000 CheckIn David 905 niagara 2012-12-17 21:56:30.000 CheckOut David 1391 mississippi 2012-12-14 16:18:14.000 CheckIn David 1391 mississippi 2012-12-14 16:18:18.000 CheckOut David 1416 mississippi 2012-12-15 09:09:52.000 CheckIn Antoine 1416 mississippi 2012-12-17 13:09:04.000 CheckOut Antoine1496 danube 2012-12-14 21:57:46.000 CheckIn Tim1504 danube 2012-12-21 20:40:10.000 CheckOut Tim1575 clyde 2012-12-18 17:10:28.000 CheckIn David1582 mississippi 2012-12-28 08:03:05.000 CheckIn Vibhav I expect it hasReservationId Name Date Type Name321 clyde 2012-12-21 21:20:33.000 CheckIn Tim1575 clyde 2012-12-18 17:10:28.000 CheckIn David1582 mississippi 2012-12-28 08:03:05.000 CheckIn Vibhav Thanks |
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-03 : 17:48:19
|
[code]SELECT I.ReservationId ,E.Name ,I.Date ,I.Type ,U.NameFROM [dbo].[CheckInOut] I,INNER JOIN [dbo].[Users] U ON U.Id=I.UserIdINNER JOIN[dbo].[Locations] E I.LocationId=E.IdWHERE I.ReservationId <> 0AND U.Type = 'CheckIn'ORDER BY I.ReservationId[/code]but I'm thinking you want the last person who checked in? You'llhave to be more specific as to what your criteria are.JimEveryday I learn something that somebody else already knew |
|
|
sabercats
Starting Member
7 Posts |
Posted - 2013-01-03 : 18:13:27
|
Hi Jimf,I triedSELECT I.ReservationId ,E.Name ,I.Date ,I.Type ,U.NameFROM [dbo].[CheckInOut] IINNER JOIN [dbo].[Users] U ON U.Id=I.UserIdINNER JOIN[dbo].[Locations] E I.LocationId=E.IdWHERE I.ReservationId <> 0AND I.Type = 'CheckIn'ORDER BY I.ReservationId But it also be the same if i add code WHERE I.ReservationId <> 0AND I.Type = 'CheckIn'If same Reservation ID but it had Type CheckIn AND CheckOut; then don't include it. I only need which Type=CheckIn AND Dont CheckOut yet. How we do that?Thanks. |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-03 : 18:26:11
|
I think this will do itSELECT I.ReservationId ,E.Name ,I.Date ,'CheckIn' as [Type] ,U.NameFROM [dbo].[CheckInOut] IINNER JOIN [dbo].[Users] U ON U.Id=I.UserIdINNER JOIN[dbo].[Locations] E I.LocationId=E.IdWHERE I.ReservationId <> 0AND I.Type = 'CheckIn'GROUP BY I.ReservationId ,E.Name ,I.Date ,U.NameHAVING SUM(CASE WHEN I.TYPE = 'CheckIn' THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN I.TYPE = 'CheckOut' THEN 1 ELSE 0 END) = 0ORDER BY I.ReservationIdJim Everyday I learn something that somebody else already knew |
|
|
sabercats
Starting Member
7 Posts |
Posted - 2013-01-03 : 18:35:44
|
quote: Originally posted by jimf I think this will do itSELECT I.ReservationId ,E.Name ,I.Date ,'CheckIn' as [Type] ,U.NameFROM [dbo].[CheckInOut] IINNER JOIN [dbo].[Users] U ON U.Id=I.UserIdINNER JOIN[dbo].[Locations] E I.LocationId=E.IdWHERE I.ReservationId <> 0AND I.Type = 'CheckIn'GROUP BY I.ReservationId ,E.Name ,I.Date ,U.NameHAVING SUM(CASE WHEN I.TYPE = 'CheckIn' THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN I.TYPE = 'CheckOut' THEN 1 ELSE 0 END) = 0ORDER BY I.ReservationIdJim Everyday I learn something that somebody else already knew
Same results as :-(SELECT I.ReservationId ,E.Name ,I.Date ,I.Type ,U.NameFROM [dbo].[CheckInOut] IINNER JOIN [dbo].[Users] U ON U.Id=I.UserIdINNER JOIN[dbo].[Locations] E I.LocationId=E.IdWHERE I.ReservationId <> 0AND I.Type = 'CheckIn'ORDER BY I.ReservationId |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-03 : 18:51:21
|
Oops! Get rid of AND I.Type = 'CheckIn'JimEveryday I learn something that somebody else already knew |
|
|
sabercats
Starting Member
7 Posts |
Posted - 2013-01-03 : 19:02:10
|
[code]SELECT I.ReservationId ,E.Name ,I.Date ,'CheckIn' as [Type] ,U.NameFROM [dbo].[CheckInOut] IINNER JOIN [dbo].[Users] U ON U.Id=I.UserIdINNER JOIN[dbo].[Locations] E I.LocationId=E.IdWHERE I.ReservationId <> 0GROUP BY I.ReservationId ,E.Name ,I.Date ,U.NameHAVING SUM(CASE WHEN I.TYPE = 'CheckIn' THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN I.TYPE = 'CheckOut' THEN 1 ELSE 0 END) = 0ORDER BY I.ReservationId[/code]Jim, somehow it has the same answer |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
Posted - 2013-01-03 : 19:22:35
|
Sorry, I'm not good at this without sample data. The query will probably have to be done in 2 parts, since the data of checkin and checkout will always be different. If you could provide some sample data from each table, it'd be easier to get you the right answer. DOes this at least get the right checkinsSELECT I.ReservationId ,I.UserIDFROM [dbo].[CheckInOut] IWHERE I.ReservationId <> 0GROUP BY i.ReservationId ,i.UserIDHAVING SUM(CASE WHEN I.TYPE = 'CheckIn' THEN 1 ELSE 0 END) > 0 AND SUM(CASE WHEN I.TYPE = 'CheckOut' THEN 1 ELSE 0 END) = 0)JimEveryday I learn something that somebody else already knew |
|
|
jimf
Master Smack Fu Yak Hacker
2875 Posts |
|
sabercats
Starting Member
7 Posts |
Posted - 2013-01-03 : 19:52:44
|
Table I (has I.ReservationId, I.Location, I.Date, I.Type, I.UserId321 1 2012-12-21 21:20:33.000 CheckIn 123905 2 2012-12-17 21:56:28.000 CheckIn 234 905 2 2012-12-17 21:56:30.000 CheckOut 234 1391 3 2012-12-14 16:18:14.000 CheckIn 234 1391 4 2012-12-14 16:18:18.000 CheckOut 234 1416 4 2012-12-15 09:09:52.000 CheckIn 456 1416 4 2012-12-17 13:09:04.000 CheckOut 4561496 5 2012-12-14 21:57:46.000 CheckIn 1231504 5 2012-12-21 20:40:10.000 CheckOut 1231575 1 2012-12-18 17:10:28.000 CheckIn 2341582 4 2012-12-28 08:03:05.000 CheckIn 567Table E (has E.Id,E.Name)1 clyde2 niagara3 mississippi4 danube5 amazonTable U (has U.Id,U.name)123 Tim234 David345 Antonie456 Vibhav |
|
|
sabercats
Starting Member
7 Posts |
Posted - 2013-01-03 : 19:56:15
|
quote: Originally posted by jimf That's your desired result. Sample data would be data that is in each table that will give you the desired result. This link will helphttp://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspxJimEveryday I learn something that somebody else already knew
Sorry Jim, I will get back to you tomorrow. I gotta go to pick up my kid. Thanks. |
|
|
sodeep
Master Smack Fu Yak Hacker
7174 Posts |
Posted - 2013-01-03 : 23:27:36
|
[code]SELECT I.ReservationId ,E.Name ,I.Date ,I.Type ,U.NameFROM [dbo].[CheckInOut] IINNER JOIN [dbo].[Users] U ON U.Id = I.UserIdINNER JOIN [dbo].[Locations] E ON I.Location = E.IdINNER JOIN ( Select B.Name,Count(distinct Type)CNT from [dbo].[Locations] A inner join [dbo].[Locations] B ON A.Location = B.Id Group by B.Name Having Count(distinct TYpe) = 1)T on T.Name = E.NameWHERE I.ReservationId <> 0ORDER BY I.ReservationId[/code] |
|
|
|