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.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2004-06-29 : 11:43:40
|
| Gert writes "i have a table that looks likedate value nr20040621 10 apa00120040603 20 apa00120040521 20 apa00120040410 0 apa001 20040620 70 apa02220040602 30 apa02220040519 90 apa02220040401 10 apa022 enz. i want to create an sql statment that give the sum of the value from the max(date) records and it must be selected from a date to a date.so when if query between date 19000101 to 20040625 i excpect20040621 10 apa000120040620 70 apa0002 so the sum is 80 so when if query between date 19000101 to 20040615 i excpect20040603 20 apa000120040602 30 apa0002 so the sum is 50 i am only intrested in the sumThank for helpingGert van Wijk" |
|
|
Seventhnight
Master Smack Fu Yak Hacker
2878 Posts |
Posted - 2004-06-29 : 12:30:27
|
| Try this:Declare @myTable table (myDate nvarchar(8), myVal int, nr nvarchar(10))Insert Into @myTable values('20040621',10,'apa001')Insert Into @myTable values('20040603',20,'apa001')Insert Into @myTable values('20040521',20,'apa001')Insert Into @myTable values('20040410',0,'apa001')Insert Into @myTable values('20040620',70,'apa022')Insert Into @myTable values('20040602',30,'apa022')Insert Into @myTable values('20040519',90,'apa022')Insert Into @myTable values('20040401',10,'apa022')Select * From @myTableDeclare @beginRange nvarchar(8), @endRange nvarchar(8)Select @beginRange = '19000101', @endRange = '20040625'Select sum(myVal) From (Select myVal=(Select myVal From @myTable Where nr=A.nr and myDate = A.maxDate) From (Select nr, maxDate = max(myDate) From @myTable Where myDate between @beginRange and @endRange Group By nr) as A ) as ZSelect @beginRange = '19000101', @endRange = '20040615'Select sum(myVal) From (Select myVal=(Select myVal From @myTable Where nr=A.nr and myDate = A.maxDate) From (Select nr, maxDate = max(myDate) From @myTable Where myDate between @beginRange and @endRange Group By nr) as A ) as ZCorey |
 |
|
|
|
|
|
|
|
max