SQL SELECT Query for a Total and parts building the total

Question

Hello!

I need a SQL Server Query for the following scenario:

The Tables:

  CREATE TABLE [dbo].[JobStatus]
  (
   [OID] [int] IDENTITY(1, 1)
               NOT NULL,
   [Name] [varchar](100) NOT NULL,
   [Code] [varchar](5) NOT NULL,
   [PictureID] [int] NOT NULL,
   [LastModifiedAt] [timestamp] NOT NULL,
   CONSTRAINT [PK_JobStatuses] PRIMARY KEY CLUSTERED ([OID] 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
SET IDENTITY_INSERT [dbo].[JobStatus] ON
INSERT  [dbo].[JobStatus]
        ([OID], [Name], [Code], [PictureID])
VALUES
        (1, N'Ready to book', N'J_RTB', 13)
INSERT  [dbo].[JobStatus]
        ([OID], [Name], [Code], [PictureID])
VALUES
        (2, N'Pending', N'J_P', 14)
INSERT  [dbo].[JobStatus]
        ([OID], [Name], [Code], [PictureID])
VALUES
        (3, N'Booked', N'J_B', 15)
INSERT  [dbo].[JobStatus]
        ([OID], [Name], [Code], [PictureID])
VALUES
        (4, N'Cancelled', N'J_CAN', 16)
INSERT  [dbo].[JobStatus]
        ([OID], [Name], [Code], [PictureID])
VALUES
        (6, N'CallBack', N'J_CAL', 17)
SET IDENTITY_INSERT [dbo].[JobStatus] OFF

CREATE TABLE [dbo].[JobBatch]
  (
   [OID] [varchar](250) NOT NULL,
   [UserID] [bigint] NOT NULL,
   [BatchDate] [smalldatetime] NOT NULL,
   [LastModifiedAt] [timestamp] NOT NULL,
   CONSTRAINT [PK_JobBatches] PRIMARY KEY CLUSTERED ([OID] 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].[JobBatch] ADD  CONSTRAINT [DF_JobBatch_BatchDate]  DEFAULT (GETDATE()) FOR [BatchDate]
GO

CREATE TABLE [dbo].[Job]
  (
   [OID] [bigint] IDENTITY(1, 1)
                  NOT NULL,
   [BatchID] [varchar](250) NOT NULL,
   [PharmacyID] [bigint] NOT NULL,
   [BranchID] [bigint] NOT NULL,
   [CoordinatorID] [bigint] NOT NULL,
   [LocumTypeID] [int] NOT NULL,
   [ShiftID] [int] NOT NULL,
   [JobDate] [smalldatetime] NOT NULL,
   [Rate] [money] NOT NULL,
   [RatePlus] [money] NOT NULL,
   [StartTime] [smalldatetime] NOT NULL,
   [EndTime] [smalldatetime] NOT NULL,
   [PriorityID] [tinyint] NOT NULL,
   [JobCode] [varchar](100) NULL,
   [JobStatusID] [int] NOT NULL,
   [TravelExpense] [bit] NOT NULL,
   [MealAllowance] [bit] NOT NULL,
   [Accommodation] [bit] NOT NULL,
   [UserID] [bigint] NULL,
   [RegisteredDate] [smalldatetime] NULL,
   [TextSent] [bit] NOT NULL,
   [BookingFee] [decimal](6, 2) NOT NULL,
   [LastModifiedAt] [timestamp] NOT NULL,
   CONSTRAINT [PK__Jobs__056690C222951AFD] PRIMARY KEY CLUSTERED ([OID] 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].[Job]  WITH CHECK ADD  CONSTRAINT [FK_Job_JobBatch] FOREIGN KEY([BatchID])
REFERENCES [dbo].[JobBatch] ([OID])
GO

ALTER TABLE [dbo].[Job] CHECK CONSTRAINT [FK_Job_JobBatch]
GO

ALTER TABLE [dbo].[Job]  WITH CHECK ADD  CONSTRAINT [FK_Job_JobStatus] FOREIGN KEY([JobStatusID])
REFERENCES [dbo].[JobStatus] ([OID])
GO

ALTER TABLE [dbo].[Job] CHECK CONSTRAINT [FK_Job_JobStatus]
GO

The query:

SELECT 
  dbo.Job.BatchID,
  COUNT(dbo.Job.OID) AS Total,
  dbo.JobBatch.BatchDate
FROM
  dbo.Job
  INNER JOIN dbo.JobBatch ON dbo.Job.BatchID = dbo.JobBatch.OID
GROUP BY
  dbo.Job.BatchID,
  dbo.JobBatch.BatchDate
HAVING
  (dbo.JobBatch.BatchDate > CONVERT(DATETIME, '2010-08-01 00:00:00', 102))
ORDER BY
  dbo.JobBatch.BatchDate,
  Total DESC

Now, there is a column called JobStatusID. It holds five values representing a Job's status. I need the data selected like this:

GoogleDocs Online Sample Spreadsheet

The Group By and the Having needs to be intact. Any ideas? Thanks.

Answer 1

SELECT 
  dbo.Job.BatchID,
  COUNT(dbo.Job.OID) AS Total,
  COUNT(CASE WHEN JobStatusID = 1 THEN 1 END) AS [Ready to book],
  COUNT(CASE WHEN JobStatusID = 2 THEN 1 END) AS [Pending],
  COUNT(CASE WHEN JobStatusID = 3 THEN 1 END) AS [Booked],
  COUNT(CASE WHEN JobStatusID = 4 THEN 1 END) AS [Cancelled],
  COUNT(CASE WHEN JobStatusID = 6 THEN 1 END) AS [CallBack],
  dbo.JobBatch.BatchDate
FROM
  dbo.Job
  INNER JOIN dbo.JobBatch ON dbo.Job.BatchID = dbo.JobBatch.OID
GROUP BY
  dbo.Job.BatchID,
  dbo.JobBatch.BatchDate
HAVING
 (dbo.JobBatch.BatchDate > CONVERT(DATETIME, '2000-01-01 00:00:00', 102))
ORDER BY
  dbo.JobBatch.BatchDate,
  Total DESC