如何从三个单独的表中获取每月数据 [英] How to get monthly data from three separate table

问题描述

我有三个单独的表来存储数据。

I have three separate tables which stores data.

表1（每周执行和存储一次。使用上周的数据（例如4/27到5 / 3））：

Date         Value1     Value2
5/3/2014      56          13
5/10/2014     12          25
5/17/2014     90          52
5/24/2014     82          36
5/31/2014     76          98
6/7/2014      34          25
6/14/2014     13          63
6/21/2014     45          98
...

表2（每周执行和存储一次。使用上周的数据（例如4/27至5/3））：

Date         Value3      Value4
5/3/2014       54          62
5/10/2014      43          36
5/17/2014      90          43
5/24/2014      54          35
5/31/2014      76          45
6/7/2014       34          43
6/14/2014      23          37
6/21/2014      34          56
...

表3（每天从4/27开始执行和存储等等）：

Date         Value5      Value6
4/27/2014      56          45
4/28/2014      34          34
4/29/2014      23          34
4/30/2014      15          90
5/1/2014       34          23
5/2/2014       45          12
5/3/2014       46          35
5/4/2014       67          38
5/5/2014       34          23
...

我该如何写一个查询来按MONTH计算每一列？

How can I write a query which will calculate each column by MONTH?

我的表格如下：

Month     Value1     Value2     Value3     Value4     Value5     Value6
April      NULL       NULL       NULL       NULL       128         203
May        316        224        317        221        226         131

我有一个查询，该查询获取了当月的第一个月：

I have the query which gets the first of the current month:

CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, @DATE) as DATE)

下个月的第一个月

CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, DATEADD(m, 1, @DATE)) as DATE)

我认为查询可以是

SELECT
     DATENAME(MONTH, @DATE) as [Date],
     (SELECT [Value1] From [database].[dbo].[Table1]
          WHERE [Date] >= CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, @DATE) as DATE)
          AND [Date] < CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, DATEADD(m, 1, @DATE)) as DATE)
     ) AS [VAL1],
     (SELECT [Value2] From [database].[dbo].[Table1]
          WHERE [Date] >= CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, @DATE) as DATE)
          AND [Date] < CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, DATEADD(m, 1, @DATE)) as DATE)
     ) AS [VAL2],
     (SELECT [Value3] From [database].[dbo].[Table2]
          WHERE [Date] >= CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, @DATE) as DATE)
          AND [Date] < CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, DATEADD(m, 1, @DATE)) as DATE)
     ) AS [VAL3],
     (SELECT [Value4] From [database].[dbo].[Table2]
          WHERE [Date] >= CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, @DATE) as DATE)
          AND [Date] < CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, DATEADD(m, 1, @DATE)) as DATE)
     ) AS [VAL4],
     HOW TO DO VALUE5,
     HOW TO DO VALUE6
     INTO [database].[dbo].[tablemonthly]

我需要一些帮助来完成上述查询。

I am requesting some help in completing the above query.

此外，如何在上述查询中使用 IIF 语句，因此它只添加一次行并进行更新

Also, how can I use an IIF statement with the above query so it only adds the row once and update only Value5 and Value6 as it is DAILY and keep the other intact because it will be part of a bigger Stored Procedure which is ran daily?

我的三个表是：

p>

推荐答案

为确保获得所有月份，您需要拥有参考表-在这里，我使用CTE，但您可以制作该表。您也可以使用递归CTE根据输入来构建表，而不是像我在此处那样对值进行硬编码。

To make sure you get all the months you need to have a reference table -- here I use a CTE but you could just make the table. You could also use a recursive CTE to build the table based on input instead of hard coding the values as I did here.

注意，我使用的是Gordon提到的技术

Note I'm using the technique that Gordon mentioned to join all the data together in a union.

WITH Ranges AS
(
   SELECT 'April' AS M, '4/1/2014' AS [START], '4/30/2014' AS [END]
  UNION ALL
   SELECT 'May', '5/1/2014', '5/31/2014'
-- etc
), Data AS
(
   select [date], value1, value2, NULL as value3, NULL as value4, NULL as value5, NULL as value6 from table1
 union all
   select [date], NULL, NULL, value3, value4, NULL, NULL from table2
 union all
   select [date], NULL, NULL, NULL, NULL, value5, value6 from table3
)
, Joined AS
(
   SELECT M as [Month], Value1, Value2, Value3, Value4, Value5, Value6
   FROM Ranges R
   JOIN Data D ON D.[DATE] >= R.[START] AND D.[Date] <= R.[END]
)
SELECT [Month], SUM(Value1), SUM(Value2), SUM(Value3), SUM(Value4), SUM(Value5), SUM(Value6)
FROM Joined
GROUP BY [Month]

这篇关于如何从三个单独的表中获取每月数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！