CODEWITHSUNDEEP
c#sqlsql-servervisual-studio
Megha Rao
Megha Rao

Reputation: 31

sql query to count data entry of each hour

I want to display the count of data entry of the present day during each hour(1 hour time interval) for a particular line and finally the cumulative of all the hours. Table details: 

CREATE TABLE [dbo].[ProductTable] (
    [Id]         INT          IDENTITY (1, 1) NOT NULL,
    [ProductID]  INT          NOT NULL,
    [EmployeeID] INT          NOT NULL,
    [Operation]  VARCHAR (50) NOT NULL,
    [Section]    NCHAR (10)   NOT NULL,
    [Line]       INT          NOT NULL,
    [Date]       DATETIME     DEFAULT (getdate()) NULL,
    PRIMARY KEY CLUSTERED ([Id] ASC),
);

I am using Microsoft Visual Studio SQL server I have written so much but don't know to generalise for every hour

SELECT COUNT(Id) AS Expr1
FROM ProductTable
WHERE (Line = 2)
AND (CONVERT(VARCHAR(10), Date, 105) = CONVERT(VARCHAR(10), GETDATE(), 105))
AND (DATEPART(hour, GETDATE()) BETWEEN 9 AND 10)`

ProductTable data ProductTable Data

Error on using "case" Error on using case

Upvotes: 3

Views: 515

Answers (2)

JamieA
JamieA

Reputation: 2013

it sounds like you want to group by the hour of the day

SELECT      DATEPART(hour, date) as TimeOfDay,  COUNT(Id) AS Entries
FROM            ProductTable
WHERE        (Line = 2) AND cast ([date] as date) =cast (getdate() as date) 
GROUP BY DATEPART(hour, date)

To get the cumulative of all hours of the day, use grouping sets

      SELECT       ISNULL(cast(DATEPART(hour,[date]) as varchar(5)),'Total') as TimeOfDay, COUNT(Id) AS Entries
        FROM            ProductTable
        WHERE        (Line = 2) AND cast ([date] as date) =cast (getdate() as date)  
        GROUP BY GROUPING SETS (DATEPART(hour, [date]) , ())
ORDER BY   ISNULL(cast(DATEPART(hour, [date]) as varchar(5)),'Total')

Upvotes: 1

Nazir Ullah
Nazir Ullah

Reputation: 610

simply use group by clause 

SELECT        COUNT(Id) AS Expr1
FROM            ProductTable 
where (Line = 2) AND (CONVERT(Date,[Date])) = CONVERT(DATE,GETDATE()) 
GROUP BY DATEPART(HOUR, Date)

Upvotes: 0

Related Questions