It works but I know it is still wrong ...

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

It works but I know it is still wrong ...

Author

Topic

tinlar
Starting Member

1 Post

Posted - 2013-05-31 : 16:56:23

You ever put something together on your own and look at it and see that it is fully functional but you just know that if a professional looks at it then they will die laughing? Ya... I just had one of those moments.

The task at hand is to look at the tickets in our database and determine which ones are out of our published SLA based on the severity that the tickets are set to. Weekends are excluded from our SLA, which complicates matters a bit but not so much I can't work around it.

What blows me out of the water is that we decide if a case in in SLA based on the last time it had an outbound email sent from it or a public view-able updated added to it. That's a many to one relationship as each ticket will have many activities; but I only care about the time stamp on the most recent activity.

Sounds simple enough but I ended up with a dozen or so queries inside of my query and well .. What I've got, and it works, is a mess. Any pointers to clean it up would be appreciated ...


SELECT
  h.SR_NUM,
  SUBSTRING(h.SR_NUM,3,LEN(h.SR_NUM)-2) AS "SR_NO1DASH",
  ISNULL((Select TOP 1 CREATED FROM SVLCORPSILO1.MEGASILO.dbo.SR_ACTIVITY a where h.SR_NUM = a.SR_NUM AND a.[Type] NOT IN ('Web Update', 'Email - Inbound')AND (a.Type = 'Email - Outbound' OR a.[Internal] = 'Public') ORDER BY CREATED desc), h.Date_Open) as LastSLAActivity,
  h.End_User,
  h.[Status],
  h.Severity,
  h.Feature,
  h.SubFeatComp,
  h.[LOGIN] as "Owner",
  h.[Priority],
  h.Date_Open,
  h.SR_TITLE,
  h.Sub_Status,
  h.SR_AREA,
  h.[Partner],
  h.SR_Version,
  h.Support_Prog_for_SR,
  h.ContactId,
  DATEDIFF("dd",(Select TOP 1 CREATED FROM SVLCORPSILO1.MEGASILO.dbo.SR_ACTIVITY a where h.SR_NUM = a.SR_NUM AND a.[Type] NOT IN ('Web Update', 'Email - Inbound')AND (a.Type = 'Email - Outbound' OR a.[Internal] = 'Public')ORDER BY CREATED desc),getdate()) as "DSLT",
  e.Team,
  e.JobTitle,
  e.EmpGroup,
  c.WorkPhone as "SR_Contact_Phone",
  c.FName as "SR_Contact_FName",
  c.LName as "SR_Contact_LName",
  c.Email as "SR_Contact_Email",
  (c.FName + ' ' + c.LName) AS "SR_Contact_Name",
  DATEDIFF("hh",(Select TOP 1 CREATED FROM SVLCORPSILO1.MEGASILO.dbo.SR_ACTIVITY a where h.SR_NUM = a.SR_NUM AND a.[Type] NOT IN ('Web Update', 'Email - Inbound')AND (a.Type = 'Email - Outbound' OR a.[Internal] = 'Public')ORDER BY CREATED desc),getdate()) as "BzHrsSinceLastAct"
FROM 
  SVLCORPSILO1.MEGASILO.dbo.SR_HEADER h
  JOIN SVLCORPSILO1.MEGASILO.dbo.Employees e on h.[LOGIN] = e.[Login]
  JOIN SVLCORPSILO1.MEGASILO.dbo.CONTACTS c on h.ContactId = c.ContactId
WHERE 1=1
  AND h.[Status] IN ('Open', 'Re-Open')
  AND h.Sub_Status NOT IN ('Pending Review')
  AND h.Feature NOT IN ('Mobility','Contact Center/Conf Bridge')
  AND h.SR_AREA NOT IN ('Enhancement')
  AND e.Team IS NOT NULL
  AND e.Team NOT IN ('NPI', 'TAM/MAP - Smith')
  AND DATEDIFF("hour",(Select TOP 1 CREATED FROM SVLCORPSILO1.MEGASILO.dbo.SR_ACTIVITY a where h.SR_NUM = a.SR_NUM AND a.[Internal] = 'Public' AND a.[Type] NOT IN ('Web Update', 'Email - Inbound') ORDER BY CREATED desc),getdate())>=24
  AND 0=
    CASE h.Severity 
      WHEN 'Low' THEN 
        CASE WHEN DATEDIFF("hour",(Select TOP 1 CREATED FROM SVLCORPSILO1.MEGASILO.dbo.SR_ACTIVITY a where h.SR_NUM = a.SR_NUM AND a.[Type] NOT IN ('Web Update', 'Email - Inbound')AND (a.Type = 'Email - Outbound' OR a.[Internal] = 'Public')ORDER BY CREATED desc),getdate())>=168 THEN 0 ELSE 1 END
      WHEN 'Medium' THEN 
        CASE WHEN DATENAME("dw",getdate()) = 'Monday' THEN 
          CASE WHEN DATEDIFF("hour",(Select TOP 1 CREATED FROM SVLCORPSILO1.MEGASILO.dbo.SR_ACTIVITY a where h.SR_NUM = a.SR_NUM AND a.[Type] NOT IN ('Web Update', 'Email - Inbound')AND (a.Type = 'Email - Outbound' OR a.[Internal] = 'Public')ORDER BY CREATED desc),getdate())>=120 THEN 0 ELSE 1 END
        WHEN DATENAME("dw",getdate()) = 'Tuesday' THEN 
          CASE WHEN DATEDIFF("hour",(Select TOP 1 CREATED FROM SVLCORPSILO1.MEGASILO.dbo.SR_ACTIVITY a where h.SR_NUM = a.SR_NUM AND a.[Type] NOT IN ('Web Update', 'Email - Inbound')AND (a.Type = 'Email - Outbound' OR a.[Internal] = 'Public')ORDER BY CREATED desc),getdate())>=120 THEN 0 ELSE 1 END
        WHEN DATENAME("dw",getdate()) = 'Wednesday' THEN 
          CASE WHEN DATEDIFF("hour",(Select TOP 1 CREATED FROM SVLCORPSILO1.MEGASILO.dbo.SR_ACTIVITY a where h.SR_NUM = a.SR_NUM AND a.[Type] NOT IN ('Web Update', 'Email - Inbound')AND (a.Type = 'Email - Outbound' OR a.[Internal] = 'Public')ORDER BY CREATED desc),getdate())>=120 THEN 0 ELSE 1 END
        ELSE 
          CASE WHEN DATEDIFF("hour",(Select TOP 1 CREATED FROM SVLCORPSILO1.MEGASILO.dbo.SR_ACTIVITY a where h.SR_NUM = a.SR_NUM AND a.[Type] NOT IN ('Web Update', 'Email - Inbound')AND (a.Type = 'Email - Outbound' OR a.[Internal] = 'Public')ORDER BY CREATED desc),getdate())>=72 THEN 0 ELSE 1 END 
        END
      WHEN 'High' THEN 
        CASE WHEN DATENAME("dw",getdate()) = 'Monday' THEN 
          CASE WHEN DATEDIFF("hour",(Select TOP 1 CREATED FROM SVLCORPSILO1.MEGASILO.dbo.SR_ACTIVITY a where h.SR_NUM = a.SR_NUM AND a.[Type] NOT IN ('Web Update', 'Email - Inbound')AND (a.Type = 'Email - Outbound' OR a.[Internal] = 'Public')ORDER BY CREATED desc),getdate())>=96 THEN 0 ELSE 1 END
        WHEN DATENAME("dw",getdate()) = 'Tuesday' THEN 
          CASE WHEN DATEDIFF("hour",(Select TOP 1 CREATED FROM SVLCORPSILO1.MEGASILO.dbo.SR_ACTIVITY a where h.SR_NUM = a.SR_NUM AND a.[Type] NOT IN ('Web Update', 'Email - Inbound')AND (a.Type = 'Email - Outbound' OR a.[Internal] = 'Public')ORDER BY CREATED desc),getdate())>=96 THEN 0 ELSE 1 END
        ELSE 
          CASE WHEN DATEDIFF("hour",(Select TOP 1 CREATED FROM SVLCORPSILO1.MEGASILO.dbo.SR_ACTIVITY a where h.SR_NUM = a.SR_NUM AND a.[Type] NOT IN ('Web Update', 'Email - Inbound')AND (a.Type = 'Email - Outbound' OR a.[Internal] = 'Public')ORDER BY CREATED desc),getdate())>=48 THEN 0 ELSE 1 END 
        END
      WHEN 'Critical' THEN 
        CASE WHEN DATENAME("dw",getdate()) = 'Monday' THEN 
          CASE WHEN DATEDIFF("hour",(Select TOP 1 CREATED FROM SVLCORPSILO1.MEGASILO.dbo.SR_ACTIVITY a where h.SR_NUM = a.SR_NUM AND a.[Type] NOT IN ('Web Update', 'Email - Inbound')AND (a.Type = 'Email - Outbound' OR a.[Internal] = 'Public')ORDER BY CREATED desc),getdate())>=72 THEN 0 ELSE 1 END
        ELSE 
          CASE WHEN DATEDIFF("hour",(Select TOP 1 CREATED FROM SVLCORPSILO1.MEGASILO.dbo.SR_ACTIVITY a where h.SR_NUM = a.SR_NUM AND a.[Type] NOT IN ('Web Update', 'Email - Inbound')AND (a.Type = 'Email - Outbound' OR a.[Internal] = 'Public')ORDER BY CREATED desc),getdate())>=24 THEN 0 ELSE 1 END 
        END
      ELSE 0
    END
ORDER BY DATEDIFF("hour",(Select TOP 1 CREATED FROM SVLCORPSILO1.MEGASILO.dbo.SR_ACTIVITY a where h.SR_NUM = a.SR_NUM AND a.[Type] NOT IN ('Web Update', 'Email - Inbound')AND (a.Type = 'Email - Outbound' OR a.[Internal] = 'Public')ORDER BY CREATED desc),getdate()) DESC

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-06-01 : 05:31:15

I dont think anybody will have the time and patience to look into the query and suggest something
why not post some sample data and explain what you want giving output?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-06-01 : 09:46:59

Hi tinlar,

Syntax of my suggestion is probably off, as I don't have access to my database. But from the top of my head (and thanks to notepad), I suggest:


select h.sr_num
      ,substring(h.sr_num,3,len(h.sr_num)-2) as sr_no1dash
      ,isnull(a1.created,h.date_open) as lastslaactivity
      ,h.end_user
      ,h.status
      ,h.severity
      ,h.feature
      ,h.subfeatcomp
      ,h.login as owner
      ,h.priority
      ,h.date_open
      ,h.sr_title
      ,h.sub_status
      ,h.sr_area
      ,h.partner
      ,h.sr_version
      ,h.support_prog_for_sr
      ,h.contactid
      ,datediff('dd',a1.created,getdate()) as dslt
      ,e.team
      ,e.jobtitle
      ,e.empgroup
      ,c.workphone as sr_contact_phone
      ,c.fname as sr_contact_fname
      ,c.lname as sr_contact_lname
      ,c.email as sr_contact_email
      ,c.fname+' '+c.lname) as sr_contact_name
      ,a2.created_hh_diff as bzhrssincelastact
  from svlcorpsilO1.megasilo.dbo.sr_header as h
       inner join svlcorpsilO1.megasilo.dbo.employees as e
               on e.login=h.login
              and e.team is not null
              and e.team not in ('NPI','TAM/MAP - Smith')
       inner join svlcorpsilO1.megasilo.dbo.contacts as c
               on c.contactid=h.contactid
       left outer join (select sr_num
                              ,max(created) as created
                              ,datediff('hh',max(created),getdate()) as created_hh_diff
                          from svlcorpsilO1.megasilo.dbo.sr_activity
                         where type='Email - Outbound'
                            or (internal='Public'
                           and  type not in ('Web Update','Email - Inbound')
                               )
                         group by sr_num
                       ) as a1
                    on a1.sr_num=h.sr_num
       left outer join (select sr_num
                              ,max(created) as created
                              ,datediff('hh',max(created),getdate()) as created_hh_diff
                          from svlcorpsilO1.megasilo.dbo.sr_activity
                         where internal='Public'
                           and type not in ('Web Update','Email - Inbound')
                         group by sr_num
                       ) as a2
                    on a2.sr_num=h.sr_num
 where h.status in ('Open','Re-Open')
   and h.sub_status not in ('Pending Review')
   and h.feature not in ('Mobility','Contact Center/Conf Bridge')
   and h.sr_area not in ('Enhancement')
   and datediff('hh',a2.created,getdate())>=24
   and 0=case h.severity
            when 'Low'
               then case when a1.created_hh_diff>=168 then 0 else 1 end
            when 'Medium'
               then case when datepart('dw',getdate()) in (1,2,3)
                       then case when a1.created_hh_diff>=120 then 0 else 1 end
                       else case when a1.created_hh_diff>=72 then 0 else 1 end 
                    end
            when 'High'
               then case when datepart('dw',getdate()) in (1,2)
                       then case when a1.created_hh_diff>=96 then 0 else 1 end
                       else case when a1.created_hh_diff>=48 then 0 else 1 end
                    end
            when 'Critical'
               then case when datepart('dw',getdate())=1
                       then case when a1.created_hh_diff>=72 then 0 else 1 end
                       else case when a1.created_hh_diff>=24 then 0 else 1 end
                    end
            else 0
         end
 order by a1.created_hh_diff desc

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-06-01 : 10:08:33

Darn bitsmed! You beat me to it.

I was thinking along the same lines - i.e., pulling the long subquery into a join (I wrote it as an OUTER APPLY) than left join.

But, I was struggling to keep it together without having the benefit of any insight into the underlying data - very impressive that you were able to write all of that in note pad.

When I parsed your code, it complained about a few minor syntax errors, which I have corrected below.

select h.sr_num
      ,substring(h.sr_num,3,len(h.sr_num)-2) as sr_no1dash
      ,isnull(a1.created,h.date_open) as lastslaactivity
      ,h.end_user
      ,h.status
      ,h.severity
      ,h.feature
      ,h.subfeatcomp
      ,h.login as owner
      ,h.priority
      ,h.date_open
      ,h.sr_title
      ,h.sub_status
      ,h.sr_area
      ,h.partner
      ,h.sr_version
      ,h.support_prog_for_sr
      ,h.contactid
      ,datediff(dd,a1.created,getdate()) as dslt
      ,e.team
      ,e.jobtitle
      ,e.empgroup
      ,c.workphone as sr_contact_phone
      ,c.fname as sr_contact_fname
      ,c.lname as sr_contact_lname
      ,c.email as sr_contact_email
      ,(c.fname+' '+c.lname) as sr_contact_name
      ,a2.created_hh_diff as bzhrssincelastact
  from svlcorpsilO1.megasilo.dbo.sr_header as h
       inner join svlcorpsilO1.megasilo.dbo.employees as e
               on e.login=h.login
              and e.team is not null
              and e.team not in ('NPI','TAM/MAP - Smith')
       inner join svlcorpsilO1.megasilo.dbo.contacts as c
               on c.contactid=h.contactid
       left outer join (select sr_num
                              ,max(created) as created
                              ,datediff(hh,max(created),getdate()) as created_hh_diff
                          from svlcorpsilO1.megasilo.dbo.sr_activity
                         where type='Email - Outbound'
                            or (internal='Public'
                           and  type not in ('Web Update','Email - Inbound')
                               )
                         group by sr_num
                       ) as a1
                    on a1.sr_num=h.sr_num
       left outer join (select sr_num
                              ,max(created) as created
                              ,datediff(hh,max(created),getdate()) as created_hh_diff
                          from svlcorpsilO1.megasilo.dbo.sr_activity
                         where internal='Public'
                           and type not in ('Web Update','Email - Inbound')
                         group by sr_num
                       ) as a2
                    on a2.sr_num=h.sr_num
 where h.status in ('Open','Re-Open')
   and h.sub_status not in ('Pending Review')
   and h.feature not in ('Mobility','Contact Center/Conf Bridge')
   and h.sr_area not in ('Enhancement')
   and datediff(hh,a2.created,getdate())>=24
   and 0=case h.severity
            when 'Low'
               then case when a1.created_hh_diff>=168 then 0 else 1 end
            when 'Medium'
               then case when datepart(dw,getdate()) in (1,2,3)
                       then case when a1.created_hh_diff>=120 then 0 else 1 end
                       else case when a1.created_hh_diff>=72 then 0 else 1 end 
                    end
            when 'High'
               then case when datepart(dw,getdate()) in (1,2)
                       then case when a1.created_hh_diff>=96 then 0 else 1 end
                       else case when a1.created_hh_diff>=48 then 0 else 1 end
                    end
            when 'Critical'
               then case when datepart(dw,getdate())=1
                       then case when a1.created_hh_diff>=72 then 0 else 1 end
                       else case when a1.created_hh_diff>=24 then 0 else 1 end
                    end
            else 0
         end
 order by a1.created_hh_diff desc

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-06-01 : 10:11:24

Hi James,

Thanks for syntax correcting ;-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources