Computed Column Formulas

I am working on migrating an existing Access DB to SQL Server Express.
The DB is for rental tracking. I am creating it as an ADP project and have
created all the tables, forms etc. from scratch.
I am using Access 2007 as a front end for the DB.
I have fields RentalDays, StartDate and EndDate in a table.
In the existing Access DB I have a query that checks for NULL of the EndDate
field. If it is NULL I calculate the RentalDays from the StartDate to NOW.
If the EndDate is populated I calculate the difference between the StartDate
and EndDate. This gives me the number of accrued days a product has been in
rental or if it is a finished rental it gives the number of days the product
was rented for.
IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]
-[StartDate]))
AS RentDays
I've been unable to figure out the syntax for the expression entered as a
formula in the table with the RentalDays column in SQL Server Express. I am
able to make a datediff formula for the RentalDays column using the formula:
(datediff,(day,[StartDate],[EndDate]))
This works fine but only populates the RentalDays field with the number of
days for a rental with both StartDate and EndDate entries. I would like to
have the computed column, (RentalsDays) expression return the number of days
for ongoing rentals (no EndDate) as well as finished rentals.
My goal is to have the RentalDays calculated on the back end and use the
data in reports (open and finished rentals) as well as for day-to-day entry
forms for check in and out of rented products. Those forms are already
created but I am doing the RentalDays caculation on the form. Actually, I am
not sure which is the technically correct or preferred method for doing this
(on the form or via a formula for the RentalDays column in the table). This
all works wonderfully in Access 2007 at present but the number of users and
the amount of data is anticipated to increase and will present a problem in
the longer term. Which is why I am working to change over to SQL Server
Express. I'm pretty fair with Access but making a full blown ADP project and
using SQL Server Express are new to me.
Any help with this is appreciated.
John K.> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDat
e]))
> AS RentDays
Try:
DATEDIFF(day, [StartDate], COALESCE([EndDate], GETDATE()))
Hope this helps.
Dan Guzman
SQL Server MVP
"John Kraus" <jkraus3@.twcny.rr.com> wrote in message
news:0417BFAB-76AE-4374-9CE1-E18FC389C16A@.microsoft.com...
>I am working on migrating an existing Access DB to SQL Server Express.
> The DB is for rental tracking. I am creating it as an ADP project and have
> created all the tables, forms etc. from scratch.
> I am using Access 2007 as a front end for the DB.
> I have fields RentalDays, StartDate and EndDate in a table.
> In the existing Access DB I have a query that checks for NULL of the
> EndDate field. If it is NULL I calculate the RentalDays from the StartDate
> to NOW. If the EndDate is populated I calculate the difference between the
> StartDate and EndDate. This gives me the number of accrued days a product
> has been in rental or if it is a finished rental it gives the number of
> days the product was rented for.
> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDat
e]-[StartDate]))
> AS RentDays
> I've been unable to figure out the syntax for the expression entered as a
> formula in the table with the RentalDays column in SQL Server Express. I
> am able to make a datediff formula for the RentalDays column using the
> formula:
> (datediff,(day,[StartDate],[EndDate]))
> This works fine but only populates the RentalDays field with the number of
> days for a rental with both StartDate and EndDate entries. I would like to
> have the computed column, (RentalsDays) expression return the number of
> days for ongoing rentals (no EndDate) as well as finished rentals.
> My goal is to have the RentalDays calculated on the back end and use the
> data in reports (open and finished rentals) as well as for day-to-day
> entry forms for check in and out of rented products. Those forms are
> already created but I am doing the RentalDays caculation on the form.
> Actually, I am not sure which is the technically correct or preferred
> method for doing this (on the form or via a formula for the RentalDays
> column in the table). This all works wonderfully in Access 2007 at present
> but the number of users and the amount of data is anticipated to increase
> and will present a problem in the longer term. Which is why I am working
> to change over to SQL Server Express. I'm pretty fair with Access but
> making a full blown ADP project and using SQL Server Express are new to
> me.
> Any help with this is appreciated.
> John K.|||Dan,
That worked perfectly. I have to go lookup the COALESCE function to see
what is really going on there. I looked around and didn't see anybody offer
such a simple solution. I did find out though that I couldn't do a
conditional statement in a formula field.
Thanks for the quick response.
John K
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:F2038EAF-BB4F-4CDB-86F4-37BDA948933C@.microsoft.com...
> Try:
> DATEDIFF(day, [StartDate], COALESCE([EndDate], GETDATE()))
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Kraus" <jkraus3@.twcny.rr.com> wrote in message
> news:0417BFAB-76AE-4374-9CE1-E18FC389C16A@.microsoft.com...
>|||> such a simple solution. I did find out though that I couldn't do a
> conditional statement in a formula field.
You can specify a conditional expression with CASE like the example below.
I think a COALESCE function is better choice in you case, though.
CASE
WHEN [EndDate] IS NULL THEN DATEDIFF(day, [StartDate], GETDATE())
ELSE DATEDIFF(day, [StartDate], [EndDate])
END
Hope this helps.
Dan Guzman
SQL Server MVP
"John Kraus" <jkraus3@.twcny.rr.com> wrote in message
news:07069509-ACC7-4392-A2B6-E8671E8ED36C@.microsoft.com...
> Dan,
> That worked perfectly. I have to go lookup the COALESCE function to see
> what is really going on there. I looked around and didn't see anybody
> offer such a simple solution. I did find out though that I couldn't do a
> conditional statement in a formula field.
> Thanks for the quick response.
> John K
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:F2038EAF-BB4F-4CDB-86F4-37BDA948933C@.microsoft.com...
>|||On Nov 28, 7:03 am, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> You can specify a conditional expression with CASE like the example below.
> I think a COALESCE function is better choice in you case, though.
> CASE
> WHEN [EndDate] IS NULL THEN DATEDIFF(day, [StartDate], GETDATE
())
> ELSE DATEDIFF(day, [StartDate], [EndDate])
> END
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "John Kraus" <jkra...@.twcny.rr.com> wrote in message
> news:07069509-ACC7-4392-A2B6-E8671E8ED36C@.microsoft.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
or
DATEDIFF(day, [StartDate], COALESCE([EndDate],GETDATE()))|||> or
> DATEDIFF(day, [StartDate], COALESCE([EndDate],GETDATE()))
I agree that is is the best approach for John's specific situation and is
why I suggested it in my initial response. I added the CASE example in
response to John's statement that he couldn't find a "conditional
statement". CASE can be used for other situations that involve conditions
other than NULL.
Hope this helps.
Dan Guzman
SQL Server MVP
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:d582a1b3-2ef6-4138-8764-c23404f8e9b0@.s8g2000prg.googlegroups.com...
> On Nov 28, 7:03 am, "Dan Guzman" <guzma...@.nospam-
> online.sbcglobal.net> wrote:
> or
> DATEDIFF(day, [StartDate], COALESCE([EndDate],GETDATE()))|||On Nov 28, 6:17 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
>
> I agree that is is the best approach for John's specific situation and is
> why I suggested it in my initial response. I added the CASE example in
> response to John's statement that he couldn't find a "conditional
> statement". CASE can be used for other situations that involve conditions
> other than NULL.
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Madhivanan" <madhivanan2...@.gmail.com> wrote in message
> news:d582a1b3-2ef6-4138-8764-c23404f8e9b0@.s8g2000prg.googlegroups.com...
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Hi Dan
I think I forgot that you already replied the same answer |||Dan,
I has seen the case example and wasn't sure it was the best approach and
that is when I decided to post here to see if there was a better solution.
I'm confident that for my usage the COALESCE function was the cleanest.
Thanks again,
John K
"Madhivanan" <madhivanan2001@.gmail.com> wrote in message
news:aae6a62c-0401-4d5d-bbff-146495686c1e@.s19g2000prg.googlegroups.com...
> On Nov 28, 6:17 pm, "Dan Guzman" <guzma...@.nospam-
> online.sbcglobal.net> wrote:
> Hi Dan
> I think I forgot that you already replied the same answer |||> I'm confident that for my usage the COALESCE function was the cleanest.
I agree. As I mentioned to Madhivanan, I provided the CASE example as an
alternative approach that might come in handy for other conditional
expressions you might have in the future.
Hope this helps.
Dan Guzman
SQL Server MVP
"John Kraus" <jkraus3@.twcny.rr.com> wrote in message
news:18B271B1-AEF3-45A2-A065-7E43BD734A48@.microsoft.com...
> Dan,
> I has seen the case example and wasn't sure it was the best approach and
> that is when I decided to post here to see if there was a better solution.
> I'm confident that for my usage the COALESCE function was the cleanest.
> Thanks again,
> John K
> "Madhivanan" <madhivanan2001@.gmail.com> wrote in message
> news:aae6a62c-0401-4d5d-bbff-146495686c1e@.s19g2000prg.googlegroups.com...
>