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 2012 Forums
 Transact-SQL (2012)
 Select min and max for distinct id(rows) from colu

Author  Topic 

cyborgnaren
Starting Member

3 Posts

Posted - 2013-01-16 : 05:40:26
WS   WS StartTime   WS EndTime   WS LstModTime   Eqt Actual   Mat Actual
1001   1-15-13 22:35   1-15-13 23:39   1-15-13 22:19   001INI         NULL
1001   1-15-13 23:35   NULL                1-15-13 23:51   NULL            NULL
1001   1-15-13 23:35   1-15-13 23:49   1-15-13 20:18   NULL          F1516
1210   1-15-13 18:30   NULL                1-16-13 8:46     005VERBO   NULL
1210   1-15-13 19:30   NULL                1-16-13 9:46     NULL         123456
1210   1-15-13 20:30   1-16-13 10:46   1-16-13 10:46    NULL         NULL

Should pick up the Red Highlighted Cells : logic as below
       Min                 Max                Max                Not Null          Not Null
WS  WS StartTime   WS EndTime   WS LstModTime   Eqt Actual   Mat Actual
1001  1-15-13 22:35  1-15-13 23:49  1-15-13 23:51  001INI         F1516
1210  1-15-13 18:30  1-16-13 10:46  1-16-13 10:46  005VERBO   123456

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-01-16 : 06:04:56
[code]declare @tab TABLE( WS int, WSStartTime datetime, WSEndTime datetime, WSLstModTime datetime, EqtActual varchar(10), MatActual varchar(10))
insert into @tab
SELECT 1001, '1-15-13 22:35', '1-15-13 23:39', '1-15-13 22:19', '001INI', NULL union all
SELECT 1001, '1-15-13 23:35', NULL, '1-15-13 23:51', NULL, NULL union all
SELECT 1001, '1-15-13 23:35', '1-15-13 23:49', '1-15-13 20:18', NULL, 'F1516' union all
SELECT 1210, '1-15-13 18:30', NULL, '1-16-13 8:46', '005VERBO', NULL union all
SELECT 1210, '1-15-13 19:30', NULL, '1-16-13 9:46', NULL, '123456' union all
SELECT 1210, '1-15-13 20:30', '1-16-13 10:46', '1-16-13 10:46', NULL, NULL

SELECT ws, MIN(wsstarttime), MAX( WSEndtime), MAX(WSLstModTime), MAX(EqtActual), MAX(MatActual)
FROM @tab
GROUP BY ws[/code]

--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-01-16 : 23:00:42
Can there be a case where there can be more than one value present for Eqt Actual Mat Actual fields within same WS group?

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

Go to Top of Page
   

- Advertisement -