[Resolved] Can I combine this query

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)

[Resolved] Can I combine this query

Author

Topic

rkalyani
Starting Member

30 Posts

Posted - 2009-03-18 : 13:22:22

Query 1

SELECT districtOffice,doc_type, sum(TotalappID) as TotalAppsSold from(select EBST_district_office.district_office as districtOffice, count(app_id) as TotalappID,
CASE WHEN ind_app_id = 0 THEN 'Paper' ELSE 'Online' END AS doc_type FROM EBST_applications INNER JOIN EBST_district_office ON EBST_applications.district_office_id = EBST_district_office.district_office_id
WHERE case when isdate(Sales_service_receive_date) =1 then convert(datetime, Sales_service_receive_date) end between @StartDate and @EndDate and EBST_applications.transaction_type_id=7 group by quote_only_district_office_receive_date,EBST_district_office.district_office, CASE WHEN ind_app_id = 0 THEN 'Paper' ELSE 'Online' END)t
group by doc_type, districtOffice

Query 2

SELECT districtOffice, doc_type sum(TotalappID) as TotalAppsSubmitted from(select EBST_district_office.district_office as districtOffice, count(app_id) as TotalappID, CASE WHEN ind_app_id = 0 THEN 'Paper' ELSE 'Online' END AS doc_type FROM EBST_applications INNER JOIN EBST_district_office ON EBST_applications.district_office_id = EBST_district_office.district_office_id
WHERE case when isdate(quote_only_district_office_receive_date) =1 then convert(datetime, quote_only_district_office_receive_date) end
between @StartDate and @EndDate and dbo.EBST_applications.transaction_type_id=7 group by quote_only_district_office_receive_date,
EBST_district_office.district_office, CASE WHEN ind_app_id = 0 THEN 'Paper' ELSE 'Online' END)t
group by doc_type, districtOffice

Is there any way to make this into one query. One is used to find total submitted and other is used to find total sold. They match different dates in the where clause. I have marked the changes in bold.

Thank you,
kal30

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-18 : 13:29:21

you should format your query so that it makes sense for someone who is going thru it. Is there a difference bw the 2 queries ??? I can't see any.

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-18 : 13:59:51

Can you just give this a try.

	SELECT 
		EBST_district_office.district_office as districtOffice, 
		TotalAppsSold=sum(case when isdate(Sales_service_receive_date) =1 and convert(datetime, Sales_service_receive_date)  between @StartDate and @EndDate then 1 else 0 end),
		TotalAppsSubmitted=sum(case when isdate(quote_only_district_office_receive_date) =1 and convert(datetime, quote_only_district_office_receive_date) between @StartDate and @EndDate then 1 else 0 end),
		CASE WHEN ind_app_id = 0 THEN 'Paper' ELSE 'Online' END AS doc_type 
	FROM 
		EBST_applications 
		INNER JOIN EBST_district_office ON EBST_applications.district_office_id = EBST_district_office.district_office_id
	WHERE 
		EBST_applications.transaction_type_id=7 
	GROUP BY 
		EBST_district_office.district_office, 
		CASE WHEN ind_app_id = 0 THEN 'Paper' ELSE 'Online' END

rkalyani
Starting Member

30 Posts

Posted - 2009-03-18 : 15:03:52

Thank you so much. It worked great.

Thank you,
kal30

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2009-03-18 : 16:04:11

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources