Calculate in join in sql
I am trying to achieve staggered calculation in joins in sql 2008. I can have n number of rows for 1 job id. I created a sample below
CREATE TABLE Job
(
JobID INT NOT NULL,
Amount INT NOT NULL
);
INSERT INTO Job (JobID, Amount)
VALUES (1, 25),
(1, 45),
(1, 40),
(2, 25),
(3, 26),
(3, 26);
now the discount for JobID = 1
is 80, so what I expect in the query result output is below:
If Amount > Discount
, then show, finalvalue = Amount - Discount
but if Amount < Discount
, then show Finalvalue = Amount - Amount
, and if Discount
still remains, subtract the same from the following lines.
Job ID Amount FinalValue
1 25 0
1 45 0
1 40 30
Can all this be done in conjunction?
source to share
Here you are:
EDIT: WARNING: you must add a column to sort. My approach is partitioning and sorting by JobID, which makes the output random ...
EDIT: Sorry, didn't add tables ...
CREATE TABLE Job
(
JobID INT NOT NULL,
Amount INT NOT NULL
);
INSERT INTO Job (JobID, Amount)
VALUES (1, 25), (1, 45), (1, 40), (2, 25), (3, 26), (3, 26);
CREATE TABLE Discount
(
JobID INT NOT NULL,
Discount INT NOT NULL
);
INSERT INTO Discount(JobID,Discount)VALUES(1,80),(2,0),(3,10);
WITH myCTE AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY Job.JobID ORDER BY Job.JobID) AS inx
,Job.JobID
,Job.Amount
,Discount.Discount
FROM Job
INNER JOIN Discount ON Job.JobID=Discount.JobID
)
SELECT * FROM myCTE
CROSS APPLY
(
SELECT SUM(x.Amount)
FROM myCTE AS x
WHERE x.JobID=myCTE.JobID
AND x.inx<=myCTE.inx
) AS AmountCummulativ(AmountCummulativ)
CROSS APPLY(SELECT AmountCummulativ-myCTE.Discount) AS DiscountCalculated(DiscountCalculated)
CROSS APPLY(SELECT CASE WHEN DiscountCalculated<0 THEN 0 ELSE DiscountCalculated END) AS DiscountResolved(DiscountResolved)
Hope it helps
source to share
I think what you are looking for can be accomplished using case case
select a.jobid,a.Amount,case when a.amount > b.discount then a.amount - b.discount else 0 end final_value
from Job a inner join Job_discount b to a.jobid = b.jobid
You can see the results here http://sqlfiddle.com/#!3/f9a46/1
I had to accept the structure of the discount table
source to share
I added some sequence (row number) to Job table (JobOrder call) to increase the amounts. You can change the order like now for JobId, Amount!
With JobOrder as (
-- Job order by id and amount
select row_number() over (order by JobID, Amount asc) as rowno, JobId, Amount from Job
),
JobSumIncr as (
select
JobID,
Amount,
(select sum(Amount)
from JobOrder j2
where j2.JobID = j.JobID and j2.RowNo <= j.RowNo
) as AmountTotal
from JobOrder j
)
select
j.JobID,
j.Amount,
j.AmountTotal,
d.Discount,
(case when d.Discount>=j.AmountTotal then 0 else j.AmountTotal-d.Discount end) as FinalValue
from
JobSumIncr j left join Discount d on j.JobID = d.JobID;
Assuming your discount table looks something like this:
CREATE TABLE Discount (
JobID INT,
Discount INT
);
SqlFiddle is here! and for safer Sql (checking for null values and looking at discount on the left) see this SQLFiddle too .
For the version that tracks the remaining discount, see sqlfiddle-2 above.
source to share