# How can I obtain "Month By Month" data?

I want to show a sum for each month that falls between the variables `@startdate` and `@enddate` - I think my issue is stemming from the `Group By` I have to add at the end so SSMS does not bark at me.

Current output:

``````Year Month Cash
1900  01   300
``````

I want to see:

``````Year    Month   Cash
2016    01      150
2016    02      50
2016    03      100
``````

Here is query I attempt:

``````Declare @startdate date = '20160101', @enddate date = '20160331'

SELECT
YEAR(satDate) [Year], MONTH(satDate) [Month],
SUM(ISNULL(A.truckrv,0)-ISNULL(A.DC,0)) As CashAmt
FROM
(
SELECT
ZT1.empName,
SUM(ISNULL(ZT1.[truckrv],0)) AS truckrv,
drivedate As TDate,
0 As satDate,
0 As DC
FROM truck AS ZT1
WHERE ZT1.[drivedate] >= @startdate
AND ZT1.[drivedate] <= @enddate
GROUP BY ZT1.empName, ZT1.drivedate
UNION ALL
Select
t1.empName,
0 As Tdate,
satDate
,0 As truckrv
,SUM(ISNULL(t1.DC,0)) As DC
FROM car t1
WHERE t1.[satDate] >= @startdate
AND t1.[satDate] <= @enddate
Group By t1.empName, t1.satDate
) AS A
GROUP BY A.satDate
``````

How should this query be altered in order to provide a "Month by Month" result set?

• Your query explicit sets `satDate` to 0 for the upper query in your `UNION ALL`; that gives every row there the same year and month (0 date is 1900-01-01 00:00:00). All I can guess if that the lower half of the query is returning no rows. Commented Jul 17, 2017 at 19:38

I'll take a couple of wild guesses here:

• In the `truck` table, the date you want to look at is in the column `driveDate`
• In the `car` table the date you want to look at is in the `satDate` column.
• Your `SUM` should reflect the total of `truckrv` from the `truck` table, and `DC` from the `car` table.

You problem appears to be (in part, at least) a lack of understanding as to how UNION ALL works.

When doing a `UNION` 9or `UNION ALL`) of two result sets, what goes in what column is determined solely by the order in which the columns appear, and the names of the columns is determined by the names in the first result set. You seem to be assuming that your columns will be mapped together based on the column names you provide.

In both parts of the query, the fourth column is explicitly set to 0. In the first query, that column is named `satDate`. So, `satDate` is 1900-01-01 00:00:00 (the "zero" date) for all columns.

In addition, you're grouping by `satDate`. So, even if that was the date values you want, you would get daily totals, showing the month and year of the current date. For instance, if there was only data for Jan 1-3 and March 1-3, you'd get back something like:

``````2016     01     75
2016     01     25
2016     01     50
2016     03     25
2016     03     25
2016     03     50
``````

``````2016     01    150
2016     03    100
``````

``````Declare @startdate date = '20160101', @enddate date = '20160331'

SELECT
YEAR(activityDate) as [Year],
MONTH(activityDate) as [Month],
SUM(CashAmt) As CashAmt
FROM
(
SELECT
ZT1.empName,
drivedate As activityDate,
SUM(ISNULL(ZT1.[truckrv],0)) AS CashAmt
FROM truck AS ZT1
WHERE ZT1.[drivedate] >= @startdate
AND ZT1.[drivedate] <= @enddate
GROUP BY ZT1.empName, ZT1.drivedate
UNION ALL
Select
t1.empName,
satDate as activityDate,
SUM(ISNULL(t1.DC,0)) As DC
FROM car t1
WHERE t1.[satDate] >= @startdate
AND t1.[satDate] <= @enddate
Group By t1.empName, t1.satDate
) AS A
GROUP BY YEAR(activityDate), MONTH(activityDate)
;
``````

This should:

• place the applicable date from both tables into a column labeled `activityDate`
• place the cash amount from both tables into a column labeled `CashAmt`
• Actually break down your total by month and year, instead of by date.
• I get an error of Operand type clash: float is incompatible with date -- if i run each select statement independently no error. Commented Jul 17, 2017 at 23:05
• I got it - had to re-arrange the columns in the second select statement to match the order of the 1st then PRESTO! Exactly what I was after. Commented Jul 17, 2017 at 23:12
• My apologies - cur and paste error. Corrected now. Commented Jul 18, 2017 at 14:05

Likely what's happening here is that your second set of data (after the `UNION ALL` does not return any data, and so only the first part is returning anything. Because you are passing a 0 for the date, this is 1900-01-01. All of the results are going to get grouped for that date.

Here's a similar mockup, but this one returns data after the `UNION ALL`

``````DECLARE @t TABLE
(
C1 INT ,
C2 DATETIME ,
C3 INT
);

INSERT  INTO @t
VALUES  ( 1, '2016-01-01', 100 )
,       ( 1, '2016-01-01', 250 )
,       ( 1, '2016-03-05', 200 )
,       ( 2, '2016-02-01', 200 )
,       ( 2, '2016-02-01', 500 )
,       ( 2, '2016-06-01', 200 );

SELECT  YEAR(C2) [Year] ,
MONTH(C2) [Month] ,
SUM(C3)
FROM    ( SELECT    0 AS C2 ,
SUM(C3) AS C3
FROM      @t
WHERE     C1 = 1
GROUP BY  C2
UNION ALL
SELECT    C2 ,
SUM(C3) AS C3
FROM      @t
WHERE     C1 = 2
GROUP BY  C2
) AS A
GROUP BY C2;
``````
• very good explanation as to why the issue occurs! Commented Jul 17, 2017 at 23:07