How to do a cte or temp table off a few queries that joined via a union for querying?

James Murphy
James Murphy used Ask the Experts™
on
Hi,

Firstly thank you for taking a look at my question.

Say that I have the below queries that are working nicely, but I wanted to put those into a Temp Table or a CTE, so that I can query it, what would be the best way of going about this? Basically I need to do a group by on Area on the below results.

select 'ACT Regional' as [Area] ,count(de_listed_date) as [Num Listed], sum(de_principal) as [$ Listed] ,sum(de_adjustments) as [Adjustments] ,sum(de_paid) as [Paid],sum(case when de_status IN ('PAR','PFP','HAR','PAR','PAD','SOF','PAB') then de_owing else 0.00 END) as [Promised $], sum(case when de_status = 'REC' then 1 else 0 END) as [Recalled #], sum(case when de_status in ('PDF','PLR','SET') then 1 else 0 END) as [Paid & Closed]
from debtor
join client on de_rowid_client = cl_rowid
where de_zip between '2611' and '2620' and cl_number IN ('107','118','117') and de_listed_date > '2016-12-01'
UNION ALL
select 'Adelaide Metro' as [Area] ,count(de_listed_date) as [Num Listed], sum(de_principal) as [$ Listed] ,sum(de_adjustments) as [Adjustments] ,sum(de_paid) as [Paid],sum(case when de_status IN ('PAR','PFP','HAR','PAR','PAD','SOF','PAB') then de_owing else 0.00 END) as [Promised $], sum(case when de_status = 'REC' then 1 else 0 END) as [Recalled #], sum(case when de_status in ('PDF','PLR','SET') then 1 else 0 END) as [Paid & Closed]
from debtor
join client on de_rowid_client = cl_rowid
where de_zip between '5000' and '5199' and cl_number IN ('107','118','117') and de_listed_date > '2016-12-01'
UNION ALL
select 'Brisbane Metro' as [Area] ,count(de_listed_date) as [Num Listed], sum(de_principal) as [$ Listed] ,sum(de_adjustments) as [Adjustments] ,sum(de_paid) as [Paid],sum(case when de_status IN ('PAR','PFP','HAR','PAR','PAD','SOF','PAB') then de_owing else 0.00 END) as [Promised $], sum(case when de_status = 'REC' then 1 else 0 END) as [Recalled #], sum(case when de_status in ('PDF','PLR','SET') then 1 else 0 END) as [Paid & Closed]
from debtor
join client on de_rowid_client = cl_rowid
where de_zip between '4000' and '4207' and cl_number IN ('107','118','117') and de_listed_date > '2016-12-01'

Open in new window


Any help you could provide would be most appreciated!

cheers
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
As a temp table #temp..
select 'ACT Regional' as [Area] ,count(de_listed_date) as [Num Listed], sum(de_principal) as [$ Listed] ,sum(de_adjustments) as [Adjustments] ,sum(de_paid) as [Paid],sum(case when de_status IN ('PAR','PFP','HAR','PAR','PAD','SOF','PAB') then de_owing else 0.00 END) as [Promised $], sum(case when de_status = 'REC' then 1 else 0 END) as [Recalled #], sum(case when de_status in ('PDF','PLR','SET') then 1 else 0 END) as [Paid & Closed]
INTO #temp
from debtor
join client on de_rowid_client = cl_rowid
where de_zip between '2611' and '2620' and cl_number IN ('107','118','117') and de_listed_date > '2016-12-01'
UNION ALL
select 'Adelaide Metro' as [Area] ,count(de_listed_date) as [Num Listed], sum(de_principal) as [$ Listed] ,sum(de_adjustments) as [Adjustments] ,sum(de_paid) as [Paid],sum(case when de_status IN ('PAR','PFP','HAR','PAR','PAD','SOF','PAB') then de_owing else 0.00 END) as [Promised $], sum(case when de_status = 'REC' then 1 else 0 END) as [Recalled #], sum(case when de_status in ('PDF','PLR','SET') then 1 else 0 END) as [Paid & Closed]
from debtor
join client on de_rowid_client = cl_rowid
where de_zip between '5000' and '5199' and cl_number IN ('107','118','117') and de_listed_date > '2016-12-01'
UNION ALL
select 'Brisbane Metro' as [Area] ,count(de_listed_date) as [Num Listed], sum(de_principal) as [$ Listed] ,sum(de_adjustments) as [Adjustments] ,sum(de_paid) as [Paid],sum(case when de_status IN ('PAR','PFP','HAR','PAR','PAD','SOF','PAB') then de_owing else 0.00 END) as [Promised $], sum(case when de_status = 'REC' then 1 else 0 END) as [Recalled #], sum(case when de_status in ('PDF','PLR','SET') then 1 else 0 END) as [Paid & Closed]
from debtor
join client on de_rowid_client = cl_rowid
where de_zip between '4000' and '4207' and cl_number IN ('107','118','117') and de_listed_date > '2016-12-01'

Open in new window

OR as a cte like this..
; with cte as (
select 'ACT Regional' as [Area] ,count(de_listed_date) as [Num Listed], sum(de_principal) as [$ Listed] ,sum(de_adjustments) as [Adjustments] ,sum(de_paid) as [Paid],sum(case when de_status IN ('PAR','PFP','HAR','PAR','PAD','SOF','PAB') then de_owing else 0.00 END) as [Promised $], sum(case when de_status = 'REC' then 1 else 0 END) as [Recalled #], sum(case when de_status in ('PDF','PLR','SET') then 1 else 0 END) as [Paid & Closed]
from debtor
join client on de_rowid_client = cl_rowid
where de_zip between '2611' and '2620' and cl_number IN ('107','118','117') and de_listed_date > '2016-12-01'
UNION ALL
select 'Adelaide Metro' as [Area] ,count(de_listed_date) as [Num Listed], sum(de_principal) as [$ Listed] ,sum(de_adjustments) as [Adjustments] ,sum(de_paid) as [Paid],sum(case when de_status IN ('PAR','PFP','HAR','PAR','PAD','SOF','PAB') then de_owing else 0.00 END) as [Promised $], sum(case when de_status = 'REC' then 1 else 0 END) as [Recalled #], sum(case when de_status in ('PDF','PLR','SET') then 1 else 0 END) as [Paid & Closed]
from debtor
join client on de_rowid_client = cl_rowid
where de_zip between '5000' and '5199' and cl_number IN ('107','118','117') and de_listed_date > '2016-12-01'
UNION ALL
select 'Brisbane Metro' as [Area] ,count(de_listed_date) as [Num Listed], sum(de_principal) as [$ Listed] ,sum(de_adjustments) as [Adjustments] ,sum(de_paid) as [Paid],sum(case when de_status IN ('PAR','PFP','HAR','PAR','PAD','SOF','PAB') then de_owing else 0.00 END) as [Promised $], sum(case when de_status = 'REC' then 1 else 0 END) as [Recalled #], sum(case when de_status in ('PDF','PLR','SET') then 1 else 0 END) as [Paid & Closed]
from debtor
join client on de_rowid_client = cl_rowid
where de_zip between '4000' and '4207' and cl_number IN ('107','118','117') and de_listed_date > '2016-12-01'
) 
select * from cte 

Open in new window

Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> Basically I need to do a group by on Area on the below results.

Seems like you already have 3 SELECT queries to select 3 areas, kindly explain with some sample records on how you wish to group by further so that we can guide you accordingly..

Author

Commented:
thank you! That was precisely what i needed!
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Welcome, glad to assist!!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial