| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
|
01-05-12, 02:51
|
|
Registered User
|
|
Join Date: Jan 2012
Posts: 10
|
|
|
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
|
|
01-05-12, 03:06
|
|
Registered User
|
|
Join Date: Apr 2006
Location: Belgium
Posts: 1,159
|
|
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
DB2 UDB LUW Certified V7-V8-V9-V9.7 DB Admin - Dprop..
Information Server Datastage Certified
http://www.infocura.be
|
|
01-05-12, 03:11
|
|
Registered User
|
|
Join Date: Mar 2003
Posts: 86
|
|
|
|
Quote:
Originally Posted by Stranger_G
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
|
|
01-05-12, 03:17
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
|
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'
Quote:
|
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.
|
|
01-05-12, 03:24
|
|
Registered User
|
|
Join Date: Jan 2012
Posts: 10
|
|
Quote:
Originally Posted by tonkuma
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
|
|
01-05-12, 03:29
|
|
Registered User
|
|
Join Date: Jan 2012
Posts: 10
|
|
Quote:
Originally Posted by lelle12
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
|
|
01-05-12, 03:31
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
You already have an anawer.
Quote:
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
|
Quote:
|
But i do not want to pass a datetime value
|
Why?
|
|
01-05-12, 03:32
|
|
Registered User
|
|
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
|
|
01-05-12, 03:43
|
|
Registered User
|
|
Join Date: Mar 2003
Posts: 86
|
|
using current_date as input parameter:
where report_date between timestamp(current_date) + 22 hours
and timestamp(current_date + 1 day) + 8 hours
/Lennart
|
|
01-05-12, 03:50
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Try something like...
WHERE tb1.report_date BETWEEN TIMESTAMP(current_date, '22:30:00') AND TIMESTAMP(current_date + 1 DAY , '07:30:00')
|
|
01-05-12, 05:17
|
|
Registered User
|
|
Join Date: Jan 2012
Posts: 10
|
|
Quote:
Originally Posted by tonkuma
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
|
|
01-05-12, 05:29
|
|
Registered User
|
|
Join Date: Jan 2012
Posts: 10
|
|
Quote:
Originally Posted by lelle12
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
|
|
01-05-12, 05:46
|
|
Registered User
|
|
Join Date: Feb 2008
Location: Japan
Posts: 2,193
|
|
Quote:
Originally Posted by Stranger_G
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?
|
|
01-05-12, 05:52
|
|
Registered User
|
|
Join Date: Jan 2012
Posts: 10
|
|
Quote:
Originally Posted by przytula_guy
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
|
|
01-05-12, 05:56
|
|
Registered User
|
|
Join Date: Jan 2012
Posts: 10
|
|
Quote:
Originally Posted by tonkuma
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
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
calculating night shifts
