ic3man7019 February 2016

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.

Answers


Siyual February 2016

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
Outer Apply
(
    Select  Sum(B.StuffToSum)   As  Sum
    From    OtherDatabase.OtherSchema.OtherTable    B
    Where   B.Date Between A.Dates And GetDate()
) X

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).

Post Status

Asked in February 2016
Viewed 3,387 times
Voted 11
Answered 1 times

Search




Leave an answer