How to get monthly data from three separate table

Question

I have three separate tables which stores data.

Table1 (executes and stores weekly. use data from last week (e.g. 4/27 to 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
...

Table2 (executes and stores weekly. use data from last week (e.g. 4/27 to 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
...

Table3 (executes and stores daily from 4/27 and so forth):

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

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

My table will look like this:

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)

First of the next month:

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

I am thinking my query can be something like this:

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.

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?

My Three tables is:

Answer 1

This query lists the YEAR, MONTH, and totals for VALUE#s.

Even though the query may be long, it would run very quickly, because the joins operate on a smaller subset of Table1, Table2, and Table3 (because they are first filtered). Then, they are joined using the Month and Year parts of the Date column. Finally, the data is ordered by Month and Year.

SELECT
COALESCE(DATENAME(YEAR, Table1.[Date]), DATENAME(YEAR, Table2.[Date]), DATENAME(YEAR, Table3.[Date])) AS Year
, COALESCE(DATENAME(MONTH, Table1.[Date]), DATENAME(MONTH, Table2.[Date]), DATENAME(MONTH, Table3.[Date])) AS Month 
, COALESCE(SUM(Table1.Value1), 0) Value1 
, COALESCE(SUM(Table1.Value2), 0) Value2 
, COALESCE(SUM(Table2.Value3), 0) Value3 
, COALESCE(SUM(Table2.Value4), 0) Value4 
, COALESCE(SUM(Table3.Value5), 0) Value5 
, COALESCE(SUM(Table3.Value6), 0) Value6 
FROM
(
  SELECT *
FROM Table1
WHERE [Date] >= CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, @DATE) as DATE)
AND [Date] < CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, DATEADD(m, 9, @DATE)) as DATE)
) Table1
FULL JOIN
(
  SELECT *
FROM Table2
WHERE [Date] >= CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, @DATE) as DATE)
AND [Date] < CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, DATEADD(m, 9, @DATE)) as DATE)
) Table2
ON DATENAME(MONTH, Table1.[Date]) = DATENAME(MONTH, Table2.[Date])
AND DATENAME(YEAR, Table1.[Date]) = DATENAME(YEAR, Table2.[Date])
FULL JOIN
(
  SELECT *
FROM Table3
WHERE [Date] >= CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, @DATE) as DATE)
AND [Date] < CAST(DATEADD(d, - DATEPART(d, @DATE) + 1, DATEADD(m, 9, @DATE)) as DATE)
) Table3
ON DATENAME(MONTH, Table1.[Date]) = DATENAME(MONTH, Table3.[Date])
AND DATENAME(YEAR, Table1.[Date]) = DATENAME(YEAR, Table3.[Date])
GROUP BY 
COALESCE(DATENAME(YEAR, Table1.[Date]), DATENAME(YEAR, Table2.[Date]), DATENAME(YEAR, Table3.[Date]))
, COALESCE(DATENAME(MONTH, Table1.[Date]), DATENAME(MONTH, Table2.[Date]), DATENAME(MONTH, Table3.[Date]))
ORDER BY 
COALESCE(DATENAME(YEAR, Table1.[Date]), DATENAME(YEAR, Table2.[Date]), DATENAME(YEAR, Table3.[Date]))
, MONTH(CAST(COALESCE(DATENAME(MONTH, Table1.[Date]), DATENAME(MONTH, Table2.[Date]), DATENAME(MONTH, Table3.[Date])) + ' 1 2014' AS DATETIME));

Here is the updated SQL Fiddle demo, which contains data for all sorts of combinations.

Answer 2

with MonthDataSummed(theMonth,value1,value2,value3,value4,value5,value6) as 
(
    select dateName(Month,date),Sum(value1),Sum(value2),null,null,null,null from table11
     group by dateName(Month,date)
     UNION all
     select dateName(Month,date),NULL,NULL,Sum(value3),Sum(value4),null,null 
     from table22
     group by dateName(Month,date)
     UNION all
     select dateName(Month,date),NULL,NULL,NULL,NULL,Sum(value5),Sum(value6) 
     from table33
     group by dateName(Month,date)
 )
,SummedFlattened(theMonth,value1,value2,value3,value4,value5,value6) as 
(
    select
    theMonth,Max(value1),Max(value2),Max(value3),Max(value4),Max(value5),Max(value6)
    from MonthDataSummed
    group by theMonth
)
SELECT * FROM SummedFlattened

Answer 3

I think the easiest way to do this by by union'ing the tables together and then extracting what you want:

select datename(month, @date) as [date],
       max(value1) as value1, max(value2) as value2,
       max(value3) as value3, max(value4) as value4,
       sum(value5) as value5, sum(value6) as value6
into [database].[dbo].[tablemonthly]
from ((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
      )
     ) t
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);

You can do multiple months at the same time by using group by.

EDIT:

The query for getting multiple months is:

select year([date]) as yr, month([date]) as mon,
       max(value1) as value1, max(value2) as value2,
       max(value3) as value3, max(value4) as value4,
       sum(value5) as value5, sum(value6) as value6
into [database].[dbo].[tablemonthly]
from ((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
      )
     ) t
group by year([date]), month([date])

(I also put the year in to avoid ambiguity.)

This replaces the where with a group by with the associated changes to the select.

Answer 4

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.

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]