Calculate Month to date , 6 month and Year to date for Union All Query

Question

I am trying to calculate the month to date and year to date for query created using Union All for two different tables one for receiving and one for sale. They both have the same Vendor name. I have created the query as bellow but can calculate the Month to date, last six month or year to date as I get error each time. Any idea please? I used the following code

SELECT VEND_NO,
        ACTV_DAT,
       SUM(RECVR_SUB_TOT)AS RECV_TOT,
       SUM(CALC_EXT_PRC) AS SAL_TOT

FROM ((SELECT P.VEND_NO, P.RECVR_DAT as ACTV_DAT, P.RECVR_SUB_TOT, 0 as CALC_EXT_PRC 
       FROM PO_RECVR_HIST P
      ) UNION ALL
      (SELECT S.ITEM_VEND_NO, S.BUS_DAT, 0, CALC_EXT_PRC
       FROM PS_TKT_HIST_LIN S
      )
     ) PS

GROUP BY VEND_NO,
ACTV_DAT,
CALC_EXT_PRC   
ORDER BY VEND_NO,
ACTV_DAT;

Conversion failed when converting character string to smalldatetime data type. — Bahaa Gerges, Nov 19 '18 at 02:23

score 1 · Accepted Answer · answered Nov 19 '18 at 01:43

Remove CALC_EXT_PRC from your group by clause

SELECT
    VEND_NO
  , ACTV_DAT
  , SUM( RECVR_SUB_TOT ) AS RECV_TOT
  , SUM( CALC_EXT_PRC )  AS SAL_TOT

FROM (
    SELECT
        P.VEND_NO
      , P.RECVR_DAT     AS ACTV_DAT
      , P.RECVR_SUB_TOT
      , 0               AS CALC_EXT_PRC
    FROM PO_RECVR_HIST P

    UNION ALL
    SELECT
        S.ITEM_VEND_NO
      , S.BUS_DAT
      , 0
      , CALC_EXT_PRC
    FROM PS_TKT_HIST_LIN S

) PS
GROUP BY
    VEND_NO
  , ACTV_DAT
ORDER BY
    VEND_NO
  , ACTV_DAT;

Also you don't need parentheses for each part of the union.

Calculate Month to date , 6 month and Year to date for Union All Query

1 Answers1