units divided by quantity and order by date
-
Hi Expert,
I am facing challanges for logic to apply all previous units sold divided by next or nearest dates quantity
here is the query
Create table ratio1 (UNIT1 int,CONS integer,SaleDateId date,consumables integer)
insert ratio1
values
(4444444,222,'20210407',10),
(4444444,333,'20210407',15),
(4444444,444,'20210407',24),
(4444444,555,'20210407',24),
(4444444,777,'20210412',10),
(4444444,888,'20210414',16),
(4444444,999,'20210414',20),
(4444444,000,'20210421',10)
=========================================
create table main ( oid int, UNIT1 int,SaleDateId date, quantity int, cash int)
--truncate table main
insert main
values (343434,4444444,'20210406',1,200),
(343434,4444444,'20210412',1,200),
(343434,4444444,'20210414',2,200),
(343434,4444444,'20210414',1,200),
(343434,4444444,'20210416',1,200)
The units divide by quantity and order by date so for dtd 07 April Agreegation will happen and after that it will be equally distributed for all previous orders
rough calculation:
1-quantity purchased on 20210406 from unit1 from Table Main
on the 7th April from consuambles sold for same unit1 73 cons unit( new column to be added)
on 12 th 2 more unit1 sold for which 10 cons units sold so values will be devided by quantiy euqally so 73+5 and 5*2 means 10 this calculation will go further
how to give logic for it. I am newbie
Expected output
Expected output
oid UNIT1 SaleDateId Quantity cashCons Unit
343434 4444444 06-04-2021 1 200 86.8
34343 44444444 12-04-2021 1 200 13.8
34343 44444444 14-04-2021 2 200 17.4
34343 44444444 14-04-2021 1 200 8.8
34343 44444444 16-04-2021 1 200 1.6 -
Shree23 wrote:
how to give logic for it. I am newbie
Heh... no you're not. According to the date of your first post, you've been working with SQL for nearly 2 years. Please, at least try. It may take you a little longer to figure out but you learn something about SQL and yourself. You actually can do this. 😉
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions -
Hi,
Actually id do not understand logic for this help me with clue
on the 6th April -Main Table - 1 (quantity )sold and 73 consumables from Ratio Table on 7th April- means first quantity sold and then sold some consumable of the quantity - cons unit ( new column to be added)
so first row- 73
on 12 th 1 more quantity sold for which 10 consumables sold so 1+1 values will be divided by consumable 10 so 73+5 and 5*2 means 5
on 14th 3 quantity sold for which 36 consumable sold on so 1+1+3(quantity) divided by 36 consumable which is 7.2 so
first row 7.2+78- 85.2 (cons unit)
2nd row 5+ 7.2-12.2
3 row 7.2*2(quantity)=14.4
4th row 7.2*1(quantity)=7.2
on 16th 1 unit sold for which for which 1o consumable sold so 1+1+3+1=6(Quantity) divided by 10 means 1.6 so
first row 85.2 +1.6(cons unit)- 86.8
2nd row 12.2+1.6=13.8
3 row 14.2+1.6 *2=17.8
4th row 7.2+1.6=8.8
5th row- 1.6*1= 1.6
-
suggestion pls
-
Do you mean doing running totals and dividing by some value in the current record?
Use a windowing function to do a running total, then divide.
-
The logic example and expected output seem to be a little different. Maybe I'm not reading clearly. It does get difficult when the main table is used singularly (treating the two dates '20210414' separate rows) and then as an aggregate, but while trying to follow along, I see Expected output row 3
oid UNIT1 SaleDateId Quantity cashCons Unit
34343 44444444 14-04-2021 2 200 17.4
is different from the example 3 row 14.2+1.6 *2=17.8
In any case, I'd try to create all the needed elements with multiple CTEs to keep it clear in my head and then join together to get the final results. Once that was worked through, I'd look for overlap and try to reduce complexity.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply