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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewIT / Software Engineering ConsultantCommented:
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 PrewIT / Software Engineering ConsultantCommented:
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
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
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Bill PrewIT / Software Engineering ConsultantCommented:
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
Bill PrewIT / Software Engineering ConsultantCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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 PrewIT / Software Engineering ConsultantCommented:
Great, glad we got to something useful, thanks for the feedback.


»bp
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.