CODEWITHSUNDEEP
sqlsql-server
Sabyasachi Mishra
Sabyasachi Mishra

Reputation: 1749

How to get records of last working day using sql server

I need to query an SQL database to get all purchased products on last working date. For example, on Monday I need to get the records of Saturday. Also suppose due to some reason Tuesday is my leave I need to get the records of Monday. I tried my query like:

select ProductName , count(ProductName) as [No Of Products],SUM(Total) as [TotalCost] 
from dbo.TransactionDetails 
where CurrentTime >=dateadd(day,datediff(day,1,GETDATE()),0) 
    AND CurrentTime <dateadd(day,datediff(day,0,GETDATE()),0) 
    and  Transaction='BUY' 
group by ProductName

But here I am only getting the details of last date.

Also I tried by checking on my last closed shifts:

select ProductName , count(ProductName) as [No Of Products],SUM(Total) as [TotalCost] 
from dbo.TransactionDetails 
where CurrentTime = (select MAX(CurrentDateTime) from CloseShifts) and  Transaction='BUY' 
group by ProductName

But the problem is here I am not getting any result as the below query returns DateTime and on that particular time I don't have any transactions.

select MAX(CurrentDateTime) 
from CloseShifts

Upvotes: 0

Views: 449

Answers (3)

wowbaggerza
wowbaggerza

Reputation: 11

If I'm reading this correctly, you need to drop the "Time" part of your DateTime columns, so how about casting both DateTimes as Dates, e.g.:

... where Cast(CurrentTime as DATE) = (select MAX(Cast(CurrentDateTime as DATE)) ...

Upvotes: 1

Gin Uchiha
Gin Uchiha

Reputation: 574

select ProductName , count(ProductName), SUM(Total), MAX(CurrentDateTime) from "dbo.TransactionDetails" inner join with CloseShifts on your condition between two table (because you diid not provides the table)"

Upvotes: 3

SimarjeetSingh Panghlia
SimarjeetSingh Panghlia

Reputation: 2200

Try this

 select ProductName , count(ProductName) as [No Of Products],SUM(Total) as [TotalCost] 
    from dbo.TransactionDetails 
    where CurrentTime >=dateadd(day,datediff(day,1,(select MAX(CurrentDateTime) 
    from CloseShifts)),0) 
    AND CurrentTime <dateadd(day,datediff(day,0,(select MAX(CurrentDateTime) 
    from CloseShifts)),0) 
    and  Transaction='BUY' 
    group by ProductName

Upvotes: 1

Related Questions