Avatar of Star79
Star79
Flag for United States of America asked on

Query Syntax help

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




 

Open in new window

Microsoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
Simone B

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Aneesh

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Simone B

I removed the "then" and also put square brackets around your table variable [@t]. This has removed the syntax errors. Give it a try and let me know if that helps.

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

Open in new window

Star79

ASKER
Annesh,what does the select 1 do in a query
Aneesh

It checks whether any records exists matching the condition, and in case it matches, returns '1'; you can put anything there instead of '1', *, null anything;
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Simone B

Aneeshattingal has the right idea.