Query for cancels within trial period

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

Query for cancels within trial period

Author

Topic

Petronas
Posting Yak Master

134 Posts

Posted - 2009-01-22 : 13:39:09

Hi,
I have to get count of all orders cancelled within the trial period . Each product has a trial day limit .
This trial period in stored in the Orders table as Trial_days(smallint,not null).
I have the following query:

SELECT sum(case when cancel_date) is not null then 1 else 0 end) Cancel_count,
convert(datetime(convert(varchar(10), order_date,101)),101)Order_date,
convert(datetime(convert(varchar(10), cancel_date,101)),101) Cancel_date,
trial_days
FROM orders
WHERE
datediff(mm,order_date,getdate()) =2 -- Month prior to last month
AND
trial_days= datediff(dd,order_date,cancel_date)---Not correct
GROUP BY
convert(datetime(convert(varchar(10), order_date,101)),101)Order_date,
convert(datetime(convert(varchar(10), cancel_date,101)),101) Cancel_date,
trial_days

How do I get the cancellations within the trial period?

Thanks for you help,
Petronas

revdnrdy
Posting Yak Master

220 Posts

Posted - 2009-01-22 : 14:10:37

Can you be more specific?

Does each products have a unique expiration time for trial date or do they all share a common time (ie.. 60 days for each trial)?

r&r

Petronas
Posting Yak Master

134 Posts

Posted - 2009-01-22 : 14:30:07

Hi,

They have a common trial period.. 30 days.

Thanks,
Petronas

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources