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
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Select min and max for distinct id(rows) from colu
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cyborgnaren
Starting Member

3 Posts

Posted - 01/16/2013 :  05:40:26  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2202 Posts

Posted - 01/16/2013 :  06:04:56  Show Profile  Reply with Quote
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


--
Chandu
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/16/2013 :  23:00:42  Show Profile  Reply with Quote
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
  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.05 seconds. Powered By: Snitz Forums 2000