SQL COUNT par date avec plusieurs champs de date

Question

J'ai la table et la requête suivantes qui ne donnent pas le bon résultat.

Le tableau JOB comporte des dates d'ouverture, de fin et de clôture.

Je dois maintenant obtenir le nombre d'emplois ouverts, terminés et clôturés aux dates sélectionnées, en les regroupant par date et par lieu.

Veuillez m'aider à obtenir le résultat escompté ci-dessous.

+-------+-----------+------------+-----------+----------+
| JOB_id| DateOpen  | DateFinish | DateClose | Location |
+-------+-----------+------------+-----------+----------+
|   100 | 16-Dec-18 | 18-Dec-18  | 19-Dec-18 | A        |
|   101 | 16-Dec-18 | 18-Dec-18  | 19-Dec-18 | A        |
|   102 | 17-Dec-18 | 19-Dec-18  | 20-Dec-18 | C        |
|   103 | 10-Dec-18 | 11-Dec-18  | 16-Dec-18 | D        |
|   104 | 17-Dec-18 | 19-Dec-18  | 18-Dec-18 | E        |
+-------+-----------+------------+-----------+----------+

Requête :

SELECT count(DateOpen) as Opened,
       count(DateFinish) as Finised,
      count(DateClose) as Closed,
      (DateOpen) as Date 
FROM JOBS
WHERE DateOpen BETWEEN '12/16/2018' AND DATEADD(DAY, 1, '12/17/2018')
group by DateOpen

Résultat attendu :

+-----------+------+----------+--------+----------+
|   Date    | Open | Finished | Closed | Location |
+-----------+------+----------+--------+----------+
| 16-Dec-18 |    2 |        0 |      0 | A        |
| 16-Dec-18 |    0 |        0 |      1 | D        |
| 17-Dec-18 |    1 |        0 |      0 | C        |
| 17-Dec-18 |    1 |        0 |      0 | E        |
+-----------+------+----------+--------+----------+

Answer 1

Vous pourriez regrouper toutes les dates d'ouverture, de fin et de clôture dans une seule colonne et en faire une jointure gauche de votre tableau des emplois :

DECLARE @date1 AS DATE = '2018-12-16';
DECLARE @date2 AS DATE = '2018-12-17';

WITH dates(date) AS (
    SELECT DateOpen FROM jobs
    UNION
    SELECT DateFinish FROM jobs
    UNION
    SELECT DateClose FROM jobs
)
SELECT dates.date
     , Location
     , COUNT(CASE WHEN dates.date = DateOpen THEN 1 END) AS Opened
     , COUNT(CASE WHEN dates.date = DateFinish THEN 1 END) AS Finished
     , COUNT(CASE WHEN dates.date = DateClose THEN 1 END) AS Closed
FROM dates
LEFT JOIN jobs ON dates.date IN (DateOpen, DateFinish, DateClose)
WHERE dates.date BETWEEN @date1 AND @date2
GROUP BY dates.date
       , Location

Résultat :

| date       | Location | Opened | Finished | Closed |
|------------|----------|--------|----------|--------|
| 16/12/2018 | A        | 2      | 0        | 0      |
| 16/12/2018 | D        | 0      | 0        | 1      |
| 17/12/2018 | C        | 1      | 0        | 0      |
| 17/12/2018 | E        | 1      | 0        | 0      |

Démonstration sur DB Fiddle

Answer 2

Vous pouvez utiliser la requête ci-dessous pour obtenir les résultats souhaités.

select coalesce(t1.date, t2.date, t3.date) as date, t1.Opened, t2.Finished, t3.Closed,
coalesce(t1.location, t2.location, t3.location) as location
from
(SELECT Convert(date,DateOpen) as Date ,count(JobID) as Opened, location      
FROM JOBS
WHERE DateOpen BETWEEN '12/16/2018' AND DATEADD(DAY, 1, '12/17/2018')
group by Convert(date,DateOpen), location 
) t1
Full join 
(SELECT Convert(date,DateFinish) as Date ,count(JobID) as Finished, location      
FROM JOBS
WHERE DateFinish BETWEEN '12/16/2018' AND DATEADD(DAY, 1, '12/17/2018')
group by Convert(date,DateFinish), location 
) t2  ON(t1.date = t2.date and t1.location = t2.location)
FULL JOIN
(SELECT Convert(date,DateClose) as Date ,count(JobID) as Closed, location      
FROM JOBS
WHERE DateClose BETWEEN '12/16/2018' AND DATEADD(DAY, 1, '12/17/2018')
group by Convert(date,DateClose), location 
) t3  ON(t2.date = t3.date and t2.location = t3.location)

Answer 3

Tout d'abord, je vous recommande de ne plus stocker les dates sous forme de texte et d'utiliser les types de données appropriés.

Pour ce faire, dressez une liste de toutes les dates et de tous les lieux en sélectionnant chacune des colonnes de dates et de lieux et en les réunissant en un seul résultat (first cte - allDates). Ensuite, nous avons besoin d'une liste distincte (deuxième catégorie - agrégée) pour effectuer une sélection et compter le nombre de lignes de votre tableau qui correspondent aux valeurs actuelles de la date et du lieu.

Voici la solution complète :

declare @JOBS table(JOB_ID int, DateOpen varchar(10), DateFinish varchar(10), DateClose varchar(10), Location varchar(5))
insert into @JOBS values
(   100, '16-Dec-18', '18-Dec-18', '19-Dec-18', 'A'),
(   101, '16-Dec-18', '18-Dec-18', '19-Dec-18', 'A'),
(   102, '17-Dec-18', '19-Dec-18', '20-Dec-18', 'C'),
(   103, '10-Dec-18', '11-Dec-18', '16-Dec-18', 'D'),
(   104, '17-Dec-18', '19-Dec-18', '18-Dec-18', 'E')

;with allDates as (
select convert(date, DateOpen) as [Date], Location from @JOBS
union
select convert(date, DateFinish), Location from @JOBS
union
select convert(date, DateClose), Location from @JOBS
),
aggregated as (
    select [Date], Location
    from allDates
    group by [Date], Location
)
select
    a.Date
    , (select count(*) from @JOBS where a.[Date] = DateOpen and a.Location = Location) [Open]
    , (select count(*) from @JOBS where a.[Date] = DateFinish and a.Location = Location) Finished
    , (select count(*) from @JOBS where a.[Date] = DateClose and a.Location = Location) Closed
    , Location
from aggregated a
where a.Date between '20181216' and '20181217'

Answer 4

Vous pouvez utiliser une instruction case avec sum,

    SELECT  Convert(date,DateOpen) as Date ,      
           sum(case when DateOpen =DateOpen then 1 else 0 end) as Opened,
           sum(case when DateFinish=DateOpen then 1 else 0 end) as Finised,
           sum(case when DateClose=DateOpen then 1 else 0 end) as Closed,
           Location

    FROM JOBS
    WHERE DateOpen BETWEEN '12/16/2018' AND DATEADD(DAY, 1, '12/17/2018')
    group by Convert(date,DateOpen),Location

    UNION

    SELECT  Convert(date,DateClose) as Date ,      
           sum(case when DateOpen =DateClose then 1 else 0 end) as Opened,
           sum(case when DateFinish=DateClose then 1 else 0 end) as Finised,
           sum(case when DateClose=DateClose then 1 else 0 end) as Closed,
           Location

    FROM JOBS
    WHERE DateClose BETWEEN '12/16/2018' AND DATEADD(DAY, 1, '12/16/2018')
    group by Convert(date,DateClose),Location

Answer 5

Vous pouvez essayer le code suivant, en utilisant SUM qui calcule l'état des processus aux dates indiquées :

SELECT Convert(date1,DateOpen) as Date,
       sum(case
            when DateOpen = Convert(date1,DateOpen) then 1
            else 0
           end) as Open,
       sum(case
            when DateFinish = Convert(date1,DateOpen) then 1
            else 0
           end) as Finished,
      sum(case
            when DateClose = Convert(date1,DateOpen) then 1
            else 0
           end) as Closed,
      Location
FROM JOBS
group by Location
UNION ALL  
SELECT Convert(date2,DateOpen) as Date,
       sum(case
            when DateOpen = Convert(date2,DateOpen) then 1
            else 0
           end) as Open,
       sum(case
            when DateFinish = Convert(date2,DateOpen) then 1
            else 0
           end) as Finished,
      sum(case
            when DateClose = Convert(date2,DateOpen) then 1
            else 0
           end) as Closed,
      Location
FROM JOBS
group by Location;

MODIFIÉ : date1 y date2 sont des paramètres d'entrée.