March 3, 2022 at 11:34 am
Hi Expert
I am having data in the table main and Ratio1 table
expected calculation:
on this main.id
81187546(machine id) main. quantiy purchased -1 dtd 06/04
for the same quantity purchased we have consumable row ratio.consumables =1 and
so the first row will be 1
for the 2nd consumable row ratio.consumables =1 and no change in quantity purchased so equally distributed in 2 rows that is 0.5 will be 2nd row and first 1.5
and so on for last 5 years
drop table main;
drop table main
create table main ( oid int, UNIT1 int,quantity int,SaleDateId date, cash int)
insert main
values
(81187546,343434,4444444, '20210406',1652),
(81379242,343434,4444444,'20210412',1645),
(81397273,343434,4444444, '20210414',1647),
(81397274,343434,4444444, '20210414',1647),
(81412076,343434,4444444, '20210416',1655),
(81439392,343434,4444444, '20210421',1652),
(81439396,343434,4444444, '20210421',1674),
(81439397,343434,4444444, '20210421',1656)
=========================================
drop table ratio1
--truncate table ratio1;
Create table ratio1 (oid int,UNIT1 int,SaleDateId date,CONS integer,consumables integer)
insert ratio1
values (81250972,4444444, '20210407',1,65),
(81250973,4444444, '20210407',1,65),
(81250974,4444444, '20210407',1,65),
(81250975,4444444, '20210407',1,65),
(81250976,4444444, '20210407',1,65),
(81250977,4444444, '20210407',1,65),
(81250978,4444444, '20210407',1,65),
(81250979,4444444, '20210407',1,65),
(81250980,4444444, '20210407',1,65),
(81250981,4444444, '20210407',1,65),
(81250982,4444444, '20210407',1,65),
(81250983,4444444, '20210407',1,65),
(81251071,4444444, '20210407',15,77),
(81251072,4444444, '20210407',10,519),
(81251073,4444444, '20210407',1,51),
(81251074,4444444, '20210407',1,51),
(81251075,4444444, '20210407',1,51),
(81251076,4444444, '20210407',1,51),
(81251077,4444444, '20210407',1,51),
(81251078,4444444, '20210407',1,51),
(81251079,4444444, '20210407',1,51),
(81251080,4444444, '20210407',1,51),
(81251081,4444444, '20210407',1,51),
(81251082,4444444, '20210407',1,51),
(81251083,4444444, '20210407',1,51),
(81251084,4444444, '20210407',1,51),
(81251085,4444444, '20210407',1,51),
(81251086,4444444, '20210407',1,51),
(81251087,4444444, '20210407',1,51),
(81251088,4444444, '20210407',24,2281),
(81251089,4444444, '20210407',1,95),
(81251090,4444444, '20210407',1,95),
(81251091,4444444, '20210407',1,95),
(81251092,4444444, '20210407',1,95),
(81251093,4444444, '20210407',1,95),
(81251094,4444444, '20210407',1,95),
(81251095,4444444, '20210407',1,95),
(81251096,4444444, '20210407',1,95),
(81251097,4444444, '20210407',1,95),
(81251098,4444444, '20210407',1,95),
(81251099,4444444, '20210407',1,95),
(81251100,4444444, '20210407',1,95),
(81251101,4444444, '20210407',1,95),
(81251102,4444444, '20210407',1,95),
(81251103,4444444, '20210407',1,95),
(81251104,4444444, '20210407',1,95),
(81251105,4444444, '20210407',1,95),
(81251106,4444444, '20210407',1,95),
(81251107,4444444, '20210407',1,95),
(81251108,4444444, '20210407',1,95),
(81251109,4444444, '20210407',1,95),
(81251110,4444444, '20210407',1,95),
(81251111,4444444, '20210407',1,95),
(81251112,4444444, '20210407',1,95),
(81379247,4444444, '20210412',1,95),
(81379248,4444444, '20210412',1,95),
(81379249,4444444, '20210412',1,95),
(81379250,4444444, '20210412',1,95),
(81379251,4444444, '20210412',1,95),
(81379252,4444444, '20210412',1,95),
(81379253,4444444, '20210412',1,95),
(81379254,4444444, '20210412',1,95),
(81379255,4444444, '20210412',1,95),
(81379256,4444444, '20210412',1,95),
(81397321,4444444, '20210414',1,95)
Expected Output:
main.id ratio.oid Ratio1.SaleDateId ratio.consumables
81187546 81250972 20210407' 1 (+0.5) (+0.3)
81187546 81250973 20210407' 0.5 (+0.3)
81187546 81250974 20210407' 0.3 (+0.3)
81187546 81250975 20210407'
81187546 81250976 20210407'
81187546 81250977 20210407'
81187546 81250978 20210407'
81187546 81250979 20210407'
81187546 81250980 20210407'
81187546 81250981 20210407'
81187546 81250982 20210407'
81187546 81250983 20210407'
81187546 81251071 20210407'
81187546 81251072 20210407'
81187546 81251073 20210407'
81187546 81251074 20210407'
81187546 81251075 20210407'
81187546 81251076 20210407'
81187546 81251077 20210407'
81187546 81251078 20210407'
81187546 81251079 20210407'
81187546 81251080 20210407'
81187546 81251081 20210407'
81187546 81251082 20210407'
81187546 81251083 20210407'
81187546 81251084 20210407'
81187546 81251085 20210407'
81187546 81251086 20210407'
81187546 81251087 20210407'
81187546 81251088 20210407'
81187546 81251089 20210407'
81187546 81251090 20210407'
81187546 81251091 20210407'
81187546 81251092 20210407'
81187546 81251093 20210407'
81187546 81251094 20210407'
81187546 81251095 20210407'
81187546 81251096 20210407'
81187546 81251097 20210407'
81187546 81251098 20210407'
81187546 81251099 20210407'
81187546 81251100 20210407'
81187546 81251101 20210407'
81187546 81251102 20210407'
81187546 81251103 20210407'
81187546 81251104 20210407'
81187546 81251105 20210407'
81187546 81251106 20210407'
81187546 81251107 20210407'
81187546 81251108 20210407'
81187546 81251109 20210407'
81187546 81251110 20210407'
81187546 81251111 20210407'
81187546 81251112 20210407'
Tried:
;WITH CTE1 AS
(SELECT UNIT1,SaleDateId,CAST(SUM(consumables)AS FLOAT)AS SUM_Consumables,ROW_NUMBER()OVER(PARTITION BY UNIT1 ORDER BY SaleDateId)AS RowNum
FROM ratio1
GROUP BY UNIT1,SaleDateId
),CTE2 AS
(SELECT UNIT1,SaleDateId,SUM(SUM(quantity))OVER(PARTITION BY UNIT1 ORDER BY SaleDateId ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW)AS SUM_Quantity
,DENSE_RANK()OVER(PARTITION BY UNIT1 ORDER BY SaleDateId) AS RankNum
FROM main
GROUP BY UNIT1,SaleDateId
),CTE3 AS
( SELECT C1.UNIT1,C2.SaleDateId,FLOOR(SUM_Consumables/SUM_Quantity*10)/10 AS Dev_Consumables,
SUM(FLOOR(SUM_Consumables/SUM_Quantity*10)/10)OVER(PARTITION BY C1.UNIT1 ORDER BY C1.SaleDateId DESC ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS Unit
FROM CTE1 C1 JOIN CTE2 C2 ON C1.UNIT1=C2.UNIT1 AND C1.RowNum=C2.RankNum
)
SELECT oid,M.UNIT1,M.SaleDateId,M.quantity,M.cash AS cashCons,C3.Unit*quantity AS Unit
FROM main M JOIN CTE3 C3 ON M.UNIT1=C3.UNIT1 AND M.SaleDateId=C3.SaleDateId
ORDER BY SaleDateId
but getting wrong output
March 3, 2022 at 2:56 pm
I am really confused about what you are asking.... in your expected output, you have 4 columns, yet in your final SELECT you have 6. So right away, I can see your output isn't going to match up with the input.
That being said, I think I am misunderstanding the requirement the more I read your query and your data. I think we need to know more about your tables. You say "on this main.id 81187546(machine id) main. quantiy purchased -1 dtd 06/04", but when I look at that, it looks to me like 4444444 were purchased based on the "quantity" value, unless the "quantity" doesn't indicate the amount purchased, in which case, how do you determine how many were purchased? Is it 1 row per item purchased and you rely on the date to determine the number purchased?
Next potential typo that I see - your ratio table has 5 columns, but your sample insert statement only has 4 columns. Is the 5th column always NULL? And your "CONS" column is defined as an INTEGER, and then you insert 1.65 (for example) which is not an integer into the column.
Next question - why are there so many ratios for a single date? And how do you determine which ratio applies to each row in the main table? Also, there is no ratio for the date 20210406, how are you handling that scenario?
Also, your expected output only seems to tackle 1 of the main.oid values. Are you intentionally filtering the others out or should they all be in the output?
I think you need to correct and verify your sample input data before you start trying to solve the problem.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply