Is there a way to call GROUP BY as a variable within a calculated field in sql?

Question

i am trying to sort by the year as my calculated field is not correct when trying to graph by date. i think if i get the date to be in chronological order then my problem will be solved but i'm not sure how to do that. My dateIn field is in the format "7/10/2022"

SELECT 
  i.dateIn, 
  i.productId, 
  i.docNumber,
  b.batchTotalCount,
  i.productInCount,
  b.batchTotalCount - COALESCE(SUM(i.productInCount) OVER(PARTITION BY i.productId,i.docNumber ORDER BY i.productInDate ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW),0) AS CurrentQuantity_warehouse

FROM 
  product_batch  as b
   JOIN product_in AS i ON i.productId = b.productId AND i.docNumber = b.docNumber

ORDER BY year(i.dateIn),month(i.dateIn),day(i.dateIn) asc

I tried putting the long order by field in the last line but it didn't work. If it helps I am using Azure Databricks.

Answer 1

With

SUM(i.productInCount) OVER (PARTITION BY i.productId, i.docNumber
                                ORDER BY i.productInDate)

You are building a running total. But as you

ORDER BY year(i.dateIn), month(i.dateIn), day(i.dateIn) ASC

or just

ORDER BY i.dateIn

for that matter, your result will look mixed up.

Typically you would want to show the same order in your result rows, so you see the totals building up:

ORDER BY i.productId, i.docNumber, i.productInDate