CTE and Union All

Question

CTE and Union All

Janie.Carlisle

Ten Centuries

Points: 1377
More actions
November 5, 2010 at 12:08 pm

#228812

Hi. I wrote a query to select the Top25 records from my dataset. I now want to write another query to select the records that are NOT in that result set and union the two sets of result to generate a report. How do I do that? I thought I could use 2 CTE's but that didn't seem to work.
with top25 as
(
SELECT DISTINCT TOP 25
A.Account
, b.Name1
, COUNT(A.Account) OVER(PARTITION BY Account) AS Cnt
, SUM(A.Amount) OVER(PARTITION BY Account) AS Amount
FROM Resolved a
INNER JOIN Customer b on a.account = b.rf_kunnr
WHERE a.documentnumber LIKE '79%'
and a.LoadID = dbo.dbFunc_Determine_CurRptMth_LoadID()
ORDER BY Amount
)
, balance as
(
select distinct
A.Account
, b.Name1
, COUNT(A.Account) OVER(PARTITION BY Account) AS Cnt
, SUM(A.Amount) OVER(PARTITION BY Account) AS Amount
FROM Resolved a
INNER JOIN Customer b on a.account = b.rf_kunnr
inner join top25 on a.account = top25.account
WHERE a.documentnumber LIKE '79%'
and a.LoadID = dbo.dbFunc_Determine_CurRptMth_LoadID()
and a.account not in top25
)

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

WayneS SSC Guru Points: 95460 More actions · Answer 1

Janie.Carlisle (11/5/2010)
Hi. I wrote a query to select the Top25 records from my dataset. I now want to write another query to select the records that are NOT in that result set and union the two sets of result to generate a report. How do I do that? I thought I could use 2 CTE's but that didn't seem to work.
with top25 as
(
SELECT DISTINCT TOP 25
A.Account
, b.Name1
, COUNT(A.Account) OVER(PARTITION BY Account) AS Cnt
, SUM(A.Amount) OVER(PARTITION BY Account) AS Amount
FROM Resolved a
INNER JOIN Customer b on a.account = b.rf_kunnr
WHERE a.documentnumber LIKE '79%'
and a.LoadID = dbo.dbFunc_Determine_CurRptMth_LoadID()
ORDER BY Amount
)
, balance as
(
select distinct
A.Account
, b.Name1
, COUNT(A.Account) OVER(PARTITION BY Account) AS Cnt
, SUM(A.Amount) OVER(PARTITION BY Account) AS Amount
FROM Resolved a
INNER JOIN Customer b on a.account = b.rf_kunnr
inner join top25 on a.account = top25.account
WHERE a.documentnumber LIKE '79%'
and a.LoadID = dbo.dbFunc_Determine_CurRptMth_LoadID()
EXCEPT
SELECT Account, Name1, Cnt, Amount
FROM top25
)

See changes above (last three lines, bolded)

Edit: struck out line that's keeping it from returning the same 25.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Janie.Carlisle Ten Centuries Points: 1377 More actions · Answer 2

Oddly enough that resulted in just the original 25 records. There are 380 records so Top 25 should of course be 25 and the Balance should be 355 records. I added the union all statement to union Top 25 with Balance but still only got 25 records.

Pam Brisjar SSChampion Points: 12094 More actions · Answer 3

Why in the world do you need to do this with two different queries when all you're doing is mashing them back together?

If you need to know which ones are the top 25 and mark that in some way other than the presentation / data order, look at the ROW_NUMBER() function.

WayneS SSC Guru Points: 95460 More actions · Answer 4

See the edit I made in the query.

I would have to agree with Pam... what's the purpose of this? It would be simpler to just run the one query.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Janie.Carlisle Ten Centuries Points: 1377 More actions · Answer 5

Hi Everyone,

Thanks for your suggestions - none of which actually solved the problem. However, they caused me to think in a different direction and I was able to get the results I was looking for which did require the use of a CTE to create the block of data I needed to work with. The issue with the other solutions was that either too much data (10,220 records) or too little data (25 records) was being returned. The desired result set is 380 records which I now have. Thanks again.

Janie

WayneS SSC Guru Points: 95460 More actions · Answer 6

Would you mind posting your solution, so that we can see how it differs?

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

Janie.Carlisle Ten Centuries Points: 1377 More actions · Answer 7

WITH basedata AS

(

SELECT DISTINCT

Account

, sum(amount) over (partition by account) Total

FROM Resolved

WHERE documentnumber LIKE '79%'

AND LoadID = dbo.dbFunc_Determine_CurRptMth_LoadID()

GROUP BY Account

, Amount

)

SELECT DISTINCT

basedata.Account

, basedata.Total

, b.Name1 as Customer

, COUNT(basedata.Account) OVER(PARTITION BY basedata.Account) AS Cnt

, SUM(datediff(dd, a.documentdate, a.clearingdate)) over (partition by basedata.account)/

COUNT(basedata.Account) OVER(PARTITION BY basedata.Account) AS Avg_Days

FROM basedata

INNER JOIN Customer b on basedata.Account = b.RF_Account

INNER JOIN Resolved a on basedata.Account = a.Account

ORDER BY basedata.total desc

WayneS SSC Guru Points: 95460 More actions · Answer 8

Thanks.

Out of curiosity, what does dbo.dbFunc_Determine_CurRptMth_LoadID do? Where you are using it, it is obviously a scalar function instead of a table-valued function. Those would be a lot faster.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2