September 22, 2022 at 9:25 pm
I am not sure I understand your question - I think you are asking if you wanted to get the data for the day prior to the last day of the month? Or any number of days prior?
If so - then you start from the end of the month and subtract that many days. To make the code clear as to your intentions - use the appropriate functions. Although it is perfectly fine subtracting days from a datetime data type - it doesn't work for the date/datetime2/datetimeoffset data types.
DECLARE @last_day int = DATEADD(DAY, -1, EOMONTH(getdate(), -1)) -- One day prior to end of previous month
So here - we get the end of the month using EOMONTH with the second parameter. The second parameter allows us to add/subtract the number of months from the first parameter. We then wrap that with a DATEADD to add the appropriate number of days before/after the value returned from the EOMONTH function.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 22, 2022 at 11:56 pm
Sorry Jeffrey, I was not very clear there.
What I am looking it is for example today is the 22nd and lets say the boss wants to go up to the 21st.
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 23, 2022 at 1:27 pm
Hi Jeffrey,
I think this is what I need to add/change
Declare @last_day datetime = DATEADD(day, -1, convert(date, GETDATE()))
Is this correct?
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 23, 2022 at 2:44 pm
Jeffrey, here is the complete script. This appears to be working;
Note I added an additional Declare for @prev_date. Then changed [End of Month] to [Yesterday] in the select statement.
Does that all look correct to you?
Thanks,
Use tmdsDatabaseStatistics;
Declare @current_date datetime = getdate(); -- Run for the current date
Declare @prev_date datetime= DATEADD(DAY, -1, @current_date); -- Run for the current date
Declare @last_day int = day(eomonth(@current_date)) -- Last day of the month for current date
, @current_month int = month(@current_date)
, @current_year int = year(@current_date);
Select [CP Name] = ControlPointName
, Codeline
, Type = cl.Legacytype
, [Sub Division] = SubName
, [Control Failures] = sum(ControlFailCount)
, [Control Point Down] = sum(ControlPointStatusDownCount)
, Failures = FailureCount
, [First of Month] = sum(Case When DAY = 1 Then FailureCount Else 0 End)
, [Middle of Month] = sum(Case When DAY = 15 Then FailureCount Else 0 End)
, [Yesterday] = sum(Case When DAY = @last_day Then FailureCount Else 0 END)
From tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay sld WITH (NOLOCK)
Inner Join tmdsDatabaseStatic.dbo.tblCodeLines cl On cl.CodelineNumber = sld.codeline
Where ControlPointName NOT LIKE 'W[DM]%'
And DAY In (1, 15, @prev_date)
And month = @current_month
And year = @current_year
And FailureCount > '500'
Group By
ControlPointName
, codeline
, cl.LegacyType
, SubName
, FailureCount
Order By
Failures desc;
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 23, 2022 at 4:00 pm
There are several ways to approach this - one way is to have separate scripts and you just pick the one you want. So one will be current month to date and the other would be previous month. The same code except you modify the calculation for current date and @last_day.
Another option is to use a flag - and based on that flag you set @current_date and @last_day to the appropriate values.
For example, here I am calculating month to date based on current day. If the current day is greater than 16 then report month to date - prior to the 15th report previous month. Now, if you want to be able to report month to date prior to the 15th - then additional changes *might* be needed because the 15th is hard-coded.
Use tmdsDatabaseStatistics;
--==== Use MTD after the 16th of the month up to the last day of the month
Declare @previous_date date = iif(day(getdate() > 16, dateadd(day, -1, getdate()), eomonth(getdate(), -1));
--==== Get report parameters
Declare @last_day int = day(@previous_date)
, @report_month int = month(@previous_date)
, @report_year int = year(@previous_date);
Select [CP Name] = ControlPointName
, Codeline
, Type = cl.Legacytype
, [Sub Division] = SubName
, [Control Failures] = sum(ControlFailCount)
, [Control Point Down] = sum(ControlPointStatusDownCount)
, [Report Month] = sld.[Month]
, [Report Year] = sld.[Year]
, Failures = FailureCount
, [First of Month] = sum(Case When DAY = 1 Then FailureCount Else 0 End)
, [Middle of Month] = sum(Case When DAY = 15 Then FailureCount Else 0 End)
, [End of Month] = sum(Case When DAY = @last_day Then FailureCount Else 0 END)
From tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay sld
Inner Join tmdsDatabaseStatic.dbo.tblCodeLines cl On cl.CodelineNumber = sld.codeline
Where ControlPointName NOT LIKE 'W[DM]%'
And DAY In (1, 15, @last_day)
And month = @report_month
And year = @report_year
And FailureCount > '500'
Group By
ControlPointName
, codeline
, cl.LegacyType
, SubName
, [Month]
, [Year]
Order By
Failures desc;
And I highly recommend that you update the columns in this query to use the table alias everywhere. For example - is ControlPointName coming from table tblStatisticsLocationDay or for tblCodeLines? Guessing it comes from the former - so it should be sld.ControlPointName everywhere in the query.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 23, 2022 at 4:30 pm
Thanks Jeffrey, that does help a lot. I will modify the alias piece, just have not gotten to it yet. Function before form... Yes sld.ControlPointName would be correct.
I really appreciate the explanations with the code example. they are very helpful.
I think that about covers all the things on my hit list. now jut clean it up and put into a stored procedure and call it a day.
Have a great weekend all of you!
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 23, 2022 at 5:03 pm
Thank you for the feedback - if converting to a stored procedure then you want to determine what parameter(s) to pass to the procedure. It would depend on the usage though and how the procedure will be used - and my not need any parameters at all.
You could pass into the procedure the current date - make it optional (null), and if null use the calculation in the code. Or - you could calculate the last day from the calling code/report and have @last_day as the parameter. Many options - but really depends on how it will be called.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 23, 2022 at 5:05 pm
Great suggestions! Much appreciated.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 29, 2022 at 12:31 pm
Hi Jeffrey! I have been battling with the
Declare @previous_date date = iif(day(getdate() > 16, dateadd(day, -1, getdate()), eomonth(getdate(), -1));
Command you suggested. It keeps erring out with needs 3 arguments.
So what I have done and it seems to be working is;
/** note the change on the next line **/
Declare @previous_date date = CAST(GETDATE() -1 as DATE);
--==== Get report parameters
Declare @last_day int = day(@previous_date)
, @report_month int = month(@previous_date)
, @report_year int = year(@previous_date);
Select [CP Name] = ControlPointName
, Codeline
, Type = cl.Legacytype
, [Sub Division] = SubName
, [Control Failures] = sum(ControlFailCount)
, [Control Point Down] = sum(ControlPointStatusDownCount)
, [Report Month] = sld.[Month]
, [Report Year] = sld.[Year]
, Failures = FailureCount
, [First of Month] = sum(Case When DAY = 1 Then FailureCount Else 0 End)
, [Middle of Month] = sum(Case When DAY = 15 Then FailureCount Else 0 End)
, [End of Month] = sum(Case When DAY = @last_day Then FailureCount Else 0 END)
From tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay sld
Inner Join tmdsDatabaseStatic.dbo.tblCodeLines cl On cl.CodelineNumber = sld.codeline
Where ControlPointName NOT LIKE 'W[DM]%'
And DAY In (1, 15, @last_day)
And month = @report_month
And year = @report_year
And FailureCount > '500'
Group By
sld.ControlPointName
, codeline
, cl.LegacyType
, SubName
, [Month]
, [Year]
, FailureCount
Order By
Failures desc;
Thanks again for taking the time to look it over.
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 29, 2022 at 4:00 pm
I missed a closing parentheses:
Declare @previous_date date = iif(day(getdate()) > 16, dateadd(day, -1, getdate()), eomonth(getdate(), -1));
Your change does not handle your requirements and would require manually modifying the code depending on the results you want. If this is supposed to be setup as a stored procedure then you need to understand what process/system is going to call that procedure so you can make sure the parameters are setup correctly.
For example, if building a report in SSRS - how will the user select which type of report to generate? Will that be determined by a report parameter - or will it be determined by when the report is executed? Something else?
What you are trying to design here is a single procedure that supports at least 2 types/styles of report. You either want previous month - or you want month to date. If the report is for previous month you select the 1st, 15th and EOM for the previous month. If the report is MTD you select the 1st, 15th and (day - 1) for the current month.
Back to the SSRS example - let's assume you build a report parameter allowing for those 2 options. In the report parameter you setup 2 options where 'Previous Month' = 0 and 'Current Month' = 1.
In the stored procedure you have an input parameters for report type:
CREATE PROCEDURE dbo.YourProcedure
@rptType smallint = 0 -- Defaults to previous month report type
AS
Now - you can use that parameter to determine the date to be used. Instead of using a variable called @previous_date - we will change that to @report_date and calculate it:
DECLARE @report_date date = iif(@rptType = 0, eomonth(getdate(), -1), cast(getdate() - 1));
Here - if our report type = 0 we determine the report date to be the end of the previous month, else we calculate the report date to be yesterday.
And with that - we can now calculate the variables needed to generate the report.
--==== Get report parameters
Declare @last_day int = day(@report_date)
, @report_month int = month(@report_date)
, @report_year int = year(@report_date);
So the question becomes - how is this procedure going to be called?
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
September 30, 2022 at 10:44 am
Thanks Jeffrey, I see and now understand where the close parenthesis was missing. that makes better sense.
I am calling the stored procedure from a C# Windows Application Menu item. I pull the data and create an Excel spreadsheet per my directors requirements.
I have not dabbled in SSRS. This gives me a good starting point to learn that as well.
Thank you Jeffrey!
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
October 1, 2022 at 12:34 pm
Jeffrey, you have been very educational and helpful. I am a little reluctant to ask one more helper question regarding this query set.
Is there a way to combine similar results on to a single line. see example of current results below.
MP 4.25609TIWS 220FRONT RANGE0082022842400084240
MP 4.25609TIWS 220FRONT RANGE0082022767460767460
MP 4.25609TIWS 220FRONT RANGE00820228208820800
Here is a better example of the same data;
CP Name|Codeline|Type|Sub Division|Control Failures|Control Point Down|Report Month|Report Year|Failures|First of Month|Middle of Month|End of Month
MP 4.25|609|TIWS 220|FRONT RANGE|0|0|9|2022|160008|0|160008|0
MP 4.25|609|TIWS 220|FRONT RANGE|0|1|9|2022|36310|0|036310
MP 4.25|609|TIWS 220|FRONT RANGE|0|0|9|2022|7020|7020|0|0
Want it to look like this;
SUM the FailureCount field of the three rows. and put the First/Middle/End all on the same line.
CP Name|Codeline|Type|Sub Division|Control Failures|Control Point Down|Report Month|Report Year|Failures|First of Month|Middle of Month|End of Month
MP 4.25|609|TIWS 220|FRONT RANGE|0|0|9|2022|211318|7020|160008|36310
Column 1 is the common identifier/denominator here. the last three columns are the 1st/15th/end of month results. Is there a way to combine them into a single row?
Thank you and I hope you have a great weekend.
Celebrating Choctoberfest in Hico, TX today!
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
October 3, 2022 at 7:39 pm
I think you have something incorrect in your grouping statement - or there are some hidden characters/spaces in one or more of the columns that is causing those values to be considered as different.
What you should get is a single rows for each set of unique values defined by the GROUP BY statement. If you see multiple rows, then something in one of those columns is causing SQL to consider them as different. Or - you have included an additional column in the group that isn't displayed in the select.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
October 3, 2022 at 8:02 pm
Jeffrey, I have tried placing the [First of Month], [Middle of Month], [End of Month] in the Group by as it is in the select statement, and have also changed to to Single quotes. In both instances I get this error;
Each GROUP BY expression must contain at least one column that is not an outer reference.
I am using the Previous month query shown here.
Use tmdsDatabaseStatistics;
Declare @previous_month date = eomonth(getdate(), -1); -- Run for the current date
Declare @last_day int = day(eomonth(@previous_month)) -- Last day of the month for current date
, @report_month int = month(@previous_month)
, @report_year int = year(@previous_month);
Select [CP Name] = ControlPointName
, Codeline
, Type = cl.Legacytype
, [Sub Division] = SubName
, [Control Failures] = sum(ControlFailCount)
, [Control Point Down] = sum(ControlPointStatusDownCount)
, [Report Month] = sld.[Month]
, [Report Year] = sld.[Year]
, Failures = FailureCount
/** as written **/
, [First of Month] = sum(Case When DAY = 1 Then FailureCount Else 0 End)
, [Middle of Month] = sum(Case When DAY = 15 Then FailureCount Else 0 End)
, [End of Month] = sum(Case When DAY = @last_day Then FailureCount Else 0 END)
/** alternate attempt **/
, 'First of Month' = sum(Case When DAY = 1 Then FailureCount Else 0 End)
, 'Middle of Month' = sum(Case When DAY = 15 Then FailureCount Else 0 End)
, 'End of Month' = sum(Case When DAY = @last_day Then FailureCount Else 0 END)
From tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay sld WITH (NOLOCK)
Inner Join tmdsDatabaseStatic.dbo.tblCodeLines cl On cl.CodelineNumber = sld.codeline
Where ControlPointName NOT LIKE 'W[DM]%'
And DAY In (1, 15, @last_day)
And month = @report_month
And year = @report_year
And FailureCount > '500'
Group By
ControlPointName
, codeline
, cl.LegacyType
, SubName
,FailureCount
, [Month]
, [Year]
/** query runs fine before adding this next set **/
/** as declared in the Select Portion **/
-- , [First of Month]
-- , [Middle of Month]
-- , [End of Month]
/** Changing the Select to Single Quotes **/
-- , 'First of Month'
-- , 'Middle of Month'
-- , 'End of Month'
Order By
Failures desc;
I attached the output of the query in an xls for you.
Thanks!
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
October 3, 2022 at 8:32 pm
The problem is actually quite simple - you are grouping by FailureCount. So you are getting a failure count for the 1st, the 15th and the last day = 3 rows.
Since you want to cross-tab the failure count - you need to remove that column from the select and grouping.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
Viewing 15 posts - 31 through 45 (of 51 total)
You must be logged in to reply to this topic. Login to reply