May 17, 2009 at 2:40 pm
Hi,
I have a table named "historic" where i save all requests' status. I want to calculate the difference between two dates : date when the status is "new" and date when the status is "closed". Then, i want to group the requests by duration of treatment.
Example :
-number of requests -------duration of treatement
----------30 -----------------< 1 day
----------10 ------------------< 1 hour
----------20 ------------------< 1 week
Is it possible to have this result ?
any help or any comment will be highly appreciated.
May 18, 2009 at 1:09 am
rafii.mili (5/17/2009)
-number of requests -------duration of treatement----------30 -----------------< 1 day
----------10 ------------------< 1 hour
----------20 ------------------< 1 week
Is it possible to have this result ?
Yes,Possible
ARUN SAS
May 18, 2009 at 2:18 am
This may not be the best way, and without knowing your exact record structure this can only be a hint, but it will give you an idea to try out.
WITH Diffs AS (
SELECT DATEDIFF(hh,date1,date2) AS DiffHours,
.....
FROM .....
), DiffRange AS (
SELECT CASE DiffHours
WHEN < 24 THEN '< 1 Day'
WHEN <168 THEN '< 1 Week'
ELSE 'Longer'
END AS Duration,
.....
FROM Diffs )
SELECT COUNT(...), Duration
FROM DiffRange
GROUP BY Duration
May 18, 2009 at 1:04 pm
Thanks for your help, but it's not working.:crying:
WITH Diffs AS (
SELECT DATEDIFF(hh,date1, getdate()) AS DiffHours,
FROM historique_dde
), DiffRange AS (
SELECT CASE
WHEN DiffHours < 24 THEN '< 1 Day'
WHEN DiffHours <168 THEN '< 1 Week'
ELSE 'Longer'
END AS Duration,
FROM Diffs )
SELECT COUNT(historique_dde.DemandeID), Duration
FROM DiffRange
GROUP BY Duration
-------------------------------------
an error is displayed when i try to work with it:
[frensh]
Msg 4104,Niveau 16 Etat 1 , ligne 1
L'identificateur en plusieurs parties 'historique_dde.DemandeID' ne peut pas être lié
i translate it in english :
The identifier in several parts ' historique_dde.DemandeID ' can not be bound
🙁
May 18, 2009 at 1:52 pm
you will have to bubble up "DemandeID" column from the previous CTEs
* Noel
May 19, 2009 at 2:14 am
really, great thanks for your help.
I solved my probleme. I used this code:
WITH
Diffs AS (
SELECT DATEDIFF(hh,demande_help_desk.DateEnvoi,historique_dde.date_modif_dde ) AS DiffHours ,historique_dde.DemandeID
FROM historique_dde INNER JOIN
StatutDde ON historique_dde.StatutDdeID = StatutDde.StatutDdeID INNER JOIN
demande_help_desk ON historique_dde.DemandeID = demande_help_desk.DemandeID
where (StatutDde.LibelleStatut = 'Cloturée')
) ,
DiffRange AS (
SELECT CASE
WHEN DiffHours < 24 THEN ' < 1 day'
WHEN DiffHours < 72 THEN ' < 3 days'
WHEN DiffHours < 168 THEN ' = 1 week'
END AS Duration,historique_dde.DemandeID
FROM Diffs
INNER JOIN historique_dde ON historique_dde.DemandeID = Diffs.DemandeID
)
SELECT count (distinct(historique_dde.DemandeID) ) as 'Nombre de demandes', Duration
FROM DiffRange
INNER JOIN historique_dde ON historique_dde.DemandeID = DiffRange.DemandeID
GROUP BY Duration
order by Duration
It's work perfectly.:cool::cool::cool:
May 19, 2009 at 3:04 am
Bravo!
code informatique exécution par excellence.
- In case Google didn't translate this correctly - I meant to say
Well done, thats a gread bit of coding. 😀
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply