Advise on how to add counts to a query

Question

Post reply

Advise on how to add counts to a query

TK-484813

Valued Member

Points: 68
More actions
June 11, 2014 at 9:08 am

#396896

Need assistance working out how to get task counts in a sales report.

The data is located in three tables: Projects, ProjectTasks, Sales.

I need to group the data by month and project. It needs to include sales per month

as well as the number of each project task completed that month.

Projects: ProjectID Name 1 Project1 ProjectTasks: ProTaskID ProjectID TaskCode BeginDate EndDate 1 1 Task1 1/1/2014 1/15/2014 2 1 Task2 1/15/2014 1/20/2014 3 1 Task3 1/21/2014 1/29/2014 Sales: SalesID ProjectID Closing Amount 1 1 1/31/2014 $5000 Query Output: ClosingDate Project TotalSales Task1s Task2s Task3s 1/2014 Project1 $5000 1 1 1 My query so far is: SELECT right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)) as ClosingDate, p.name as Project, SUM(s.amount) as TotalSales FROM Sales s JOIN Project p ON p.projectID = s.projectID WHERE s.closing >= DATEADD(mm, -12, GETDATE()) GROUP BY right('0' + cast(month(s.closing) as varchar(2)), 2) + '/' + cast(year(s.closing) as varchar(4)), p.name

This will give me the grouping by month/year and project.

Need some advise on how to add the task counts as well.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Sean Lange SSC Guru Points: 286562 More actions · Answer 1

Hi and welcome to the forums. In order to help we will need a few things:

1. Sample DDL in the form of CREATE TABLE statements

2. Sample data in the form of INSERT INTO statements

3. Expected results based on the sample data

Please take a few minutes and read the first article in my signature for best practices when posting questions.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

TK-484813 Valued Member Points: 68 More actions · Answer 2

USE [master]



GO

/****** Object:  Database [ProjectTesting]    Script Date: 06/11/2014 10:58:40 ******/

CREATE DATABASE [ProjectTesting] ON  PRIMARY

( NAME = N'ProjectTesting', FILENAME = N'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ProjectTesting.mdf' , SIZE = 2048KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )

 LOG ON

( NAME = N'ProjectTesting_log', FILENAME = N'd:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ProjectTesting_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)

GO

ALTER DATABASE [ProjectTesting] SET COMPATIBILITY_LEVEL = 100

GO

USE [ProjectTesting]

GO

/****** Object:  Table [dbo].[Sales]    Script Date: 06/11/2014 10:58:40 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Sales](

	[SalesID] [int] NOT NULL,

	[ProjectID] [int] NOT NULL,

	[Closing] [date] NOT NULL,

	[Amount] [int] NULL,

 CONSTRAINT [PK_Sales] PRIMARY KEY CLUSTERED

(

	[SalesID] 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

INSERT [dbo].[Sales] ([SalesID], [ProjectID], [Closing], [Amount]) VALUES (1, 1, CAST(0x1F380B00 AS Date), 5000)

INSERT [dbo].[Sales] ([SalesID], [ProjectID], [Closing], [Amount]) VALUES (2, 2, CAST(0x1F380B00 AS Date), 4500)

INSERT [dbo].[Sales] ([SalesID], [ProjectID], [Closing], [Amount]) VALUES (3, 3, CAST(0x3B380B00 AS Date), 6000)

/****** Object:  Table [dbo].[ProjectTask]    Script Date: 06/11/2014 10:58:40 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[ProjectTask](

	[ProTaskID] [int] NOT NULL,

	[ProjectID] [int] NOT NULL,

	[TaskCode] [varchar](50) NOT NULL,

	[BeginDate] [date] NULL,

	[EndDate] [date] NULL,

 CONSTRAINT [PK_ProjectTask] PRIMARY KEY CLUSTERED

(

	[ProTaskID] 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

INSERT [dbo].[ProjectTask] ([ProTaskID], [ProjectID], [TaskCode], [BeginDate], [EndDate]) VALUES (1, 1, N'Task1', CAST(0x01380B00 AS Date), CAST(0x0F380B00 AS Date))

INSERT [dbo].[ProjectTask] ([ProTaskID], [ProjectID], [TaskCode], [BeginDate], [EndDate]) VALUES (2, 1, N'Task2', CAST(0x0F380B00 AS Date), CAST(0x14380B00 AS Date))

INSERT [dbo].[ProjectTask] ([ProTaskID], [ProjectID], [TaskCode], [BeginDate], [EndDate]) VALUES (3, 1, N'Task3', CAST(0x15380B00 AS Date), CAST(0x1D380B00 AS Date))

INSERT [dbo].[ProjectTask] ([ProTaskID], [ProjectID], [TaskCode], [BeginDate], [EndDate]) VALUES (4, 2, N'Task1', CAST(0x05380B00 AS Date), CAST(0x0A380B00 AS Date))

INSERT [dbo].[ProjectTask] ([ProTaskID], [ProjectID], [TaskCode], [BeginDate], [EndDate]) VALUES (5, 2, N'Task2', CAST(0x0B380B00 AS Date), CAST(0x19380B00 AS Date))

INSERT [dbo].[ProjectTask] ([ProTaskID], [ProjectID], [TaskCode], [BeginDate], [EndDate]) VALUES (6, 2, N'Task3', CAST(0x19380B00 AS Date), CAST(0x1E380B00 AS Date))

INSERT [dbo].[ProjectTask] ([ProTaskID], [ProjectID], [TaskCode], [BeginDate], [EndDate]) VALUES (7, 3, N'Task1', CAST(0x23380B00 AS Date), CAST(0x28380B00 AS Date))

INSERT [dbo].[ProjectTask] ([ProTaskID], [ProjectID], [TaskCode], [BeginDate], [EndDate]) VALUES (8, 3, N'Task2', CAST(0x2E380B00 AS Date), CAST(0x32380B00 AS Date))

INSERT [dbo].[ProjectTask] ([ProTaskID], [ProjectID], [TaskCode], [BeginDate], [EndDate]) VALUES (9, 3, N'Task3', CAST(0x32380B00 AS Date), CAST(0x3A380B00 AS Date))

INSERT [dbo].[ProjectTask] ([ProTaskID], [ProjectID], [TaskCode], [BeginDate], [EndDate]) VALUES (10, 1, N'Task3', CAST(0x1E380B00 AS Date), CAST(0x1F380B00 AS Date))

/****** Object:  Table [dbo].[Project]    Script Date: 06/11/2014 10:58:40 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[Project](

	[ProjectID] [int] NOT NULL,

	[Name] [varchar](100) NULL,

 CONSTRAINT [PK_Project] PRIMARY KEY CLUSTERED

(

	[ProjectID] 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

INSERT [dbo].[Project] ([ProjectID], [Name]) VALUES (1, N'Project1')

INSERT [dbo].[Project] ([ProjectID], [Name]) VALUES (2, N'Project2')

INSERT [dbo].[Project] ([ProjectID], [Name]) VALUES (3, N'Project3')

Query Output:



ClosingDate		Project 	TotalSales		Task1s		Task2s		Task3s

1/2014			Project1	$5000			1			1 			2

1/2014			Project2	$4500			1			1 			1

2/2014 Project3 $6000 1 1 1

Sean Lange SSC Guru Points: 286562 More actions · Answer 3

This works for your sample data.



select s.Closing as ClosingDate

	, p.Name as Project

	, s.Amount as TotalSales

	, sum(case TaskCode when 'Task1' then 1 end) as Task1

	, sum(case TaskCode when 'Task2' then 1 end) as Task2

	, sum(case TaskCode when 'Task3' then 1 end) as Task3

from Sales s

join Project p on p.ProjectID = s.ProjectID

join ProjectTask pt on pt.ProjectID = s.ProjectID

group by s.Closing

	, p.Name

	, s.Amount

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

ScottPletcher SSC Guru Points: 100262 More actions · Answer 4

I deferred converting the date to character until the very end to avoid having to convert every date and to avoid grouping on a char/varchar.



SELECT  right('0' + cast(month(ClosingMonth) as varchar(2)), 2) + '/' + cast(year(ClosingMonth) as varchar(4)) as ClosingDate,

        Project,

        TotalSales,

        TasksCompleted

FROM (

    SELECT  dateadd(month, datediff(month, 0, s.closing), 0) as ClosingMonth,

		    p.name as Project,

		    SUM(s.amount) as TotalSales,

		    COUNT(DISTINCT s.projectID) as TasksCompleted

    FROM 	Sales s

    JOIN 	Project p ON p.projectID = s.projectID

    WHERE	s.closing >= DATEADD(mm, -12, DATEADD(month, datediff(month, 0, getdate()), 0))

    GROUP BY dateadd(month, datediff(month, 0, s.closing), 0), p.name

) AS subquery1

ORDER BY

    ClosingDate, Project

SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!