September 14, 2022 at 7:12 pm
I don't think so. PIVOT works on only one column at a time, IIRC. A cross-tab is almost certainly the best method here.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 14, 2022 at 9:33 pm
I have only scan -read the conversation and not opened the workbook, but it sounds like you want to move the day number to 3 columns and populate those columns with a value from one of the other fields, with all of the other fields sill making up the row data.
PIVOT should be able to handle that but more fields makes the PIVOT more difficult to construct. Is there a shorter unique key you can use to generate the pivot and then join the pivot data back up to the full query?
PIVOT becomes a bit of a pain if the columns names you want to pivot are not consistent as you then have to do it as a dynamic query. This would also mean that you couldn't use the output in a reporting tool because the signature of the returns data will change unless you can call the day 'first''fifteenth','last', but if you are outputting to a reporting tool then probably just easier to generate a cross tab or equivalent in the reporting tool. not sure how you would handle inconsistent results sets in c# as you would have to map on column position or dynamic names from the model.
September 19, 2022 at 12:16 pm
Thanks Scott. Sorry for the delay. Took a few days away!
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 19, 2022 at 12:18 pm
Yes, Aaron, that is correct. I need it to display the three dates and the associated data in new columns. Reading up on CrossTab I think you and are correct. A crosstab may be my answer. I need to dig into it more though to understand it.
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 19, 2022 at 2:47 pm
Scott/Aaron, I think I have nailed the basic. Take a look at this one.
I still have to figure out how to replace the 1, 15, 31 for;
DECLARE @FirstDOM datetime, @NextFirstDOM datetime
SET @FirstDOM = DATEADD(MONTH,-1,(SELECT DATEADD(d,-1,DATEADD(mm,DATEDIFF(m,0,GETDATE()),1 ))))
SET @NextFirstDOM = DATEADD(MONTH, 1, @FirstDOM)
Have not used this before.
USE tmdsDatabaseStatistics
GO
SELECT
ControlPointName as 'CP Name',
Codeline,
tmdsDatabaseStatic.dbo.tblCodelines.Legacytype as Type,
SubName as 'Sub Division',
Year,
Month,
Day,
SUM(ControlFailCount) AS 'Control Failures',
SUM(ControlPointStatusDownCount) AS 'Control Point Down',
FailureCount AS 'Failures',
(CASE WHEN MONTH = 8 and DAY = 1
THEN FailureCount
ELSE 0
END) AS "First of Month",
(CASE when MONTH = 8 and DAY = 15
THEN FailureCount
ELSE 0
END) AS "Middle of Month",
(CASE when MONTH = 8 and DAY = 31
THEN FailureCount
ELSE 0
END) AS "End of Month"
FROM tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay WITH (NOLOCK) INNER JOIN tmdsDatabaseStatic.dbo.tblCodeLines ON tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay.codeline = tmdsDatabaseStatic.dbo.tblCodeLines.CodelineNumber
--FROM tblStatisticsLocationDay
WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and day in (1, 15, 31) and month = 8 and year = 2022 and FailureCount < '500' and FailureCount > '99'
Group BY ControlPointName, codeline, tmdsDatabaseStatic.dbo.tblCodeLines.LegacyType, SubName, Year, Month, Day, FailureCount
Order by Failures
Thanks
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 20, 2022 at 10:57 am
How do I pass a variable to the CASE Statement for the First/Middle/End of Month.
doing a
DECLARE
@year int
@month int
@day int
SET @year = 2022
SET @month = 8
SET @day = 1
I also tried using;
DECLARE @d DATETIME = getdate(), @dm TINYINT = 29, @dm2 TINYINT=31
SELECT month(DATEADD(DAY, @dm-1, DATEADD(MONTH, -1, DATEADD(DAY, 1-DAY(@d), @d))));
SELECT month(DATEADD(DAY, @dm2-1, DATEADD(MONTH, -1, DATEADD(DAY, 1-DAY(@d), @d))));
And pass that as variables.
(CASE WHEN @month AND @day THEN FailureCount ELSE 0 END) AS "First of Month",
(CASE WHEN MONTH = 8 and DAY = 15 THEN FailureCount ELSE 0 END) AS "Middle of Month",
(CASE WHEN MONTH = 8 and DAY = 31 THEN FailureCount ELSE 0 END) AS "End of Month"
I receive this error
Msg 137, Level 15, State 2, Line 27
Must declare the scalar variable "@month".
Msg 319, Level 15, State 1, Line 31
Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.
Can I pass a variable with a CASE Statement?
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 20, 2022 at 2:26 pm
No need to specify the last day / number of days in the month, that can easily be calculated:
(CASE WHEN MONTH = @month AND DAY = @day THEN FailureCount ELSE 0 END) AS "First of Month",
(CASE WHEN MONTH = @month AND DAY = 15 THEN FailureCount ELSE 0 END) AS "Middle of Month",
(CASE WHEN MONTH = @month AND DAY = DATEDIFF(DAY, DATEFROMPARTS(@year, @month, @day), DATEADD(MONTH, 1, DATEFROMPARTS(@year, @month, @day))) THEN FailureCount ELSE 0 END) AS "End of Month"
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
September 20, 2022 at 4:00 pm
I will give a try when I get back to my desk.
thanks Scott
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 21, 2022 at 3:10 pm
Scott, I modified the query and it looks like this (included to make sure we are both on the same page);
USE tmdsDatabaseStatistics
DECLARE @day int
DECLARE @month int
DECLARE @year int
GO
SELECT
ControlPointName as 'CP Name',
Codeline,
tmdsDatabaseStatic.dbo.tblCodelines.Legacytype as Type,
SubName as 'Sub Division',
Year,
Month,
Day,
SUM(ControlFailCount) AS 'Control Failures',
SUM(ControlPointStatusDownCount) AS 'Control Point Down',
FailureCount AS 'Failures',
(CASE WHEN MONTH = @month AND DAY = @day THEN FailureCount ELSE 0 END) AS "First of Month",
(CASE WHEN MONTH = @month AND DAY = 15 THEN FailureCount ELSE 0 END) AS "Middle of Month",
(CASE WHEN MONTH = @month AND DAY = DATEDIFF(DAY, DATEFROMPARTS(@year, @month, @day), DATEADD(MONTH, 1, DATEFROMPARTS(@year, @month, @day))) THEN FailureCount ELSE 0 END) AS "End of Month"
FROM tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay INNER JOIN tmdsDatabaseStatic.dbo.tblCodeLines ON tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay.codeline = tmdsDatabaseStatic.dbo.tblCodeLines.CodelineNumber
WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and day in (1, 15, 19) and month = 9 and year = 2022 and FailureCount > '500'
Group BY ControlPointName, codeline, tmdsDatabaseStatic.dbo.tblCodeLines.LegacyType, SubName, Year, Month, Day, FailureCount
Order by Failures desc
Running the query results in;
Must declare the scalar variable "@month".
It shows the same error for the @year, and @day variables. You will note I have the variable declared at the beginning of the query.
If I change the CASE Statements to;
(CASE WHEN DAY = 1 THEN FailureCount ELSE 0 END) AS "First of Month",
(CASE WHEN DAY = 15 THEN FailureCount ELSE 0 END) AS "Middle of Month",
(CASE WHEN DAY = DATEDIFF(DAY, DATEFROMPARTS(YEAR, MONTH, DAY), DATEADD(MONTH, 1, DATEFROMPARTS(YEAR, MONTH, DAY))) THEN FailureCount ELSE 0 END) AS "End of Month"
Then the query runs and based on the where statement it gives the results for the 1, 15, 19, which is what I had in it. I think I need to move the or First/Middle/End of month date command into the WHERE clause.
Is this making sense? or am I just hitting the wrong direction.
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 21, 2022 at 3:27 pm
Scott, I think this is the answer. Please review and give me your opinion.
USE tmdsDatabaseStatistics
--DECLARE @day int
DECLARE @month int
DECLARE @year int
DECLARE @d DATETIME = getdate(), @day TINYINT = 29, @dm2 TINYINT=31
SET @day = month(DATEADD(DAY, @day-1, DATEADD(MONTH, -1, DATEADD(DAY, 1-DAY(@d), @d))));
SET @month = DATEPART(mm, DATEADD(mm,-1, GETDATE()));
SET @year = DATEPART(yyyy, DATEADD(yyyy, 0, GETDATE()));
SELECT
ControlPointName as 'CP Name',
Codeline,
tmdsDatabaseStatic.dbo.tblCodelines.Legacytype as Type,
SubName as 'Sub Division',
Year,
Month,
Day,
SUM(ControlFailCount) AS 'Control Failures',
SUM(ControlPointStatusDownCount) AS 'Control Point Down',
FailureCount AS 'Failures',
(CASE WHEN DAY = 1 THEN FailureCount ELSE 0 END) AS "First of Month",
(CASE WHEN DAY = 15 THEN FailureCount ELSE 0 END) AS "Middle of Month",
(CASE WHEN DAY = DATEDIFF(DAY, DATEFROMPARTS(YEAR, MONTH, DAY), DATEADD(MONTH, 1, DATEFROMPARTS(YEAR, MONTH, DAY))) THEN FailureCount ELSE 0 END) AS "End of Month"
FROM tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay INNER JOIN tmdsDatabaseStatic.dbo.tblCodeLines ON tmdsDatabaseStatistics.dbo.tblStatisticsLocationDay.codeline = tmdsDatabaseStatic.dbo.tblCodeLines.CodelineNumber
WHERE ControlPointName NOT LIKE 'WD%' AND ControlPointName NOT LIKE 'WM%' and DAY in (1, 15, @day) and month = @month and year = @year and FailureCount > '500'
Group BY ControlPointName, codeline, tmdsDatabaseStatic.dbo.tblCodeLines.LegacyType, SubName, Year, Month, Day, FailureCount
Order by Failures desc
Thanks!
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 21, 2022 at 8:40 pm
I was trying to follow this - and it seems to have gone in different directions. It seems you want to define the DAY for the end of the month - which is actually a lot easier than what Scott has provided.
Use tmdsDatabaseStatistics;
Declare @current_date datetime = getdate(); -- 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)
, [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 = @current_month
And year = @current_year
And FailureCount > '500'
Group By
ControlPointName
, codeline
, cl.LegacyType
, SubName
Order By
Failures desc;
Some of the changes I made:
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 12:25 pm
First off, THANK YOU to all of you.
Jeffrey, one clarification question regarding Month/Day/year. The boss wants that info there. I am not following comment # 7 completely.
Also if I want to query it for PREVIOUS month, I should only have to change the @current_date and add a -1 correct?
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
Since the query is already filtered by year/month and specific days - there is no reason to include them in the results. If you do want to include them, then you can include the year/month but not the days. The day column is what you are cross-tabbing and including that as a part of the grouping would actually cause the results to not roll up the way you want.
If you want the previous month - then you actually need to do something like DATEADD(month, -1, GETDATE()). Any date in the previous month will work. If this is going to need to be run for the previous month then change @current_date to a DATE data type and use EOMONTH:
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
, [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;
Here I have changed the variables to reflect the previous month - and labeled the columns/variables to reflect the previous month, and report month/year.
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 2:34 pm
Jeffrey, thank you. that is very helpful and explained well.
Arron/Scott, Yours were very helpful and I appreciate all the explanation and recommendations. I learned a lot from this little query!
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
September 22, 2022 at 3:50 pm
Jeffrey, one last afterthought question;
With regard to the current Month query; What if I wanted to go to @current_date -1?
I tried changing the @last_day to date = day(day(@current_date), -1) .
This runs however it is not providing results for the Middle of month now.
Thanks,
Retired Navy Chief Petty Officer, Consulting Systems Engineer, C# Programmer, SQL Beginner
Viewing 15 posts - 16 through 30 (of 51 total)
You must be logged in to reply to this topic. Login to reply