case statement - SQL Server Forums

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

case statement

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-06 : 09:40:39

This is the final question of the date

Aim – Case statement which looks at the current date today and calculates the number of days between ”todays date” and ‘[Last_Post_Date]’and the results to be populated in a column called “Indicator”

Example

Todays date = 06082013
Last_Post_Date] = 01082013
Indicator = 6 days

Exmaple 2
Todays date = 30062013
Last_Post_Date] = 01062013
Indicator = 30 days

I did try doing the following
CASE WHEN [Last_Post_Date] AND (CURDATE( ) BETWEEN 0 AND 7) THEN '1week' ELSE 'mason' END AS indicator
But to no avail

Final results table
--Final results --
select
[FDMSAccountNo],
[ParentID],
[DBA_Name]
,[DBA_Addr1]
,[DBA_Addr2]
,[DBA_Addr4]
,[DBA_City]
,[DBA_County]
,[Post_Code]
,[Last_Post_Date]
,[Account_Status]
from #account
where Account_Status not in ('02','03','13')
and [IsOutlet] = '1'
and LBG_Status <> 'accepted'
order by Account_Status desc

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2013-08-06 : 10:12:57

You're using curdate() which indicates you might be using mysql. This forum is for mssql so you will probably get better answers in an mysql forum.

To calculate days between two dates, you can use the function datediff. Looking at the two examples, given results are off by one, so to correct for this, you must add one to the return value of datediff function.

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2013-08-06 : 10:30:02

Hi bitsmed

I went this way around it

--Gathering Results --
select
[FDMSAccountNo],
[ParentID],
[DBA_Name]
,[DBA_Addr1]
,[DBA_Addr2]
,[DBA_Addr4]
,[DBA_City]
,[DBA_County]
,[Post_Code]
,[Last_Post_Date]
,[Account_Status]
,cast((getdate()-1-[Last_Post_Date]) as int) as [Days from last Post]
into #final
from #account
where Account_Status not in ('02','03','13')
and [IsOutlet] = '1'
and LBG_Status <> 'accepted'
order by Account_Status desc

--Final Results --
select
[FDMSAccountNo],
[ParentID],
[DBA_Name]
,[DBA_Addr1]
,[DBA_Addr2]
,[DBA_Addr4]
,[DBA_City]
,[DBA_County]
,[Post_Code]
,[Last_Post_Date]
,[Account_Status]
,[Days from last Post],
case when
[Days from last Post] between 0 and 7 then '1week'
when [Days from last Post] between 7 and 14 then '1-2weeks'
when [Days from last Post] between 15 and 30 then '2-4weeks'
when [Days from last Post] > 30 then '4weeks++'
else 'No Sales' end as [Indicator]
from #final

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-08-07 : 01:06:06

quote:
Originally posted by masond

Hi bitsmed

I went this way around it

--Gathering Results --
select
[FDMSAccountNo],
[ParentID],
[DBA_Name]
,[DBA_Addr1]
,[DBA_Addr2]
,[DBA_Addr4]
,[DBA_City]
,[DBA_County]
,[Post_Code]
,[Last_Post_Date]
,[Account_Status]
,cast((getdate()-1-[Last_Post_Date]) as int) as [Days from last Post]
into #final
from #account
where Account_Status not in ('02','03','13')
and [IsOutlet] = '1'
and LBG_Status <> 'accepted'
order by Account_Status desc

You can use DATEDIFF() function to have the same functionality of the above highlighted code
DATEDIFF ( DD, [Last_Post_Date], GETDATE())-1

--
Chandu

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources