March 9, 2020 at 5:40 pm
Can anyone give me a step by step defition of what this code is doing and a break down? I'm doing a report on this and I'm new to sql
declare @Filename varchar(50),
@year varchar(4),
@month varchar(2),
@day varchar(2),
@date varchar(10),
@msg varchar(1000)
set @year = case
when month(getdate()) = 1 and day(getdate()) = 1
then cast(year(getdate())-1 as varchar(4))
else cast(year(getdate()) as varchar(4)) end
set @month = case
when eomonth(getdate()) = CONVERT(char(10), GetDate(),126)
then month(DATEADD(month,-1,getdate()))
else month(getdate()) end
set @month = case
when len(@month) < 2
then '0' + @month
else @month end
set @day = day(DATEADD(day,-1,getdate()))
set @day = case
when len(@day) < 2
then '0' + @day
else @day end
set @date = @year + @month + @day
set @Filename = 'WESA_New_DONOR_' + @date + '.csv'
set @Filename = ltrim(@Filename)
set @msg =
EXEC msdb.dbo.sp_send_dbmail @recipients='Exampleemail',
@subject = 'Example',
@body = @msg,
@body_format = 'HTML';
March 9, 2020 at 7:11 pm
Looks like a lot of work to build an 8-digit string that means 'yesterday', one date part at a time.
First, it determines what year was yesterday.
The part that sets the month looks like a bug... if today is the last day of the month, then use last month. Otherwise, use this month. I think it meant to check if today is the first day of the month. I'm not sure.
Then it sets the day value to yesterday's day.
Then it slaps all those together to form a suffix for a filename, likely a daily report of yesterday's activity.
If the month calculation is in fact a bug, you can dump all that mess you posted for just this:
DECLARE @Filename varchar(50), @year varchar(4), @month varchar(2),
@day varchar(2), @date varchar(10), @msg varchar(1000);
SELECT @Filename = 'WESA_New_DONOR_' + CONVERT(char(8), convert(date, getdate() - 1), 112) + '.csv';
SELECT @msg = EXEC msdb.dbo.sp_send_dbmail @recipients='Exampleemail', @subject = 'Example', @body = @msg, @body_format = 'HTML';
Here's your original post with comments:
-- Note: this motnh-calculating logic makes no sense to me... seems to return 20200230 (Feb. 30) when run on March 31
DECLARE @Filename varchar(50), @year varchar(4), @month varchar(2),
@day varchar(2), @date varchar(10), @msg varchar(1000);
/* if it's January 1st, set the @year variable to last year
Otherwise, set it to this year. */
set @year = case when month(getdate()) = 1 and day(getdate()) = 1
then cast(year(getdate())-1 as varchar(4))
else cast(year(getdate()) as varchar(4))
end
/* if today is the last day of the month, set @month to last month. Otherwise, use this month.
CONVERT(char(10), GetDate(),126) returns today formatted as yyyy-mm-ddThh:mi:ss.mmm. Using CONVERT(char(10))
grabs only the first 10 characters of that string (yyyy-mm-dd), stripping the time component off. */
set @month = case when eomonth(getdate()) = CONVERT(char(10), GetDate(),126)
then month(DATEADD(month,-1,getdate()))
else month(getdate())
end
/* if the month number is a single digit, then zero-pad it to two characters */
set @month = case when len(@month) < 2
then '0' + @month
else @month
end
/* set the 'day' value to yesterday by adding (-1) days to today */
set @day = day(DATEADD(day,-1,getdate()))
/* if the day number is a single digit, then zero-pad it to two characters */
set @day = case when len(@day) < 2
then '0' + @day
else @day
end
/* build a YYYYMMDD string out of what was calculated */
set @date = @year + @month + @day
/* use the YYYYMMDD string to create a file name */
set @Filename = 'WESA_New_DONOR_' + @date + '.csv'
/* Email the file */
set @Filename = ltrim(@Filename)
set @msg = EXEC msdb.dbo.sp_send_dbmail @recipients='Exampleemail', @subject = 'Example', @body = @msg, @body_format = 'HTML';
Eddie Wuerch
MCM: SQL
March 9, 2020 at 7:20 pm
It seems fairly straightforward, what part are you having problems with? If you're not sure what some of the datemath is doing, try some of it outside of this script on its own to see what it does:
SELECT month(getdate()), day(getdate()), cast(year(getdate())-1 as varchar(4)), cast(year(getdate()) as varchar(4));
SELECT eomonth(getdate()), CONVERT(char(10), GetDate(),126), month(DATEADD(month,-1,getdate())), month(getdate())
I think there might be a logic bug in there if this is trying to do what I think it's trying to do, but then you would probably know better than us what it is trying to do.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply