Click here to Skip to main content
15,867,686 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a table such as:

TYPE_________DATE________________MONEY
=====================================
F01__________2013/11/01__________2
F01__________2013/11/01__________4
F01__________2013/11/03__________3
F02__________2013/11/01__________2
F02__________2013/11/02__________4
F02__________2013/11/06__________4

If I search follow condition "2013/11/01 <= DATE <= 2013/11/05", I have result such as:

TYPE_________MONEY______NEWDATE
===============================
F01__________9__________2013/11/03
F02__________6__________2013/11/06

please help me a sql command to have this result
Thanks
Posted
Updated 20-Nov-13 21:10pm
v4
Comments
OriginalGriff 21-Nov-13 3:12am    
Show the SQL command you are using at present to get that result.

You might use a subquery, something like:
SQL
select typ, mon, dat as newdate from mytable as a where a.dat = (select max(b.dat) from mytable as b where '2013/11/01' <= dat <= '2013/11/05');
 
Share this answer
 
Comments
DinhCongLoc 21-Nov-13 4:53am    
not right
CPallini 21-Nov-13 4:57am    
left?
Orcun Iyigun 21-Nov-13 5:00am    
:) i think i give up...
CPallini 21-Nov-13 5:14am    
Yes, that dragged me down.
SQL
select a.Type, Sum(a.Money) as Money, b.Date as newdate
from temp a
left join (select type, max(date) as Date from temp group by type) b on a.Type = b.Type
where a.Date >= '2013-11-1' AND
      a.Date <= '2013-11-5'
group by a.type, b.Date


[=EDIT=] due to the OP's comment
SQL
SELECT t.Type, Sum(t.Money) as Money, Max(t.Date) as NewDate
FROM Table t
WHERE Date >= '2013-11-1' AND
      Date <= '2013-11-5'
Group By t.Type


[=EDIT=]


Good luck,
OI
 
Share this answer
 
v5
Comments
DinhCongLoc 21-Nov-13 4:53am    
not right
DinhCongLoc 21-Nov-13 5:00am    
Hi Orcun,

In solution 3, I have to supply data to test.
I have to run but the result is not right

You might run follow my data in solution 3
Orcun Iyigun 21-Nov-13 5:09am    
In my current solution if you run the lower query your result will be ;
F01 9 2013-11-03 00:00:00.000
F02 6 2013-11-02 00:00:00.000

If you run the upper query:
F01 9 2013-11-03 00:00:00.000
F02 6 2013-11-06 00:00:00.000

your result set will be this. So if these results set doesnt match your result set do tell me.
create table AAA
(
LOAIPHIEU VARCHAR(3) NOT NULL,
THOIGIAN DATETIME NOT NULL,
TONGTIEN INT NOT NULL
)

SELECT * FROM AAA
INSERT AAA (LOAIPHIEU, THOIGIAN, TONGTIEN) VALUES ('F01','2013/11/01',2)
INSERT AAA (LOAIPHIEU, THOIGIAN, TONGTIEN) VALUES ('F01','2013/11/01',4)
INSERT AAA (LOAIPHIEU, THOIGIAN, TONGTIEN) VALUES ('F01','2013/11/03',3)
INSERT AAA (LOAIPHIEU, THOIGIAN, TONGTIEN) VALUES ('F02','2013/11/01',2)
INSERT AAA (LOAIPHIEU, THOIGIAN, TONGTIEN) VALUES ('F02','2013/11/02',4)
INSERT AAA (LOAIPHIEU, THOIGIAN, TONGTIEN) VALUES ('F02','2013/11/06',4)

--SOLUTION 1: ERROR

--SOLUTION 2: NOT RIGHT FOR CASE "F02 IS 2013/11/02" - MY RESULT IS 2013/11/06
SELECT LOAIPHIEU as vType, Sum(TONGTIEN) as iMoney, Max(THOIGIAN) as NEWDATE
FROM AAA
WHERE THOIGIAN >= '2013-11-1' AND
THOIGIAN <= '2013-11-5'
Group By LOAIPHIEU


=> please help me another solution (2013/11/01 <= DATE <= 2013/11/05) to have result as:
TYPE_________MONEY______NEWDATE
===============================
F01__________9__________2013/11/03
F02__________6__________2013/11/06
 
Share this answer
 
v4
Comments
Orcun Iyigun 21-Nov-13 4:47am    
If you check my solution history and Revision1 will be the answer to your question if you asked it properly. And do not post a comment as a solution and mark it as the answer? It is so wrong..
I now updated my answer you can see it on the edited part...
DinhCongLoc 21-Nov-13 4:55am    
hi Occun,
I don't know how modify your solution to have my observe result.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900