January 23, 2014 at 9:39 am
I am trying to add a field to a view that actually contains data from the record previous to the one I am selecting here:
SELECT dbo.abc.TYPE, dbo.abc.BASE, dbo.abc.LOT, dbo.abc.SPLIT,
dbo.abc.SUB, dbo.abc.SEQ, dbo.abc.RESOURCE, dbo.abc.RUN, dbo.abc.RUN_TYPE,
dbo.abc.START_QTY, dbo.abc.END_QTY, dbo.abc.COMPLETED_QTY, dbo.abc.DEVIATED_QTY,
dbo.DEF.PART_ID, dbo.GHI.AUTO_REPORTING, dbo.abc.SETUP_HRS
FROM dbo.abc INNER JOIN
dbo.def ON dbo.abc.WORKORDER_TYPE = dbo.def.TYPE AND
dbo.abc.BASE = dbo.def.BASE AND dbo.abc.LOT = dbo.def.LOT AND
dbo.abc.SPLIT = dbo.def.SPLIT AND dbo.abc.SUB = dbo.def.SUB INNER JOIN
dbo.ghi ON dbo.abc.RESOURCE = dbo.ghi.ID
I'd like to add the setup hours from the previous operation. Is that possible? If so, please let me know if I can provide any other information. I realize this is pretty vague but I want to make sure it is possible before I inundate with a bunch of information. Thank you in advance for your assistance.
January 23, 2014 at 10:22 am
Don't worry about posting too much data to help us understand - that's something that rarely happens here.
You need to post some table definitions, test data and expected in order to save anyone trying to help you an awful lot of work.
A good article on how to do this is http://www.sqlservercentral.com/articles/Best+Practices/61537/.
January 23, 2014 at 10:23 am
aberndt (1/23/2014)
I am trying to add a field to a view that actually contains data from the record previous to the one I am selecting here:SELECT dbo.abc.TYPE, dbo.abc.BASE, dbo.abc.LOT, dbo.abc.SPLIT,
dbo.abc.SUB, dbo.abc.SEQ, dbo.abc.RESOURCE, dbo.abc.RUN, dbo.abc.RUN_TYPE,
dbo.abc.START_QTY, dbo.abc.END_QTY, dbo.abc.COMPLETED_QTY, dbo.abc.DEVIATED_QTY,
dbo.DEF.PART_ID, dbo.GHI.AUTO_REPORTING, dbo.abc.SETUP_HRS
FROM dbo.abc INNER JOIN
dbo.def ON dbo.abc.WORKORDER_TYPE = dbo.def.TYPE AND
dbo.abc.BASE = dbo.def.BASE AND dbo.abc.LOT = dbo.def.LOT AND
dbo.abc.SPLIT = dbo.def.SPLIT AND dbo.abc.SUB = dbo.def.SUB INNER JOIN
dbo.ghi ON dbo.abc.RESOURCE = dbo.ghi.ID
I'd like to add the setup hours from the previous operation. Is that possible? If so, please let me know if I can provide any other information. I realize this is pretty vague but I want to make sure it is possible before I inundate with a bunch of information. Thank you in advance for your assistance.
how do you define the setup hours from the previous operation? is it a previous query, or is it a different row in your "abc" table?
most likely , you have to join the table again, with an alias, and on a slightly different criteria (T1.ID -1 = abc.ID? i dunno)
your query reformatted for readability
SELECT
dbo.abc.TYPE,
dbo.abc.BASE,
dbo.abc.LOT,
dbo.abc.SPLIT,
dbo.abc.SUB,
dbo.abc.SEQ,
dbo.abc.RESOURCE,
dbo.abc.RUN,
dbo.abc.RUN_TYPE,
dbo.abc.START_QTY,
dbo.abc.END_QTY,
dbo.abc.COMPLETED_QTY,
dbo.abc.DEVIATED_QTY,
dbo.DEF.PART_ID,
dbo.GHI.AUTO_REPORTING,
dbo.abc.SETUP_HRS
FROM dbo.abc
INNER JOIN dbo.def
ON dbo.abc.WORKORDER_TYPE = dbo.def.TYPE
AND dbo.abc.BASE = dbo.def.BASE
AND dbo.abc.LOT = dbo.def.LOT
AND dbo.abc.SPLIT = dbo.def.SPLIT
AND dbo.abc.SUB = dbo.def.SUB
INNER JOIN dbo.ghi
ON dbo.abc.RESOURCE = dbo.ghi.ID
Lowell
January 23, 2014 at 10:34 am
I appreciate both of your help. It is the row above in both the abc table and the view. I am reviewing the article that Brain Donor referenced and hope to provide more data shortly.
January 23, 2014 at 10:40 am
aberndt (1/23/2014)
I appreciate both of your help. It is the row above in both the abc table and the view. I am reviewing the article that Brain Donor referenced and hope to provide more data shortly.
perfect; now since SQL server doesn't treat the "order" of a table as anything important, you have to define how to define how a row is related to another via code;
if you had an identity() column, it would be easy. i see a column nname "SEQ", which implies a sequence, but you know your data better.
here's a first guess.
SELECT
T1.TYPE,
T1.BASE,
T1.LOT,
T1.SPLIT,
T1.SUB,
T1.SEQ,
T1.RESOURCE,
T1.RUN,
T1.RUN_TYPE,
T1.START_QTY,
T1.END_QTY,
T1.COMPLETED_QTY,
T1.DEVIATED_QTY,
dbo.DEF.PART_ID,
dbo.GHI.AUTO_REPORTING,
T2.SETUP_HRS
FROM dbo.abc T1
LEFT OUTER JOIN dbo.abc T2
ON T1.TYPE = T2.TYPE
AND T1.BASE = T2.BASE
AND T1.LOT = T2.LOT
AND T1.SPLIT = T2.SPLIT
AND T1.SUB = T2.SUB
AND T1.SEQ + 1 = T2.SEQ
INNER JOIN dbo.def
ON T1.WORKORDER_TYPE = dbo.def.TYPE
AND T1.BASE = dbo.def.BASE
AND T1.LOT = dbo.def.LOT
AND T1.SPLIT = dbo.def.SPLIT
AND T1.SUB = dbo.def.SUB
INNER JOIN dbo.ghi
ON T1.RESOURCE = dbo.ghi.ID
Lowell
January 23, 2014 at 11:03 am
Lowell - You are correct, the SEQ is a number and the information that I want is associated with the SEQ # directly before that operation. I attached a .txt example for you. I'd like the information from seq_no 20 but I would also like to include the setup_hrs from seq_no 10. The sequence numbers are not always the same but the operation that I want to get the setup_hrs from will always be the operation immediately preceeding it. Since this is a view, I could bring in the rowid field if that would make this easier. I can't guarantee that it will always be the rowid immediately preceeding but most of the time, that should be correct.
January 23, 2014 at 12:07 pm
aberndt (1/23/2014)
Lowell - You are correct, the SEQ is a number and the information that I want is associated with the SEQ # directly before that operation. I attached a .txt example for you. I'd like the information from seq_no 20 but I would also like to include the setup_hrs from seq_no 10. The sequence numbers are not always the same but the operation that I want to get the setup_hrs from will always be the operation immediately preceeding it. Since this is a view, I could bring in the rowid field if that would make this easier. I can't guarantee that it will always be the rowid immediately preceeding but most of the time, that should be correct.
well you've got to remember that SQL server is not like excel; sql has no concept of previous row/next row. unless you explicitly say ORDER BY rowid, then SQl server is free to return the data in the method it determines is fastest at the given momemnt.
querying the view and seeing the data in the same order is only a coincidence as far as sql server goes; things like indexing, reindexing, parallellism, inserts and updates and lots of other things can affect the perceived order unless you have an explicit order by in your query.
so for a row to have "meaning" compared to another row, it's got to be by a data relationship. if your view has a rowid, then that's how you'd relate the rows, you bet!
by usign the relationship to join, you get the "right" data regardless of the order of the rows when they finally get displayed.
Lowell
January 23, 2014 at 12:23 pm
Thank you...I'm going to have to review my data further and do some testing to see if row id will work. I hope so. I'll post back in a few days to let you know. Thank you so much for your advice.
January 23, 2014 at 5:19 pm
aberndt (1/23/2014)
Thank you...I'm going to have to review my data further and do some testing to see if row id will work. I hope so. I'll post back in a few days to let you know. Thank you so much for your advice.
I know you posted in the SQL 2008 forum, but since you've done so in the newbie section, I'm wondering if perhaps you are using SQL 2012? There's a very simple method in that version to address this (LAG).
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 29, 2014 at 7:35 am
Hi Dwain - Thank you for the advice, but no, I am on 2008.
January 31, 2014 at 7:25 am
Thank you all for your input but after further review, I cannot use the rowid field as some of the operations were added later so the numbers are not in sequential order. We are going to see if we can add the information somewhere else. I will save this information should I have a need for this functionality again. Thanks again for all of your help!!!
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply