How to convert my SQL query to MS Access query?

SQL Query to MS Access Query

• I have a section of SQL code that can be run to show age buckets of a particular workload. Due to some system changes i will soon not be able to access this database through SQL and I do not really follow it. please can someone help me convert this into an MS Access Query, I have included the SQL and Results below, SQL Use MoxieApp select b.mailboxid, b.Name as 'Mailbox', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) <= '1' THEN 1 ELSE 0 END) as '1', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '1' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '2' THEN 1 ELSE 0 END) as '2', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '2' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '3' THEN 1 ELSE 0 END) as '3', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '3' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '4' THEN 1 ELSE 0 END) as '4', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '4' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '5' THEN 1 ELSE 0 END) as '5', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '5' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '6' THEN 1 ELSE 0 END) as '6', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '6' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '7' THEN 1 ELSE 0 END) as '7', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '7' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '8' THEN 1 ELSE 0 END) as '8', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '8' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '9' THEN 1 ELSE 0 END) as '9', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '9' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '10' THEN 1 ELSE 0 END) as '10', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '10' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '11' THEN 1 ELSE 0 END) as '11', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '11' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '12' THEN 1 ELSE 0 END) as '12', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '12' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '13' THEN 1 ELSE 0 END) as '13', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '13' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '14' THEN 1 ELSE 0 END) as '14', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '14' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '15' THEN 1 ELSE 0 END) as '15', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '15' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '16' THEN 1 ELSE 0 END) as '16', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '16' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '17' THEN 1 ELSE 0 END) as '17', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '17' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '18' THEN 1 ELSE 0 END) as '18', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '18' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '19' THEN 1 ELSE 0 END) as '19', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '19' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '20' THEN 1 ELSE 0 END) as '20', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '20' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '21' THEN 1 ELSE 0 END) as '21', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '21' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '22' THEN 1 ELSE 0 END) as '22', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '22' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '23' THEN 1 ELSE 0 END) as '23', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '23' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '24' THEN 1 ELSE 0 END) as '24', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '24' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '48' THEN 1 ELSE 0 END) as '48', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '48' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '72' THEN 1 ELSE 0 END) as '72', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '72' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '96' THEN 1 ELSE 0 END) as '96', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '96' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '120' THEN 1 ELSE 0 END) as '120', SUM(case when datediff(hh,a.DateRecv,GETUTCDATE()) > '120' and datediff(hh,a.DateRecv,GETUTCDATE()) <= '10000' THEN 1 ELSE 0 END) as 'Greater' from mailmessage a left outer join mailbox b on a.origmailboxid = b.mailboxid where a.routedate > '2015-04-21' and a.status = '0' and b.MailBoxID IN ('163') group by name,mailboxid select mailboxid, name from mailbox Results 163 Mailbox Name 39 16 9 8 4 2 1 2 4 2 2 7 9 5 14 18 18 12 9 4 2 3 0 0 2 3 2 0 1 I would really appreciate some help with this! Thanks in advance

• Answer:

  Here is an example of how you can do the date arithmetic: SUM(iif(datediff("h", a.DateRecv, now()) > 1 and datediff("h", a.DateRecv, now()) <= 2, 1, 0)) as [2] However, this doesn't handle the GETUTCDATE() issue. If NOW() doesn't work for you (for some reason), then calculate the value you want and pass it into the query.