Stored procedure with as of dates

Question

Post reply

Stored procedure with as of dates

PSB

SSChampion

Points: 13195
More actions
October 21, 2009 at 11:31 am

#385622

I have a table named xyz that has data stored with different As_Of_Dates like 23-Apr,21-May,25-Jun,23-Jul,20-Aug,24-Sep

For example If I select Present date as '9/24/2009' then

I want to get in the resultset data that didnot expire (Purchased> Used) for the previous as_of_dates as well as data that expired (Purchased = Used) and did not expire (Purchased > Used ) in the present as_Of_date '9/24/2009' .

Also if the same account existed in the previous as_of_dates with (used = 3000 and Purchased = 10000) as well as the Present date (used = 1000 ) then the new balance should be 6000 and used should be shown as 1000 only .

But if unexpired accounts from previous asofdates did not appear in the current asofdate then I donot want to leave them as it is .

Below is my SP

--sp_GetUsageData '9/24/2009'

alter procedure sp_GetUsageData

(

@PresentDate DateTime

)

as

BEGIN

with cte1 as

(

select AccountID,AccountName,CampaignId,CampaignStartDate,[Type],Purchased,Cost,Used,Balance,BannerRevenue, as_of_date,RegionName

from dbo.RPT_UsageReport UR where As_Of_Date <= @PresentDate AND UR.Purchased > UR.Used

), cte2 as

(

SELECT AccountID,AccountName,CampaignId,CampaignStartDate,[Type],Purchased,Cost,Used,Balance,BannerRevenue, as_of_date,RegionName

from dbo.RPT_UsageReport where As_Of_Date = @PresentDate --and AccountID In (Select AccountID from dbo.RPT_UsageReport where As_Of_Date < @PresentDate )

)

select cte1.AccountID,cte1.AccountName,cte1.CampaignId,cte1.CampaignStartDate,cte1.[Type],

cte1.Purchased,cte1.Cost,cte1.Used,cte1.Balance,cte1.BannerRevenue, cte1.as_of_date,cte1.RegionName

from cte1

LEFT JOIN cte2 on cte1.accountid = cte2.accountid

where cte1.AccountID not in ( select distinct cte2.AccountID from cte2 )

UNION

select cte1.AccountID,cte1.AccountName,cte1.CampaignId,cte1.CampaignStartDate,cte1.[Type],

cte1.Purchased,cte1.Cost,cte2.Used,cte1.Balance-cte1.Used-cte2.Used as Balance,cte1.BannerRevenue, cte1.as_of_date,cte1.RegionName

from cte1

LEFT JOIN cte2 on cte1.accountid = cte2.accountid

where cte1.AccountID in ( select distinct cte2.AccountID from cte2 )

END

My Query is pulling correct results for previous unexpired accounts from previous asofdates but for the current asofdate it is not showing the ones that expired and the balance is wrongly calculated .

Thanks,