August 31, 2006 at 11:03 am
Hello,
I needed a query in which I pulled CY.AcctCode's and PR.PrmLog_Customer_ID
from a two different tables where the CY.BillingType = 'Payment Servi' and ONLY 'Payment Servi'. Each CY.AcctCode can have multiple BillingTypes.
The way I usually approach something like this is to dump results into one or more temp tables until I have what I need. The following queries attempt to accomplish the following;
-Get all of the billingTypes from the BillingType table accept for 'Payment Servi' into a temp table called #BillingTypeRecs
-Pull all of the Acctcodes and BillingTypes from CySummaryByMo where the BillingType is not in #BillingTypeRecs (The only one that should not be there is 'Payment Servi'). Put these into #PaymentServicesAccts.
-Join #PaymentServiceAccts back to CySummaryByMo by joining AcctCode and BillingType. Put these records into #DistinctCyAccts
-Join #DistinctCyAccts by AcctCode to another table called PrmrLog so that I can get the PrmLog_Customer_id value.
********************************
--get all billingTypes accecpt for Payment Servi into a temp table
SELECT *
INTO
#BillingTypeRecs
FROM
dbo.BillingType
WHERE
BillingType <> 'Payment Servi'
--get only 'payment servi' accounts, and put into temp table
SELECT
AcctCode, BillingType
INTO
#PaymentServicesAccts
FROM
dbo.CySummaryByMo
WHERE
BillingType = 'Payment Servi'
AND
BillingType NOT IN
(Select BR.BillingType from #BillingTypeRecs BR
Inner Join dbo.CySummaryByMo CY
ON CY.BillingType = BR.BillingType)
--join those to CYSummaryByMo and put into temp table
SELECT distinct CY.AcctCode, CY.BillingType
INTO
#DistinctCYAccts
FROM
dbo.CYSummaryByMo CY
INNER JOIN #PaymentServicesAccts PY
ON CY.AcctCode = PY.AcctCode
AND CY.BillingType = PY.BillingType
ORDER BY
CY.AcctCode
--join to PRMRLOG table by acctcode to get results
SELECT
DISTINCT PR.PrmLog_Acct_Code, PR.PrmLog_Customer_ID
FROM
dbo.PrmrLog PR
INNER JOIN #DistinctCYAccts DA
ON DA.AcctCode = PR.PrmLog_Acct_Code
ORDER BY
PR.PrmLog_Acct_Code
BEGIN
DROP TABLE #BillingTypeRecs
DROP TABLE #PaymentServicesAccts
DROP TABLE #DistinctCYAccts
END
*********************************
I hope that the series of queries above makes sense. I've actually just discovered that I didn't get the results I needed. Some of the acctcodes still had billingtypes in addition to 'payment servi'.
If you have any ideas on how I could improve on this, please let me know.
Thanks!
CSDunn
August 31, 2006 at 11:29 am
A quick perusal of your code suggests that the following may work.
-- Do not have enough info to tell if distinct is needed
select distinct PR.PrmLog_Acct_Code, PR.PrmLog_Customer_ID
from dbo.PrmrLog PR
where exists (select *
from dbo.CYSummaryByMo DA
where DA.BillingType = 'Payment Servi'
and DA.AcctCode = PR.PrmLog_Acct_Code)
order by PR.PrmLog_Acct_Code
ps In production code do not use SELECT * (except with an exists sub-query where it is meaningless) or SELECT ... INTO
August 31, 2006 at 11:47 am
Just noticed that you do not want AcctCode in addition to 'Payment Servi'.
This may work:
-- Do not have enough info to tell if distinct is needed
select distinct PR.PrmLog_Acct_Code, PR.PrmLog_Customer_ID
from dbo.PrmrLog PR
where exists (select *
from dbo.CYSummaryByMo DA
where DA.BillingType = 'Payment Servi'
and DA.AcctCode = PR.PrmLog_Acct_Code)
and not exists (select *
from dbo.CYSummaryByMo DA2
where DA2.BillingType <> 'Payment Servi'
and DA2.AcctCode = PR.PrmLog_Acct_Code)
order by PR.PrmLog_Acct_Code
August 31, 2006 at 11:57 am
Thank you for your help. Yes, I've heard that SELECT * is a sin, but this is not for production.
I just modified my first attempt, and now it looks like this;
--get a count of billingTypes for all acctcodes
SELECT
acctcode, COUNT(DISTINCT billingtype)AS cbillingtype
INTO
#BillingTypeCounts
FROM
cysummarybymo
GROUP BY
acctcode
ORDER BY
cbillingtype
--Isolate the AcctCodes where the count of billingTypes = 1
SELECT
AcctCode
INTO
#OneBillingType
FROM
#BillingTypeCounts BC
WHERE
BC.cbillingtype = 1
--Join those records with one billingtype back to CYSummaryByMo,
--and filter for 'Payment Servi'
SELECT DISTINCT
CY.AcctCode, CY.BillingType
INTO
#FinalResult
FROM
CySummaryByMo CY
INNER JOIN #OneBillingType OT
ON OT.AcctCode = CY.AcctCode
WHERE
CY.BillingType = 'Payment Servi'
ORDER BY
CY.AcctCode
--Join the final result to PRMRLOG to get Customer_Id
SELECT
DISTINCT PR.PrmLog_Acct_Code, PR.PrmLog_Customer_ID
FROM
dbo.PrmrLog PR
INNER JOIN #FinalResult FR
ON FR.AcctCode = PR.PrmLog_Acct_Code
ORDER BY
PR.PrmLog_Acct_Code
BEGIN
DROP TABLE #BillingTypeCounts
DROP TABLE #OneBillingType
DROP TABLE #FinalResult
END
August 31, 2006 at 1:31 pm
I think you don't need the temporary tables.
See if this works?
The only thing I'm not sure of is the DISTINCT in the HAVING statement.
SELECT DISTINCT PR.PrmLog_Acct_Code,
PR.PrmLog_Customer_ID
FROM
dbo.PrmrLog PR Inner Join CySummaryByMo CY
On CY.AcctCode = PR.PrmLog_Acct_Code
Inner Join (SELECT acctcode
FROM cysummarybymo
GROUP BY acctcode
ORDER BY cbillingtype
HAVING COUNT(DISTINCT billingtype) = 1) OT
On OT.AcctCode = CY.AcctCode
WHERE
CY.BillingType = 'Payment Servi'
ORDER BY PR.PrmLog_Acct_Code
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply