SQL Query that Groups Multiple Date Fields

Question

I have a table that has several date fields. For example, in the defects table, I have the following: dateAssigned, dateCompleted, dateResolved. I am trying to get a query that summarizes the defects as such:

           | Number Assigned | Number Completed | Number Resolved 
-----------------------------+------------------+-----------------
  Mar-2011 |      33         |        22        |        33        
  Apr-2011 |      10         |        11        |        22
  May-2011 |      22         |        66        |        46

etc

I have come up with the following to no avail:

SELECT year(d.dateAssigned)
      ,month(d.dateAssigned)
      ,COUNT(d.dateAssigned)
      ,COUNT(d.dateCompleted)
      ,COUNT(d.dateResolved)
  FROM defect d
  GROUP BY year(d.dateAssigned), month(d.dateAssigned)
  ORDER BY year(d.dateAssigned), month(d.dateAssigned)

This works correctly for summarizing the dateAssigned defects, but not for the others. I realize this is probably due to the fact I am grouping by dateAssigned, but I dont know how else to do it.

Any help would be appreciated.

i don't see an elegant way, but you could union together three different sub-selects, grouped for each in turn. hopefully someone will post something better. — andrew cooke
– andrew cooke, Commented Aug 6, 2011 at 21:57

Conrad Frix · Accepted Answer · 2011-08-06 22:15:58Z

This is one way to do it.

SELECT YEAR(typedate), 
       MONTH(typedate), 
       SUM(CASE 
             WHEN TYPE = 'assinged' THEN 1 
             ELSE 0 
           END) number_assigned, 
       SUM(CASE 
             WHEN TYPE = 'completed' THEN 1 
             ELSE 0 
           END) number_completed, 
       SUM(CASE 
             WHEN TYPE = 'Resolved' THEN 1 
             ELSE 0 
           END) number_resolved 
FROM   (SELECT dateassigned typedate, 
               'assinged'   AS TYPE 
        FROM   sampledata 
        WHERE  dateassigned IS NOT NULL 
        UNION ALL 
        SELECT datecompleted typedate, 
               'completed'   AS TYPE 
        FROM   sampledata 
        WHERE  datecompleted IS NOT NULL 
        UNION ALL 
        SELECT dateresolved typedate, 
               'Resolved'   AS TYPE 
        FROM   sampledata 
        WHERE  dateresolved IS NOT NULL) data 
GROUP  BY YEAR(typedate), 
          MONTH(typedate) 
ORDER  BY YEAR(typedate), 
          MONTH(typedate)

The sub select creates two columns typedate and type

Which will look like this

typedate type
-------- ---------
1/1/2011 assinged
1/1/2011 assinged
1/1/2011 assinged
2/1/2011 completed
2/1/2011 completed
2/3/2011 Resolved

Then I used SUM(CASE... to pivot the data. If you're using a RDBMS that supports it you could use pivot instead

The final output looks something like this

                        Number_Assigned Number_completed Number_Resolved
----------- ----------- --------------- ---------------- ---------------
2011        1           3               0                0
2011        2           0               2                1

I'll leave it to you to do the formatting of the year and month

Neil · Accepted Answer · 2011-08-06 22:28:50Z

2

SELECT year(defectDate), month(defectDate), SUM(assigned), SUM(resolved), SUM(completed)
  FROM (
    SELECT d.dateAssigned AS defectDate, 1 AS assigned, 0 AS resolved, 0 AS completed
      FROM defect d
    UNION ALL
    SELECT d.dateCompleted AS defectDate, 0 AS assigned, 1 AS resolved, 0 AS completed
      FROM defect d
    UNION ALL
    SELECT d.dateResolved AS defectDate, 0 AS assigned, 0 AS resolved, 1 AS completed
      FROM defect d
  )
  GROUP BY year(defectDate), month(defectDate)
  ORDER BY year(defectDate), month(defectDate)

answered Aug 6, 2011 at 22:28

Neil

55.5k8 gold badges65 silver badges74 bronze badges

Comments

Mikael Eriksson · Accepted Answer · 2011-08-06 22:33:20Z

1

SELECT year(d.dateAssigned)
      ,month(d.dateAssigned)
      ,COUNT(d.dateAssigned)
      ,(SELECT count(*)
        FROM defect as dc
        WHERE month(dc.dateCompleted) = month(d.dateAssigned) and
              year(dc.dateCompleted) = year(d.dateAssigned))
      ,(SELECT count(*)
        FROM defect as dr
        WHERE month(dr.dateResolved) = month(d.dateAssigned) and
              year(dr.dateResolved) = year(d.dateAssigned))
FROM defect d
GROUP BY year(d.dateAssigned), month(d.dateAssigned)

answered Aug 6, 2011 at 22:33

Mikael Eriksson

139k22 gold badges223 silver badges293 bronze badges

Comments

ypercubeᵀᴹ · Accepted Answer · 2011-08-06 22:35:01Z

Another way would be to create a table months with fields: year, month, firstDate, nextMonthFirstDate, either on the fly or as a temp table or as permanent table (having a table stored with 100 years and 100x12 rows won't take much space and can be handy in other queries) and use something like this (which is equivalent to months LEFT JOINed three times to table defect):

SELECT
    ( SELECT COUNT(*)
      FROM defect AS d
      WHERE m.firstDate <= d.dateAssigned 
        AND d.dateAssigned < m.nextMonthFirstDate
    ) AS Number_Assigned 
  . ( SELECT COUNT(*)
      FROM defect AS d
      WHERE m.firstDate <= d.dateCompleted
        AND d.dateCompleted < m.nextMonthFirstDate
    ) AS Number_Completed
  . ( SELECT COUNT(*)
      FROM defect AS d
      WHERE m.firstDate <= d.dateResolved
        AND d.dateResolved < m.nextMonthFirstDate
    ) AS Number_Resolved
FROM months AS m
ORDER BY m.year
       , m.month

zapping · Accepted Answer · 2016-07-05 06:31:13Z

-1

This is possible if we use group by in case aliases

SELECT CASE 
         WHEN (year(Challan_Date) is null or year(TAX_INVOICE_Date)!=null) THEN year(TAX_INVOICE_Date)
         WHEN (year(Challan_Date) != null or year(TAX_INVOICE_Date) is null) THEN YEAR(Challan_Date)
         WHEN (year(Challan_Date) is null or year(TAX_INVOICE_Date)is null) THEN 'NA'
         WHEN (year(Challan_Date) != null or year(TAX_INVOICE_Date)!= null) THEN year(Challan_Date)
         ELSE year(TAX_INVOICE_Date) END as Dates_Record, TAX_INVOICE_Date, Challan_Date
FROM TransactionRecords

edited Jul 5, 2016 at 6:31

zapping

4,1266 gold badges40 silver badges56 bronze badges

answered Jul 5, 2016 at 6:06

shashank

1

Collectives™ on Stack Overflow

SQL Query that Groups Multiple Date Fields

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related