Hello All,
Ok, I had an excellent answer when I put up my previous post. It solved my problem but a new problem arose. I need your expertise once again.
I'm working in SQL Server 2005 (sp2) environment. I have a table containing a tires life, beginning from when it was fitted, stored and reused until it was scrapped as shown below;
H means fitted new
D means is running
E means Store
C means scrapped
From excel, difference between fitted new date and scrapped date is 10270 hours (approx) and
Actual running time is 8891 hours (when not in storage).
So, i need to query this table to produce tireID, Count of Es from the "table_from" column and actual running time as shown below.
If you need to know, Its about a research Im doing about the effect of tire rotation on tire life.
Thanks in advance.
Try this one:
select
TireId,
Count(taken_from),
datediff(d, (select top 1 [date] from Tires where TireId = d.TireId order by [date]), (select top 1 [date] from Tires where TireId = d.TireId order by [date] desc))
from Tires d
group by d.TireId
It is not very efficient, but it works. I named the table [Tires].
|||Hamdawed,
Please, next time post DDL statements (table definition, including constraints and indexes, and insert statements) so we do not have to take our time reproducing your environment. The help should be in both ways. Try:
Code Snippet
create table dbo.t1 (
TireID int not null,
axle int not null,
position int not null,
taken_from char(1) not null,
taken_to char(1) not null,
date datetime not null
)
go
set nocount on
insert into dbo.t1 values(6006, 1, 2, 'H', 'D', '10/6/2003 10:45')
insert into dbo.t1 values(6006, 1, 2, 'D', 'D', '10/8/2003 8:30')
insert into dbo.t1 values(6006, 1, 2, 'D', 'D', '10/30/2003 13:30')
insert into dbo.t1 values(6006, 1, 2, 'D', 'D', '11/28/2003 8:30')
insert into dbo.t1 values(6006, 1, 2, 'D', 'D', '12/29/2003 11:30')
insert into dbo.t1 values(6006, 1, 2, 'D', 'E', '1/27/2004 8:30')
insert into dbo.t1 values(6006, 2, 6, 'E', 'D', '2/14/2004 11:45')
insert into dbo.t1 values(6006, 2, 6, 'D', 'D', '2/28/2004 8:30')
insert into dbo.t1 values(6006, 2, 6, 'D', 'D', '3/30/2004 8:30')
insert into dbo.t1 values(6006, 2, 6, 'D', 'D', '4/28/2004 8:30')
insert into dbo.t1 values(6006, 2, 6, 'D', 'E', '5/7/2004 8:30')
insert into dbo.t1 values(6006, 2, 5, 'E', 'D', '5/12/2004 10:45')
insert into dbo.t1 values(6006, 2, 5, 'D', 'D', '5/28/2004 8:30')
insert into dbo.t1 values(6006, 2, 5, 'D', 'D', '5/28/2004 11:00')
insert into dbo.t1 values(6006, 2, 5, 'D', 'D', '6/30/2004 8:30')
insert into dbo.t1 values(6006, 2, 5, 'D', 'D', '7/6/2004 8:30')
insert into dbo.t1 values(6006, 2, 5, 'D', 'D', '7/28/2004 8:30')
insert into dbo.t1 values(6006, 2, 5, 'D', 'E', '8/3/2004 8:40')
insert into dbo.t1 values(6006, 2, 4, 'E', 'D', '8/6/2004 10:45')
insert into dbo.t1 values(6006, 2, 4, 'D', 'D', '8/28/2004 8:30')
insert into dbo.t1 values(6006, 2, 4, 'D', 'D', '9/4/2004 8:30')
insert into dbo.t1 values(6006, 2, 4, 'D', 'E', '9/9/2004 8:30')
insert into dbo.t1 values(6006, 2, 5, 'E', 'D', '10/4/2004 10:45')
insert into dbo.t1 values(6006, 2, 5, 'D', 'E', '10/29/2004 8:30')
insert into dbo.t1 values(6006, 2, 5, 'E', 'D', '11/4/2004 9:45')
insert into dbo.t1 values(6006, 2, 5, 'D', 'D', '11/22/2004 8:30')
insert into dbo.t1 values(6006, 2, 5, 'D', 'D', '11/29/2004 8:30')
insert into dbo.t1 values(6006, 2, 5, 'D', 'D', '11/29/2004 11:00')
insert into dbo.t1 values(6006, 2, 5, 'D', 'C', '12/7/2004 8:30')
set nocount off
go
;with cte_1
as
(
select
TireID,
axle,
position,
taken_from,
taken_to,
date,
row_number() over(order by date) as rn
from
dbo.t1
),
cte_2
as
(
select
a.TireID,
a.axle,
a.position,
a.taken_from,
a.taken_to,
a.date,
isnull(datediff(minute, b.date, a.date), 0) as minutes
from
cte_1 as a
left join
cte_1 as b
on a.TireID = b.TireID
and a.rn = b.rn + 1
)
select
TireID,
sum(case when taken_from = 'E' then 1 else 0 end) as [Count taken_from E's],
ceiling(sum(case when taken_from = 'E' then 0 else minutes end) / 60.00) as [Running time]
from
cte_2
group by
TireID
go
drop table dbo.t1
go
AMB
|||Correction,
Change the row_number() function to:
row_number() over(partition by TireID order by date) as rn
I am sure you have more tires in that table, don't you?
AMB
|||Hi,
Sorry for my inefficiency in setting up this help request. I promise to make things much clearer the next time.
Well, your code works great when I copy and paste but I tried breaking it down to fit the real table in the database im using and it produces errors.
The example i gave was just a subset of the real table and this table includes information about 130000 tires.
So, my question is: I have this table in a database in SQL Server 2005 just like the example I gave but with more tire information. Im not creating any tables but just querying a table (using views) in a certain database.Which part of your code im i going to use, analyse?
I know the "create" part is not necessary in my environment but when I eliminate it I get errors.
Also, theres an error that "OVER" clause is not supported by SQL Server 2005. Why is that?
Thanks
|||Yeah, i got like 130000 tires in that table in a database. Sorry for the troubles.
The code you produced works great but I need to fit that to the real table in the database.
I wish I can put up the whole table up or even share my computer screen to see the real problem.
Im working on the correction. More suggestions as to how to do the modification to your code to fit the real table is welcome.
Thanks
|||Can you check the compatibility level of that db?
exec sp_cmptlevel your_db_name
go
If should be 90 in order to use new features from SQL Server 2005.
AMB
|||I received the following error when I checked the compatibility level;
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'sp_cmptlevel'.
I have been able to fit your code to my whole table and it works but only when i copy paste in 'new query'. When I try to use it in views it produces the error that 'OVER CONSTRUCT IS NOT SUPPORTED'.
Thanks
|||Sorry, I posted the incorrect name for that sp. I meant sp_dbcmptlevel.
Can you post what you are trying to do?
Example:
Code Snippet
use northwind
go
create view dbo.v1
as
with cte
as
(
select
CustomerID, OrderID, OrderDate,
row_number() over(partition by CustomerID order by OrderDate, OrderID) as rn
from
dbo.orders
)
select CustomerID, OrderID, OrderDate, rn
from cte
go
select CustomerID, OrderID, OrderDate, rn
from dbo.v1
order by CustomerID, rn
go
drop view dbo.v1
go
AMB
|||Hi,
The current compatibility level is 90 when I checked.
The view was created when i applied your example. But, no diagrams were created and it still produces that "OVER" sql construct is not supported.
I will like to post what i want to do but how?
sorry for my naiveness.
|||Ok,
Basically, what I need to see is a diagram of the table with joins in the "diagrams" pane when working in views.
The code you created for me gets the information I want but does not produce a diagram because of the OVER thing.
The audience Im going to present this to understand basic ER diagrams and links and not SQL codes.
Please, I will appreciate it very much if an alternate query can be written without the OVER construct. If not, never mind. You have been very helpful.
Thanks
|||Ok, now I understand your problem. You are trying to create the query using "Query Designer". Well, you can create the view using T-SQL and use a third party tool, like Visio, to create the ER diagram. The "Query Designer" seems not to allow OVER() clause in the construction of a query, so better to learn how to do it using T-SQL.
AMB
|||Yeah, right on. I should have used the lingo ''Query designer''. Im learning. Will I be right if i assume there is no other way to create a query in the "Query designer" to get me the same information.
Thank you for your time and patience
|||Sorry, but I do not use "Query Designer" much, so it is little what I can tell you about its features.
AMB
No comments:
Post a Comment