Having Friday afternoon brain fahrts - DATEADD - DATEDIFF

1

I have code I worked out to get the previous workday as a basis for an account balance. The problem I have is using the global variable to try and retrieve the workday before that to check the 'Previous Balance':
[DECLARE @ReportingDate DATETIME
DECLARE @PreviousDate DATETIME
SET @ReportingDate = GETDATE()
SET @ReportingDate =
CASE DATEDIFF(DAY, 0, DATEADD(DAY, DATEDIFF(DAY, 0, @ReportingDate), -1))%7
WHEN 5 THEN
DATEADD(DAY, -1, DATEADD(DAY,DATEDIFF(DAY, 0, @ReportingDate), -1)) -- if Saturday subtract one day
WHEN 6 THEN
DATEADD(DAY, -2, DATEADD(DAY,DATEDIFF(DAY, 0, @ReportingDate), -1)) -- if Sunday subtract two days
ELSE
DATEADD(DAY, DATEDIFF(DAY, 0, @ReportingDate), -1)
END
SET @PreviousDate =
CASE
DATEDIFF(DAY, 0, DATEADD(DAY, DATEDIFF(DAY, 0, @ReportingDate), -1))%7
WHEN 5 THEN
DATEADD(DAY, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @PreviousDate), -1)) -- if Saturday subtract one day
WHEN 6 THEN
DATEADD(DAY, -2, DATEADD(DAY,DATEDIFF(DAY, 0, @PreviousDate), -1)) -- if Sunday subtract two days
ELSE
DATEADD(DAY, DATEDIFF(DAY, 0, @PreviousDate), -1)
END

-- ##### TEST BLOCK ##########
Print @ReportingDate
Print '-----------'
Print @PreviousDate
-- #####END OF TEST BLOCK ####]

Please excuse the debugging with 'Print'. I work remotely and it is all I have permissions to do other than 'SELECT'. @ReportingDate works well, but I am having problems basing @PreviousDate on the variable @ReportingDate. I get nothing returned for @PreviousDate and figure it is due to the variable. Ideas? Suggestions?

2

Where is @previousdate initially set?

3

[DECLARE @ReportingDate DATETIME
DECLARE @PreviousDate DATETIME
SET @ReportingDate = GETDATE()
SET @ReportingDate =
CASE DATEDIFF(DAY, 0, DATEADD(DAY, DATEDIFF(DAY, 0, @ReportingDate), -1))%7
WHEN 5 THEN
DATEADD(DAY, -1, DATEADD(DAY,DATEDIFF(DAY, 0, @ReportingDate), -1)) -- if Saturday subtract one day
WHEN 6 THEN
DATEADD(DAY, -2, DATEADD(DAY,DATEDIFF(DAY, 0, @ReportingDate), -1)) -- if Sunday subtract two days
ELSE
DATEADD(DAY, DATEDIFF(DAY, 0, @ReportingDate), -1)
END
SET @PreviousDate =
CASE
DATEDIFF(DAY, 0, DATEADD(DAY, DATEDIFF(DAY, 0, @ReportingDate), -1))%7
WHEN 5 THEN
DATEADD(DAY, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @PreviousDate), -1)) -- if Saturday subtract one day
WHEN 6 THEN
DATEADD(DAY, -2, DATEADD(DAY,DATEDIFF(DAY, 0, @PreviousDate), -1)) -- if Sunday subtract two days
ELSE
DATEADD(DAY, DATEDIFF(DAY, 0, @PreviousDate), -1)
END

-- ##### TEST BLOCK ##########
Print @ReportingDate
Print '-----------'
Print @PreviousDate
-- #####END OF TEST BLOCK ####]

4

I think I understand. I am using it before it is initialized. Is that what you were implying?

5

Shouldn't @PreviousDate be set based on @ReportingDate, rather than itself (which, as you have said, has not had a value assigned at that point)

Would DATEPART(WEEKDAY, @ReportingDate) help? (although maybe no different to MODULUS 7 that you are using)

6

What results are you trying to get?

Is @ReportingDate YESTERDAY - adjusted to be a working day.
and
Is @PreviousDate the day BEFORE @ReportingDate - again, adjusted to be a working day

?

7

Kristen:

I have yet to be able to log in and see the code today (server being worked on), so this is by memory:
The report is a daily report that returns the balance for the previous workday - simple enough. The report has a field that is asking for the previous workday's balance. To get the result for this, I need to calculate two dates, not one.
Today is Monday and the previous workday would be Friday. The previous workday's balance would be on Thursday's numbers. A bit tougher to wrap your mind around, but still fairly simple.
As djj55 pointed out, I had forgotten to initialize [quote="JimSnyder, post:3, topic:6003"]
@PreviousDate
[/quote]
before assigning a value to it. I think I made the change and was working on other parts last Friday, but was having trouble thinking straight as a cluster headache was stealing my ability to concentrate. djj55 gave me just enough of a pointer to penetrate the fog and fix it.
The thing I cannot do is answer your question of whether @PreviousDate was set based on @PreviousDate or @ReportingDate until I can access my code again.

8

Finally got access back. Here is the working code:
` DECLARE @ReportingDate DATETIME
DECLARE @PreviousDate DATETIME
SET @ReportingDate = GETDATE()
SET @PreviousDate = DATEADD(DAY, DATEDIFF(DAY, 0, @ReportingDate), -1)

SET @ReportingDate = 
	CASE DATEDIFF(DAY, 0, DATEADD(DAY, DATEDIFF(DAY, 0, @ReportingDate), -1))%7
		WHEN 5 THEN 
			DATEADD(DAY, -1, DATEADD(DAY,DATEDIFF(DAY, 0, @ReportingDate), -1)) -- if Saturday subtract one day
		WHEN 6 THEN 
			DATEADD(DAY, -2, DATEADD(DAY,DATEDIFF(DAY, 0, @ReportingDate), -1)) -- if Sunday subtract two days
		ELSE 
			DATEADD(DAY, DATEDIFF(DAY, 0, @ReportingDate), -1) 
	END
	
SET @PreviousDate =
	CASE
		DATEDIFF(DAY, 0, DATEADD(DAY, DATEDIFF(DAY, 0, @ReportingDate), -1))%7
		WHEN 5 THEN
			DATEADD(DAY, -1, DATEADD(DAY, DATEDIFF(DAY, 0, @PreviousDate), -1)) -- if Saturday subtract one day
		WHEN 6 THEN 
			DATEADD(DAY, -2, DATEADD(DAY,DATEDIFF(DAY, 0, @PreviousDate), -1))	-- if Sunday subtract two days
		ELSE 
			DATEADD(DAY, DATEDIFF(DAY, 0, @PreviousDate), -1) 
	END

-- ##### TEST BLOCK ##########
Print @ReportingDate
Print '-----------'
Print @PreviousDate
-- #####END OF TEST BLOCK ####

Results:
Apr 19 2016 12:00AM

Apr 18 2016 12:00AM

`