CODEWITHSUNDEEP
sqlsql-server
Burnt Toast
Burnt Toast

Reputation: 13

Combine multiple date fields into one on query

I have a requirement to create a report that counts a total from 2 date fields into one. A simplified example of the table I'm querying is:

ID, FirstName, LastName, InitialApplicationDate, UpdatedApplicationDate

I need to query the two date fields in a way that creates similar output to the following:

 Date | TotalApplications

I would need the date output to include both InitialApplicationDate and UpdatedApplicationDate fields and the TotalApplications output to be a count of the total for both types of date fields. Originally I thought maybe a Union would work however that returns 2 separate records for each date. Any ideas how I might accomplish this?

Upvotes: 1

Views: 456

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269483

The simplest way, I think, is to unpivot using apply and then aggregate:

select v.thedate, count(*)
from t cross apply
     (values (InitialApplicationDate), (UpdatedApplicationDate)) v(thedate)
group by v.thedate;

You might want to add where thedate is not null if either column could be NULL.

Note that the above will count the same application twice, once for each date. That appears to be your intention.

Upvotes: 1

Related Questions