0

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. – RDFozz Jul 17 '17 at 19:38
1

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

Instead of

2016     01    150
2016     03    100

Try this instead:

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.
| improve this answer | |
  • I get an error of Operand type clash: float is incompatible with date -- if i run each select statement independently no error. – Smith Stanley Jul 17 '17 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. – Smith Stanley Jul 17 '17 at 23:12
  • My apologies - cur and paste error. Corrected now. – RDFozz Jul 18 '17 at 14:05
1

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;
| improve this answer | |
  • very good explanation as to why the issue occurs! – Smith Stanley Jul 17 '17 at 23:07

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.