Greetings experts,
I have a stored proc that uses table pivot to dynamically create the value for a column name called EventYear and then uses that value as column name
For instance, this current year is 2019.
The stored proc determines it is 2019 and then PIVOTS that year as column name so the value of another fieldname called AmountPaid can be stored under the 2019 column.
Sample Data:
Name Transaction ID Date Registered Annual Dues Amount Owed 2019
John Doe 1 09/18/2019 $120.00 $60.00 $60.00 (this will be amount paid in 2019)
This code works great.
The issue we are having is that we would like to append Year_ to EventYear field (datetime data type) so that if the year is 2019, it will display as Year_2019. Regardless of the year, Year_ will be appended to it.
How do I modify the below stored proc to add Year_ to EventYear?
ALTER PROCEDURE [dbo].[uspGetPivotedData]
AS
IF object_id('dbo.MemberHistory') IS NOT NULL
DROP TABLE dbo.MemberHistory
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX);
SET @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(YEAR(t.EventYear))
FROM Transactions t
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'),1,1,'');
set @query = N'WITH CTE AS
(
SELECT
t.TransactionID,
m.memberName,
m.date_registered,
envelopeNumber,
registrationFee,
Amount,
YEAR(eventyear) eventyear,
CONVERT(varchar, CAST(t.AmountPaid AS money), 1) AS AmountPaid,
t.Balance
FROM
dbo.Transactions AS t
INNER JOIN dbo.Members AS m
ON t .MemberID = m.MemberID
INNER JOIN dbo.PaymentTypes AS p
ON t .TypeID = p.PaymentTypeID
)
SELECT
TransactionID,
memberName,
date_registered,
envelopeNumber,
CONVERT(varchar, CAST(registrationFee AS money), 1) AS registrationFee,
Amount,
' + @cols + N',
CONVERT(varchar, CAST(Balance AS money), 1) AS Balance
INTO MemberHistory
FROM
CTE
PIVOT
(
MAX(AmountPaid)
FOR eventyear IN (' + @cols + N')
) p
ORDER BY
TransactionID';
EXECUTE sp_executesql @query;
Many thanks in advance.
September 18, 2019 at 4:42 pm
Maybe this?
'YEAR_' + CONVERT(nvarchar(8), YEAR(eventyear)) eventyear,
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 18, 2019 at 5:01 pm
Thanks for your response, Michael.
I actually tried that and got incorrect syntax near 'Year_'.
If I remove the single quotes, it gives me invalid column name error.
Please this is more like dynamic sql embedded in stored proc.
September 18, 2019 at 5:54 pm
It's embedded. You need to ADD quotes, not take them away
''YEAR_'' + CONVERT(nvarchar(8), YEAR(eventyear)) eventyear,
Before you execute the dynamic SQL, print it.
PRINT @query
Run that query, fix the issues, and make it work. You can then put it back into the Dynamic SQL.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 18, 2019 at 6:04 pm
It's embedded. You need to ADD quotes, not take them away
''YEAR_'' + CONVERT(nvarchar(8), YEAR(eventyear)) eventyear,
Before you execute the dynamic SQL, print it.
PRINT @query
Run that query, fix the issues, and make it work. You can then put it back into the Dynamic SQL.
It's not that simple. He's using a comma separated list of years in his dynamic SQL, and this would only handle the first year in that list. Also, why use NVARCHAR(8) when years will have exactly four digits for many centuries? Why not use NCHAR(4)? And why are you mixing CHAR and NCHAR? It should be N''YEAR_'' + ...
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 18, 2019 at 6:17 pm
Thanks a lot Drew for your response.
When I used your solution, I get
2019
NULL
NULL
NULL
NULL
NULL
NULL
NULL
Normally, result looks like this:
2019
60.0000
120.0000
120.0000
60.0000
80.0000
120.0000
All I am trying to do is get the result to look like this:
Year_2019
60.0000
120.0000
120.0000
60.0000
80.0000
120.0000
or Year_2020
60.0000
120.0000
120.0000
60.0000
80.0000
120.0000,
depending on the year.
Just to be sure, this is how I am using the solution you provided:
N''YEAR_'' + CONVERT(char(4), YEAR(eventyear)) eventyear
September 18, 2019 at 6:23 pm
One more thing, when I printed the query as Michael suggested, I get the following:
WITH CTE AS
(
SELECT
t.TransactionID,
m.memberName,
m.date_registered,
envelopeNumber,
registrationFee,
Amount,
N'YEAR_' + CONVERT(NCHAR(4), YEAR(eventyear)) eventyear,
CONVERT(varchar, CAST(t.AmountPaid AS money), 1) AS AmountPaid,
t.Balance
FROM
dbo.Transactions AS t
INNER JOIN dbo.Members AS m
ON t .MemberID = m.MemberID
INNER JOIN dbo.PaymentTypes AS p
ON t .TypeID = p.PaymentTypeID
)
SELECT
TransactionID,
memberName,
date_registered,
envelopeNumber,
CONVERT(varchar, CAST(registrationFee AS money), 1) AS registrationFee,
Amount,
[2019],
CONVERT(varchar, CAST(Balance AS money), 1) AS Balance
INTO MemberHistory
FROM
CTE
PIVOT
(
MAX(AmountPaid)
FOR eventyear IN ([2019])
) p
ORDER BY
TransactionID
September 18, 2019 at 6:38 pm
Michael L John wrote:It's embedded. You need to ADD quotes, not take them away
''YEAR_'' + CONVERT(nvarchar(8), YEAR(eventyear)) eventyear,
Before you execute the dynamic SQL, print it.
PRINT @query
Run that query, fix the issues, and make it work. You can then put it back into the Dynamic SQL.
It's not that simple. He's using a comma separated list of years in his dynamic SQL, and this would only handle the first year in that list. Also, why use
NVARCHAR(8)when years will have exactly four digits for many centuries? Why not useNCHAR(4)? And why are you mixingCHARandNCHAR? It should beN''YEAR_'' + ...Drew
Because i was lazy and was answering this on my phone.
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 18, 2019 at 6:48 pm
Take a look at this. By changing things such as in my original suggestion, it broke your query.
This will change all of the values 'YEAR_' + CONVERT(NCHAR(4), YEAR(eventyear)) eventyear, so the comparison in the pivot will no longer get any matches, so you see null values being returned.
See is this query does it. Do you see what has changed, and what you may need to do?
Also, what happens when someone wants data for 2018? Or, it's now 2020? Do you need to manually change the query?
WITH CTE AS
(
SELECT
t.TransactionID,
m.memberName,
m.date_registered,
envelopeNumber,
registrationFee,
Amount,
--This breaks your query.
--N'YEAR_' + CONVERT(NCHAR(4), YEAR(eventyear)) eventyear,
--The comparison below will no longer work
YEAR(eventyear)) eventyear,
--Please size your variables.
--And formating is usually better in the actual report, as opposed to T-SQL
CONVERT(varchar, CAST(t.AmountPaid AS money), 1) AS AmountPaid,
t.Balance
FROM
dbo.Transactions AS t
INNER JOIN dbo.Members AS m
ON t .MemberID = m.MemberID
INNER JOIN dbo.PaymentTypes AS p
ON t .TypeID = p.PaymentTypeID
)
SELECT
TransactionID,
memberName,
date_registered,
envelopeNumber,
CONVERT(varchar, CAST(registrationFee AS money), 1) AS registrationFee,
Amount,
--Simply name the header differently
[2019] as 'YEAR_2019',
CONVERT(varchar, CAST(Balance AS money), 1) AS Balance
INTO MemberHistory
FROM
CTE
PIVOT
(
MAX(AmountPaid)
--This is what is now broken by the above.
FOR eventyear IN ([2019])
) p
ORDER BY
TransactionID
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 18, 2019 at 7:35 pm
No, the query is dynamic for a reason.
This way, it will pick up and display current year.
The reason I used 2020 is because that will be the next year after 2019.
The requirement is that at the end of the current year, in this case 2019, all the values of 2019 will be retained. Then it will begin to show 2020.
In the end, we will have results similar to this:
Year_2019 Year_2020 Year_2021 etc with their values for history comparisons.
So, no the year values can't be created manually.
About the one that is now broken, any ideas how to get it to work like the others?
Thanks again for your help
September 18, 2019 at 7:55 pm
The problem you are seeing is that you made a dynamic part of your query static. Specifically, the 'Year_' + CONVERT(NCHAR(4), YEAR(eventyear)) needs to be dynamic so that when you print your query it will read Year_2019, Year_2020. I told you it wasn't that simple.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
September 18, 2019 at 8:01 pm
>> I have a stored proc that uses table pivot to dynamically create the value for a column name called EventYear and then uses that value as column name <<
I hope you're not really writing code like this. One of the major concepts of RDBMS is separating entities, attributes and values not scrambling together in a non-data model. Dynamic SQL is how you tell the world that you don't know what you're doing until some unknown user at runtime makes the design decision you should have made. I also don't like the pivot operation since its proprietary and non-relational, but that's another issue.
>> The stored proc determines it is 2019 .. <<
Actually the data should determine a year, not a procedure.
>> .. and then PIVOTS that year as column name so the value of another field [sic] name called AmountPaid can be stored under the 2019 column. <<
It would've been nice if you post to DDL, as per this forum's netiquette. You are formatting a report in the database tier of a tiered architecture; you're not supposed to do thaT. The database tier should pass data to a presentation layer. This is the way we been coding client/server since the late 1970s.
Your sample data includes punctuation marks on the form of $'s and you don't know the correct format for a date in ANSI ISO standard SQL. Google why we never use the old Sybase MONEY data types; they do not do correct math!
The issue we are having is that we would like to append Year_ to EventYear field [sic: columns are not fields] (DATETIME data type) so that if the year is 2019, it will display as Year_2019. Regardless of the year, Year_ will be appended to iT.
You should not have a table entitled "PaymentTypes" unless those types are constantly changing and the domain is very large. I doubt that you have more than 100 different kinds of payments. But your next design error was creating a column entitled "type_id" in violation of ISO 11179 naming rules and common sense. Postfix is like "_type" and "_id" are what data modelers call and attribute property. They have to be part of an attribute (column) name such as "blood_type" or "customer_id"; essentially what you've written looks like a bunch of attitudes strung together without a noun. An SQL programmer would probably have put the list of valid payment types in a CHECK() constraint.
>> How do I modify the below stored proc to add Year_ to EventYear? <<
No, you throw it out and start programming correctly instead. Just starting with your CTE (why do you think CTE is a good name for it? What does it represent?) We seem to have two or more tables and we don't know the name of the third table. We have no table names on many of the columns. We don't have any idea what you're paying off. Etc. In short, these generic names tell us nothing.
SELECT
T.transaction_id,
M.member_name,
M.registration_date,
??.envelope_nbr,
??.registration_fee_amt,
??.event_year,
T.something_paid_amt
T.something_balance
FROM Transactions AS T, Members AS m, ?? AS ??
WHERE T.member_id = M.member_id;
Would you like to start over, follow the forum netiquette, and post DDL for these tables? Then we can begin to really help you. It would be nice if we knew the keys and the relationships among the tables.
Please post DDL and follow ANSI/ISO standards when asking for help.
September 18, 2019 at 8:04 pm
Hey Drew,
Which part of the query is static, I am curious?
If you referring to the last query I posted, that's the printed version after I did PRINT @query.
I don't see anything static in the original code I posted. What do I know!
And you can't do this:
'Year_' + CONVERT(NCHAR(4), YEAR(eventyear))
The single quotes will blow up the dynamic query.
September 18, 2019 at 8:14 pm
Michael,
You said formatting is better in actual report and I agree but it was as a result of trying to use the values in report that created the issue that led to me wanting to change the query in this stored proc.
When I use report to query the Membership table, 2019 obviously is displayed as a column name and ms reporting services doesn't like that.
It has issues with displaying 2019 as value.
Wait, let me try another trick in reporting services and see if that resolves my issue.
September 18, 2019 at 8:43 pm
Hey Drew,
Which part of the query is static, I am curious?
If you referring to the last query I posted, that's the printed version after I did PRINT @query.
I don't see anything static in the original code I posted. What do I know!
And you can't do this:
'Year_' + CONVERT(NCHAR(4), YEAR(eventyear))The single quotes will blow up the dynamic query.
The dynamic part is the part that changes based on the data. The static part is the part that is static regardless of the data. So, in your original query the two @cols references are dynamic, and everything else for that query is static.
The single quotes are blowing up, because you're including a dynamic part in the static part. Your printed query should say Year_2019. No quotes, no conversion, and it should change based on the data, that is, it should be dynamic. Your actual printed query says 'Year_' + CONVERT(NCHAR(4), YEAR(eventyear)), which contains quotes, contains conversions and does NOT change based on the data, this is, it's static.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 15 posts - 1 through 15 (of 19 total)
You must be logged in to reply to this topic. Login to reply