Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    Join Date
    Jan 2012
    Posts
    10

    Unanswered: calculating night shifts

    hi guys
    i am a new bee to forums and DB.. i am just facing some problem with a query in the db, i am trying to find the number of tickets available for night shift which is 23.00.00 to next day morning 07.00.00. but the query is not giving any results

    QUERY:-
    select ticketid,
    reportdate
    from ticket
    where time (reportdate ) > '22.00.00' and time (reportdate + 1 day ) < '07.59.00'

    the output is displaying blank


    if i execute it in this way i am able to get the result

    Query:-

    select ticketid, reportdate , time (reportdate + 1 day ) from ticket
    where (reportdate) between '2012-01-02-22.00.00' and '2012-01-03-07.59.00'

    the output is returning 12 values
    But i do not want to pass a datetime value

    Any Help Is Much Appreciated
    thanks in advance
    sai

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    yes
    time (reportdate ) > '22.00.00' and time (reportdate + 1 day ) < '07.59.00'
    this is correct : time > 22.00 will never be smaller than 07.59
    the reportdate + 1 day is useless
    you have to rewrite the query so you can match rows from day>22.00 and day+1<7.59
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  3. #3
    Join Date
    Mar 2003
    Posts
    280
    Quote Originally Posted by Stranger_G View Post
    hi guys

    where time (reportdate ) > '22.00.00' and time (reportdate + 1 day ) < '07.59.00'

    sai
    If you think about it, how many things happens before 8 a clock in the morning and at the same time after 22 at night? Is this what you are looking for?

    select
    date(reportdate - 8 hours), count(1)
    from ticket
    where time (reportdate ) > '22.00.00' or time (reportdate) < '07.59.00'
    group by date(reportdate - 8 hours)

    /Lennart

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    time (reportdate + 1 day ) < '07.59.00'
    If you added days, time was not different.
    The result of time(reportdate + n days) are always same as the result of time(reportdate).
    A time cannot be satisfyed both of
    time (reportdate) > '22.00.00'
    and
    time(reportdate + 1 day)/* same as time (reportdate)*/ < '07.59.00'

    i am trying to find the number of tickets available for night shift which is 23.00.00 to next day morning 07.00.00.
    If you ask only time, another days'(for example yesterday's) rows included in that timezone are also selected.
    So, it is inevitable to be asked date, too.

  5. #5
    Join Date
    Jan 2012
    Posts
    10
    Quote Originally Posted by tonkuma View Post
    If you added days, time was not different.
    The result of time(reportdate + n days) are always same as the result of time(reportdate).
    A time cannot be satisfyed both of
    time (reportdate) > '22.00.00'
    and
    time(reportdate + 1 day)/* same as time (reportdate)*/ < '07.59.00'


    If you ask only time, another days'(for example yesterday's) rows included in that timezone are also selected.
    So, it is inevitable to be asked date, too.
    thanks for your Quick response Guys.
    ok then is there any way that i can actully pull the tickets of this particular time. because the dates are being passed by the end user as a parameter, so when a user passes the dates say for example jan 4 is passed as an input then , i just want to pull the records between jan4 22.00.00 and jan 5 7.00.00

  6. #6
    Join Date
    Jan 2012
    Posts
    10
    Quote Originally Posted by lelle12 View Post
    If you think about it, how many things happens before 8 a clock in the morning and at the same time after 22 at night? Is this what you are looking for?

    select
    date(reportdate - 8 hours), count(1)
    from ticket
    where time (reportdate ) > '22.00.00' or time (reportdate) < '07.59.00'
    group by date(reportdate - 8 hours)

    /Lennart
    thanks for your quick reply ,
    yeah i am trying see the records from night 10 PM to next day mor 7.00 AM

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You already have an anawer.
    if i execute it in this way i am able to get the result

    Query:-

    select ticketid, reportdate , time (reportdate + 1 day ) from ticket
    where (reportdate) between '2012-01-02-22.00.00' and '2012-01-03-07.59.00'

    the output is returning 12 values
    But i do not want to pass a datetime value
    Why?

  8. #8
    Join Date
    Jan 2012
    Posts
    10
    SELECT
    ((A.TOTAL_FLT_SMS_SENT)/ CASE WHEN (DECIMAL( A.TOTAL_TKT_OPENED)) = 0 THEN 1 ELSE (DECIMAL( A.TOTAL_TKT_OPENED)) END) AS FAULT_NOTIFICATION_SMS_SENT_PERCENT,
    ((A.TOTAL_FLT_SMSSENT_SLA)/CASE WHEN (DECIMAL (A.TOTAL_FLT_SMS_SENT)) = 0 THEN 1 ELSE (DECIMAL (A.TOTAL_FLT_SMS_SENT)) END) AS FAULT_NOTIFICATION_SMS_SENT_PERCENT_SLA,
    (A.CNT_OF_DISHA_SENT)/ CASE WHEN (DECIMAL (A.TOT_SA_TT) )= 0 THEN 1 ELSE (DECIMAL (A.TOT_SA_TT)) END AS SA_NOTIFICATION_SENT_TO_DISHA_PERCENT,
    A.TOT_SA_SD_TT_CLOSED,
    A.TOTAL_RST_SMS_SENT,
    A.TOTAL_RST_SMSSENT_SLA,
    (A.TOTAL_RST_SMS_SENT)/ CASE WHEN (DECIMAL (A.TOT_TKT_CLOSED))= 0 THEN 1 ELSE (DECIMAL (A.TOT_TKT_CLOSED)) END AS RST_SMS_SENT_PERCENT,
    (A.TOTAL_RST_SMSSENT_SLA)/ CASE WHEN (DECIMAL(A.TOTAL_RST_SMS_SENT)) = 0 THEN 1 ELSE (DECIMAL(A.TOTAL_RST_SMS_SENT)) END AS RST_SMS_SENT_SLA_PERCENT,
    A.DISHA_SENT_WITHIN_SLA/ CASE WHEN (DECIMAL (CNT_OF_DISHA_SENT)) = 0 THEN 1 ELSE (DECIMAL (CNT_OF_DISHA_SENT)) END AS NOTIFICATION_SENT_TO_DISHA_SLA,
    A.MTTR_SA_SD,
    A.OPEN_NSA_ST_TKT,
    A.CLOSED_NSA_SD_TKT,
    A.MTTR_NSA_ST,
    A.PENDING_FOR_CLOSED



    FROM
    (select
    COUNT ( CASE WHEN TB1.IMPACT IN ('1','3') AND TB1.STATUS NOT IN ('RESOLVED','CLOSED') THEN TB1.TICKETID END ) AS SA_SD_TKT_OPENED,
    COUNT ( CASE WHEN TB1.FLT IN '1' THEN TB1.FLT END ) AS TOTAL_FLT_SMS_SENT ,
    COUNT ( CASE WHEN STATUS NOT IN ('RESOLVED','CLOSED') THEN TB1.TICKETID END) AS TOTAL_TKT_OPENED,
    COUNT ( CASE WHEN TB1.FLT IN '1' AND (TIMESTAMPDIFF(4,CHAR(TIMESTAMP(TB1.SMS_SENT_TIME)-TIMESTAMP(TB1.REPORTDATE)))) < ? THEN 'SMS SENT' END )as TOTAL_FLT_SMSSENT_SLA,
    COUNT ( CASE WHEN TB1.IMPACT IN ('1') THEN TB1.IMPACT END) AS TOT_SA_TT,
    COUNT ( CASE WHEN TB1.IMPACT IN ('1') AND TB1.DISHAINSERTED = ('1') THEN 'DISHA UPDATED' END ) AS CNT_OF_DISHA_SENT,
    --(COUNT ( CASE WHEN TB1.IMPACT IN ('1') AND TB1.DISHAINSERTED = ('1') THEN 'DISHA UPDATED' END ))/(COUNT ( CASE WHEN TB1.IMPACT IN ('1') THEN TB1.IMPACT END)) AS A1,
    COUNT ( CASE WHEN TB1.IMPACT IN ('1','3') AND STATUS IN ('CLOSED') THEN TB1.IMPACT END) AS TOT_SA_SD_TT_CLOSED,
    COUNT ( CASE WHEN TB1.RST IN '1' THEN TB1.RST END ) AS TOTAL_RST_SMS_SENT,
    COUNT ( CASE WHEN STATUS IN ('CLOSED') THEN TB1.TICKETID END) AS TOT_TKT_CLOSED,
    COUNT ( CASE WHEN TB1.RST IN '1' AND (TIMESTAMPDIFF(4,CHAR(TIMESTAMP(TB1.SMS_SENT_TIME)-TIMESTAMP(TB1.REPORTDATE)))) < ? THEN 'SMS SENT' END )as TOTAL_RST_SMSSENT_SLA,
    COUNT ( CASE WHEN TB1.IMPACT IN ('1') AND TB1.DISHAINSERTED = ('1') THEN 'DISHA UPDATED' END ) AS CNT_OF_DISHA_SENT1,
    COUNT ( CASE WHEN TB1.IMPACT IN '1' AND TB1.DISHAINSERTED IN '1' AND (TIMESTAMPDIFF (4, CHAR ( TIMESTAMP( TB1.DISHA_UPDTIME) - TIMESTAMP(TB1.REPORTDATE)))) < ? THEN 'WITH IN SLA' END )AS DISHA_SENT_WITHIN_SLA,
    (AVG ( CASE WHEN IMPACT IN ('1','3') THEN (TIMESTAMPDIFF (8, CHAR ( TIMESTAMP( TB1.ACTUALFINISH) - TIMESTAMP(TB1.REPORTDATE))))END )||':'||(AVG ( CASE WHEN IMPACT IN ('1','3') THEN (MOD(TIMESTAMPDIFF (4,CHAR(TIMESTAMP(TB1.ACTUALFINISH) - TIMESTAMP(TB1.REPORTDATE))),60)) END ))) AS MTTR_SA_SD,
    COUNT (CASE WHEN IMPACT IN ('2','4') AND TB1.STATUS NOT IN ('RESOLVED','CLOSED') THEN TB1.TICKETID END ) AS OPEN_NSA_ST_TKT,
    COUNT (CASE WHEN IMPACT IN ('2','4') AND TB1.STATUS IN ('CLOSED') THEN TB1.TICKETID END ) AS CLOSED_NSA_SD_TKT,
    COUNT ( CASE WHEN STATUS IN ('RESOLVED') THEN TB1.TICKETID END )AS PENDING_FOR_CLOSED,
    (AVG ( CASE WHEN IMPACT IN ('2','4') THEN (TIMESTAMPDIFF (8, CHAR ( TIMESTAMP( TB1.ACTUALFINISH) - TIMESTAMP(TB1.REPORTDATE))))END )||':'||(AVG ( CASE WHEN IMPACT IN ('2','4') THEN (MOD(TIMESTAMPDIFF (4,CHAR(TIMESTAMP(TB1.ACTUALFINISH) - TIMESTAMP(TB1.REPORTDATE))),60)) END ))) AS MTTR_NSA_ST

    FROM TICKET TB1
    WHERE TIME (TB1.REPORTDATE) BETWEEN '22.30.00' AND '07.30.00'
    AND TB1.REPORTDATE BETWEEN ? AND ?
    ) A

  9. #9
    Join Date
    Mar 2003
    Posts
    280
    using current_date as input parameter:

    where report_date between timestamp(current_date) + 22 hours
    and timestamp(current_date + 1 day) + 8 hours

    /Lennart

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Try something like...
    WHERE tb1.report_date BETWEEN TIMESTAMP(current_date, '22:30:00') AND TIMESTAMP(current_date + 1 DAY , '07:30:00')

  11. #11
    Join Date
    Jan 2012
    Posts
    10
    Quote Originally Posted by tonkuma View Post
    Try something like...
    WHERE tb1.report_date BETWEEN TIMESTAMP(current_date, '22:30:00') AND TIMESTAMP(current_date + 1 DAY , '07:30:00')


    No LUCK guys
    it is returning with an error
    DB2 Database Error: ERROR [42884] [IBM][DB2/LINUXX8664] SQL0440N No authorized routine named "TIMESTAMP" of type "FUNCTION" having compatible arguments was found. SQLSTATE=42884


    the query i have tried is

    select ticketid,
    reportdate
    from ticket
    WHERE reportdate BETWEEN TIMESTAMP(current_date, '22:30:00') AND TIMESTAMP(current_date + 1 DAY , '07:30:00')

    thanks for your time guys

  12. #12
    Join Date
    Jan 2012
    Posts
    10
    Quote Originally Posted by lelle12 View Post
    using current_date as input parameter:

    where report_date between timestamp(current_date) + 22 hours
    and timestamp(current_date + 1 day) + 8 hours

    /Lennart
    sorry Lennart i did not understand the logic u are trying, But let me explain what i want
    reportdate is like a creation date. when ever a ticket is created reportdate is generated)

    and what i actually need is when a user is passing two dates i need the count of tickets created from night 22.00.00 to tomorrow morning 07.00.00

    so if u don't mind can u please explain me the logic, i am really sorry if i am troubling
    thanks for your patience and time

  13. #13
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Quote Originally Posted by Stranger_G View Post
    No LUCK guys
    ...
    the query i have tried is

    select ticketid,
    reportdate
    from ticket
    WHERE reportdate BETWEEN TIMESTAMP(current_date, '22:30:00') AND TIMESTAMP(current_date + 1 DAY , '07:30:00')

    ...
    This worked on my DB2, like...
    Code:
    ------------------------------ Commands Entered ------------------------------
    connect to SAMPLE ;
    ------------------------------------------------------------------------------
    
       Database Connection Information
    
     Database server        = DB2/NT 9.7.5
     SQL authorization ID   = DB2ADMIN
     Local database alias   = SAMPLE
    
    
    A JDBC connection to the target has succeeded.
    ------------------------------ Commands Entered ------------------------------
    WITH
    ticket(ticketid , reportdate) AS (
    VALUES
      ( 1 , TIMESTAMP('2012-01-04-23.45.00') )
    , ( 2 , TIMESTAMP('2012-01-05-21.10.00') )
    , ( 3 , TIMESTAMP('2012-01-05-23.45.00') )
    , ( 4 , TIMESTAMP('2012-01-06-06.00.00') )
    , ( 5 , TIMESTAMP('2012-01-06-12.34.56') )
    )
    select ticketid,
    reportdate
    from ticket
    WHERE reportdate BETWEEN TIMESTAMP(current_date, '22:30:00') AND TIMESTAMP(current_date + 1 DAY , '07:30:00');
    ------------------------------------------------------------------------------
    
    TICKETID    REPORTDATE                
    ----------- --------------------------
              3 2012-01-05-23.45.00.000000
              4 2012-01-06-06.00.00.000000
    
      2 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    WITH
    ticket(ticketid , reportdate) AS (
    VALUES
      ( 1 , '2012-01-04-23.45.00' )
    , ( 2 , '2012-01-05-21.10.00' )
    , ( 3 , '2012-01-05-23.45.00' )
    , ( 4 , '2012-01-06-06.00.00' )
    , ( 5 , '2012-01-06-12.34.56' )
    )
    select ticketid,
    reportdate
    from ticket
    WHERE reportdate BETWEEN TIMESTAMP(current_date, '22:30:00') AND TIMESTAMP(current_date + 1 DAY , '07:30:00');
    ------------------------------------------------------------------------------
    
    TICKETID    REPORTDATE         
    ----------- -------------------
              3 2012-01-05-23.45.00
              4 2012-01-06-06.00.00
    
      2 record(s) selected.
    What are your DB2 version/release and platform OS?

  14. #14
    Join Date
    Jan 2012
    Posts
    10
    Quote Originally Posted by przytula_guy View Post
    yes
    time (reportdate ) > '22.00.00' and time (reportdate + 1 day ) < '07.59.00'
    this is correct : time > 22.00 will never be smaller than 07.59
    the reportdate + 1 day is useless
    you have to rewrite the query so you can match rows from day>22.00 and day+1<7.59
    can u give an example to rewrite the query
    thanks in advance

  15. #15
    Join Date
    Jan 2012
    Posts
    10
    Quote Originally Posted by tonkuma View Post
    This worked on my DB2, like...
    Code:
    ------------------------------ Commands Entered ------------------------------
    connect to SAMPLE ;
    ------------------------------------------------------------------------------
    
       Database Connection Information
    
     Database server        = DB2/NT 9.7.5
     SQL authorization ID   = DB2ADMIN
     Local database alias   = SAMPLE
    
    
    A JDBC connection to the target has succeeded.
    ------------------------------ Commands Entered ------------------------------
    WITH
    ticket(ticketid , reportdate) AS (
    VALUES
      ( 1 , TIMESTAMP('2012-01-04-23.45.00') )
    , ( 2 , TIMESTAMP('2012-01-05-21.10.00') )
    , ( 3 , TIMESTAMP('2012-01-05-23.45.00') )
    , ( 4 , TIMESTAMP('2012-01-06-06.00.00') )
    , ( 5 , TIMESTAMP('2012-01-06-12.34.56') )
    )
    select ticketid,
    reportdate
    from ticket
    WHERE reportdate BETWEEN TIMESTAMP(current_date, '22:30:00') AND TIMESTAMP(current_date + 1 DAY , '07:30:00');
    ------------------------------------------------------------------------------
    
    TICKETID    REPORTDATE                
    ----------- --------------------------
              3 2012-01-05-23.45.00.000000
              4 2012-01-06-06.00.00.000000
    
      2 record(s) selected.
    
    
    ------------------------------ Commands Entered ------------------------------
    WITH
    ticket(ticketid , reportdate) AS (
    VALUES
      ( 1 , '2012-01-04-23.45.00' )
    , ( 2 , '2012-01-05-21.10.00' )
    , ( 3 , '2012-01-05-23.45.00' )
    , ( 4 , '2012-01-06-06.00.00' )
    , ( 5 , '2012-01-06-12.34.56' )
    )
    select ticketid,
    reportdate
    from ticket
    WHERE reportdate BETWEEN TIMESTAMP(current_date, '22:30:00') AND TIMESTAMP(current_date + 1 DAY , '07:30:00');
    ------------------------------------------------------------------------------
    
    TICKETID    REPORTDATE         
    ----------- -------------------
              3 2012-01-05-23.45.00
              4 2012-01-06-06.00.00
    
      2 record(s) selected.
    What are your DB2 version/release and platform OS?
    i am using DB2 toad to connect to my database. version is V9.7

Posting Permissions

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