Subquery with case statement

Question

Subquery with case statement

h_maksuda

SSC Eights!

Points: 966
More actions
August 18, 2009 at 1:55 pm

#208826

Hi:
I have two tables T1 and T2, both have the same structure.
Tables contain Quarterly data. I usually produce charts from T1 table.
Table structure is :
Event_Qtr Total ....
Q1-2009 21644865 ....
Q2-2009 152899 .....
Both table have from 2007 to 2009 quarterly data.
For some management requirement I have to select total from table T2 if quarter is else total will be selected from T1 table(for Q4-2009 and up).
Is it possible to select data by subquery (using case statement) ?
I need help badly pls. Any coding help is highly appreciated
Thanking in advance.
Maksuda

Viewing 7 posts - 1 through 6 (of 6 total)

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

John Rowan SSC Guru Points: 56440 More actions · Answer 1

Some sample data along with an example of the result set you would like to see based off of your sample data would help you get a better quality answer. See this thread for putting together a post that will get you a quick answer.

http://www.sqlservercentral.com/articles/Best+Practices/61537/"> http://www.sqlservercentral.com/articles/Best+Practices/61537/

John Rowan

======================================================
======================================================
Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

h_maksuda SSC Eights! Points: 966 More actions · Answer 2

Thanks John. You are right. I should provide enough information to get the quick response.

Here is my table:Qtr_Event_TotalInfo(T1)

CREATE TABLE [dbo].[Qtr_TotalInfo](

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

Event_Qtr varchar(7) ,

Event_Total int NULL

)

Table T2 also have the same structure

data in table(T1)

insert into Qtr_Event_TotalInfo values('Q1-2009',21644865)

insert into Qtr_Event_TotalInfo values('Q2-2009', 38533950)

insert into Qtr_Event_TotalInfo values('Q3-2009',23143204)

insert into Qtr_Event_TotalInfo values('Q4-2009',6355608)

insert into Qtr_Event_TotalInfo values('Q1-2008',21376661)

insert into Qtr_Event_TotalInfo values('Q2-2008',16412433)

insert into Qtr_Event_TotalInfo values('Q3-2008',18035155

insert into Qtr_Event_TotalInfo values('Q4-2008',17574502)

Data in table T2:

insert into T2 values('Q1-2009', 68200 )

insert into T2 values('Q2-2009', 3161591)

insert into T2 values('Q3-2009',5950028)

insert into T2 values('Q1-2008',168212)

insert into T2 values('Q2-2008', 152899)

insert into T2 values('Q3-2008',2265710)

insert into T2 values('Q4-2008',168212)

Infact table T2 has data from Q1-2007 to Q3-2009.

Table Qtr_TotalInfo (T1) has data from Q1-2007 to Q4-2009.

Now in my report user selects year from a list

if selected year is 2008 or 2007 data will show up from T2 table,

if selected year is 2009 then Q1-2009,Q2-2009 and Q3-2009 data will show up from table T2 and Q4-2009 data will show up from Qtr_TotalInfo (T1) table.

Is it possible to select data for this condition by a single query (using subquery) ?

Please need help. code sample is highly appreciated.

Thanks,

Maksuda

drew.allen SSC Guru Points: 76985 More actions · Answer 3

I think this will give you what you are looking for.

SELECT IsNull(t2.Event_Total, t1.Event_Total) AS Event_Total

FROM qtr_totalinfo AS t1

LEFT OUTER JOIN t2

ON t1.Event_Qtr = t2.Event_Qtr

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

h_maksuda SSC Eights! Points: 966 More actions · Answer 4

Thanks Drew for the reply.

I tried and it giving me Cartesian Product.

Thanking you,

Maksuda

drew.allen SSC Guru Points: 76985 More actions · Answer 5

h_maksuda (8/19/2009)
Thanks Drew for the reply.
I tried and it giving me Cartesian Product.

Is your data representative of the actual data? Your data only has one value for each date in each table. If that is the case you will not get a Cartesian Product.

If either or both of your tables has multiple rows for a single date, I suggest that you update your sample data to be more representative of your actual data. As they say, "garbage in, garbage out". If you want a good solution, your going to need to provide good data.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

John Hopkins SSC Enthusiast Points: 162 More actions · Answer 6

If you're aggregating quarters that happen to exist in both tables, I think you want UNION ALL rather than a join. Here's one way - not super pretty, but it does what I think you want to do with your sample data (Q1-2008 from Qtr_Event_TotalInfo plus Q1-2008 from T2 = 21,544,873. If that's not it, we may need a little more clarification.

SELECT

A.Event_Qtr,

SUM(A.Event_Total) AS Event_Total

FROM (

SELECT * FROM Qtr_Event_TotalInfo Q

UNION ALL

SELECT * FROM T2 Q

) A

GROUP BY

A.Event_Qtr

ORDER BY RIGHT(A.Event_Qtr, 4) + LEFT(A.Event_Qtr,2)

Regards,

John

Regards,

John Hopkins