0

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 fromdate and todate, but I am getting only the first record.

3
  • Can you please provide an example @fromdate and @todate? – J.D. Jan 22 at 13:05
  • declare @fromdate date='01-jan-2021 while @fromdate<'01-feb-2021 begin select convert(varchar(12),@fromdate,106) set @fromdate=dateadd(day,1,@fromdate end; – hemu_k Jan 22 at 14:27
  • Yes, but you also have a WHERE rnk = 1 which will only give you the first record and as you are getting all the records before the @fromDate, this will always be the same – Martin Cairney Jan 24 at 22:42
1

In this section of your procedure:

where rtrim(substring(PrdAcctId,1,8)) in (@prd)
              and floor(cast(substring(PrdAcctId,17,8) as int))=0
              and CblDate<=@fromdate

you are using the @fromdate as the filter and looking for the values earlier. Then you increase the @fromdate value. But the earliest date before Wednesday of the current week is still the same when I get the earliest date before Thursday of the current week - so wouldn't you end up with the same record?

I also assume there is more to the procedure as you only insert into the temp table and never query it? What is the remainder of the procedure? Please post the entire code. Do you get multiple records with the same value as the output?

4
  • yes sir I created a report in report builder and it is giving me result now i changed nothing in a procedure .. – hemu_k Jan 22 at 14:26
  • i am setting @fromdate value with dateadd() function – hemu_k Jan 22 at 14:30
  • prd Code bal date amt 101001 01-Jan-2021 21490232.4 101001 02-Jan-2021 21490232.4 101001 03-Jan-2021 21490232.4 101001 04-Jan-2021 21490232.4 101001 05-Jan-2021 21490232.4 101001 06-Jan-2021 21490232.4 this a result set from my report builder – hemu_k Jan 22 at 14:37
  • Your output selects the value of @fromDate, but the others come from the inner select which is ALWAYS selecting rnk=1 - i.e. the same record. You need to look at doing this as a set-based approach by maybe creating a temp table of all the dates you want and joining to your other table – Martin Cairney Jan 24 at 22:45
0

this is my stored procedure procedure [dbo].[hstatement] (@fromdate datetime,@todate datetime,@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,Balance rank() over (partition by LBrCode,PrdAcctId order by CblDate desc ) rnk from cl_balances 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 ;

                        set @fromdate=dateadd(day,1,@fromdate)

end;

select LBrCode,prdCode,bal_date,bal_amt from #tempdata end; now the problem is i want to select multiple items from picklist but in spite of selecting report dose show only first selected value from picklist can anyone help?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.