Query for generating month level & YTD level data

Question

Write SQL Queries to -
Given - Day level Sales Data for different Companies
1) Create Month Level , YTD Level Data for the given data.
YTD- Year to Date
YTD(Mar) = Jan + Feb +Mar
2) Create Overall level on the basis of Company for the data created in Step 1. Mark it as "Industry"
Eg: Industry = CompA + CompB + CompC + CompD
3) Calculate Month and YTD Level Share( both Value , Volume) for the data created after Step 2.
Calculation of Share - Comp / Industry.

I get that we can use partition by in over clause but in general i don't understand the question.

schema:

[Period] - date
[Company]- nvarchar
[Metric] - nvarchar
[Values] - Float

Period      Company Metric           Values

01-01-2018  CompA   Sales Vol   72947.30664

02-01-2018  CompA   Sales Vol   21553.65941

03-01-2018  CompA   Sales Vol   777.6831962

04-01-2018  CompA   Sales Vol   34871.11234

05-01-2018  CompA   Sales Vol   42598.06526

I tried using partition by month & year. but I'm not clear if this is what is expected.

 SELECT YEAR([Period]) AS Year,
        MONTH([Period]) as month,
        MTD  = SUM([Values]) OVER (PARTITION BY year([period]),month([period]) order by period),
        YTD  = SUM([Values]) OVER (PARTITION BY Year([period]) order by period)
        FROM  DP

@DhruvJoshi : I want to know how to do this "Create Overall level on the basis of Company for the data created in Step 1. Mark it as "Industry" " after creating YTD data — chahak joshi, May 11 '19 at 12:01

score 0 · Accepted Answer · answered May 10 '19 at 09:13

From the sounds of the question it seems like what is being asked is the expression of layers of totals.

Your query works well and does everything required except for the total for the industry.

The query below uses "rollup" which allows you to generate hierarchical groupings based on the columns running left to right. As such you will get a grand total for all industries, a total for all years per company, and a total for all months per year per company.

declare @sales table

(
    [Period] date,
    [Company] nvarchar(50),
    [Metric] nvarchar(50),
    [Values] float
);

insert @sales ([Period], [Company], [Metric], [Values])
values
('01-01-2018',  'CompA',   'Sales Vol',   72947.30664),
('02-01-2018',  'CompA',   'Sales Vol',   21553.65941),
('03-01-2018',  'CompA',   'Sales Vol',   777.6831962),
('04-01-2018',  'CompA',   'Sales Vol',   34871.11234),
('05-01-2018',  'CompA',   'Sales Vol',   42598.06526);

SELECT coalesce(Company,'Industry') as Company, coalesce(cast(YEAR([Period]) as nvarchar(50)), 'All Years') AS Year,
        coalesce(cast(MONTH([Period])as nvarchar(50)),'All Months') as month, coalesce(sum([values]),0) as sales
        FROM  @sales
        group by rollup (company, year([period]), month([period]))

Thank you . Starting to understand what was expected. – chahak joshi May 11 '19 at 12:22 — chahak joshi, May 11 '19 at 12:22

Query for generating month level & YTD level data

1 Answers1