MSSQL grouping and ordering

Hello,
I have a query I could use some help with... My issue is grouping / ordering / formatting (I think).

The question is, how do I get the results of my query to "group" on the same line?  If you view attachment 1  you will see the highlighted items is displayed on two lines, I need to get them to display on one line as one Item.

attachment 1

Attachment 2 is the column names in the database:


attachment 2

The hope this can be done with a simple group by or order by clause, but it has me jammed up at the moment.

My query is below:

<cfquery name="Projects_List" datasource="CPdB">
SELECT	ID,
		Proposed_Fiscal_Year,
        Department_Name,
        Project_Number,
        Project_Priority_Type,
        Project_Name,
        MID_Facility,
        Project_Type,
        Date_Board_Approved_Budget,
        Amount_Approved,
        Date_CPA_Approved,
        Funding_Source,
        Project_Description,
        Project_Justification,
        Project_Alternatives_Considered,
        Labor_Amt,
        Contracted_Services_Amt,
        Consultant_Services_Amt,
        Other_Costs_Amt,
        Equipment_Amt,
        Equipment_Rental_Amt,
        Materials_Amt,
        Reimbursable_Amt,
        Submitted_By,
        Submitted_Date,
        Project_Status,
        Department_Number,
        Fund_Name,
        Fund_Number,
        Reimbursable_Project,
        Emergency_Request,
        Project_Status,
        <!---Priority_Type,--->
        Priority_Rank,
		Fund_Number +' '+ Fund_Name +' '+ Department_Name AS CONCAT_Dept_Fund
FROM	Capital_Projects_tbl
<cfif isdefined("form.submitted")>
		WHERE	1=1
		<cfif #FORM.Department_Name# NEQ "Show All">
       		AND Department_Name = '#FORM.Department_Name#'
        </cfif>
        <cfif #FORM.Project_Status# NEQ "Show All">
       		AND Project_Status = '#FORM.Project_Status#'
        </cfif>        
<cfelse>
        WHERE 1=1
</cfif>
ORDER BY Proposed_Fiscal_Year ASC
</cfquery>

Open in new window

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:
I'm looking into PIVOT / UNPIVOT to see if I can get something working using this method... still stuck.
-jes
0
_agx_Commented:
> If you view attachment 1

1. Is attachment 1 just a mockup or are is that something you're currently generating via code or query? If so, what's the actual code used to produce it?

2. Using attachment 1 as an example, what's the desired output look like? Specifically which columns to you need to group by?
0
midhelpdeskAuthor Commented:
Hello,
I can attached my full page of source for the report page this is displaying on if you would like. Here is attachment 3 which shows how I need the any rows that are the same project name to "merge" together. As for the totals etc, that's other code I'm using via CFML to create form local variables.

Capture3.png
thanks
-jes
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

midhelpdeskAuthor Commented:
Below is a more clear example of how we need to have multiple matching records combined into one row:

From this:
Capture4.PNG
To this:
Capture5.PNG
0
midhelpdeskAuthor Commented:
Here is an example of the final result I need to achieve.

Capture6.PNG
0
_agx_Commented:
>  I can attached my full page of source for the report page this is displaying on if you would like.

Yes please. It's hard to tell which columns are used for in report, and what adjustments you need to make without the code.
0
PortletPaulfreelancerCommented:
The SQL portion of the query you have included in the question has NO GROUPING OR SUMMING of data at all, it it just a listing of data; and it includes many more columns than you display in the output screens.

If you expect SQL to help you solve this, the query will look more like this (and the reduced number of columns is necessary!):

select MAX(Priority) as Priority, Project_Name as Project, SUM(Cost) as [Total Cost]
from YourTable
GROUP BY Project_Name
0
_agx_Commented:
If you expect SQL to help you solve this

... which is the better way to approach it.  While it could be done in CF, it is more efficient to do the aggregation on the db side.  That said, we'd still need the column names involved to provide more specifics.
0
midhelpdeskAuthor Commented:
I would love to do it in SQL and out put results using CFML. Below is the complete page that that I am using as well as the SQL to build the main Capital_Projects_tbl.

Thanks for the help!!! I'd love to see how this is done in SQL. I'm sure my multiple queries is going to look childish to you all.. lol
-jes

<!--- SQL --->
USE [CPdB]
GO

/****** Object:  Table [dbo].[Capital_Projects_tbl]    Script Date: 05/01/2015 08:34:00 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[Capital_Projects_tbl](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[Proposed_Fiscal_Year] [nvarchar](50) NULL,
	[Department_Name] [nvarchar](50) NULL,
	[Project_Number] [nchar](50) NULL,
	[Project_Priority_Type] [nvarchar](50) NULL,
	[Project_Name] [nvarchar](max) NULL,
	[MID_Facility] [nvarchar](50) NULL,
	[Project_Type] [nvarchar](50) NULL,
	[Date_Board_Approved_Budget] [date] NULL,
	[Amount_Approved] [decimal](18, 2) NULL,
	[Date_CPA_Approved] [date] NULL,
	[Funding_Source] [nvarchar](50) NULL,
	[Project_Description] [varchar](max) NULL,
	[Project_Justification] [varchar](max) NULL,
	[Project_Alternatives_Considered] [varchar](max) NULL,
	[Labor_Amt] [decimal](18, 2) NULL,
	[Contracted_Services_Amt] [decimal](18, 2) NULL,
	[Consultant_Services_Amt] [decimal](18, 2) NULL,
	[Other_Costs_Amt] [decimal](18, 2) NULL,
	[Equipment_Amt] [decimal](18, 2) NULL,
	[Equipment_Rental_Amt] [decimal](18, 2) NULL,
	[Materials_Amt] [decimal](18, 2) NULL,
	[Reimbursable_Amt] [decimal](18, 2) NULL,
	[Submitted_By] [nvarchar](50) NULL,
	[Submitted_Date] [date] NULL,
	[Project_Status] [nvarchar](50) NULL,
	[Department_Number] [nvarchar](50) NULL,
	[Fund_Name] [nvarchar](50) NULL,
	[Fund_Number] [nvarchar](50) NULL,
	[Reimbursable_Project] [nvarchar](50) NULL,
	[Emergency_Request] [nvarchar](50) NULL,
	[Priority_Rank] [nchar](10) NULL,
 CONSTRAINT [PK_Capital_Projects_tbl] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Capital_Projects_tbl] ADD  CONSTRAINT [DF_Capital_Projects_tbl_Amount_Approved]  DEFAULT ((0.00)) FOR [Amount_Approved]
GO

ALTER TABLE [dbo].[Capital_Projects_tbl] ADD  CONSTRAINT [DF_Capital_Projects_tbl_Labor_Amt]  DEFAULT ((0.00)) FOR [Labor_Amt]
GO

ALTER TABLE [dbo].[Capital_Projects_tbl] ADD  CONSTRAINT [DF_Capital_Projects_tbl_Contracted_Services_Amt]  DEFAULT ((0.00)) FOR [Contracted_Services_Amt]
GO

ALTER TABLE [dbo].[Capital_Projects_tbl] ADD  CONSTRAINT [DF_Capital_Projects_tbl_Consultant_Services_Amt]  DEFAULT ((0.00)) FOR [Consultant_Services_Amt]
GO

ALTER TABLE [dbo].[Capital_Projects_tbl] ADD  CONSTRAINT [DF_Capital_Projects_tbl_Other_Costs_Amt]  DEFAULT ((0.00)) FOR [Other_Costs_Amt]
GO

ALTER TABLE [dbo].[Capital_Projects_tbl] ADD  CONSTRAINT [DF_Capital_Projects_tbl_Equipment_Amt]  DEFAULT ((0.00)) FOR [Equipment_Amt]
GO

ALTER TABLE [dbo].[Capital_Projects_tbl] ADD  CONSTRAINT [DF_Capital_Projects_tbl_Equipment_Rental_Amt]  DEFAULT ((0.00)) FOR [Equipment_Rental_Amt]
GO

ALTER TABLE [dbo].[Capital_Projects_tbl] ADD  CONSTRAINT [DF_Capital_Projects_tbl_Materials_Amt]  DEFAULT ((0.00)) FOR [Materials_Amt]
GO

ALTER TABLE [dbo].[Capital_Projects_tbl] ADD  CONSTRAINT [DF_Capital_Projects_tbl_Reimbursable_Amt]  DEFAULT ((0.00)) FOR [Reimbursable_Amt]
GO


<!--- CFML--->

<!---http://datatables.net/--->
<!DOCTYPE html>
<html lang="en">
<head>
    <title></title>
    <meta charset="utf-8">
    <link rel="stylesheet" href="css/reset.css" type="text/css" media="screen">
    <link rel="stylesheet" href="css/style.css" type="text/css" media="screen">
    <link rel="stylesheet" href="css/grid.css" type="text/css" media="screen">   
    
    <script src="js/jquery-1.6.4.min.js" type="text/javascript"></script>
    <script src="js/ff_cash.js" type="text/javascript"></script>
    <script src="js/superfish.js" type="text/javascript"></script>
    <script src="js/cufon-yui.js" type="text/javascript"></script>
    <script src="js/cufon-replace.js" type="text/javascript"></script>
    <script src="js/Kozuka_Gothic_Pro_R.font.js" type="text/javascript"></script>
    <script src="js/Kozuka_Gothic_Pro_M.font.js" type="text/javascript"></script>
    <script src="js/Kozuka_Gothic_Pro_B.font.js" type="text/javascript"></script>        
    <script src="js/Kozuka_Gothic_Pro_H.font.js" type="text/javascript"></script>                
	<!--[if lt IE 7]> 
  <div style='clear:both; text-align:center; position:relative;'><a href="http://windows.microsoft.com/en-US/internet-explorer/products/ie/home?ocid=ie6_countdown_bannercode"><img                       src="http://storage.ie6countdown.com/assets/100/images/banners/warning_bar_0000_us.jpg" border="0" height="42" width="820" alt="You are using an outdated browser. For a  faster, safer browsing experience, upgrade for free today." /></a></div>
 <![endif]-->
    <!--[if lt IE 9]>
   		<script type="text/javascript" src="js/html5.js"></script>
        <link rel="stylesheet" href="css/ie.css" type="text/css" media="screen">
	<![endif]-->

	<link rel="stylesheet" type="text/css" href="media/css/jquery.dataTables.css">
	<link rel="stylesheet" type="text/css" href="resources/syntax/shCore.css">
	<!---<link rel="stylesheet" type="text/css" href="resources/demo.css">--->
	<style type="text/css" class="init">

	</style>
	<script type="text/javascript" language="javascript" src="media/js/jquery.js"></script>
	<script type="text/javascript" language="javascript" src="media/js/jquery.dataTables.js"></script>
	<script type="text/javascript" language="javascript" src="resources/syntax/shCore.js"></script>
	<!---<script type="text/javascript" language="javascript" src="resources/demo.js"></script>--->
	<script type="text/javascript" language="javascript" class="init">
$(document).ready(function() {
    $('#example').dataTable( {
        "order": [[ 3, "desc" ]]
    } );
} );

	</script>

</head>

<cfquery name="Projects_List" datasource="CPdB">
SELECT	ID,
		Proposed_Fiscal_Year,
        Department_Name,
        Project_Number,
        Project_Priority_Type,
        Project_Name,
        MID_Facility,
        Project_Type,
        Date_Board_Approved_Budget,
        Amount_Approved,
        Date_CPA_Approved,
        Funding_Source,
        Project_Description,
        Project_Justification,
        Project_Alternatives_Considered,
        Labor_Amt,
        Contracted_Services_Amt,
        Consultant_Services_Amt,
        Other_Costs_Amt,
        Equipment_Amt,
        Equipment_Rental_Amt,
        Materials_Amt,
        Reimbursable_Amt,
        Submitted_By,
        Submitted_Date,
        Project_Status,
        Department_Number,
        Fund_Name,
        Fund_Number,
        Reimbursable_Project,
        Emergency_Request,
        Project_Status,
        <!---Priority_Type,--->
        Priority_Rank,
		Fund_Number +' '+ Fund_Name +' '+ Department_Name AS CONCAT_Dept_Fund
FROM	Capital_Projects_tbl
<cfif isdefined("form.submitted")>
		WHERE	1=1
		<cfif #FORM.Department_Name# NEQ "Show All">
       		AND Department_Name = '#FORM.Department_Name#'
        </cfif>
        <cfif #FORM.Project_Status# NEQ "Show All">
       		AND Project_Status = '#FORM.Project_Status#'
        </cfif>        
<cfelse>
        WHERE 1=1
</cfif>
ORDER BY Proposed_Fiscal_Year ASC
</cfquery>

<cfquery name="Departments_Dropdown" datasource="CPdB">
SELECT	Fund_Name, 
		Fund_Number, 
        Department_Name, 
        Department_Number, 
        Fund_Number +' '+ Department_Name AS CONCAT_Dept_Fund
FROM	Departments_Dropdown_tbl
ORDER BY	Fund_Number ASC
</cfquery>

<cfquery name="Project_Status_Dropdown" datasource="CPdB">
SELECT	Project_Status
FROM	Project_Status_Dropdown_tbl
ORDER BY	Project_Status ASC
</cfquery>

<!--- Totals Queries --->
<cfquery name="Total_Cost_Sum" datasource="CPdB">
SELECT 
   SUM(Labor_Amt) as 'Labor_Amt',
   SUM(Contracted_Services_Amt) as 'Contracted_Services_Amt',
   SUM(Consultant_Services_Amt) as 'Consultant_Services_Amt',
   SUM(Other_Costs_Amt) as 'Other_Costs_Amt',
   SUM(Equipment_Amt) as 'Equipment_Amt',
   SUM(Equipment_Rental_Amt) as 'Equipment_Rental_Amt',
   SUM(Materials_Amt) as 'Materials_Amt',
   (SUM(Labor_Amt) + SUM(Contracted_Services_Amt) + SUM(Consultant_Services_Amt) + SUM(Other_Costs_Amt) + SUM(Equipment_Amt )+ SUM(Equipment_Rental_Amt) + SUM(Materials_Amt)) as 'Total_Cost_Sum'
FROM 	Capital_Projects_tbl
WHERE	1=1
<cfif isdefined("form.submitted")>
		<cfif #FORM.Department_Name# NEQ "Show All">
       		AND Department_Name = '#FORM.Department_Name#'
        </cfif>
        <cfif #FORM.Project_Status# NEQ "Show All">
       		AND Project_Status = '#FORM.Project_Status#'
        </cfif>  
</cfif>
</cfquery>

<cfquery name="Total_Cost_2014_2015_Sum" datasource="CPdB">
SELECT 
   SUM(Labor_Amt) as 'Labor_Amt',
   SUM(Contracted_Services_Amt) as 'Contracted_Services_Amt',
   SUM(Consultant_Services_Amt) as 'Consultant_Services_Amt',
   SUM(Other_Costs_Amt) as 'Other_Costs_Amt',
   SUM(Equipment_Amt) as 'Equipment_Amt',
   SUM(Equipment_Rental_Amt) as 'Equipment_Rental_Amt',
   SUM(Materials_Amt) as 'Materials_Amt',
   SUM(Reimbursable_Amt) as 'Reimbursable_Amt',
   (SUM(Labor_Amt) + SUM(Contracted_Services_Amt) + SUM(Consultant_Services_Amt) + SUM(Other_Costs_Amt) + SUM(Equipment_Amt )+ SUM(Equipment_Rental_Amt) + SUM(Materials_Amt) - SUM(Reimbursable_Amt)) as 'Total_Cost_2014_2015_Sum'
FROM 	Capital_Projects_tbl
WHERE	Proposed_Fiscal_Year = '2014/2015'
<cfif isdefined("form.submitted")>
		<cfif #FORM.Department_Name# NEQ "Show All">
       		AND Department_Name = '#FORM.Department_Name#'
        </cfif>
        <cfif #FORM.Project_Status# NEQ "Show All">
       		AND Project_Status = '#FORM.Project_Status#'
        </cfif>  
</cfif>
</cfquery>

<cfquery name="Total_Cost_2015_2016_Sum" datasource="CPdB">
SELECT 
   SUM(Labor_Amt) as 'Labor_Amt',
   SUM(Contracted_Services_Amt) as 'Contracted_Services_Amt',
   SUM(Consultant_Services_Amt) as 'Consultant_Services_Amt',
   SUM(Other_Costs_Amt) as 'Other_Costs_Amt',
   SUM(Equipment_Amt) as 'Equipment_Amt',
   SUM(Equipment_Rental_Amt) as 'Equipment_Rental_Amt',
   SUM(Materials_Amt) as 'Materials_Amt',
   SUM(Reimbursable_Amt) as 'Reimbursable_Amt',
   (SUM(Labor_Amt) + SUM(Contracted_Services_Amt) + SUM(Consultant_Services_Amt) + SUM(Other_Costs_Amt) + SUM(Equipment_Amt )+ SUM(Equipment_Rental_Amt) + SUM(Materials_Amt) - SUM(Reimbursable_Amt)) as 'Total_Cost_2015_2016_Sum'
FROM 	Capital_Projects_tbl
WHERE	Proposed_Fiscal_Year = '2015/2016'
<cfif isdefined("form.submitted")>
		<cfif #FORM.Department_Name# NEQ "Show All">
       		AND Department_Name = '#FORM.Department_Name#'
        </cfif>
        <cfif #FORM.Project_Status# NEQ "Show All">
       		AND Project_Status = '#FORM.Project_Status#'
        </cfif>  
</cfif>
</cfquery>

<cfquery name="Total_Cost_2016_2017_Sum" datasource="CPdB">
SELECT 
   SUM(Labor_Amt) as 'Labor_Amt',
   SUM(Contracted_Services_Amt) as 'Contracted_Services_Amt',
   SUM(Consultant_Services_Amt) as 'Consultant_Services_Amt',
   SUM(Other_Costs_Amt) as 'Other_Costs_Amt',
   SUM(Equipment_Amt) as 'Equipment_Amt',
   SUM(Equipment_Rental_Amt) as 'Equipment_Rental_Amt',
   SUM(Materials_Amt) as 'Materials_Amt',
   SUM(Reimbursable_Amt) as 'Reimbursable_Amt',
   (SUM(Labor_Amt) + SUM(Contracted_Services_Amt) + SUM(Consultant_Services_Amt) + SUM(Other_Costs_Amt) + SUM(Equipment_Amt )+ SUM(Equipment_Rental_Amt) + SUM(Materials_Amt) - SUM(Reimbursable_Amt)) as 'Total_Cost_2016_2017_Sum'
FROM 	Capital_Projects_tbl
WHERE	Proposed_Fiscal_Year = '2016/2017'
<cfif isdefined("form.submitted")>
		<cfif #FORM.Department_Name# NEQ "Show All">
       		AND Department_Name = '#FORM.Department_Name#'
        </cfif>
        <cfif #FORM.Project_Status# NEQ "Show All">
       		AND Project_Status = '#FORM.Project_Status#'
        </cfif> 
</cfif>
</cfquery>

<cfquery name="Total_Cost_2017_2018_Sum" datasource="CPdB">
SELECT 
   SUM(Labor_Amt) as 'Labor_Amt',
   SUM(Contracted_Services_Amt) as 'Contracted_Services_Amt',
   SUM(Consultant_Services_Amt) as 'Consultant_Services_Amt',
   SUM(Other_Costs_Amt) as 'Other_Costs_Amt',
   SUM(Equipment_Amt) as 'Equipment_Amt',
   SUM(Equipment_Rental_Amt) as 'Equipment_Rental_Amt',
   SUM(Materials_Amt) as 'Materials_Amt',
   SUM(Reimbursable_Amt) as 'Reimbursable_Amt',
   (SUM(Labor_Amt) + SUM(Contracted_Services_Amt) + SUM(Consultant_Services_Amt) + SUM(Other_Costs_Amt) + SUM(Equipment_Amt )+ SUM(Equipment_Rental_Amt) + SUM(Materials_Amt) - SUM(Reimbursable_Amt)) as 'Total_Cost_2017_2018_Sum'
FROM 	Capital_Projects_tbl
WHERE	Proposed_Fiscal_Year = '2017/2018'
<cfif isdefined("form.submitted")>
		<cfif #FORM.Department_Name# NEQ "Show All">
       		AND Department_Name = '#FORM.Department_Name#'
        </cfif>
        <cfif #FORM.Project_Status# NEQ "Show All">
       		AND Project_Status = '#FORM.Project_Status#'
        </cfif>  
</cfif>
</cfquery>

<cfquery name="Total_Cost_2018_2019_Sum" datasource="CPdB">
SELECT 
   SUM(Labor_Amt) as 'Labor_Amt',
   SUM(Contracted_Services_Amt) as 'Contracted_Services_Amt',
   SUM(Consultant_Services_Amt) as 'Consultant_Services_Amt',
   SUM(Other_Costs_Amt) as 'Other_Costs_Amt',
   SUM(Equipment_Amt) as 'Equipment_Amt',
   SUM(Equipment_Rental_Amt) as 'Equipment_Rental_Amt',
   SUM(Materials_Amt) as 'Materials_Amt',
   SUM(Reimbursable_Amt) as 'Reimbursable_Amt',
   (SUM(Labor_Amt) + SUM(Contracted_Services_Amt) + SUM(Consultant_Services_Amt) + SUM(Other_Costs_Amt) + SUM(Equipment_Amt )+ SUM(Equipment_Rental_Amt) + SUM(Materials_Amt) - SUM(Reimbursable_Amt)) as 'Total_Cost_2018_2019_Sum'
FROM 	Capital_Projects_tbl
WHERE	Proposed_Fiscal_Year = '2018/2019'
<cfif isdefined("form.submitted")>
		<cfif #FORM.Department_Name# NEQ "Show All">
       		AND Department_Name = '#FORM.Department_Name#'
        </cfif>
        <cfif #FORM.Project_Status# NEQ "Show All">
       		AND Project_Status = '#FORM.Project_Status#'
        </cfif> 
</cfif>
</cfquery>

<cfquery name="Total_Cost_2019_2020_Sum" datasource="CPdB">
SELECT 
   SUM(Labor_Amt) as 'Labor_Amt',
   SUM(Contracted_Services_Amt) as 'Contracted_Services_Amt',
   SUM(Consultant_Services_Amt) as 'Consultant_Services_Amt',
   SUM(Other_Costs_Amt) as 'Other_Costs_Amt',
   SUM(Equipment_Amt) as 'Equipment_Amt',
   SUM(Equipment_Rental_Amt) as 'Equipment_Rental_Amt',
   SUM(Materials_Amt) as 'Materials_Amt',
   SUM(Reimbursable_Amt) as 'Reimbursable_Amt',
   (SUM(Labor_Amt) + SUM(Contracted_Services_Amt) + SUM(Consultant_Services_Amt) + SUM(Other_Costs_Amt) + SUM(Equipment_Amt )+ SUM(Equipment_Rental_Amt) + SUM(Materials_Amt) - SUM(Reimbursable_Amt)) as 'Total_Cost_2019_2020_Sum'
FROM 	Capital_Projects_tbl
WHERE	Proposed_Fiscal_Year > '2019/2020'
<cfif isdefined("form.submitted")>
		<cfif #FORM.Department_Name# NEQ "Show All">
       		AND Department_Name = '#FORM.Department_Name#'
        </cfif>
        <cfif #FORM.Project_Status# NEQ "Show All">
       		AND Project_Status = '#FORM.Project_Status#'
        </cfif> 
</cfif>
</cfquery>

<cfquery name="Total_Reimbursable_Amt_Sum" datasource="CPdB">
SELECT 
   SUM(Reimbursable_Amt) as 'Total_Reimbursable_Amt_Sum'
FROM 	Capital_Projects_tbl
WHERE 1=1
<cfif isdefined("form.submitted")>
		<cfif #FORM.Department_Name# NEQ "Show All">
       		AND Department_Name = '#FORM.Department_Name#'
        </cfif>
        <cfif #FORM.Project_Status# NEQ "Show All">
       		AND Project_Status = '#FORM.Project_Status#'
        </cfif>  
</cfif>
</cfquery>


<cfparam name="Total_Cost_Sum" type="any" default="0.00">
<cfparam name="Total_Reimbursable_Amt_Sum" type="any" default="0.00">
<cfparam name="Total_Net_Sum" type="any" default="0.00">

<cfif #Total_Reimbursable_Amt_Sum.Total_Reimbursable_Amt_Sum# EQ "">
<cfset Total_Reimbursable_Amt_Sum = '0.00'>
<cfelse>
<cfset Total_Reimbursable_Amt_Sum = #Total_Reimbursable_Amt_Sum.Total_Reimbursable_Amt_Sum#> 
</cfif>

<cfif #Total_Cost_Sum.Total_Cost_Sum# EQ "">
<cfset Total_Cost_Sum = '0.00'>
<cfelse>
<cfset Total_Cost_Sum = '#Total_Cost_Sum.Total_Cost_Sum#'> 
</cfif>

	<cfset Total_Net_Sum = #Total_Cost_Sum# - #Total_Reimbursable_Amt_Sum#>

<!---
Total_Cost_Sum: <cfdump var="#Total_Cost_Sum#"><br>
Total_Reimbursable_Amt_Sum: <cfdump var="#Total_Reimbursable_Amt_Sum#"><br>
Total_Net_Sum: <cfdump var="#Total_Net_Sum#"><br>
--->


<body class="dt-example">
	<div class="container">
		<section>
        
<section id="content">
	<div class="padding-content">
    	<div class="container_24">
        	<div class="wrapper">
            	<article class="grid_24">
                	<div class="box">
                    
                    	<div class="padding2-box">
                        	<h3 class="indent-bot">Capital Plan - Total Projected Projects <cfif isdefined("form.submitted")>- <cfoutput>Dept: #FORM.Department_Name# - Status: #FORM.Project_Status#</cfoutput></cfif></h3>
                            <h6>The list below contains data for capital projects taking place. To view a spacific fund - department, make your selection and click submit. You may also sort 
                            projects by clicking the title of each column as needed. 
                            <br>
                            <br>
                            <cfform name="Search">
                            Fund & Department Name: 
                            <cfselect name="Department_Name" query="Departments_Dropdown" value="Department_Name" display="CONCAT_Dept_Fund">
                            <cfif isdefined("form.submitted") AND #FORM.Department_Name# EQ "Show All">
                            <cfelse>
                            <option selected>Show All</option>
                            </cfif>
                            <cfif isdefined("form.submitted")>
							<cfoutput><option selected>#FORM.Department_Name#</option></cfoutput>
                            </cfif>
                            </cfselect>
                            <br><br>
                            Project Status: 
                            <cfselect name="Project_Status" query="Project_Status_Dropdown" value="Project_Status">
                            <cfif isdefined("form.submitted") AND #FORM.Project_Status# EQ "Show All">
                            <cfelse>
                            <option selected>Show All</option>
                            </cfif>
                            <cfif isdefined("form.submitted")>
							<cfoutput><option selected>#FORM.Project_Status#</option></cfoutput>
                            </cfif>
                            </cfselect>&nbsp;                          
                            <cfinput type="submit" name="submitted" value="Filter Results">
                            </cfform>
                            <br>
                            </h6>
                            
			<table id="example" class="display dt-head-right" cellspacing="0"  width="100%">
				<thead>
					<tr>
						<th>Priority</th>
                        <th>Project</th>
                        <th>Cost</th>
                       <!--- <th>2014/2015</th>--->
						<th>2015/2016</th>
                        <th>2016/2017</th>                       
						<th>2017/2018</th>
						<th>2018/2019</th>
                        <th>>2019</th>
                        <th>Reimbusable</th>
						<th>Net</th>
					</tr>
				</thead>

				<tfoot>                  
					<tr>
						<th>&nbsp;</th>
                        <th>&nbsp;</th>
                        <th><cfoutput>$#numberFormat(Total_Cost_Sum)#</cfoutput></th><!---<cfoutput query="Total_Cost_Sum">#DollarFormat(Total_Cost_Sum)#<br /></cfoutput>--->
                        <!---<th><cfoutput query="Total_Cost_2014_2015_Sum">$#numberFormat(Total_Cost_2014_2015_Sum)#</cfoutput></th>--->
					  	<th><cfoutput query="Total_Cost_2015_2016_Sum">$#numberFormat(Total_Cost_2015_2016_Sum)#</cfoutput></th>
                        <th><cfoutput query="Total_Cost_2016_2017_Sum">$#numberFormat(Total_Cost_2016_2017_Sum)#</cfoutput></th>                       
						<th><cfoutput query="Total_Cost_2017_2018_Sum">$#numberFormat(Total_Cost_2017_2018_Sum)#</cfoutput></th>
						<th><cfoutput query="Total_Cost_2018_2019_Sum">$#numberFormat(Total_Cost_2018_2019_Sum)#</cfoutput></th>
                        <th><cfoutput query="Total_Cost_2019_2020_Sum">$#numberFormat(Total_Cost_2019_2020_Sum)#</cfoutput></th>
                        <th><cfoutput>$#numberFormat(Total_Reimbursable_Amt_Sum)#</cfoutput><!---<cfoutput query="Total_Reimbursable_Amt_Sum">(#Total_Reimbursable_Amt_Sum#)</cfoutput>---></th><!---DollarFormat(Total_Reimbursable_Amt_Sum)--->
                        <th><cfoutput>$#numberFormat(Total_Net_Sum)#</cfoutput></th><!---DollarFormat(Total_Net_Sum)--->
					</tr>                
				</tfoot>

				<tbody>              
                <cfoutput query="Projects_List">
<cfset Total_Cost = #Projects_List.Labor_Amt# + #Projects_List.Contracted_Services_Amt# + #Projects_List.Consultant_Services_Amt# + #Projects_List.Other_Costs_Amt# + #Projects_List.Equipment_Amt# + #Projects_List.Equipment_Rental_Amt# + #Projects_List.Materials_Amt#>    

<cfset Total_Cost_Minus_Reimbursable_Amt = #Total_Cost# - #Projects_List.Reimbursable_Amt#>    
          
					<tr title="#Project_Status# | #Department_Name# | #Project_Type# | #Submitted_By#">
						<td>#Priority_Rank#</td>
                        <td>#Project_Name#</td>
						<td align="right">$#numberFormat(Total_Cost)#</td>
                       <!--- 
					   <cfif Projects_List.Proposed_Fiscal_Year EQ "2014/2015">                       
                        <td>$#numberFormat(Total_Cost)#</td>
                        <cfelse>
                        <td></td>
                        </cfif>  
						 --->                      
                        <cfif Projects_List.Proposed_Fiscal_Year EQ "2015/2016">                       
                        <td align="right">$#numberFormat(Total_Cost_Minus_Reimbursable_Amt)#</td>
                        <cfelse>
                        <td></td>
                        </cfif>                        
                        <cfif Projects_List.Proposed_Fiscal_Year EQ "2016/2017">                       
                        <td align="right">$#numberFormat(Total_Cost_Minus_Reimbursable_Amt)#</td>
                        <cfelse>
                        <td></td>
                        </cfif>   
                        <cfif Projects_List.Proposed_Fiscal_Year EQ "2017/2018">                       
                        <td align="right">$#numberFormat(Total_Cost_Minus_Reimbursable_Amt)#</td>
                        <cfelse>
                        <td></td>
                        </cfif>
                        <cfif Projects_List.Proposed_Fiscal_Year EQ "2018/2019">                       
                        <td align="right">$#numberFormat(Total_Cost_Minus_Reimbursable_Amt)#</td>
                        <cfelse>
                        <td></td>
                        </cfif>
                       <cfif Projects_List.Proposed_Fiscal_Year GT "2019/2020">                       
                        <td align="right">$#numberFormat(Total_Cost_Minus_Reimbursable_Amt)#</td>
                        <cfelse>
                        <td></td>
                        </cfif>
                        <td align="right">$#numberFormat(Reimbursable_Amt)#</td>                                
                        <cfset Total_Net_Cost = #Total_Cost# - #Projects_List.Reimbursable_Amt#>                        
						<td align="right">$#numberFormat(Total_Net_Cost)#</td>

					</tr>
                 </cfoutput>
				</tbody>
			</table>
                    
                    </div>                    
                </article>
        </div>
    </div>
</section>        

</div>


<cfinclude template="Bottom_Boxes.cfm">

<cfinclude template="footer.cfm">

Open in new window

0
midhelpdeskAuthor Commented:
Attached is some sample data from the Capital_Projects_tbl as well in case this helps.

Sample-Data.xlsx

thanks
-jes
0
_agx_Commented:
I'm short on time right now (so sorry for the quick and dirty fiddle), but try something like this.

SQLFiddle
SELECT 
    Project_Name
    , Total_Cost_Overall
    , [2015/2016]
    , [2016/2017]
    , Total_Reimbursable_Amt
FROM
(
SELECT Project_name
  , 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
;

Open in new window


I only used 2 years in the example, but the result should be:

Project_Name       | Total_Cost_Overall       | 2015/2016       | 2016/2017       | Total_Reimbursable_Amt
ProjectA                        | 59000                       | 52000               | 7000               |  10862

Run the above in a cfquery (add your WHERE filters...), then use something like this to output the results

<cfoutput query="yourQuery">
     #yourQuery["Project_Name"][CurrentRow]#  
     #yourQuery["Total_Cost_Overall"][CurrentRow]#  
     #yourQuery["2015/2016"][CurrentRow]#  
     #yourQuery["Total_Reimbursable_Amt"][CurrentRow]#  
</cfoutput>

Open in new window

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
midhelpdeskAuthor Commented:
Ill give it a good right now and let you know. Thanks for your time, I really really appreciate it!
-jes
0
_agx_Commented:
Sounds good.  It may need a few changes (and do double check the columns used in the SUMs ;-) but that should give you the basic structure.
0
midhelpdeskAuthor Commented:
This is it!! What a solution. Soo sweet!! THANK YOU THANK YOU THANK YOU!!

Trying to GROUP BY and ORDER BY, but it bombs out?

Error:
"The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified."

Goal is to get the results to group by "Project_Priority_Type" (Carry Over/Other/Grant Reimbursement/Critical/ROI etc.) and then order by "Proposed_Fiscal_Year" (2015/2016/2016/2017/ etc.)

Can SQL do this also?
0
midhelpdeskAuthor Commented:
Here is the query that it doesn't  like:

SELECT	Project_Name,
		Project_Priority_Type,
        Total_Cost_Overall,
        [2015/2016],
        [2016/2017],
        [2017/2018],
        [2018/2019],
        [2019/2020],
        [2020/2021],
        [2021/2022],
        [2022/2023],
        [2023/2024],
        [2024/2025],    
        Total_Reimbursable_Amt
FROM
    (
        SELECT	Project_name,
        		Project_Priority_Type,
                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
        --- put my WHERE filters and shizzzz herrrr
        WHERE 1=1
        GROUP BY Project_Priority_Type, Proposed_Fiscal_Year
        ORDER BY Proposed_Fiscal_Year ASC
    ) AS src
PIVOT
    (
       SUM(Total_Cost_Project)
        FOR Proposed_Fiscal_Year IN ([2015/2016],
                                     [2016/2017],
                                     [2017/2018],
                                     [2018/2019],
                                     [2019/2020],
                                     [2020/2021],
                                     [2021/2022],
                                     [2022/2023],
                                     [2023/2024],
                                     [2024/2025]
    )
    ) AS PivotTable;

Open in new window

0
_agx_Commented:
Glad it helped!

You shouldn't need the GROUP BY. For the ordering, move the ORDER BY outside the PIVOT.  Try this:

SQLFiddle
SELECT 
    Project_Name
    , Project_Priority_Type
    , Total_Cost_Overall
    , [2015/2016]
    , [2016/2017]
    , Total_Reimbursable_Amt
FROM
(
SELECT Project_name
  , Project_Priority_Type
  , 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
) AS src
PIVOT
(
   SUM(Total_Cost_Project)
    FOR Proposed_Fiscal_Year IN ([2015/2016], [2016/2017])
) AS PivotTable
ORDER BY Project_name
;

Open in new window

0
_agx_Commented:
> ORDER BY Proposed_Fiscal_Year ASC

Hm...  on second thought I'm not sure that one makes sense within the context of a PIVOT. Can you give an example of the desired affect?
0
midhelpdeskAuthor Commented:
Sure, he is the example:

Capture6.PNG
0
_agx_Commented:
I've got to think on that one.  The problem is grouping and ordering at one time. Not sure if you can do it without a subquery.
0
midhelpdeskAuthor Commented:
Any help is very appreciated! Thanks do much!
0
_agx_Commented:
Edit:
> then order by "Proposed_Fiscal_Year" (2015/2016/2016/2017/ etc.)

Thinking about this, you can't really order the results that way.  In other words, you can sort by the first fiscal year column, but the other columns only come into play if there's a tie in the amounts.  ie:

......
) AS PivotTable
ORDER BY [2015/2016] DESC, [2016/2017] DESC, other columns

Perhaps that's what you meant?
0
midhelpdeskAuthor Commented:
It seems to be sorting well enough for the user. I thank you for all the help!

-jes
0
_agx_Commented:
No problem :)
0
midhelpdeskAuthor Commented:
Hello,
I'm back to this issue... I have tried what I can to get it to group and sort as displayed in the attachment, but I have had little luck. Attached for your review is the report I need to build, with the grouping and sort order we need.

The pivot table puts multiple records on one row which is perfect, now I need to group projects by Proposed Fiscal Year and Project Priority Type so it will display as attached.

I am having a hell of a time getting the query to group and sort as needed. Any help would be much appreciated.
Thanks

ReportGroupandSort.PNG
0
midhelpdeskAuthor Commented:
BTW, I am using ColdFusion and have tried a query of queries with no success.

Attached is the closest I can get using MANY queries.. basically one query for each Project Priority Type.  But as you can see when done this way, I lose the single line for multi year projects.

thanks

re.PNG
0
_agx_Commented:
> Proposed Fiscal Year and Project Priority Type

I assume all projects have a single "Project Priority Type", so that part makes sense. However, I'm not sure what group by "Proposed Fiscal Year" means in the context of this report.  In particular, what is the expect result when a project has a budget for multiple years?
0
midhelpdeskAuthor Commented:
Hi,
Correct, all projects have a single "Project Priority Type" and some projects can span multiple years.
The "Proposed Fiscal Year" means that what year is that project scheduled to take place.

If the project is to span multiple years, they enter the project multiple times, with the amount of money for that years capital projects.

 The expected result is if a project starts in 2014 and ends in 2017, that project would be listed in Proposed Fiscal Year 2014 first yet span all the years... the attached is an example output:

Report-Format-with-GroupandSort.PNG
Thank you very much!
0
midhelpdeskAuthor Commented:
Also, Id like to point out that this report is by department name.

By
Department_Name
Project_Priority_Type
Priority_Rank

*Only one report per department, they will run the report for each department...
0
_agx_Commented:
> that project would be listed in Proposed Fiscal Year 2014 first yet span all the years

Hm... the reason for my asking is that is similar to what the original query did, but obviously it is not "quite" right.  So I'm trying to digest the screen shots to see what is "off" about the current grouping/sorting and how to fix it.  Let me play around with the SQL Fiddle ...
0
midhelpdeskAuthor Commented:
Thank you very much! Im still looking at it as well, but my eyes are starting to cross. LOL
0
PortletPaulfreelancerCommented:
as I suggested above this extra need regarding ordering should be a new question, however I'd like to make this observations:

The expected result is if a project starts in 2014 and ends in 2017, that project would be listed in Proposed Fiscal Year 2014 first yet span all the years

So what you need - BEFORE the pivot - is a column that holds the minimum of Proposed Fiscal Year e.g.

SELECT
      Project_name
    , Project_Priority_Type
    , Proposed_Fiscal_Year

    , MIN(Proposed_Fiscal_Year) OVER(PARTITION BY 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

Open in new window


then do the pivot
then include [MinFiscalYear] in the final ORDER BY clause
0
_agx_Commented:
> my eyes are starting to cross

Speaking of bleary eyed programmers ... looks like I'm the inadvertent trouble maker on this one ;-)  

Paul - Totally right that old threads don't get much attention. Midhelpdesk did the right thing and opened a new thread. Unfortunately ... while I was reviewing the old conversation, I forgot to switch over and started posting on the old thread instead of the new one.  My bad.

Sorry guys! :)
0
PortletPaulfreelancerCommented:
0
midhelpdeskAuthor Commented:
Thank you all for the comments and suggestions. I appreciate all the effort especially from _agx_ and all the help so far!

I will be following up in the other thread, looks like it is working...
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
Microsoft SQL Server

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.