Showing Trends? 1

[MFritts253]

I'm working on a project in which i need to check a trend for contracts and show the precentage of contracts still N weeks away from the week the contract was opened. I enter a begin date and End Date to select my Range. Then for Week Date In my range I count the total number of Contacts, Count the number of contracts still open then i divide the still open number by the total number of contracts.
I understand how to a date range but what i do not understand is how to calculated the number of contracts 1 Week from opened date, 2 Weeks from Opened date, 3 Weeks and so on.

looks like this in excel:

Week 1 Week 2
Total C Open % Open %
6/10 16,000 15,000 95 12,000 85 .........
6/17 15,000 12,000 80 11,500 77 .......
...
....
.....

My table is
Contract
.......opendate
........closedate
.........ContractID
........CustID
..........Item

 

[rac2]

Use CASE expressions with SUMs. Write a CASE expression to calculate whether the row falls into the week 1 category, another to calculate whether the row falls into the week 2 category, etc. The result of the calculation is 1 for yes and 0 for no. SUM the 1's and 0's instead of COUNTing rows.

Access may not support the CASE expression, instead use the IIf( opendate in the week 1 range, 1, 0 ).

SELECT COUNT(*) AS TotalContracts,
       CASE
          WHEN closedate IS NULL AND DATEDIFF(week, opendate, getdate()) = 1 THEN 1
          ELSE 0
       END AS OpenOneWeek
FROM ...

Do you want the number or the dollar amount? Do you really have 15,000 contracts?

If it is the dollar amount then use the dollars when the contract should be counted in a week, but use zero otherwise.

Getting the Totals over several rows to use them in each individual row is not feasible in SQL unless your RDBMS has a CROSSTAB function as does Access.

Or rather getting the Totals and percentages will be a two step process, first write a query to get the various totals, then store them in variables and use them in expressions in the query which gets the details of the breakdown. This can be done in a stored procedure.

 

[MFritts253]

rac2,

Thanks for the help i'll give it a try and then let you know.

Sincerely,
Michael