| Author |
 Topic  |
|
|
sabercats
Starting Member
USA
7 Posts |
 Posted - 01/03/2013 : 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.Name
FROM [dbo].[CheckInOut] I,
[dbo].[Users] U,
[dbo].[Locations] E
WHERE I.ReservationId!=0 AND I.LocationId=E.Id AND U.Id=I.UserId
ORDER BY I.ReservationId
Result
ReservationId Name Date Type Name
321 clyde 2012-12-21 21:20:33.000 CheckIn Tim
905 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 Antoine
1496 danube 2012-12-14 21:57:46.000 CheckIn Tim
1504 danube 2012-12-21 20:40:10.000 CheckOut Tim
1575 clyde 2012-12-18 17:10:28.000 CheckIn David
1582 mississippi 2012-12-28 08:03:05.000 CheckIn Vibhav
I expect it has
ReservationId Name Date Type Name
321 clyde 2012-12-21 21:20:33.000 CheckIn Tim
1575 clyde 2012-12-18 17:10:28.000 CheckIn David
1582 mississippi 2012-12-28 08:03:05.000 CheckIn Vibhav
Thanks |
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 01/03/2013 : 17:48:19
|
SELECT I.ReservationId
,E.Name
,I.Date
,I.Type
,U.Name
FROM [dbo].[CheckInOut] I,
INNER JOIN [dbo].[Users] U ON U.Id=I.UserId
INNER JOIN[dbo].[Locations] E I.LocationId=E.Id
WHERE I.ReservationId <> 0
AND U.Type = 'CheckIn'
ORDER BY I.ReservationId
but I'm thinking you want the last person who checked in? You'll
have to be more specific as to what your criteria are.
Jim
Everyday I learn something that somebody else already knew |
 |
|
|
sabercats
Starting Member
USA
7 Posts |
Posted - 01/03/2013 : 18:13:27
|
Hi Jimf,
I tried
SELECT I.ReservationId
,E.Name
,I.Date
,I.Type
,U.Name
FROM [dbo].[CheckInOut] I
INNER JOIN [dbo].[Users] U ON U.Id=I.UserId
INNER JOIN[dbo].[Locations] E I.LocationId=E.Id
WHERE I.ReservationId <> 0
AND I.Type = 'CheckIn'
ORDER BY I.ReservationId
But it also be the same if i add code WHERE I.ReservationId <> 0
AND 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
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 01/03/2013 : 18:26:11
|
I think this will do it
SELECT
I.ReservationId
,E.Name
,I.Date
,'CheckIn' as [Type]
,U.Name
FROM [dbo].[CheckInOut] I
INNER JOIN [dbo].[Users] U ON U.Id=I.UserId
INNER JOIN[dbo].[Locations] E I.LocationId=E.Id
WHERE I.ReservationId <> 0
AND I.Type = 'CheckIn'
GROUP BY
I.ReservationId
,E.Name
,I.Date
,U.Name
HAVING 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
ORDER BY I.ReservationId
Jim
Everyday I learn something that somebody else already knew |
|
|
|
sabercats
Starting Member
USA
7 Posts |
Posted - 01/03/2013 : 18:35:44
|
quote:
Originally posted by jimf
I think this will do it
SELECT
I.ReservationId
,E.Name
,I.Date
,'CheckIn' as [Type]
,U.Name
FROM [dbo].[CheckInOut] I
INNER JOIN [dbo].[Users] U ON U.Id=I.UserId
INNER JOIN[dbo].[Locations] E I.LocationId=E.Id
WHERE I.ReservationId <> 0
AND I.Type = 'CheckIn'
GROUP BY
I.ReservationId
,E.Name
,I.Date
,U.Name
HAVING 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
ORDER BY I.ReservationId
Jim
Everyday I learn something that somebody else already knew
Same results as :-(
SELECT I.ReservationId
,E.Name
,I.Date
,I.Type
,U.Name
FROM [dbo].[CheckInOut] I
INNER JOIN [dbo].[Users] U ON U.Id=I.UserId
INNER JOIN[dbo].[Locations] E I.LocationId=E.Id
WHERE I.ReservationId <> 0
AND I.Type = 'CheckIn'
ORDER BY I.ReservationId
|
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 01/03/2013 : 18:51:21
|
Oops! Get rid of AND I.Type = 'CheckIn'
Jim
Everyday I learn something that somebody else already knew |
|
|
|
sabercats
Starting Member
USA
7 Posts |
Posted - 01/03/2013 : 19:02:10
|
SELECT
I.ReservationId
,E.Name
,I.Date
,'CheckIn' as [Type]
,U.Name
FROM [dbo].[CheckInOut] I
INNER JOIN [dbo].[Users] U ON U.Id=I.UserId
INNER JOIN[dbo].[Locations] E I.LocationId=E.Id
WHERE I.ReservationId <> 0
GROUP BY
I.ReservationId
,E.Name
,I.Date
,U.Name
HAVING 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
ORDER BY I.ReservationId
Jim, somehow it has the same answer |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
Posted - 01/03/2013 : 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 checkins
SELECT
I.ReservationId
,I.UserID
FROM [dbo].[CheckInOut] I
WHERE I.ReservationId <> 0
GROUP BY
i.ReservationId
,i.UserID
HAVING 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)
Jim
Everyday I learn something that somebody else already knew |
|
|
|
jimf
Flowing Fount of Yak Knowledge
USA
2868 Posts |
|
|
sabercats
Starting Member
USA
7 Posts |
Posted - 01/03/2013 : 19:52:44
|
Table I (has I.ReservationId, I.Location, I.Date, I.Type, I.UserId
321 1 2012-12-21 21:20:33.000 CheckIn 123
905 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 456
1496 5 2012-12-14 21:57:46.000 CheckIn 123
1504 5 2012-12-21 20:40:10.000 CheckOut 123
1575 1 2012-12-18 17:10:28.000 CheckIn 234
1582 4 2012-12-28 08:03:05.000 CheckIn 567
Table E (has E.Id,E.Name)
1 clyde
2 niagara
3 mississippi
4 danube
5 amazon
Table U (has U.Id,U.name)
123 Tim
234 David
345 Antonie
456 Vibhav |
|
|
|
sabercats
Starting Member
USA
7 Posts |
Posted - 01/03/2013 : 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 help
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
Jim
Everyday 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
Flowing Fount of Yak Knowledge
USA
7173 Posts |
Posted - 01/03/2013 : 23:27:36
|
SELECT I.ReservationId
,E.Name
,I.Date
,I.Type
,U.Name
FROM [dbo].[CheckInOut] I
INNER JOIN [dbo].[Users] U ON U.Id = I.UserId
INNER JOIN [dbo].[Locations] E ON I.Location = E.Id
INNER 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.Name
WHERE I.ReservationId <> 0
ORDER BY I.ReservationId |
|
|
| |
Topic |
|
How to query sql using SQL SMS 2008
