Datatype conversion and calculated column

Question

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

Venkataraman R · Accepted Answer · 2020-07-10T10:11:53.500

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 |
+--------+--------------+---------+-------------+

Datatype conversion and calculated column

1 Answers1