June 11, 2014 at 9:08 am
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:
ProjectIDName
1Project1
ProjectTasks:
ProTaskIDProjectIDTaskCodeBeginDateEndDate
11 Task11/1/20141/15/2014
21Task21/15/20141/20/2014
31Task3 1/21/20141/29/2014
Sales:
SalesIDProjectIDClosingAmount
11 1/31/2014$5000
Query Output:
ClosingDateProject TotalSalesTask1sTask2sTask3s
1/2014Project1$500011 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
WHEREs.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.
June 11, 2014 at 9:16 am
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/
June 11, 2014 at 10:00 am
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:
ClosingDateProject TotalSalesTask1sTask2sTask3s
1/2014Project1$500011 2
1/2014Project2$450011 1
2/2014Project3$60001 1 1
June 11, 2014 at 10:19 am
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/
June 12, 2014 at 4:40 pm
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
WHEREs.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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply