SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Data from the previous day excluding weekends
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

wsilage
Yak Posting Veteran

USA
52 Posts

Posted - 01/23/2014 :  11:39:14  Show Profile  Reply with Quote
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

India
52309 Posts

Posted - 01/23/2014 :  13:08:40  Show Profile  Reply with Quote
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
Go to Top of Page

bitsmed
Constraint Violating Yak Guru

319 Posts

Posted - 01/23/2014 :  13:16:18  Show Profile  Reply with Quote
Alternative:

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

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/23/2014 :  13:18:32  Show Profile  Reply with Quote
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
Go to Top of Page

wsilage
Yak Posting Veteran

USA
52 Posts

Posted - 01/23/2014 :  13:52:59  Show Profile  Reply with Quote
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)
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 01/24/2014 :  01:04:57  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000