Post reply

Find out consecutive date difference by comparing with master table

  • March 12, 2017 at 12:42 pm

    #321332

    Dear All,

    Please help me to sort out it

    I have reporttable where the fault report date and complete date is inserted and i have a master table for the ID. 

    1 ReportTable

    ID ReportedDate CompletedDate
    101 10-02-2016 12-02-2016
    111 18-02-2016 19-02-2016
    113 18-03-2016 22-03-2016
    101 25-02-2016 27-02-2016
    111 10-03-2016 12-03-2016

    CREATE TABLE [dbo].[ReportTable](
           [ID] [int] NULL,
           [ReportedDate] [datetime] NULL,
           [CompletedDate] [datetime] NULL
    ) ON [PRIMARY]

    insert into [dbo].[ReportTable] ([ID],[ReportedDate],[CompletedDate]) values(101,'2016-02-10','2016-02-12')

    insert into [dbo].[ReportTable] ([ID],[ReportedDate],[CompletedDate]) values(111,'2016-02-18','2016-02-19')

    insert into [dbo].[ReportTable] ([ID],[ReportedDate],[CompletedDate]) values(113,'2016-03-18','2016-03-22')

    insert into [dbo].[ReportTable] ([ID],[ReportedDate],[CompletedDate]) values(101,'2016-02-25','2016-02-27')

    insert into [dbo].[ReportTable] ([ID],[ReportedDate],[CompletedDate]) values(111,'2016-03-10','2016-03-12')

    2 IDTable

     

    ID
    101
    111
    113
    101
    111
    122
    133
    141
    151

     

    CREATE TABLE [dbo].[IDTable](
           [ID] [int] NULL
    ) ON [PRIMARY]

    insert into [dbo].[IDTable]([ID]) values(101)
    insert into [dbo].[IDTable]([ID]) values(113)
    insert into [dbo].[IDTable]([ID]) values(111)
    insert into [dbo].[IDTable]([ID]) values(122)
    insert into [dbo].[IDTable]([ID]) values(133)
    insert into [dbo].[IDTable]([ID]) values(141)
    insert into [dbo].[IDTable]([ID]) values(151)

    Output

    I would like to get the result based on the date selection as 113 was down for 4 days, 101 was down for 2 + 2 = 4 days, 111 was down for 1 + 2 = 3 days and whatever ID not present in the reporttable will have date difference as 0. In where conditioin i must get the option to put Date range 

    ID Date Difference
    113 4
    101 4
    111 3
    122 0
    133 0
    141 0
    151 0

  • March 12, 2017 at 10:17 pm

    #1932843

    Can anyone help me to solve this issue ?

  • March 13, 2017 at 2:16 am

    #1932852

    Most probably
    declare @start date = '2016-02-11';
    declare @end date = '2016-03-11';

    select i.ID, coalesce(sum(datediff(day
                ,case when r.[ReportedDate] < @start then @start else r.[ReportedDate] end
                ,case when r.[CompletedDate] > @end then @end else r.[CompletedDate] end)
             ),0)
    from [dbo].[IDTable] i
    left join [dbo].[ReportTable] r on r.[ID]= i.[ID]
       and r.[CompletedDate] >= @start and r.[ReportedDate] <= @end
    group by i.[ID]
    order by i.[ID]

  • March 13, 2017 at 10:25 am

    #1932907

    shagil.a.gopinath - Sunday, March 12, 2017 10:17 PM

    >>I have report table where the fault report date and complete date is inserted and I have a master table for the ID. <<

    There is no such thing as a master table in RDBMS; we have referenced and referencing tables. That term comes from the old days with network databases and file systems. Also, putting the word “_table†in the name of a table is design error called a Tibble. You are mixing data and metadata in violation of all of basic data modeling rules.It usually the symptom of some more serious design problems, as well as a source of amusement for experienced people.

    You also got the display format for dates wrong; the only format allowed in ANSI'), ISO standard SQL is based on ISO 8601. The DDL you posted can never have a key, because everything is NULL-able. But by definition, a table must have at least one key.

    Finally,there is no such thing as a generic “id†in RDBMS. It must be theidentifier of something in particular. Have you ever had a course in basic logic? This is a result of what is called the law of identity,the foundation of all Western thought. Furthermore, and identifier can never be a numeric value; what math were you going to do on it?

    Let's start  by fixing what you’ve got.

    CREATE TABLE Something_Reports
    (report_id CHAR(3) NOT NULL
     REFERENCES Report(report_id)
    ON DELETE CASCADE
    ON UPDATE CASCADE,
    report_date DATE NOT NULL,
    CHECK(report date <= completion_date),
    PRIMARY KEY (report_id, report_date));

    Please notice all the guessing I had to do. I had to guess at a key. I had to correct the data types. I had to guess the relationship between the report date and the completion date. I hope I got it right since you didn’t help. Then your insertions can be done in one statement and should be. Microsoft has had the ANSI/ISO standard syntax for a few years now.

    INSERT INTO Something_Reports
    VALUES (‘101’,'2016-02-10', '2016-02-12'),
    (‘101’,'2016-02-25', '2016-02-27'),
    (‘111’,'2016-02-18', '2016-02-19'),
    (‘111’,'2016-03-10', '2016-03-12'),
    (‘113’,'2016-03-18', '2016-03-22');

    Instead of having to do these one row at a time, like you did when you were working with punch cards, this insertion can be done in a single statement.

    CREATE TABLE Reports
    (report_id CHAR(3) NOT NULL PRIMARY KEY);

    See how we have a key? See how we don’t have duplicate rows because that's the definition of a table and First Normal Form (1NF) in RDBMS? Now go back up and look at the references clause in the Something_Reports table. Again, I have to make a guess that if are port_id is dropped or changed, this update cascades.

    INSERT INTO Reports
    VALUES('101'), ('111'), ('113'),
    ('122'),('133'), ('141'), ('151');

    >>I would like to get the result based on the date selection as 113 was down for 4 days, 101 was down for 2 + 2 = 4 days, 111 was down for 1+ 2 = 3 days and whatever report_id not present in the Something_Report will have date difference as 0. In WHERE condition I must get the option to put Date range <<

    this is a little bit problematic. We don’t have a definition about what it means for an event to be in a range. Does it have to be completely enclosed by the range? Or can it start outside the range? Or can it finish outside the range? Why do you want to set the results to zeros instead of null? Think about it. If an event completes on its report date then it’s a zero. But if it never occurs at all, then it’s null. The basic query. You seem to be after would look like this,and then you can put it in a CTE and filter it, according to whatever your business rules are.

    SELECT I.report_id,   SUM(DATEDIFF(DAY, R.report_date,R.completion_date))FROM Reports AS I   LEFT OUTER JOIN   Something_Reports AS R   ONR.report_id = I.report_idGROUP BY I.report_id;

    [/

    Please post DDL and follow ANSI/ISO standards when asking for help. 

  • March 13, 2017 at 4:10 pm

    #1932969

    jcelko212 32090 - Monday, March 13, 2017 10:24 AM

    You also got the display format for dates wrong; the only format allowed in ANSI'), ISO standard SQL is based on ISO 8601.

    That's a load of hooie, Joe.  You've previously stated that reporting can be based on regional dialect if SQL Server output is being used as the "presentation layer" and you've made up your own formats as an unsupported extension of the ISO 8601 standard, as well.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply