0

I would like some help please with the following.

Here is my SQL Server 2014 table schema:

Date [DateTime]
Invoice1 [float]
Invoice2 [float]
Operator [nvarchar]
Client [nvarchar]

In my Operator column I have 2 values: Operator 1 and Operator 2.

I would like to achieve the following output (in columns) in a single query:

Date
Invoice 1 of Operator 1
Invoice 1 of Operator 2
Invoice 2 of Operator 1
Invoice 2 of Operator 2
Count of Clients

I tried the following query but it returns an error: Aggregates on the right side of an APPLY cannot reference columns from the left side

SELECT *
FROM
(
    SELECT
        [Date],
        CASE
            when col = 'Invoice 1' then col + ' of ' + [Operator]
            when col = 'Invoice 2' then col + ' of ' + [Operator]
            when col = 'Clients' then col
    END AS pivot_col,
    CASE
        when col = 'Invoice 1' then SUM(value)
        when col = 'Invoice 2' then SUM(value)
        when col = 'Clients' then value
    END AS myvalues
FROM [table]
CROSS APPLY
(
VALUES
    ([Invoice1], 'Invoice 1'),
    ([Invoice2], 'Invoice 2'),
    (COUNT([Client]), 'Clients')
) x (value, col)
GROUP BY
    [Date],
    col + ' of ' + [Operator],
    col
) AS [Source]
PIVOT
(
    SUM(myvalues)
    FOR pivot_col IN (
        [Invoice 1 of Operator 1],
        [Invoice 1 of Operator 2],
        [Invoice 2 of Operator 1],
        [Invoice 2 of Operator 2],
        [Clients]
    )
) AS [Pivot]

I assume the error is due to the fact that I am using COUNT([Client]) inside the CROSS APPLY VALUES block. However I am not able to solve the problem.

Any ideas please?

Thanks Reda

[EDIT]

Bonjour Julien,

As requested:

Sample DB Data:

  Date | Invoice1 | Invoice2 |  Operator  |  Client  |  Entity
----------------------------------------------------------------
  JAN  |    10    |    15    | Operator 1 | Client 1 | Entity A
  FEB  |    20    |    25    | Operator 1 | Client 1 | Entity B
  JAN  |    30    |    35    | Operator 1 | Client 2 | Entity C
  FEB  |    40    |    45    | Operator 1 | Client 2 | Entity D
  JAN  |    50    |    55    | Operator 2 | Client 1 | Entity E
  FEB  |    70    |    75    | Operator 2 | Client 2 | Entity F
  JAN  |    80    |    85    | Operator 2 | Client 1 | Entity G
  FEB  |    90    |    95    | Operator 2 | Client 2 | Entity H

Desired output (please scroll to the right):

  Date  | Sum of Invoice 1 for Operator 1 | Sum of Invoice 2 for Operator 1 | Sum of Invoice 1 for Operator 2 | Sum of Invoice 2 for Operator 2 | Count of Distinct Clients | Count of Distinct Entities
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
   JAN  |             40                  |             50                  |         130                     |         140                     |       2                   |       4
   FEB  |             60                  |             70                  |         160                     |         170                     |       2                   |       4
5
  • can you add sample rows and expected output? – Julien Vavasseur Nov 9 '15 at 12:58
  • Hi Julien, Please see edited question. Thanks! – Reda Nov 9 '15 at 14:11
  • is date a datetime (and real date) or a string with JAN, FEB ? – Julien Vavasseur Nov 9 '15 at 14:25
  • Date in Sample Data and Expected Output is real DateTime (full format) and shall remain as such. I wrote it in short month format for display purposes. – Reda Nov 9 '15 at 14:28
  • Julien, I am sorry the expected output is wrong. I will rewrite it. – Reda Nov 9 '15 at 14:33
1

This works although I am not sure how to handle the date (sample is not a date):

Select DATENAME(month, [Date]) as 'date'
    , SUM(Case When Operator = 'Operator 1' then Invoice1 end) as 'Sum of Invoice 1 for Operator 1'
    , SUM(Case When Operator = 'Operator 1' then Invoice2 end) as 'Sum of Invoice 2 for Operator 1'
    , SUM(Case When Operator = 'Operator 2' then Invoice1 end) as 'Sum of Invoice 1 for Operator 2'
    , SUM(Case When Operator = 'Operator 2' then Invoice2 end) as 'Sum of Invoice 2 for Operator 2'
    , COUNT(distinct Client) as 'Count of Distinct Clients'
    , COUNT(distinct Entity) as 'Count of Distinct Entities'
From @data 
Group By [date];

Output:

date        | Sum of Invoice 1 for Operator 1   | Sum of Invoice 2 for Operator 1   | Sum of Invoice 1 for Operator 2   | Sum of Invoice 2 for Operator 2   | Count of Distinct Clients | Count of Distinct Entities
January     | 40                                | 50                                | 130                               | 140                               | 2                         | 4
February    | 60                                | 70                                | 160                               | 170                               | 2                         | 4

Your data (replaced by real dates):

Declare @data TABLE ([Date] datetime, Invoice1 int, Invoice2 int, Operator varchar(10), Client varchar(8), Entity varchar(10));
INSERT INTO @data(Date, Invoice1, Invoice2, Operator, Client, Entity)
VALUES
    ('20150101', 10, 15, 'Operator 1', 'Client 1', 'Entity A'),
    ('20150201', 20, 25, 'Operator 1', 'Client 1', 'Entity B'),
    ('20150101', 30, 35, 'Operator 1', 'Client 2', 'Entity C'),
    ('20150201', 40, 45, 'Operator 1', 'Client 2', 'Entity D'),
    ('20150101', 50, 55, 'Operator 2', 'Client 1', 'Entity E'),
    ('20150201', 70, 75, 'Operator 2', 'Client 2', 'Entity F'),
    ('20150101', 80, 85, 'Operator 2', 'Client 1', 'Entity G'),
    ('20150201', 90, 95, 'Operator 2', 'Client 2', 'Entity H')
;
1
  • Pourquoi compliquer quand on peut faire simple ! Thank you Julien, I went the complicated way of trying to pivot/unpivot but your solution is so simple. – Reda Nov 9 '15 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.