API tools faq
paste
Advertisement
Guest User

sqlquery

a guest
Oct 10th, 2019
115
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
T-SQL 9.31 KB | None | 0 0
raw download clone embed print report
  1.  
  2. ;WITH
  3. FilteredLogDate AS
  4. (
  5.     SELECT eowhe.LogDate
  6.     , eowhe.EnterpriseOrderEntryWorkflowHistoryEventId [eventid]
  7.     , ROW_NUMBER() OVER (PARTITION BY eowhe.logdate order by eowhe.logdate desc) AS FILTERLD
  8.     , eoe.EnterpriseOrderEntryId [entryid]
  9.     from [RxKey].[dbo].[EnterpriseOrderEntryWorkflowHistoryEvent] eowhe
  10.     LEFT OUTER JOIN [RxKey].[dbo].EnterpriseOrderEntry eoe on eowhe.EnterpriseOrderEntryId = eoe.EnterpriseOrderEntryId
  11.     where Description like '''Packing'' stage completed%'
  12.     and cast (eowhe.LogDate as date) = (SELECT  DATEADD(DAY, CASE (DATEPART(WEEKDAY, GETDATE()) + @@DATEFIRST) % 7
  13.                         WHEN 1 THEN -2 --Sunday, get friday by shipby
  14.                         WHEN 2 THEN -3 --Monday, get friday by shipby
  15.                         ELSE -1
  16.                     END, DATEDIFF(DAY, 0, GETDATE())))
  17.                     ) Select * INTO #TEMPTABLE from FilteredLogDate where FILTERLD = 1
  18.  
  19. SELECT *
  20. INTO #STSHIPMENTTEMP
  21. FROM
  22. (
  23.  
  24. SELECT
  25.         ISNULL(NULLIF(CAST(Customer.Identifier as varchar(25)),''),'||') as 'Patient ID',
  26.         ISNULL(NULLIF(CAST(Customer.LegacyCustomerID as varchar(25)),''),'||') as 'HUB Patient #',
  27.         ISNULL(NULLIF(CAST(pat.FirstName as varchar(50)),''),'||') 'Patient First Name',
  28.         ISNULL(NULLIF(CAST(pat.LastName as varchar(50)),''),'||') as 'Patient Last Name',
  29.         ISNULL(NULLIF(CAST(CustomerAddress.Address1 as varchar(50)),''),'||') as 'Patient Address1',
  30.         ISNULL(NULLIF(CAST(CustomerAddress.Address2 as varchar(50)),''),'||') as 'Patient Address2',
  31.         ISNULL(NULLIF(CAST(CustomerAddress.City as varchar(50)),''),'||') as 'Patient City',
  32.         ISNULL(NULLIF(CAST(CustomerAddress.State as varchar(2)),''),'||') as 'Patient State',
  33.         ISNULL(NULLIF(CAST(CustomerAddress.ZipCode as varchar(9)),''),'||') as 'Patient Zip',
  34.         ISNULL(NULLIF(CAST((SELECT TOP 1 PhoneNumber FROM [RxKey].[dbo].CustomerPhoneNumber with (nolock) WHERE phonenumbertype = 1 AND CustomerId = Customer.CustomerId) as varchar(10)),''),'||') as 'Home Phone',
  35.         ISNULL(NULLIF(CAST((SELECT TOP 1 PhoneNumber FROM [RxKey].[dbo].CustomerPhoneNumber with (nolock) WHERE phonenumbertype = 3 AND CustomerId = Customer.CustomerId) as numeric(25)),''),'||') as 'Mobile Phone',
  36.         ISNULL(NULLIF(CAST((SELECT TOP 1 PhoneNumber FROM [RxKey].[dbo].CustomerPhoneNumber with (nolock) WHERE phonenumbertype = 2 AND CustomerId = Customer.CustomerId) as varchar(25)),''),'||') as 'Work Phone',
  37.         CASE
  38.             WHEN ISNULL(NULLIF(CAST(Customer.Gender as varchar(1)),''),'||') = 0 THEN 'U'
  39.             WHEN ISNULL(NULLIF(CAST(Customer.Gender as varchar(1)),''),'||') = 1 THEN 'M'
  40.             WHEN ISNULL(NULLIF(CAST(Customer.Gender as varchar(1)),''),'||') = 2 THEN 'F'
  41.         END AS 'Patient Gender',
  42.         ISNULL(NULLIF(CAST(CONVERT (Varchar, pat.DateOfBirth, 112) as varchar(25)),''),'||') as 'Patient DOB',
  43.         ISNULL(NULLIF(CAST(EnterpriseOrder.DestinationAddress1 as varchar(50)),''),'||') as 'Patient Ship Address1',
  44.         ISNULL(NULLIF(CAST(EnterpriseOrder.DestinationAddress2 as varchar(50)),''),'||') as 'Patient Ship Address2',
  45.         ISNULL(NULLIF(CAST(EnterpriseOrder.DestinationCity as varchar(50)),''),'||') as 'Patient Ship City',
  46.         ISNULL(NULLIF(CAST(EnterpriseOrder.DestinationState as varchar(2)),''),'||') as 'Patient Ship State',
  47.         ISNULL(NULLIF(CAST(EnterpriseOrder.DestinationZip as varchar(9)),''),'||') as 'Patient Ship Zip',
  48.         ISNULL(NULLIF(CAST(rdc.DiseaseDescription as numeric(25)),''),'||') as 'Primary Diagnosis Code',
  49.         ISNULL(NULLIF(CAST(Prescriber.FirstName as Varchar(50)),''),'||') as 'Prescriber First Name',
  50.         ISNULL(NULLIF(CAST(Prescriber.LastName as Varchar(50)),''),'||') as 'Prescriber Last Name',
  51.         ISNULL(NULLIF(CAST(PrescriberAddress.Address1 as Varchar(50)),''),'||') as 'Prescriber Address1',
  52.         ISNULL(NULLIF(CAST(PrescriberAddress.Address2 as Varchar(50)),''),'||') as 'Prescriber Address2',
  53.         ISNULL(NULLIF(CAST(PrescriberAddress.City as Varchar(50)),''),'||') as 'Prescriber City',
  54.         ISNULL(NULLIF(CAST(PrescriberAddress.State as Varchar(2)),''),'||') as 'Prescriber State',
  55.         ISNULL(NULLIF(CAST(PrescriberAddress.Zip as Varchar(9)),''),'||') as 'Prescriber Zip',
  56.         ISNULL(NULLIF(CAST(PrescriberAddress.PhoneNumber as Varchar(9)),''),'||') as 'Prescriber Phone',
  57.         ISNULL(NULLIF(CAST(PrescriberAddress.FaxNumber as numeric(10)),''),'||') as 'Prescriber Fax',
  58.         ISNULL(NULLIF(CAST(Prescriber.NpiNbr as varchar(30)),''),'||') as 'Prescriber NPI',
  59.         ISNULL(NULLIF(CAST(Drugidentifier.Identifier as varchar(11)),''),'||') as 'NDC#',
  60.         ISNULL(NULLIF(CAST(Drug.Description as varchar(50)),''),'||') as 'Product Description',
  61.         ISNULL(NULLIF(CAST(Refill.DispensedQuantity as numeric(3)),''),'||') as 'Quantity Dispensed',
  62.         ISNULL(NULLIF(CAST(Refill.DaysSupply as numeric(3)),''),'||') as 'Days Supply',
  63.         ISNULL(NULLIF(CAST(PrimaryPlan.PlanName as varchar(50)),''),'||') as 'Program DispenseType',
  64.         ISNULL(NULLIF(CAST(temp.LogDate as date(8)),''),'||') as 'Ship Date',
  65.         '' as ' '
  66.  
  67. FROM  [RxKey].[dbo].EnterpriseOrder with (nolock)
  68. LEFT OUTER JOIN [RxKey].[dbo].RefillDestinationType DestinationTypeChild with (nolock) on DestinationTypeChild.Type = EnterpriseOrder.DeliveryMethod
  69. LEFT OUTER JOIN [RxKey].[dbo].refilldestinationtype DestinationTypeParent with (nolock) on DestinationTypeParent.Type = DestinationTypeChild.ParentId
  70. LEFT OUTER JOIN [RxKey].[dbo].Customer with (nolock) on Customer.CustomerId = EnterpriseOrder.CustomerId
  71. LEFT OUTER JOIN [RxKey].[dbo].CustomerAddress with (nolock) on CustomerAddress.CustomerId = EnterpriseOrder.CustomerId
  72.  
  73. LEFT OUTER JOIN [RxKey].[dbo].EnterpriseOrderEntry with (nolock) on EnterpriseOrderEntry.EnterpriseOrderId = EnterpriseOrder.EnterpriseOrderId and WorkflowState <> 4
  74. LEFT OUTER JOIN [RxKey].[dbo].WorkQueueItem with (nolock) on WorkQueueItem.WorkQueueItemId = EnterpriseOrderEntry.ItemFillId
  75. LEFT OUTER JOIN [RxKey].[dbo].Refill with (nolock) on Refill.RefillId = WorkQueueItem.RefillId
  76. LEFT OUTER JOIN [RxKey].[dbo].Rx with (nolock) on Rx.RxId = Refill.RxId
  77. LEFT OUTER JOIN [RxKey].[dbo].Customer pat on pat.customerid = rx.customerid
  78.  
  79. LEFT OUTER JOIN [RxKey].[dbo].PrescriberAddress with (nolock) on PrescriberAddress.PrescriberAddressId = Refill.PrescriberAddressId
  80. LEFT OUTER JOIN[RxKey].[dbo]. Prescriber with (nolock) on Prescriber.PrescriberId = PrescriberAddress.PrescriberID
  81. LEFT OUTER JOIN [RxKey].[dbo].DrugIdentifier with (nolock) on DrugIdentifier.DrugId = refill.DrugId
  82. LEFT OUTER JOIN [RxKey].[dbo].CustomerDisease with (nolock) on CustomerDisease.CustomerId = Customer.CustomerId
  83. LEFT OUTER JOIN [RxKey].[dbo].[vw_Reporting_CustomerDiseaseDiagnosisAllergy] cdda on cdda.CustomerId = Rx.CustomerId
  84. LEFT OUTER JOIN [RxKey].[dbo].[vw_Reporting_DiseaseCodes] rdc on rdc.DiseaseCode = cdda.DiseaseType
  85.  
  86.  
  87. LEFT OUTER JOIN [RxKey].[dbo].[vw_Reporting_DrugNDC] Drug with (nolock) on Drug.DrugId = Refill.DrugId
  88. LEFT OUTER JOIN [RxKey].[dbo].[vw_RefillClaims] Claim with (nolock) on Claim.RefillId = Refill.RefillId
  89. LEFT OUTER JOIN [RxKey].[dbo].ThirdParty PrimaryPlan with (nolock) on PrimaryPlan.ThirdPartyId = Claim.FirstPlanThirdPartyId
  90. inner JOIN [RxKey].[dbo].StoreProfile with (nolock) ON StoreProfile.StoreNum = EnterpriseOrder.StoreNum
  91.  
  92. inner join #TEMPTABLE temp on temp.entryid = EnterpriseOrderEntry.EnterpriseOrderEntryId
  93.  
  94.  
  95. Where (1=1
  96. and StoreProfile.IsPrimary = 1
  97. and Refill.Status = 7 --(7 = refill has been filled)
  98. and (EnterpriseOrderEntry.WorkflowStage > 120 or EnterpriseOrderEntry.WorkflowStage = 200 and EnterpriseOrderEntry.workflowstate = 5) --PACKING STAGE
  99. and EnterpriseOrderEntry.IsDeleted = 0
  100. and EnterpriseOrder.IsCancelled = 0
  101. and Drugidentifier.Identifier IN ('42747060290','42747060490')) -- 1st condition * ))
  102. or (EnterpriseOrder.IsCancelled = 1 AND EnterpriseOrderEntry.WorkflowStateDesc = 'Transferred') --second condition
  103.  
  104. ) as info;
  105.  
  106.  
  107. ;With dataCTE
  108. AS
  109. (
  110.     select *, ''  as RowCnt
  111.     from #STSHIPMENTTEMP
  112. ),
  113. footerCTE
  114. AS
  115. (
  116.     select
  117.     CAST('' as varchar(25)) as 'Patient ID',
  118.     CAST('' AS varchar(25)) as 'HUB Patient #',
  119.     CAST('' AS varchar(25)) as 'Patient First Name',
  120.     CAST('' AS varchar(10)) as 'Patient Last Name',
  121.     CAST('' AS varchar(50)) as 'Patient Address1',
  122.     CAST('' AS varchar(50)) as 'Patient Address2',
  123.     CAST('' AS varchar(10)) as 'Patient City',
  124.     CAST('' AS varchar(10)) as 'Patient State',
  125.     CAST('' AS varchar(9)) as 'Patient Zip',
  126.     CAST('' AS varchar(50)) as 'SP Secondary Order Status',
  127.     CAST('' AS varchar(50)) as 'Home Phone',
  128.     CAST('' AS varchar(50)) as 'Mobile Phone',
  129.     CAST('' AS varchar(50)) as 'Work Phone',
  130.     CAST('' AS varchar(50)) as 'Patient Gender',
  131.     CAST('' AS varchar(50)) as 'Patient DOB',
  132.     CAST('' AS varchar(50)) as 'Patient Ship Address1',
  133.     CAST('' AS varchar(50)) as 'Prescriber Address2',
  134.     CAST('' AS varchar(50)) as 'Prescriber City',
  135.     CAST('' AS varchar(50)) as 'Prescriber State',
  136.     CAST('' AS varchar(50)) as 'Patient Ship Zip',
  137.     CAST('' AS varchar(50)) as 'Prescriber Phone',
  138.     CAST('' AS varchar(50)) as 'Prescriber Fax',
  139.     CAST('' AS varchar(50)) as 'Prescriber NPI',
  140.     CAST('' AS varchar(50)) as 'NDC#',
  141.     CAST('' AS varchar(50)) as 'Product Description',
  142.     CAST('' AS varchar(50)) as 'Quantity Dispensed',
  143.     CAST('' AS varchar(50)) as 'Days Supply',
  144.     CAST('' AS varchar(50)) as 'Program DispenseType',
  145.     CAST('' AS varchar(50)) as 'Ship Date',
  146.  
  147.  
  148.     ' '+convert(varchar(25),COUNT(*)) as RowCNT from #STSHIPMENTTEMP
  149. )
  150.  
  151. select * from #STSHIPMENTTEMP
  152. union all
  153. select * from footerCTE
  154.  
  155. drop table #STSHIPMENTTEMP
  156.  
  157. drop table #TEMPTABLE
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!