Split a date column, and calculate an amount based on the result

Question

In the sales table I have a column that contains full dates of each sale, I need to split it and add 2 separate columns of month and year to the sales table, and a column that contains the sum of all sales of each month that was.

This is the table I have-

Sales table

customer_id	date	Quantity
123	01/01/2020	6
124	01/02/2020	7
123	01/03/2021	5
123	15/01/2020	4

Here's what I wrote -

ALTER TABLE SALES ADD SELECT DATEPART (year, date) as year FROM SALES;

ALTER TABLE SALES ADD SELECT DATEPART (month, date) as month FROM SALES;

ALTER TABLE SALES ADD SUM_Quantity AS SUM() (Here I was stuck in a query...)

Is this a valid query, and how to write it correctly? Thanks!

Answer 1

One of the problems you're going to have here is that the outcome of the computed columns you have isn't compatible with the data being stored. For example you can't build a sum of the quantity for all of the rows in January for each row in January. You need to group by the date and aggregate the quantity.

As such I think this might be an ideal candidate for an indexed view. This will allow you to store the calculated data, whilst preserving the data in the original table.

create table SalesTable (customer_id int, date date, quantity smallint);

insert SalesTable (customer_id, date, quantity)
select 123, '2020-01-01', 6
union
select 124, '2020-02-01', 7
union
select 123, '2021-03-01', 5
union
select 123, '2020-01-15', 4;

select * from SalesTable order by date; /*original data set*/

GO

CREATE VIEW dbo.vwSalesTable /*indexed view*/
   WITH SCHEMABINDING
   AS  
      SELECT customer_id, DATEPART(year, date) as year, datepart(MONTH, date) as 
month, SUM(quantity) as sum_quantity from dbo.SalesTable group by Customer_Id, 
DATEPART(year, date), DATEPART(MONTH, date)
  GO

  select * from vwSalesTable /*data from your indexed view*/

drop view vwSalesTable;
drop table SalesTable;