MAX of one column with corresponding columns in SQL Server 2000
MAX of one column with corresponding columns in SQL Server 2000
This is a small portion of what I have in my table, there are multiple dates, but this is just for one date:
propertyId groupId date quantity rate
------------------------------------------------
3475 616375 2018-09-21 25 139.99
3475 626696 2018-09-21 6 144.99
3475 602361 2018-09-21 25 134.99
3475 622321 2018-09-21 5 119.99
3482 609348 2018-09-21 11 139.99
3482 621872 2018-09-21 5 75
3482 614956 2018-09-21 25 114.99
3482 583585 2018-09-21 10 139
3488 627286 2018-09-21 11 164.99
3488 619219 2018-09-21 5 129.99
3488 603781 2018-09-21 2 149.99
3488 583573 2018-09-21 2 0
I need the MAX(quantity) with the corresponding propertyId, groupId, date, and rate...the minimum rate if the quantity is a tie...and finally the SUM(quantity) for each day by property, not group.
MAX(quantity)
propertyId
groupId
date
rate
SUM(quantity)
From the sample I would need:
propertyId groupId date quantity rate sumQuantity
--------------------------------------------------------------
3475 616375 2018-09-21 25 134.99 61
3482 614956 2018-09-21 25 114.99 51
3488 627286 2018-09-21 11 164.99 20
Hopefully that makes sense.
1 Answer
1
Data feed for testing:
set dateformat ymd
declare @aux table (propertyId int, groupId int,[date] datetime, quantity int, rate money)
insert into @aux values (3475,616375,'2018-09-21',25,139.99)
insert into @aux values (3475,626696,'2018-09-21',6,144.99)
insert into @aux values (3475,602361,'2018-09-21',25,134.99)
insert into @aux values (3475,622321,'2018-09-21',5,119.99)
insert into @aux values (3482,609348,'2018-09-21',11,139.99)
insert into @aux values (3482,621872,'2018-09-21',5,75)
insert into @aux values (3482,614956,'2018-09-21',25,114.99)
insert into @aux values (3482,583585,'2018-09-21',10,139)
insert into @aux values (3488,627286,'2018-09-21',11,164.99)
insert into @aux values (3488,619219,'2018-09-21',5,129.99)
insert into @aux values (3488,603781,'2018-09-21',2,149.99)
insert into @aux values (3488,583573,'2018-09-21',2,0)
Proposed solution:
select a.*
,(select sum(quantity)
from @aux
where [date] = a.[date]
and propertyId = a.propertyId
) sumQuantity
from @aux a
join (
select x.*
,(select max(rate)
from @aux
where [date] = x.[date]
and propertyId = x.propertyId
and quantity = x.qt
) rt
from (select [date]
,propertyId
,max(quantity) as qt
from @aux
group by [date], propertyId
) x
) b on a.[date] = b.[date]
and a.propertyid = b.propertyid
and qt = a.quantity
and rt = a.rate
Thanks for contributing an answer to Stack Overflow!
But avoid …
To learn more, see our tips on writing great answers.
Required, but never shown
Required, but never shown
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
