Select group question - 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)

Select group question

Author

Topic

baze7
Yak Posting Veteran

58 Posts

Posted - 2011-10-03 : 14:48:39

I am trying to get 1 return for this but not sure how. In this table there are miltiple transactions per emp_num because of trans_type. The trans type of 1 for clock in, 2 for clock out, 3 for lunch out and 4 for lunch in. So for a given day there could be 4 transcations. What I want it only to return the last transaction and see what the trans_type is.

SELECT
MAX(trans_num)
,trans_type
,emp_num
FROM dcta

where trans_date = '10/03/2011'
group by trans_type,emp_num

thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-03 : 15:11:59

[code]
SELECT d.trans_num
,d.trans_type
,d.emp_num
FROM dcta d
INNER JOIN (SELECT emp_num,trans_date,MAX(trans_num) AS last
FROM dcta
GROUP BY emp_num,trans_date)d1
On d1.emp_num = d.emp_num
AND d1.trans_date = d.trans_date
AND d1.last = d.trans_num
[/code]

i hope your trans_date doesnt have timepart

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

m_imran18
Starting Member

14 Posts

Posted - 2011-10-03 : 15:19:14

Here is the sample data.

Drop table dcta
GO
Create table dcta
(trans_num int identity(1,1) ,
trans_type int,
emp_num nvarchar(50),
trans_date datetime
)

Insert into dcta values(1,'Emp111','10/03/2011')
Insert into dcta values(2,'Emp111','10/03/2011')
Insert into dcta values(3,'Emp111','10/03/2011')
Insert into dcta values(4,'Emp111','10/03/2011')

Insert into dcta values(1,'Emp112','10/03/2011')
Insert into dcta values (3,'Emp112','10/03/2011')
Insert into dcta values(2,'Emp112','10/03/2011')

GO
Select * from dcta
GO

;With CTE (emp_num,trans_num) as (Select emp_num,Max(trans_num) from dcta Where trans_date='10/03/2011'
group By emp_num)
Select dcta.* from cte
Inner Join dcta on cte.[trans_num]=dcta.[trans_num]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 00:39:07

quote:
Originally posted by m_imran18

Here is the sample data.

Drop table dcta
GO
Create table dcta
(trans_num int identity(1,1) ,
trans_type int,
emp_num nvarchar(50),
trans_date datetime
)

Insert into dcta values(1,'Emp111','10/03/2011')
Insert into dcta values(2,'Emp111','10/03/2011')
Insert into dcta values(3,'Emp111','10/03/2011')
Insert into dcta values(4,'Emp111','10/03/2011')

Insert into dcta values(1,'Emp112','10/03/2011')
Insert into dcta values (3,'Emp112','10/03/2011')
Insert into dcta values(2,'Emp112','10/03/2011')

GO
Select * from dcta
GO

;With CTE (emp_num,trans_num) as (Select emp_num,Max(trans_num) from dcta Where trans_date='10/03/2011'
group By emp_num)
Select dcta.* from cte
Inner Join dcta on cte.[trans_num]=dcta.[trans_num]

what will happen if employee has more than 1 days data in table which is how it would be in real scenario

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

m_imran18
Starting Member

14 Posts

Posted - 2011-10-04 : 01:49:54

It will give you the same result I included one more date in the sample.

Here we go.

Drop table dcta
GO
Create table dcta
(trans_num int identity(1,1) ,
trans_type int,
emp_num nvarchar(50),
trans_date datetime
)

Insert into dcta values(1,'Emp111','10/03/2011')
Insert into dcta values(2,'Emp111','10/03/2011')
Insert into dcta values(3,'Emp111','10/03/2011')
Insert into dcta values(4,'Emp111','10/03/2011')
Insert into dcta values(1,'Emp111','11/03/2011')
Insert into dcta values(2,'Emp111','11/03/2011')

Insert into dcta values(1,'Emp112','10/03/2011')
Insert into dcta values (3,'Emp112','10/03/2011')
Insert into dcta values(2,'Emp112','10/03/2011')
Insert into dcta values (3,'Emp112','11/03/2011')
Insert into dcta values(2,'Emp112','11/03/2011')

GO
Select * from dcta
GO

;With CTE (emp_num,trans_num) as (Select emp_num,Max(trans_num) from dcta Where trans_date='10/03/2011'
group By emp_num)
Select dcta.* from cte
Inner Join dcta on cte.[trans_num]=dcta.[trans_num]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-04 : 02:02:40

thats because you're running it for one day. please read requirement. it says
So for a given day there could be 4 transcations. What I want it only to return the last transaction and see what the trans_type is

so its basically asking for last transaction type in a given day. your suggestion is fine so far its run for one day at a time. Also you're assuming that tran_num is reseting for each employee which might not be case in actual scenario

but if you want a single solution catering to any number of days and with trans_num sequence not specific for each employee it should be something like


;With CTE (emp_num,trans_date,trans_num) as 
(Select emp_num,trans_date,Max(trans_num) 
from dcta --Where trans_date='10/03/2011' (not required any more)
group By emp_num,trans_date)
Select dcta.* from cte 
Inner Join dcta 
on cte.[trans_num]=dcta.[trans_num]
and cte.[emp_num]=dcta.[emp_num]
and cte.trans_date=dcta.trans_date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

m_imran18
Starting Member

14 Posts

Posted - 2011-10-04 : 02:28:43

you are right :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources