Results 1 to 8 of 8

Thread: SQL Query

  1. #1
    Join Date
    Feb 2013
    Posts
    6

    Unanswered: SQL Query

    New to this site. Any help would be appreciated, thank you.
    We would like to create a report on MS SQL server v2008 R2

    We have run the following code to generate an extract of data: -

    SELECT Member_ID, Visit_ID, Start_Date, End_Date
    FROM tblVisit
    ORDER BY Staff_ID, Visit_ID DESC

    Please see the attached .pdf file attached for full details

    From this code we would like to achieve the following : -

    1 - The day difference between the members latest Start_Date and previous End_Date (e.g. Row 2; Member_ID 7469; the day difference between 18/05/2012 and 10/05/2012. We have calculated the result being 8 days in excel this formula =IF(A2=A3,SUM(D3-C2),"")

    2 Continue point 1 until the Member_Id row changes

    3 - If next row is a different Member_ID to the previous row then calculate for the new Member_ID

    We would like the SQL code to generate the same results as in column E (as in the snapshot above) which was generated using the excel formula =IF(A2=A3,SUM(D3-C2),"")
    Attached Files Attached Files

  2. #2
    Join Date
    Apr 2012
    Posts
    213
    Try something like this:

    Code:
    with CTE_R as
    (
        select 
            Member_ID, 
            Visit_ID, 
            Start_Date, 
            End_Date,
            ROW_NUMBER() OVER(PARTITION BY Member_ID
                              ORDER BY Staff_ID, Visit_ID DESC) as RowNum
        FROM tblVisit
    )
    
    select 
        r.Member_ID,
        r.Visit_ID,
        r.Start_Date,
        r.End_Date,
        case when r.RowNum = 1 
            then null 
            else DATEDIFF(day, r.Start_Date, p.End_Date)
        end as QtyDays
    from CTE_R as r
    left join CTE_R as p
        on p.Member_ID = r.Member_ID and
           p.RowNum = r.RowNum - 1
    Hope this helps.

  3. #3
    Join Date
    Feb 2013
    Posts
    6
    Thank you very much for this.
    Just testing this out now. We need a few tweaks to suit our needs, which we are looking at but its about 90% there.
    We may get back to you but thank you for this great start.

  4. #4
    Join Date
    Feb 2013
    Posts
    6
    One last question, how do we apply a filter on QtyDays to the code previously provided?
    i.e. where QtyDays between 0 and 50
    Thank you

  5. #5
    Join Date
    Apr 2012
    Posts
    213
    Try something like this:

    Code:
    where
        case when r.RowNum = 1 
            then null 
            else DATEDIFF(day, r.Start_Date, p.End_Date)
        end between 0 and 50
    Hope this helps.

  6. #6
    Join Date
    Feb 2013
    Posts
    6
    This totally solves the issues we had. Thank you very much for your help

  7. #7
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    New to this site. Any help would be appreciated, thank you.
    Well, you made a bad first impression Let's fix it.

    Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules (you did not). Temporal data should use ISO-8601 formats (wrong again!). Code should be in Standard SQL as much as possible and not local dialect.

    This is minimal polite behavior on SQL forums.

    >> We would like to create a report on MS SQL server v2008 R2<<

    SQL does queries, not reports. This is like asking how to use a saw as a screwdriver on a woodworking forum

    >> We have run the following code to generate an extract of data: -<<

    I have to laugh a "tblVisit"; it is singular so there is only one visit! An SQL programmer would use a collecitve name for the SET in the table. The use of "tbl-" is so bad it has a name -- "tbling"!

    SELECT member_id, visit_nbr, visit_start_date, visit_end_date
    FROM Member_Visits;

    Please see the attached .pdf file attached for full details
    NO!! Your posting should be complete in itself, in text, without artwork and colors. Do you open every attachment you get from strangers? Do you like to re-type the PDF from a stranger in order to do his job for him? Want to open that file from Nigeria?

    >> 1 - The day difference between the members latest Start_Date and previous End_Date (e.g. Row 2; Member_ID 7469; the day difference between '2012-18-05' and '2012-10-05'. We have calculated the result being 8 days in excel this formula IF(A2=A3,SUM(D3-C2),"") <<

    I corrected your dates to ISO Standards; this is a vital standard in IT and you need to learn it. The dialect you picked is the third worst possible format! Before you ask, Slavic names and Roman numeral month are the least readable.

    You posted color! Why? It looks childish. What is EXCEL? This is an SQL forum and we do not speak anything but SQL. The reason for this is simple; a spreadsheet has ordering and a table does not. This is a huge difference!

    >> 2 Continue point 1 until the Member_Id row changes <<

    That is a procedural algorithm, with loops and sequence. Too bad SQL is declarative and does not work that way. We do not loop or sequence; we declare!

    >> 3 - If next row is a different Member_ID to the previous [sic] row then calculate for the new Member_ID <<

    SQL is a set-oriented language, so there is no concept of "previous" without an ordered value in columns. Look up the "information Prinicple" which is open of Codd's 12 laws of RM. You think of the physical layout of a spreadsheet! ARRGH!

    Do you watch FUTURAMA? Dr. Zoidberg? "Of course I know about humans! Now lift your gill slits so I can check for parasites." I could give you a kludge (and someone will), but I would rather give you an education. Hey, what is the fun of getting free time with one of creators of SQL and not learning something?


    Here is your data, done correctly:

    CREATE TABLE Visits
    (member_id CHAR(4)NOT NULL,
    visit_nbr CHAR(5)NOT NULL,
    PRIMARY KEY(member_id, visit_nbr),
    visit_start_date DATE NOT NULL,
    visit_end_date DATE,
    CHECK(visit_start_date <= visit_end_date));

    INSERT INTO Visits
    VALUES
    ('7469', '10246', '2012-05-18', NULL),
    ('7469', '10245', '2012-09-05', '2012-10-05')
    ('7475', '10252', '2012-08-14', NULL),
    ('7480', '10262', '2012-09-30', '2011-02-13'),
    ('7483', '10266', '2012-06-29', '2012-08-21'),
    ('7486', '10278', '2012-05-17', '2012-07-06'),
    ('7491', '10276', '2012-07-11', NULL);

    An SQL programmer would not do this!! We will write declarative code to prevent NULLs. There are tricks in the DDL to prevent gaps, etc

    CREATE TABLE Events
    (event_id CHAR(10) NOT NULL,
    previous_event_end_date DATE NOT NULL
    CONSTRAINT Chained_Dates
    REFERENCES Events (event_end_date),
    event_start_date DATE NOT NULL,
    event_end_date DATE UNIQUE, -- null means event in progress
    PRIMARY KEY (event_id, event_start_date),
    CONSTRAINT Event_Order_Valid
    CHECK (event_start_date <= event_end_date),
    CONSTRAINT Chained_Dates
    CHECK (DATEADD(DAY, 1, previous_event_end_date) = event_start_date)
    );

    -- disable the Chained_Dates constraint
    ALTER TABLE Events NOCHECK CONSTRAINT Chained_Dates;
    GO

    -- insert a starter row
    INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
    VALUES ('Foo Fest', '2010-01-01', '2010-01-02', '2010-01-05');
    GO

    -- enable the constraint in the table
    ALTER TABLE Events CHECK CONSTRAINT Chained_Dates;
    GO

    -- this works
    INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
    VALUES ('Glob Week', '2010-01-05', '2010-01-06', '2010-01-10');

    -- this fails
    INSERT INTO Events(event_id, previous_event_end_date, event_start_date, event_end_date)
    VALUES ('Snoob', '2010-01-09', '2010-01-11', '2010-01-15');

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by Celko View Post
    Well, you made a bad first impression Let's fix it.
    No. He was courteous and informative.
    Quote Originally Posted by Celko View Post
    Please post DDL, ....blah blah blah.
    No. He posted enough information to allow a responder to pose intelligent questions for further information.

    Quote Originally Posted by Celko View Post
    This is minimal polite behavior on SQL forums.
    Not on this forum. As I have pointed out before. It is inappropriate of you, Joe, to jump into this forum and immediately start dictating to other people what is and what is not considered polite behavior.
    Quote Originally Posted by Celko View Post
    >> We would like to create a report on MS SQL server v2008 R2<<

    SQL does queries, not reports. This is like asking how to use a saw as a screwdriver on a woodworking forum
    I understood exactly what the poster meant.
    Quote Originally Posted by Celko View Post

    >> We have run the following code to generate an extract of data: -<<

    I have to laugh a "tblVisit"; it is singular so there is only one visit! An SQL programmer would use a collecitve name for the SET in the table. The use of "tbl-" is so bad it has a name -- "tbling"!
    Inappropriate abusive, and outside the topic of the post. The poster was not asking about naming conventions, and may have no control over the design of the database he is using.

    The rest of you reply was similarly abusive.
    Gratuitously so, as the poster's question had already been answered to his satisfaction.
    I've asked you before to stop this behavior, and if you continue to alienate guests then I will start blocking your posts.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

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