September 18, 2011 at 8:14 pm
Hi I need to get count of records where my columns are from different tables.Is it possible?Please see my below 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'
Thanks,
Komal
September 19, 2011 at 12:03 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 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply