Data issue with sp new financial year.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Data issue with sp new financial year.

Author

Topic

jamesrah
Starting Member

13 Posts

Posted - 2010-05-07 : 06:15:56

Hi,

I have been pulling my hair out trying to fix this issue. The following sp is used to create a crystal report and shows data for the weeks ending 03/28/2010 and 04/11/2010, however does not show data for the weekending 04/04/2010.

Any help or advice would be much appreciated.

Cheers,
James

ALTER PROCEDURE dbo.pr_Arrears_by_age_of_debt @date datetime
AS
BEGIN

DECLARE @StartDate DATETIME
DECLARE @TodaysDate DATETIME
declare @4thperiodenddate datetime
declare @pernumber int
declare @fromdate datetime

--Set @4thperiodenddate= dateadd(month,-1,(select max([per-end-dte]) from rntbal))
if @date is null
begin
SELECT @TodaysDate = CONVERT(DATETIME, CONVERT(CHAR(10),getdate(), 101), 101)
end
else
begin
SELECT @TodaysDate = CONVERT(DATETIME, CONVERT(CHAR(10), @date, 101), 101)
end

set @4thperiodenddate=isnull((select distinct([per-end-dte]) from rntcal
where convert(datetime,[per-end-dte],103)=@TodaysDate),

(select dateadd(wk,-1,(select distinct([per-end-dte]) from rntcal
where convert(datetime,[per-com-dte],103)<=@TodaysDate and convert(datetime,[per-end-dte],103)>=@TodaysDate))))

set @fromdate=dateadd(wk,-3,@4thperiodenddate)
set @pernumber =(select distinct(per) from rntcal where convert(datetime,[per-end-dte],103)= @4thperiodenddate)

--If it is April 1 or greater, then the StartDate is the current year

IF MONTH(@TodaysDate) >= 4

-- Amendment Neil Williams 13th May 2009 the code now selects the end date of period 1 in the current financial year, not the 1st of April

select @StartDate=(select [per-end-dte]
from rntcal
where [rntcalgrp-prop-grp] = 10
and year([per-end-dte]) = year(@todaysdate)
and per = 1)

-- SELECT @StartDate = CONVERT(CHAR(4), YEAR(@TodaysDate)) + '0401'

--Otherwise, it's the previous year

-- ELSE SELECT @StartDate = CONVERT(CHAR(4), YEAR(@TodaysDate)-1) + '0401'

ELSE select @StartDate=(select [per-end-dte]
from rntcal
where [rntcalgrp-prop-grp] = 10
and year([per-end-dte]) = year(@todaysdate)-1
and per = 1)

/*create table trace_table
(startdate datetime,
todaysdate datetime,
fourththperiodenddate datetime,
pernumber int,
fromdate datetime)

insert into trace_table(startdate, todaysdate, fourththperiodenddate, pernumber, fromdate)
values
(@startdate, @todaysdate, @4thperiodenddate, @pernumber, @fromdate)*/

create table #temptable
(tncynumber int,
perenddate datetime null,
Dateinbalance datetime null)

Create table #periodscount
(periodsnumber varchar(25) null,
lessthreemonthsmonth int null,
Lessthreemonthyear int null,
threetosixmonth int null,
threetosixyear int null,
sixtoninemonth int null,
sixtonineyear int null,
ninetotwelvemonth int null,
ninetotwelveyear int null,
twelvetotwentyfourmonth int null,
twelvetotwentyfouryear int null,
twentyfourtotirtysixmonth int null,
twentlyfourtotirtysixyear int null,
overthirtysixmonth int null)

drop table graph

create table graph
(age_of_debt varchar(30),
period_end_date datetime,
number_of_tenancies int)

insert into #temptable(tncynumber,perenddate)
select [tncy-occ-num],[per-end-dte]
from rntbal,
mis_tncy
where rntbal.[bal-val]>75
and [rntsubac-cde]=0
and [per-end-dte]<=@TodaysDate
and [per-end-dte]>=@StartDate
and rntbal.[tncy-occ-num]=mis_tncy.[occ_num]
and (mis_tncy.term_dat is null or mis_tncy.term_dat >= rntbal.[per-end-dte])
and mis_tncy.[per_num] != 1

update #temptable set Dateinbalance=(select top 1[per-end-dte] from rntbal where rntbal.[bal-val]<=0 and rntbal.[rntsubac-cde]=0 and
rntbal.[per-end-dte]<perenddate and rntbal.[tncy-occ-num]=tncynumber order by rntbal.[per-end-dte] desc)

insert into #periodscount (periodsnumber)
select 'Periods ' + convert(varchar(3),@pernumber-3) + ' to '+convert(varchar(3),@pernumber)

Update #periodscount set lessthreemonthsmonth=
(select count(distinct(tncynumber)) from #temptable
where (/*dateinbalance is not null or */dateinbalance > dateadd(m,-3, perenddate))
and perenddate = @4thperiodenddate )

-- and perenddate<=@4thperiodenddate and perenddate>=@fromdate)

Update #periodscount set Lessthreemonthyear=isnull((select count(distinct(tncynumber)) from #temptable
where /*dateinbalance is not null or*/ dateinbalance>dateadd(m,-3, perenddate)),0)

Update #periodscount set threetosixmonth=isnull((select count(distinct(tncynumber)) from #temptable
where dateinbalance<=(dateadd(m,-3,perenddate)-1)
and dateinbalance>=(dateadd(m,-6,perenddate ))
and perenddate=@4thperiodenddate
/*and perenddate>=@fromdate*/),0)

Update #periodscount set threetosixyear=isnull((select count(distinct(tncynumber)) from #temptable
where dateinbalance<=(dateadd(m,-3,perenddate )-1)
and dateinbalance>=(dateadd(m,-6,perenddate ))),0)

Update #periodscount set sixtoninemonth=isnull((select count(distinct(tncynumber)) from #temptable
where dateinbalance<=(dateadd(m,-6,perenddate )-1)
and dateinbalance>=(dateadd(m,-9,perenddate ))
and perenddate=@4thperiodenddate
/*and perenddate>=@fromdate*/),0)

Update #periodscount set sixtonineyear=isnull((select count(distinct(tncynumber)) from #temptable
where dateinbalance<=(dateadd(m,-6,perenddate )-1)
and dateinbalance>=(dateadd(m,-9,perenddate ))),0)

Update #periodscount set ninetotwelvemonth=isnull((select count(distinct(tncynumber)) from #temptable
where dateinbalance<=(dateadd(m,-9,perenddate )-1)
and dateinbalance>=(dateadd(m,-12,perenddate ))
and perenddate=@4thperiodenddate
/*and perenddate>=@fromdate*/),0)

Update #periodscount set ninetotwelveyear=isnull((select count(distinct(tncynumber)) from #temptable
where dateinbalance<=(dateadd(m,-9,perenddate )-1)
and dateinbalance>=(dateadd(m,-12,perenddate ))),0)

Update #periodscount set twelvetotwentyfourmonth=isnull((select count(distinct(tncynumber)) from #temptable
where dateinbalance<=(dateadd(m,-12,perenddate )-1)
and dateinbalance>=(dateadd(m,-24,perenddate ))
and perenddate=@4thperiodenddate
/*and perenddate>=@fromdate*/),0)

Update #periodscount set twelvetotwentyfouryear=isnull((select count(distinct(tncynumber)) from #temptable
where dateinbalance<=(dateadd(m,-12,perenddate )-1)
and dateinbalance>=(dateadd(m,-24,perenddate ))),0)

Update #periodscount set twentyfourtotirtysixmonth=isnull((select count(distinct(tncynumber)) from #temptable
where dateinbalance<=(dateadd(m,-24,perenddate )-1)
and dateinbalance>=(dateadd(m,-36,perenddate ))
and perenddate=@4thperiodenddate
/*and perenddate>=@fromdate*/),0)

Update #periodscount set twentlyfourtotirtysixyear=isnull((select count(distinct(tncynumber)) from #temptable
where dateinbalance<=(dateadd(m,-24,perenddate )-1)
and dateinbalance>=(dateadd(m,-36,perenddate ))),0)

Update #periodscount set overthirtysixmonth=isnull((select count(distinct(tncynumber)) from #temptable
where (dateinbalance<(dateadd(m,-36,perenddate )-1)
or dateinbalance is null)
/*and dateinbalance<(dateadd(m,-36,perenddate ))*/
and perenddate=@4thperiodenddate
/*and perenddate>=@fromdate*/),0)

insert into graph (age_of_debt, number_of_tenancies)
select 'Less than 3 months', #periodscount.lessthreemonthsmonth
from #periodscount
insert into graph (age_of_debt, number_of_tenancies)
select '3 to 6 months', #periodscount.threetosixmonth
from #periodscount
insert into graph (age_of_debt, number_of_tenancies)
select '6 to 9 months', #periodscount.sixtoninemonth
from #periodscount
insert into graph (age_of_debt, number_of_tenancies)
select '9 to 12 months', #periodscount.ninetotwelvemonth
from #periodscount
insert into graph (age_of_debt, number_of_tenancies)
select '12 to 24 months', #periodscount.twelvetotwentyfourmonth
from #periodscount
insert into graph (age_of_debt, number_of_tenancies)
select '24 to 36 months', #periodscount.twentyfourtotirtysixmonth
from #periodscount
insert into graph (age_of_debt, number_of_tenancies)
select 'Over 36 months', #periodscount.overthirtysixmonth
from #periodscount

update graph
set period_end_date = @4thperiodenddate

--select * from #periodscount
select *from graph

drop table #temptable
drop table #periodscount

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-07 : 14:53:58

does you table have the data for the week?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jamesrah
Starting Member

13 Posts

Posted - 2010-05-10 : 04:43:15

Hi,

Thanks for replying, yes their is data in the tables.

Kind Regards,
James

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-05-10 : 14:25:49

whats the purpose of other table you use in join?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jamesrah
Starting Member

13 Posts

Posted - 2010-05-19 : 05:07:56

Hi,

The rntcal & mis_tncy table hold debt data and customer info.

perenddate - period_end_date
tncynumber - customer number

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources