Do i need a Derived table for this situation

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

Do i need a Derived table for this situation

Author

Topic

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2007-02-01 : 09:51:34

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
Table1 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
and
and Terminate_dm is null
(I don;t know what condition to give that same customer good should not be send email again if send once)
i don't know whether i need to create a derive table or it can work without drive table

Cananyone help me with this query please

X002548
Not Just a Number

15586 Posts

Posted - 2007-02-01 : 10:07:09

Please read the link in my sig and post what it asks for...DDL, sample Data, expected results...should get an answer in minutes

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Add yourself!
http://www.frappr.com/sqlteam

wshtrue
Yak Posting Veteran

74 Posts

Posted - 2007-02-01 : 11:34:29

Hello,
I don't know how to paste DDL but i am trying
CREATE TABLE [dbo].[tblTransactionSolicitation] (
[Transaction_GUID] [uniqueidentifier] NOT NULL ,
[Customer_GUID] [uniqueidentifier] NULL ,
[First_nm] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Last_nm] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Email_nm] [varchar] (80) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Film_id] [int] NOT NULL ,
[Title] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ImagePath_tx] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Performance_dm] [datetime] NULL ,
[RateReview_dm] [datetime] NULL ,
[Create_dm] [datetime] NOT NULL ,
[Terminate_dm] [datetime] NULL
) ON [indexes]
GO

Now my expected result is i need to get few field names which i mentioned in the first query and if the customer has already rated it once then that person should not get email or reminder email again and RateReview should be yesterdays date let's say person rated it today that person will get email tomorrow morning at 5AM that would be done by strongmail server.
My main goal right now is that RateReview shoudl display me yesterdays date if it have rated it yesterday otherwise it will always be null.Please help me. Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources