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

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
James MurphyAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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..
James MurphyAuthor Commented:
thank you! That was precisely what i needed!
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome, glad to assist!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.