SUM CASE WHEN CLAUSE

Question

I have the below Query and I want the Ageing profile to show as a new column my ageing profile is 0-30, 31-60, 61-90, 91-120, 121-180, 181-365, 365+

I basically want the results to appear lie this with the Net due appearing in the appropriate ageing bracket.

BU   0-30   31-60   61-90   91-120   121-180   181-365   365+
--------------------------------------------------------------
A    
B
C

Query:

select
 BU,
 Ageing,
 sum(NetDue) as Netdue
 from [dbo].[vw_FACT_CONSOL_CREDITORS]
 where date = @date
 Group by BU, Ageing
Order by BU ;

I am new to SQL and just trying to learn so apologise as this is basic stuff Thanks

Does the "Ageing" field already exist in your view, and you're just trying to display it in columns like that? Or do you only have a date field, and you need to both properly group the data, and display in the correct column? — BradC, Oct 16 '17 at 15:53
This should really be asked over in Stack Overflow instead of in the DBA section. However, you should look into PIVOT queries for a solution to your problem here. — Laughing Vergil, Oct 16 '17 at 15:55
@LaughingVergil "Advanced queries" are on-topic for dba.SE. https://dba.stackexchange.com/help/on-topic I think range groupings and pivot columns are definitely not beginner SQL. — BradC, Oct 16 '17 at 15:57

score 3 · Answer 1 · answered Oct 16 '17 at 17:48

Assuming that the Ageing values match the names of the columns in your expected output, then this should work:

SELECT  *
FROM    (
    SELECT  BU, Ageing, NetDue
    FROM    [dbo].[vw_FACT_CONSOL_CREDITORS]
    WHERE   Date = @date
) src
PIVOT
(
    SUM(NetDue)
    FOR Ageing IN ([0-30], [31-60], [61-90], [91-120], [121-180], [181-365], [365+])
) piv

If they are not, then you will need to add some logic to the interior select statement to convert Ageing from whatever it is into those string values (e.g. '0-30', '365+').

As an example to check this, I did the following:

CREATE TABLE x (
    BU INT,
    NetDue SMALLMONEY,
    Ageing VARCHAR(10),
    Date DATE
)

INSERT x
VALUES 
  (1, 100, '31-60', '2017-04-01'),
  (2, 25.47, '31-60', '2017-04-01'),
  (1, 90.17, '31-60', '2017-05-01'),
  (1, 16.42, '181-365', '2017-04-01'),
  (2, 99.99, '365+', '2017-04-01')

SELECT  *
FROM    (
    SELECT  BU, Ageing, NetDue
    FROM    x
    WHERE   Date = '2017-04-01'
) src
PIVOT
(
    SUM(NetDue)
    FOR Ageing IN ([0-30], [31-60], [61-90], [91-120], [121-180], [181-365], [365+])
) piv

which produced the output:

BU  0-30    31-60   61-90   91-120  121-180 181-365 365+
1   NULL    100.00  NULL    NULL    NULL    16.42   NULL
2   NULL    25.47   NULL    NULL    NULL    NULL    99.99

All the information (and more) for determining this logic can be found here.

SUM CASE WHEN CLAUSE

1 Answers1