Previous month when in January

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

Previous month when in January

Author

Topic

Johnny Gunn
Starting Member

5 Posts

Posted - 2012-01-09 : 04:46:07

Hi,

I have a select counting the number of contracts per sales person for previous month. It goes like this:

select count(*) from contract c, sale s
where 1=1
and c.salesperson = s.salesperson
and to_char(c.fromdate,'MM') = to_char(sysdate,'MM') -1
and to_char(c.fromdate,'YY') = to_char(sysdate,'YY')
group by p.salesperson

This one is of course not working in January. Any help on this?

regards JG

regards Johnny Gunn

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-01-09 : 04:50:09

where datediff(mm,c.fromdate,sysdate) = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-09 : 04:56:06

is it oracle code? i dont think to_char is in sql server

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

GilaMonster
Master Smack Fu Yak Hacker

4507 Posts

Posted - 2012-01-09 : 04:57:20

That's either Oracle or PostGreSQL, Not MS SQL Server.

--
Gail Shaw
SQL Server MVP

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources