Click here to Skip to main content
14,425,302 members
   

Database

 
GeneralRe: Can't get my 2nd table working Pin
piano00112-Jun-18 16:42
Memberpiano00112-Jun-18 16:42 
QuestionError in Restoring SQL Server Backup of v 2012 to v 2008 Pin
indian1431-Jun-18 12:14
Memberindian1431-Jun-18 12:14 
AnswerRe: Error in Restoring SQL Server Backup of v 2012 to v 2008 Pin
Victor Nijegorodov1-Jun-18 21:46
MemberVictor Nijegorodov1-Jun-18 21:46 
AnswerRe: Error in Restoring SQL Server Backup of v 2012 to v 2008 Pin
Eddy Vluggen2-Jun-18 1:21
mveEddy Vluggen2-Jun-18 1:21 
GeneralRe: Error in Restoring SQL Server Backup of v 2012 to v 2008 Pin
indian1434-Jun-18 7:10
Memberindian1434-Jun-18 7:10 
GeneralRe: Error in Restoring SQL Server Backup of v 2012 to v 2008 Pin
Eddy Vluggen4-Jun-18 8:08
mveEddy Vluggen4-Jun-18 8:08 
QuestionA Control flow Task is appearing in the SSIS Packages xml even after disabling and the deleting it. Pin
indian14331-May-18 14:34
Memberindian14331-May-18 14:34 
QuestionThe following Query is taking too long to execute Pin
indian14330-May-18 14:33
Memberindian14330-May-18 14:33 
Hi,

I am executing the following query, its taking too long to execute, can anybody give me any advice what can I do to improve its performance, because its taking hours of time to execute. Thanks in advance.
SELECT
    vw_OwnerXML5010.Owner,
    vw_OwnerXML5010.Billing_EIN AS TP_EIN,
    vw_OwnerXML5010.TP_Name AS TP_NAME,
    vw_OwnerXML5010.County_Code AS County_Code,
    (
   SELECT
   (
    SELECT
        vw_ServiceFacilityXML5010.Service_Facility_NPI AS "Service_Facility_NPI",
        vw_ServiceFacilityXML5010.County_Owned_Indicator AS "County_Owned_Indicator",
    (
    Select 
        vw_ServicesXML5010.Procedure_Code AS "Procedure_Code",
        vw_ServicesXML5010.Modifier1 As "Modifier1",
        vw_ServicesXML5010.Modifier2 AS "Modifier2",
        vw_ServicesXML5010.Modifier3 AS "Modifier3",
        vw_ServicesXML5010.Modifier4 AS "Modifier4",
        vw_ServicesXML5010.From_Date AS "From_Date",
        vw_ServicesXML5010.To_Date AS "To_Date",
        vw_ServicesXML5010.Revenue_Code AS "Revenue_Code",
        vw_ServicesXML5010.Place_Of_Service AS "Place_Of_Service",
        vw_ServicesXML5010.Taxonomy_Code AS "Taxonomy_Code"
    FROM 
    vw_ServicesXML5010 
    WHERE
        vw_ServiceFacilityXML5010.Service_Facility_NPI = vw_ServicesXML5010.Service_Facility_NPI and 
        vw_ServiceFacilityXML5010.County_Code = vw_ServicesXML5010.County_Code
        and vw_ServiceFacilityXML5010.Billing_EIN = vw_ServicesXML5010.Billing_EIN  
    FOR XML PATH('Service'), TYPE , ELEMENTS XSINIL
    ) 
    FROM 
    vw_ServiceFacilityXML5010
    WHERE
        vw_ServiceFacilityXML5010.County_Code = vw_OwnerXML5010.County_Code
        and vw_ServiceFacilityXML5010.Billing_EIN = vw_OwnerXML5010.Billing_EIN
    FOR XML PATH ('TB_SERVICE_FACILITY'), TYPE
) FOR XML PATH ('TB_BILLING_PROVIDER_FILE'), TYPE   
)
FROM 
    dbo.vw_SDMCPhaseTwoParentLevelXML vw_OwnerXML5010
ORDER BY vw_OwnerXML5010.County_Code
FOR XML PATH ('TB_TRADING_PARTNER'), root('TB_BILLING_PROVIDER_FILES')

Here are the following Views:
CREATE VIEW [dbo].[vw_SDMCPhaseTwoParentLevelXML]
AS
SELECT
        DISTINCT 'DMH' AS [Owner]
        ,'' AS [Billing_NPI]
        ,SUBSTRING(RTRIM(LE.TaxId), 1, 9) AS [Billing_EIN]
        ,'' AS DirectIndicator
        ,CO.PK_Geographic_Location_Code AS [County_Code]
        ,CO.County_Name AS [TP_Name]
    FROM
        dbo.LegalEntity LE<br />
        INNER JOIN dbo.county CO
            ON LE.FKCountyLKPId = CO.PKCountyId
    Where LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')
    AND LEFT(LegalEntityNbr,3) IN ('000')
    AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
GO

CREATE VIEW [dbo].[vw_ServiceFacilityXML5010]
AS
SELECT
    distinct VW.Billing_EIN AS [Billing_EIN]
    ,CO.PK_Geographic_Location_Code AS [County_Code]<br />
    ,US.NPINumber AS [Service_Facility_NPI]
    , 'true' AS [County_Owned_Indicator]
    FROM
        dbo.Provider C
        INNER JOIN dbo.ProviderDate CD
            ON CD.FKProviderId = C.PKProviderId
        INNER JOIN dbo.ProviderService CS
            ON CS.FKProviderDateId = CD.PKProviderDateId and cs.FKProviderId=cd.FKProviderId
        INNER JOIN dbo.ProviderService CSD
            ON CSD.FKProviderId = CS.FKProviderId
        LEFT OUTER JOIN dbo.ProviderService MCS
            ON MCS.FKProviderId = CS.FKProviderId
        LEFT OUTER JOIN dbo.ProviderService MCSD
            ON MCSD.FKProviderId = MCS.FKProviderId
        INNER JOIN dbo.ServiceFunctionCategoryLKP SFC
            ON SFC.PKServiceFunctionCategoryLKPId = CS.FKServFuncCatMCModeOfServiceId
        INNER JOIN dbo.ModeOfServiceLKP MS
            ON MS.Code = SFC.ModeOfServiceCode
        LEFT OUTER JOIN dbo.ServFuncCatMCModeOfService SFCMMS
            ON MS.Code= SFCMMS.MCModeOfServiceCode
        LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
            ON MMS.PKMCModeOfServiceLKPId = SFCMMS.MCModeOfServiceCode
        INNER JOIN dbo.NPIAssociation USP
            ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId=(SELECT Top 1 PKParentTypeLKPId from ParentTypeLKP WHERE ParentCode='PRV')
        INNER JOIN  dbo.NPI US
            ON US.PKNPIId = USP.FKNPIId
        INNER JOIN dbo.LegalEntity LE
            ON LE.PKLegalEntityId = C.FKLegalEntityId
        --INNER JOIN dbo.usrTaxid TX
        --  ON LE.FKTaxId = TX.PKTaxId
        INNER JOIN dbo.County CO
            ON C.FKCountyLKPId = CO.PKCountyId
        INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
            ON CO.PK_Geographic_Location_Code = VW.county_code
        INNER JOIN dbo.ProviderType FT
            ON C.ProviderTypeId = FT.ProviderTypeId
        INNER JOIN dbo.MCMSSFCrosswalkLKP SP
            ON MMS.Code = SP.MCModeOfServiceCode
    Where LEFT(LegalEntityNbr,3) IN ('000', 'AFC')
    AND LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')<br />
    AND MMS.Code IS NOT NULL
    AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
    AND SP.ServiceFunctionCategoryCode = SFC.ServiceFunctionCategoryCode
    UNION ALL<br />
SELECT
    distinct    VW.Billing_EIN AS [Billing_EIN]
    ,CO.PK_Geographic_Location_Code AS [County_Code]<br />
    ,US.NPINumber AS [Service_Facility_NPI]
    FROM
        dbo.Provider C
        INNER JOIN dbo.ProviderDate CD
            ON CD.FKProviderId = C.PKProviderId
        INNER JOIN dbo.ProviderService CS
            ON CS.FKProviderDateId = CD.PKProviderDateId and cs.FKProviderId=cd.FKProviderId
        INNER JOIN dbo.ProviderService CSD
            ON CSD.FKProviderId = CS.FKProviderId
        LEFT OUTER JOIN dbo.ProviderService MCS
            ON MCS.FKProviderId = CS.FKProviderId
        LEFT OUTER JOIN dbo.ProviderService MCSD
            ON MCSD.FKProviderId = MCS.FKProviderId
        INNER JOIN dbo.ServiceFunctionCategoryLKP SFC
            ON SFC.PKServiceFunctionCategoryLKPId = CS.FKServFuncCatMCModeOfServiceId
        INNER JOIN dbo.ModeOfServiceLKP MS
            ON MS.Code = SFC.ModeOfServiceCode
        LEFT OUTER JOIN dbo.ServFuncCatMCModeOfService SFCMMS
            ON MS.Code= SFCMMS.MCModeOfServiceCode
        LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
            ON MMS.PKMCModeOfServiceLKPId = SFCMMS.MCModeOfServiceCode
        INNER JOIN dbo.NPIAssociation USP
            ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId=(SELECT Top 1 PKParentTypeLKPId from ParentTypeLKP WHERE ParentCode='PRV')
        INNER JOIN  dbo.NPI US
            ON US.PKNPIId = USP.FKNPIId
        INNER JOIN dbo.LegalEntity LE
            ON LE.PKLegalEntityId = C.FKLegalEntityId
        --INNER JOIN dbo.usrTaxid TX
        --  ON LE.FKTaxId = TX.PKTaxId
        INNER JOIN dbo.County CO
            ON C.FKCountyLKPId = CO.PKCountyId
        INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
            ON CO.PK_Geographic_Location_Code = VW.county_code
        INNER JOIN dbo.ProviderType FT
            ON C.ProviderTypeId = FT.ProviderTypeId
        INNER JOIN dbo.MCMSSFCrosswalkLKP SP
            ON MMS.Code = SP.MCModeOfServiceCode
    Where LEFT(LegalEntityNbr,3) NOT IN ('000', 'AFC','HFP', '00F')<br />
    AND MMS.Code IS NOT NULL
    AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
    AND SP.ServiceFunctionCategoryCode = SFC.ServiceFunctionCategoryCode

GO

CREATE VIEW [dbo].[vw_ServicesXML5010]
AS
SELECT
    VW.Billing_EIN AS [Billing_EIN]
    ,CO.PK_Geographic_Location_Code AS [County_Code]    
    ,US.NPINumber AS [Service_Facility_NPI]
    ,ISNULL(SP.RevenueCode, '') AS [Revenue_Code]
    ,SP.ProcedureCode AS [Procedure_Code]
    ,SP.Modifier1 as Modifier1
    ,ISNULL(SP.Modifier2, '') AS [Modifier2]    
    ,ISNULL(SP.Modifier3, '') AS [Modifier3]
    ,ISNULL(SP.Modifier4, '') AS [Modifier4]
    ,CASE 
    WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
    WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'
    WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
    WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'  
    ELSE ''
    END Place_Of_Service
    , '' AS Taxonomy_Code   
    ,ISNULL(CONVERT(VARCHAR(8), CS.MCBeginDate, 112), '') AS [From_Date]
    ,CONVERT(VARCHAR(8), CS.MCEndDate, 112) AS [To_Date] 
    FROM
        dbo.Provider C
        INNER JOIN dbo.ProviderDate CD
            ON CD.FKProviderId = C.PKProviderId
        INNER JOIN dbo.ProviderService CS
            ON CS.FKProviderDateId = CD.PKProviderDateId
        INNER JOIN dbo.ServFuncCatMCModeOfService SFC
            ON SFC.PKServFuncCatMCModeOfServiceId = CS.FKServFuncCatMCModeOfServiceId
        INNER JOIN dbo.ModeOfServiceLKP MS
            ON MS.Code = SFC.ModeOfServiceCode
        LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
            ON MMS.Code = SFC.MCModeOfServiceCode
        INNER JOIN dbo.NPIAssociation USP
            ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId IN
            (
            SELECT PKParentTypeLKPId FROM dbo.ParentTypeLKP a WHERE ParentCode IN ('PRV') --, 'NPI'
            )
        INNER JOIN  dbo.NPI US
            ON US.PKNPIId = USP.FKNPIId
        INNER JOIN dbo.LegalEntity LE
            ON LE.PKLegalEntityId = C.FKLegalEntityId
        --INNER JOIN dbo.usrTaxid TX
        --  ON LE.FKTaxId = TX.PKTaxId
        INNER JOIN dbo.County CO
            ON C.FKCountyLKPId = CO.PKCountyId
        INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
            ON CO.PK_Geographic_Location_Code = VW.county_code
        INNER JOIN dbo.FacilityDesignationLKP FT
            ON US.FKFacilityDesignationLKPId = FT.PKFacilityDesignationLKPId
        INNER JOIN dbo.MCMSSFCrosswalkLKP SP
            ON MMS.Code = SP.MCModeOfServiceCode
    Where LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')
    AND MMS.Code IS NOT NULL
    AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
    AND SP.FKServFuncCatMCModeOfServiceId = SFC.PKServFuncCatMCModeOfServiceId
    AND SP.Modifier1 <> 'HK'    
    UNION ALL   
    SELECT
    VW.Billing_EIN AS [Billing_EIN]
    ,CO.PK_Geographic_Location_Code AS [County_Code]    
    ,US.NPINumber AS [Service_Facility_NPI]
    ,ISNULL(SP.RevenueCode, '') AS [Revenue_Code]
    ,SP.ProcedureCode AS [Procedure_Code]
    ,SP.Modifier1 as Modifier1
    ,ISNULL(SP.Modifier2, '') AS [Modifier2]    
    ,ISNULL(SP.Modifier3, '') AS [Modifier3]
    ,ISNULL(SP.Modifier4, '') AS [Modifier4]
    ,CASE 
    WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
    WHEN SP.MCModeOfServiceCode = '12' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'
    WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '20' THEN '23'
    WHEN SP.MCModeOfServiceCode = '18' AND SP.ServiceFunctionCategoryCode = '25' THEN '20'  
    ELSE ''
    END Place_Of_Service
    , '' AS Taxonomy_Code
    ,CASE
        WHEN (SP.ProcedureCode = 'H2015' or SP.ProcedureCode = 'T1017')AND SP.Modifier1 = 'HK' --all Katie A. Services start on 20130101
        THEN '20130101' 
        ELSE
            ISNULL(CONVERT(VARCHAR(8), CS.MCBeginDate, 112), '')
        END [From_Date]
    ,CONVERT(VARCHAR(8), CS.MCEndDate, 112) AS [To_Date] -- end date is not changing for Katie A. services. Just eliminate the rows that have an end date
    FROM
        dbo.Provider C
        INNER JOIN dbo.ProviderDate CD
            ON CD.FKProviderId = C.PKProviderId
        INNER JOIN dbo.ProviderService CS
            ON CS.FKProviderDateId = CD.PKProviderDateId
        INNER JOIN dbo.ServFuncCatMCModeOfService SFC
            ON SFC.PKServFuncCatMCModeOfServiceId = CS.FKServFuncCatMCModeOfServiceId
        INNER JOIN dbo.ModeOfServiceLKP MS
            ON MS.Code = SFC.ModeOfServiceCode
        LEFT OUTER JOIN dbo.MCModeOfServiceLKP MMS
            ON MMS.Code = SFC.MCModeOfServiceCode
        INNER JOIN dbo.NPIAssociation USP
            ON USP.FKParentId = C.PKProviderId AND USP.FKParentTypeLKPId IN
            (
            SELECT PKParentTypeLKPId FROM dbo.ParentTypeLKP a WHERE ParentCode IN ('PRV') --, 'NPI'
            )
        INNER JOIN  dbo.NPI US
            ON US.PKNPIId = USP.FKNPIId
        INNER JOIN dbo.LegalEntity LE
            ON LE.PKLegalEntityId = C.FKLegalEntityId
        --INNER JOIN dbo.usrTaxid TX
        --  ON LE.FKTaxId = TX.PKTaxId
        INNER JOIN dbo.County CO
            ON C.FKCountyLKPId = CO.PKCountyId
        INNER JOIN VW_SDMCPhaseTwoParentLevelXML VW
            ON CO.PK_Geographic_Location_Code = VW.county_code
        INNER JOIN dbo.FacilityDesignationLKP FT
            ON US.FKFacilityDesignationLKPId = FT.PKFacilityDesignationLKPId
        INNER JOIN dbo.MCMSSFCrosswalkLKP SP
            ON MMS.Code = SP.MCModeOfServiceCode
    Where LEFT(LegalEntityNbr,3) NOT IN ('HFP', '00F')
    AND MMS.Code IS NOT NULL
    AND CO.PK_Geographic_Location_Code NOT IN ('99', '00', '65', '66')
    AND SP.ServiceFunctionCategoryCode = SFC.ServiceFunctionCategoryCode    
    AND (SP.Modifier1 = 'HK' and (CS.MCEndDate is null or  (CS.MCBeginDate >= '20130101' or  CS.MCEndDate > '20130101')))
