Home SQL query to count data from specific day and time, grouped by classification

Questions

SQL query to count data from specific day and time, grouped by classification

November 24, 2023

I have a database table with this data:

PurchaseDate = <date & time>
Classification = <string>
Quantity = <integer>

I need to create report on how many Quantity per day, per Classification, with PurchaseDate between 14:00 and 17:00.

Anyone can help?

Using Microsoft SQL Server

>Solution :

Something like this perhaps:

Testdata:

create table #class (
    PurchaseDate datetime
    , Classification varchar(30)
    , Quantity int
)

insert into #class (
    PurchaseDate
    , Classification
    , Quantity
)
select top 100 cast('20231124 15:00' as datetime) + (10 % row_number() over(order by @@spid))  + (1.0 / row_number() over(order by @@spid))
    , char(65 + row_number() over(order by @@spid) % 3)
    , ((100 + row_number() over(order by NEWID())) % 17)
from sys.objects so

The code:

select cast(purchasedate as date), classification, sum(quantity)
from #class
where cast(purchasedate as time) between '14:00' and '17:00'
group by cast(purchasedate as date), classification

It’s a simple group by date. To get the times, I use the cast as time