Sql Comparing two tables: working

Showing posts with label working. 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...
>> 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 :)
>

Computed Column Formulas

|||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...
>
sql sql

Computed Column Formulas

Tuesday, March 20, 2012

Compress full backup file

Hello,

Is it possible to compress a full backup file? I've noticed that the backup file size is usually the same size of the working database.
My goal is to compress and break in small parts the backup file to be send to another location.

Does anyone knows a program to do that?

Thanks for the help!
Diogo SantosRefer to http://www.sqllitespeed.com/ (SQL Litespeed) for more information which is lot quicker and reliable.

Saturday, February 25, 2012

Complex query help needed....

I have been working with SQL for a while...but I am stumped. I can not
seem to get my arms around this query...can anyone help...

Here it is:

Table = 12 rows, 4 columns (id, name, amount, date)
row1 = 771, "steve", $50.00, "01/01/2005"
row2 = 772, "steve", $100.00, "01/11/2005"
row3 = 773, "steve", $200.00, "01/11/2005"
row4 = 774, "dave", $300.00, "01/01/2005"
row5 = 775, "dave", $400.00, "01/12/2005"
row6 = 776, "dave", $500.00, "01/12/2005"
row7 = 777, "mike", $600.00, "01/01/2005"
row8 = 778, "mike", $700.00, "01/13/2005"
row9 = 789, "mike", $800.00, "01/13/2005"
row10 = 790, "chuck", $900.00, "01/01/2005"
row11 = 791, "chuck", $950.00, "01/14/2005"
row12 = 792, "chuck", $975.00, "01/14/2005"

I need a query that returns (1) ONE ROW PER NAME based on the MOST
RECENT DATE and returns the correct corresponding information. The
keys to this question are the following:
1. The query needs to return ONE ROW PER NAME
2. I do not want to use a First() function (in MS Access)
3. Even though (2) two DATE for each NAME are the same, i want the
query to return one record and whatever record it returns, i have to be
able to have all the corresponding records (id, name, amount, and
date). I recorgnize that the DATE is ambiguous and that SQL may return
one or the other...but that is ok.
4. The return set should include (4) four rows

Any help with this would be thoroughly appreciated...Assuming that your id column is unique and does not allow NULL values,
the SQL below should return a single record per name, using the record
with the latest date and in this case if the dates are identical then
it will return the record with the greater id.

SELECT T1.id, T1.name, T1.amount, T1.date
FROM Test T1
LEFT OUTER JOIN Test T2 ON T2.name = T1.name
AND ((T2.date > T1.date) OR (T2.date = T1.date
AND T2.id > T1.id))
WHERE T2.id IS NULL

-Tom.

Complex Math functions

Hi there friends,

Can any of you please advice me as to how I am going to accomplish certain mathematical functions on numbers such as working out the n-th root of a number since the mathematical functions built in don't go past the square root (2nd root)?

Regards
  <![endif]-->hi,
you can use the POWER function since the equivalent exponential function of an [n'th root of x] is [x power of 1/n]..
e.g.
if you want to get the 5th root of 100, use POWER(100, 1.0/5.0)

- clintz|||Thank you very much clintz.
|||you can also create user defined function (UDF) for other mathematical functions that you need.. like factorial, permutations, etc.|||It seems i have rejoiced to early:

if i do a simple query like select power(27,1/3),
the resulting value is 1 not 3. why is that?

Regards

|||

bcs 1/3 = 0 (INTEGER DIVISION)

So anything power 0 = 1

To overcome this use the following expression

Select Power(27.0,1.0/3.0)

|||lol of course, thanks so much once again

Regards

complex join replication filtering is not working in 3.5 beta

Hi,

I have a complex join filtering on a replicated sql server database which was working fine in previous versions of sql compact. The query is something like the following:

I am not understanding what my issue is on this one! I can look at the data
returned from the dataset in Data view, but when I attempt to show a table
based on at least one value being true (from several) it says:
"The hidden expression for the table 'table1' refers to the field
'SSFLunchRegular'. Report item expressions can only refer to fields within
the current data set scope..."
I can turn right around and show/hide a row of the table based on the exact
same field name and it works. The structure of my IIF statment appears to be
fine because the same structure works in another report. Is it the number of
conditions? Is there a limit?
Here are the conditions placed on the Hidden statement:
=IIF(
(FIELDS!SLPLunchRegular.Value <> 0) and
(FIELDS!SLPLunchProv1.Value <> 0) and
(FIELDS!SLPLunchProv2.Value <> 0) and
(FIELDS!SLPLunchProv3.Value <> 0) and
(FIELDS!SLPBkfstRegular.Value <> 0) and
(FIELDS!SLPBkfstProv1.Value <> 0) and
(FIELDS!SLPBkfstProv2.Value <> 0) and
(FIELDS!SLPBkfstProv3.Value <> 0) and
(FIELDS!SLPSevereRegular.Value <> 0) and
(FIELDS!SLPSevereProv1.Value <> 0) and
(FIELDS!SLPSevereProv2.Value <> 0) and
(FIELDS!SLPSevereProv3.Value <> 0) and
(FIELDS!SLPSnackArea.Value <> 0) and
(FIELDS!SLPSnackNonArea.Value <> 0), False, True
)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200511/1oops, I wrote 'SSFLunchRegular', it should have been 'SLPLunchRegular'. The
misspelling was in this thread, not my code so the problem is not solved..
Rick wrote:
>I am not understanding what my issue is on this one! I can look at the data
>returned from the dataset in Data view, but when I attempt to show a table
>based on at least one value being true (from several) it says:
>"The hidden expression for the table 'table1' refers to the field
>'SSFLunchRegular'. Report item expressions can only refer to fields within
>the current data set scope..."
>I can turn right around and show/hide a row of the table based on the exact
>same field name and it works. The structure of my IIF statment appears to be
>fine because the same structure works in another report. Is it the number of
>conditions? Is there a limit?
>Here are the conditions placed on the Hidden statement:
>=IIF(
>(FIELDS!SLPLunchRegular.Value <> 0) and
>(FIELDS!SLPLunchProv1.Value <> 0) and
>(FIELDS!SLPLunchProv2.Value <> 0) and
>(FIELDS!SLPLunchProv3.Value <> 0) and
>(FIELDS!SLPBkfstRegular.Value <> 0) and
>(FIELDS!SLPBkfstProv1.Value <> 0) and
>(FIELDS!SLPBkfstProv2.Value <> 0) and
>(FIELDS!SLPBkfstProv3.Value <> 0) and
>(FIELDS!SLPSevereRegular.Value <> 0) and
>(FIELDS!SLPSevereProv1.Value <> 0) and
>(FIELDS!SLPSevereProv2.Value <> 0) and
>(FIELDS!SLPSevereProv3.Value <> 0) and
>(FIELDS!SLPSnackArea.Value <> 0) and
>(FIELDS!SLPSnackNonArea.Value <> 0), False, True
>)
--
Message posted via SQLMonster.com
http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200511/1|||The problem seems to be that you're attempting to hide a whole table based
on one single record. That wouldn't be logical. You'd need to aggregate the
values somehow, something like
=IIF(
(Sum(FIELDS!SLPLunchRegular.Value) <> 0) and
(Sum(FIELDS!SLPLunchProv1.Value) <> 0) and
...
...
Hope this helps.
--
Robert Jeppesen
Durius
http://www.durius.com/
"Rick via SQLMonster.com" <u15024@.uwe> wrote in message
news:5773f7edc9ca2@.uwe...
>I am not understanding what my issue is on this one! I can look at the
>data
> returned from the dataset in Data view, but when I attempt to show a table
> based on at least one value being true (from several) it says:
> "The hidden expression for the table 'table1' refers to the field
> 'SSFLunchRegular'. Report item expressions can only refer to fields
> within
> the current data set scope..."
> I can turn right around and show/hide a row of the table based on the
> exact
> same field name and it works. The structure of my IIF statment appears to
> be
> fine because the same structure works in another report. Is it the number
> of
> conditions? Is there a limit?
> Here are the conditions placed on the Hidden statement:
> =IIF(
> (FIELDS!SLPLunchRegular.Value <> 0) and
> (FIELDS!SLPLunchProv1.Value <> 0) and
> (FIELDS!SLPLunchProv2.Value <> 0) and
> (FIELDS!SLPLunchProv3.Value <> 0) and
> (FIELDS!SLPBkfstRegular.Value <> 0) and
> (FIELDS!SLPBkfstProv1.Value <> 0) and
> (FIELDS!SLPBkfstProv2.Value <> 0) and
> (FIELDS!SLPBkfstProv3.Value <> 0) and
> (FIELDS!SLPSevereRegular.Value <> 0) and
> (FIELDS!SLPSevereProv1.Value <> 0) and
> (FIELDS!SLPSevereProv2.Value <> 0) and
> (FIELDS!SLPSevereProv3.Value <> 0) and
> (FIELDS!SLPSnackArea.Value <> 0) and
> (FIELDS!SLPSnackNonArea.Value <> 0), False, True
> )
> --
> Message posted via SQLMonster.com
> http://www.sqlmonster.com/Uwe/Forums.aspx/sql-server-reporting/200511/1

Complex Case Statement in View in SQL Server 2000

I am working on SQL Server 2000.
I am trying to create a Case Statement where I do something like the
following:
I want to set up the case statement to check if a value falls within a
specific range then sum the unit amount as the type of data it
represents.
SELECT COMPANY, FISCAL_YEAR, BUDGET_NBR, ACCT_UNIT, ACCOUNT,
[MONTH],
CASE ACCOUNT WHEN >= 51110 AND <= 51140 THEN
SUM(UNIT) AS BudFTEProd
WHEN >= 51210 and <= 51240 THEN
SUM(UNIT) AS BudFTEProdOth
WHEN >= 51310 and <= 51340 THEN
SUM(UNIT) AS BudFTENonProd
END
FROM dbo.FBDETAIL_MO_AU_ACCT_VIEW
GROUP BY COMPANY, FISCAL_YEAR, BUDGET_NBR, [MONTH], ACCT_UNIT, ACCOUNT
HAVING (FISCAL_YEAR = 2006)
Anyone know how I would do this?
Thanks,
DebbieTry:
SELECT COMPANY, FISCAL_YEAR, BUDGET_NBR, ACCT_UNIT, ACCOUNT,
[MONTH],
SUM (CASE WHEN ACCOUNT >= 51110 AND ACCOUNT <= 51140 THEN
UNIT ELSE 0 END) AS BudFTEProd,
SUM (CASE WHEN ACCOUNT >= 51210 and ACCOUNT <= 51240 THEN
UNIT ELSE 0 END) AS BudFTEProdOth,
SUM(CASE WHEN ACCOUNT >= 51310 and ACCOUNT <= 51340 THEN
UNIT ELSE 0 END) AS BudFTENonProd
FROM dbo.FBDETAIL_MO_AU_ACCT_VIEW
WHERE (FISCAL_YEAR = 2006)
GROUP BY COMPANY, FISCAL_YEAR, BUDGET_NBR, [MONTH], ACCT_UNIT, ACCOUNT
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
.
"Debbie" <debsmithmich@.hotmail.com> wrote in message
news:1156263058.634273.58140@.m79g2000cwm.googlegroups.com...
I am working on SQL Server 2000.
I am trying to create a Case Statement where I do something like the
following:
I want to set up the case statement to check if a value falls within a
specific range then sum the unit amount as the type of data it
represents.
SELECT COMPANY, FISCAL_YEAR, BUDGET_NBR, ACCT_UNIT, ACCOUNT,
[MONTH],
CASE ACCOUNT WHEN >= 51110 AND <= 51140 THEN
SUM(UNIT) AS BudFTEProd
WHEN >= 51210 and <= 51240 THEN
SUM(UNIT) AS BudFTEProdOth
WHEN >= 51310 and <= 51340 THEN
SUM(UNIT) AS BudFTENonProd
END
FROM dbo.FBDETAIL_MO_AU_ACCT_VIEW
GROUP BY COMPANY, FISCAL_YEAR, BUDGET_NBR, [MONTH], ACCT_UNIT, ACCOUNT
HAVING (FISCAL_YEAR = 2006)
Anyone know how I would do this?
Thanks,
Debbie

Complex Case Statement in View in SQL Server 2000

Sunday, February 19, 2012

Complete idiot in dire need of help. :)

Alright, I've been trying to get this working for for-friggin ever, but it doesn't seem to work... :(

Here's the script:

<%
Set Text = request.form ("textfield")
Text = escape(Text)
Text = Replace(Text,"%0D%0A","<br>")
Text = unescape(Text)

Dim DB
Set DB = Server.CreateObject ("ADODB.Connection")
DB.Mode = adModeReadWrite
DB.Open ("PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=" + "DB\example.mdb")
Dim RS
Set RS = Server.CreateObject ("ADODB.Recordset")
RS.Open "startpage", DB, adOpenDynamic
RS.AddNew
RS ("content") = Text
RS.Update
%>

And I get:
ADODB.Recordset- Error '800a0cb3'

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

H:\Web Server\htdocs\CCI\forms2.asp, line 14

Guessing this is because i am, as the subject suggests, a complete idiot. :P

So perhaps someone here would find it in their heart to enlighten me?On closer inspection, that probably shouldve been posted in the ASP forum, huh?

Friday, February 10, 2012

Comparing Two Tables Without a Cursor...HELP!

I have a project I am working on that has two tables. One is a reference
table, and the other is a table that stores incoming data from an outside
vendor. The customer wants us to write a "solution" that will compare the
incoming data table to the lookup table, and find records that don't match.
Any records in the incoming data table should have corresponding records in
the lookup table, and specific columns need to match. The "solution" will
create a list of records with anomolies.
I had planned to write a cursor to march through the lookup table and check
for records in the incoming data table, but the customer feels cursors are a
bad idea, and does not want us to use them.
Are there alternatives to cursors?
BV.Please post DDL along with
1) definition of matching rows (e.g., incoming.col1 = lookup.col1 and
incoming.col2 = lookup.col2...)
2) sample data [match and mismatch]
3) which data you're returning - in most cases, the desired result is
the incoming data w/o corresponding lookup values. however, you're
initial approach sounds like you want lookups that aren't in the
incoming data. whichever, a cursor is indeed unnecessary and probably bad
w/o the above all you'll get is guesses, like this
-- use NOT EXISTS
select * -- list columns in real code
from incoming i
where not exists (
select *
from lookup
where col1 = i.col1
and col2 = i.col2
)
BenignVanilla wrote:
> I have a project I am working on that has two tables. One is a reference
> table, and the other is a table that stores incoming data from an outside
> vendor. The customer wants us to write a "solution" that will compare the
> incoming data table to the lookup table, and find records that don't match
.
> Any records in the incoming data table should have corresponding records i
n
> the lookup table, and specific columns need to match. The "solution" will
> create a list of records with anomolies.
> I had planned to write a cursor to march through the lookup table and chec
k
> for records in the incoming data table, but the customer feels cursors are
a
> bad idea, and does not want us to use them.
> Are there alternatives to cursors?
> BV.
>|||"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:Odbq1JkFGHA.516@.TK2MSFTNGP15.phx.gbl...
> Please post DDL along with
> 1) definition of matching rows (e.g., incoming.col1 = lookup.col1 and
> incoming.col2 = lookup.col2...)
> 2) sample data [match and mismatch]
> 3) which data you're returning - in most cases, the desired result is the
> incoming data w/o corresponding lookup values. however, you're initial
> approach sounds like you want lookups that aren't in the incoming data.
> whichever, a cursor is indeed unnecessary and probably bad
Trey, thanks for helping me clarify. Here goes...In the sample data below, I
have a lookup table with a list of people, and an Incoming table, with new
records. I need to be able to run a query whereby I search for either name,
address, or phone in the lookup table, and compare the records in the
Incoming table to ensure the incoming data is accurate.
Using the data below, let's assume I am searching by name. The results of
this run of the "solution" would return a hit on the Bob row, as the
incoming address does not match what is in the lookup table.
My plan was to cursor through the lookup table, fetching rows from the
incoming data table, and generate my report output into a temp table, then
return the contents of the temp table.
Does this help clarify my issue?
Lookup Table
Name Address Phone
Bob Maine 410-555-1212
Tom New Jersey 908-555-1234
Jane Delware 402-555-4392
Incoming Table
Name Address Phone
Bob Georgia 410-555-1212
Tom New Jersey 908-555-1234
Jane Delware 402-555-4392|||You're approaching the problem from a procedural perspective; try to
think relationally. You do not need a cursor for this; a simple SQL
join will do it for you.
SELECT inc.Name, inc.Address, inc.Phone
FROM Incoming inc LEFT JOIN Reference ref
ON inc.Name = ref.Name AND inc.Address = ref.Address AND inc.Phone =
ref.Phone
WHERE ref.Name is NULL
You can use NOT EXISTS as suggested by Trey above, and it may perform
faster; I'm just used to reading LEFT JOIN's.
HTH,
Stu|||"Stu" <stuart.ainsworth@.gmail.com> wrote in message
news:1136951096.202363.67240@.z14g2000cwz.googlegroups.com...
> You're approaching the problem from a procedural perspective; try to
> think relationally. You do not need a cursor for this; a simple SQL
> join will do it for you.
> SELECT inc.Name, inc.Address, inc.Phone
> FROM Incoming inc LEFT JOIN Reference ref
> ON inc.Name = ref.Name AND inc.Address = ref.Address AND inc.Phone =
> ref.Phone
> WHERE ref.Name is NULL
> You can use NOT EXISTS as suggested by Trey above, and it may perform
> faster; I'm just used to reading LEFT JOIN's.
>
Stu, this was our option #2, I just wasn't sure it was a good idea from
performance angle, so I thought I'd avoid it. My concern is that the
customer wants to be able to do the comparison via different comparisons of
fields, by name, by address, etc. So I'll need to write different
statements, which is not a big deal, I am just worried that if one of these
fields is not indexed, and I am joining millions of rows to millions of
rows...what will that do to the server?
BV.|||Stu wrote:
> You're approaching the problem from a procedural perspective; try to
> think relationally. You do not need a cursor for this; a simple SQL
> join will do it for you.
> SELECT inc.Name, inc.Address, inc.Phone
> FROM Incoming inc LEFT JOIN Reference ref
> ON inc.Name = ref.Name AND inc.Address = ref.Address AND inc.Phone =
> ref.Phone
> WHERE ref.Name is NULL
> You can use NOT EXISTS as suggested by Trey above, and it may perform
> faster; I'm just used to reading LEFT JOIN's.
> HTH,
> Stu
instead of joining the tables and compare the cols it is sometimes
easieer (especially with many many columns to compare) to use the
checksum() functions of SQL Server.
you can do things like
select * from incoming x where checksum(name,address,phone) in (select
checksum(name,address,phone) from lookup)
to compare many many rows very easily. I dont know if this violates some
best practices or something, but checksum() helped me out very often.
hth
Gregor Stefka|||As long as you are joining on the key fields every time, performance should
not be an issue with this approach. I am assuming that the key fields have
to be equal before you begin looking at any other criteria. Adding extra
criteria should not prevent you from using indexes (assuming the extra
criteria does not include an additional table).
"BenignVanilla" <bvanilla@.tibetanbeefgarden.com> wrote in message
news:JOudnY6-w907uFjeRVn-sw@.giganews.com...
> "Stu" <stuart.ainsworth@.gmail.com> wrote in message
> news:1136951096.202363.67240@.z14g2000cwz.googlegroups.com...
> Stu, this was our option #2, I just wasn't sure it was a good idea from
> performance angle, so I thought I'd avoid it. My concern is that the
> customer wants to be able to do the comparison via different comparisons
of
> fields, by name, by address, etc. So I'll need to write different
> statements, which is not a big deal, I am just worried that if one of
these
> fields is not indexed, and I am joining millions of rows to millions of
> rows...what will that do to the server?
> BV.
>|||Performance tuning is always an issue, especially with a very large
database; however, a JOIN will ALWAYS perform better than a cursor.
I'm not saying that cursors don't have their place; this just ain't one
of them.
If you're worried about performance, be sure that your indexes are
onthe appropriate joining columns, and you may consider reducing your
isolation level (since it sounds as if you are reporting on batched
data, rather than live data).
Stu

Thursday, March 22, 2012

Computed Column Formulas

Computed Column Formulas

Computed Column Formulas

Tuesday, March 20, 2012

Compress full backup file

Saturday, February 25, 2012

Complex query help needed....

Complex Math functions

complex join replication filtering is not working in 3.5 beta

Friday, February 24, 2012

complex join replication filtering is not working in 3.5 beta

complex join replication filtering is not working in 3.5 beta

complex join replication filtering is not working in 3.5 beta

complex join replication filtering is not working in 3.5 beta

Complex IIF statement not working

Complex Case Statement in View in SQL Server 2000

Complex Case Statement in View in SQL Server 2000

Sunday, February 19, 2012

Complete idiot in dire need of help. :)

Friday, February 10, 2012

Comparing Two Tables Without a Cursor...HELP!

Sql Comparing two tables

Blog Archive

About Me