Post reply

select statement

  • October 8, 2007 at 9:13 am

    #62957

    I have a table, it has three columns (phonenumber,date,outofservice)

    If the phone number is outofservice the value for that day would be N otherwise Y

    I need to generate a report for a month , the phonenumber which outof service for a consecutive days (that start date could be in previous month), the report format should be like this (this report is month of Septemeber)

    PhoneNumber StartDate EndDate Numberofdays of service

    1111 8/30/2007 9/2/2007 4

    2222 8/29/2007 9/3/2007 6

    3333 9/29/2007 9/30/2007 2

    4444 7/29/2007 9/3/2007 39

    How can I write SQL Statement to get this report? thanks in advance for your help.

    Regards,

    RS.

  • October 8, 2007 at 9:25 am

    #740058

    This solution is taking so many assumptions, it's not funny... but this works

    with the assumptions that

    1. the start date is the first entry in the table.

    2. a phone number only has one out of service date.

    3. and others I've missed that someone else here will be so kind and point out.

    CREATE TABLE #phoneStats (phonenumber VARCHAR(10), serviceDate DATETIME, outOfService CHAR(1))

    INSERT #phoneStats

    SELECT '0005551111', '1/1/2007', 'N' UNION

    SELECT '0005551111', '2/1/2007', 'N' UNION

    SELECT '0005551111', '3/15/2007', 'Y' UNION

    SELECT '0005552222', '1/1/2007', 'N' UNION

    SELECT '0005552222', '3/1/2007', 'Y' UNION

    SELECT '0005553333', '1/1/2007', 'N' UNION

    SELECT '0005553333', '2/1/2007', 'N' UNION

    SELECT '0005553333', '3/1/2007', 'N' UNION

    SELECT '0005553333', '4/1/2007', 'N' UNION

    SELECT '0005553333', '5/1/2007', 'Y' UNION

    SELECT '0005554444', '1/1/2007', 'N'

    SELECT

    phoneNumber

    ,MIN(serviceDate) AS startdate

    ,(SELECT ISNULL(MAX(p2.serviceDate), GETDATE()) FROM #phoneStats p2 WHERE p2.phonenumber = p1.phoneNumber AND outOfService = 'Y')

    ,DATEDIFF(DAY,MIN(serviceDate),(SELECT ISNULL(MAX(p2.serviceDate), GETDATE()) FROM #phoneStats p2 WHERE p2.phonenumber = p1.phoneNumber AND outOfService = 'Y')) AS daysOfService

    FROM

    #phoneStats p1

    GROUP BY

    phoneNumber

    DROP TABLE #phoneStats

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

  • October 8, 2007 at 9:37 am

    #740065

    Thanks for the reply.

    Please see my answers for your assumtions

    1. No it can be any where

    2. Can be 2 out of services but need the latest one

    Note: The max date should be in report month but min date can be any where before the max date.

    Thanks.

  • October 8, 2007 at 10:21 am

    #740082

    #1 above doesn't matter. It's in his example. You'd just put phone number in your query. SQL will find the field.

    Look for the max date for which you have things set to "N", which means they're out of service.

    Now look for the max date which is < this other max date.

    so if I had a series of dates

    create table mytable

    ( cust int, dt datetime, service char(1))

    go

    insert mytable select 1, '1/2/07', 'N'

    insert mytable select 1, '2/1/07', 'N'

    insert mytable select 1, '3/1/07', 'Y'

    insert mytable select 1, '4/1/07', 'Y'

    insert mytable select 1, '5/1/07', 'N'

    insert mytable select 2, '1/12/07', 'N'

    insert mytable select 2, '1/14/07', 'N'

    insert mytable select 2, '3/1/07', 'Y'

    insert mytable select 2, '3/12/07', 'N'

    insert mytable select 2, '4/1/07', 'N'

    go

    select a.cust

    , datediff( d, b.dt, a.dt)

    from Mytable a

    inner join MyTable b

    on a.cust = b.cust

    where a.dt = (select max(dt)

    from mytable c

    where c.cust = a.cust

    )

    and b.dt = ( select max(dt)

    from MyTable d

    where d.dt < a.dt)

    go

    drop table MyTable

  • October 8, 2007 at 10:32 am

    #740093

    Thanks Steve,

    I haved added dt AS ST_Date, a.dt AS END_Date to your script (in SELECT Statement)

    here is the result I got

    custST_Date END_Date Days

    12007-04-01 00:00:00.0002007-05-01 00:00:00.00030

    22007-03-12 00:00:00.0002007-04-01 00:00:00.00020

    But the for the cust 1 on ST_Date (2007-04-01) the service was Y so it should show in report because its not consecutive days for N.

    Thanks

  • October 8, 2007 at 10:34 am

    #740094

    Thanks Steve. FYI, I didn't mean the "first" physical record, rather the first record in terms of datetime.

    But anywho, thanks for the better solution Steve.

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg

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

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