Basically date times and per each, an ID (the ID represents a specific business service.
For example, ID 1 is let's say "Service A", ID 2 is "Service B", and so forth.
What I'd like to do is have the following output:
DateTime Service A Service B Service C
The Service columns are a count/total of the instances that ID 1, 2, 3 etc come up for each date. So in the above, Service A will have 2 and Service B 4.
How could this be done? I hope I have provided all the info needed! Note that date and id comes from two different tables.
i am giving my solution by putting the data in temp table ... you can use a CTE to get your result set instead of the temp table
then same SQL logic ..
please click arrow to the left for drop create data script
----------------------
-- create table
create table Data
(
date date ,
ServiceID int
)
GO
----------------------------
-- date format
set dateformat dmy
---------------------------
-- insert into table
insert into data values
( '26/07/2020', 1),
( '26/07/2020', 1),
( '26/07/2020', 2),
( '26/07/2020', 2),
( '26/07/2020', 2),
( '26/07/2020', 3),
( '26/07/2020', 4),
( '26/07/2020', 4),
( '26/07/2020', 4),
( '26/07/2020', 4),
( '27/07/2020', 1),
( '27/07/2020', 2),
( '27/07/2020', 2),
( '27/07/2020', 2),
( '27/07/2020', 3),
( '27/07/2020', 4),
( '27/07/2020', 4)
go
select
date
, sum(case when ServiceID = 1 then 1 else 0 end ) as ServiceA
, sum(case when ServiceID = 2 then 1 else 0 end ) as ServiceB
, sum(case when ServiceID = 3 then 1 else 0 end ) as ServiceC
, sum(case when ServiceID = 4 then 1 else 0 end ) as ServiceD
from
data
group by
date
Hey thanks, I got this working with the following query:
select distinct DBO.Bookings.dropOffDate,
sum(DISTINCT case when DBO.BookingRequests.ServiceDescriptionId = 1 then 1 else 0 end ) as MOT
, sum(case when DBO.BookingRequests.ServiceDescriptionId = 2 then 1 else 0 end ) as 'Full Service',
sum(case when DBO.BookingRequests.ServiceDescriptionId = 3 then 1 else 0 end ) as 'Major Service'
from BookingRequests, Bookings
WHERE DBO.BookingRequests.ServiceDescriptionId = 1
GROUP BY DBO.Bookings.dropOffDate
However, I have a question:
I want to pass in integer values. When I do the aggregate of service IDs, I want to check this against the integers I pass in. For example, there is a maximum value for a certain service ID and need to check the value returned equals this.
How could I do an equals check in the query above?
Looks like Your Question is very very simple to do !!!
i am having trouble understanding you .. what you want !!!
maybe its just me .. but it helps a lot to be clear precise and simple to understand .. in what you are saying
it will be crystal clear to you ... but for other people to make sense sometimes its very difficult
please use excel .. and words to explain ...if you can ..
Firstly, I will pass in values to a stored proc as maximums. I.E. I want to check that there is no more than 7 MOTs booked for a day, so a parameter is MOTMaximum.
So let's 's say that column for MOT is 7 (ie the sum of the instances of service descriptions with 1, i.e. MOTs, on a single date is 7). I want to check the sum, i.e. this line:
sum(DISTINCT case when DBO.BookingRequests.ServiceDescriptionId = 1 then 1 else 0 end ) as MOT
Against the parameter I passed in. I.E. basically if an "if sum = x".
select distinct DBO.Bookings.dropOffDate,
sum(DISTINCT case when DBO.BookingRequests.ServiceDescriptionId = 1 then 1 else 0 end ) as MOT
, sum(case when DBO.BookingRequests.ServiceDescriptionId = 2 then 1 else 0 end ) as 'Full Service',
sum(case when DBO.BookingRequests.ServiceDescriptionId = 3 then 1 else 0 end ) as 'Major Service'
from BookingRequests, Bookings
WHERE DBO.BookingRequests.ServiceDescriptionId = 1
GROUP BY DBO.Bookings.dropOffDate
SELECT DISTINCT dbo.bookings.dropoffdate,
Sum(DISTINCT CASE
WHEN dbo.bookingrequests.servicedescriptionid = 1
THEN 1
ELSE 0
END) AS MOT,
Sum(CASE
WHEN dbo.bookingrequests.servicedescriptionid = 2 THEN 1
ELSE 0
END) AS 'Full Service',
Sum(CASE
WHEN dbo.bookingrequests.servicedescriptionid = 3 THEN 1
ELSE 0
END) AS 'Major Service'
FROM bookingrequests,
bookings
WHERE dbo.bookingrequests.servicedescriptionid = 1
-----------------------------------------------------------------------------------
AND Sum(DISTINCT CASE
WHEN dbo.bookingrequests.servicedescriptionid = 1
THEN 1
ELSE 0
END) = @Param_Int
---------------------------------------------------------------------------------
GROUP BY dbo.bookings.dropoffdate
