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
 General SQL Server Forums
 New to SQL Server Programming
 How to query sql using SQL SMS 2008

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.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
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.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

[/code]

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
Go to Top of Page

sabercats
Starting Member

7 Posts

Posted - 2013-01-03 : 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.
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-03 : 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
Go to Top of Page

sabercats
Starting Member

7 Posts

Posted - 2013-01-03 : 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
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-03 : 18:51:21
Oops! Get rid of AND I.Type = 'CheckIn'

Jim

Everyday I learn something that somebody else already knew
Go to Top of Page

sabercats
Starting Member

7 Posts

Posted - 2013-01-03 : 19:02:10
[code]
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
[/code]

Jim, somehow it has the same answer
Go to Top of Page

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 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
Go to Top of Page

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2013-01-03 : 19:42:57
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
Go to Top of Page

sabercats
Starting Member

7 Posts

Posted - 2013-01-03 : 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
Go to Top of Page

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 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.
Go to Top of Page

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.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[/code]
Go to Top of Page
   

- Advertisement -