Query Help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query Help

Author

Topic

Jay87
Starting Member

41 Posts

Posted - 2010-02-05 : 07:27:27

I use the following code to show all the logs that have status of 20 from the current month:

SELECT * from faultlog where closed>DATENAME(YEAR,GETDATE()) + '-' + cast(DATEPART(MONTH,GETDATE()) as nvarchar(12)) + '-01'   AND status=20

Can someone please help me alter the code so i can show all the logs that have a status of 20 from the start of last month up until the end of the last month

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-05 : 07:32:30

SELECT * from faultlog
where
closed>=dateadd(month,datediff(month,0,getdate())-1,0) AND
closed<dateadd(month,datediff(month,0,getdate()),0) AND
AND status=20

Madhivanan

Failing to plan is Planning to fail

Jay87
Starting Member

41 Posts

Posted - 2010-02-05 : 07:47:01

Legend mate, worked a treat!

Do you know how to alter the query to show all the status 20 logs from this year (2010)

also how i can show all the status 20 logs from the last year

I am making a statistics screen and these lil queries are frustrating me

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-05 : 07:50:32

--All data of Last year

SELECT * from faultlog
where
closed>=dateadd(year,datediff(year,0,getdate())-1,0) AND
closed<dateadd(year,datediff(year,0,getdate()),0) AND
AND status=20

--All data of this year

SELECT * from faultlog
where
closed>=dateadd(year,datediff(year,0,getdate()),0) AND
closed<dateadd(year,datediff(year,0,getdate())+1,0) AND
AND status=20

Madhivanan

Failing to plan is Planning to fail

Jay87
Starting Member

41 Posts

Posted - 2010-02-05 : 08:04:18

Worked a treat.

Sorry to be a pain mate, but do you know how i can get it so it shows this week logs i.e. from monday to sunday?

and also todays logs i.e. friday instead of going back 24 hours and running into thursday...

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-05 : 08:08:56

--Monday to Sunday

SELECT * from faultlog
where
closed>=dateadd(week,datediff(week,0,getdate())-1,0) AND
closed<dateadd(week,datediff(week,0,getdate()),0) AND
AND status=20

--Today's log

SELECT * from faultlog
where
closed>=dateadd(day,datediff(day,0,getdate())-1,0) AND
closed<dateadd(day,datediff(day,0,getdate())+1,0) AND
AND status=20

Madhivanan

Failing to plan is Planning to fail

Jay87
Starting Member

41 Posts

Posted - 2010-02-05 : 08:49:07

quote:
Originally posted by madhivanan

--Monday to Sunday

SELECT * from faultlog
where
closed>=dateadd(week,datediff(week,0,getdate())-1,0) AND
closed<dateadd(week,datediff(week,0,getdate()),0) AND
AND status=20

This statement shows last weeks logs (25th- 31st), i want it to show this weeks (starting from 1st ending 6th), is that possible mate?

Jay87
Starting Member

41 Posts

Posted - 2010-02-05 : 08:52:19

quote:
Originally posted by madhivanan

--Today's log

SELECT * from faultlog
where
closed>=dateadd(day,datediff(day,0,getdate())-1,0) AND
closed<dateadd(day,datediff(day,0,getdate())+1,0) AND
AND status=20

Again this shows the last 24 hours i.e. some of yesterdays logs, is it possible to restrict it to just today?

thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-05 : 10:00:16

quote:
Originally posted by Jay87

quote:
Originally posted by madhivanan

--Today's log

SELECT * from faultlog
where
closed>=dateadd(day,datediff(day,0,getdate())-1,0) AND
closed<dateadd(day,datediff(day,0,getdate())+1,0) AND
AND status=20

Again this shows the last 24 hours i.e. some of yesterdays logs, is it possible to restrict it to just today?

thanks

SELECT * from faultlog
where
closed>=dateadd(day,datediff(day,0,getdate()),0) AND
closed<dateadd(day,datediff(day,0,getdate())+1,0) AND
AND status=20

Madhivanan

Failing to plan is Planning to fail

Jay87
Starting Member

41 Posts

Posted - 2010-02-05 : 10:22:51

quote:
Originally posted by Jay87

quote:
Originally posted by madhivanan

--Monday to Sunday

SELECT * from faultlog
where
closed>=dateadd(week,datediff(week,0,getdate())-1,0) AND
closed<dateadd(week,datediff(week,0,getdate()),0) AND
AND status=20

This statement shows last weeks logs (25th- 31st), i want it to show this weeks (starting from 1st ending 6th), is that possible mate?

Can you have a look at this if you can mate?

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-02-05 : 10:26:45

quote:
Originally posted by Jay87

quote:
Originally posted by Jay87

quote:
Originally posted by madhivanan

--Monday to Sunday

SELECT * from faultlog
where
closed>=dateadd(week,datediff(week,0,getdate())-1,0) AND
closed<dateadd(week,datediff(week,0,getdate()),0) AND
AND status=20

This statement shows last weeks logs (25th- 31st), i want it to show this weeks (starting from 1st ending 6th), is that possible mate?

Can you have a look at this if you can mate?

Try

SELECT * from faultlog
where
closed>=dateadd(week,datediff(week,0,getdate()),0) AND
closed<dateadd(week,datediff(week,0,getdate())+1,0) AND
AND status=20

Madhivanan

Failing to plan is Planning to fail

Jay87
Starting Member

41 Posts

Posted - 2010-02-05 : 10:41:58

legend, worked a treat!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources