another help - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

another help

Author

Topic

tup
Starting Member

13 Posts

Posted - 2003-09-05 : 03:36:44

I have to ask once again for help.Hopefully this would be the last query I have to do for this month.

After executing this:

select p.partija ,a.naziv ,
sum(p.iznos) ,p.kod , p.tip
from pstdev p
join istdev i
on p.partija=i.partija and p.valuta = i.valuta
join adresar a on i.embg=a.embg
where (p.dknizenja>='2003.02.06' and p.dknizenja<'2003.05.13')
and (p.dp=1 and p.status=0)
group by p.partija ,a.naziv ,p.kod , p.tip
having sum(p.iznos)<>0
order by a.naziv

I get

partija naziv iznos kod tip
----- --------------- ----- ---- ---
1300021101000042 ''GRESA NHTSH 27818.0 3511 211
1300021101000139 ''GRESA '' NHTSH 29550.0 3511 211
1300271200079079 ''PAJTIMI COMPANY'' 4536.5 9173 10
1300023401001089 SELIME DIBRA 1020.0 3511 234
1300023401002059 ADEM IDRIZI 3883.5 3511 234
1300023401001865 AHMET IDRIZAJ 4315.0 3511 234
1300023101002205 Ajten Calpan 404.8 3511 231
1300023101002496 Ajten Calpan 400 3511 0 1300023101002302 Ajten Calpan 30 3511 231
1300023401003029 ALTINA BESIMI 350 3422 233
1301271100271170 ALTINA BESIMI 120 2111 111
1300023501000232 Bedri Peci 890.7 2111 111
1300023501000329 Bedri Peci 250 2111 211
1300023101002787 Bedri Zasella 500 3122 312

Now what I want is to edit my code so I could display only the records that meet the criteria: sum of p.iznos grouped by a.naziv to be less then 850.
This what I want to get:

partija naziv iznos kod tip
------- ----- ------ ---- ----
1300023101002205 Ajten Calpan 404.8 3511 231
1300023101002496 Ajten Calpan 400 3511 0 1300023101002302 Ajten Calpan 30 3511 231
1300023401003029 ALTINA BESIMI 350 3422 233
1301271100271170 ALTINA BESIMI 120 2111 111

so when you sum first three records you get 834.8 less then 850

thanks to everyone

mr_mist
Grunnio

1870 Posts

Posted - 2003-09-05 : 03:57:24

select p.partija ,a.naziv ,
sum(p.iznos) ,p.kod , p.tip
from pstdev p
join istdev i
on p.partija=i.partija and p.valuta = i.valuta
join adresar a on i.embg=a.embg
where (p.dknizenja>='2003.02.06' and p.dknizenja<'2003.05.13')
and (p.dp=1 and p.status=0)
group by p.partija ,a.naziv ,p.kod , p.tip
having sum(p.iznos) < 850 and sum(p.iznos) <> 0
order by a.naziv

-------
Moo. :)

tup
Starting Member

13 Posts

Posted - 2003-09-05 : 04:23:12

I have to apologize .I didn't explain good what I really want .
I want to display all the records that have the same value in field a.naziv but when you sum p.iznos in these records the value has to be less then 850.
For example:

If I had this table:

a b c
--- --- ---
11 A 90
12 B 20
13 B 25
14 B 22
15 C 50
16 C 85

and I want the same thing exept the criteria to be less then 85 then I would get

a b c
--- --- ---
12 B 20
13 B 25
14 B 22

the fifth record is not included because he has the same value in field c as the sixth one 'C' and when you sum them together you'll get 135 .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources