How would I use a CURSOR to iterate through a list of values to do a calculation for each value?
I have searched and searched for a way to do what I'm trying to do, but I haven't found a solution yet. I have dates stored in a table like so:
Dates Code Sum
1-15-2015 AAA (to be calculated)
2-2-2016 BBB (to be calculated)
11-23-2015 CCC (to be calculated)
The sum for each record in the table is calculated based on the date in the Date column. I want to use the date from the Date column as a parameter of sorts to do the calculation for each record in the table. I assume a CURSOR would be the best approach, but I am new to SQL and I don't how to implement such functionality. For example's sake, you can totally make up the sum calculation. It's trivial, anyway. I have looked at several examples elsewhere, but I've had no luck. Any help is greatly appreciated.
The Sum Calculation
The sum is calculated by pulling values out of another database, via a pass-through query, within a date range. The date range is determined by the date in the Dates column of the example table. The date range is the date value in the Dates column to today's date (GetDate()). All I need to know how to do is change the left date to the date in the Dates column for each record in the sample table. Efficiency is key.
My answer will be very generic, but you'll need to do either an OUTER APPLY or a CROSS APPLY depending on if you want to filter NULL values or not.
The syntax would look something similar to this:
Select A.Dates, A.Code, X.Sum
From YourTable A
Select Sum(B.StuffToSum) As Sum
From OtherDatabase.OtherSchema.OtherTable B
Where B.Date Between A.Dates And GetDate()
Your OUTER APPLY logic should match the actual logic you're using, I'm just using this as an example showing how you can get a date range from the Date in the first table to GETDATE().
Using an OUTER APPLY, if there are no values in your other table that fall in that date range, it will return NULL. In the case of a CROSS APPLY, the entire record will be filtered out (this behaviour is similar to that of INNER and OUTER JOINS).
Asked in February 2016Viewed 3,387 timesVoted 11Answered 1 times