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
 SQL Server 2008 Forums
 Transact-SQL (2008)
 If then this and that
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

latture
Starting Member

24 Posts

Posted - 02/22/2013 :  10:28:47  Show Profile  Reply with Quote
I'm trying to figure this out but I'm having really hard time with it. So here is my statement in a procedure.

IF @ReportDate = convert(date,GETDATE()) -- if today's date is sent from job agent, go back 2 day. Otherwise let user key date.
set @ReportDate = convert(date,GETDATE()-2)
IF DATEPART(dd,@reportDate)=26
set @reportdate = DATEADD(day,-day(@ReportDate)+25,@ReportDate)

--set @ReportDate = '12/27/2010'

If (DATEPART(Day, @ReportDate)) >= 26 -- if date is after 25 keep same month
begin
Set @BegMTD = @ReportDate
Set @BegMTD = DATEADD(day,-day(@BegMTD)+26,@BegMTD)
end
Else
Begin
Set @BegMTD = DATEADD(month,-1,@ReportDate)
Set @BegMTD = DATEADD(day,-day(@BegMTD)+26,@BegMTD)

-- If report is run for 12/26/yyyy and it is run after 1/1/yyyy change beginning date to 1/1/yyyy.

If DATEPART(MONTH,@BegMTD) = 12 and DATEPART(MONTH,GETDATE()) = 1
begin
Set @BegMTD = @ReportDate
Set @BegMTD = DATEADD(day,-day(@BegMTD)+01,@BegMTD)
end
End

This all works fine except for one problem. Because we're looking 2 days back except on the 26th which looks at one day back to the 25th, along the way 24th is getting skipped. I need a way to account for 24th too. Both 24th and 25th needs to run. Can someone help me? It seems like it should be easy but I'm having a hard time since I'm just a rookie... :P

Thanks.

Edited by - latture on 02/22/2013 10:42:36

James K
Flowing Fount of Yak Knowledge

3588 Posts

Posted - 02/22/2013 :  11:56:46  Show Profile  Reply with Quote
I couldn't quite follow the logic you are trying to implement. Can you describe the logic, for example in a tabular form like shown below? In my table below, @ReportDate is the input and @BegMTDDate is the desired result you are trying to calculate.
@ReportDate @BegMTDDate
       1        1
       2        2
       ..       ..
       24       24
       25       25
       26       25
       27       25
       ..       ...
       31       25

Edited by - James K on 02/22/2013 11:57:07
Go to Top of Page

latture
Starting Member

24 Posts

Posted - 02/22/2013 :  12:25:08  Show Profile  Reply with Quote
Logic as in what I'm trying to do with this stetement? Basically rest of the procedure underneath the @ReportDate statement are updates to a table. So throughout the month everyday the table gets updated from fields pulled from other tables with dates from 2 days ago. On the 1st it'll pull the 29th or 30th records depending on the month. On the 3rd it'll pull the 1st records. 25th is our month cut off and 26th is our beginning of new month period. This procedure accumulates all the data from previous months period and on the 26th of each month we will pull or extract the data for previous month's reporting purposes.
So this statment does what it supposed to. We're looking back 2 days instead of 1 because sometimes we have issues with users, servers, or whatever that delays the data from coming into our server to be pulled and updated into the reporting table. The only problem is that since we have to start working on our reporting for the previous month on the 26th, we can't wait until the 27th to pull the 25th report. That's why IF DATEPART(dd,@reportDate)=26
set @reportdate = DATEADD(day,-day(@ReportDate)+25,@ReportDate) was added so that 25th will be covered on the 26th. Here lies my problem. If data will extracted for the -2 days from getdate() and on the 26th the 25th data is extracted then I'm missing the 24th completely. So I was hoping I can somehow include 24th and the 25th to be run on the 26th by adjusting the part of the statement IF DATEPART(dd,@reportDate)=26
set @reportdate = DATEADD(day,-day(@ReportDate)+25,@ReportDate). Does this make sense??? Sorry, sometimes I feel like I'm rambling.
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3588 Posts

Posted - 02/22/2013 :  13:12:35  Show Profile  Reply with Quote
My cognitive skills in digesting written sentences are not as good as I would like it to be, so some others on the forum who are better at it may be able understand your description and respond with a suggestion on the required changes.

I was coming at it purely from a data perspective. Reading through your code, it seemed to me that the user provides one parameter @ReportDate. Using that, the code calculates the value of another parameter @BegMTD. So I took that to mean that for a given value of @ReportDate we should be able to calculate an unambiguous value of @BegMTD. What I was showing in the table that I posted earlier was that relation - given value of @ReportDate and expected value of @BegMTD.

Edited by - James K on 02/22/2013 13:12:49
Go to Top of Page

latture
Starting Member

24 Posts

Posted - 02/22/2013 :  17:00:20  Show Profile  Reply with Quote
Actually @BegMTD establishes the first reporting date of the month. So it should always show the 26th of the previous month of GETDATE().

I actually ended up doing a while loop. So I added this IF DATEPART(dd, @ReportDate) = 26 set @EndFlag = 2 else set @EndFlag = 1 before the first IF DATEPART from above and set WHILE (@EndFlag <=2) then at the end of the procedure I made it so that if @EndFlag = 1 then break but if @EndFlag = 2 then set @reportdate = DATEADD(day,-day(@ReportDate)+24,@ReportDate). This gave me the results that I wanted. :)
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