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]-[StartDate]))
> 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()),([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.|||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...
>> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate]))
>> 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()),([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.
>|||> 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...
>> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate]))
>> 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()),([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.
>|||On Nov 28, 7:03 am, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > 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" <jkra...@.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" <guzma...@.nospam-online.sbcglobal.net> wrote in message
> >news:F2038EAF-BB4F-4CDB-86F4-37BDA948933C@.microsoft.com...
> >> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate-]))
> >> AS RentDays
> >> Try:
> >> DATEDIFF(day, [StartDate], COALESCE([EndDate], GETDATE()))
> >> --
> >> Hope this helps.
> >> Dan Guzman
> >> SQL Server MVP
> >> "John Kraus" <jkra...@.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()),([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.- Hide quoted text -
> - 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:
>> > 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" <jkra...@.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" <guzma...@.nospam-online.sbcglobal.net> wrote in message
>> >news:F2038EAF-BB4F-4CDB-86F4-37BDA948933C@.microsoft.com...
>> >> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate-]))
>> >> AS RentDays
>> >> Try:
>> >> DATEDIFF(day, [StartDate], COALESCE([EndDate], GETDATE()))
>> >> --
>> >> Hope this helps.
>> >> Dan Guzman
>> >> SQL Server MVP
>> >> "John Kraus" <jkra...@.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()),([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.- Hide quoted text -
>> - Show quoted text -
> or
> DATEDIFF(day, [StartDate], COALESCE([EndDate],GETDATE()))|||On Nov 28, 6:17 pm, "Dan Guzman" <guzma...@.nospam-
online.sbcglobal.net> wrote:
> > 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" <madhivanan2...@.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:
> >> > 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" <jkra...@.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" <guzma...@.nospam-online.sbcglobal.net> wrote in message
> >> >news:F2038EAF-BB4F-4CDB-86F4-37BDA948933C@.microsoft.com...
> >> >> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate--]))
> >> >> AS RentDays
> >> >> Try:
> >> >> DATEDIFF(day, [StartDate], COALESCE([EndDate], GETDATE()))
> >> >> --
> >> >> Hope this helps.
> >> >> Dan Guzman
> >> >> SQL Server MVP
> >> >> "John Kraus" <jkra...@.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()),([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.- Hide quoted text -
> >> - Show quoted text -
> > or
> > DATEDIFF(day, [StartDate], COALESCE([EndDate],GETDATE()))- Hide quoted text -
> - 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:
>> > 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" <madhivanan2...@.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:
>> >> > 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" <jkra...@.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" <guzma...@.nospam-online.sbcglobal.net> wrote in message
>> >> >news:F2038EAF-BB4F-4CDB-86F4-37BDA948933C@.microsoft.com...
>> >> >> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate--]))
>> >> >> AS RentDays
>> >> >> Try:
>> >> >> DATEDIFF(day, [StartDate], COALESCE([EndDate], GETDATE()))
>> >> >> --
>> >> >> Hope this helps.
>> >> >> Dan Guzman
>> >> >> SQL Server MVP
>> >> >> "John Kraus" <jkra...@.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()),([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.- Hide quoted text -
>> >> - Show quoted text -
>> > or
>> > DATEDIFF(day, [StartDate], COALESCE([EndDate],GETDATE()))- Hide quoted
>> > text -
>> - Show quoted text -
> 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...
>> On Nov 28, 6:17 pm, "Dan Guzman" <guzma...@.nospam-
>> online.sbcglobal.net> wrote:
>> > 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" <madhivanan2...@.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:
>> >> > 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" <jkra...@.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" <guzma...@.nospam-online.sbcglobal.net> wrote in
>> >> > message
>> >> >news:F2038EAF-BB4F-4CDB-86F4-37BDA948933C@.microsoft.com...
>> >> >> IIf(IsNull([EndDate]),DateDiff('d',[StartDate],Now()),([EndDate]-[StartDate--]))
>> >> >> AS RentDays
>> >> >> Try:
>> >> >> DATEDIFF(day, [StartDate], COALESCE([EndDate], GETDATE()))
>> >> >> --
>> >> >> Hope this helps.
>> >> >> Dan Guzman
>> >> >> SQL Server MVP
>> >> >> "John Kraus" <jkra...@.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()),([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.- Hide quoted text -
>> >> - Show quoted text -
>> > or
>> > DATEDIFF(day, [StartDate], COALESCE([EndDate],GETDATE()))- Hide quoted
>> > text -
>> - Show quoted text -
>> Hi Dan
>> I think I forgot that you already replied the same answer :)
>
Showing posts with label express. Show all posts
Showing posts with label express. Show all posts
Thursday, March 22, 2012
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]-[StartDate]))
> 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()),([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.
|||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...
>
sqlsql
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]-[StartDate]))
> 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()),([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.
|||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...
>
sqlsql
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...
>
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...
>
Tuesday, March 20, 2012
Compress or Compact Database with SQL Express
How do you Compress or Compact a Database with SQL Express?
If feedback is of benefit, this would be a useful feature to include with SSME.
Regards,
FlavelleRight click on the database in Object Explorer, select Tasks > Shrink > Database.|||Thanks - that was too easy!
Regards,
Flavelle
Sunday, February 19, 2012
Completely Removing SQL Server 2005 Express
I've recently tried to load SQL Server 2005 Express onto the main server that services our small company of 10 people. The server is a W2K Server, and acts as our domain controller. I've had NUMEROUS problems trying to get SQL Server 2005 Express to work on this system - so much so that I've given up trying. However, in attempting to uninstall this, I've now encountered even bigger issues. It seems that the uninstaller leaves files behind (such as VSS Writer, Set up Files) that then will not uninstall themselves. So, being the novice I am, I tried re-installing SQL Server Express as a whole, removing the above files, then uninstalling SQL Server Express "proper." Well, that didn't work either - I got the subordinate stuff to come off, but now the main files won't remove - nor can I reinstall the whole thing any more.
This is an absolute mess - and I need help getting out of it. Can anyone direct me to help on how to SAFELY remove the ENTIRE instance of SQL Server 2005 Express so that nothing else is interrupted/harmed? Any sound advice at this point is most appreciated.
I ve got the same Problem here. Ive installed the Beta of Visual Studie - even after de-installation it was not possible to install Sql Server Developer edition.
I think it is something wrong in registry...|||Not sure about uninstalling, but I think I saw a blurb somewhere on the books online stating you shouldn't install SQL 2005 on a domain controller...|||
SQLWriter and Support files (amongst other 'leftovers') should uninstall just fine after SQL is gone. They may pop up a message saying that removing them may cause other dependent applications to fail. But if you have already removed all instances of SQL Server 2005, you should be fine.
In the situation where you removed them while SQL Express was already on the box, I would suggest installing Express again (to let the installer put back what you deleted), then uninstalling Express, then uninstalling the leftovers.
If that fails, as a last resort you can use the Windows Installer clean up tool. However this may leave some services behind. I definitely recommend
http://download.microsoft.com/download/E/9/D/E9D80355-7AB4-45B8-80E8-983A48D5E1BD/msicuu2.exe
Friday, February 17, 2012
Compatible?
I have been doing some research on SQL Server 2005 Express. We have recently purchased Visual Studio 2005 Professional Edition which comes with a copy of SQL Server 2005 Developer Edition. My question is this: Once we complete development using VS Pro + SQL Server DE, will we be able to load SQL Server Express on the client's machines and be able to have full functionality of the database that we have created to be used with the program? I have read that SQL Server Express IS in fact compatible and fully integrated with VS Express, but i have not read any information saying wheather or not it is integrated with VS Pro/SQL Server DE. Does anyone know if it is or not, and either way know the difficulty in converting it (ie. loss of data, tables, etc)?
Short answer: Yes, they are compatible.
Long answer: SQL Express has a smaller feature set than SQL Dev. If you've used functionality in your application that is not included in SQL Express, then it will fail on SQL Express. As far as the database though, the files are exactly the same and can be transfered from one Edition to another.
You can check out the SQL Server Feature Comparison to determine if you're using any features that are not available in SQL Express.
Tuesday, February 14, 2012
Compatibility with SQL 2000
I have moved a database from SQL 2000 to SQL 2005 Express. I have modified the structure in 2005 Management Studio Express.
Now I cannot attach to the modified dataabse in SQL 2000 Enterprise Manager. I get "Error 602: Could not find row in sysindexes for database ID.... Run DBCC CHECKTABLE on sysindexes".
This occurs despite the fact that I have kept the database at Compatibiluty Level SQL Server 2000, as reported in 2005 Management Studio Express.
Are 2005 and 2000 databases not compatible?
Many thanks.
As you discovered, once you attach a database to SQL 2005, you cannot move it back to SQL 2000.
Moving a database is a one-way operation, and cannot be reversed.
|||Compatability Mode has nothing to do with the database format, it has to do with how commands are run by the database engine. Databases are automatically converted to the 2005 file format when they are attached, there is no way to convert the database format back. You should be able to manage SQL 2000 database from SSMS if that is the tool you would prefer to use, but you can not bounce databases back and forth between SQL 2000 and SQL 2005.
Mike
|||Thanks.
Sunday, February 12, 2012
Compatibility between sql express and MSDE
I need to know if I will run into problems with sql express on a sql 2000 server. I get the impression that my isp is using 200 server just by lookng at the connection string. Is there a addin I could use if needed I seen a post mentioning the publishing wizard cpt but not much after is it released yet. and then there is the web data admnistrator for msde will that work with sql express.One other question can I reinstall sql and change the authentication process from windows to user name and password.
DKB
Compatibility between sql express and MSDE there is none, it is not simple to move SQL Server 2005 database to 2000 it is covered in details by another user in the thread below.
http://forums.asp.net/thread/1494648.aspx
To use SQL Server with password you need to change to mixed authentication mode and that is covered in the SQL Server docs below. Hope this helps.
http://msdn2.microsoft.com/en-us/library/ms188670.aspx
|||Thank you Caddre I guess I will have to wait and see by the looks of it I have two options find a new isp or unistall the instances that are installed and remove sql express and install msde.
DKB
Subscribe to:
Posts (Atom)