How to create a query in ssms 18 that transforms dates from an integer to a date and counts the number of rows

Question

Here example of my data

   CREATE TABLE elast (  
      monthorder    int,  
      SKU         int,  
      groups      varchar(50),
      sale_count        int,  
      Xprice  decimal(10,2),  
      Yprice   decimal(10,2)  
      MAXprice   decimal(10,2)
   )  
     
   INSERT  into elast VALUES 
(202204,118103,'electric_shop'4,1184.5,592.25,0.5),
(202202,118104,'electric_shop'20,920.42,437.751752,0.5244),
(202208,118104,'electric_shop'309,1126.22,523.917544,0.5348),
(202207,118103,'electric_shop'122,1069.02,513.022698,0.5201),
(202208,118103,'electric_shop'243,1069.02,497.308104,0.5348),
(202203,118104,'electric_shop'234,1185.5,592.75,0.5),
(202212,118104,'electric_shop'290,1126.22,520.201018,0.5381),
(202205,118104,'electric_shop'22,1126.22,563.11,0.5),
(202206,118103,'electric_shop'1,1069.02,525.209526,0.5087),
(202209,118104,'electric_shop'431,1126.22,523.917544,0.5348),
(202206,118104,'electric_shop'36,1126.22,553.311886,0.5087),
(202205,118103,'electric_shop'2,1069.02,534.51,0.5),
(202207,118104,'electric_shop'269,1126.22,540.472978,0.5201),
(202204,118104,'electric_shop'359,1185.5,592.75,0.5),
(202210,118104,'electric_shop'290,1126.22,540.360356,0.5202),
(202211,118104,'electric_shop'38,1126.22,540.360356,0.5202),
(202212,118103,'electric_shop'5,1069.02,493.780338,0.5381),
(202301,118104,'electric_shop'102.3084,1126.22,520.201,0.5381)

Can you help me please create query, because i need calculate how much for each sku, of each groups there is data by monthorder.

For example, here we see for sku =118103 and groups ='electricshop' ,monthorder goes from 202204 to 202212 , 202212-202204=8 I.E. i need that in new column month_diff for this concrete sku+groups was put ,cause 8 months because the difference is 8 months.

Then i need create column N which contains number of observations, in 8 months only 6 observations.

And last i need create column which transforms the integer format of monthorder into a date format like

01.MM.YYYY

For example 202204 will be transformed as 01.04.2022.

So in desired output will be added 3 new columns month_diff,N and date for each sku+gpoups

In excel i provided example

How perform this tranformations easy via ssms 18?

Thank you for your help.

Answer 1

First of all, whether you use SSMS 18, SSMS 19, Azure Data Studio or something else does not matter. SSMS or ADS do not run queries. They only send queries to SQL Server, and this is where they queries are executed.

Here is a query. But it does not handle Xprice, as you did not explain how that column is to be calculated.

SELECT groups, monthorder, SKU, sale_count, Xprice,
       N = COUNT(*) OVER (PARTITION BY groups, SKU),
       date = concat_ws('.', '01', right(convert(char(3), monthorder % 1000), 2), monthorder / 100),
       month_diff = datediff(MONTH,
                             convert(char(6), MIN(monthorder) OVER (PARTITION BY groups, SKU)) + '01',
                             convert(char(6), MAX(monthorder) OVER (PARTITION BY groups, SKU)) + '01')
FROM   elast
ORDER  BY groups, SKU

Answer 2

Xprice is just a column, it will not be calculated in any way, it just exists and that's it. But thanks, tomorrow I will try to run query on my server and I will write to you and accept the answer