CODEWITHSUNDEEP
sql
j0nnyf1ve
j0nnyf1ve

Reputation: 339

SQL Query that Groups Multiple Date Fields

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.

Upvotes: 6

Views: 1627

Answers (5)

shashank
shashank

Reputation: 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

Upvotes: -1

ypercubeᵀᴹ
ypercubeᵀᴹ

Reputation: 115530

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

Upvotes: 0

Mikael Eriksson
Mikael Eriksson

Reputation: 138960

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)

Upvotes: 1

Neil
Neil

Reputation: 55392

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)

Upvotes: 2

Conrad Frix
Conrad Frix

Reputation: 52645

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

Upvotes: 2

Related Questions