SELECT
YEAR(A.AppointmentDate) AS [Year],
ATS.[Description] AS AppointmentSource,
MONTH(A.Appointmentdate) AS MonthNumber,
COUNT(A.AppointmentID) AS NumberOfAppointments
FROM
Appointment A
INNER JOIN
AppointmentStatus AST ON AST.AppointmentStatusID = A.AppointmentStatusID
INNER JOIN
AppointmentSource ATS ON ATS.AppointmentSourceID = A.AppointmentSourceID
WHERE
YEAR(A.AppointmentDate) IN (2021, 2020)
AND ATS.AppointmentSourceID IN (1, 3)
AND A.AppointmentStatusID = 1 -- Active
GROUP BY
YEAR(A.AppointmentDate), MONTH(A.AppointmentDate),
ATS.[Description], AST.AppointmentStatusName
Appointment source table has below columns:
I need to show count of Call Center + Agent as 'Call Center' and Web as 'Web'
so if per month, Appointments made by
Call Center = 50
Agent = 25
Web = 30
I need the result as below.
Call Center = 50 + 25 = 75
Web = 30
January 25, 2021 at 10:34 pm
Could you post some sample data and expected results?
I'd use a Calendar table for this kind of thing.
Remove AST.AppointmentStatusName from the GROUP BY - you are not including it in the select. If you want to group by 'Call Center' or 'Web' then you need to define the AppointmentSource as either of those values.
Select year(A.AppointmentDate) As [Year]
, iif(ATS.[Description] = 'Web', 'Web', 'Call Center') As AppointmentSource
, month(A.Appointmentdate) As MonthNumber
, count(A.AppointmentID) As NumberOfAppointments
From Appointment A
Inner Join AppointmentStatus AST On AST.AppointmentStatusID = A.AppointmentStatusID
Inner Join AppointmentSource ATS On ATS.AppointmentSourceID = A.AppointmentSourceID
Where A.AppointmentDate >= '2020-01-01'
And A.AppointmentDate < getdate() --Include everything up to current execution time
And ATS.AppointmentSourceID In (1 , 3)
And A.AppointmentStatusID = 1 -- Active
Group By
year(A.AppointmentDate)
, month(A.AppointmentDate)
, iif(ATS.[Description] = 'Web', 'Web', 'Call Center');
I also changed the way you are looking for AppointmentDate - using a function (YEAR) on the column will prevent usage of an index on that column if one exists (or is created in the future). Since I would not include appointments for today because I don't know when this would run - I would change to this:
And A.AppointmentDate < cast(getdate() As date) --Include everything prior to today
You could also use a CASE expression as:
, Case ATS.Description
When 'Call Center' Then 'Call Center'
When 'Agent' Then 'Call Center'
When 'Web' Then 'Web'
Else 'Unknown'
End As AppointmentSource
Or simplified - to:
, Case ATS.Description
When 'Web' Then 'Web'
Else 'Call Center'
End As AppointmentSource
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 28, 2021 at 11:17 pm
In the future, would you mind following netiquette and post DDL for us? You have several fundamental mistakes in your design. Status is not a kind of entity; it's an attribute so it should be a column in a table. SQL is declarative and not procedural, so we don't use flags. That was assembly language. A status cannot have a name because the name is another kind of attribute property. There is no such thing as a "status_id"; this is called an attribute property and it must be either an identifier or status, but it can never by definition be both.
Finally, an experienced RDBMS person would probably have used a calendar for the appointment book. But why doesn't your model have any clients attending these appointments? Your mind is still trapped the old days of procedural languages, so you compute temporal data. Here's a possible skeleton:
CREATE TABLE Appointments
(agent_id CHAR(10) NOT NULL
REFERENCES Agents,
client_id CHAR(10) NOT NULL
REFERENCES Clients,
appointment_date DATE NOT NULL,
appointment_status CHAR(3) NOT NULL
CHECK (appointment_status IN ???)
appointment_source CHAR(1) NOT NULL
CHECK (appointment_source IN (1,3)),
..):
But this is about as far as I can get trying to read your mind. Do you want to add more flesh to the skeleton?
Please post DDL and follow ANSI/ISO standards when asking for help.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply