sql server – SQL SELECT Query for a Total and parts building the total – Education Career Blog

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.

,

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

Leave a Comment