T-SQL Question - Counting and Average

Question

I have a set of data that consists of a filenbr, open date and close date.

I need to produce a summary table similar to the below, i need to count how many files belong to each day period, but i need those greater than 20 grouped together. I know how to get the datediff, what i'm stumbling on is how to get the 20+ and the % column

1 day - 30 files - 30%
3 days - 25 files - 25%
10 days - 5 files - 5%
13 days - 20 files - 20%
>= 20 days - 20 files - 20%

Answer 1

suppose you have a table named dayFile with the following columns

Table DayFile

days - files
1    - 10
1    - 5
1    - 15
3    - 20
3    - 5
10   - 5
13   - 20
20   - 5
22   - 5
28   - 10

Then you could do the following

SELECT
    SummaryTable.Day,
    SUM(SummaryTable.Files) as SumFiles,
    Sum(SummaryTable.Files) / SummaryTable.TotalFiles
  FROM 
    (SELECT 
      CASE WHEN (days >= 20) THEN
        20
      ELSE DF.days END as Day
      files,
      (SELECT Count(*) FROM DayFile DFCount) as TotalFiles
    FROM DayFile DF) SummaryTable
Group By SummaryTable.Day

---

EDITED:

SELECT
    SummaryTable.Day,
    SUM(SummaryTable.Files) as SumFiles,
    Sum(SummaryTable.Files) / (SELECT Count(*) FROM DayFile DFCount)
FROM 
    (SELECT 
      CASE WHEN (days >= 20) THEN
        20
      ELSE DF.days END as Day
      files
    FROM DayFile DF) SummaryTable
Group By SummaryTable.Day

Answer 2

You are unclear as to how the ranges are determined (e.g. does "3 days mean < 3 days, <= 3 days, = 3 days, > 3 days or >= 3 days?). If you are using SQL Server 2005 and higher, you get your results like so:

With PeriodLength As
    (
    Select DateDiff(d, OpenDate, CloseDate) As DiffDays
    From Table
    )
    , Ranges As 
    (
    Select Case
            When DiffDays < 3 Then 'Less than 3 Days'
            When DiffDays >= 3 And DiffDays < 10 Then 'Less than 10 Days'
            When DiffDays >= 10 And DiffDays < 13 Then 'Less than 13 Days'
            When DiffDays >= 13 And DiffDays < 20 Then 'Less than 20 Days'
            When DiffDays >= 20 Then 'Greater than 20 days'
            End As Range
    From PeriodLength   
    )
Select Range
    , Count(*) As FileCount
    , Count(*) * 100.000 / (Select Count(*) From Ranges) As Percentage
From Ranges 
Group By Range