Hi All,
I'm a Geomechanics student and newcomer to SQL. I'm currently working on a project and using SQL as my data analysis tool. Below are some of the sample results from instrument reading. I would really like to have your assistance to create a summary from the data (table) provided below. Further info about summary table is also provided.
=========
create table dbo.t1 (
[PK] varchar (20),
[date] datetime,
[Location] varchar(20),
[Weigth] float,
[Readings] float,
[Days_inc] float
)
go
set nocount on
insert into dbo.t1 values( ' DAFN25S ' , ' 1/1/2004 12:00:00 AM ' , ' A-23 ' , ' 22.6 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' DANS37J ' , ' 1/2/2004 12:00:00 AM ' , ' A-23 ' , ' 27.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' FKFH34U ' , ' 1/3/2004 12:00:00 AM ' , ' A-23 ' , ' 29 ' , ' 0.0083 ' , '3' )
insert into dbo.t1 values( ' DRCY55O ' , ' 1/16/2004 12:00:00 AM ' , ' A-23 ' , ' 23.2 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' DRKR13T ' , ' 1/17/2004 12:00:00 AM ' , ' A-23 ' , ' 31.9 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' TRKR54Y ' , ' 1/18/2004 12:00:00 AM ' , ' A-23 ' , ' 29.6 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' RTYT24T ' , ' 1/19/2004 12:00:00 AM ' , ' A-23 ' , ' 23.8 ' , ' 0.0244 ' , '4' )
insert into dbo.t1 values( ' RKCB26T ' , ' 1/24/2004 12:00:00 AM ' , ' A-23 ' , ' 18.3 ' , ' -0.0061 ' , '1' )
insert into dbo.t1 values( ' DKRH51R ' , ' 1/28/2004 12:00:00 AM ' , ' A-23 ' , ' 10.8 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' DRGT77U ' , ' 1/29/2004 12:00:00 AM ' , ' A-23 ' , ' 25.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' USNT80T ' , ' 1/30/2004 12:00:00 AM ' , ' A-23 ' , ' 27.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' TDNF71Q ' , ' 1/31/2004 12:00:00 AM ' , ' A-23 ' , ' 26.9 ' , ' 0.0014 ' , '4' )
insert into dbo.t1 values( ' DKAH23Y ' , ' 1/2/2004 12:00:00 AM ' , ' B-60 ' , ' 15.4 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' DAKD44T ' , ' 1/3/2004 12:00:00 AM ' , ' B-60 ' , ' 21.7 ' , ' 0.0229 ' , '2' )
insert into dbo.t1 values( ' PDNR56Y ' , ' 1/4/2004 12:00:00 AM ' , ' B-60 ' , ' 19.2 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' DBTG87K ' , ' 1/5/2004 12:00:00 AM ' , ' B-60 ' , ' 17.6 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' KDHR43I ' , ' 1/6/2004 12:00:00 AM ' , ' B-60 ' , ' 19.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' QJKD00F ' , ' 1/7/2004 12:00:00 AM ' , ' B-60 ' , ' 20.5 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' KKKR83I ' , ' 1/8/2004 12:00:00 AM ' , ' B-60 ' , ' 18.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' KDDR90R ' , ' 1/9/2004 12:00:00 AM ' , ' B-60 ' , ' 20.5 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' TTRC87R ' , ' 1/10/2004 12:00:00 AM ' , ' B-60 ' , ' 19.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' RDTD67E ' , ' 1/11/2004 12:00:00 AM ' , ' B-60 ' , ' 19.3 ' , ' 0 ' , '0' )
insert into dbo.t1 values( ' ZDHR44I ' , ' 1/31/2004 12:00:00 AM ' , ' B-60 ' , ' 24.6 ' , ' 0.0428 ' , '9' )
insert into dbo.t1 values( ' SRHT23T ' , ' 2/1/2004 12:00:00 AM ' , ' B-60 ' , ' 29 ' , ' 0.0413 ' , '1' )
set nocount off
go
=============
Summary table:
PK Location Sum Weight reading var. FKFH34U A-23 78.9 0.0083 RTYT24T A-23 108.5 0.0161 RKCB26T A-23 18.3 -0.0305 TDNF71Q A-23 90.3 0.0075 DAKD44T B-60 37.1 0.0229 ZDHR44I B-60 178.6 0.0199 SRHT23T B-60 29 -0.0015
Note:
1.'Sum weight' is the accumulation of weight between two readings. The weight of the previous reading is not included in the calculation. Example: the 'sum weight' of location A-23 between the reading on 1/24/2004 to 1/31/2004 is (10.8+25.3+27.3+26.9)= 90.3
2.'Reading var.' is the substraction value of the most recent reading value to the previous date reading values. Example: the 'reading var' for location A-23, between the latest reading on 01/31/2004 and previous reading on 01/24/2004 is 0.0075. If there is no value on previous reading, then the latest date reading is substracted by zero (0). The substraction can only be done on the records of similar reading location (A-23 or B-60, etc).
3. 'Location' is obtained from the most recent date of reading, which is used for the calculation.
4. 'PK' is obtained similarly than procedure at point 3.
Thank you in advance.
WP
Really complex one – here the query for SQL Server 2000/2005.
If you use SQL Server 2005 you can eliminate this complex query much easier.. let me try that one also J
Code Snippet
Select
datedata.PK,
datedata.date,
datedata.Location,
Sum([Weigth]) [Weigth],
max(datedata.[Readings] - isnull(datedata2.[Readings],0))
From
#data data
Join
(
select
[PK],
[Location],
(select min([date]) [date]
from#data as Sub
Where [Days_inc] <> 0
and sub.[date]>=main.[date] and sub.[Location]=Main.[Location]) groupeddate
from
#data as Main
) as Grouped On data.PK = Grouped.PK
Join
(
select
[PK],
[Location],
(select max([date]) [date]
from#data as Sub
Where [Days_inc] <> 0
and sub.[date]<main.[date] and sub.[Location]=Main.[Location]) groupeddate
from
#data as Main
) as Grouped2 On data.PK = Grouped2.PK
Join
(
select
[PK],
[date],
[Location],
[Readings]
from
#data
Where
[Days_inc] <> 0
) as datedata on Grouped.groupeddate = datedata.date andGrouped.[Location] = datedata.[Location]
Left Outer Join
(
select
[PK],
[date],
[Location],
[Readings]
from
#data
Where
[Days_inc] <> 0
) as datedata2 on Grouped2.groupeddate = datedata2.date andGrouped2.[Location] = datedata2.[Location]
Group By
datedata.PK,
datedata.Location,
datedata.date
Order By 3,2
|||
Code Snippet
create table #temp (
ID int identity(1,1),
[PK] varchar (20),
[date] datetime,
[Location] varchar(20),
[Weigth] float,
[Readings] float,
[Days_inc] float
)
Insert into #temp([PK],[date],[Location],[Weigth],[Readings],[Days_inc])
select * from t1 order by Location,date
select PK,
Location,
max(isnull(weigth,0)) + isnull((select sum(isnull(weigth,0)) from #temp where #temp.ID < TableAns.ID and #temp.ID >= TableAns.ID - max(TableAns.Days_inc - 1) and #temp.Location = TableAns.Location),0) as SumWeight,
max(readings) -isnull((select sum(readings) from #temp where #temp.ID < TableAns.ID and #temp.ID >= TableAns.ID - max(TableAns.Days_inc - 1) and #temp.Location = TableAns.Location),0) as readingvar
from #temp TableAns
where ID between ID - (Days_inc) and ID and Days_inc != 0
group by Location,PK ,date,ID
order by Location,date
Go
drop table #temp
Go
|||
Hi Vidhura,
I have taken your query and run it under SQL 2005. However, the result is not agreeing with the expected summary table. I have multiple answers for one calculation. Could you please suggest alternative solution? Thanks. WP
PK Location SumWeight Reading var FKFH34U A-23 83.6 0.0083 FKFH34U A-23 85.3 0 RTYT24T A-23 114.9 0.0244 RTYT24T A-23 106.8 0 RKCB26T A-23 18.3 -0.0061 RKCB26T A-23 18.3 -0.0061 TDNF71Q A-23 106.8 0.0014 TDNF71Q A-23 108.4 0 DAKD44T B-60 37.1 0.0229 DAKD44T B-60 43.4 0 ZDHR44I B-60 179.4 0.0428 ZDHR44I B-60 185.7 0 SRHT23T B-60 29 0.0413 SRHT23T B-60 29 0.0413
|||Hi Manivannan,
Wow...Actually, your solution is more complex than what I thought.... 
However your solution is almost correct. The only problem is on the calculation for 'sum weight'. I don't know why the result is way too big. As presented below. Can you suggest for the alternative solution?
Anyway, I'm actually using SQL server 2005, so I'd be very grateful if you can you suggest me for a simpler solution, which hopefully, I could understand on it. ..
PK Location Sum Weight reading var. FKFH34U 00:00.0 A-23 1262.4 0.0083 RTYT24T 00:00.0 A-23 3472 0.0161 RKCB26T 00:00.0 A-23 585.6 -0.0305 TDNF71Q 00:00.0 A-23 2889.6 0.0075 DAKD44T 00:00.0 B-60 593.6 0.0229 ZDHR44I 00:00.0 B-60 5715.2 0.0199 SRHT23T 00:00.0 B-60 928 -0.0015
Cheers,
WP
|||
For SQL Server 2005,
;With Ordered
as
(
Select *,Row_number() Over(Partition By Location order By Location,date) RID from t1
)
, Parted
as
(
Select *,Row_Number() Over(Partition By Location Order By RId) PartId from Ordered Where Days_inc <> 0
)
, Boundry
as
(
select A.Pk BottomPK, B.Pk TopPk, A.Rid BottomRId, Isnull(B.Rid,0) TopRId,A.Location,A.Readings BottomReadings,Isnull(B.Readings,0) TopReadings
from Parted A
Left Outer Join Parted B on A.PartId = B.PartId+1
and A.Location=B.Location
)
, Result
as
(
Select BottomPK as PK, BottomRId, A.Location ,Sum(A.Weigth) as Weigth,max(Round(BottomReadings-TopReadings,4)) as [reading var.]
from Ordered A Join Boundry B
On A.RId > B.TopRId and A.Rid<=B.BottomRId and A.location=b.location
Group By
BottomPK, BottomRId, A.Location
)
Select PK, Location, Weigth, [reading var.]
from Result
Order By Location,BottomRId
|||Yes. The solution is complex on SQL Server 2000.
The big number bcs of the rounding issue I fixed on the above query (using Common Table Expression)
How it works?
Step 1:
First Order the Row and mark the Row Number => Ordered
Step 2:
Order the Ordered Rows where Days_inc<>0 => Parted
Step 3:
Using Parted we can get the Top & Bottom Boundary , it is very much useful to find the data between the Row Number for each part(group) & easy to calculate the reading variance => Boundry
Step 4:
Using Boundry join the Ordered data to get the desired calculations => Result
Step 5:
Order the Result as we need.
Let me know if you have any doubts.
|||Hi Manivannan,
The solution you provided is much simpler now and more understandable. However, the results of the query (particularly for ‘sum weigth’) still do not match with the expected summary table. Also, it creates double results for the same calculation - shown below. I guess, it may be affected by the in accuracy in parted procedure. What do you think?
PK Location Sum Weight reading var. FKFH34U A-23 128.8 0.0083
|||Hi Pratama,
Do you have more records than you given?
I tried the queries given by Manivannan and Me.It works fine in sql 2005 with ur given data .
No comments:
Post a Comment