I am trying to generate a report from this procedure
create procedure [dbo].[hstatement] ( @fromdate date, @todate date, @prd varchar(8) ) as begin create table #tempdata ( LBrCode int, prdCode varchar(8), bal_date datetime, bal_amt numeric(18,2) ) while @fromdate<@todate begin insert into #tempdata select LBrCode, substring(PrdAcctId,1,8) prdcd, convert(varchar(12),@fromdate,106) tdt, Balance4 from ( select LBrCode, PrdAcctId, CblDate dt, Balance4, rank() over (partition by LBrCode,PrdAcctId order by CblDate desc ) rnk from cl_balance where rtrim(substring(PrdAcctId,1,8)) in (@prd) and floor(cast(substring(PrdAcctId,17,8) as int))=0 and CblDate<=@fromdate ) x where rnk= 1 and LBrCode=1; set @fromdate=dateadd(day,1,@fromdate) end;
The procedure is compiled successfully.
However, I am expecting a result as each date is between
todate, but I am getting only the first record.