June 14, 2005 at 6:01 am
I am working on a Helpdesk system for my organisation that is based on sql server 2000.
I have a report that is made up from a number of views: count of calls outstanding, count of calls resolved, count of calls resolved in target, count of calls logged.
Each request has a priority, and management want to see a summary for each analyst team, broken down by priority.
The issue I have got is where there are no calls for a certain priority, say B, rather than show up in the query as 0 they are a not included in at all, i.e.:
Calls Resolved query:
sys_analgroup | ParamValueStart | ParamValueEnd | CallResolvedin TargetCount | sys_priority_id |
ICT Support | 31-May-2005 | 3-June-2005 | 6 | B1 |
ICT Support | 31-May-2005 | 3-June-2005 | 128 | C |
What I need if there are no calls for a proirity is:
sys_analgroup | ParamValueStart | ParamValueEnd | CallResolvedCount | sys_priority_id |
ICT Support | 31-May-2005 | 3-June-2005 | 6 | B1 |
ICT Support | 31-May-2005 | 3-June-2005 | 128 | C |
ICT Support 31-May-2005 3-June-2005 0 B
Does anyone know how I could achieve this using SQL?
Many thanks in advance.
Andrew
June 14, 2005 at 6:28 am
Do you mean that you want to have a line for each priority even if no calls were taken with the priority?
June 14, 2005 at 6:32 am
Thats exactly what I need to get....:
June 14, 2005 at 7:00 am
Can I get both tables' DDL, with a few inserts statements for the sample data (don't wanna guess the column's name) ?
June 14, 2005 at 7:06 am
Hope this makes sense....
Count of Calls resolved query:
SELECT db_owner.CallsResolved.sys_analgroup, db_owner.CallsResolved.ParamValueStart, db_owner.CallsResolved.ParamValueEnd,
COUNT(db_owner.CallsResolved.sys_request_id) AS CallsResolvedCount, dbo.priority.sys_priority_id
FROM dbo.priority LEFT OUTER JOIN
db_owner.CallsResolved ON dbo.priority.sys_priority_id = db_owner.CallsResolved.sys_requestpriority
GROUP BY db_owner.CallsResolved.sys_analgroup, db_owner.CallsResolved.ParamValueStart, db_owner.CallsResolved.ParamValueEnd,
dbo.priority.sys_priority_id
Count of Calls Logged query:
SELECT dbo.CallsLogged.sys_analgroup, dbo.CallsLogged.ParamValueEnd, dbo.CallsLogged.ParamValueStart, COUNT(dbo.CallsLogged.sys_request_id)
AS [Calls Logged Count], dbo.priority.sys_priority_id
FROM dbo.CallsLogged INNER JOIN
dbo.priority ON dbo.CallsLogged.sys_priority_id = dbo.priority.sys_priority_id
GROUP BY dbo.CallsLogged.sys_analgroup, dbo.CallsLogged.ParamValueEnd, dbo.CallsLogged.ParamValueStart, dbo.priority.sys_priority_id
Query that ties them all together:
SELECT TOP 100 PERCENT dbo.CallsLoggedCount.[Calls Logged Count], ISNULL(dbo.CallsOutstandingCount.[Calls Outstanding Count], 0)
AS [Calls Outstanding Count], ISNULL(dbo.CallsResolvedCount.CallResolvedCount, 0) AS CallResolvedCount, dbo.analgroup.sys_analgroup,
dbo.analgroup.sys_analgroup + N' Team Performance for Period Between:' AS Ttile,
'For requests between ' + dbo.CallsLoggedCount.ParamValueStart + ' and ' + dbo.CallsLoggedCount.ParamValueEnd AS [Between],
dbo.priority.sys_priority_id, dbo.priority.sys_prioritysla, dbo.CallsResolvedRemotelyCount.ResolvedRemotelyCount
FROM dbo.CallsLoggedCount INNER JOIN
dbo.CallsResolvedCount INNER JOIN
dbo.priority ON dbo.CallsResolvedCount.sys_priority_id = dbo.priority.sys_priority_id INNER JOIN
dbo.analgroup ON dbo.CallsResolvedCount.sys_analgroup = dbo.analgroup.sys_analgroup LEFT OUTER JOIN
dbo.CallsOutstandingCount ON dbo.analgroup.sys_analgroup = dbo.CallsOutstandingCount.sys_analgroup AND
dbo.priority.sys_priority_id = dbo.CallsOutstandingCount.sys_priority_id ON dbo.CallsLoggedCount.sys_priority_id = dbo.priority.sys_priority_id AND
dbo.CallsLoggedCount.sys_analgroup = dbo.analgroup.sys_analgroup INNER JOIN
dbo.CallsResolvedRemotelyCount ON dbo.priority.sys_priority_id = dbo.CallsResolvedRemotelyCount.sys_priority_id AND
dbo.analgroup.sys_analgroup = dbo.CallsResolvedRemotelyCount.sys_analgroup
WHERE (dbo.analgroup.sys_analgroup IS NOT NULL)
June 14, 2005 at 7:09 am
Never seen it written that way, but as long as it works for you .
June 14, 2005 at 7:24 am
What did you mean by "both tables' DDL"?
How would you go about creating somehting like this?
June 14, 2005 at 7:24 am
What did you mean by "both tables' DDL"?
How would you go about creating somehting like this?
June 14, 2005 at 7:29 am
DDL = Data definition language.
Go in enterprise manager, select the 2 tables then right-click, copy, then you'll be able to paste the results in the boards here.
Then I'd like to have the insert statements to populate the tables with some sample data (insert into a (b, c) values (2, 'f')...
June 14, 2005 at 7:54 am
This is the request table, from which the count queries are generated.
CREATE TABLE [request] (
[sys_request_id] [int] IDENTITY (1, 1) NOT NULL ,
[sys_requestdate] [smalldatetime] NULL ,
[sys_ownedby] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL CONSTRAINT [DF_request_sys_ownedby] DEFAULT (N'End User'),
[sys_eusername] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_asset_id] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_asset_location] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[sys_requesttype_id] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_problemsummary] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_solution_id] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_assignedto] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_assignedtoanalgroup] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_requeststatus] [int] NULL ,
[sys_solutiondesc] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[sys_problemdesc] [ntext] COLLATE Latin1_General_CI_AS NULL ,
[sys_requestclosedate] [smalldatetime] NULL ,
[sys_requestpriority] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_resolve] [smalldatetime] NULL ,
[sys_escalate1] [smalldatetime] NULL ,
[sys_escalate2] [smalldatetime] NULL ,
[sys_escalate3] [smalldatetime] NULL ,
[sys_resolve_emailed] [smallint] NULL ,
[sys_escalate1_emailed] [smallint] NULL ,
[sys_escalate2_emailed] [smallint] NULL ,
[sys_escalate3_emailed] [smallint] NULL ,
[sys_resolve_assigned] [smallint] NULL ,
[sys_escalate1_assigned] [smallint] NULL ,
[sys_escalate2_assigned] [smallint] NULL ,
[sys_escalate3_assigned] [smallint] NULL ,
[sys_closedonschedule] [smallint] NULL ,
[sys_expiryto] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_esc1to] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_esc2to] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_esc3to] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_siteid] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_requestparent_id] [int] NULL ,
[sys_requestlinktype] [int] NULL ,
[usr_itemserial] [nvarchar] (20) COLLATE Latin1_General_CI_AS NULL ,
[sys_blocksurvey] [int] NULL ,
[sys_source] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_requestclass_id] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_request_hdmins] [int] NULL ,
[sys_request_24mins] [int] NULL ,
[sys_request_timespent] [int] NULL ,
[usr_closedremotely] [nvarchar] (3) COLLATE Latin1_General_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[sys_request_id]
  WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK__request__sys_ass__25869641] FOREIGN KEY
(
[sys_assignedto]
  REFERENCES (
[sys_username]
 ,
FOREIGN KEY
(
[sys_assignedtoanalgroup]
  REFERENCES [analgroup] (
[sys_analgroup]
 ,
CONSTRAINT [FK__request__sys_eus__22AA2996] FOREIGN KEY
(
[sys_eusername]
  REFERENCES [euser] (
[sys_eusername]
 ,
CONSTRAINT [FK__request__sys_own__21B6055D] FOREIGN KEY
(
[sys_ownedby]
  REFERENCES (
[sys_username]
 ,
FOREIGN KEY
(
[sys_requesttype_id]
  REFERENCES [requesttype] (
[sys_requesttype_id]
 ,
FOREIGN KEY
(
[sys_requestpriority]
  REFERENCES [priority] (
[sys_priority_id]
 ,
FOREIGN KEY
(
[sys_requestparent_id]
  REFERENCES [request] (
[sys_request_id]
 ,
FOREIGN KEY
(
[sys_requestclass_id]
  REFERENCES [requestclass] (
[sys_requestclass_id]
 ,
CONSTRAINT [FK__request__sys_sit__286302EC] FOREIGN KEY
(
[sys_siteid]
  REFERENCES [site] (
[sys_siteid]
 ,
FOREIGN KEY
(
[sys_solution_id]
  REFERENCES [solution] (
[sys_solution_id]
 
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
There is also an analystgroup table (analgroup!):
CREATE TABLE [analgroup] (
[sys_analgroup] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[sys_escalate1email] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[sys_escalate2email] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[sys_escalate3email] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[sys_resolveemail] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[sys_analgroup]
  WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO
And finally a priority table:
CREATE TABLE [priority] (
[sys_priority_id] [nvarchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
[sys_prioritysla] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_resolvehours] [decimal](9, 2) NULL ,
[sys_escalate1hours] [decimal](9, 2) NULL ,
[sys_escalate2hours] [decimal](9, 2) NULL ,
[sys_escalate3hours] [decimal](9, 2) NULL ,
[sys_resolveemail] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[sys_escalate1email] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[sys_escalate2email] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[sys_escalate3email] [nvarchar] (255) COLLATE Latin1_General_CI_AS NULL ,
[sys_allowedit] [tinyint] NULL ,
[sys_autoresolve_assignto] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_autoesc1_assignto] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_autoesc2_assignto] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
[sys_autoesc3_assignto] [nvarchar] (50) COLLATE Latin1_General_CI_AS NULL ,
PRIMARY KEY CLUSTERED
(
[sys_priority_id]
  WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [FK__priority__sys_au__145C0A3F] FOREIGN KEY
(
[sys_autoresolve_assignto]
  REFERENCES (
[sys_username]
 ,
CONSTRAINT [FK__priority__sys_au__15502E78] FOREIGN KEY
(
[sys_autoesc1_assignto]
  REFERENCES (
[sys_username]
 ,
CONSTRAINT [FK__priority__sys_au__164452B1] FOREIGN KEY
(
[sys_autoesc2_assignto]
  REFERENCES (
[sys_username]
 ,
CONSTRAINT [FK__priority__sys_au__173876EA] FOREIGN KEY
(
[sys_autoesc3_assignto]
  REFERENCES (
[sys_username]
 
) ON [PRIMARY]
GO
Hope this helps....
June 14, 2005 at 8:07 am
I still need the script to insert the sample data.
Also you're aware that this system is denormalized (escalate1hours1, 2, 3)?
June 14, 2005 at 8:14 am
This is an out of the box product with a few inconsistancies.
We have to make do with the structure as the front end cannot be tinkered with...
What kind of script would you you use to populate data?
June 14, 2005 at 8:17 am
Insert into tableX (col1, col2) values (1, 2)
Insert into tableX (col1, col2) values (3, 4)
Insert into tableX (col1, col2) values (5, 6)
Insert into tableY (col1, col2) values ('a', 'b')
Insert into tableY (col1, col2) values ('c', 'd')
Insert into tableY (col1, col2) values ('e', 'f')
June 14, 2005 at 8:30 am
Is there any way to automate this from SQL server?
June 14, 2005 at 8:38 am
Yes :
Create table A
(
DateS datetime,
Name varchar(10),
number int
)
Insert into dbo.A (DateS, name, number) values (GetDate(), 'a', 1)
Insert into dbo.A (DateS, name, number) values (GetDate() + 1, 'b', 3)
Insert into dbo.A (DateS, name, number) values (GetDate() - 5, 'c', 5)
Insert into dbo.A (DateS, name, number) values (GetDate() + 3, 'd', 7)
Select * from A
Select 'Insert into dbo.A (DateS, name, number) values (''' + CONVERT(Varchar(25), DateS, 113) + ''', ''' + name + ''', ' + CAST(Number as varchar(10)) + ')' from dbo.A
--
Insert into dbo.A (DateS, name, number) values ('14 juin 2005 10:22:26:620', 'a', 1)
Insert into dbo.A (DateS, name, number) values ('15 juin 2005 10:22:26:620', 'b', 3)
Insert into dbo.A (DateS, name, number) values ('09 juin 2005 10:22:26:620', 'c', 5)
Insert into dbo.A (DateS, name, number) values ('17 juin 2005 10:22:26:620', 'd', 7)
--
Select * from A
DROP table A
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy