Help With a sql query in a view

Do you mean just include them in the WHERE clause to filter on them, or add them to the CASE?  If the CASE, then what should the results be for those combinations?

---

»bp

If you are just wanting to add to the CASE statement, then something like this (you already had this one "FY CD = 2018 Rate Type = T"):

SELECT
  PROJ_ID,
  ACCT_ID,
  FY_CD,
  PD_NO,
  POOL_NO,
  POOL_NAME,
  RATE_TYPE,
  PTD_INCUR_AMT,
  CASE
    WHEN RATE_TYPE = 'T' OR RATE_TYPE = 'A' THEN '5. INDIRECT'
    ELSE (CASE
        WHEN ACCT_ID LIKE '%-60-100' THEN '2. TEMP LABOR'
        WHEN ACCT_ID LIKE '%-60-150' THEN '2. TEMP LABOR'
        WHEN ACCT_ID LIKE '%-10-%' THEN '1. LABOR'
        WHEN ACCT_ID LIKE '%-40-%' THEN '3. MATERIAL'
        ELSE '4. ODC'
      END)
  END AS CostGroup,
  LEFT(PROJ_ID, 10) AS ShortName,
  SUB_TOT_TYPE_NO AS SubTotTypeNo,
  CASE
    WHEN FY_CD = 2018 AND RATE_TYPE = 'T' THEN PTD_INCUR_AMT
    WHEN FY_CD < 2018 AND RATE_TYPE = 'A' THEN PTD_INCUR_AMT
    WHEN FY_CD < 2017 AND RATE_TYPE = 'A' THEN PTD_INCUR_AMT
    WHEN FY_CD = 2017 AND RATE_TYPE = 'T' THEN PTD_INCUR_AMT
    WHEN RATE_TYPE = 'N' THEN PTD_INCUR_AMT
  END AS TotalAmt,
  YEAR(GETDATE()) AS CurrentFY
FROM DELTEKCP.DELTEK.PSR_FINAL_DATA
WHERE (SUB_TOT_TYPE_NO <> 1)

Select allOpen in new window

---

»bp

My apologies if I wasn't specific enough. Basically the original sql I posted gives result totals based on selecting parameters such as month (PD_NO), Year (FY_CD) and Rate_type (A) for actuals.

Instead of reporting on the "actuals", I now want to report on "Target" RATE_TYPE = 'T'. However, for any FY_CD < 2016 still needs to report on the "Actuals" and not the "Target".

So if I run my old statement on "Target" for December on a specific project I get a total of 3,090.87. When I run the new code you provided I get a total of $6,181.74. It looks like its doubling. Could this be somehow its adding the PTD_INCUR_AMT and the TotalAmt?

Sorry, not following what you are trying to do.

For one thing you mention getting a "total", but I don't see any totaling going on in your query, just line by line dtata.

---

»bp

Bill, sorry for the poor explanation. Let me try one last time. I'll try not to write a novel. I have an excel spread sheet that I inherited. Within that sheet contains a macro. When running the macro it kicks off the code in my original post. The original code produces output with one of the key factors being Rate_Type. The original code is producing data based of the rate_type being "A" which stands for actual. What I need to happen per my post is I need to produce the out put to where that rate_type is now "T" which stands for target. However, anything where FY_CD is <= 2016 has to report back as "A" (actual). I took your code and modified and came up with this.

SELECT        PROJ_ID, ACCT_ID, FY_CD, PD_NO, POOL_NO, SUB_TOT_TYPE_NO, ACCT_ID AS Expr1, POOL_NAME, RATE_TYPE, 0 AS Expr2, CASE WHEN (SUB_TOT_TYPE_NO IN (4, 5)) 
                         THEN '5. INDIRECT' ELSE (CASE WHEN ACCT_ID LIKE '%-60-100' THEN '2. TEMP LABOR' WHEN ACCT_ID LIKE '%-60-150' THEN '2. TEMP LABOR' WHEN ACCT_ID LIKE '%-10-%' THEN '1. LABOR' WHEN ACCT_ID LIKE
                          '%-40-%' THEN '3. MATERIAL' ELSE '4. ODC' END) END AS CostGroup, LEFT(PROJ_ID, 10) AS ShortName, SUB_TOT_TYPE_NO AS SubTotTypeNo, 
                         CASE WHEN RATE_TYPE = 'N' THEN PTD_INCUR_AMT WHEN FY_CD = '2018' AND RATE_TYPE = 'T' THEN PTD_INCUR_AMT WHEN FY_CD < '2018' AND 
                         RATE_TYPE = 'A' THEN PTD_INCUR_AMT END AS TotalAmt, YEAR(GETDATE()) AS CurrentFY
FROM            DELTEKCP.DELTEK.PSR_FINAL_DATA
WHERE        (SUB_TOT_TYPE_NO <> 1) AND (PTD_INCUR_AMT <> 0)

Select allOpen in new window

I have a report from my erp system that allows me to check against the final output totals versus the spread sheet totals and it appears that for 2017 I am still getting output based on the "Actual" and not the "Target". I have attached a screen shot of test data to show you the comparison between the erp systems psr report and my excel spread sheet. So what I can't figure out is how to report on the "T" for 2017 and beyond. I think I'm close.
ScreenShot_Issue.docx

Great, glad we got to something useful, thanks for the feedback.

---

»bp