Sql – How to rewrite TSQL query for use in Microsoft Access 2003: TSQL Convert

ms-accesssqltsql

I'd like to rewrite this query for Microsoft Access 2003:


SELECT t1.PERSONID
       ,t1.CARDEVENTDATE
       ,MIN(t1.CARDEVENTTIME1) AS Intime
       ,MAX(t2.CARDEVENTTIME1) AS Outtime 

FROM (   SELECT PERSONID
                , CARDEVENTDATE
                , FUNCTIONKEY
                , CONVERT(VARCHAR(10), SUBSTRING(CARDEVENTTIME, 1, 2) + ':' + SUBSTRING(CARDEVENTTIME, 3, 2) + ':' + SUBSTRING(CARDEVENTTIME, 5, 2), 8) 
                 AS CARDEVENTTIME1  
         FROM T_CARDEVENT 
         WHERE      (FUNCTIONKEY = 'A')) AS t1 
         LEFT OUTER JOIN (SELECT PERSONID
                                 ,CARDEVENTDATE, CARDEVENTDAY, FUNCTIONKEY
                                 ,CONVERT(VARCHAR(10), SUBSTRING(CARDEVENTTIME, 1, 2) + ':' + SUBSTRING(CARDEVENTTIME, 3, 2) + ':' + SUBSTRING(CARDEVENTTIME, 5, 2), 8) 
                                  AS CARDEVENTTIME 
                      FROM T_CARDEVENT AS T_CARDEVENT_3) 
           AS t2 
           ON t1.PERSONID = t2.PERSONID 
           AND t1.CARDEVENTDATE = t2.CARDEVENTDATE   
           GROUP BY t1.PERSONID, t1.CARDEVENTDATE )

The above works in SQL Server, but in Access I've tried to run this query. It produces an error in convert.

How should I modify my query to suit Access 2003? What's the suitable equivalent for TSQL's Convert function in Access 2003?

Best Answer

The sample below may suit, if you use SQL Server compatible Syntax.

In t2, you appear to be missing 1 from CARDEVENTTIME, so I added it to give CARDEVENTTIME1. You also appear to have an extra bracket at the end.

SELECT t1.PERSONID
       ,t1.CARDEVENTDATE
       ,MIN(t1.CARDEVENTTIME1) AS Intime
       ,MAX(t2.CARDEVENTTIME1) AS Outtime 

FROM (   SELECT PERSONID
                , CARDEVENTDATE
                , FUNCTIONKEY
                , CDate(Mid(CARDEVENTTIME, 1, 2) + ':' + Mid(CARDEVENTTIME, 3, 2) + ':' + Mid(CARDEVENTTIME, 5, 2)) 
                 AS CARDEVENTTIME1  
         FROM T_CARDEVENT 
         WHERE      (FUNCTIONKEY = 'A')) AS t1 
         LEFT OUTER JOIN (SELECT PERSONID
                                 ,CARDEVENTDATE, CARDEVENTDAY, FUNCTIONKEY
                                  , CDate(Mid(CARDEVENTTIME, 1, 2) + ':' + Mid(CARDEVENTTIME, 3, 2) + ':' + Mid(CARDEVENTTIME, 5, 2)) 
                                  AS CARDEVENTTIME1
                      FROM T_CARDEVENT AS T_CARDEVENT_3) 
           AS t2 
           ON t1.PERSONID = t2.PERSONID 
           AND t1.CARDEVENTDATE = t2.CARDEVENTDATE   
           GROUP BY t1.PERSONID, t1.CARDEVENTDATE

Related Solutions

Sql-server – How to generate a random number for each row in a T-SQL select

Take a look at SQL Server - Set based random numbers which has a very detailed explanation.

To summarize, the following code generates a random number between 0 and 13 inclusive with a uniform distribution:

ABS(CHECKSUM(NewId())) % 14

To change your range, just change the number at the end of the expression. Be extra careful if you need a range that includes both positive and negative numbers. If you do it wrong, it's possible to double-count the number 0.

A small warning for the math nuts in the room: there is a very slight bias in this code. CHECKSUM() results in numbers that are uniform across the entire range of the sql Int datatype, or at least as near so as my (the editor) testing can show. However, there will be some bias when CHECKSUM() produces a number at the very top end of that range. Any time you get a number between the maximum possible integer and the last exact multiple of the size of your desired range (14 in this case) before that maximum integer, those results are favored over the remaining portion of your range that cannot be produced from that last multiple of 14.

As an example, imagine the entire range of the Int type is only 19. 19 is the largest possible integer you can hold. When CHECKSUM() results in 14-19, these correspond to results 0-5. Those numbers would be heavily favored over 6-13, because CHECKSUM() is twice as likely to generate them. It's easier to demonstrate this visually. Below is the entire possible set of results for our imaginary integer range:

Checksum Integer: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19
Range Result:     0 1 2 3 4 5 6 7 8 9 10 11 12 13  0  1  2  3  4  5

You can see here that there are more chances to produce some numbers than others: bias. Thankfully, the actual range of the Int type is much larger... so much so that in most cases the bias is nearly undetectable. However, it is something to be aware of if you ever find yourself doing this for serious security code.

Sql – one-to-many query selecting all parents and single top child for each parent

select p.id, p.text, c.id, c.parent, c.feature
from Parents p
left join (select c1.id, c1.parent, c1.feature
             from Childs c1
             join (select p1.id, max(c2.feature) maxFeature
                     from Parents p1
                left join Childs c2 on p1.id = c2.parent
            group by p1.id) cf on c1.parent = cf.id 
                              and c1.feature = cf.maxFeature) c
on p.id = c.parent

Related Topic

Mysql – Replace Default Null Values Returned From Left Outer Join