MSSQL Query Help for Report

Hello,
This is a follow up question to a previous question I got some GREAT help with linked below:

http://www.experts-exchange.com/questions/28665684/MSSQL-grouping-and-ordering.html#a40846979 

It seems we need help with grouping and sorting in order to get the report to display properly.
Attached is a sample of the report format we need along with the SQL to build the table, sample data and SQL query with pivot that currently works minus the grouping and sorting we need to match the report.

Any help you could provide is much appreciated.
thanks

Format we are trying to achieve:

SQL to build table: RE-Qry.txt

Pivot Query we are current using (unable to sort and group properly): RE-CurrentQry.txt

Sample Data in Excel: dbo-Capital-Projects-tbl.xlsx
midhelpdeskAsked:
Who is Participating?
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.

midhelpdeskAuthor Commented:
Here is how the report needs to look with proper grouping and sorting: ReportGroupandSort.PNG
0
PortletPaulfreelancerCommented:
Please note that whilst SQL can do a great deal here, it is not responsible for the final layout particularly the "grouping". That will be performed by ColdFusion in your case I believe.

I believe the "trick" to this solution will be to include more columns into the SQL result than you will see in the final report.  In particular I think you want the minimum of Proposed_Fiscal_Year as a column you can order by.

nb: I can only help with the SQL, the ColdFusion I will leave to others:

SELECT
      Project_Priority_Type
    , Project_name
    , MinFiscalYear
    , Priority_Rank
    , Total_Cost_Overall
    , [2015/2016]
    , [2016/2017]
    , Total_Reimbursable_Amt
FROM (
      SELECT
            Project_Priority_Type
          , Project_name
          , Priority_Rank
          , MIN(Proposed_Fiscal_Year) OVER(PARTITION BY Project_Priority_Type, Project_name) AS MinFiscalYear
          , Proposed_Fiscal_Year
          , SUM(Labor_Amt + Contracted_Services_Amt + Consultant_Services_Amt + Other_Costs_Amt + Equipment_Amt + Equipment_Rental_Amt + Materials_Amt) OVER (PARTITION BY Project_name) AS Total_Cost_Overall
          , Labor_Amt + Contracted_Services_Amt + Consultant_Services_Amt + Other_Costs_Amt + Equipment_Amt + Equipment_Rental_Amt + Materials_Amt AS Total_Cost_Project
          , SUM(Reimbursable_Amt) OVER (PARTITION BY Project_name) AS Total_Reimbursable_Amt
      FROM Capital_Projects_tbl
--- add your WHERE filters here 
) AS src
PIVOT
(
SUM(Total_Cost_Project)
FOR Proposed_Fiscal_Year IN ([2015/2016], [2016/2017])
) AS PivotTable
ORDER BY
      Project_Priority_Type
    , MinFiscalYear
    , Project_name
;

Open in new window


{+edit}
and when it comes to the ColdFusion don't output that MinFiscalYear column (and any others you don't need to see)
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
_agx_Commented:
I'm heading out, but I think the query above should give you the desired sql results.  Then you can do the grouping in CF as Paul suggested.  Something along these lines.


<cfoutput query="yourQuery" group="Project_Priority_Type">
      <!--- this value will only print once per group --->  
     #yourQuery["Project_Priority_Type"][CurrentRow]#  

      <!--- print all projects with the current priority type --->
      <cfoutput>
     #yourQuery["Priority_Rank"][CurrentRow]#  
     #yourQuery["Project_Name"][CurrentRow]#  
     #yourQuery["Total_Cost_Overall"][CurrentRow]#  
     #yourQuery["2015/2016"][CurrentRow]#  
     ... other columns .....
     #yourQuery["Total_Reimbursable_Amt"][CurrentRow]#  
      </cfoutput>

</cfoutput>

Open in new window



A few important things about using <cfoutput group....>

a) notice the code contains multiple "cfoutput" tags. that's intentional and not a typo
b) the "group" feature requires sorted results.  Since you want to group the results by the "Project_Priority_Type",  the query results must be ordered by that column 1st (as in Paul's example above).  As long as you sort by that column 1st, the remainder of the ORDER BY can be whatever you wish ...
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

midhelpdeskAuthor Commented:
This is fantastic! It seems to be doing the trick.

While I have your attention, is there a way to SUM the totals for all years beyond 2018/2019?

i.e.   [2020/2021], [2021/2022], [2022/2023], [2023/2024], [2024/2025] would be totaled up if its not null??

Capture.PNG
Thanks!!!!!!!
-jes
0
_agx_Commented:
One option is to use a CASE statement to modify the fiscal year value. B Basically have it return 'Beyond 2019' if the value is not in the list of years you want to display.  Something like this

SELECT
      Project_Priority_Type
    , Project_name
    , MinFiscalYear
    , Priority_Rank
    , Total_Cost_Overall
    , [2015/2016]
    , [2016/2017]
    , [Beyond 2019]
    , Total_Reimbursable_Amt
FROM (
      SELECT
            Project_Priority_Type
          , Project_name
          , Priority_Rank
          , CASE 
              WHEN Proposed_Fiscal_Year IN ('2015/2016','2016/2017') THEN Proposed_Fiscal_Year
              ELSE 'Beyond 2019'
           END AS Proposed_Fiscal_Year

          , MIN(Proposed_Fiscal_Year) OVER(PARTITION BY Project_Priority_Type, Project_name) AS MinFiscalYear
          , SUM(Labor_Amt + Contracted_Services_Amt + Consultant_Services_Amt + Other_Costs_Amt + Equipment_Amt + Equipment_Rental_Amt + Materials_Amt) OVER (PARTITION BY Project_name) AS Total_Cost_Overall
          , Labor_Amt + Contracted_Services_Amt + Consultant_Services_Amt + Other_Costs_Amt + Equipment_Amt + Equipment_Rental_Amt + Materials_Amt AS Total_Cost_Project
          , SUM(Reimbursable_Amt) OVER (PARTITION BY Project_name) AS Total_Reimbursable_Amt
      FROM Capital_Projects_tbl
--- add your WHERE filters here 
) AS src
PIVOT
(
SUM(Total_Cost_Project)
FOR Proposed_Fiscal_Year IN ([2015/2016], [2016/2017], [Beyond 2019])
) AS PivotTable
ORDER BY
      Project_Priority_Type
    , MinFiscalYear
    , Project_name
;

Open in new window

0
midhelpdeskAuthor Commented:
What a TREMENDOUS amount of help you have been! I appreciate it so much I wish I could buy you a beer or 12!!

Capture.PNG
Thank you thank you thank you!
-jes
0
_agx_Commented:
You are welcome. Glad it helped :)
0
midhelpdeskAuthor Commented:
It is fantastic! Your help has motivated me want to learn more about the sheer power of SQL Queries.
After some 17 years of hacking at SQL, I will for sure be taking an official SQL training class this year.

Thank you!
0
_agx_Commented:
That is a great idea.  I think you'll love it.  Personally I think every web app developer should have database experience too.  While you certainly can do a LOT with languages like CF/PHP/etc.., it doesn't mean you always should.  When you're not comfortable with SQL, you tend to end up with a lot of complex or convoluted code in the application level.  It gets the job done, but usually not very cleanly or efficiently ;-).

When it comes to manipulating data, a database blows an application server out of the water 99% of the time. Unlike app servers, databases are specifically optimized for the task of manipulating data. Plus they provide a lot of great tools like bulk importing, CTE's, views, stored procedures, user defined functions, etc... It is amazing how much simpler and more efficient certain tasks are with the right tool. Bottom line, if a job involves data - it almost certainly belongs in the database ;-)

(... steps down off SQL soapbox ;-)
0
midhelpdeskAuthor Commented:
"It gets the job done, but usually not very cleanly or efficiently..." -- Preach brother, PREACH! lol

After working thru this issue, I have seen the light and will forever strive to do all I can on the dB side rather then the application level.
0
_agx_Commented:
Haha! Welcome new member of the SQL congregation ;-)

Honestly, like everything it is a balance. There are some things CF does better, like complex string functions.  But others, well ... let us just say every time I see someone importing a 200K row text file by looping, line-by-line, with CF, instead of using a db import tool, it makes me want to send them to SQL boot camp for reprogramming ;-)

Well back to work. Later!
0
midhelpdeskAuthor Commented:
Thank you!!
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
Query Syntax

From novice to tech pro — start learning today.

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.