Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
mrtweaver
Yak Posting Veteran
67 Posts |
 Posted - 2008-11-13 : 21:19:57
|
| Sorry to sound so vague but I am just learning SQL and really need some help. I am using SQL 2005 Microsoft. What I am looking to do is pull data from a table with certain condition. I will try to be as clear as possible.Table has the following header:machine (int), start (date/time), end (date/time), mech1 (int), mech2 (int), mechpage (int), duration (int), and t_stamp (date/time) There are other but they are not in this request.The data in it might look somehting like:1 <date> <date> 0 0 0 0 0 <date>1 0 0 0 1 5 1 0 0 0 1 71 0 0 0 1 161 0 0 0 1 51 123 0 0 1 141 123 0 0 1 231 123 0 0 1 141 0 0 0 0 51 0 0 0 0 21This is a small portion but it is the same for all 49 machine we have online and the all go into the same table. So the mechpage can come on multiple times per shift. And any mechanic can arrive to answer the page.What I would like to try to do is run a query and I realize it will probably require additional views but am unsure. So please when answering be as specific as possible.Anyway the output from the query would do the following:1. It would show the date/time from start column where the mechpage first comes on.2. It would show the mechanic number for the mechanic who answer the page and it would also pick up the start time from when he/she arrived.3. It would show the date/time from the start when the mechanic leaves. When the mechanic leaves the mechpage and mech1 value both goto 0.4. In the duration time it would sum up the values of a: from the time the indication came on till the time the mechanic arrived and b: from the time the mechanic arrived till the time the mechanic left.So the final output view/table might have the following headers:machine (int), time page (date/time), time arrive (date/time), time left (date/time), mech, page duration, work durationThe data might look like this:1 10/10/2008 6:15:20am 6:30:10am 6:35:02am 123 900 600note: because the duration fields are sums they are in seconds.1 10/10/2008 9:15:02am etc.............14 10/10/2008 7:00:00am etc.....Hopefully you can understand this and I have made it clear. I dont even know if this is possible. Like I said I am just getting started in SQL and am learning and i know this is far more advanced then I can do by myself at this time. So any help greatly appreciated. |
|
|
darkdusky
Aged Yak Warrior
591 Posts |
|
|
mrtweaver
Yak Posting Veteran
67 Posts |
Posted - 2008-11-14 : 07:37:25
|
I know the basic format for SQL query. I have been successful with basic general queries. THings like update info set piece = this where certain condition apply, and select these columns from this table using this criteria. So the basics I do have some knowledge on. It is being able to select certain infromation from an entire table where a change has occured. What I have so far for my query that I am working on to do this task for which I posted help is:select machine, start, stop, mech1, mech2, shift, t_stamp from livedata where mechpage = 1.This of course just returns all the records where mechpage = 1. What I want to do is select the same information but just for that one row where the mechpage changes from 0 to 1 and then again from 1 to 0. I also want to select the row of information where the mech1 or the mech2 changes from 0 to some value. When the query is ran it will have many records this I am sure of. I am thinking that for each of these functions listed here I would need a view then I could take and using join or union could merge the data back together to form one table containing the information I require as my output. However I am not sure how to go about doing this, which perusing this form I came across a post that said about using TRIGGER. I am going to start looking into that and seeing what it uses and how it works. But I would appreciate some guidance. THanks and have a great day.quote:
Originally posted by darkdusky
Basic format of query:Select * from Name_of_TableWhere column1='' OR (column2>'' and column2<'')Basic intro to SQL queries:http://www.w3schools.com/sql/default.asp
|
 |
|
|
mrtweaver
Yak Posting Veteran
67 Posts |
Posted - 2008-11-15 : 18:25:27
|
| A friend gave me an idea that seems easy but he gave the code in MySQL and I know that it is slightly different than Microsoft SQL. So what I want to do is write down some of the particulars here and see if anyone can help convert it. This way I might get it to work.First off he had mentioned about using indexes. By default MySql used auto index and each record gets its own unique index. Does Microsoft SQL 2005 have this auto enabled?Next he say to make another table/view containing the exact same data as the table I am working with. Then using the index and a join command you can get the values of where the data changes from 0 to 1 and from 1 to a 0.using a query like:select * from table1 join table2 on table1_IDX != to table2_idx +1So to do this how would I address it in Microsoft SQL. I know in MySQL they use the _IDX for the index column. But I dont know much yet about the Microsoft version. Would I first creat the Index for the table and call it some name then on the second table have the same index defined and would then instead of calling like I do about I would call out the name of the index? Is that how Microsoft would work?Also I have not read to far yet but can you create an index on a view?Thanks for all assistance and have a great day. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
|
|
|
|
|
SQL query help requested