0

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?

halfer
  • 19,824
  • 17
  • 99
  • 186
user1777929
  • 777
  • 1
  • 10
  • 28

3 Answers3

1

If I understand your question, you can do the following:

Where   TransDT >= Convert(Date, DateAdd(Day, -1, GetDate())
And     TransDT < Convert(Date, GetDate())
Siyual
  • 16,415
  • 8
  • 44
  • 58
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))
Horaciux
  • 6,322
  • 2
  • 22
  • 41
0

TransDT is a date, but your comparing with an INT.

Try something like that: 

TransDT = CAST(SYSDATETIME() AS DATE)
Bestter
  • 877
  • 1
  • 12
  • 29