Combine Query - 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)

Combine Query

Author

Topic

rkalyani
Starting Member

30 Posts

Posted - 2009-03-06 : 12:37:59

I have two queries

select count(ind_app_id)as totalSubmitted from tableA where
SubmittedDate between '02/01/2009' and '03/01/2009'

select count(ind_app_id)as totalSold from tableA where
SoldDate between '02/01/2009' and '03/01/2009'

My requirement is to show total submitted vs total sold for a given date range. I created both these queries. Is there a way to combine these.

Thank you

Thank you,
kal30

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2009-03-06 : 12:45:35

[code]
Select Count(Case when SubmittedDate between '02/01/2009' and '03/01/2009' then ind_app_id Else 0 End)as totalSubmitted,
Count(Case when SoldDate between '02/01/2009' and '03/01/2009'
then ind_app_id Else 0 End)as totalSold
from table A[/code]

tmitch
Yak Posting Veteran

60 Posts

Posted - 2009-03-06 : 12:48:02

SELECT (select count(ind_app_id) from tableA where SubmittedDate between '02/01/2009' and '03/01/2009') [totalSubmitted]
, (select count(ind_app_id) from tableA where SoldDate between '02/01/2009' and '03/01/2009') [totalSold]

hth,
Tim

---------------------

Tim Mitchell
www.BucketOfBits.com

rkalyani
Starting Member

30 Posts

Posted - 2009-03-06 : 13:25:18

Tim,

I used your query. I had to attach a where clause in the end and my dates were not real dates. They were varchar fields and so I had to convert it. So my final query looks like this

select
(select count(ind_app_id) from tableA where case when
isdate(submittedDate) =1 then convert(datetime, submittedDate) end
between '02/01/2009' and '03/01/2009')[totalSubmitted],
(select count(ind_app_id) from tableA where
case when isdate(SoldDate) =1 then
convert(datetime, SoldDate) end between '02/01/2009' and '03/01/2009')[totalSold]
from tableA
where transaction_type_id=7 and ind_app_id <> 0

But I get different answer from my original seperated query
Total submitted should be 293. I get 1325
Total Sold should be 112. I get 875

Am I missing something while converting the varchar to date

Thank you

Thank you,
kal30

tmitch
Yak Posting Veteran

60 Posts

Posted - 2009-03-06 : 13:49:07

You need to put your WHERE clause in each of the subqueries, not in the outer query.

hth,
Tim

---------------------

Tim Mitchell
www.BucketOfBits.com

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-03-06 : 23:36:38

try this one if there are varchar fields
slight modification to tmitch query,

SELECT (select count(ind_app_id) from tableA where convert(datetime,SubmittedDate) between '02/01/2009' and '03/01/2009' and transaction_type_id=7 and ind_app_id <> 0) [totalSubmitted]
, (select count(ind_app_id) from tableA where convert(datetime,SoldDate) between '02/01/2009' and '03/01/2009' and transaction_type_id=7 and ind_app_id <> 0) [totalSold]

try sodeep query as like this
Select Count(Case when convert(datetime,SubmittedDate) between '02/01/2009' and '03/01/2009' then ind_app_id Else 0 End)as totalSubmitted,
Count(Case when convert(datetime,SoldDate) between '02/01/2009' and '03/01/2009'
then ind_app_id Else 0 End)as totalSold
from table A
where transaction_type_id=7 and ind_app_id <> 0

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-03-07 : 13:27:35

quote:

Originally posted by sodeep


Select Count(Case when SubmittedDate between '02/01/2009' and '03/01/2009' then ind_app_id Else Null End)as totalSubmitted,
Count(Case when SoldDate between '02/01/2009' and '03/01/2009'
then ind_app_id Else Null End)as totalSold
from table A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources