New answers tagged

0

Let me preface this answer by stating that I don't know how well this solution would scale, but it does work. You can retrieve the results you're after by selecting the StateId = 4 rows only and using CROSS APPLY and a subquery to select the StateId = 3 or 7 rows that are after the StateId = 4 rows. Code: SELECT s1.Name, MIN(s1.LogTime) AS DateTime, -- ...


0

WITH cte AS ( SELECT *, MIN(CASE WHEN stateid IN (3,7) THEN datetime END) OVER ( PARTITION BY name ORDER BY datetime ASC ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) as datetimestop FROM sourcetable ) SELECT name, datetime, datetimestop, ...


2

Kathi Kellenberger did a comparison of FIFO costing for Redgate in 2010. I used the winning entry method to calculate monthly inventory quantities and values for our Mexico operations. You can search for Set based speed PHreakery if this link does not work. https://www.red-gate.com/simple-talk/sql/performance/set-based-speed-phreakery-the-fifo-stock-...


0

you can probably use a group by: select item, sum(qtyrct * rctcostperitem) as costOHB group by item


1

After looking at custom aggregate functions, I realized that was the appropriate way to solve the issue. In case anyone else comes across this question and would like to see how I solved it: CREATE OR REPLACE FUNCTION sfunc_outstanding_amount_with_carry ( previous_row NUMERIC[] , -- previous row output current_row NUMERIC[] -- current ...


0

This is what I intended to say in my comment: select ctr_id, mth, cumulative_pmt_amt , coalesce(cumulative_amt, 0) as cumulative_amt , coalesce(cumulative_amt, 0) - cumulative_pmt_amt as balance from ( select ctr_id, mth , sum(pmt_amt) over (partition by ctr_id order by mth) as cumulative_pmt_amt ...


Top 50 recent answers are included