Help With a sql query in a view

I need some help with a sql query. I have the following query the runs correctly.

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 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)

Open in new window

What I can't figure out (newbie) is how to also include records on top of the above query where FY_CD < 2017 Rate Type = A, and FY CD = 2017 Rate Type = T and FY CD = 2018 Rate Type = T.
DJ PAsked:
Who is Participating?
 
Bill PrewCommented:
That helped some, but still a little hard for me to fully understand.  Not seeing the Excel part makes it a little harder.

But, based on this:

  • anything where FY_CD is <= 2016 has to report back as "A" (actual)
  • how to report on the "T" for 2017 and beyond
I would suggest trying 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 > '2016' AND RATE_TYPE = 'T' THEN PTD_INCUR_AMT
    WHEN FY_CD < '2017' 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)

Open in new window


»bp
0
 
Bill PrewCommented:
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
0
 
Bill PrewCommented:
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)

Open in new window


»bp
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
DJ PAuthor Commented:
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?
0
 
Bill PrewCommented:
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
0
 
DJ PAuthor Commented:
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)

Open 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
0
 
DJ PAuthor Commented:
Bill, I got the following working. Your help with your code showed me the way.

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 IN ('2018', '2017') AND RATE_TYPE = 'T' THEN PTD_INCUR_AMT WHEN FY_CD < '2017' 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)
0
 
Bill PrewCommented:
Great, glad we got to something useful, thanks for the feedback.


»bp
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.