GO

Any help would be greatly helpful, thanks in advance.

Thanks,

Abdul Aleem

"There is already enough hatred in the world lets spread love, compassion and affection."
SuggestionRe: The following Query is taking too long to execute Pin
CHill6031-May-18 3:53
mveCHill6031-May-18 3:53 
GeneralRe: The following Query is taking too long to execute Pin
indian14331-May-18 14:26
Memberindian14331-May-18 14:26 
GeneralRe: The following Query is taking too long to execute Pin
Eddy Vluggen1-Jun-18 1:10
mveEddy Vluggen1-Jun-18 1:10 
QuestionGraph Database vs Dimensional Pin
DADom24-May-18 22:32
MemberDADom24-May-18 22:32 
AnswerRe: Graph Database vs Dimensional Pin
Eddy Vluggen26-May-18 3:45
mveEddy Vluggen26-May-18 3:45 
GeneralRe: Graph Database vs Dimensional Pin
John 1356415428-Jun-18 10:07
MemberJohn 1356415428-Jun-18 10:07 
GeneralRe: Graph Database vs Dimensional Pin
Eddy Vluggen28-Jun-18 10:19
mveEddy Vluggen28-Jun-18 10:19 
QuestionHow to create a insert trigger on just one table? Pin
Member23622-May-18 0:02
MemberMember23622-May-18 0:02 
QuestionRe: How to create a insert trigger on just one table? Pin
CHill6022-May-18 2:34
mveCHill6022-May-18 2:34 
AnswerRe: How to create a insert trigger on just one table? Pin
Richard Deeming22-May-18 3:30
communityengineerRichard Deeming22-May-18 3:30 
AnswerRe: How to create a insert trigger on just one table? Pin
Mycroft Holmes22-May-18 15:09
MemberMycroft Holmes22-May-18 15:09 
GeneralRe: How to create a insert trigger on just one table? Pin
CHill6022-May-18 23:56
mveCHill6022-May-18 23:56 
GeneralRe: How to create a insert trigger on just one table? Pin
Mycroft Holmes23-May-18 14:20
MemberMycroft Holmes23-May-18 14:20 
GeneralRe: How to create a insert trigger on just one table? Pin
Member23623-May-18 0:04
MemberMember23623-May-18 0:04 
GeneralRe: How to create a insert trigger on just one table? Pin
Mycroft Holmes23-May-18 14:23
MemberMycroft Holmes23-May-18 14:23 
QuestionFOR XML PATH is not creating Child elements if they are coming with null values Pin
indian14316-May-18 13:39
Memberindian14316-May-18 13:39 
AnswerRe: FOR XML PATH is not creating Child elements if they are coming with null values Pin
CHill6022-May-18 2:32
mveCHill6022-May-18 2:32 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.