SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to query sql using SQL SMS 2008
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sabercats
Starting Member

USA
7 Posts

Posted - 01/03/2013 :  17:14:47  Show Profile  Reply with Quote
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
2869 Posts

Posted - 01/03/2013 :  17:48:19  Show Profile  Reply with Quote


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

sabercats
Starting Member

USA
7 Posts

Posted - 01/03/2013 :  18:13:27  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/03/2013 :  18:26:11  Show Profile  Reply with Quote
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

USA
7 Posts

Posted - 01/03/2013 :  18:35:44  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/03/2013 :  18:51:21  Show Profile  Reply with Quote
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

USA
7 Posts

Posted - 01/03/2013 :  19:02:10  Show Profile  Reply with Quote

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

jimf
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/03/2013 :  19:22:35  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
2869 Posts

Posted - 01/03/2013 :  19:42:57  Show Profile  Reply with Quote
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

USA
7 Posts

Posted - 01/03/2013 :  19:52:44  Show Profile  Reply with Quote
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

USA
7 Posts

Posted - 01/03/2013 :  19:56:15  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 01/03/2013 :  23:27:36  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000