Counting The Number Of Apps Using A Date Field

[TJVFree]

I think I almost got it,
So with the code below, its counting the apps “Total”[CORRESPONDENT_PURCHASE_DATE] “that came in on that day”. Now if I could sum it up so that there would be new fields like Jan, Feb, Mar, April ect. And the totals apps for that month would fall under one of these new fields.


Rep / Jan /Feb/ Mar/ April/ 3Month
Tim White 15 / 2 / 15/ 51 / 83
Billy Joe 16 / 5 / 25 / 41 / 87

I guess it would sound something like this

Iff the CORRESPONDENT_PURCHASE_DATE is between 01/01/2010 and 01/30/2010 then put in new table called Jan and count as one.

So I’m taking all the dates from CORRESPONDENT_PURCHASE_DATE and making them so I can count each date as one

Also with the qry below, that will all the totals that fall into those dates

SELECT [master table].orig_code, [master table].branch, [master table].NAME_WHOLESALE_ACCOUNT_EXEC, Sum(IIf([CORRESPONDENT_PURCHASE_DATE]>DateAdd("m",-3,Date()),[Amount_Actual_Loan],0)) AS 3Month, Sum(IIf([CORRESPONDENT_PURCHASE_DATE]>DateAdd("m",-6,Date()),[Amount_Actual_Loan],0)) AS 6Month, Sum(IIf([CORRESPONDENT_PURCHASE_DATE]>DateAdd("m",-9,Date()),[Amount_Actual_Loan],0)) AS 9Month, Sum(IIf([CORRESPONDENT_PURCHASE_DATE]>DateAdd("m",-12,Date()),[Amount_Actual_Loan],0)) AS 12Month, Count([Master Table].CORRESPONDENT_PURCHASE_DATE) AS CountOfApps
FROM [master table]
WHERE (((IsDate([CORRESPONDENT_PURCHASE_DATE]))=True))
GROUP BY [master table].orig_code, [master table].branch, [master table].NAME_WHOLESALE_ACCOUNT_EXEC
ORDER BY [master table].orig_code;

Thanks again for all your help

TCB

 

[TheBugSlayer]

Hey Corey, you may want to look at the PIVOT/UNPIVOT statements.

Good luck.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)