How to merge sql query with union into one query

debugcn 投稿 Dev

Seeker

I would like to get the results of the below query with just one query not by using union. My query is as below

I am generating a SSRS chart this query, so need to merge the query into one and get a proper result as shown in table 2

select 
    res.Count, res.Month, res.status, res.SortOrder 
from 
    (SELECT 
        count(analysis_complete_date) as Count,
        DATENAME(month, analysis_complete_date) AS Month, 
        DATEPART(month, analysis_complete_date) AS SortOrder,
        'Analysis' as status
     FROM  
        SCN_Part_Details AS parts
     WHERE  
        analysis_complete_date BETWEEN '2014-01-01' AND '2014-12-11'
     GROUP BY 
        DATENAME(month, analysis_complete_date), 
        DATEPART(month, analysis_complete_date)

     union

     SELECT 
        count(Act_Supp_Negotiation_Date) as Count,
        DATENAME(month, Act_Supp_Negotiation_Date) AS Month, 
        DATEPART(month, Act_Supp_Negotiation_Date) AS SortOrder,
        'Negotiated' as status
     FROM  
        SCN_Part_Details AS parts
     WHERE  
        Act_Supp_Negotiation_Date BETWEEN '2014-01-01' AND '2014-12-11'
     GROUP BY 
        DATENAME(month, Act_Supp_Negotiation_Date), 
        DATEPART(month, Act_Supp_Negotiation_Date) ) as res
order by 
    res.SortOrder

This will give a result like:

Table 1

Count          Month          Status     SortOrder
--------------------------------------------------
167            January       Analysis     1
631            January       Negotiated   1
70             February      Analysis     2
237            February      Negotiated   2

and so on..

I want a result like this:

Table 2

AnalysisCount    NegotiatedCount    Month       SortOrder
---------------------------------------------------------
167                 631             January      1
70                  237             February     2

Veera

Give it a try:

 ;WITH CTEResult AS 
(
    select 
        res.Count, res.Month, res.status, res.SortOrder 
    from 
        (SELECT 
            count(analysis_complete_date) as Count,
            DATENAME(month, analysis_complete_date) AS Month, 
            DATEPART(month, analysis_complete_date) AS SortOrder,
            'Analysis' as status
         FROM  
            SCN_Part_Details AS parts
         WHERE  
            analysis_complete_date BETWEEN '2014-01-01' AND '2014-12-11'
         GROUP BY 
            DATENAME(month, analysis_complete_date), 
            DATEPART(month, analysis_complete_date)

         union

         SELECT 
            count(Act_Supp_Negotiation_Date) as Count,
            DATENAME(month, Act_Supp_Negotiation_Date) AS Month, 
            DATEPART(month, Act_Supp_Negotiation_Date) AS SortOrder,
            'Negotiated' as status
         FROM  
            SCN_Part_Details AS parts
         WHERE  
            Act_Supp_Negotiation_Date BETWEEN '2014-01-01' AND '2014-12-11'
         GROUP BY 
            DATENAME(month, Act_Supp_Negotiation_Date), 
            DATEPART(month, Act_Supp_Negotiation_Date) ) as res
)

SELECT DISTINCT
  (SELECT TOP 1 Count FROM CTEResult A WHERE A.Month = C.Month AND A.STATUS =  'Analysis' AND A.SortOrder = C.SortOrder) AS  AnalysisCount,
  (SELECT TOP 1 Count FROM CTEResult B WHERE B.Month = C.Month AND B.STATUS =  'Negotiated' AND B.SortOrder = C.SortOrder) AS  
    NegotiatedCount, C.Month, C.SortOrder 
FROM CTEResult C

この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。

侵害の場合は、連絡してください[email protected]