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)
 If then this and that

Author  Topic 

latture
Starting Member

24 Posts

Posted - 2013-02-22 : 10:28:47
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.

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-22 : 11:56:46
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
Go to Top of Page

latture
Starting Member

24 Posts

Posted - 2013-02-22 : 12:25:08
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-02-22 : 13:12:35
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.
Go to Top of Page

latture
Starting Member

24 Posts

Posted - 2013-02-22 : 17:00:20
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
   

- Advertisement -