Data from the previous day excluding weekends

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

Data from the previous day excluding weekends

Author

Topic

wsilage
Yak Posting Veteran

82 Posts

Posted - 2014-01-23 : 11:39:14

One of our departments once to automate a query that they have to pull data from the previous day. We are going to set this up as a job. How can we do this without using the Saturday and Sunday dates? So what we want to do is Pull the data from Friday on Monday. Is this possible? This is what they have. I know this pull the data from the day before.

select distinct

clm_id1, clm_rcvd, clm_6a, clm_6b, clm_dout, clm_cc1, clm_clir, clm_65a, clm_5, clm_1a, clm_1a1, clm_1a2, clm_1b, clm_1d, clm_1e, clm_1f, clm_tchg, clm_nego, clm_sppo, clm_att1, clm_att2, clm_att3, clm_att4, clm_att5, clm_chast, clme_fild

from

impact.dbo.clm
left join impact.dbo.clme on clm_id1 = clme_id
where
clm_dout = getdate()-1

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 13:08:40

make it like


where
clm_dout = CASE WHEN 4-DATEDIFF(dd,0,getdate()-1)%7 >0 THEN getdate()-1 ELSE getdate()-1-(4-DATEDIFF(dd,0,getdate()-1)) END

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2014-01-23 : 13:16:18

Alternative:


 where clm_dout=getdate()-case datepart(dw,getdate())
                             when 1 then 2 /* Sunday */
                             when 2 then 3 /* Monday */
                             else        1
                          end

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-23 : 13:18:32

quote:

Originally posted by bitsmed

Alternative:


 where clm_dout=getdate()-case datepart(dw,getdate())
                             when 1 then 2 /* Sunday */
                             when 2 then 3 /* Monday */
                             else        1
                          end

Only issue is this is dependent on DATEFIRST setting amd may vary from server to server

see
http://visakhm.blogspot.in/2012/08/creating-server-independent-day.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

wsilage
Yak Posting Veteran

82 Posts

Posted - 2014-01-23 : 13:52:59

Thanks for you help, but how do I just pull the data from the previous day though? My data is pull yesterdays and todays.

from
impact.dbo.clm
where
clm_doubt= DATEADD(day, DATEDIFF(day, 0, getDate()) - 1, 0)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2014-01-24 : 01:04:57

quote:
Originally posted by wsilage

Thanks for you help, but how do I just pull the data from the previous day though? My data is pull yesterdays and todays.

from
impact.dbo.clm
where
clm_doubt= DATEADD(day, DATEDIFF(day, 0, getDate()) - 1, 0)


where 
      clm_doubt>= DATEADD(day, DATEDIFF(day, 0, getDate()) - 1, 0)
and clm_doubt < DATEADD(day, DATEDIFF(day, 0, getDate()), 0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources