How to show previous day values in query

Question

I am going to be crazy I think. Please Help.

I have a table which fields are Date, No, Turnover, TotalWin, Opencredit, Handpay, Billin, gamesplayed and I am trying to write sql in vb.net that will show me previous day value but I cant. Here is what I am trying to do.

SELECT Meter.* 
FROM 
  Meter AS Previous, 
  Turnover As Prev_turnover  
WHERE Date = '
  SELECT Max(Date) 
  FROM Meter AS Previous2
  WHERE Previous2.Date < Date 
  AND Previous2.No = ‘No' 
  AND Previous.No = ‘No’ 

What is wrong in where I am doing mistake I really don't know. If anyone help will be appreciated.

Thanks

Answer 1

What specific problem are you having? Is it returning the wrong value or just giving you an error?

I'm guessing that you just need to get the prior days date. If that is the case in SQL Server you can use the DATEADD function like this: select DATEADD(day, -1, date). You could also use that in a where clause or a join clause.

Answer 2

Ok Thomas I am not able to give correct answer to your question because first time in my Life I am trying to write sql. I was doing this with MSAccess and it was easy. Now I am getting pretty simple error I think with this code but I can't find solution again. What is wrong.

SELECT [Date], [No], Turnover, TotalWin, GamesPlayed, CreditIn, BillIn, HandPay FROM Meter As Previous, Turnover As Prev_turnover WHERE [Date] = 'SELECT Max([Date]) FROM Meter As Previous2 WHERE Previous2.[Date]<[Date] AND Previous.[No]=[No]'

Answer 3

I found answer

SELECT     Date, No, Turnover, [Total Win], [Games Played], [Credit In], [Bill In], [Hand Pay]

FROM tblMeter WHERE (Date >= DATEADD(day, DATEDIFF(day, 1, GETDATE()), 0)) AND (Date < DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0))

Thaks to everyone who try to help me.