0

I have two queries and I want to join these two queries by CUSTOMERID

Query 1:

SELECT [CUSTOMERID], ISTAGBLOCKED,MONTH([CREATEDDATE]) AS MONTH, YEAR([CREATEDDATE]) AS YEAR
FROM [ISSUER].[HISTORY].[TP_BLACKLISTTAG] where [ISTAGBLOCKED] = 1
GROUP BY CUSTOMERID, ISTAGBLOCKED ,MONTH([CREATEDDATE]), YEAR([CREATEDDATE])

Query 2:

select customerid, MONTH([CREATEDDATE]) ,YEAR([CREATEDDATE]) FROM [ISSUER].[HISTORY].[TP_BLACKLISTTAG]
    where [CUSTOMERID] in (
    SELECT   [CUSTOMERID]
    FROM     [ISSUER].[HISTORY].[TP_BLACKLISTTAG] WHERE ISTAGBLOCKED = 1
    GROUP BY [CUSTOMERID],MONTH([CREATEDDATE]) ,YEAR([CREATEDDATE])
    HAVING   COUNT(*) > 10) AND ISTAGBLOCKED = 1
    group by customerid, MONTH([CREATEDDATE]) ,YEAR([CREATEDDATE])
    order by customerid, MONTH([CREATEDDATE]) ,YEAR([CREATEDDATE])

What I have tried but failed because throwing error:

invalid column t2month and t2year

SELECT t1.[CUSTOMERID], t1.t1month, t1.t1year,
t2.CUSTOMERID, t2.t2month, t2.t2year
FROM (SELECT [CUSTOMERID], MONTH([CREATEDDATE]) AS T1MONTH, YEAR([CREATEDDATE]) AS T1YEAR FROM [ISSUER].[HISTORY].[TP_BLACKLISTTAG] where [ISTAGBLOCKED] = 1
GROUP BY CUSTOMERID, MONTH([CREATEDDATE]), YEAR([CREATEDDATE])) as t1, (

select customerid, MONTH([CREATEDDATE]) ,YEAR([CREATEDDATE]) FROM [ISSUER].[HISTORY].[TP_BLACKLISTTAG]
where [CUSTOMERID] in (
SELECT   [CUSTOMERID], MONTH([CREATEDDATE]) AS T2MONTH ,YEAR([CREATEDDATE]) AS T2YEAR
FROM     [ISSUER].[HISTORY].[TP_BLACKLISTTAG] WHERE ISTAGBLOCKED = 1
GROUP BY [CUSTOMERID], MONTH([CREATEDDATE] AS T2MONTH,YEAR([CREATEDDATE]) AS T2YEAR
HAVING   COUNT(*) > 10) AND ISTAGBLOCKED = 1
group by customerid, MONTH([CREATEDDATE]) AS T2MONTH ,YEAR([CREATEDDATE]) AS T2YEAR
order by customerid, MONTH([CREATEDDATE]) AS T2MONTH ,YEAR([CREATEDDATE]) AS T2YEAR

) as t2
WHERE t1.CUSTOMERID = t2.CUSTOMERID
GROUP BY t1.[CUSTOMERID], t1.t1month, t1.t1year,
t2.CUSTOMERID, t2.t2month, t2.t2year

The queries are similar, but it is important to plot the requirement: "plot blacklisted frequent travelers travel dates vs blacklist dates".

0
3

There's a few issues here,

  • you're naming fields inside your group by and order by statements
  • You're not naming the selection fields from t2 which is where that specific error is coming from
  • You're not declaring the join (not neccessary but a lot easier for syntax for using ON after
  • Your where [CUSTOMERID] in ( statement returns three columns instead of just the required field
  • Ordering a sub query will produce an error unless you specifically need a top n from it

Also what are you expecting to get out of this, its going to return a mess of things

SELECT 
    t1.[CUSTOMERID]
    , t1.t1month
    , t1.t1year
    , t2.CUSTOMERID
    , t2.t2month
    , t2.t2year
FROM (
    SELECT 
        [CUSTOMERID] AS CUSTOMERID
        , MONTH([CREATEDDATE]) AS T1MONTH
        , YEAR([CREATEDDATE]) AS T1YEAR
    FROM [ISSUER].[HISTORY].[TP_BLACKLISTTAG] 
    where [ISTAGBLOCKED] = 1
    GROUP BY CUSTOMERID, MONTH([CREATEDDATE]), YEAR([CREATEDDATE])
    ) as t1
JOIN(
    select customerid AS CUSTOMERID
        , MONTH([CREATEDDATE]) AS T2MONTH
        ,YEAR([CREATEDDATE]) AS T2YEAR
    FROM [ISSUER].[HISTORY].[TP_BLACKLISTTAG]
    where 
        [CUSTOMERID] in (
            SELECT   [CUSTOMERID]
            FROM     [ISSUER].[HISTORY].[TP_BLACKLISTTAG] 
            WHERE ISTAGBLOCKED = 1
            GROUP BY 
                [CUSTOMERID]
                , MONTH([CREATEDDATE]
                ,YEAR([CREATEDDATE]
            HAVING   COUNT(*) > 10)
        AND ISTAGBLOCKED = 1
    group by customerid, MONTH([CREATEDDATE]),YEAR([CREATEDDATE])
) as t2
    on  t1.CUSTOMERID = t2.CUSTOMERID
        AND t1.T1MONTH = t2.T2MONTH
        AND t1.T1YEAR = t2.T2YEAR
GROUP BY 
    t1.[CUSTOMERID]
    , t1.t1month
    , t1.t1year
    , t2.CUSTOMERID
    , t2.t2month
    , t2.t2year

Should fix your bugs (don't have the table structure so can't 100% promise, but I don't think its going to get what you're wanting

2
  • Why the extra GROUP BY in the end? Jun 18 '18 at 11:18
  • 1
    In all honesty I was mainly just cleaning the code and removing errors, its not actually needed due to the group by inside the selects and then joining
    – Ste Bov
    Jun 18 '18 at 11:25
1

An alternative to using sub-queries as per Ste Bov's answer and your initial attempt is to defined the parts as CTEs and join them like so:

WITH Query1 AS (
        SELECT 
            [CUSTOMERID] AS CUSTOMERID
            , MONTH([CREATEDDATE]) AS T1MONTH
            , YEAR([CREATEDDATE]) AS T1YEAR
        FROM [ISSUER].[HISTORY].[TP_BLACKLISTTAG] 
        where [ISTAGBLOCKED] = 1
        GROUP BY CUSTOMERID, MONTH([CREATEDDATE]), YEAR([CREATEDDATE])
    )
   , Query2 AS (
        select customerid AS CUSTOMERID
            , MONTH([CREATEDDATE]) AS T2MONTH
            ,YEAR([CREATEDDATE]) AS T2YEAR
        FROM [ISSUER].[HISTORY].[TP_BLACKLISTTAG]
        where 
            [CUSTOMERID] in (
                SELECT   [CUSTOMERID]
                FROM     [ISSUER].[HISTORY].[TP_BLACKLISTTAG] 
                WHERE ISTAGBLOCKED = 1
                GROUP BY 
                    [CUSTOMERID]
                    , MONTH([CREATEDDATE]
                    ,YEAR([CREATEDDATE]
                HAVING   COUNT(*) > 10)
            AND ISTAGBLOCKED = 1
        group by customerid, MONTH([CREATEDDATE]),YEAR([CREATEDDATE])
    )
SELECT 
    t1.[CUSTOMERID]
    , t1.t1month
    , t1.t1year
    , t2.CUSTOMERID
    , t2.t2month
    , t2.t2year
FROM Query1 as t1
JOIN Query2 as t2
     ON  t1.CUSTOMERID = t2.CUSTOMERID
     AND t1.T1MONTH    = t2.T2MONTH
     AND t1.T1YEAR     = t2.T2YEAR
GROUP BY 
    t1.[CUSTOMERID]
    , t1.t1month
    , t1.t1year
    , t2.CUSTOMERID
    , t2.t2month
    , t2.t2year

This is just a rearrangement of Ste Bov's query, and the query planner should see it as equivalent so optimise it the same way, so which you use is mainly personal preference (some people find the CTE layout easier to read and maintain as it more obviously breaks the wall of code into smaller chunks, some either don't like it or avoid it because they need cross-DB compatibility with engines that don't support CTEs or don't support them efficiently).

Obviously you should choose better names than Query1 and Query2 when naming the CTE expressions - give them names that match what their functional intent is.

0

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.