SQLTeam.com | Weblogs | Forums

Complex query help needed


#1

DECLARE @PreProcessing Table
(
[new_licenceidName] [varchar] (6) NULL,
[new_dbfmlivedataId] [varchar] (100) NULL,
[new_value] [decimal] (11,2)
)

INSERT INTO @PreProcessing VALUES ('123456' , 'E6354675-25EE-E611-842D-00155D011A0A' , 35.25)
INSERT INTO @PreProcessing VALUES ('123456' , '2207D213-28EE-E611-842D-00155D011A0A' , -1.25)

INSERT INTO @PreProcessing VALUES ('325678' , 'BB4906D8-9DED-E611-842D-00155D011A0A' , -99.99)
INSERT INTO @PreProcessing VALUES ('325678' , '5B88FC2A-E2ED-E611-842D-00155D011A0A' , -10.00)

INSERT INTO @PreProcessing VALUES ('457485' , '3A1BF1C5-2AEE-E611-842D-00155D011A0A' , -10.00)
INSERT INTO @PreProcessing VALUES ('457485' , 'E72CAAB8-83ED-E611-842D-00155D011A0A' , -50.00)
INSERT INTO @PreProcessing VALUES ('457485' , 'F817C4ED-2BEE-E611-842D-00155D011A0A' , 59.00)

INSERT INTO @PreProcessing VALUES ('789256' , 'B6E84552-89ED-E611-842D-00155D011A0A' , 15.00)

I'm trying to get back only the new_dbfmlivedataid for the new_licenceidname where the sum of new_value is > 0
In this instance 123456 has a + 35.25 & -1.25 therefore the sum > 0
325678 has a -99.99 and a - 10.00 so no record should be returned as sum < 0

With the data above I'm looking to get back the following
'E6354675-25EE-E611-842D-00155D011A0A'
'2207D213-28EE-E611-842D-00155D011A0A'
'B6E84552-89ED-E611-842D-00155D011A0A'


#2
select p.new_dbfmlivedataId
from 
	@PreProcessing p
	cross apply 
	(
		select new_licenceidName 
		from @PreProcessing p2 
		where p2.new_licenceidName = p.new_licenceidName 
		group by new_licenceidName 
		having sum(new_value) > 0 
	) p2

You can use other constructs - e.g. exists clause instead of cross apply. Regardless, your objective is to find the id's that are of interest (in a subquery) and then use that list to select the rows from the table.