November 21, 2012 at 9:20 am
I have a dataset that i need to iterate thru in a row wise manner where each row has columns that will either be filled with a value or zero.
I need to find the first occurance in each row(column) that a non-zero value occurs.
I can think of at least one way i can do this but it is very inelegant, and something a bit more clever would suit my vanity... 😀
ideas/examples?
November 21, 2012 at 9:25 am
richard.noordam (11/21/2012)
I have a dataset that i need to iterate thru in a row wise manner where each row has columns that will either be filled with a value or zero.I need to find the first occurance in each row(column) that a non-zero value occurs.
I can think of at least one way i can do this but it is very inelegant, and something a bit more clever would suit my vanity... 😀
ideas/examples?
ddl/sample data/desired output?
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 21, 2012 at 9:34 am
96719672967396749781978297839784989198929893989499019902
09672967300009784000000
00000000000989499010
First row is the header.
each row has an identifier as well, but as i go thru the rows, what i should get the first occurance only.
the columns are varchar(4)
ie.
row 1: 9672
row 2: 9894
sry should of left data example.
November 21, 2012 at 9:50 am
Not sure how it'll perform (and could depend on your definition of "first"), but:
SELECT COALESCE(
NULLIF([9671], 0),
NULLIF([9672], 0),
NULLIF([9673], 0),
NULLIF([9674], 0),
NULLIF([9781], 0),
NULLIF([9782], 0),
NULLIF([9783], 0),
NULLIF([9784], 0),
NULLIF([9891], 0),
NULLIF([9892], 0),
NULLIF([9893], 0),
NULLIF([9894], 0),
NULLIF([9901], 0),
NULLIF([9902], 0)
)
FROM tbl
November 21, 2012 at 9:54 am
I should also add that the column list changes and gets added to on a quarterly basis (as defined by the institution i work at) and the data set i show is only a small set of the column list.
November 21, 2012 at 9:55 am
And if the 0's can be NULLs in the table instead, you can get rid of all the NULLIF's.
November 21, 2012 at 10:02 am
richard.noordam (11/21/2012)
I should also add that the column list changes and gets added to on a quarterly basis (as defined by the institution i work at)
Can these changes/additions be codified? You'll almost certainly end up having to modify the query otherwise.
the data set i show is only a small set of the column list.
Not too difficult to produce a select list containing all columns that you can then modify.
Cheers
Gaz
November 27, 2012 at 9:19 am
well this is what was done to accomplish it. inelegant as it seems to me.
1. generates the SQL YearQuarter list.
declare YRQList cursor for
SELECT DISTINCT YearQuarterID from CCSGen.dbo.vwODSSM_YearQuarter where YearQuarterID between @YRQSTART and @YRQMax and firstClassDay < GETDATE() and YearQuarterID >= @YRQStart
2. runs thru a series of updates for each column in order.
DYN2 = fixing nulls to 0's
DYN3 = where they are NOT null put a 1
DYN4 = because of the progression i can update the YRQStart field if there is a 1 in the current YRQ column and YRQSTART is null. (being first occurrance)
OPEN YRQList
FETCH NEXT FROM YRQList
INTO @YRQ
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @DYN2 as nvarchar(4000)
DECLARE @DYN3 as nvarchar(4000)
DECLARE @DYN4 as nvarchar(4000)
SET @DYN2 = 'UPDATE [CCSIRDev].[dbo].[CCSRetention] SET [' + @YRQ + ']=''0'' WHERE [' + @YRQ + '] IS NULL'
SET @DYN3 = 'UPDATE [CCSIRDev].[dbo].[CCSRetention] SET [' + @YRQ + ']=1 WHERE [' + @YRQ + '] <> ''0'''
SET @DYN4 = 'UPDATE CCSRetention SET YRQStart = ''' + @YRQ + ''' WHERE [' + @YRQ + '] = 1 and YRQStart IS NULL'
PRINT '#1:' + @DYN2
EXEC sp_executesql @DYN2
PRINT '#2:' + @DYN3
EXEC sp_executesql @DYN3
PRINT '#3:' + @DYN4
EXEC sp_executesql @DYN4
print 'Column Update Complete ' + @YRQ
--PRINT @DYN2
FETCH NEXT FROM YRQList INTO @YRQ
END
CLOSE YRQList
DEALLOCATE YRQList
now there is probably a few ways that this could be solved... maybe better? this most definitely works though.
November 27, 2012 at 10:25 am
now there is probably a few ways that this could be solved... maybe better? this most definitely works though.
Agreed there are better ways to do this. I doubt you need a **cough**cursor**cursor** for this. But without anything to work with there is nothing anybody can do to help. You have certainly been around here long enough to know the format of what is expected when posting questions. If you are unsure, please read the article at the first link in my signature.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 27, 2012 at 11:11 am
first of all i was NOT looking for a specific solution. So your comment, and snarkyness are not appreciated, nor warranted.
I was indeed looking for a pattern of behavior to apply, to a problem i have, which this problem is a very minor piece of the whole problem that i'm trying to solve.
I was already using a coalesce statement with a select to generate the column list, and the solution wasn't to put the entire set of code here, and ask that it be tore apart. (FYI: another part is a crosstab using said column list, but that is outside of what i'm asking, which works awesome btw). Mainly because the problem that I asked about here can be stated simply.
What is the best way to deal with a set of rows and determine/return the first occurance of a non-zero column value in each row, using a set of column fields that are determined at runtime?
There should be a 'best-practice' on this, i would think, maybe not.
in any case.... there should be a pattern of behavior in assessing and attempting to solve, that doesn't rely on specific data.
So datasets were 'extra' and unnecessary, even though I provided in the second post.
Can anyone else help?
November 27, 2012 at 12:03 pm
richard.noordam (11/27/2012)
first of all i was NOT looking for a specific solution. So your comment, and snarkyness are not appreciated, nor warranted.
If I came across as snarky I apologize. I think perhaps you misunderstood my coughing around a cursor.
I was indeed looking for a pattern of behavior to apply, to a problem i have, which this problem is a very minor piece of the whole problem that i'm trying to solve.
I was already using a coalesce statement with a select to generate the column list, and the solution wasn't to put the entire set of code here, and ask that it be tore apart. (FYI: another part is a crosstab using said column list, but that is outside of what i'm asking, which works awesome btw). Mainly because the problem that I asked about here can be stated simply.
What is the best way to deal with a set of rows and determine/return the first occurance of a non-zero column value in each row, using a set of column fields that are determined at runtime?
There should be a 'best-practice' on this, i would think, maybe not.
in any case.... there should be a pattern of behavior in assessing and attempting to solve, that doesn't rely on specific data.
You are looking for best practices in dealing with data is denormalized. I get that you have done this via dynamic cross tab but there isn't really a normal way of handling this. The best chance is the coalesce. An even better place in the process is to get your data the way you want it when you crosstab it initially.
So datasets were 'extra' and unnecessary, even though I provided in the second post.
What you posted is not a dataset. Nobody can load that into a table without first writing all the insert statements and create table statements. Keep in mind that we are all volunteers around here. When you don't post stuff in a consumable format the best anybody can do is take a shot in the dark.
Your post that contained your solution sounded very much like you wanted help with a better approach. You included selecting from a view (that we don't have), then updating a table (that we don't have). It seems that you want a magical solution to a problem without providing the details needed to solve it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 27, 2012 at 12:05 pm
richard.noordam (11/27/2012)
What is the best way to deal with a set of rows and determine/return the first occurance of a non-zero column value in each row, using a set of column fields that are determined at runtime?
In my opinion best practice here would be to do the least clever option.
That said, doesn't it really depend on how responsive you need your system to be? Ie., if a new column appears, would you like your code to immediately change to adapt, or will hand editting a routine be enough?
I have a routine that checks for column additions or changes for a few tables but it doesn't do much more than alert me to the fact I need to update a few routines. I could make it dynamic of course, but I worry about leaving the company with too much "clever" programming. It might be a borderline concern.
On the other hand, I have a system I threw together with m4 the macro processor that I can supply a server name, database name, network share, and it produces a hardwired set of backup routines, I actually did it that way because we like nice static backup t-sql routines but I got tired of doing the manual edits for each new database.
There should be a 'best-practice' on this, i would think, maybe not.
in any case.... there should be a pattern of behavior in assessing and attempting to solve, that doesn't rely on specific data.
I've never had much luck discussing interesting and maybe generalized topics here and have for the most part just decided to stick with answering a few posts now and then, but maybe you'll fare better. Best of luck in any case!
November 27, 2012 at 12:18 pm
You are looking for best practices in dealing with data is denormalized. I get that you have done this via dynamic cross tab but there isn't really a normal way of handling this. The best chance is the coalesce. An even better place in the process is to get your data the way you want it when you crosstab it initially.
This what i was looking for.
What you posted is not a dataset. Nobody can load that into a table without first writing all the insert statements and create table statements. Keep in mind that we are all volunteers around here. When you don't post stuff in a consumable format the best anybody can do is take a shot in the dark.
Your post that contained your solution sounded very much like you wanted help with a better approach. You included selecting from a view (that we don't have), then updating a table (that we don't have). It seems that you want a magical solution to a problem without providing the details needed to solve it.
i didn't need nor want a specific solution to this 'data', and the 'data' was and 'example set', and not a 'working set'. i was indeed looking for a more 'patterned' approach to do this, more of a WAY to solve this type of problem rather than the supposed 'magical' fix.
Apparently, i was already doing it.
the iterative approach through the columns works, and does indeed allow me to generate the YRQStart (first occurance column), because of this iterative approach.... but dang it it uses a cursor, and anytime anyone uses a cursor... i hear a 'it should be a set based approach'.
i am unclear how i can clear that point up, if you would like to expound on it?
November 27, 2012 at 12:55 pm
Due the denormalized data and the very unique situation it is impossible to give a generic this is a set based way to do this. If what you have is satisfactory to you then I say go for it.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 28, 2012 at 1:23 pm
i do have to say, you should give yourself a bit more credit, as our private message indicated you did understand my problem, and did suggest a potential solution, which did indeed change the approach i was using for that section of the problem....
to a 75% decrease in run time as well.
and it's beginning to apease my need of a bit more elegant solution. 😉
THANKS!!!
Viewing 15 posts - 1 through 15 (of 16 total)
You must be logged in to reply to this topic. Login to reply