July 5, 2011 at 3:00 pm
I've got following problem.
TableA contains info about machine for each month
Column id
column period1 (January)
column period2 (February)
...
column period12 (December)
tableA is populated by process that runs on the worksations on daily basis
TableB
I insert data from table A for that specific Month. If I run the script today it would pick info for July to populate the table
I'm able to run the script and everything works fine however I do have issue when the value in Table A for specific machine is not yet populated
in that case no info is present in tableB for that machine, what I would like to do in such case is look at the info from previous month if thats
populate use that info, my question now is how could I achieve this using SQL Script (Stored procedure)
Any help or suggestions are really appreciated.
July 5, 2011 at 3:59 pm
If the period columns are nullable and dont get filled in until the current month, then this will get the last filled in month:
insert into tableB ...
select coalesce(period12, period11, period10,...period2, period1) as CurrentMonth from tableA
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
July 5, 2011 at 4:20 pm
What Todd recommended is about the best I could offer blindly as well. If you'd like more particular help, we're going to need some sample DDL/data to understand the issue more specifically.
If you need that, please check the first link in my signature, it'll walk you through what we're looking for to assist you. Remember we're volunteers, and the more work you can do to make our lives easier, the more likely we are to help you.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 5, 2011 at 4:41 pm
Hi thx for the quick feedback, the solution works for values that are 'NULL'. How about empty/blank columns?
July 5, 2011 at 4:51 pm
denis.gendera (7/5/2011)
Hi thx for the quick feedback, the solution works for values that are 'NULL'. How about empty/blank columns?
Quick and dirty? Use a NULLIF function around each column in the Coalesce.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 5, 2011 at 5:15 pm
That did the trick, I like these quick and dirty solutions 🙂
Thx again for all the help appreciated.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply