How to display a today date column, time column, previous date column, time column

Question

Using Access Database

Table

Cardno  name        cardeventdate Intime Outtime

0001    Michael     20080811      102746 185249
0001    Michael     20080812      080828 080828
0002    Michael     20080811      082615 082615
0002    Michael     20080812      073624 190605

From the Above Table I want to Display another Two Column like

Cardno, name, cardeventdate, Intime, Outtime, Yesterday cardeventdate (Previous column date), Yesterday Outtime (Previous column Outtime)

For Cardno – 0001
Name – Michael
Date – 20080811
Intime – 102746
Outtime – 185249
Yesterday Date – 102746
Yesterday Outtime – 185249

For Example, Today Intime is 090000 and Outtime is 180000 I want to display Today Date, Intime, Outtime, Previous Date Column, Previous Outtime Column belong to Cardno

My Date is not continuously, in my database date is like 20090601, 20090508. So we cannot put date-1

Am Using Sub Queries

Expected Output

Cardno name    cardeventdate Intime Outtime Yesterdaycardeventdate YesterdayOuttime

0001   Michael 20080811      102746 185249  20080810               175050
0001   Michael 20080812      080828 080828  20080811               185249
0002   Michael 20080811      082615 082615  20080810               192727
0002   Michael 20080812      073624 190605  20080811               082615

Need Query Help?

Answer 1

"Previous" isn't an absolute concept in SQL Server - it depends on how your data is sorted. How are you sorting the result set? I can't see anything that holds true across all of the rows.

The SQL 2000 solution is ugly, but it basically looks like this: **Note: Code Edited based upon additional information above.

SELECT CARDNO, CARDEVENTDATE, INTIME, OUTTIME, 
       (SELECT TOP 1 CARDEVENTDATE 
        FROM MyTable b
        WHERE a.CARDNO = b.CARDNO
        AND   a.INTIME > b.INTIME
        ORDER BY INTIME DESC) AS PREVCARDEVENTDATE,
       (SELECT TOP 1 OUTTIME
        FROM MyTable b
        WHERE a.CARDNO = b.CARDNO
        AND   a.INTIME > b.INTIME
        ORDER BY INTIME DESC) AS PREVOUTTIME
FROM   MyTable AS a

A much more flexible solution is available using SQL 2005 or 2008 and ranking functions - have a look at my blog post here if you're interested:

http://thehobt.blogspot.com/2009/02/rownumber-rank-and-denserank.html

Answer 2

Hi

Can you not cast the string to a smalldatetime and then remove one from the date - such as cast('20080102' as smalldatetime) -1

Answer 3

Can you not cast the string to a smalldatetime and then remove one from the date - such as cast('20080102' as smalldatetime) -1

cast('20080101' as smalldatetime) -1 will return 20080100