How to display 0 in a count query if no records?

  • 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

     

  • Do you mean that you want to have a line for each priority even if no calls were taken with the priority?

  • Thats exactly what I need to get....:

  • Can I get both tables' DDL, with a few inserts statements for the sample data (don't wanna guess the column's name) ?

  • 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)

  • Never seen it written that way, but as long as it works for you .

  • What did you mean by "both tables' DDL"?

    How would you go about creating somehting like this?

     

     

  • What did you mean by "both tables' DDL"?

    How would you go about creating somehting like this?

     

     

  • 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')...

  • 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]

    &nbsp WITH  FILLFACTOR = 90  ON [PRIMARY] ,

     CONSTRAINT [FK__request__sys_ass__25869641] FOREIGN KEY

     (

      [sys_assignedto]

    &nbsp REFERENCES (

      [sys_username]

    &nbsp,

      FOREIGN KEY

     (

      [sys_assignedtoanalgroup]

    &nbsp REFERENCES [analgroup] (

      [sys_analgroup]

    &nbsp,

     CONSTRAINT [FK__request__sys_eus__22AA2996] FOREIGN KEY

     (

      [sys_eusername]

    &nbsp REFERENCES [euser] (

      [sys_eusername]

    &nbsp,

     CONSTRAINT [FK__request__sys_own__21B6055D] FOREIGN KEY

     (

      [sys_ownedby]

    &nbsp REFERENCES (

      [sys_username]

    &nbsp,

      FOREIGN KEY

     (

      [sys_requesttype_id]

    &nbsp REFERENCES [requesttype] (

      [sys_requesttype_id]

    &nbsp,

      FOREIGN KEY

     (

      [sys_requestpriority]

    &nbsp REFERENCES [priority] (

      [sys_priority_id]

    &nbsp,

      FOREIGN KEY

     (

      [sys_requestparent_id]

    &nbsp REFERENCES [request] (

      [sys_request_id]

    &nbsp,

      FOREIGN KEY

     (

      [sys_requestclass_id]

    &nbsp REFERENCES [requestclass] (

      [sys_requestclass_id]

    &nbsp,

     CONSTRAINT [FK__request__sys_sit__286302EC] FOREIGN KEY

     (

      [sys_siteid]

    &nbsp REFERENCES [site] (

      [sys_siteid]

    &nbsp,

      FOREIGN KEY

     (

      [sys_solution_id]

    &nbsp REFERENCES [solution] (

      [sys_solution_id]

    &nbsp

    ) 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]

    &nbsp 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]

    &nbsp WITH  FILLFACTOR = 90  ON [PRIMARY] ,

     CONSTRAINT [FK__priority__sys_au__145C0A3F] FOREIGN KEY

     (

      [sys_autoresolve_assignto]

    &nbsp REFERENCES (

      [sys_username]

    &nbsp,

     CONSTRAINT [FK__priority__sys_au__15502E78] FOREIGN KEY

     (

      [sys_autoesc1_assignto]

    &nbsp REFERENCES (

      [sys_username]

    &nbsp,

     CONSTRAINT [FK__priority__sys_au__164452B1] FOREIGN KEY

     (

      [sys_autoesc2_assignto]

    &nbsp REFERENCES (

      [sys_username]

    &nbsp,

     CONSTRAINT [FK__priority__sys_au__173876EA] FOREIGN KEY

     (

      [sys_autoesc3_assignto]

    &nbsp REFERENCES (

      [sys_username]

    &nbsp

    ) ON [PRIMARY]

    GO

    Hope this helps....

  • I still need the script to insert the sample data.

    Also you're aware that this system is denormalized (escalate1hours1, 2, 3)?

  • 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?

  • 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')

  • Is there any way to automate this from SQL server?

  • 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