Showing posts with label address. Show all posts
Showing posts with label address. Show all posts

Friday, February 24, 2012

Help with WHERE CLAUSE

I'm having a heck of time with this where clause. I have a table that contains client addresses, a client can have more than one address. So some of the addresses may be seasonal. I need to return only the current address based on a flag MailTo (bit) and a date range, just the month and day, the start and end are datetime datatypes.

Here is what i have tried:

I would really would like it to work on a range of month and day based on the startdate and enddate fields and the MailTo flag.
The table looks like this;

tblClientAddresses:
Address_ID,Client_ID,Address,Address2,City,State,Zip,Country,AddressType,StartDate,
EndDate,MailTo

WHERE (A.MailTo=1) AND (A.EndDate Is Null OR DatePart(mm,A.Enddate) >= DatePart(mm,GETDATE()) AND DatePart(dd,A.Enddate) >= DatePart(dd,GETDATE()))

Thank you for any help!I have tried this:

WHERE (A.MailTo=1) AND (Month(Day(GETDATE())) BETWEEN Month(Day(A.Startdate)) AND(Month(Day(A.Enddate))) OR (A.Startdate Is Null) AND (A.EndDate Is Null))

But it still not returning the proper row.

Does anyone have an idea if what i show above is even doable?

Thanks|||Try this:

WHERE (A.MailTo=1)
AND Convert(varchar(5),GetDate(),10) BETWEEN
Convert(varchar(5),A.Startdate,10) AND Convert(varchar(5),A.Enddate,10)

The datetime format #10 is mm-dd-yy. If you limit the cast to varchar(5) you only get mm-dd.

JK|||That has fixed the date issue, but now the MailTo flag is being ignored. Should I adjust the paranthises somehow? I have moved the flag to the end and added paranthises but no luck.

Thank you very much|||Hmm. It is odd that the MailTo flag is being ignored.

Could you post your full query command with all the trimmings?
JK

Sunday, February 19, 2012

Help with update trigger

Hi all,
I know squat about triggers so was hoping somebody could point me in the
right direction. I wanted to copy an email address field from a salesman
table to a note field in a customer table. Seems easy enough for a one time
update. But I would like to add a trigger to auto-update the customer table
anytime an email address changes in the saleman table or a new salesman
record is added.

Here's my update script (this copies the salesman email address to each of
his customers)
UPDATE CUSTOMERS
SET NOTE_5 = SALESMAN.EMAIL_ADDR
FROM CUSTOMERS INNER JOIN
SALESMAN ON CUSTOMERS.SLSPSN_NO = SALESMAN.SLSPSN_NO

How can I turn this into a trigger for automatic updates?

Thanks for any help.rdraider (rdraider@.sbcglobal.net) writes:
> I know squat about triggers so was hoping somebody could point me in the
> right direction. I wanted to copy an email address field from a
> salesman table to a note field in a customer table. Seems easy enough
> for a one time update. But I would like to add a trigger to auto-update
> the customer table anytime an email address changes in the saleman table
> or a new salesman record is added.
> Here's my update script (this copies the salesman email address to each of
> his customers)
> UPDATE CUSTOMERS
> SET NOTE_5 = SALESMAN.EMAIL_ADDR
> FROM CUSTOMERS INNER JOIN
> SALESMAN ON CUSTOMERS.SLSPSN_NO = SALESMAN.SLSPSN_NO
>
> How can I turn this into a trigger for automatic updates?

CREATE TRIGGER salesman_tri FOR INSERT, UPDATE ON SALESMAN AS
UPDATE CUSTOMERS
SET NOTE_5 = i.EMAIL_ADDR
FROM CUSTOMERS c
JOIN inserted c.SLSPSN_NO = i.SLSPSN_NO

"inserted" is a virtual table that holds the row that were inserted, or
the after-image of the updated rows.

"deleted" is a sister table that holds deleted rows, or the before-image
of the updated rows.

Note that triggers fires once per statement, so these tables can include
many rows.

You can only access these tables directly in a trigger.

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