September 18, 2011 at 8:17 pm
Hi ,
I need to get the count of records , where columns are from different tables.Below is my query.
Select
,AC.Account_Id
,SBP.Billing_CARRIER_ID as Carrier_id
,SBP.Billing_CARRIER_NAME as Carrier_Name
,AC.Case_Number_Id
,SBC.Cash_Received_Premium
, 1 CASH_IND
,SEM.GLCompany as Company
, SBP.AcqCode as CostCenter
,SBC.Date_Paid_To_Broker
,BA.DEPARTMENT_NAME
,SBC.Deposit_date
, 0 DerivedLivesInd
, 0 DerivesPremiumInd
,ISNULL(AMR.JE_Account,'update epicor mapping')
,Wl.GenID as GAID
,'CashBP'+cast(cast(SBC.Deposit_date as date) as varchar(9))+isnull(Wl.GenID,'unknown') as JE_Account_Description
,Case
When ((DATEDIFF(Month ,SBP.ORIG_EFF_DATE,getdate())< 12 )
OR
(GETDATE()<SBP.ORIG_EFF_DATE))then 1 else
(Case
when(SBP.CANCELLATION_DATE>'1900-01-01 00:00:00.000')
then (Case when(DATEDIFF(Month ,SBP.CANCELLATION_DATE,SBP.EFF_DATE)> 6 )
then(Case when((DATEDIFF(Month ,SBP.EFF_DATE,getdate())< 12)
OR (SBP.EFF_DATE)> getdate())Then 1
else 0
END)
Else 0
END)
Else 0
END
) end
as 'RenewalIND'
,SBC.INCOMING_CHECK_NUMBER
,SBC.IS_Posted as ISPosted
,SBC.Number_Of_Lives
,BA.OFFICE_NAME
,SBC.Outgoing_CHECK_NUMBER
,SBC.PaidAmount
,SBC.PAIDBROKERID
,SBC.PAIDBROKERNAME
,SBC.PAYEE_ID
,SBC.PAYEE_NAME
,SBP.PLAN_ID
,SBP.POLICY_NUM
,SBC.Apply_to_date as Premium_Month
,SBC.Statement_id
,SBC.Statement_Status_desc
,'BP' System_ID
from Stage.BP_Cash SBC
INNER Join Stage.BP_policies SBP ON SBC.Adhoc_Product_id = SBP.PLAN_ID
INNER Join Ods.Case_Plan_Mapping CPM ON CPM.Plan_Id = SBC.Adhoc_Product_id
INNER Join Ods.Account_Case AC ON CPM.Case_Number_Id = AC.Case_Number_Id
INNER Join Stage.BP_Accounts BA ON AC.Account_Id = BA.CLIENT_ID
Left Join Stage.EpicorMapping SEM ON BA.OFFICE_NAME = SEM.BPOffice
AND (case when BA.department_name = 'Retail' then ''
else isnull(BA.Department_name,'')end) = ISNULL(SEM.BPDept,'')
And SEM.System_Id = 'bp'
LEFT JOIN Warehouse.warehouse.dbo.dim_bmll_Location Wl ON isnull(BA.office_id,0)= isnull(Wl.Office_Id,0)
and ISNULL(BA.department_id,0) = ISNULL(Wl.department_id,0)
LEFT JOIN ODS.VW_GL_Accounts AMR ON AMR.[transaction]= 'Cash'
AND AMR.Account_Type = 'Rev'
AND AMR.System = 'BP'
AND AMR.[GAID] =WL.GENID
where SBP.EFF_DATE >='2009-09-01'
September 18, 2011 at 10:12 pm
Can't you just count off a field that will be in every record, like this:
Select count(account_id),
... blah blah blah ...
group by ... everything else in select list ...
The first line of code starts with coffee. The last line ends with alcohol.
September 19, 2011 at 12:32 am
Hello Friend,
At first be sure that all columns are uniquely named and should have names. your one column "[update epicor mapping]" were not having name . After naming you can try it ....
Select COUNT(*)
From
(
Select AC.Account_Id
,SBP.Billing_CARRIER_ID as Carrier_id
,SBP.Billing_CARRIER_NAME as Carrier_Name
,AC.Case_Number_Id
,SBC.Cash_Received_Premium
, 1 CASH_IND
,SEM.GLCompany as Company
, SBP.AcqCode as CostCenter
,SBC.Date_Paid_To_Broker
,BA.DEPARTMENT_NAME
,SBC.Deposit_date
, 0 DerivedLivesInd
, 0 DerivesPremiumInd
,ISNULL(AMR.JE_Account,'update epicor mapping') as [update epicor mapping]
,Wl.GenID as GAID
,'CashBP'+cast(cast(SBC.Deposit_date as date) as varchar(9))+isnull(Wl.GenID,'unknown') as JE_Account_Description
,Case
When ((DATEDIFF(Month ,SBP.ORIG_EFF_DATE,getdate())< 12 )
OR
(GETDATE()<SBP.ORIG_EFF_DATE)) then 1 else
(Case
when(SBP.CANCELLATION_DATE>'1900-01-01 00:00:00.000')
then (Case when(DATEDIFF(Month ,SBP.CANCELLATION_DATE,SBP.EFF_DATE)> 6 )
then(Case when((DATEDIFF(Month ,SBP.EFF_DATE,getdate())< 12)
OR (SBP.EFF_DATE)> getdate())Then 1
else 0
END)
Else 0
END)
Else 0
END
) end
as 'RenewalIND'
,SBC.INCOMING_CHECK_NUMBER
,SBC.IS_Posted as ISPosted
,SBC.Number_Of_Lives
,BA.OFFICE_NAME
,SBC.Outgoing_CHECK_NUMBER
,SBC.PaidAmount
,SBC.PAIDBROKERID
,SBC.PAIDBROKERNAME
,SBC.PAYEE_ID
,SBC.PAYEE_NAME
,SBP.PLAN_ID
,SBP.POLICY_NUM
,SBC.Apply_to_date as Premium_Month
,SBC.Statement_id
,SBC.Statement_Status_desc
,'BP' System_ID
from Stage.BP_Cash SBC
INNER Join Stage.BP_policies SBP ON SBC.Adhoc_Product_id = SBP.PLAN_ID
INNER Join Ods.Case_Plan_Mapping CPM ON CPM.Plan_Id = SBC.Adhoc_Product_id
INNER Join Ods.Account_Case AC ON CPM.Case_Number_Id = AC.Case_Number_Id
INNER Join Stage.BP_Accounts BA ON AC.Account_Id = BA.CLIENT_ID
Left Join Stage.EpicorMapping SEM ON BA.OFFICE_NAME = SEM.BPOffice
AND (case when BA.department_name = 'Retail' then ''
else isnull(BA.Department_name,'')end) = ISNULL(SEM.BPDept,'')
And SEM.System_Id = 'bp'
LEFT JOIN Warehouse.warehouse.dbo.dim_bmll_Location Wl ON isnull(BA.office_id,0)= isnull(Wl.Office_Id,0)
and ISNULL(BA.department_id,0) = ISNULL(Wl.department_id,0)
LEFT JOIN ODS.VW_GL_Accounts AMR ON AMR.[transaction]= 'Cash'
AND AMR.Account_Type = 'Rev'
AND AMR.System = 'BP'
AND AMR.[GAID] =WL.GENID
where SBP.EFF_DATE >='2009-09-01'
) dt
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply