|
nextaxtion
Yak Posting Veteran
54 Posts |
 Posted - 2014-09-05 : 01:56:14
|
| PLEASE HELP ME IN FOLLOWING SCENARIO , FOLLOWING IS AMPLE DATA DECLARE @TBL_CASH TABLE (ID NUMERIC(10) , DUE_DATE DATETIME , AMNT NUMERIC(10))INSERT INTO @TBL_CASH(ID , DUE_DATE, AMNT)VALUES(1, '05-SEP-2014', 200 )INSERT INTO @TBL_CASH(ID , DUE_DATE, AMNT)VALUES(1, '05-OCT-2014', 150 )INSERT INTO @TBL_CASH(ID , DUE_DATE, AMNT)VALUES(1, '05-NOV-2014', 257 )INSERT INTO @TBL_CASH(ID , DUE_DATE, AMNT)VALUES(1, '05-DEC-2014', 157 )INSERT INTO @TBL_CASH(ID , DUE_DATE, AMNT)VALUES(2, '05-SEP-2014', 700 )INSERT INTO @TBL_CASH(ID , DUE_DATE, AMNT)VALUES(2, '05-OCT-2014', 750 )INSERT INTO @TBL_CASH(ID , DUE_DATE, AMNT)VALUES(2, '05-NOV-2014', 857 )INSERT INTO @TBL_CASH(ID , DUE_DATE, AMNT)VALUES(2, '05-DEC-2014', 957 ) SELECT ID , MAX(AMNT) MAXAMNT , MIN(DUE_DATE) MINDATE FROM @TBL_CASH GROUP BY ID --NOW I WANT TO ADD A NEW COLUMN IN QUERY WHERE THE AMNT AGAINS MINDATE WILL SHOW MEANSA THAT FOR ID =1 AND MINDATE = 2014-09-05 THEN AMNT = 200 --TDIE TO SOME CIRCUMSTANCES I NEED TO RETURN ONLY ONE ROW SO I CANNOT USE AMNT IN GROUP BY prithvi nath pandey |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2014-09-05 : 10:54:41
|
how about something like this:SELECT ID , AMNT, MAX(AMNT) over(partition by id) MAXAMNT , MIN(DUE_DATE) over(partition by id) MINDATEFROM @TBL_CASH |
 |
|
query help