CODEWITHSUNDEEP
sql-servert-sqlselectjoinunion
sams
sams

Reputation: 449

Joining two SELECT statements?

I'm trying to combine two SELECT statements into one using SQL Server 2012 SP1, to compare this year versus last years figures.

The problem I'm having is that the first SELECT is repeated 12 times (one year).

What I would like is this:

CY_Month_Year   CY_Adviser_Commission   PY_Month_Year   PY_Adviser_Commission
April 2014      8462.561250000000       April 2013      14493.773250000000
August 2014    11092.801500000000       August 2013     20284.555500000000

This is the current example of the output:

CY_Month_Year   CY_Adviser_Commission   PY_Month_Year   PY_Adviser_Commission
April 2014      8462.561250000000       April 2013      14493.773250000000
April 2014      8462.561250000000       August 2013     20284.555500000000
April 2014      8462.561250000000       December 2013    4782.624000000000
April 2014      8462.561250000000       February 2013   11581.927500000000
April 2014      8462.561250000000       January 2013     4823.082000000000
April 2014      8462.561250000000       July 2013       13607.822250000000
April 2014      8462.561250000000       June 2013       43582.885500000000
April 2014      8462.561250000000       March 2013      17677.662750000000
April 2014      8462.561250000000       May 2013        12642.168000000000
April 2014      8462.561250000000       November 2013   12698.284500000000
April 2014      8462.561250000000       October 2013     6730.836750000000
April 2014      8462.561250000000       September 2013  12339.673500000000
August 2014    11092.801500000000       April 2013      14493.773250000000
...

Here is the SQL code I have so far: 

SELECT DISTINCT CY_Month_Year
    ,CY_Adviser_Commission
    ,PY_Month_Year
    ,PY_Adviser_Commission
FROM 
    -- Current year
    (
    SELECT DISTINCT DATE.Month_Name AS CY_Month_Year
        ,sum(ADV_COMM) AS CY_Adviser_Commission
    FROM vw_Invoice_Data
    INNER JOIN DATE ON vw_Invoice_Data.TransDate = DATE.PK_Date
    WHERE DATE.Fiscal_Month BETWEEN '2014-01-01 00:00:00'
            AND '2014-12-01 00:00:00'
        AND vw_Invoice_Data.broker_code IN ('1-ABC') 
    GROUP BY DATE.month
        ,DATE.month_Name
    ) TY_Inv
    ,
    -- Current year minus one
    (
        SELECT DISTINCT DATE.Month_Name AS PY_Month_Year
            ,sum(ADV_COMM) AS PY_Adviser_Commission
        FROM vw_Invoice_Data id
        INNER JOIN DATE ON id.TransDate = DATE.PK_Date
        WHERE DATE.Fiscal_Month BETWEEN '2013-01-01 00:00:00'
                AND '2013-12-01 00:00:00'
            AND id.broker_code IN ('1-ABC')
        GROUP BY DATE.month
            ,DATE.month_Name
        ) PY_Inv

Any help would be much appreciated.

Daniel

Upvotes: 0

Views: 77

Answers (1)

Recursive
Recursive

Reputation: 952

Updated with other options ! I have used your query, formed a id column with month and join them with that.

SELECT DISTINCT CY_Month_Year,
                CY_Adviser_Commission,
                PY_Month_Year,
                PY_Adviser_Commission
FROM
-- Current year
(SELECT DISTINCT Substring(Month_Name, 0, Len(Month_Name) - 4) AS id,
                 DATE.Month_Name                               AS CY_Month_Year,
                 Sum(ADV_COMM)                                 AS CY_Adviser_Commission
 FROM   vw_Invoice_Data
        INNER JOIN DATE
                ON vw_Invoice_Data.TransDate = DATE.PK_Date
 WHERE  DATE.Fiscal_Month BETWEEN '2014-01-01 00:00:00' AND '2014-12-01 00:00:00'
        AND vw_Invoice_Data.broker_code IN ( '1-ABC' )
 GROUP  BY DATE.month,
           DATE.month_Name) TY_Inv
JOIN
-- Current year minus one
(SELECT DISTINCT Substring(Month_Name, 0, Len(Month_Name) - 4) AS id,
                 DATE.Month_Name                               AS PY_Month_Year,
                 Sum(ADV_COMM)                                 AS PY_Adviser_Commission
 FROM   vw_Invoice_Data id
        INNER JOIN DATE
                ON id.TransDate = DATE.PK_Date
 WHERE  DATE.Fiscal_Month BETWEEN '2013-01-01 00:00:00' AND '2013-12-01 00:00:00'
        AND id.broker_code IN ( '1-ABC' )
 GROUP  BY DATE.month,
           DATE.month_Name) PY_Inv
  ON TY_Inv.id = PY_Inv.id

So this typically joins months of two different years!

This can also be tried

;WITH Y1
     AS (SELECT MONTH(TRANSDATE)AS MONTHS,
                MONTH_NAME,
                ADV_COMM
         FROM   VW_INVOICE_DATA
                INNER JOIN DATE
                        ON VW_INVOICE_DATA.TRANSDATE = DATE.PK_DATE
         WHERE  DATE.FISCAL_MONTH BETWEEN '2013-01-01 00:00:00' AND '2013-12-01 00:00:00'
                AND ID.BROKER_CODE IN ( '1-ABC' )),
     Y2
     AS (SELECT MONTH(TRANSDATE)AS MONTHS,
                MONTH_NAME,
                ADV_COMM
         FROM   VW_INVOICE_DATA
                INNER JOIN DATE
                        ON VW_INVOICE_DATA.TRANSDATE = DATE.PK_DATE
         WHERE  DATE.FISCAL_MONTH BETWEEN '2014-01-01 00:00:00' AND '2014-12-01 00:00:00'
                AND ID.BROKER_CODE IN ( '1-ABC' ))
SELECT Y1.MONTH_NAME,
       SUM(Y1.ADV_COMM),
       Y2.MONTH_NAME,
       SUM(Y2.ADV_COMM)
FROM   Y1
       JOIN Y2
         ON Y1.MONTHS = Y2.MONTHS
GROUP  BY Y1.MONTH_NAME,
          Y2.MONTH_NAME

if u have the date column in invoice data table you can avoid the join to "date" and directly use year(datecolumn)=2013/2014/...

Upvotes: 1

Related Questions