SUMIF greater than and Workday column

Question

So I'm trying to convert an Excel table into SQL and I'm having difficulty coming up with the last 2 columns. Below, find my Excel table that is fully functional (in green) and a table for the code that I have in SQL so far (in yellow). I need help replicating columns C and D, I pasted the Excel formula I'm using so you can understand what I'm trying to do:

Here's the code that I have so far:

WITH
    cte_DistinctScheduling AS (
        SELECT DISTINCT 
            s.JobNo
        FROM 
            dbo.Scheduling s
        WHERE 
        s.WorkCntr = 'Framing')
SELECT 
  o.OrderNo,
  o.Priority AS [P],
  SUM(r.TotEstHrs)/ROUND((8*w.CapacityFactor*(w.UtilizationPct/100)),2) AS 
  [Work Days Left],
  Cast(GetDate()+ROUND(SUM(r.TotEstHrs)/ROUND((8*w.CapacityFactor*
(w.UtilizationPct/100)),2),3) AS DATE) AS DueDate
FROM OrderDet o JOIN cte_DistinctScheduling ds ON o.JobNo = ds.JobNo
  JOIN OrderRouting r ON o.JobNo = r.JobNo
  JOIN WorkCntr w ON r.WorkCntr = w.ShortName
WHERE r.WorkCntr = 'Framing'
  AND o.OrderNo NOT IN ('44444', '77777')
GROUP BY o.OrderNo, o.Priority, ROUND((8*w.CapacityFactor*
(w.UtilizationPct/100)),2)
ORDER BY o.Priority DESC;

My work days left column in SQL gets the right amount for that particular row, but I need it to sum itself and everything with a P value above it and then add that to today's date, while taking workdays into account. I don't see a Workday function in SQL from what I've been reading, so I'm wondering what are some creative solutions? Could perhaps a CASE statement be the answer to both of my questions? Thanks in advance

There is not enough information here. You need to provide some sample data, preferably using SQL Fiddle (http://sqlfiddle.com/) — Alex, Jul 17 '17 at 22:24

score 1 · Answer 1 · answered Jul 18 '17 at 08:21

Took me a while to understand how is the Excel helpful, and I'm still having a hard time absorbing the rest, can't tell if it's a me thing or a you thing, in any case...

First, I've mocked up something to test SUM per your rationale, the idea is doing a self-JOIN and summing everything from that JOIN side, relying on the fact that NULLs will come up for anything that shouldn't be summed:

DECLARE @TABLE TABLE(P int, [Value] int)
INSERT INTO @TABLE SELECT 1, 5
INSERT INTO @TABLE SELECT 2, 6
INSERT INTO @TABLE SELECT 3, 2
INSERT INTO @TABLE SELECT 4, 4
INSERT INTO @TABLE SELECT 5, 9

SELECT T1.P, [SUM] = SUM(ISNULL(T2.[Value], 0))
FROM @TABLE AS T1
LEFT JOIN @TABLE AS T2 ON T2.P <= T1.P
GROUP BY T1.P
ORDER BY P DESC

Second, workdays is a topic that comes up regularly. In case you didn't, consider reading a little about it from previous questions, I even posted an answer on one question last week, and the thread as a whole had several references.

Thirdly, we could use table definitions and sample data loaded on SQL itself, something like I did above.

Lastly, could you please check result of UtilizationPct / 100? If that's an integer-like data type, you're probably getting a bad result on it.

SUMIF greater than and Workday column

1 Answers1