October 21, 2009 at 11:31 am
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,
October 23, 2009 at 1:20 pm
Thanks all for your help . I have already solved the problem .
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply