Solved

Query to Run bi-monthly pulling transactions dated 1-14 and 15-EOM

Posted on 2006-07-13
2
296 Views
Last Modified: 2008-02-01
I have a dts package that is currently scheduled to execute every other Tuesday.  I pull in the data from the previous 2 weeks (ending sunday) using the following on my where statement:

(DATEDIFF(week, TranDate, GETDATE()) IN (1, 2))

I have now been requested to change this to run on the 1st and 15th of each month.  

The run on the 1st should pull data from the 15 to the end of the previous month.
The run on the 15th should pull data from the 1st to the 14th of the current month.

Can I use a CASE statement in my selection criteria to pull based upon the run date?  I'd like it to be a little more generic than hardcoding the 1st and 15th.  For instance, if it had to be ran on the 16th, I'd still like it to pull the data for the 1st  - 14th.  

Thanks!
0
Comment
Question by:Angela_Wilcox
2 Comments
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 50 total points
ID: 17103628
what about this:

where TranDate >= case when datepart(day, getdate())<15
  then convert(datetime, convert(varchar(10), dateadd(month,-1,dateadd( day, 15 - datepart(day, getdate()), getdate())) ,120), 120)
  else convert(datetime, convert(varchar(10), dateadd( day, 1 - datepart(day, getdate()), getdate()) ,120), 120)
  end
and TranDate < case when datepart(day, getdate())<15
  then convert(datetime, convert(varchar(10), dateadd( day, 1 - datepart(day, getdate()), getdate()) ,120), 120)
  else convert(datetime, convert(varchar(10), dateadd( day, 15 - datepart(day, getdate()), getdate()) ,120), 120)
 end
                 
0
 

Author Comment

by:Angela_Wilcox
ID: 17107492
Thank you!  Looks like it should work!!
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question