0

I tried to search in existing queries but didn't find any related thread. I would like to run a query in user table (for example), and like to get output result on per second basis. My query gives me zero (0) result if no second found.

Here is my query:

select 
            call_start = convert(varchar(50),dateadd(SS,datediff(SS,'09/29/2014',[session_datetime]) + 1,0),108),
            rows = count(*)  
  from [User_Table]  
      where [session_datetime] >= ('09/29/2014' + DATEADD(mi, -3, CONVERT(varchar(50), GETDATE(),108))) 
    and [session_datetime]<= ('09/29/2014' + DATEADD(mi, -1, CONVERT(varchar(50), GETDATE(),108)))  
     group by convert(varchar(50),dateadd(SS,datediff(SS,'09/29/2014',[session_datetime]) + 1,0),108)

and current output/result of the query is like:

15:10:06    1 
15:10:07    1 
15:10:10    1

Here you can see that for 15:10:08 and 09 there is no entry shown, because no record exists in the database. How can I bring these entries as well with zero entry, like this:

15:10:06    1 
15:10:07    1 
15:10:08    0
15:10:09    0
15:10:10    1
2
0

How about creating a table holding all times for a day and using it to drive the query?

In this simple demo, I set up a table similar to yours, load it with data, then use the dbo.timeLookup table to drive it. The example assumes you are only working with one day, but it could be extended if your timespan needed to span multiple days:

USE tempdb
GO

SET NOCOUNT ON

-- Create a large table
IF OBJECT_ID('dbo.user_table') IS NOT NULL DROP TABLE dbo.user_table
CREATE TABLE dbo.user_table ( 
    rowId INT IDENTITY PRIMARY KEY, 
    someData UNIQUEIDENTIFIER DEFAULT NEWID(), 
    session_datetime DATETIME DEFAULT GETDATE(), 
    addedBy VARCHAR(30) DEFAULT SUSER_NAME(), 
    ts ROWVERSION
    )
GO


IF OBJECT_ID('dbo.timeLookup') IS NOT NULL DROP TABLE dbo.timeLookup
CREATE TABLE dbo.timeLookup ( 
    [time]      TIME ,
    [hour]      AS DATEPART( hour, [time] ),
    [minute]    AS DATEPART( minute, [time] ),
    [second]    AS DATEPART( second, [time] ),

    CONSTRAINT PK_dbo_timeLookup PRIMARY KEY ( [time] ),
    CONSTRAINT UK_dbo_timeLookup UNIQUE ( [hour], [minute], [second] )
    )
GO


-- Load up some dummy data
;WITH cte AS (
SELECT TOP 1000000 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.user_table ( session_datetime )
SELECT DATEADD( second, rn % 86400, '09/29/2014' )
FROM cte
WHERE rn % 33 != 0  
GO

-- Suggested index
CREATE INDEX _idx ON dbo.user_table ( session_datetime )
GO

-- Populate the timeLookup table
;WITH cte AS (
SELECT TOP 86400 ROW_NUMBER() OVER ( ORDER BY ( SELECT 1 ) ) rn
FROM master.sys.columns c1
    CROSS JOIN master.sys.columns c2
    CROSS JOIN master.sys.columns c3
)
INSERT INTO dbo.timeLookup ( [time] )
SELECT DATEADD( second, rn, '1 Jan 2014' )
FROM cte
GO

-- Remove certain seconds for the demo
DELETE dbo.user_table
WHERE  CAST( session_datetime AS TIME ) In ( '15:10:08', '15:10:09' )

-- Have a look at the sample records loaded
--SELECT MIN(session_datetime) xmin, MAX(session_datetime) xmax, COUNT(*) records
--FROM [User_Table]


-- Main query
SELECT tl.[time], COUNT(ut.session_datetime) AS records
FROM dbo.timeLookup tl
    LEFT JOIN dbo.user_table ut ON tl.[time] = CAST( ut.session_datetime AS TIME )
        AND ut.[session_datetime] Between '2014-09-29 00:00:00.000' And '2014-09-29 23:59:59.999'
WHERE tl.[time] Between '15:10:06' And '15:10:10'
GROUP BY tl.[time]

Result:

enter image description here

2
  • thanks its working fine, but i am looking for a solution where i didnt need to create a table with every second in a day data. and a view or function generate desire data. Oct 28 '14 at 16:55
  • This is possible, but from experience you might be better of with a table, especially if you are doing this regularly. If you really need to do as view, just look at the statement I've used to populate the time table and adapt it.
    – wBob
    Oct 28 '14 at 17:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.