November 9, 2015 at 12:24 pm
Hey All,
I was hoping one of you guys would be able to help with an SQL Query problem if you can spare a minute,...
(I'm VERY new to SQL2012, by no means a SQL expert, or even novice for that matter,...)
I'm working with a SQL Server 2012 DB
The table of interest has a few hundred million production records
The table has 8 columns
The Propnum column is a unique identifier and is per well.
The P_DATE column is the date of the monthly production record (in order)
The OIL column is the oil production for the month (this will be left alone)
The GAS, WATER and DAYSON columns can be ignored
The WELLS column is the criteria I would like to trigger a logic flag on.
The OIL_UA column is a clone of the OIL column (until altered) This is the column to be effected by the trigger
Essentially what I would like to accomplish is, after the first instance(in time) (per well (propnum)) of "WELLS" greater than "1" the OIL_UA column is made NULL for the subsequent production records for that "PROPNUM".
The first instance aspect is critical, sometimes the "WELLS" column will go beyond 1, then come back, I want to trim it at the first instance (in time) of greater than "1", even if it reverts back to "1"
All other columns should remain unaffected, the row/record cannot be deleted, just the "OIL_UA" made NULL.
Again, any help is greatly appreciated!
Thanks!
-E
November 9, 2015 at 4:24 pm
Hi,
Maybe you can try with a computed column
REF: https://msdn.microsoft.com/en-AU/library/ms188300.aspx
CREATE FUNCTION dbo.getFirstP_DatePropnum(@date date, @Propnum INT)
RETURNS BIT
AS
BEGIN
RETURN
CASE
WHEN @date = (
SELECT MIN(tt.P_DATE)
FROM dbo.TestTab tt
WHERE tt.Propnum = @Propnum
GROUP BY tt.Propnum,tt.WELLS
)
THEN 1
END
END
GO
--example table (add additional constraints ..)
CREATE TABLE dbo.TestTab ( Propnum INT
,P_DATE date
,OIL INT
,WELLS INT
,OIL_UA AS dbo.getFirstP_Date(P_DATE,Propnum)
)
GO
INSERT INTO dbo.TestTab(Propnum, P_DATE, OIL) SELECT 1,'20150101',1
INSERT INTO dbo.TestTab(Propnum, P_DATE, OIL) SELECT 1,'20150103',3
INSERT INTO dbo.TestTab(Propnum, P_DATE, OIL) SELECT 1,'20150104',2
INSERT INTO dbo.TestTab(Propnum, P_DATE, OIL) SELECT 1,'20150105',1
INSERT INTO dbo.TestTab(Propnum, P_DATE, OIL) SELECT 2,'20150105',1
INSERT INTO dbo.TestTab(Propnum, P_DATE, OIL) SELECT 2,'20150106',3
SELECT * FROM dbo.TestTab tes
This is just an idea(if I got it right 🙂 not the solution. There are other concerns related to the performances.
Regards
Dean
D.Mincic
😀
MCTS Sql Server 2008, Database Development
November 9, 2015 at 5:03 pm
Thank you for the reply, I appreciate you taking the time, I'll have to struggle through a bit trying to figure out your purposed solution, SQL beyond basic joins and select/update/delete statements is way outside of my comfort zone.
If I can figure it out I'll let you know!
November 9, 2015 at 5:17 pm
Hi,
The function in the table definition is a calculated column (you can google it 🙂
the function returns 1 if the P_DATE for the Propnum is minimum .. the first inserted date for the propnum/Well
and NULL if not ( if I understood the question correctly 🙂
D.Mincic
😀
MCTS Sql Server 2008, Database Development
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply