locked
Adding certain rows to a query. Something different RRS feed

  • Question

  • Hello folks,

    Tried this in another thread but haven't got the solution yet, so excuse me if I give another try. 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

     

    Monday, June 13, 2011 6:05 PM

Answers

  • Well, I don't like this solution very much, so perhaps someone else will come up with something better.

    First, your DATE column appears to be of the datatype DATE, not DATETIME. And that's probably good. Otherwise you will have time values messing up the dates.

    Second, you need to provide a table with all of the possible dates. No doubt there are many ways to programatically create the table, including temporary tables. To keep it simple, I would just create a table named PossibleDates. I'll use your column name of DATE even though that makes it confusing. CREATE TABLE PossibleDates (DATE DATE NOT NULL). And populate that table with all the dates that interest you. This is laborious, but a one-time activity. And it brings up another problem. Did all wells start gathering data at the same time? If not, you will have to wipe out past dates for those wells that did not exist yet.

    Third, query the WellTable joining the Possible Dates table. Use an outer join to get all dates, even if there is no OffWell data.

    SELECT
    CASE
         WHEN WELL_ID IS NULL THEN 'w1'
          ELSE WELL_ID
    END AS WELL_ID
    , DATE,
    CASE
         WHEN OFF_WELL IS NULL THEN 0
          ELSE OFF_WELL
    END AS OFF_WELL
    FROM WellTable
    RIGHT OUTER JOIN PossibleDates ON WellTable.OFF_WELL_DATE = PossibleDates.DATE
    WHERE WELL_ID = 'w1' OR WELL_ID IS NULL
    UNION
    SELECT
    CASE
         WHEN WELL_ID IS NULL THEN 'w2'
          ELSE WELL_ID
    END AS WELL_ID
    , DATE,
    CASE
         WHEN OFF_WELL IS NULL THEN 0
          ELSE OFF_WELL
    END AS OFF_WELL
    FROM WellTable
    RIGHT OUTER JOIN PossibleDates ON WellTable.OFF_WELL_DATE = PossibleDates.DATE
    WHERE WELL_ID = 'w2' OR WELL_ID IS NULL
    ORDER BY WELL_ID, DATE

    Last, I assume you want to terminate the list at today's date, or yesterdays? You don't want to assume that all future wells had 0 off times, just because you have no data for them.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Monday, June 13, 2011 9:58 PM

All replies

  • lol,

    Ok so you want all the records:

    Select * from myWellTable

    You're welcome :)

    Adam


    Ctrl+Z
    Monday, June 13, 2011 6:37 PM
  • Adam, actually it is not that simple ;)

    The problem is that in the table I do not have the rows with wells that have 0s in OFF_WELL field. I only have rows with wells that have been turned off (as showed in the table at the begining of the thread) What I need at the end is a query that add rows with wells that haven't been turned off for all dates in the month.

    Thanks,

    Marco

    Monday, June 13, 2011 8:02 PM
  • Well, I don't like this solution very much, so perhaps someone else will come up with something better.

    First, your DATE column appears to be of the datatype DATE, not DATETIME. And that's probably good. Otherwise you will have time values messing up the dates.

    Second, you need to provide a table with all of the possible dates. No doubt there are many ways to programatically create the table, including temporary tables. To keep it simple, I would just create a table named PossibleDates. I'll use your column name of DATE even though that makes it confusing. CREATE TABLE PossibleDates (DATE DATE NOT NULL). And populate that table with all the dates that interest you. This is laborious, but a one-time activity. And it brings up another problem. Did all wells start gathering data at the same time? If not, you will have to wipe out past dates for those wells that did not exist yet.

    Third, query the WellTable joining the Possible Dates table. Use an outer join to get all dates, even if there is no OffWell data.

    SELECT
    CASE
         WHEN WELL_ID IS NULL THEN 'w1'
          ELSE WELL_ID
    END AS WELL_ID
    , DATE,
    CASE
         WHEN OFF_WELL IS NULL THEN 0
          ELSE OFF_WELL
    END AS OFF_WELL
    FROM WellTable
    RIGHT OUTER JOIN PossibleDates ON WellTable.OFF_WELL_DATE = PossibleDates.DATE
    WHERE WELL_ID = 'w1' OR WELL_ID IS NULL
    UNION
    SELECT
    CASE
         WHEN WELL_ID IS NULL THEN 'w2'
          ELSE WELL_ID
    END AS WELL_ID
    , DATE,
    CASE
         WHEN OFF_WELL IS NULL THEN 0
          ELSE OFF_WELL
    END AS OFF_WELL
    FROM WellTable
    RIGHT OUTER JOIN PossibleDates ON WellTable.OFF_WELL_DATE = PossibleDates.DATE
    WHERE WELL_ID = 'w2' OR WELL_ID IS NULL
    ORDER BY WELL_ID, DATE

    Last, I assume you want to terminate the list at today's date, or yesterdays? You don't want to assume that all future wells had 0 off times, just because you have no data for them.


    Rick Byham, Microsoft, SQL Server Books Online, Implies no warranty
    Monday, June 13, 2011 9:58 PM