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?