How to get transactions between yesterday and today in SQL Server 2014?

Question

I am trying to pull up a count of all transactions that happened yesterday per hour. I don't want transactions made today. But I can't seem to get the code right in the condition.

My code so far:

SELECT  CAST(TransDT as DATE) as 'TransDate' 
       ,CAST(DATEPART(Hour, TransDT) as varchar) + ':00' as 'Hour' 
       ,LaneID
       ,Direction
       ,COUNT(*) as 'Ct'

FROM Traffic_analysis

WHERE TransDT >= DATEADD (DAY, -1 , SYSDATETIME())
  AND TransDT < CAST(DATEPART(DAY, SYSDATETIME()) as int) --This is the faulty line.

GROUP BY CAST(TransDT as DATE), DATEPART(Hour, TransDT), LaneID, Direction
ORDER BY CAST(TransDT as DATE) ASC

If I comment out the entire faulty line, I get results for yesterday and today as well. I only need results for yesterday.

What I'm trying to do is show a report with a graph that shows traffic per hour, per lane, per direction. It would be nicer to have all NB traffic in one column and all SB traffic in another column. Maybe a UNION join?

Answer 1

have you tried this?

WHERE cast(TransDT as date) > DATEADD (DAY, -2 , cast(SYSDATETIME() as date))
  AND cast(TransDT as date) <= DATEADD (DAY, -1 , cast(SYSDATETIME() as date))  

Actually, this is enougth I think

WHERE cast(TransDT as date) = DATEADD (DAY, -1 , cast(SYSDATETIME() as date))