Post reply

Find out consecutive date difference

  • March 12, 2017 at 10:23 pm

    #401772

    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

     
    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 11:38 pm

    #1932846


    SELECT
      ID
    , DateDifference = SUM(DATEDIFF(DAY, ReportedDate, CompletedDate))
    FROM #ReportTable
    GROUP BY ID;

    How to post data/code on a forum to get the best help.

  • March 12, 2017 at 11:49 pm

    #1932847


    SELECT IdTable.ID ,
       ISNULL(SUM(DATEDIFF(DAY, ReportedDate ,CompletedDate)),0) AS DateDifference
    FROM dbo.IDTable IdTable
        LEFT JOIN dbo.ReportTable diff ON diff.ID = IdTable.ID
    GROUP BY IdTable.ID

Viewing 3 posts - 1 through 2 (of 2 total)

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