T-Sql Grouping Question 2

[Guru7777]

I have data similar to what is below. I have duplicates across the 4 grouping criteria that I’ve indicated. I need the one record that is the max value of both the date submitted and time submitted (unfortunately, both varchar data) across the grouping values. So, in the data set I've provided, there are 2 groups of 3 (these are transactions.. bill, reverse, bill) and I need the last one to have happened. I've been boning this up all day and was hoping one of you guys could help bail me out.

PK	Transaction Status 	DateSubmitted	TimeSubmitted	Amount	Grouping Criteria 1	Grouping Criteria 2	Grouping Criteria 3	Grouping Criteria 4
18	P	20070405	183907	195.46	20070405	12345678	180000	XJ37SMTR
19	X	20070405	190346	-195.46	20070405	12345678	180000	XJ37SMTR
22	P	20070406	113724	195.46	20070405	12345678	180000	XJ37SMTR
61	P	20070401	161821	18.88	20070401	87654321	60000	WSYD342
62	X	20070401	163754	-18.88	20070401	87654321	60000	WSYD342
63	P	20070401	163803	18.88	20070401	87654321	60000	WSYD342




My results should be								

22	P	20070406	113724	195.46	20070405	12345678	180000	XJ37SMTR
63	P	20070401	163803	18.88	20070401	87654321	60000	WSYD342

----------------------------------------

TWljcm8kb2Z0J3MgIzEgRmFuIQ==

 

[bborissov]

SELECT YourTable.PK,
       YourTable.[Transaction Status],
       YourTable.DateSubmitted,
       YourTable.TimeSubmitted,
       YourTable.Amount,
       YourTable.[Grouping Criteria 1],
       YourTable.[Grouping Criteria 2],
       YourTable.[Grouping Criteria 3],
       YourTable.[Grouping Criteria 4]
FROM YourTable
INNER JOIN (SELECT YourTable.[Grouping Criteria 1],
                   YourTable.[Grouping Criteria 2],
                   YourTable.[Grouping Criteria 3],
                   YourTable.[Grouping Criteria 4],
                   MAX(DateSubmitted+TimeSubmitted) AS Dt
            FROM YourTable
            GROUP BY YourTable.[Grouping Criteria 1],
                     YourTable.[Grouping Criteria 2],
                     YourTable.[Grouping Criteria 3],
                     YourTable.[Grouping Criteria 4]) Tbl1
ON YourTable.[Grouping Criteria 1] = Tbl1.[Grouping Criteria 1] AND
   YourTable.[Grouping Criteria 2] = Tbl1.[Grouping Criteria 2] AND
   YourTable.[Grouping Criteria 3] = Tbl1.[Grouping Criteria 3] AND
   YourTable.[Grouping Criteria 4] = Tbl1.[Grouping Criteria 4] AND
   (YourTable.DateSubmitted+YourTable.TimeSubmitted) = Tbl1.Dt

Not tested.


Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[SQLDenis]

Please provide DDL and DML next time

create table #test(PK int,   Transaction_Status  char(1) ,  DateSubmitted varchar(10),
   TimeSubmitted varchar(10),   Amount decimal(20,2),    GroupingCriteria1  varchar(10),
  GroupingCriteria2  varchar(10),  GroupingCriteria3 varchar(10),  GroupingCriteria4
varchar(10))
insert #test values(18,    'P',    '20070405',    '183907',    195.46 ,   '20070405' ,   '12345678','180000','XJ37SMTR')
insert #test values(19,    'X',    '20070405',    '190346',    -195.46,    '20070405',    '12345678',    '180000','XJ37SMTR')
insert #test values(22,    'P',    '20070406',    '113724',    195.46,    '20070405',    '12345678',    '180000','XJ37SMTR')
insert #test values(61,    'P',   '20070401',    '161821',    18.88,    '20070401',    '87654321',    '60000','WSYD342')
insert #test values(62,    'X',    '20070401',    '163754',    -18.88,    '20070401',    '87654321',    '60000','WSYD342')
insert #test values(63,    'P',    '20070401',    '163803',    18.88,    '20070401',    '87654321',    '60000','WSYD342')

Here is the query

select t1.* from(
select max(DateSubmitted + TimeSubmitted) as MaxJunk,GroupingCriteria1,GroupingCriteria2,GroupingCriteria3,GroupingCriteria4 
from #test
group by GroupingCriteria1,GroupingCriteria2,GroupingCriteria3,GroupingCriteria4)
t2
join #test t1 on t2.MaxJunk = DateSubmitted + TimeSubmitted
and t1.GroupingCriteria1 = t2.GroupingCriteria1
and t1.GroupingCriteria2 = t2.GroupingCriteria2
and t1.GroupingCriteria3 = t2.GroupingCriteria3
and t1.GroupingCriteria4 = t2.GroupingCriteria4

Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
SQLBlog.com, Google Interview Questions

 

[Guru7777]

Thanks, guys. That did the trick.

SQLDenis, sorry about that. I will try to remember that for next time.

----------------------------------------

TWljcm8kb2Z0J3MgIzEgRmFuIQ==