November 26, 2014 at 3:03 pm
GrassHopper (11/26/2014)
The answer to your question is that when the Running total is greater than the Volume total (60), instead of entering in the running total amount, you calculate the total volume minus the previous running total and then put the value in that cell. i.e 60 - 59.98 = .02That is how I got the .02. Also, if there is a record below that one, you would stop calculating running total and ToFrom and put Null values as a value for those 2 columns. for the rest of the records in that groupid. (see my example I posted with better explanation).
Yes... as I stated, I got all of that the first time around. What I'm questioning is why you're doing that. It doesn't seem right for the reasons previously mentioned.
You also appear to be missing a couple of pieces in your latest JPG.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2014 at 3:21 pm
To be honest I don't know why that is being calculated like that. I was given these instructions and this is how the data needs to be displayed for the end user. I just know it is a business requirement for the business process.
November 26, 2014 at 4:07 pm
You might want to tell them of the mistake I think they're making.
Back to the issue at hand, where does the "60" limit come from in your original JPG and why isn't that information in the sample data you were good enough to post. Better yet, what should it be for each group in your sample data?
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2014 at 4:09 pm
Check the post I made with the desired results.jpg It is explained there and shows where the volume comes from.
November 26, 2014 at 8:25 pm
GrassHopper (11/26/2014)
Check the post I made with the desired results.jpg It is explained there and shows where the volume comes from.
Ah... got it. Without the penny per row mistake, it would make no sense (or is that "cents" :-P). Now I've got something to go on. I'll see what I can do over the weekend.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 26, 2014 at 9:38 pm
Thanks all for looking at this to help me. I have been playing, trying to figure it out as well. I came up with this query. It takes like 20 seconds to run but the data appears to be correct. I"m examining the results better, but if it is not it I think I am close. I think it could be more effecient though.
;With deals
AS
(Select groupid, [line number],[TO], [From]
From [PRGX_AS_CC_SandBox_Rebates_2013].[dbo].[DealEntry_Rebate_ClaimTank]
Group by GroupID, [Line Number],[to],[from]
)
,deals2
AS
(Select groupid, [line number],[TO], [From]
From [PRGX_AS_CC_SandBox_Rebates_2013].[dbo].[DealEntry_Rebate_ClaimTank]
Group by GroupID, [Line Number],[to],[from]
)
,VolGroup
AS
(Select SUM(Calc_line_net_amount) as volume,GroupID,[Include]
from [PRGX_AS_CC_SandBox_Rebates_2013].[dbo].ClaimTank
Group by GroupID, [Include]
Having Include <> 0
--order by GroupID
)
,dealsRSum
AS
(Select d2.groupid, d2.[line number],d2.[TO]-d2.[From] as ToFrom, VG.volume
From deals2 as d2
Left Join VolGroup as VG
ON d2.GroupID = VG.GroupID
)
,dealsRSum2
AS
(Select d2.groupid, d2.[line number],d2.[TO]-d2.[From] as ToFrom, VG.volume
From deals2 as d2
Left Join VolGroup as VG
ON d2.GroupID = VG.GroupID
)
Select d2.groupid, d2.[line number], d2.Volume, ds.[To], ds.[From]
, Case
When d2.ToFrom < d2.volume
Then d2.ToFrom
Else d2.volume - d1.ToFrom
END as ToFrom
From dealsRSum2 as d2
Inner Join dealsRSum as d1
ON d1.GroupID = d2.GroupID
AND d1.[Line Number] =+ d2.[Line Number]
Left Join VolGroup as VG
ON d1.GroupID = VG.GroupID
Inner Join deals as ds
ON ds.GroupID = d2.GroupID
AND ds.[Line Number] = d2.[Line Number]
Order by d1.GroupID, d1.[Line Number]
December 1, 2014 at 6:56 pm
Anyone think they can improve on this query?
December 1, 2014 at 7:25 pm
GrassHopper (12/1/2014)
Anyone think they can improve on this query?
Almost no question. The key here is that your code now has two real tables in it. For my stuff to work, you'll need to post the code necessary to not only create the Temp Table like you did before, but also write the code that joins the two tables to populate it. Don't spend any time on trying to do aggregates. Just make it so that it populates the temp table with flat data like you did in your example. Post that query and then we can get you down to some probably much quicker code.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply