Cancel_dt, order_received_date manipulation

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

Cancel_dt, order_received_date manipulation

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2008-12-10 : 10:09:23

Hi,

I am beginer at SQL, so please excuse the simplicity ofthis question.
I have to exclude all records that took place more than 30 days after the order_received_date. Below is the SQL

SELECT
dbo.v_tbl_orders_base.Order_ID,
dbo.v_tbl_orders_base.Customer_ID,
dbo.v_tbl_orders_base.Order_Received_Date,
convert(datetime,(convert(varchar(10),dbo.v_tbl_orders_base.cancel_Date,101) ),101)cancel_date,
dbo.v_tbl_orders_base.Product_ID,
dbo.v_tbl_mktg.New_Product_Group,
dbo.v_tbl_orders_base.Product_Price,
dbo.v_tbl_orders_base.Partner_data
FROM
dbo.v_tbl_orders_base INNER JOIN dbo.v_tbl_mktg ON (dbo.v_tbl_orders_base.Marketing_ID=dbo.v_tbl_mktg.MID)

WHERE
(
dbo.v_tbl_orders_base.Partner_data LIKE '%creditcom%'
AND
dbo.v_tbl_icdmktg.Parent = 'Y'
AND
dbo.v_tbl_orders_base.Billing_Interval_ID Not In ( 8,7 )
AND
( ( dbo.v_tbl_orders_base.Cancel_Date ) > convert(datetime,convert(varchar(11),dateadd(ww,-1,getdate())- datepart(dw,getdate())+2 )) and ( dbo.v_tbl_orders_base.Cancel_Date ) < convert(datetime,convert(varchar(11),getdate()- datepart(dw,getdate())+2)) ))

If I put in Cancel_dt>order_Order_received_date -30 it still shows me records for eg:

Order_received_date Cancel_dt
2008-10-23 2008-12-05

Database is SQL server 2005

Thanks ,
Petronas

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 10:16:43

try this out


SELECT
dbo.v_tbl_orders_base.Order_ID,
dbo.v_tbl_orders_base.Customer_ID,
dbo.v_tbl_orders_base.Order_Received_Date,
convert(datetime,(convert(varchar(10),dbo.v_tbl_orders_base.cancel_Date,101) ),101)disp_cancel_date,
dbo.v_tbl_orders_base.Product_ID,
dbo.v_tbl_mktg.New_Product_Group,
dbo.v_tbl_orders_base.Product_Price,
dbo.v_tbl_orders_base.Partner_data
FROM
dbo.v_tbl_orders_base INNER JOIN dbo.v_tbl_mktg ON (dbo.v_tbl_orders_base.Marketing_ID=dbo.v_tbl_mktg.MID)

WHERE
(
dbo.v_tbl_orders_base.Partner_data LIKE '%creditcom%'
AND
dbo.v_tbl_icdmktg.Parent = 'Y'
AND
dbo.v_tbl_orders_base.Billing_Interval_ID Not In ( 8,7 )
AND
( ( dbo.v_tbl_orders_base.Cancel_Date ) > convert(datetime,convert(varchar(11),dateadd(ww,-1,getdate())- datepart(dw,getdate())+2 )) and ( dbo.v_tbl_orders_base.Cancel_Date ) < convert(datetime,convert(varchar(11),getdate()- datepart(dw,getdate())+2)) ))
AND dbo.v_tbl_orders_base.cancel_Date>order_Order_received_date -30

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-10 : 10:20:30

quote:

Originally posted by visakh16

try this out


SELECT
dbo.v_tbl_orders_base.Order_ID,
dbo.v_tbl_orders_base.Customer_ID,
dbo.v_tbl_orders_base.Order_Received_Date,
convert(datetime,(convert(varchar(10),dbo.v_tbl_orders_base.cancel_Date,101) ),101)disp_cancel_date,
dbo.v_tbl_orders_base.Product_ID,
dbo.v_tbl_mktg.New_Product_Group,
dbo.v_tbl_orders_base.Product_Price,
dbo.v_tbl_orders_base.Partner_data
FROM
dbo.v_tbl_orders_base INNER JOIN dbo.v_tbl_mktg ON (dbo.v_tbl_orders_base.Marketing_ID=dbo.v_tbl_mktg.MID)

WHERE
(
dbo.v_tbl_orders_base.Partner_data LIKE '%creditcom%'
AND
dbo.v_tbl_icdmktg.Parent = 'Y'
AND
dbo.v_tbl_orders_base.Billing_Interval_ID Not In ( 8,7 )
AND
( ( dbo.v_tbl_orders_base.Cancel_Date ) > convert(datetime,convert(varchar(11),dateadd(ww,-1,getdate())- datepart(dw,getdate())+2 )) and ( dbo.v_tbl_orders_base.Cancel_Date ) < convert(datetime,convert(varchar(11),getdate()- datepart(dw,getdate())+2)) ))
AND dbo.v_tbl_orders_base.cancel_Date>order_Order_received_date -30

He wants to exclude them, try this:

SELECT
dbo.v_tbl_orders_base.Order_ID,
dbo.v_tbl_orders_base.Customer_ID,
dbo.v_tbl_orders_base.Order_Received_Date,
convert(datetime,(convert(varchar(10),dbo.v_tbl_orders_base.cancel_Date,101) ),101)disp_cancel_date,
dbo.v_tbl_orders_base.Product_ID,
dbo.v_tbl_mktg.New_Product_Group,
dbo.v_tbl_orders_base.Product_Price,
dbo.v_tbl_orders_base.Partner_data
FROM
dbo.v_tbl_orders_base INNER JOIN dbo.v_tbl_mktg ON (dbo.v_tbl_orders_base.Marketing_ID=dbo.v_tbl_mktg.MID)

WHERE
(
dbo.v_tbl_orders_base.Partner_data LIKE '%creditcom%'
AND
dbo.v_tbl_icdmktg.Parent = 'Y'
AND
dbo.v_tbl_orders_base.Billing_Interval_ID Not In ( 8,7 )
AND
( ( dbo.v_tbl_orders_base.Cancel_Date ) > convert(datetime,convert(varchar(11),dateadd(ww,-1,getdate())- datepart(dw,getdate())+2 )) and ( dbo.v_tbl_orders_base.Cancel_Date ) < convert(datetime,convert(varchar(11),getdate()- datepart(dw,getdate())+2)) ))
AND dbo.v_tbl_orders_base.cancel_Date<order_Order_received_date -30

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-10 : 10:44:23

exclude all records that took place more than 30 days after the order_received_date
so shouldnt it be Cancel_dt>order_Order_received_date +30

Petronas
Posting Yak Master

134 Posts

Posted - 2008-12-10 : 10:52:33

Thanks for your replies..
I am sorry it did not work.
With Visakh16 query it returns the same results i.e order_received_date+30 and order_received_date-30
with Sakets2000, it is not returning any result set.

Any ideas?
Appreciate your help,
Petronas

sakets_2000
Master Smack Fu Yak Hacker

1472 Posts

Posted - 2008-12-10 : 11:05:18

there was a mistake there, try this :

SELECT
dbo.v_tbl_orders_base.Order_ID,
dbo.v_tbl_orders_base.Customer_ID,
dbo.v_tbl_orders_base.Order_Received_Date,
convert(datetime,(convert(varchar(10),dbo.v_tbl_orders_base.cancel_Date,101) ),101)disp_cancel_date,
dbo.v_tbl_orders_base.Product_ID,
dbo.v_tbl_mktg.New_Product_Group,
dbo.v_tbl_orders_base.Product_Price,
dbo.v_tbl_orders_base.Partner_data
FROM
dbo.v_tbl_orders_base INNER JOIN dbo.v_tbl_mktg ON (dbo.v_tbl_orders_base.Marketing_ID=dbo.v_tbl_mktg.MID)

WHERE
(
dbo.v_tbl_orders_base.Partner_data LIKE '%creditcom%'
AND
dbo.v_tbl_icdmktg.Parent = 'Y'
AND
dbo.v_tbl_orders_base.Billing_Interval_ID Not In ( 8,7 )
AND
( ( dbo.v_tbl_orders_base.Cancel_Date ) > convert(datetime,convert(varchar(11),dateadd(ww,-1,getdate())- datepart(dw,getdate())+2 )) and ( dbo.v_tbl_orders_base.Cancel_Date ) < convert(datetime,convert(varchar(11),getdate()- datepart(dw,getdate())+2)) ))

AND dbo.v_tbl_orders_base.cancel_Date<order_Order_received_date +30

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources