Professional Web Applications Themes

Help in writing a query - Microsoft SQL / MS SQL Server

I have a table with Title, Effective and Expiry fields. I want to be able to group this table based on the title And dates Eg. Title is - Test effective 01/01/70 expiry 12/31/71 Test effective 01/01/72 expiry 9/1/73 Test effective 9/2/73 expiry 12/31/99 Test effective 1/1/2000 expiry 6/30/2000 Digital effective 7/1/2000 expiry 1/1/2001 Test effective 1/2/2001 expiry 10/1/2002 Super effective 10/2/2002 expiry 1/1/2003 Digital effective 1/2/2003 expiry 6/30/2003. I want to write a query to retrieve records from this table Group by the Title and dates together. For the above eg. It should retrieve me results as - Test ...

  1. #1

    Default Help in writing a query

    I have a table with Title, Effective and Expiry fields.

    I want to be able to group this table based on the title
    And dates

    Eg. Title is - Test effective 01/01/70 expiry 12/31/71
    Test effective 01/01/72 expiry
    9/1/73
    Test effective 9/2/73 expiry
    12/31/99
    Test effective 1/1/2000 expiry
    6/30/2000
    Digital effective 7/1/2000 expiry
    1/1/2001
    Test effective 1/2/2001 expiry
    10/1/2002
    Super effective 10/2/2002 expiry
    1/1/2003
    Digital effective 1/2/2003 expiry
    6/30/2003.

    I want to write a query to retrieve records from this
    table
    Group by the Title and dates together.

    For the above eg. It should retrieve me results as -

    Test effective 01/01/70 expiry 6/30/2000
    Digital effective 7/1/2000 expiry 1/1/2001
    Test effective 1/2/2001 expiry 10/1/2002
    Super effective 10/2/2002 expiry 1/1/2003
    Digital effective 1/2/2003 expiry 6/30/2003.

    Thanks much,

    -Alice

    Alice Guest

  2. #2

    Default Re: Help in writing a query

    Hello Alice !

    (As far as i understood your questions...)

    First of all, this won´t work with your resultset.
    In the the raw data you have 7 rows, but in the resultset you only have 5
    rows, even
    they can´t be grouped because they have different values.

    Results, can be grouped together, if they have values in common. So you
    should
    try to think about what you really want to get. (MAX / MIN).

    Jens Süßmeyer.


    "Alice" <com> schrieb im Newsbeitrag
    news:061f01c365d0$f4b19eb0$gbl... 


    Jens Guest

  3. #3

    Default Re: Help in writing a query

    Jen,

    That's exactly the problem I am facing.
    I was thinking if it can be done using co-related queries.
    I did it long time back and don't remember. So thought
    if someone is in touch and can help me.
    I already tried min, max, etc. but it doesn't help much.

    Else I will have to compare rec by rec.

    Thank you very much,
    -Alice
     
    resultset you only have 5 
    common. So you 
    MIN). [/ref]
    title 
    >
    >
    >.
    >[/ref]
    Alice Guest

  4. #4

    Default Re: Help in writing a query

    OK, then tell me(us), what you want to achive with your Querys. I can´t
    point out your aim you want to achieve.

    Jens.


    "Alice" <com> schrieb im Newsbeitrag
    news:03b001c365d3$f6350300$gbl...
    Jen,

    That's exactly the problem I am facing.
    I was thinking if it can be done using co-related queries.
    I did it long time back and don't remember. So thought
    if someone is in touch and can help me.
    I already tried min, max, etc. but it doesn't help much.

    Else I will have to compare rec by rec.

    Thank you very much,
    -Alice
     
    resultset you only have 5 
    common. So you 
    MIN). [/ref]
    title 
    >
    >
    >.
    >[/ref]


    Jens Guest

  5. #5

    Default Re: Help in writing a query

    Making a wild guess from what you posted, are you trying to merge
    contigous durations for the same titles?

    The ISO model of time of time is based on half-open intervals, so a date
    like '2001-01-01' includes all the "points in time" from '2001-01-01
    00:00.000...' thru '2001-01-01 59:59.

    9999..., so '2001-01-01' and '2001-01-02' are contigous, but do not
    Contracts in the sense of having a common duration. I would think that
    you want to merge contigous durations for the same (member_num,
    contract_num). Here is a solution using the Calendar table.

    SELECT X.contract_num, MIN(X.begin_dt), X.end_dt
    FROM (SELECT C1.contract_num, C1.begin_dt, MAX(C2.end_dt)
    FROM Contracts AS C1, Contracts AS C2
    WHERE C1.member_num = C2.member_num
    AND C1.contract_num = C2.contract_num
    AND C1.begin_dt < C2.end_dt
    AND DATEDIFF(DD, C1.begin_dt, C2.end_dt) +1
    = (SELECT COUNT(DISTINCT K1.cal_date)
    FROM Calendar AS K1
    WHERE EXISTS
    (SELECT *
    FROM Contracts AS C3
    WHERE C1.member_num = C3.member_num
    AND C1.contract_num = C3.contract_num
    AND K1.cal_date BETWEEN C3.begin_dt
    AND C3.end_dt
    AND C3.begin_dt BETWEEN C1.begin_dt
    AND C2.end_dt
    AND C3.end_dt BETWEEN C1.begin_dt
    AND C2.end_dt))
    GROUP BY C1.contract_num, C1.begin_dt) AS X(contract_num,
    begin_dt, end_dt)
    GROUP BY contract_num, end_dt;

    Three copies of Contracts: C1 is the start, C2 is the end, and C3 is all
    the durations in the middle. Find the length of [C1, C2] in days. Find
    the number of distinct calendar days

    in all the durations inside the range of [C1, C2]. If there is one or
    more days not covered by a duration, reject it.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

  6. #6

    Default Re: Help in writing a query

    >> don't have the calendar. <<

    The Calendar table is a standard SQL programming trick. You create it
    yourself and load it with all the temporal data your enterprises needs
    -- work days, holidays, fiscal calendar periods, marketing seasons, etc.

    Some data is easy to create, such as weekends, but other data you might
    want to do by hand with a spreadsheet. Easter and other lunar/solar
    holidays are a really complicated bits of math; declared holidays are
    unpredictable; some fiscal calendars are too weird.

    --CELKO--
    ===========================
    Please post DDL, so that people do not have to guess what the keys,
    constraints, Declarative Referential Integrity, datatypes, etc. in your
    schema are.

    *** Sent via Developersdex http://www.developersdex.com ***
    Don't just participate in USENET...get rewarded for it!
    Joe Guest

Similar Threads

  1. Trouble writing a query
    By laredotornado@zipmail.com in forum MySQL
    Replies: 2
    Last Post: April 23rd, 12:09 PM
  2. Help writing a query
    By alexp in forum MySQL
    Replies: 3
    Last Post: September 25th, 05:15 PM
  3. I Need help writing a query
    By charlese in forum Coldfusion Database Access
    Replies: 1
    Last Post: March 17th, 03:22 PM
  4. Writing SQL query without subselect
    By Steven Stern in forum PHP Development
    Replies: 15
    Last Post: October 15th, 08:27 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139