Subquery - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Subquery

Author

Topic

Masum7
Starting Member

33 Posts

Posted - 2009-03-24 : 07:36:57

I have one table:
MailType
Id-----Type
1------Easy
2------Quick

Another table
Mails
Id-----Status-----Date--------MailType
1-------1---------2009-05-03----1

Here Status=1 means success, Other value of status means failed.
MailType is a foreign key from table MailType
User can select date range so date range will be in where clause.

I need output like this:
Type-------Total-------Success-------Failed
Easy ---------20----------15----------5
Quick---------18----------10----------8
Total---------38----------25----------13

Can you please help me how efficiently I can do it? Thanks in advance.

Masum

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2009-03-24 : 10:17:30

like this...
you need to put your date range in too obviously


select	 [TYPE]
		,COUNT(1) as total
		,sum(case when [status] = 1 then 1 else 0 end) as [success]
		,sum(case when [status] <> 1 then 1 else 0 end) as [failed]
from Mails
	join MailType on mailtype.id = mails.mailtype
where [DATE] between ---------your date range here! 
group by [type]

Masum7
Starting Member

33 Posts

Posted - 2009-03-24 : 12:01:39

Hi elancaster
This is really really great help!

Actually I am not beginner but I was finding the efficient way to do it. Now you have shown me. That was the technique to do without any subquery.

Many thanks elancaster. May you live long

Masum

Masum7
Starting Member

33 Posts

Posted - 2009-03-25 : 00:17:22

Hi
One problem if there is no record in Mails table to match criteria of date then it shows no record. But I want to show:
Type-------Total-------Success-------Failed
Easy ---------0----------0----------0
Quick---------0----------0----------0

Any efficient way?

Masum

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-03-25 : 03:36:46

quote:
Originally posted by Masum7

Hi
One problem if there is no record in Mails table to match criteria of date then it shows no record. But I want to show:
Type-------Total-------Success-------Failed
Easy ---------0----------0----------0
Quick---------0----------0----------0

Any efficient way?

Masum

try replacing the table join with LEFT JOIN...if it returns null then use ISNULL() to convert the null values to zer0.

Masum7
Starting Member

33 Posts

Posted - 2009-03-25 : 06:22:57

Hi Chrianth
Thanks for replying. But that will not work because these will return no record because date of Mails table will not bring any record. Thats why whole query result will be empty.

quote:
Originally posted by chrianth
try replacing the table join with LEFT JOIN...if it returns null then use ISNULL() to convert the null values to zer0.

Masum

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-03-25 : 06:29:35

quote:
Originally posted by Masum7

Hi Chrianth
Thanks for replying. But that will not work because these will return no record because date of Mails table will not bring any record. Thats why whole query result will be empty.

quote:
Originally posted by chrianth
try replacing the table join with LEFT JOIN...if it returns null then use ISNULL() to convert the null values to zer0.

Masum

Ohh sorry, it should be the other way around (use RIGHT JOIN)..

Masum7
Starting Member

33 Posts

Posted - 2009-03-28 : 23:43:53

Actually chrianth,
Difference in inner, left or right join is for "ON" condition. But here problem is where clause. This doesn't bring any rows.

quote:
Originally posted by chrianth
Ohh sorry, it should be the other way around (use RIGHT JOIN)..

Masum

chrianth
Yak Posting Veteran

50 Posts

Posted - 2009-03-29 : 22:33:57

yeah, i know how the join works... below is what the query should be, tried with test data...

--Test Data for MailType
select a.*
into ##MailType
from (
select 1 as ID,'Easy' as Type
union all
select 2 as ID,'Quick' as Type
)a

--Test Data for Mails
select a.*
into ##Mails
from (
select 1 as ID, 1 as Status,getdate() as Date,1 MailType
union all
select 2 as ID, 2 as Status,getdate()+1 as Date, 1 MailType
union all
select 1 as ID, 1 as Status,getdate()+2 as Date, 2 MailType
union all
select 1 as ID, 2 as Status,getdate()+3 as Date, 1 MailType
)a

--Actual Query to pull your requirements...
select x.[Type],isnull(y.Total,0),isnull(y.Success,0),isnull(y.Failed,0)
from ##MailType x left join (
select [TYPE]
,COUNT(1) as total
,sum(case when [status] = 1 then 1 else 0 end) as [success]
,sum(case when [status] <> 1 then 1 else 0 end) as [failed]
from ##Mails
join ##MailType
on ##mailtype.id = ##mails.mailtype
where [DATE] between '2009-03-30 10:14:37.873' and '2009-03-31 10:14:37.873'
group by [type]
)y on (x.[type] = y.[type])

drop table ##Mails
drop table ##MailType

If you want more enhanced performance...explore!

Think outside the box!!!

Masum7
Starting Member

33 Posts

Posted - 2009-03-31 : 01:26:58

Hi chrianth
Yes this is Right! Great answer! :)
Many thanks bro for your continuous help.

Bye mate.

Masum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources