Loop thru data subset

hi

I have the following query:
select sum(iunits) as iunits,sum(ivalue) as ivalue,sum(ivolume)as ivolume,sum(ivolumeHL) as ivolumehl ,periodid as periodid ,productid as productid, tbstores.storeid as storeid
from tbstoredata inner join tbstores on tbstoredata.storeid = tbstores.storeid
where tbstoredata.uploadid = 111 group by tbstores.storeid,periodid,parentid,productid

This returns multiple rows of data. I want to loop through each row, extract 2 values and see if they exist in another table. I need to do this in Query Analyser.

Can anyone please help!

Thanking you in advance
PORRASTAROf course you can use cursor, but I prefere to use fake cursor:

Save your result in temporary table and do loop by newid.

select sum(iunits) as iunits,sum(ivalue) as ivalue,sum(ivolume)as ivolume,sum(ivolumeHL) as ivolumehl ,periodid as periodid ,productid as productid, tbstores.storeid as storeid,
IDENTITY(int, 1, 1) AS newid -- add identity filed
into #tmp -- temp table
from tbstoredata inner join tbstores on tbstoredata.storeid = tbstores.storeid
where tbstoredata.uploadid = 111 group by tbstores.storeid,periodid,parentid,productid|||I don't think you need to loop through anything if you just want to see if the values exist in another table. Just treat your query as a subquery and join it to your other table:

select Value1, Value2
from YourOtherTable
inner join
(select sum(iunits) as iunits, sum(ivalue) as ivalue, sum(ivolume) as ivolume, sum(ivolumeHL) as ivolumehl, periodid as periodid, productid as productid, tbstores.storeid as storeid
from tbstoredata
inner join tbstores on tbstoredata.storeid = tbstores.storeid
where tbstoredata.uploadid = 111
group by tbstores.storeid, periodid, parentid, productid) SummarySubquery
on YourOtherTable.Value1 = SummarySubquery.Value1 and YourOtherTable.Value2 = SummarySubquery.Value2

...or if you want to use the dataset multiple times, store it in a temporary table or table variable and join that instead.

blindman

Looking for help with an SQL query.

well, i want to select rows by date from a file. but I want
in particular one sum of values from the rows that fall WITHIN
a supplied date range, and a second sum of values from the
rows that have dates FOR ALL TIME UP TO the second date in the date range.

the former, by itself, might be:

SELECT id, value RangedValue
FROM myFile
WHERE date >= [lower date range value]
AND date <= [higher date range value]

and the latter, by itself, might be:

SELECT id, value AllTimeValue
FROM myFile
WHERE date <= [higher date range value]

but I need to grab the two separate sums (RangedValue and AllTimeValue)
using one SQL statement.

I'm thinking that the UNION might work, but my preliminary results are
taking a huge amount of time, and apparently smegging up the (rather
stupid, external) report generator to boot.
If you like the UNION idea, please give me an example.

I should mention that the report generator at very least can do the
(summing) part. I could do the summing at either the SQL level or the
report level. I should also mention that although I only talk about the
one file here (myFile), in fact I need to join to and pull values from
its "parent" file, although I don't think that that should change my
fundamental problem.

Any ideas?

Cheers in advance!

-GlennYou could try something like this:
Select Id
, Sum(Value) Alltimevalue
, Sum(Case
When Date >= [Lower Date Range Value] Then Value
Else 0 End) As Rangedvalue
From Myfile
Where Date <= [Higher Date Range Value];
;)