How yo make these two queires to one please

Question

How yo make these two queires to one please

asita

SSCarpal Tunnel

Points: 4144
More actions
December 27, 2013 at 10:56 pm

#286675

Hello There,
Could you please assist me to solve this, basically the below is 2 sql statements how can i convert these two queries to one, please
---Query 1
select
languagetype ,
Indication,
COUNT(AcctID) as Clients,
AVG(TaxRate1) as SavingRtAvg_RT,
SUM(BeforeStats) as BeforeSecs,
CASE WHEN Indication = 'Action' THEN COUNT(AcctID) END as CheckerPark,
CASE WHEN Indication = 'NoAction' THEN COUNT(AcctID) END as NONCheckerPark
INTO #Temp
from Mars.Outfitter
where period = 'Q1-2013'
group by
languagetype ,
Indication
order by
LanguageType,
Indication
-------------------------
---Query 2----
select
languagetype ,
a.Indication,
a.Clients,
a.SavingRtAvg_RT,
a.BeforeSecs,
(CONVERT(Decimal(18,4),a.CheckerPark)/(a.CheckerPark+b.NONCheckerPark)) as Pct
from #temp a
left outer join (select * from #temp where Indication = 'NoAction') b on a.languagetype = b.languagetype
and a.Indication = 'Action'
DROP TABLE #Temp
Thank you in Advance
Dhani

Viewing 15 posts - 1 through 15 (of 19 total)

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

Christian Graus SSC Eights! Points: 963 More actions · Answer 1

It's really kind of rude to post the same question on many different forums, instead of just taking part in a community somewhere.

This has been answered, by me, elsewhere.

asita SSCarpal Tunnel Points: 4144 More actions · Answer 2

Thank you Christian Graus,

I am glad, just want to learn a new way to do the things, i greatful to all of the gurus here,

but the one you posted in somewhere is not much usefull (you just mentioned to use CTE),

hope you understand, so please help me to answer my question

Thank you & Best Regards,

Christian Graus SSC Eights! Points: 963 More actions · Answer 3

I am confused. I posted some SQL that used a CTE, and someone else looked more closely than me and realised the two queries were in fact compatible to be merged, and gave you full SQL. Are you still stuck, after all that ?

kapil_kk SSC-Insane Points: 21316 More actions · Answer 4

Please do not post duplicate post in different forums it will save other people time too..

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

kapil_kk SSC-Insane Points: 21316 More actions · Answer 5

As Christian suggested, it can easily be done using CTE

_______________________________________________________________
To get quick answer follow this link:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 6

kapil_kk (12/29/2013)
As Christian suggested, it can easily be done using CTE

If it's that easy and since we're here, please post a solution. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 7

Christian Graus (12/28/2013)
It's really kind of rude to post the same question on many different forums, instead of just taking part in a community somewhere.
This has been answered, by me, elsewhere.

The OP appears to have other questions or is dissatisfied with the answers there. Would either you or the OP please post the URL for that other thread so we can get to the bottom of this? Thanks.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Christian Graus SSC Eights! Points: 963 More actions · Answer 8

I frequent several SQL forums, I don't even recall now which one I saw it on, but I did suggest using a CTE, and gave an example, and someone else posted a full solution, without a CTE. So, I am at a loss as to what's going on. However, I'm happy to take a shot at answering it again.

Christian Graus SSC Eights! Points: 963 More actions · Answer 9

Here's an attempt at using CTEs. It would really help to know the table structures and so on, of course.

with cte

(

select

languagetype ,

Indication,

COUNT(AcctID) as Clients,

AVG(TaxRate1) as SavingRtAvg_RT,

SUM(BeforeStats) as BeforeSecs,

CASE WHEN Indication = 'Action' THEN COUNT(AcctID) END as CheckerPark,

CASE WHEN Indication = 'NoAction' THEN COUNT(AcctID) END as NONCheckerPark

from Mars.Outfitter

where period = 'Q1-2013'

group by

languagetype ,

Indication

)

select

a.languagetype ,

a.Indication,

a.Clients,

a.SavingRtAvg_RT,

a.BeforeSecs,

(CONVERT(Decimal(18,4),a.CheckerPark)/(a.CheckerPark+b.NONCheckerPark)) as Pct

from cte a

left outer join cte b on a.languagetype = b.languagetype and b.Indication = 'NoAction' and a.Indication = 'Action'

order by

LanguageType,

Indication

Note to the OP, if you gave us SQL to create the tables and insert some data, we could check this for syntax and for the end result.

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 10

Here are a couple of potential solutions. They are only "potential" because there isn't any sample data to interrogate. I've knocked up a sample table to assist you and others. Please edit the definition and the data inserts to match your real data.

-- Sample data always helps.

-- If this isn't representative then please edit and repost.

DROP TABLE #Mars_Outfitter

CREATE TABLE #Mars_Outfitter (

AcctID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

Period CHAR(7),

languagetype VARCHAR(2),

Indication VARCHAR(10),

TaxRate1 INT,

BeforeStats INT)

INSERT INTO #Mars_Outfitter (Period, languagetype, Indication, TaxRate1, BeforeStats)

SELECT 'Q1-2013', '1', 'Action', 10, 10 UNION ALL

SELECT 'Q1-2013', '2', 'Action', 10, 10 UNION ALL

SELECT 'Q1-2013', '1', 'NoAction', 20, 20 UNION ALL

SELECT 'Q1-2013', '2', 'NoAction', 20, 20 UNION ALL

SELECT 'Q1-2013', '2', 'NoAction', 20, 20

CREATE INDEX ix_UsefulButExpensive ON #Mars_Outfitter (Period, languagetype, Indication) INCLUDE (TaxRate1, BeforeStats)

-- Solution1 (reads table twice)

SELECT

a.languagetype,

Indication= 'Action',

[Clients]= COUNT(*),

[SavingRtAvg_RT] = AVG(a.TaxRate1),

[BeforeSecs]= SUM(a.BeforeStats),

[pct]= CAST(COUNT(*) / (0.00+COUNT(*)+MAX(x.Clients)) AS DECIMAL(7,4))

FROM #Mars_Outfitter a

OUTER APPLY (

SELECT COUNT(*) as Clients

FROM #Mars_Outfitter i

WHERE i.period = a.period

AND i.languagetype = a.languagetype

AND i.Indication = 'NoAction'

) x

WHERE a.period = 'Q1-2013'

AND a.Indication = 'Action'

GROUP BY a.languagetype

-- Solution2 (probably faster, reads table once)

;WITH AggregatedData AS (

SELECT

languagetype,

Indication= 'Action',

[Clients]= SUM(CASE WHEN Indication = 'Action' THEN 1 ELSE 0 END),

[ClientsNoAction]= SUM(CASE WHEN Indication = 'NoAction' THEN 1 ELSE 0 END),

[SavingRtAvg_RT] = AVG(CASE WHEN Indication = 'Action' THEN TaxRate1 ELSE NULL END),

[BeforeSecs]= SUM(CASE WHEN Indication = 'Action' THEN BeforeStats ELSE 0 END)

FROM #Mars_Outfitter

WHERE period = 'Q1-2013'

GROUP BY languagetype

)

SELECT

languagetype, Indication, Clients, SavingRtAvg_RT, BeforeSecs,

[pct] = CAST(Clients / (0.00+Clients+[ClientsNoAction]) AS DECIMAL(7,4))

FROM AggregatedData

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 11

Christian Graus (12/30/2013)
I frequent several SQL forums, I don't even recall now which one I saw it on, but I did suggest using a CTE, and gave an example, and someone else posted a full solution, without a CTE. So, I am at a loss as to what's going on. However, I'm happy to take a shot at answering it again.

No problem. Thanks, Christian. I was curious to see what's going on and what some of the solutions may have been. I'm in the same boat as you... I can't always remember where I did something.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Christian Graus SSC Eights! Points: 963 More actions · Answer 12

Funny enough, I got a notification that told me where the other thread was. For anyone interested in seeing the other solutions offered, it's here:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/de788fa8-2767-47bc-a29e-f50e5245e955/how-yo-make-these-two-queires-to-one-please?forum=transactsql

mintekhab.nith SSC Enthusiast Points: 110 More actions · Answer 13

I think the below Query should work.(For a very large dataset there could a performance issue without proper indexing)

select languagetype

,Indication

,COUNT(AcctID) as Clients

,AVG(TaxRate1) as SavingRtAvg_RT

,SUM(BeforeStats) as BeforeSecs

,SUM (CASE WHEN Indication = 'Action' THEN 1 ELSE 0 END) /

(SUM (CASE WHEN Indication = 'NoAction' THEN 1 ELSE 0 END)

+ SUM (CASE WHEN Indication = 'Action' THEN 1 ELSE 0 END))

from Mars.Outfitter

where period = 'Q1-2013'

group by

languagetype ,

Indication

order by

LanguageType,

Indication

Jeff Moden SSC Guru Points: 1004318 More actions · Answer 14

Christian Graus (12/30/2013)
Funny enough, I got a notification that told me where the other thread was. For anyone interested in seeing the other solutions offered, it's here:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/de788fa8-2767-47bc-a29e-f50e5245e955/how-yo-make-these-two-queires-to-one-please?forum=transactsql

Thanks, Christian. Interesting how these things pan out.

asita (12/28/2013)
Thank you Christian Graus,
I am glad, just want to learn a new way to do the things, i greatful to all of the gurus here,
but the one you posted in somewhere is not much usefull (you just mentioned to use CTE),
hope you understand, so please help me to answer my question
Thank you & Best Regards,

@Dhani,

Now that you have some code, are you all set or is there some additional help that you need?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)