asked on

Joining 3 seperate queries into one query.

Hi,

I have the following 3 queries.

SELECT
de_listed_date
, COUNT(DISTINCT de_cl_account) count_per_day
, COUNT(CASE WHEN rnum = 1 THEN 1
ELSE NULL
END) new_count_per_day
FROM (
SELECT
de_listed_date
, de_cl_account
, ROW_NUMBER() OVER (PARTITION BY de_cl_account ORDER BY de_listed_date) rnum
FROM a_debtor
) derived
where de_listed_date between '2018-05-28' and '2018-06-01'
GROUP BY
de_listed_date
order by de_listed_date asc

SELECT
a_debtor.de_listed_date
-- ,Count(a_debtor.de_listed_date) AS Count_de_listed_date
,Sum(CASE
WHEN a_debtor.de_status != 'NEW' and a_contact.co_originator != 'ADM' and a_contact.co_originator != 'NAT' and a_contact.co_originator != 'GAD' and a_contact.co_originator != 'SDS'
THEN 1
ELSE 0
END)
-- ,a_contact.co_originator
FROM
a_debtor
LEFT JOIN a_contact ON a_contact.co_rowid_debtor = a_debtor.de_rowid
WHERE
a_debtor.de_listed_date BETWEEN '2018-05-21' AND '2018-06-01'
GROUP BY
a_debtor.de_listed_date
ORDER BY
a_debtor.de_listed_date

SELECT
a_debtor.de_listed_date
, Count(a_debtor.de_listed_date) AS Count_de_listed_date
FROM
a_debtor
left JOIN a_contact ON a_contact.co_rowid_debtor = a_debtor.de_rowid
where a_debtor.de_listed_date between '2018-05-28' and '2018-06-01' and (a_debtor.de_status = 'PFP' or a_debtor.de_status = 'PAR' or a_debtor.de_status = 'SOF' or a_debtor.de_status = 'CMD' or a_debtor.de_status = 'CRP' or a_debtor.de_status = 'CLO' or a_debtor.de_status = 'PAD' or a_debtor.de_status = 'TIO' or a_debtor.de_status = 'DSR' or a_debtor.de_status = 'ACA' or a_debtor.de_status = 'HAR' or a_debtor.de_status = 'FIN')
GROUP BY
a_debtor.de_listed_date
order by a_debtor.de_listed_date

Considering they all use similar grouping and order by, could someone assist to perhaps join the queries together? separately all three of them are correct and work.

your help is very much appreciated!!

ASKER CERTIFIED SOLUTION

ste5an

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PortletPaul

Ste5an has done it very nicely already.

However it's worth mentioning that this use of SUM:

SUM(CASE WHEN D.de_status != 'NEW'
AND NOT C.co_originator IN ( 'ADM', 'NAT', 'GAD', 'SDS' ) THEN 1
ELSE 0
END)

could be replaced with this instead:

COUNT(CASE WHEN D.de_status != 'NEW'
AND NOT C.co_originator IN ( 'ADM', 'NAT', 'GAD', 'SDS' ) THEN 1
ELSE NULL
END)

As I suggested in your previous question, to use a case expression inside a COUNT() just return NULL instead of zero. The count function just ignores NULLs. Using this approach would then be more consistent with the conditinal count I suggested for the first of the 3 queries.

I also agree with Ste5an that the left join in the third query looks completely redundant, perhaps you have forgotten to include something?

Also as I mentioned in an earlier question, it isn't essential to use CTEs for this, so more traditional method is:

SELECT *
FROM (
     SELECT   D.de_listed_date ,
              COUNT(DISTINCT D.de_cl_account) AS count_per_day ,
              COUNT(CASE WHEN D.rnum = 1 THEN 1
                         ELSE NULL
                    END) AS new_count_per_day
     FROM     (   SELECT de_listed_date ,
                         de_cl_account ,
                         ROW_NUMBER() OVER ( PARTITION BY de_cl_account
                                             ORDER BY de_listed_date ) AS rnum
                  FROM   a_debtor ) D
     WHERE    D.de_listed_date
     BETWEEN  '20180528' AND '20180601'
     GROUP BY D.de_listed_date 
    ) AS Q1
LEFT JOIN (
     SELECT   D.de_listed_date ,
              SUM(CASE WHEN D.de_status != 'NEW'
                            AND NOT C.co_originator IN ( 'ADM', 'NAT', 'GAD', 'SDS' ) THEN 1
                       ELSE 0
                  END)
     FROM     a_debtor D
              LEFT JOIN a_contact D ON C.co_rowid_debtor = D.de_rowid
     WHERE    D.de_listed_date
     BETWEEN  '20180521' AND '20180601'
     GROUP BY D.de_listed_date
    ) AS Q2 ON Q1.de_listed_date = Q2.de_listed_date
LEFT JOIN (
     SELECT   D.de_listed_date ,
              COUNT(D.de_listed_date) AS Count_de_listed_date
     FROM     a_debtor D
              /* LEFT JOIN a_contact C ON C.co_rowid_debtor = D.de_rowid */ /* appears to be redundant */
     WHERE    D.de_listed_date
              BETWEEN '20180528' AND '20180601'
              AND D.de_status IN ( 'PFP', 'PAR', 'SOF', 'CMD', 'CRP', 'CLO', 'PAD', 'TIO', 'DSR', 'ACA', 'HAR', 'FIN' )
     GROUP BY D.de_listed_date
    ) AS Q3 ON Q1.de_listed_date = Q3.de_listed_date
;

Open in new window

no points required please

James Murphy

ASKER

actually you are absolutely correct! when I was building the 3rd query - I had put the join in, in order to create one of the fields that I had already done in a separate query. It is definitely completely redundant!

many thanks, it may take me a little time to work through these, I like to ensure I completely understand the query before I use them!

The server version is SQL 2017.

James Murphy

ASKER

Hi,

I got this message when I attempted to run it:

Error executing query:
Select Top 1000 * From (Select D.de_listed_date, Count(Distinct D.de_cl_account) As count_per_day, Count(Case When D.rnum = 1 Then 1 Else Null End) As new_count_per_day From (Select a_debtor.de_listed_date, a_debtor.de_cl_account, Row_Number() Over (Partition By a_debtor.de_cl_account Order By a_debtor.de_listed_date) As rnum From a_debtor) D Where D.de_listed_date Between '20180528' And '20180601' Group By D.de_listed_date) As Q1 Left Join (Select D.de_listed_date, Sum(Case When D.de_status != 'NEW' And Not C.co_originator In ('ADM', 'NAT', 'GAD', 'SDS') Then 1 Else 0 End) From a_debtor D Left Join a_contact D On C.co_rowid_debtor = D.de_rowid Where D.de_listed_date Between '20180521' And '20180601' Group By D.de_listed_date) As Q2 On Q1.de_listed_date = Q2.de_listed_date Left Join (Select D.de_listed_date, Count(D.de_listed_date) As Count_de_listed_date From a_debtor D Where D.de_listed_date Between '20180528' And '20180601' And D.de_status In ('PFP', 'PAR', 'SOF', 'CMD', 'CRP', 'CLO', 'PAD', 'TIO', 'DSR', 'ACA', 'HAR', 'FIN') Group By D.de_listed_date) As Q3 On Q1.de_listed_date = Q3.de_listed_date
Message:
The correlation name 'D' is specified multiple times in a FROM clause

Scott Pletcher

I think you could actually combine them completely, eliminating multiple passes of the table:

SELECT
derived.de_listed_date
, COUNT(DISTINCT CASE WHEN derived.de_listed_date >= '20180528' THEN derived.de_cl_account END) AS count_per_day
, COUNT(CASE WHEN derived.de_listed_date >= '20180528' AND derived.rnum = 1 THEN 1 END) AS new_count_per_day
, SUM(CASE
WHEN derived..de_status != 'NEW' and a_contact.co_originator NOT IN ('ADM', 'GAD', 'NAT', 'SDS')
THEN 1
ELSE 0
END) AS [?column_name_here?]
, SUM(CASE WHEN derived.de_listed_date >= '20180528' THEN 1 ELSE 0 END) AS Count_de_listed_date
FROM (
SELECT
de_listed_date
, de_cl_account
, de_rowid
, ROW_NUMBER() OVER (PARTITION BY de_cl_account ORDER BY de_listed_date) rnum
FROM a_debtor
) derived
LEFT JOIN a_contact ON a_contact.co_rowid_debtor = derived.de_rowid
WHERE de_listed_date between '20180521' and '20180601'
GROUP BY
de_listed_date
ORDER BY de_listed_date

James Murphy

ASKER

Hi Ste5an,

I am getting an error stating I am using D to many times in from column should I use D & D1 and D2 as the derived names?

James Murphy

ASKER

Thank you for your help, once I had some sleep and re-read the query etc, it worked exactly as needed.

many thanks!