October 21, 2009 at 10:17 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 22, 2009 at 7:35 pm
You are much more likely to get an answer to this if you provide sample data and expected results. The article in my signature explains how to do this in a manner that is useful to us.
October 28, 2009 at 9:07 pm
Agree I must with Seth. Table DDL, sample data, expected results would greatly help. Plus, tested code you will receive in return.
October 29, 2009 at 7:10 am
Data stored as 23-Apr, without a year are not a date datatype so they will not match a true datetime datatype.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply