Sql Comparing two tables: existing

Showing posts with label existing. Show all posts

Sunday, March 25, 2012

computed value

I've added a column to my table as a computed value, pretty much only to
populate it for the existing data. I want to do this, then drop the compute
d
value, make it's new column NOT NULL and lay over it a unique constraint.
Can I drop the computed value w/out dropping the column?
-- Lynn"Lynn" <Lynn@.discussions.microsoft.com> wrote in message
news:4052E967-8301-4F0D-A2FF-C721068FC0B3@.microsoft.com...
> I've added a column to my table as a computed value, pretty much only to
> populate it for the existing data. I want to do this, then drop the
> computed
> value, make it's new column NOT NULL and lay over it a unique constraint.
> Can I drop the computed value w/out dropping the column?
> -- Lynn
Why not add it as NOT NULL and default it to zero, or x or whatever.
Then run and update on the column and compute your new values.
Better yet, why are you storing a computed value anyhow? Why not just use a
SELECT statement or a view and compute the value on the fly?
Rick Sawtell|||A computed column or one being used in a computed column cannot be altered,
nor can a computed column be updated.
What are you traing to do? If you need to change the behaviour of a computed
column, you need to drop it first. You cannot add a non-nullable column
without specifying a default value.
However, you can create a temporary table to store the values of the
computed column, drop the column, add a new column (it needs to be nullable)
,
then fill it with previous values, and alter it to make it non-nullable.
And if you post DDL and sample data, we can help you build a script to
achieve all this.
ML|||Thank you both. I think I realized I was overthinking this one a bit. It
doesn't have to be computed. I put MsgID on as varchar(64) NOT NULL, update
d
it for existing data w/this:
UPDATE tableA...
SET MsgID = endpoint+(convert(varchar(8),[exectime],
112) + [ordernumber])
GO
Then I changed it to NOT NULL and created the unique constraint. All
w/existing data in the table. What do you guys think?
-- Lynn
"ML" wrote:

> A computed column or one being used in a computed column cannot be altered
,
> nor can a computed column be updated.
> What are you traing to do? If you need to change the behaviour of a comput
ed
> column, you need to drop it first. You cannot add a non-nullable column
> without specifying a default value.
> However, you can create a temporary table to store the values of the
> computed column, drop the column, add a new column (it needs to be nullabl
e),
> then fill it with previous values, and alter it to make it non-nullable.
> And if you post DDL and sample data, we can help you build a script to
> achieve all this.
>
> ML|||Does it work as it is supposed to work? :)
Looks like you've nailed it.
ML|||I don't know yet, it's still running now. I am sure hoping we're good on
this one...
-- Lynn
"ML" wrote:

> Does it work as it is supposed to work? :)
> Looks like you've nailed it.
>
> ML|||Worked beautifully. Thank you guys for looking into this w/me.
-- Lynn
"ML" wrote:

> Does it work as it is supposed to work? :)
> Looks like you've nailed it.
>
> ML|||On Wed, 7 Sep 2005 12:01:02 -0700, Lynn wrote:

>Thank you both. I think I realized I was overthinking this one a bit. It
>doesn't have to be computed. I put MsgID on as varchar(64) NOT NULL, updat
ed
>it for existing data w/this:
>UPDATE tableA...
>SET MsgID = endpoint+(convert(varchar(8),[exectime],
112) + [ordernumber])
>GO
>Then I changed it to NOT NULL and created the unique constraint. All
>w/existing data in the table. What do you guys think?
Hi Lynn,
If the MsgID column will always be the concatenation of these three
other columns, than I wouldn't store it like this. Create a view that
does the concatenation if you prefer the ease of use.
For enforcing uniqueness, just do
ALTER TABLE tableA
ADD CONSTRAINT MyUniq UNIQUE (endpoint, exectime, ordernumber)
No need to add an extra column for this.
Of course, if the MsgID for NEW rows in the database will be filled with
other values, and this concatenation is just the "starting" value for
existing rows, then the above doesn't apply.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Indexing that view might also be of help - that way the concatenated values
are stored as if the view were a table. Views aren't cached permanently.
However, as Hugo already stated, this only applies if the values need to be
generated by concatenation *every time*.
MLsql sql

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

Sunday, March 11, 2012

Complicated Update query based on existing data

OK folks, may have a tough one or perhaps just not thinking it through
well. I need to create a SQL query or queries that updates two columns
based on some business rules. Here's an example of the data:
GroupID complete_num first_num second_num InUse biggest
965423 1.0 1 0
965423 2.0 2 0
965423 3.0 3 0 X
965423 3.1 3 1
965423 3.2 3 2 X
324554 1.0 1 0
324554 2.0 2 0 X X
123456 0.1 0 1
123456 0.2 0 2 X X
Hopefully the above even vaguely lines up for you. The last two
columns are currently blank. The representation above is how I would
like them to look after the queries run. So for the above data, you
have a group id that links all records for one set together. I need to
have the "InUse" box updated with a value of "X" for the highest number
that has zero in the second_num column for a group. I also need the
biggest column set to "X" for the largest number in a particular group,
so 3.1 is larger than 3.0. Keep in mind, I have separated the
complete_num into two columns as there could be a "decimal" value of
"10" which is higher than "1". They are not the same as complete_num
is not really a decimal numeric representation. It's used
programatically for other things. Seperating into two separate columns
allows better sorting of data as complete_num is varchar and first and
second num are integer. You will also see the case, 123456,where there
is no zero in the second_num column. In this case, the highest
second_num value will have both set to "X". Any help would be
appreciated and SQL queries are prefered over any procedures/functions
as this is a one time query I need to run against the database. If you
need further clarification or more examples, please let me know.
Thanks.
JRAlso note that there is of course a unique incremental column in the
table. For arguements sake we can say U_ID. Also these records could
be in any order by default in the database. Not necessarily grouped
together as shown above.|||JR (jriker1@.yahoo.com) writes:
> OK folks, may have a tough one or perhaps just not thinking it through
> well. I need to create a SQL query or queries that updates two columns
> based on some business rules. Here's an example of the data:
> GroupID complete_num first_num second_num InUse biggest
> 965423 1.0 1 0
> 965423 2.0 2 0
> 965423 3.0 3 0 X
> 965423 3.1 3 1
> 965423 3.2 3 2 X
> 324554 1.0 1 0
> 324554 2.0 2 0 X X
> 123456 0.1 0 1
> 123456 0.2 0 2 X X
> Hopefully the above even vaguely lines up for you. The last two
> columns are currently blank. The representation above is how I would
> like them to look after the queries run. So for the above data, you
> have a group id that links all records for one set together. I need to
> have the "InUse" box updated with a value of "X" for the highest number
> that has zero in the second_num column for a group. I also need the
> biggest column set to "X" for the largest number in a particular group,
> so 3.1 is larger than 3.0.
It is always a good idea for this sort of question to include CREATE
TABLE statements for the table, and the sample data as INSERT statements.
That makes it easy to copy-and-paste into a query tool, to develop a
tested solution.
Thus, this is an untested solution:
BEGIN TRANSACTION
UPDATE tbl
SET biggest = 0,
inuse = 0
UPDATE tbl
SET biggest = 1
FROM tbl a
WHERE EXISTS (SELECT *
FROM (SELECT GroupID,
biggest = MAX(100000 * first_num + second_num)
FROM tbl
GROUP BY GroupID) AS big
WHERE a.big = big.GroupID
AND a.first_num * 1000000 + a.second_num = big.biggest)
UPDATE tbl
SET inuse = 1
FROM tbl a
JOIN (SELECT GroupID, first_num = MAX(first_num)
FROM tbl
WHERE second_num = 0
GROUP BY GroupID) AS inuse ON a.GroupID = inuse.GroupID
AND a.first_num = inuse.first_num
AND a.second_num = 0
UPDATE tbl
SET inuse = 1
FROM tbl a
WHERE a.biggest = 1
AND NOT EXISTS (SELECT *
FROM tbl b
WHERE a.GroupID = b.GroupID
AND a.insue = 1)
COMMIT TRANSACTION
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland. SQL Query Analyser is complaining about a syntax
problem with the '=' on "biggest = MAX(100000 * first_num +
second_num)" in the second update and "JOIN (SELECT GroupID,
first_num = MAX(first_num)" in the ghird.|||JR,
I think you can say this more simply: Update InUse to X
for the largest second_num value in the group *counting 0
as the largest*, and the largest first_num value in the case of
a tie. Update biggest to X for the largest first_num value
in the group, and the largest second_num value in the case
of a tie.
There is a more compact solution than Erland's in SQL Server 2005:
with T2 as (
select
*,
rank() over (partition by GroupID order by first_num desc,
case when second_num = 0 then 2147364827 else second_num end desc)
as rk1,
rank() over (partition by GroupID order by second_num desc,
first_num desc) as rk2
from #T
)
update T2 set
InUse = case when rk1 = 1 then 'X' else InUse end,
biggest = case when rk2 = 1 then 'X' else biggest end
where rk1 = 1 or rk2 = 1
Steve Kass
Drew University
JR wrote:

>OK folks, may have a tough one or perhaps just not thinking it through
>well. I need to create a SQL query or queries that updates two columns
>based on some business rules. Here's an example of the data:
>GroupID complete_num first_num second_num InUse biggest
>965423 1.0 1 0
>965423 2.0 2 0
>965423 3.0 3 0 X
>965423 3.1 3 1
>965423 3.2 3 2 X
>324554 1.0 1 0
>324554 2.0 2 0 X X
>123456 0.1 0 1
>123456 0.2 0 2 X X
>
>Hopefully the above even vaguely lines up for you. The last two
>columns are currently blank. The representation above is how I would
>like them to look after the queries run. So for the above data, you
>have a group id that links all records for one set together. I need to
>have the "InUse" box updated with a value of "X" for the highest number
>that has zero in the second_num column for a group. I also need the
>biggest column set to "X" for the largest number in a particular group,
>so 3.1 is larger than 3.0. Keep in mind, I have separated the
>complete_num into two columns as there could be a "decimal" value of
>"10" which is higher than "1". They are not the same as complete_num
>is not really a decimal numeric representation. It's used
>programatically for other things. Seperating into two separate columns
>allows better sorting of data as complete_num is varchar and first and
>second num are integer. You will also see the case, 123456,where there
>is no zero in the second_num column. In this case, the highest
>second_num value will have both set to "X". Any help would be
>appreciated and SQL queries are prefered over any procedures/functions
>as this is a one time query I need to run against the database. If you
>need further clarification or more examples, please let me know.
>Thanks.
>JR
>
>|||On 9 Apr 2006 07:36:13 -0700, JR wrote:

>OK folks, may have a tough one or perhaps just not thinking it through
>well. I need to create a SQL query or queries that updates two columns
>based on some business rules. Here's an example of the data:
(snip)
Hi JR,
If these columns should be calculated based on the data, why store them
at all? I would only recommend that if updates are very infrequent,
queries of the data are very frequent and the table is large, or if
these columns need to capture a moment in time and stay unchanged after
that, even if the underlying data does change.
In one of the latter cases, use Erland's suggestion. Otherwise, drop the
columns from the table and set up a view instead:
CREATE VIEW ChooseGoodName
AS
SELECT a.GroupID, a.complete_num, a.first_num, a.second_num,
CASE WHEN a.first_num = b.first_num
AND a.second_num = 0
THEN 'X' -- Latest X.0
WHEN a.first_num = 0
AND a.second_num = b.second_num
THEN 'X' -- Latest 0.Y
ELSE ''
END AS InUse,
CASE WHEN b.first_num = a.first_num
AND b.second_num = a.second_num
THEN 'X'
ELSE ''
END AS biggest
FROM YourTable AS a
INNER JOIN (SELECT GroupID, complete_num, first_num, second_num
FROM YourTable AS c
WHERE NOT EXISTS (SELECT *
FROM YourTable AS d
WHERE d.GroupID = c.GroupID
AND ( d.first_num > c.first_num
OR ( d.first_num = c.first_num
AND d.second_num > c.second_num))
) ) AS b
ON b.GroupID = a.GroupID
(Untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Hugo Kornelis, SQL Server MVP|||JR (jriker1@.yahoo.com) writes:
> Thanks Erland. SQL Query Analyser is complaining about a syntax
> problem with the '=' on "biggest = MAX(100000 * first_num +
> second_num)" in the second update and "JOIN (SELECT GroupID,
> first_num = MAX(first_num)" in the ghird.
Yes, as I said the code was untested for reasons I explained. I assume
that you are able to weed out trivial syntax errors on your own. If not,
please include CREATE TABLE statements and the sample data in INSERT
statements, to make testing easy.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||As requested, here is a table creation script and some minimal data to
limit the length of the message. Also Hugo's view is great however
since this data is for one time use, would imagine updates to the
existing data would be preferable over introducing a new view into the
mix. If not updating the data directly based on the view would now be
a simple matter when you introduce the Id from the original table into
the view.
CREATE TABLE [abcd].[dbo].[TBL1
(Id,GroupId,complete_num,first_num,secon
d_num)] (
[Id] int NOT NULL,
[GroupID] nvarchar (60) NULL,
[complete_num] varchar (255) NULL,
[first_num] integer,
[second_num] integer,
[InUse] varchar (2) NULL,
[biggest] varchar (2) NULL,
)
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(91510,ABC1235,2.0,2,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(89377,ABC1235,2.1,2,1);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(89371,ABC1235,2.2,2,2);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1310,M123456,1.0,1,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1309,M123456,2.0,2,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1311,M123456,3.0,3,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1312,M123456,4.0,4,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1315,M123456,5.0,5,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1318,M123456,6.0,6,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1319,M123456,7.0,7,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1317,M123456,8.0,8,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(5342,M123456,9.0,9,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(5346,M123456,10.0,10,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(5756,M123456,11.0,11,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(6315,M123456,12.0,12,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(6604,M123456,13.0,13,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(6920,M123456,14.0,14,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(1002,M123456,15.0,15,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES
(4023,ARDFO32,0.1,0,1);|||Your programs will be total nightmares and crap until you learn how to
design a schema.
Look at the DDL; do you really have a NCHAR(60) grpoup identifier? In
Chinese' Wel;l;, since you allowed it, you will get one! Why do you
have redundant split attributes (i.e. first_num || second_num =
complete_num)? Let's spit on normalization!! I also love the clear,
meaningful names of the data elements. Tell us what a thing is, not
its sequential order inside another column. Logical not physical
descriptions.
There are no keys, no constraints. This is not a table at all! And
you invented your own syntax for CREATE TABLE.
Your idea of updatind computed columns is a way to mimic punch cards.
Back in the 1950-60's we had to store those things in the physical
card, like you are doing now.
Making a guess, if you normalized your schema, had a key and followed
the baisc data modeling rules, would this nightmare look more like
this? Better names that show subordination (well, Foobar is a dummy
name, but that is all you gave us)
CREATE TABLE Foobar
(group_id CHAR(6) NOT NULL
CHECK (group_id LIKE '[0-9][0-9][0-9][0-9][0-9][0-9]')
section_nbr INTEGER DEFAULT 1 NOT NULL
CHECK (section_num >= 0),
subsection_nbr INTEGER DEFAULT 0 NOT NULL
CHECK (subsection_num >= 0),
PRIMARY KEY (group_id, section_nbr, subsection_nbr));
Do you need a constaint to assure that the subsections are in sequence?
Is there a check digit rule in the group_id? 90% of the work in RDBMS
is done in the DDL!!
SQL does not have links; it has REFERENCES and grouping. Totally
different concepts, based on sets and not pre-RDBMS file and pointer
systems. Rows are nothing whatsoever like records.
Now, the answer to your question is a VIEW, not a "punch cards and bit
flags" solution via updates.
CREATE VIEW InUseFoobar (group_id, section_nbr, subsection_nbr)
AS
SELECT group_id, MAX(section_nbr), 0
FROM Foobar
WHERE subsection = 0
GROUP BY group_id) ;
CREATE VIEW MaxFoobar (group_id, section_nbr, subsection_nbr)
AS
SELECT group_id, section_nbr, MAX(subsection_nbr)
FROM Foobar AS F1, InUseFoobar AS U1
WHERE F1.group_id = .U1.group_id
AND F1.section_nbr = .U1.section_nbr
GROUP BY group_id, section_nbr ;
<<untested>>|||JR (jriker1@.yahoo.com) writes:
> As requested, here is a table creation script and some minimal data to
> limit the length of the message. Also Hugo's view is great however
> since this data is for one time use, would imagine updates to the
> existing data would be preferable over introducing a new view into the
> mix. If not updating the data directly based on the view would now be
> a simple matter when you introduce the Id from the original table into
> the view.
Below is a tested version of my script. I'm not sure that I understand
the syntax errors you mentioned; I did not get these. I include
your original script, with small changes. Note that I've made inuse
and biggest into bit columns; I did this as I used 0 and 1 in my script.
Beware that things get wrapped in news transport!
CREATE TABLE [dbo].[TBL1]
(
[Id] int NOT NULL,
[GroupId] nvarchar (60) NULL,
[complete_num] varchar (255) NULL,
[first_num] integer,
[second_num] integer,
[inuse] bit NULL,
[biggest] bit NULL,
)
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (9151
0,'ABC1235',2.0,2,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (8937
7,'ABC1235',2.1,2,1);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (8937
1,'ABC1235',2.2,2,2);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1310
,'M123456',1.0,1,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1309
,'M123456',2.0,2,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1311
,'M123456',3.0,3,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1312
,'M123456',4.0,4,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1315
,'M123456',5.0,5,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1318
,'M123456',6.0,6,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1319
,'M123456',7.0,7,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1317
,'M123456',8.0,8,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (5342
,'M123456',9.0,9,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (5346
,'M123456',10.0,10,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (5756
,'M123456',11.0,11,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (6315
,'M123456',12.0,12,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (6604
,'M123456',13.0,13,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (6920
,'M123456',14.0,14,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (1002
,'M123456',15.0,15,0);
INSERT INTO TBL1 (Id,GroupId,complete_num,first_num,secon
d_num) VALUES (4023
,'ARDFO32',0.1,0,1);
go
BEGIN TRANSACTION
UPDATE TBL1
SET biggest = 0,
inuse = 0
UPDATE TBL1
SET biggest = 1
FROM TBL1 a
WHERE EXISTS (SELECT *
FROM (SELECT GroupId,
biggest = MAX(100000 * first_num + second_num)
FROM TBL1
GROUP BY GroupId) AS big
WHERE a.GroupId = big.GroupId
AND a.first_num * 100000 + a.second_num = big.biggest)
UPDATE TBL1
SET inuse = 1
FROM TBL1 a
JOIN (SELECT GroupId, first_num = MAX(first_num)
FROM TBL1
WHERE second_num = 0
GROUP BY GroupId) AS inuse ON a.GroupId = inuse.GroupId
AND a.first_num = inuse.first_num
AND a.second_num = 0
UPDATE TBL1
SET inuse = 1
FROM TBL1 a
WHERE a.biggest = 1
AND NOT EXISTS (SELECT *
FROM TBL1 b
WHERE a.GroupId = b.GroupId
AND b.inuse = 1)
COMMIT TRANSACTION
SELECT * FROM TBL1
go
drop table TBL1
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Complicated query - select based on value

SQL2K on W2Kserver

I need some help revamping a rather complicated query. I've given the
table and existing query information below. (FYI, changing the
database structure is right out.)

The current query lists addresses with two particular types
('MN30D843J2', 'SC93JDL39D'). I need to change this to (1) check each
contact for address type 'AM39DK3KD9' and then (2) if the contact has
type 'AM39DK3KD9' select types ('AM39DK3KD9', 'ASKD943KDI') OR if the
contact does not have that type then select types ('MN30D843J2',
'SC93JDL39D'). (Context - the current query selects two standard
address types "Main" and "Secondary"; we've added new data and now have
types "Alternate Main" and "Alternate Secondary". If the Contact has
Alternate addresses, I need to select those; if not, I need to select
the standard addresses. There are other address types in use, so I
must specify which types to select.)

Can anyone point me in the right direction?

Thanks very much! jamileh

CREATE TABLE [CONTACTS] (
[CONTACT_X] [char] (10),
[LONGNAME] [char] (75),
[ACTIVE] [bit])

CREATE TABLE [CONTACTADDRESSES] (
[CONTACT_X] [char] (10),
[ADDRESS_X] [char] (10),
[ADDRESSTYPE_REFX] [char] (10),
[ACTIVE] [bit])

CREATE TABLE [ADDRESSES] (
[ADDRESS_X] [char] (10),
[ADDRESSLINE1] [char] (60),
[ADDRESSLINE2] [char] (60),
[CITY] [char] (20),
[STATE] [char] (2),
[ZIPCODE] [char] (11),
[PHONE] [char] (10))

CREATE TABLE [REFERENCETABLE] (
[REFERENCETABLE_X] [char] (10),
[ADDRESS_X] [char] (10),
[DESCRIPTION] [char] (60))

CREATE TABLE [MASTERTABLE] (
[CONTACT_X] [char] (10),
[RECORDTYPE] [char] (1),
[ACTIVE] [bit])

CREATE VIEW vw_CONTACTInfo_ListLoc
AS
SELECT CONTACTS.CONTACT_X, CONTACTS.LONGNAME,
CONTACTADDRESSES.ADDRESSTYPE_REFX,
Type_REFERENCETABLE.DESCRIPTION AS Type_DESCRIPTION,
CONTACTADDRESSES.ADDRESS_X, ADDRESSES.ADDRESSLINE1,
ADDRESSES.ADDRESSLINE2, ADDRESSES.CITY, ADDRESSES.STATE,
ADDRESSES.ZIPCODE, ADDRESSES.PHONE
FROM CONTACTS INNER JOIN CONTACTADDRESSES ON
CONTACTS.CONTACT_X = CONTACTADDRESSES.CONTACT_X INNER JOIN
ADDRESSES ON CONTACTADDRESSES.ADDRESS_X =
ADDRESSES.ADDRESS_X
INNER JOIN REFERENCETABLE Type_REFERENCETABLE ON
CONTACTADDRESSES.ADDRESSTYPE_REFX =
Type_REFERENCETABLE.REFERENCETABLE_X
WHERE (CONTACTS.ACTIVE = 1) AND (CONTACTADDRESSES.ADDRESSTYPE_REFX
IN
('MN30D843J2', 'SC93JDL39D') AND (CONTACTADDRESSES.ACTIVE =
1)) AND
(CONTACTS.CONTACT_X IN
(SELECT CONTACT_X FROM MASTERTABLE WHERE
ACTIVE = 1 AND RECORDTYPE = 'E'))"jqq" <jqq@.myrealbox.com> wrote in message
news:1120839746.620891.96250@.f14g2000cwb.googlegro ups.com...
> SQL2K on W2Kserver
> I need some help revamping a rather complicated query. I've given the
> table and existing query information below. (FYI, changing the
> database structure is right out.)
> The current query lists addresses with two particular types
> ('MN30D843J2', 'SC93JDL39D'). I need to change this to (1) check each
> contact for address type 'AM39DK3KD9' and then (2) if the contact has
> type 'AM39DK3KD9' select types ('AM39DK3KD9', 'ASKD943KDI') OR if the
> contact does not have that type then select types ('MN30D843J2',
> 'SC93JDL39D'). (Context - the current query selects two standard
> address types "Main" and "Secondary"; we've added new data and now have
> types "Alternate Main" and "Alternate Secondary". If the Contact has
> Alternate addresses, I need to select those; if not, I need to select
> the standard addresses. There are other address types in use, so I
> must specify which types to select.)
> Can anyone point me in the right direction?
> Thanks very much! jamileh

<snip
The short answer is probably to see CASE in Books Online. If you need more
information, I suggest you provide some INSERT statements for sample data,
and also the output you expect - it's not very clear (to me) exactly what
your query should return.

http://www.aspfaq.com/etiquette.asp?id=5006

Simon|||If the Contact has (INNER JOIN)
Alternate addresses, I need to select those;

UNION ALL
if not, NOT EXISTS(...)

I need to select
the standard addresses.|||Without better specs, this is hard. The tables had no keys; the names
of the data elements are awful, you even put physical storage and usage
into the names! You are using bit flags in SQL. There does not seem to
be any consistent design here. Clraning it up a bit, I got this:

CREATE TABLE Contacts
(contact_id CHAR(10) NOT NULL PRIMARY KEY,
long_name CHAR(75) NOT NULL);

CREATE TABLE Addresses
(address_id CHAR(10) NOT NULL PRIMARY KEY,
address_line1 CHAR(35) NOT NULL, -- usps lengths
address_line2 CHAR(35) NOT NULL,
city_name CHAR(20) NOT NULL,
state_code CHAR(2) NOT NULL,
zip_code CHAR(9) NOT NULL,
phone_nbr CHAR(10) NOT NULL));

Your codes belong to the relationship, and not in their own tables,
something more like this

CREATE TABLE ContactAddresses
(contact_id CHAR(10) NOT NULL
REFERENCES Contacts (contact_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
address_id CHAR(10) NOT NULL
REFERENCES Addresses (address_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
address_type INTEGER NOT NULL, -- see suggestion below
PRIMARY KEY (contact_id, address_id, address_type)
contact_status CHAR(3) DEFAULT 'act' NOT NULL
CHECK (contact_status IN ('act', 'old', ..));

Views should be kept simple so they can be used in many place. And
unless they dela wiyth a Volkswagen, you do not prefix them with "vw_"
:)

CREATE VIEW ContactInfo (contact_id, long_name,
address_type, address_id,
address_line1, address_line2,
city_name, state_code, zip_code,
phone_nbr)
AS
SELECT C.contact_id, C.long_name,
CA.address_type, CA.address_id,
A.address_line1, A.address_line2,
A.city_name, A.state_code, A.zip_code,
A.phone_nbr
FROM Contacts AS C,
ContactAddresses AS CA,
Addresses AS A
WHERE CA.contact_id = C.contact_id
AND CA.address_id = A.address_id
AND CA.address_type BETWEEN 100 AND 299;

>> the current query selects two standard address types "Main" and "Secondary"; we've added new data and now have types "Alternate Main" and "Alternate Secondary". If the Contact has Alternate addresses, I need to select those; if not, I need to select the standard addresses. <<

You need a better encoding scheme than those awful ten-letter
nightmares. I read your narrative as meaning one contact can have only
one address of each type. Here is a hierarchical encoding suggestion,
with some room for growth.

100-199 = Main Address
110-119 = Alternative Main Address
200-299 = Secondary Address
210-219 = Alternative Secondary Address

The query would be something like this:

SELECT DISTINCT I1.*
FROM ContactInfo AS I1
WHERE address_type IN (200, 210) -- has secondary address
OR (address_type IN (100, 110) -- has main address
AND NOT EXISTS
(SELECT *
FROM ContactInfo AS I2
WHERE address_type IN (200, 210) -- no secondary address
AND I1.contact_id = I2.contact_id
AND I1.address_id = I2.address_id));|||My apologies for leaving out the data, I didn't want to get too long in
my original post if it wasn't needed. Please see below (including one
fix on a table).

So, the current query will pull John Smith's "standard" addresses on
Main, Second, and Third streets, plus Frank Doe's "standard" addresses
on Main, Second and Third streets.

The results I need would give John Smith's "standard" addresses on
Main, Second, and Third streets, plus Frank Doe's "alternate" addresses
on Fifth and Sixth streets.

I've used CASE, but not to select multiple records based on one field.
I'm not sure how to make that work and I can't find anything in BOL to
explain it.

Thanks.

ALTER TABLE [REFERENCETABLE] DROP COLUMN [ADDRESS_X]

INSERT [CONTACTS] VALUES ('A1','John Smith',1)
INSERT [CONTACTS] VALUES ('B2','Frank Doe',1)
INSERT [CONTACTS] VALUES ('C3','Jane Jones',1)
INSERT [CONTACTS] VALUES ('D4','Susan Roe',0)
INSERT [CONTACTS] VALUES ('E5','George Brown',1)

INSERT [CONTACTADDRESSES] VALUES ('A1','F1','MN30D843J2',1)
INSERT [CONTACTADDRESSES] VALUES ('A1','G2','SC93JDL39D',1)
INSERT [CONTACTADDRESSES] VALUES ('A1','H3','SC93JDL39D',1)
INSERT [CONTACTADDRESSES] VALUES ('A1','I4','BL2309DD3L',1)
INSERT [CONTACTADDRESSES] VALUES ('A1','J5','AM39DK3KD9',0)
INSERT [CONTACTADDRESSES] VALUES ('B2','K6','MN30D843J2',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','L7','SC93JDL39D',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','M8','SC93JDL39D',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','N9','BL2309DD3L',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','O0','AM39DK3KD9',1)
INSERT [CONTACTADDRESSES] VALUES ('B2','P1','ASKD943KDI',1)
INSERT [CONTACTADDRESSES] VALUES ('C3','Q2','AM39DK3KD9',1)
INSERT [CONTACTADDRESSES] VALUES ('D4','R3','AM39DK3KD9',1)
INSERT [CONTACTADDRESSES] VALUES ('E5','S4','AM39DK3KD9',1)

INSERT [ADDRESSES] VALUES ('F1','123 Main
St','','Anytown','PA','12345','5074951548')
INSERT [ADDRESSES] VALUES ('G2','456 Second St','Apt
9','Anytown','PA','45678','5074328548')
INSERT [ADDRESSES] VALUES ('H3','789 Third
St','','Anytown','PA','45678','5074321111')
INSERT [ADDRESSES] VALUES ('I4','987 Fourth
St','','Anytown','PA','12345','5074959999')
INSERT [ADDRESSES] VALUES ('J5','654 Fifth
St','','Anytown','PA','12345','5074955555')
INSERT [ADDRESSES] VALUES ('K6','1 Main
St','','Somewhere','UT','87654','2426831234')
INSERT [ADDRESSES] VALUES ('L7','2 Second St','Suite
600','Somewhere','UT','87654','2426835678')
INSERT [ADDRESSES] VALUES ('M8','3 Third
St','','Somewhere','UT','87654','2426839876')
INSERT [ADDRESSES] VALUES ('N9','4 Fourth
St','','Somewhere','UT','87654','2426835432')
INSERT [ADDRESSES] VALUES ('O0','5 Fifth
St','','Somewhere','UT','87654','2426831111')
INSERT [ADDRESSES] VALUES ('P1','6 Sixth
St','','Somewhere','UT','87654','2426839999')
INSERT [ADDRESSES] VALUES ('Q2','123 NoGood
St','','Nowhere','AK','98765','9051875135')
INSERT [ADDRESSES] VALUES ('R3','456 NotMe
St','','Nonesuch','CA','43210','7631584625')
INSERT [ADDRESSES] VALUES ('S4','789 UhOh
St','','Noway','GA','36847','6427892462')

INSERT [REFERENCETABLE] VALUES ('MN30D843J2','Standard Main')
INSERT [REFERENCETABLE] VALUES ('SC93JDL39D','Standard Secondary')
INSERT [REFERENCETABLE] VALUES ('AM39DK3KD9','Alternate Main')
INSERT [REFERENCETABLE] VALUES ('ASKD943KDI','Alternate Secondary')
INSERT [REFERENCETABLE] VALUES ('BL2309DD3L','Billing Only')

INSERT [MASTERTABLE] VALUES ('A1','E',1)
INSERT [MASTERTABLE] VALUES ('B2','E',1)
INSERT [MASTERTABLE] VALUES ('C3','N',1)
INSERT [MASTERTABLE] VALUES ('D4','E',1)
INSERT [MASTERTABLE] VALUES ('E5','E',0)|||As I said, changing the database structure is right out. I didn't
build the beastie - it's the backend for a proprietary application.
The only thing I can do is pull data.

These views are for the exact purpose of selecting very specific sets
of data to provide to some websites. This one's not half bad, you
should see some of the others!

I did leave out PKs, etc. - sorry. The tables are actually much
bigger & I was trying to just pull the needed info for simplicity. If
there's something specific that would help, please let me know & I'll
post an update. In general, you're right and the "tablename_K" columns
are PKs and FKs.

As you can see in my last post (with data), one contact can have
multiple addresses of each type and I need to pull all addresses of the
correct types.

Any rate, thanks for the advice! j

p.s. What's wrong with bit flags?|||I read this several times without any clue what you meant, but I think
it's beginning to permeate into my poor, bleeding braincells. I'll see
what I can come up with. Thanks!|||jqq (jqq@.myrealbox.com) writes:
> p.s. What's wrong with bit flags?

Nothing.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||jqq (jqq@.myrealbox.com) writes:
> My apologies for leaving out the data, I didn't want to get too long in
> my original post if it wasn't needed. Please see below (including one
> fix on a table).
> So, the current query will pull John Smith's "standard" addresses on
> Main, Second, and Third streets, plus Frank Doe's "standard" addresses
> on Main, Second and Third streets.
> The results I need would give John Smith's "standard" addresses on
> Main, Second, and Third streets, plus Frank Doe's "alternate" addresses
> on Fifth and Sixth streets.

This query gives the result described above, but does not match your
description in the first post. But maybe you simply messed up on all
these terrible 10-letter codes when you composed the sample data.

SELECT C.CONTACT_X, C.LONGNAME, CA.ADDRESSTYPE_REFX,
R.DESCRIPTION AS Type_DESCRIPTION, CA.ADDRESS_X,
A.ADDRESSLINE1, A.ADDRESSLINE2, A.CITY, A.STATE, A.ZIPCODE,
A.PHONE
FROM CONTACTS C
JOIN CONTACTADDRESSES CA ON C.CONTACT_X = CA.CONTACT_X
JOIN ADDRESSES A ON CA.ADDRESS_X = A.ADDRESS_X
JOIN REFERENCETABLE R ON CA.ADDRESSTYPE_REFX = R.REFERENCETABLE_X
WHERE C.ACTIVE = 1
AND CA.ADDRESSTYPE_REFX IN ('MN30D843J2', 'SC93JDL39D')
AND CA.ACTIVE = 1
AND C.CONTACT_X IN (SELECT M.CONTACT_X
FROM MASTERTABLE M
WHERE M.ACTIVE = 1
AND M.RECORDTYPE = 'E')
AND NOT EXISTS (SELECT *
FROM CONTACTADDRESSES CA1
WHERE C.CONTACT_X = CA1.CONTACT_X
AND CA1.ADDRESSTYPE_REFX IN ('ASKD943KDI'))
UNION ALL
SELECT C.CONTACT_X, C.LONGNAME, CA.ADDRESSTYPE_REFX,
R.DESCRIPTION AS Type_DESCRIPTION, CA.ADDRESS_X,
A.ADDRESSLINE1, A.ADDRESSLINE2, A.CITY, A.STATE, A.ZIPCODE,
A.PHONE
FROM CONTACTS C
JOIN CONTACTADDRESSES CA ON C.CONTACT_X = CA.CONTACT_X
JOIN ADDRESSES A ON CA.ADDRESS_X = A.ADDRESS_X
JOIN REFERENCETABLE R ON CA.ADDRESSTYPE_REFX = R.REFERENCETABLE_X
WHERE C.ACTIVE = 1
AND CA.ADDRESSTYPE_REFX IN ('AM39DK3KD9', 'ASKD943KDI')
AND CA.ACTIVE = 1
AND C.CONTACT_X IN (SELECT M.CONTACT_X
FROM MASTERTABLE M
WHERE M.ACTIVE = 1
AND M.RECORDTYPE = 'E')

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||They are a proprietary, low level data type. The design is the way we
handled things with punch cards in the old days. Flags are usually
computed columns. It is usually better to invent a status code which
can be extended, or to capture the date of an event, etc.|||Wow, I went off to read up on EXISTS and came back to find the query
all done!

That's exactly what I needed *and* I learned a new trick.

Thanks very much!!

Sunday, March 25, 2012

computed value

Thursday, March 22, 2012

Computed Column Formulas

Computed Column Formulas

Computed Column Formulas

Sunday, March 11, 2012

Complicated Update query based on existing data

Complicated query - select based on value

Sql Comparing two tables

Blog Archive

About Me