troubleshooting Question

Query Syntax help

Avatar of Star79
Star79Flag for United States of America asked on
Microsoft SQL Server 2005Microsoft SQL Server 2008
5 Comments1 Solution236 ViewsLast Modified:
Hello all,
Sorry for such a big code..its executing with errors on the if then else loop at the end of the storeprocedure.Iam not able to figure out the loop syntax.Please help.


USE [FwReports]
GO
/****** Object:  StoredProcedure [dbo].[vw _perdiemcoverage]    Script Date: 04/03/2013 10:07:04 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[vw _perdiemcoverage](
 @FacID varchar(6),
 @MStart datetime,
 @MEnd datetime
) as

declare @t table (
 FacID varchar(6),
 PatID varchar(11),
 DispenseDt datetime,
 RxNo int,
 RoNo int, 
 PriceCd varchar(10),
 Qty numeric(9,4),
 NDC varchar(11),
 DrugLabelName varchar(30),
 DaysSupply numeric(5,2),
 TtlPrice money,
 PatName varchar(50), prid integer, prmoney numeric(9,4),pdays int,pstartdt datetime,penddt datetime,
 billamount numeric(9,4),billed datetime,OTC INT,IVTYPE VARCHAR(1), CCType varchar(1),perdiemratedesc varchar(50)

)

set nocount on
declare @f varchar(6), @PatID varchar(11),@FirstDay datetime, @LastDay datetime
declare @dispensedt datetime, @rono int, @rxno int, @ttlprice money, @creditamt money,@prid integer,@pstartdt 

datetime,@penddt datetime,@prrate numeric(9,4),@pdays integer,@perdiemratedesc varchar(50)
SET ANSI_WARNINGS OFF

declare @P table (
 comparevalue varchar(20)
) 
insert into @P(comparevalue)
 SELECT CCID FROM PHARM..CompoundS where CCType='I' and PRIMNDC NOT IN(select * from vw_crystalIVExclusionDrugs)
 
 declare @mE table (
 comparevalue varchar(20)
) 
insert into @mE(comparevalue)
select distinct GPIDrugGroup+GPIDrugClass+GPIDrugSubClass+GPIDrugNameCd  from PerDiemFormulary
INNER JOIN PerDiemRates ON PerDiemFormulary.PerDiemRateId = PerDiemRates.PerDiemRateID
WHERE PerDiemFormulary.FacId=@FacID AND PerDiemRateDesc IN('MEDICARE','MGDCARE')AND
   CDID='*' and Product='*' 
   
    declare @mI table (
 comparevalue varchar(20)
) 
insert into @mI(comparevalue)
select distinct GPIDrugGroup+GPIDrugClass+GPIDrugSubClass+GPIDrugNameCd  from PerDiemFormulary
INNER JOIN PerDiemRates ON PerDiemFormulary.PerDiemRateId = PerDiemRates.PerDiemRateID
WHERE PerDiemFormulary.FacId=@FacID AND PerDiemRateDesc IN('MCAIDECF')AND
   CDID='*' and Product='*' 

declare @nonformularyE table (
 comparevalue varchar(20)
) 
insert into @nonformularyE(comparevalue)
select distinct GPIDrugGroup+GPIDrugClass+GPIDrugSubClass+GPIDrugNameCd+Product  from PerDiemFormulary 
INNER JOIN PerDiemRates ON PerDiemFormulary.PerDiemRateId = PerDiemRates.PerDiemRateID

where PerDiemFormulary.FacID=@FacID and  PerDiemRateDesc IN('MEDICARE','MGDCARE') and CDID='*' and Product <> '*'

declare @nonformularyI table (
 comparevalue varchar(20)
) 
insert into @nonformularyI(comparevalue)
select distinct GPIDrugGroup+GPIDrugClass+GPIDrugSubClass+GPIDrugNameCd+Product  from PerDiemFormulary 
INNER JOIN PerDiemRates ON PerDiemFormulary.PerDiemRateId = PerDiemRates.PerDiemRateID

where PerDiemFormulary.FacID=@FacID and  PerDiemRateDesc IN('MCAIDECF') and CDID='*' and Product <> '*'
declare c1 cursor for
 select pde.facid, pde.patid,pde.perdiemrateid, pdr.PerDiemRate,
			 case 
			  when StartDt >@mstart  then StartDt 
			  else @mstart  
			 end as starting,
			 case 
			  when EndDt is null then @mend
			  when EndDt
			   > @mend then @mend
			  else EndDt
			 end as ending,DATEDIFF("day",
			 case 
			  when StartDt >@mstart  then StartDt 
			  else @mstart  
			 end,
			 case 
			  when EndDt is null then @mend
			  when EndDt
			   > @mend then @mend
			  else EndDt
			 end)+1 as pdays,pde.startdt as pstartdt,pde.enddt as penddt,pdr.perdiemratedesc
from PatientPerDiemEligibility  pde inner join PerDiemRates pdr on pde.PerDiemRateId = pdr.PerDiemRateID
where 
 StartDt <= @mend and
 (EndDt is null or EndDt >= @mstart) and
 pde.FacID =@FacID

open c1


fetch next from c1 into @f, @patid,@prid,@prrate,@firstday, @lastday,@pdays,@pstartdt,@penddt,@perdiemratedesc
while @@fetch_status = 0 begin
				 insert into @t (FacID, PatID, RxNo, RoNo, PriceCd, NDC, DrugLabelName, DispenseDt,
				  DaysSupply, Qty,  TtlPrice, prid,pdays,pstartdt,penddt,billamount,billed,IVTYPE,CCType,perdiemratedesc)
				  
				  select facid, patid, rxno, RoNo, PriceCd, case when len(ltrim(coalesce(hrxs.ccid, ''))) = 0 then ndc else 

				hrxs.ccid end,
				  druglabelname, dispensedt, dayssupply,HRXS.Qty,ttlprice - coalesce(creditamt, 0),
				  @prid,@pdays,@firstday,@lastday,'0.00',Posted,hrxs.IVType,ISNULL(Compounds.CCType,''),@perdiemratedesc
				 from hrxs
				 left join Compounds on Compounds.CCID = hrxs.CCID 

				 where facid = @f and patid = @patid and udrx = 0 and 
				  dateadd(day, dayssupply -1, dispensedt) >= @firstday and
				  dispensedt <= @lastday and
				  dispensedt between @MStart and @MEnd and 
				  transtype in ('p','b','q','u') and reversed = 0 


				union 
				select facid, patid, rxno, RoNo, PriceCd, case when len(ltrim(coalesce(Billing.ccid, ''))) = 0 then ndc else 

				Billing.ccid end,
				  druglabelname, dispensedt, dayssupply, Billing.qty, ttlprice,@prid,@pdays,@firstday,@lastday,billing.BillAmt,
				  billing.Billed,ISNULL(Compounds.CCType,''),ISNULL(Compounds.IVType,''),@perdiemratedesc
				 from Billing
				 left join Compounds on Compounds.CCID = Billing.CCID
				where facid = @f and patid = @patid  and Billed between @MStart and @MEnd and
				 billing.ccid in (select perdiemitemno from perdiemrates where facid =  @FacID) 
   
 fetch next from c1 into @f, @patid,@prid,@prrate,@firstday, @lastday,@pdays,@pstartdt,@penddt,@perdiemratedesc
end
close c1
deallocate c1
UPDATE @t SET OTC = 0
UPDATE @t SET OTC = 1 WHERE NDC IN
   (SELECT NDC FROM Drug..KeyIdentifiers WHERE RxOtcInd IN ('O','P'))
UPDATE @t SET OTC = 2 WHERE IVTYPE ='1'
UPDATE @t SET OTC = 2 WHERE CCType ='I'
UPDATE @t SET OTC = 3 WHERE NDC IN
   (select perdiemitemno from perdiemrates where facid =  @FacID)   
Update @t Set PatName = b.PatName 
from @t as a
Left Outer Join Pat..vPatNames as b on a.FacId = b.FacId and a.PatId = b.PatId

if @t.perdiemratedesc='MCAIDECF' then
begin

DELETE FROM @t WHERE NDC IN(SELECT CDID FROM PerDiemFormulary WHERE FacID=@FacID);
delete from @t where NDC in
( select NDC from KeyIdentifiers where GPIDrugGroup+GPIDrugClass+GPIDrugSubClass+GPIDrugNameCd
 in (select * from @mI));
 delete from @t where NDC IN
 ( select NDC from KeyIdentifiers where GPIDrugGroup+GPIDrugClass+GPIDrugSubClass+GPIDrugNameCd+RIGHT(KeyIdentifiers.GPI, 4)
 in (select * from @nonformularyI))
 end;
else 
if (@t.perdiemratedesc ='MEDICARE') or (@t.perdiemratedesc ='MGDCARE') 
 begin
 DELETE FROM @t WHERE NDC IN(SELECT CDID FROM PerDiemFormulary WHERE FacID=@FacID);
delete from @t where NDC in
( select NDC from KeyIdentifiers where GPIDrugGroup+GPIDrugClass+GPIDrugSubClass+GPIDrugNameCd
 in (select * from @mE));
 delete from @t where NDC IN
 ( select NDC from KeyIdentifiers where GPIDrugGroup+GPIDrugClass+GPIDrugSubClass+GPIDrugNameCd+RIGHT(KeyIdentifiers.GPI, 4)
 in (select * from @nonformularyE));
 end;

select * from @t ORDER BY PatName




 
ASKER CERTIFIED SOLUTION
Aneesh
Database Consultant
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros