SQLTeam.com | Weblogs | Forums

Need help with this query

Hi,
I have a set of sql records from a stored proc that look like below:

26/07/2020 1
26/07/2020 1
26/07/2020 2
26/07/2020 2
26/07/2020 2
26/07/2020 2

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.

hi

Hope this helps :slight_smile:

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?

hi

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 ..

Indeed it is simple. I have this output:

sqloutput

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".

hi

please see the below example ... use it as a guide ..

CREATE PROCEDURE dbo.uspGetAddress @City nvarchar(30)
AS
SELECT * 
FROM Person.Address
WHERE City = @City
GO

Ah yes this is all that's needed, not an if!

Only problem now is that I need to replace "City" with the temp column 'MOT". Not sure how to do that?

Please post your Stored Proc .. from which you get your Output

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

hi you can use the parameter like this .. !!!

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

Thanks, but that didn't work (warning about an aggregate function). Instead I used a HAVING clause.

hi

so

with a having clause .. it WORKED !!!

ok then .. you got your solution :+1:

first comment is very helpful.