How to get the rows before every date within a mon

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.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to get the rows before every date within a mon

Author

Topic

marcoviy
Starting Member

6 Posts

Posted - 2011-09-09 : 16:59:54

Hello Folks,

Could you please help me?

I have the following table. I also have the following query which gets the last reading before a given date (inclussive)

DECLARE

@wellTest TABLE (ID INT, WELL_ID INT, START_AT DATE, NET_OIL INT)

INSERT INTO @wellTest

SELECT

1,1,'9/1/2011',3

UNION

ALL SELECT 2,2,'9/1/2011',8

UNION

ALL SELECT 3,3,'9/1/2011',1

UNION

ALL SELECT 4,1,'9/18/2011',5

UNION

ALL SELECT 5,2,'9/18/2011',13

UNION

ALL SELECT 6,3,'9/10/2011',2

UNION

ALL SELECT 7,1,'9/21/2011',1

UNION

ALL SELECT 8,2,'9/22/2011',7

UNION

ALL SELECT 9,3,'9/22/2011',5

UNION

ALL SELECT 10,1,'9/24/2011',2

UNION

ALL SELECT 11,2,'9/25/2011',5

UNION

ALL SELECT 12,3,'9/24/2011',2

select * from @wellTest

ID WELL_ID START_AT NET_OIL
1 1 2011-06-05 3
2 2 2011-06-06 8
3 3 2011-06-07 1
4 1 2011-06-18 5
5 2 2011-06-18 13
6 3 2011-06-10 2
7 1 2011-06-21 1
8 2 2011-06-22 7
9 3 2011-06-22 5
10 1 2011-06-24 2
11 2 2011-06-25 5
12 3 2011-06-24 2

The query is:

SELECT ID, WELL_ID, START_AT, NET_OIL
FROM @wellTest wt_1
WHERE wt_1.ID IN
(SELECT ID
FROM @wellTest AS wt_2
WHERE (START_AT =
(SELECT MAX(START_AT) AS Expr1
FROM @wellTest AS wt_3
WHERE (START_AT <= @fromDate)
AND (WELL_ID = wt_2.WELL_ID))))

The result this query gives is:

DECLARE@fromDate DATETIME
SET @fromDate = '9/24/2011'

ID WELL_ID START_AT NET_OIL
8 1 2011-09-22 7
10 2 2011-09-24 2
12 3 2011-09-24 2

What I need now is get the last reading not before a given date, but before every day within the month. That is to say as if the given date would be varying along the month day after day i.e.

ID WELL_ID START_AT NET_OIL
1 1 2011-09-01 3 For day 1
2 2 2011-09-01 8 For day 1
3 3 2011-09-01 1 For day 1
1 1 2011-09-01 3 For day 2
2 2 2011-09-01 8 For day 2
3 3 2011-09-01 1 For day 2
… … … …
4 1 2011-09-18 5 For day 18
5 2 2011-09-18 13 For day 18
6 3 2011-09-10 2 For day 18
… … … …
10 1 2011-09-24 2 For day 30
11 2 2011-09-25 5 For day 30
12 3 2011-09-24 2 For day 30

Thank you very much and your help is appreciated,

Marco

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-10 : 00:55:01

shouldnt value for WELL_ID=3 be changing after day 10?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-10 : 00:55:01

marcoviy
Starting Member

6 Posts

Posted - 2011-09-10 : 13:04:11

Hey Visakh16,

No. All WELL_IDs (WELL_ID=1,WELL_ID=2,WELL_ID=3) should be displayed every day. What would change is the value for ID, START_AT and NET_OIL. For day 10, the query result will be:

ID WELL_ID START_AT NET_OIL
1 1 2011-09-01 3
2 2 2011-09-01 8
6 3 2011-09-10 2

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-09-10 : 14:10:12

or is it like unless you've a value for that day, you repeat the last available value?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources