Customer should receive email only once

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Customer should receive email only once

Author

Topic

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2007-01-31 : 21:49:04

Hello Guys,
I really need you help to debug this query.
OBJECTIVE:THE QUERY SHOULD GIVE ME THE FIELDS I MENTIONED IN THE FIRST QUERY WITH THE CONDITIONS BELOW.
CONDITION 1: RateReview field should have yesterday's date
CONDITION 2: Email will be send to customer only once so Customer_GUID is UniqueIdentifier
CONDITION 3: Customer shouldnt' have opted to get out from receiving any email so Termination field should be NULL
ONe Customer can have many transwactions
Is there any way i write the code specifying that no email should be sent more than once evereven if customer buys 10 tickets.
Only one email sent so i need to specify that if this email has gone to particulare CUSTOMER_GUID then Ignore that record and
do not send any email. This would be done by some tool known as StrongMail.

SELECT
CAST(a.Transaction_GUID AS varchar(36)) as Transaction_GUID,
CAST(a.Customer_GUID AS varchar(36)) as Customer_GUID,
Film_id as MovieId,
First_nm as FirstName,
Last_nm as LastName,
Email_nm as EmailAddress,

from
(
select
MIN(CAST(customer_guid AS varchar(36))) as Customer_GUID,
Transaction_GUID
from tblTransaction (nolock)
where RateReview_dm > DATEADD(dd,-1,GETDATE()) and RateReview_dm <
GETDATE()

and Terminate_dm is null
and customer_guid
not
in
(
select CAST(customer_guid AS varchar(36)) as Customer_GUID
from tblTransaction (nolock)
where RateReview_dm > DATEADD(dd,-1,GETDATE()) and RateReview_dm <
GETDATE()

and Terminate_dm is null
)
group by transaction_guid, customer_guid
) z
inner
join tblTransaction a
on z.Transaction_GUID = a.Transaction_GUID

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-31 : 21:55:52

CONDITION 1: RateReview field should have yesterday's date


where RateReview_dm > DATEADD(dd,-1,GETDATE()) and RateReview_dm < GETDATE() 

where RateReview_dm >= dateadd(day, datediff(day, 0, getdate()), -1) -- Greater or same as Yesterday day
and   RateReview_dm <  dateadd(day, datediff(day, 0, getdate()), 0)  -- Less than today's date

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2007-01-31 : 22:04:45

Hi,
Thank you so much for your prompt reply. Do you think that my query is right except what you told me to change.
Is there any other way to specify that Customer_GUID is Unique something like that.
Your help will be greatly appreciated.Thanks

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2007-01-31 : 22:17:34

Hello,
Could you please explain me the where clause which you want me to change. I know it is storing yesterday's date but i wanted to learn too so if you could explain me that would greatly appreciated.
My second question was no doubt in Unique Identifier datatype should not have null values but in my database 70% of Customer_GUID is null so do i need to use some null funtion Since right now it is not giving me any records.Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-01-31 : 22:17:57

maybe you can post the table structure (just the relevant columns) for customer_guid & transaction_guid with some sample data and the result that you want.

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2007-01-31 : 23:02:37

HELLO,
DO YOU WNAT SOMETHING LIKE THIS:
select Customer_GUID, Transaction_GUID, RateReview_dm from tblTransaction
where RateReview_dm >= dateadd(day, datediff(day, 0, getdate()), -1) -- Greater or same as Yesterday day
and RateReview_dm < dateadd(day, datediff(day, 0, getdate()), 0) -- Less than today's date
Output

FC13C8AD-09DB-44C4-90A4-7F38BEB6618F 88543E7E-27A6-4957-A213-38E1B4A845A9 2007-01-31 22:24:53.727
BUT MY WHOLE QUERY IS NOT BRINGING ME THIS RECORD.SOMETHING IS WRONG WITH MY QUERY PLEASE HELP ME IN DEBUGGING IT. THANKS

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2007-01-31 : 23:59:03

2007-01-31 22:24:53.727 is greater than todays date of 2007-01-31 00:00:00.000, so it is not being selected.

CODO ERGO SUM

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2007-02-01 : 00:12:36

HELLO,
i ran this query and this is the result i got why didn't i get yesterday's email and why today's date:
select Customer_GUID, Transaction_GUID, RateReview_dm from tblTransaction
where RateReview_dm >= dateadd(day, datediff(day, 0, getdate()), -1) -- Greater or same as Yesterday day
and RateReview_dm < dateadd(day, datediff(day, 0, getdate()), 0) -- Less than today's date
Output

FC13C8AD-09DB-44C4-90A4-7F38BEB6618F 88543E7E-27A6-4957-A213-38E1B4A845A9 2007-01-31 22:24:53.727
BUT MY WHOLE QUERY IS NOT BRINGING ME THIS RECORD.SOMETHING IS WRONG WITH MY QUERY PLEASE HELP ME IN DEBUGGING IT. THANKS

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2007-02-01 : 00:38:58

HEY,
Shouldn't my query give me this date which is less than today's date with my query
2007-01-31 22:24:53.727 NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL NULL
NULL 2007-01-26 22:21:41.743
2007-01-30 17:50:00.367 NULL
NULL NULL
NULL NULL
NULL NULL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources