0

I am capturing the web request response success / failure of multiple URLs in a MSSQL table. This is captured every minute. For reporting, trying to display the success / failure status in a tabular form. Having issue in the select statement.

Table Structure:

[PingDateTime] [datetime2](7) NOT NULL,
    [URLTested] [nvarchar](256) NOT NULL,
    [ResponseCode] [char](1) NOT NULL,
    [ExitStatus] [nvarchar](max) NOT NULL,
    [TimeTakenInMS] [int] NOT NULL,
    [StatusDescription] [nvarchar](max) NOT NULL,
    [ResponseIP] [nvarchar](256) NULL

The select query i am trying is like the below.

select
b.URLTested,B.PingDateTime,
'00' = (case when max(datepart(MM,B.PingDateTime)) = 00 THEN 1 else 0 END)
'01' = (case when max(datepart(MM,B.PingDateTime)) = 01 THEN 1 else 0 END)
...
...
FROM    URLPINGRESPONSEINFO B (nolock), 
[EQUITAS_IT].[dbo].[M_URL_MONITOR] A (nolock)
where B.PingDateTime like '2022-06-04 00%' 
group by b.URLTested, datepart(MM,B.PingDateTime)
order by URLTested, PingDateTime

The exact issue I am facing is in selecting the column "00" (which represents minutes).

In the output, I am trying to display the below column.

URL, Min 0 response code, Min 0 response time, Min 1 Response code, Min 1 response time,....Min 59 response code, Min 59 response time.

Basically trying to create a dashboard for the current hour. The column header that I wish to see in my output data is

URLTested, 00_ResponseCode,00_Exitstatus, 00_Timetakenin MS, 01_ResponseCode,01_Exitstatus, 01_Timetakenin MS..

There will be one entry for every URL for every minute.

Note: I cannot take every record in UI for display purpose due to the count.

Please suggest if there is an alternative for NOLOCK.

Sudharsan Simhan
  • 21
  • 8
  • 1
    Can you post some sample data and expected outcome, this would clear things up for me – GuidoG Jun 09 '22 at 12:22
  • The value you want seems like something for your presentation layer, not the SQL layer. – Thom A Jun 09 '22 at 12:25
  • `group by b.URLTested, dateadd(hour, datediff(hour, 0, B.PingDateTime), 0)` then `select b.URLTested, dateadd(hour, datediff(hour, 0, B.PingDateTime), 0), (case when...` – Charlieface Jun 09 '22 at 12:31
  • 1
    Stop splattering your code with [nolock](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere). If you must, AT LEAST use current (not deprecated) syntax. – SMor Jun 09 '22 at 12:33
  • While we're mentioning bad habits (of `NOLOCK`), we might as well mention that it's now 2022, so you've had **30 years** to adopt the ANSI-92 JOIN syntax. Why haven't you? [Bad Habits to Kick : Using old-style JOINs](//sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) Also, the alias `A` for `URLPINGRESPONSEINFO` and `B` for `[EQUITAS_IT].[dbo].[M_URL_MONITOR]` make no sense; neither have an `A` or `B` respectively. [Bad Habits to Kick : Using table aliases like (a, b, c) or (t1, t2, t3)](//sqlblog.org/2009/10/08/bad-habits-to-kick-using-table-aliases-like-a-b-c-or-t1-t2-t3) – Thom A Jun 09 '22 at 13:10
  • Friends... Sorry.. I am not a SQL guy. Hence i am not even aware that nolock is depricated. I just found some legacy code from which I am trying to make make some useful query to make my life eazy. – Sudharsan Simhan Jun 16 '22 at 11:52

0 Answers0