using all values from one column in another query

Question

I am trying to find a solution for the following issue that I have in sql-server:

I have one table t1 of which I want to use each date for each agency and loop it through the query to find out the avg_rate. Here is my table t1:

Table T1:

+--------+-------------+
| agency |  end_date   |
+--------+-------------+
|      1 | 2017-10-01  |
|      2 | 2018-01-01  |
|      3 | 2018-05-01  |
|      4 | 2012-01-01  |
|      5 | 2018-04-01  |
|      6 | 2017-12-01l |
+--------+-------------+

I literally want to use all values in the column end_date and plug it into the query here (I marked it with ** **):

    with averages as (
select      a.id as agency
            ,c.rate  
            , avg(c.rate) over (partition by a.id  order by a.id )  as avg_cost


from table_a as a
                     join  rates c on a.rate_id = c.id


and c.end_date =  **here I use all values from t1.end_date** 
and c.Start_date =  **here I use all values from above minus half a year** = dateadd(month,-6,end_date)

group by    a.id 
            ,c.rate  
            )
select distinct agency, avg_cost  from averages 
order by 1

The reason why I need two dynamic dates is that the avg_rates vary if you change the timeframe between these dates.

My problem and my question is now:

How can you take the end_date from table t1 plug it into the query where c.end_date is and loop if through all values in t1.end_date?

I appreciate your help!

Have you looked at the APPLY operator? How about an IN clause? — Tab Alleman
– Tab Alleman, Commented May 17, 2018 at 14:10

EzLo · Accepted Answer · 2018-05-17 14:14:38Z

1

Do you really need a windowed average? Try this out.

;with timeRanges AS
(
    SELECT
        T.end_date,
        start_date = dateadd(month,-6, T.end_date)
    FROM
        T1 AS T
)
select      
    a.id as agency,
    c.rate,
    T.end_date,
    T.start_date,
    avg_cost = avg(c.rate)
from 
    table_a as a
    join  rates c on a.rate_id = c.id
    join timeRanges AS T ON A.DateColumn BETWEEN T.start_date AND T.end_date
group by   
    a.id ,
    c.rate,
    T.end_date,
    T.start_date

You need a date column to join your data against T1 (I called it DateColumn in this example), otherwise all time ranges would return the same averages.

answered May 17, 2018 at 14:14

EzLo

14.2k10 gold badges38 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Tolu · Accepted Answer · 2018-05-17 14:15:48Z

0

I can think of several ways to do this - Cursor, StoredProcedure, Joins ... Given the simplicity of your query, a cartesian product (Cross Join) of Table T1 against the averages CTE should do the magic.

answered May 17, 2018 at 14:15

Tolu

1754 silver badges10 bronze badges

4 Comments

not_ur_avg_cookie Over a year ago

how would you use a cursor in this example?

Tolu Over a year ago

Take a look at this StackOverflow Question.If you provide the schemas, along with sample data, I can write one specifically to solve this. Of course, understand that anything more advanced than a cartesian product would be an overkill for your problem.

Tolu Over a year ago

@not_ur_avg_cookie were you able to resolve the problem?

not_ur_avg_cookie Over a year ago

Tolu, Thank you. Yes, I was able to solve it with a lot of CTEs and then I simply placed the CTEs into IN Statements. However, I really want to understand how I can solve it with a cursor where you just plug each end date into the query get the AVG cost and place it together.

Collectives™ on Stack Overflow

using all values from one column in another query

2 Answers 2

Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related