Hi everyone,
I am working on sql and I have a running total newRT. I need help with whenever the newRT is > 0 then create new column and add 10 and add zeros unitll it has reached 1000. When newRT has reached 1001 add 10 and add 0 until it has reached 2000. It should repeat the process until newRT has 0 left. I have used case and window function for running total.
the original data
I think you are on the right way, when you create a CTE of your original query you can continue with the result:
WITH RunningTotal AS
(
-- YOUR QUERY HERE ---
) SELECT org, newRT, CASE WHEN newRT > 0 THEN etc...
FROM RunningTotal
Pretty sure you can fill in the rest of it. If not, please provide a test case so I can help you further.
You showed the input... what should the output look like for that exact same data? I ask because your description doesn't make sense to me.
Hi ,
table 1
table 2
table 3
table 4
I have these 3 tables. Whenever I create joins between these tables it returns 20 results(one-to-many). i want it to return results as shown in table 4.
whenever a customer places an order they get 1000 pen free and any after that they get charged 8.57 per 100. I can use a case statement to find if table1.items > table3.items - table3.items(this is just a brief) and can apply the cost.
Is there away I can avoid duplicates. I have tried inner join(min (t3_id)). I want to achieve same result as table 4.
I first don't understand why the running total is offset by one row from what a running total normally represents. I'm also not seeing anything about the t2_ID in Table 1 to provide a join to anything else.
i am planning to apply free cost (Price_per_Item)on when running_sum =0. that will be the starting point.
the link is t1_id is exact same as t_2ID. i just picked random ids from all tables. t1_ID = t2_ID.
