I have a table with the following structure:
Unfortunately, the sale_date
is stored as INT and I had to find a way to convert it in quarter (202001 will be Q1). The conversion worked well, but I need to include also some calculated columns based on this conversion. Basically, I need to calculate for Q1 the total price for each product, and the percentage from total for “product x” and “product y” recorded by each seller, in the current year. I know that I can obtain these easily using group by, but the way I converted the date (sale_date
) from INT to quarter it’s affecting the results.
SELECT seller, product_name, LEFT([sale_date],4) Year, 'Q'+ CAST((CAST(RIGHT(sale_date,2) AS INT)-1 )/3 +1 AS varchar) Quarter, (price), price * 100.0 / sum(price) over () as percentage_total, SUM (price) as total_sales FROM table1 GROUP BY LEFT(sale_date,4), 'Q'+ CAST((CAST(RIGHT(sale_date,2) AS INT) -1 )/3 +1 AS varchar), seller, product_name, price
Advertisement
Answer
Note: You should always use the right datatype for a column. It will avoid lots of issues. Always store Date values in date
datatype.
I would suggest you first convert the INT
to date datatype and then use DATE
functions to calculate quarter name. It will be accurate.
Below I am adding 01
as the date to the yyyymm
and then making it as yyyymmdd
, to make it as ISO 8601 dateformat (which is agnostic of dateformats) and then calculating the quarter value.
declare @table table(sales_date int, product_name varchar(30),seller varchar(30), price int) insert into @table VALUES(202001,'Product X', 'Agent1',2320),(202001,'Product X', 'Agent2',1416), (202004,'Product X', 'Agent1',420) SELECT seller, product_name, CONCAT('Q',DATENAME(QUARTER,CONCAT(CAST(sales_date AS VARCHAR(10)),'01'))) as Quarter, sum(price) as total_sales from @table group by seller, product_name, CONCAT('Q',DATENAME(QUARTER,CONCAT(CAST(sales_date AS VARCHAR(10)),'01')))
+--------+--------------+---------+-------------+ | seller | product_name | Quarter | total_sales | +--------+--------------+---------+-------------+ | Agent1 | Product X | Q1 | 2320 | | Agent1 | Product X | Q2 | 420 | | Agent2 | Product X | Q1 | 1416 | +--------+--------------+---------+-------------+