Adding certain rows to a query

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

SQL Server 2008 Forums

Transact-SQL (2008)

Adding certain rows to a query

Author

Topic

marcoviy
Starting Member

6 Posts

Posted - 2011-06-13 : 13:40:38

Hello fiends,

I have the following table (OFF_WELL_DATE is DATETIME datatype; OFF_WELL is int and has the number of hours a well has been turned off):

WELL_ID OFF_WELL_DATE OFF_WELL
w1 11-05-01 5
w1 11-05-02 2
w1 11-05-10 4
w2 11-05-01 7
w2 11-05-05 5
w3 11-05-01 9
w3 11-05-07 2
w3 11-05-08 4

I need a query which displays the total time a well has been turned off every day, including the dates that the well has been turned on along the month (in which case OFF_WELL would be 0, because the well worked all day long), like this:

WELL_ID DATE OFF_WELL
w1 11-05-01 5
w1 11-05-02 2
w1 11-05-03 0
w1 … 0
w1 11-05-10 4
w1 … 0
w1 11-05-31 0
w2 11-05-01 7
w2 … 0
w2 11-05-05 5
w2 … 0
w2 11-05-31 0
w3 11-05-01 9
w3 … 0
w3 11-05-07 2
w3 11-05-08 4
w3 … 0
w3 11-05-31 0

Any help will be highly appreciated,
Marco

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-06-13 : 14:53:36

If you have a numbers table or a calendar table of some sort, that will make it much easier to do this. If you don't have a calendar table, you can construct one like this:


CREATE TABLE #tmpDates([date] DATETIME NOT NULL PRIMARY KEY CLUSTERED);

DECLARE @startDate DATETIME; SET @startDate = '20110501';
DECLARE @Days INT; SET @days = 100;

WITH N(n) AS 
(
	SELECT 1 UNION ALL SELECT n+1 FROM N WHERE n < @Days
)
INSERT INTO #tmpDates SELECT DATEADD(dd,n-1,@startDate) FROM N;

Once you have the Calendar table like above, your query would be something like this. If you have a table where the wells are listed, it would have been a little easier - we could avoid the WellsCTE which is trying to find all the wells we are interested in.

WITH WellsCTE AS
( SELECT DISTINCT well_id FROM WellsTable )
SELECT
	wc.well_id,
	d.[Date],
	ISNULL(w.OFF_WELL,0) AS OFF_WELL
FROM
	WellsCTE wc
	CROSS JOIN #tmpDates d
	LEFT JOIN WellsTable w
		ON wc.well_id = w.well_id AND d.[Date] = w.OFF_WELL_DATE
ORDER BY
	wc.well_id,
	d.[Date]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources