Multiple dates in a fact record

Question

I have a fact table that tracks multiple actions and dates for a specific population. I am having a hard time creating a query that returns the resultset I would expect for this table. Here is a sample of this table:

Record  applicant_count applicant_date  accepted_count  accepted_date   registered_count    registered_date
1       1   3            03/01/2014                
2       1   2            02/01/2014      1   3            03/01/2014      1   5                05/05/2014
3       1   1            01/01/2014      1   5            05/01/2014      1   5                05/01/2014
4       1   2            02/01/2014      1   4            04/15/2014       
5       1   4            04/01/2014

There are 5 records with 5 applications, 3 accepted, and 2 registered records for the entire set. I need to know how to write a query that can calculate these counts for a specific date, say 4/20/2014. On that day I would like to have the query return 5 applicants, 2 accepted, and 0 registered. Any help would be appreciated.

Do you want the count to be a running total of previous days, or are you just interested in activities on that specific day? — Stuart Ainsworth, May 07 '14 at 18:00
I'm looking to get a running total for all previous days based on a given date. The complexity for me summing up parts of the record based on the date. For example, any query generated for a date in April 2014 should not show any count for registered, since those events happened in May 2014. — user3613294, May 07 '14 at 18:04

score 0 · Answer 1 · answered May 07 '14 at 19:14

Looking at your sample data, I am not exactly sure what is the condition for grouping and counting records based on a date.

This is full working example, showing how you can add filter criteria in aggregate function:

DECLARE @DataSource TABLE
(
     [Record] TINYINT
    ,[applicant_count] TINYINT
    ,[applicant_date] DATETIME2
    ,[accepted_count] TINYINT
    ,[accepted_date] DATETIME2
    ,[registered_count] TINYINT
    ,[registered_date] DATETIME2
)

INSERT INTO @DataSource ([Record], [applicant_count], [applicant_date], [accepted_count], [accepted_date], [registered_count], [registered_date])
VALUES (1, 11, '03/01/2014', NULL,  NULL,  NULL, NULL)
      ,(2, 12, '02/01/2014', 12, '03/01/2014',  11, '05/05/2014')
      ,(3, 13, '04/05/2014', 13, '05/01/2014',  10, '05/01/2014')
      ,(4, 14, '02/01/2014', 11, '04/05/2014',  NULL, NULL)
      ,(5, 15, '04/05/2014', NULL,  NULL, NULL, NULL)

DECLARE @Date DATETIME2 = '4/20/2014'

SELECT SUM(IIF([applicant_date] = '4/05/2014', [applicant_count], 0)) AS [applicant_date]
      ,SUM(IIF([applicant_date] = '4/05/2014', [accepted_count], 0)) AS [accepted_count]
      ,SUM(IIF([applicant_date] = '4/05/2014', [registered_count], 0)) AS [registered_count]
FROM @DataSource

Pay attention to the condition in the SUM:

IIF([applicant_date] = '4/05/2014', [applicant_count], 0)

It means, if the date are equals we are summing the results, if not, adding 0. And you are free to change the condition as you like.

Hope this help.

Multiple dates in a fact record

1 Answers1