Start Free Trial

asked on

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.

User generated image

Attachment 2 is the column names in the database:

User generated image

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

ASKER

I'm looking into PIVOT / UNPIVOT to see if I can get something working using this method... still stuck.
-jes

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

ASKER

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.

User generated image

thanks
-jes

ASKER

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

From this:

User generated image

To this:

User generated image

ASKER

Here is an example of the final result I need to achieve.

User generated image

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

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

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.

ASKER

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

ASKER

Attached is some sample data from the Capital_Projects_tbl as well in case this helps.

Sample-Data.xlsx

thanks
-jes

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

Ill give it a good right now and let you know. Thanks for your time, I really really appreciate it!
-jes

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.

ASKER

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?

ASKER

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

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

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

ASKER

Sure, he is the example:

User generated image

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.

ASKER

Any help is very appreciated! Thanks do much!

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?

ASKER

It seems to be sorting well enough for the user. I thank you for all the help!

-jes

No problem :)

ASKER

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

User generated image

ASKER

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

User generated image

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

ASKER

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:

User generated image

Thank you very much!

ASKER

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

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

ASKER

Thank you very much! Im still looking at it as well, but my eyes are starting to cross. LOL

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

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

& that new question is:
https://www.experts-exchange.com/questions/28691841/MSSQL-Query-Help-for-Report.html

ASKER

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