quote:

---

Originally posted by masond

Hi all

Hope your well

I have to produce a report, but not sure of the best way to get the required results
Aim -
to count how many [FDMSAccountNo] there are per given [Month_end_date], and then do a case when on the[Retail_amount]

For eg

10 Fdmsaccountno in Jan
Those 10 Fdmsaccountno vary in [Retail_amount]
I have 5 fdmsaccountno which are between %0 & £5
2fdmaccounno beterrn £6& £10
3 fdmsaccountno £10>


My query is
SELECT [FDMSAccountNo]
,[Month_end_date]
,[Retail_amount]
FROM [FDMS].[dbo].[Fact_Fee_History]
where [Fee_Sequence_Number] = '32r'
and Month_end_date between '2013-01-01' and '2013-12-01'

---

SELECT  [Month_end_date] ,
        CASE WHEN [Retail_amount] <= 5 THEN 1
             ELSE 0
        END AS LessThan5 ,
        CASE WHEN [Retail_amount] > 5
                  AND [Retail_amount] <= 10 THEN 1
             ELSE 0
        END AS LessThan10 ,
        CASE WHEN [Retail_amount] > 10 THEN 1
             ELSE 0
        END AS GreaterThan10
FROM    [FDMS].[dbo].[Fact_Fee_History]
WHERE   [Fee_Sequence_Number] = '32r'
        AND Month_end_date BETWEEN '2013-01-01'
                           AND     '2013-12-01'
GROUP BY [Month_end_date]   

SELECT  [Month_end_date] ,
        [blue]SUM(CASE WHEN [Retail_amount] <= 5 THEN 1
             ELSE 0
        END) AS LessThan5 ,
        SUM(CASE WHEN [Retail_amount] > 5
                  AND [Retail_amount] <= 10 THEN 1
             ELSE 0
        END) AS LessThan10 ,
        SUM(CASE WHEN [Retail_amount] > 10 THEN 1
             ELSE 0
        END) AS GreaterThan10,
        COUNT(FDMSAccountNo) AS AccntCount
FROM    [FDMS].[dbo].[Fact_Fee_History]
WHERE   [Fee_Sequence_Number] = '32r'
        AND Month_end_date BETWEEN '2013-01-01'
                           AND     '2013-12-01'
GROUP BY [Month_end_date]

quote:

---

Originally posted by masond

HI Visakh16

My end query ended up liek this

SELECT
Fdmsaccountno ,
Case when [Retail_amount] between 0 and 4.99 then 1 else 0 end as '£0 to £4.99',
Case when [Retail_amount] between 5 and 6.99 then 1 else 0 end as '£5 to £6.99',
Case when [Retail_amount] between 7 and 7.49 then 1 else 0 end as '£7 to £7.49',
Case when [Retail_amount] between 7.50 and 7.99 then 1 else 0 end as '£7.50 to £7.99',
Case when [Retail_amount] between 8 and 9.99 then 1 else 0 end as '£8 to £9.99',
Case when [Retail_amount] between 10 and 11.99 then 1 else 0 end as '£10 to 11.99',
Case when [Retail_amount] between 12 and 14.99 then 1 else 0 end as '£12 to 14.99',
Case when [Retail_amount] between 15 and 19.99 then 1 else 0 end as '£15 to 19.99',
Case when [Retail_amount] > 20 then 1 else 0 end as '£20+'
FROM [FDMS].[dbo].[Fact_Fee_History]
WHERE [Fee_Sequence_Number] = '32r'
AND Month_end_date BETWEEN '2013-01-01' AND '2013-12-01'
GROUP BY FDMSAccountNo,[Retail_amount]

---