Help with 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

General SQL Server Forums

New to SQL Server Programming

Help with query

Author

Topic

rkalyani
Starting Member

30 Posts

Posted - 2009-04-23 : 13:44:29

SELECT
a.distOff.district_office, b.applicant.broker_assistance,
DirectSalesSubmitted = sum(case when b.Agy.COCI_ID ='aaaa'and isdate(QODistRecDate) =1 and convert(datetime, QODistRecDate)
between '12/01/2008' and '12/31/2008' then 1 else 0 end),
DirectSalesSold = sum(case when b.Agy.COCI_ID ='aaaa'and isdate(SSRecDate) =1 and isdate(QODistRecDate) =1 and convert(datetime, QODistRecDate) between '12/01/2008' and '12/31/2008' then 1 else 0 end),
ExternalSalesSubmitted=sum(case when b.Agy.COCI_ID <>'aaaa'and isdate(QODistRecDate) =1 and convert(datetime, QODistRecDate)
between '12/01/2008' and '12/31/2008' then 1 else 0 end),
ExternalSalesSold=sum(case when b.Agy.COCI_ID <>'aaaa'and isdate(SSRecDate) =1 and isdate(QODistRecDate) =1 and convert(datetime, QODistRecDate) between '12/01/2008' and '12/31/2008' then 1 else 0 end),
TotalAppsSubmitted=sum(case when isdate(QODistRecDate) =1 and convert(datetime, QODistRecDate) between '12/01/2008' and '12/31/2008' then 1 else 0 end),
TotalAppsSold=sum(case when isdate(SSRecDate) =1 and isdate(QODistRecDate) =1 and convert(datetime, QODistRecDate)
between '12/01/2008' and '12/31/2008' then 1 else 0 end)
FROM a.EBST_applications
Inner Join b.applicant on
a.EBST_applications.ind_app_id = b.applicant.ind_app_id
Inner Join a.distOff ON
a.EBST_applications.district_office_id = a.distOff.district_office_id
Left outer Join b.Agy on
a.EBST_applications.broker_number = b.Agy.COCE_ID
GROUP BY
a.distOff.district_office,
b.applicant.broker_assistance,
b.Agy.COCI_ID

I have this query. I need it to show just the totals but it shows all the rows. I am also not sure if this is the best way to write this query. Can some one tell me if I could modify this better.

Thank you,
kal30

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-04-24 : 00:11:32

I see that the following are the columns that are being displayed in here where in the last 6 are computed values.
distOff.district_office, applicant.broker_assistance, DirectSalesSubmitted , DirectSalesSold, ExternalSalesSubmitted, ExternalSalesSold, TotalAppsSubmitted, TotalAppsSold

Sample input & expected output data would just help us in understanding what you are trying to look at & also why is that you have referred with multiple aliases in here ?

regards,
Anil Kumar.

rkalyani
Starting Member

30 Posts

Posted - 2009-04-24 : 12:03:20

BDO n 0 0 0 0 0 0
BDO n 0 0 0 0 0 0
BDO n 0 0 0 0 0 0
BDO n 0 0 0 0 0 0
BDO n 0 0 0 0 0 0
BDO n 0 0 0 0 0 0
BDO n 0 0 81 27 81 27
BDO n 0 0 1 1 1 1
BDO n 0 0 0 0 0 0
BDO n 0 0 0 0 0 0
BDO n 0 0 0 0 0 0
BDO n 0 0 0 0 0 0
BDO n 0 0 0 0 0 0
BDO n 0 0 0 0 0 0
BDO n 0 0 0 0 0 0
BDO n 0 0 0 0 0 0
BDO n 0 0 1 0 1 0
BDO n 3 2 0 0 3 2
BDO n 0 0 0 0 0 0
BDO n 0 0 0 0 0 0
BDO n 0 0 0 0 0 0
BDO n 0 0 0 0 0 0
BDO y 0 0 0 0 2 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 2 1 2 1
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 2 0 2 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 1 0 1 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 1 0 1 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 1 0 1 0
BDO y 0 0 0 0 0 0
BDO y 0 0 1 1 1 1
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 16 10 16 10
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 0 0 0 0
BDO y 0 0 1 0 1 0
BDO y 0 0 1 1 1 1
BDO y 0 0 0 0 0 0
BDO y 0 0 1 0 1 0

This is how a sample of the results look. The columns are as follows.
district_office, broker_assistance, DirectSalesSubmitted , DirectSalesSold, ExternalSalesSubmitted, ExternalSalesSold, TotalAppsSubmitted, TotalAppsSold.

I just am not sure how to just show the final total. There are a lot of columns with just 0's in it. I am not sure how to eliminate those.

Sorry, this is the first time I am writing such a complex query so I am not sure what I am doing wrong.

Thank you,
kal30

Kumar_Anil
Yak Posting Veteran

68 Posts

Posted - 2009-04-24 : 14:08:54

Im sorry but it is still not clear to me at all. Please post what is the actual data in any or all of the tables & what is the output that you are expecting to see....like how data is in or
EBST_applications table and/or distOff tables and how do you want to see the output...

Anyways my best guess for now is may be this .....

regards,
Anil Kumar.

select AA.DO, AA.BA, sum(AA.DirectSalesSubmitted) as SumOfDirectSalesSubmitted, sum(AA.DirectSalesSold) as SumOfDirectSalesSold, sum(AA.ExternalSalesSubmitted) as SumOfExternalSalesSubmitted, Sum(AA.TotalAppsSubmitted) as SumOfTotalAppsSubmitted, Sum(AA.TotalAppsSold) as SumOfTotalAppsSold
from
(SELECT a.distOff.district_office as DO, b.applicant.broker_assistance as BA, DirectSalesSubmitted = sum( case when b.Agy.COCI_ID ='aaaa'and isdate(QODistRecDate) =1 and convert(datetime, QODistRecDate) between '12/01/2008' and '12/31/2008' then 1 else 0 end), DirectSalesSold = sum( case when b.Agy.COCI_ID ='aaaa'and isdate(SSRecDate) =1 and isdate(QODistRecDate) =1 and convert(datetime, QODistRecDate) between 12/01/2008' and '12/31/2008' then 1 else 0 end),ExternalSalesSubmitted = sum(case when b.Agy.COCI_ID <>'aaaa'and isdate(QODistRecDate) =1 and convert(datetime, QODistRecDate) between '12/01/2008' and '12/31/2008'
then 1 else 0 end),ExternalSalesSold = sum(case when b.Agy.COCI_ID <>'aaaa'and isdate(SSRecDate) =1 and isdate(QODistRecDate) =1 and convert(datetime, QODistRecDate) between '12/01/2008' and '12/31/2008' then 1 else 0 end), TotalAppsSubmitted = sum(case when isdate(QODistRecDate) =1 and convert(datetime, QODistRecDate) between '12/01/2008' and '12/31/2008' then 1 else 0 end),TotalAppsSold =
sum(case when isdate(SSRecDate) =1 and isdate(QODistRecDate) =1 and convert(datetime, QODistRecDate) between '12/01/2008' and '12/31/2008' then 1 else 0 end) FROM a.EBST_applications Inner Join b.applicant on a.EBST_applications.ind_app_id = b.applicant.ind_app_id
Inner Join a.distOff ON a.EBST_applications.district_office_id = a.distOff.district_office_id Left outer Join b.Agy on a.EBST_applications.broker_number = b.Agy.COCE_ID GROUP BY a.distOff.district_office, b.applicant.broker_assistance,b.Agy.COCI_ID)AA
group by AA.DO, AA.BA

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources