I am trying to find a solution for the following issue that I have in sql-server:
我正在尝试找到我在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:
我有一个表t1,其中我想使用每个代理的每个日期,并通过查询循环以找出avg_rate。这是我的表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 ** **):
我真的想要使用end_date列中的所有值并将其插入到查询中(我用** **标记它):
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.
我需要两个动态日期的原因是,如果您更改这些日期之间的时间范围,avg_rates会有所不同。
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?
如何从表t1中取出end_date将其插入到c.end_date所在的查询中,如果通过t1.end_date中的所有值,则循环它?
I appreciate your help!
我感谢您的帮助!
2 个解决方案
#1
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.
我可以想到几种方法 - Cursor,StoredProcedure,Joins ...鉴于查询的简单性,表T1的平面CTE的笛卡尔积(交叉连接)应该具有魔力。
#2
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.
您需要一个日期列来将您的数据与T1连接(在本例中我称之为DateColumn),否则所有时间范围都将返回相同的平均值。
#1
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.
我可以想到几种方法 - Cursor,StoredProcedure,Joins ...鉴于查询的简单性,表T1的平面CTE的笛卡尔积(交叉连接)应该具有魔力。
#2
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.
您需要一个日期列来将您的数据与T1连接(在本例中我称之为DateColumn),否则所有时间范围都将返回相同的平均值。