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 2008 Forums

Transact-SQL (2008)

Query help

Author

Topic

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-03 : 16:45:49

I have a job which runs daily after loading then i will update the status,udate in the below tableB based on the DATEPART(dw, GETDATE()) function which gives the value of week.
But i have a new requirement which have 8 days.

How should i identify the 8 dayno and update the 8 row since the function DATEPART(dw, GETDATE()) will give the result for 7 days.

I need a query for this.

TableB
__________

Dayno Udate status
------ ----------------------- -------
1 2011-10-29 16:01:28.010 Y
2 2011-10-30 16:01:28.010 Y
3 2011-10-31 16:01:28.010 Y
4 2011-11-01 16:01:28.010 Y
5 2011-10-02 16:01:28.010 Y
6 2011-10-03 16:01:28.010 Y
7 2011-10-04 16:01:28.010 Y
8 2011-10-05 16:01:28.010 Y

Let me know if i m not clear.

Thanks for your help in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-04 : 04:27:43

sorry how would you've 8th day for week? or do you want to consider week itself as 8 days?

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

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-04 : 13:58:20

There won't be 8 days in a week.
I have a requirement which i need to keep update status for 8 days.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-05 : 13:23:51

quote:
Originally posted by sqlfresher2k7

There won't be 8 days in a week.
I have a requirement which i need to keep update status for 8 days.

which 8days? 8 days spanning current date to previous 7?

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

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-05 : 15:05:47

Yes..currentdate to previous 8 days

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-06 : 02:42:03

you need to just use WHERE clause like below to get last eight days


WHERE Udate >= DATEADD(dd,DATEDIFF(dd,0,GETDATE()),-7)
AND Udate < DATEADD(dd,DATEDIFF(dd,0,GETDATE()),1)

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

sqlfresher2k7
Aged Yak Warrior

623 Posts

Posted - 2011-11-06 : 12:54:49

Thanks a lot Visakh !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-11-07 : 04:27:30

wc

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources