converting some t-sql queries to MS ACCESS

  • Thread starter Thread starter ashley.sql
  • Start date Start date
A

ashley.sql

how do i convert this statement to work in MS ACCESS. These are some
statements I use in SQL server now they are required for a database in
access. They do not work. Anyone knows if it is possible to write them
in SQL which would work in access

sum(case when s.type = 'D' and p.date is not null then 1 else 0 end)

sum(case when p.PaymentDate is null and datediff(day, DueDate,
getdate()) >30 then 1 else 0 end) [OVER 30]

sum(case when completionDate > Duedate then 1 else 0 end)

case when location = 'NYC' then avg(datediff(day, orderdate, shipdate))
else NULL end [Avg TIME TO SHIP in NYC]


sum(case when location = 'NYC' then 1 else 0 end) [NYC COUNT]
 
As I see it the function IIF will replace case in your examples.

IIF( test , result_if_true, result_if_false)

e.g. SUM( IIF(s.type = 'D' AND NOT ISNULL( p.date), 1 , 0 ) )
 
can you show me how to do the datediff one

David said:
As I see it the function IIF will replace case in your examples.

IIF( test , result_if_true, result_if_false)

e.g. SUM( IIF(s.type = 'D' AND NOT ISNULL( p.date), 1 , 0 ) )

how do i convert this statement to work in MS ACCESS. These are some
statements I use in SQL server now they are required for a database in
access. They do not work. Anyone knows if it is possible to write them
in SQL which would work in access

sum(case when s.type = 'D' and p.date is not null then 1 else 0 end)

sum(case when p.PaymentDate is null and datediff(day, DueDate,
getdate()) >30 then 1 else 0 end) [OVER 30]

sum(case when completionDate > Duedate then 1 else 0 end)

case when location = 'NYC' then avg(datediff(day, orderdate, shipdate))
else NULL end [Avg TIME TO SHIP in NYC]


sum(case when location = 'NYC' then 1 else 0 end) [NYC COUNT]
Click to expand...
Click to expand...
 
In Access the function DateDiff is written as um ... DateDiff

= DateDiff( Time unit , Date1, Date2)

The time unit is a string to indicate the unit of time to return. Look in
help for all of the options.


Ashley said:
can you show me how to do the datediff one

David said:
As I see it the function IIF will replace case in your examples.

IIF( test , result_if_true, result_if_false)

e.g. SUM( IIF(s.type = 'D' AND NOT ISNULL( p.date), 1 , 0 ) )

how do i convert this statement to work in MS ACCESS. These are some
statements I use in SQL server now they are required for a database in
access. They do not work. Anyone knows if it is possible to write them
in SQL which would work in access

sum(case when s.type = 'D' and p.date is not null then 1 else 0 end)

sum(case when p.PaymentDate is null and datediff(day, DueDate,
getdate()) >30 then 1 else 0 end) [OVER 30]

sum(case when completionDate > Duedate then 1 else 0 end)

case when location = 'NYC' then avg(datediff(day, orderdate, shipdate))
else NULL end [Avg TIME TO SHIP in NYC]


sum(case when location = 'NYC' then 1 else 0 end) [NYC COUNT]
Click to expand...
Click to expand...
Click to expand...
 
How do i compare this with some value

can you help me fix this statement

sum(IFF(datediff('d', #m_date#, #p_date#) between 1 and 30, 1 , 0))

means if the difference between 2 dates is between 1 and 30 then these
many
cases are late. IT can be <=30 instead of between 1 and 30

David said:
In Access the function DateDiff is written as um ... DateDiff

= DateDiff( Time unit , Date1, Date2)

The time unit is a string to indicate the unit of time to return. Look in
help for all of the options.


Ashley said:
can you show me how to do the datediff one

David said:
As I see it the function IIF will replace case in your examples.

IIF( test , result_if_true, result_if_false)

e.g. SUM( IIF(s.type = 'D' AND NOT ISNULL( p.date), 1 , 0 ) )

how do i convert this statement to work in MS ACCESS. These are some
statements I use in SQL server now they are required for a database in
access. They do not work. Anyone knows if it is possible to write them
in SQL which would work in access

sum(case when s.type = 'D' and p.date is not null then 1 else 0 end)

sum(case when p.PaymentDate is null and datediff(day, DueDate,
getdate()) >30 then 1 else 0 end) [OVER 30]

sum(case when completionDate > Duedate then 1 else 0 end)

case when location = 'NYC' then avg(datediff(day, orderdate, shipdate))
else NULL end [Avg TIME TO SHIP in NYC]


sum(case when location = 'NYC' then 1 else 0 end) [NYC COUNT]
Click to expand...
Click to expand...
Click to expand...
 
I think this will do it for you.
SUM(CASE datediff(day, m_date, p_date) WHEN BETWEEN 1 AND 30 THEN 1 ELSE
0 END CASE)

--
Bill Mosca, MS Access MVP


Ashley said:
How do i compare this with some value

can you help me fix this statement

sum(IFF(datediff('d', #m_date#, #p_date#) between 1 and 30, 1 , 0))

means if the difference between 2 dates is between 1 and 30 then these
many
cases are late. IT can be <=30 instead of between 1 and 30

David said:
In Access the function DateDiff is written as um ... DateDiff

= DateDiff( Time unit , Date1, Date2)

The time unit is a string to indicate the unit of time to return. Look in
help for all of the options.


Ashley said:
can you show me how to do the datediff one

David Cox wrote:
As I see it the function IIF will replace case in your examples.

IIF( test , result_if_true, result_if_false)

e.g. SUM( IIF(s.type = 'D' AND NOT ISNULL( p.date), 1 , 0 ) )

how do i convert this statement to work in MS ACCESS. These are some
statements I use in SQL server now they are required for a database
in
access. They do not work. Anyone knows if it is possible to write
them
in SQL which would work in access

sum(case when s.type = 'D' and p.date is not null then 1 else 0 end)

sum(case when p.PaymentDate is null and datediff(day, DueDate,
getdate()) >30 then 1 else 0 end) [OVER 30]

sum(case when completionDate > Duedate then 1 else 0 end)

case when location = 'NYC' then avg(datediff(day, orderdate,
shipdate))
else NULL end [Avg TIME TO SHIP in NYC]


sum(case when location = 'NYC' then 1 else 0 end) [NYC COUNT]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top