-
Mar 27th, 2021, 07:48 AM
#1
Thread Starter
Fanatic Member
Very complicated but perhaps possible!
hello vbforums
I have a very complicated query I'm asking experts if it is possible to run this query.
This is my table
Attachment 180822
As shown in table the record has a debt of 60 + 10 + 20 = 90 E
Now the record pays 80 E, My question:
Is it possible to distribute the 80E on the 3 items?
In other words:
Item1 ..... (+ 60 = 100) (remain = 0)
Item2 ...... (+ 10 = 20) (remain = 0)
Item3 ...... (+ 10 = 20) (remain = 10)
If this is possible , how would be the query please?
Thank you
-
Mar 27th, 2021, 08:57 AM
#2
Re: Very complicated but perhaps possible!
I don’t think you can do this with a single query.
It took me a while to see that each record is a kind of transaction.
First you need to select the records which matter.
The ones you made red. That’s not that obvious just based on these records.
When you have the records in a recordset you walk through them and subtract the remainder from the payment. Then create a new transaction and proceed to the next record when there’s still money left
-
Mar 27th, 2021, 10:01 AM
#3
Thread Starter
Fanatic Member
Re: Very complicated but perhaps possible!
Thank you sir
Code:
"Select * from tbl where acquit = '0' And id = '1'Group by Item_Id"
Rs.MoveLast
Rs!deposit = val(txt_payment.Text) - Rs! Remainder
Rs! acquit = 1
Now Item 1 is acquitted, I move to the second item
Code:
"Select * from tbl where acquit = '0' And id = '1'Group by Item_Id"
Rs.MoveLast
Rs!deposit = val(txt_payment.Text) - Rs! Remainder
Rs! acquit = 1
Now Item 2 is acquitted, I move to the third item
I’m not testing on the project
I’m just trying to see if this is what you suggest.
thanks
-
Mar 27th, 2021, 10:31 AM
#4
Re: Very complicated but perhaps possible!
You also have to deduct the paid value from the payment value.
And you have to whether you don’t take to much from the payment value.
-
Mar 27th, 2021, 11:20 AM
#5
Re: Very complicated but perhaps possible!
This can be done with a single query - here are the "steps". I would do it in a SPROC, with a temp table or table variable so it's easier to read.
If that is not possible then each "step" either needs to be a view, or a derived query placed in ()'s, so you end up with lots of sub-queries.
Here are the steps.
1) Use RowNumber() to order the ItemId's - use the order you want to take them from "first" as "1"
...Next sub-query, or view
2) Sum based on the GROUP'ing of that RowNumber() column
...next
3) Select each Row's SUM and then "add" to that a sub-query of the "rows" that are less than this "row"
...at this point I know id 1 as "60", id 2 as "70" (that's the sum of 60 and 10), and id 3 as "90" (60+10+20). If we had an id 4 it would be a bigger value and so on...
4) Now you have two possibilities...
4a) One of these "Row" sum's exactly matches your payment of 80 - none of ours does that...
4b) One of these Row" sums is the MIN(ROW) that is GREATER than 80 - that would be id 3.
5) Every Id below this "target" id gets fully paid.
6) If the "target" id sum matches the payment, then apply the full "need", otherwise apply the "payment" minus the SUM(of the ROW one below the target row).
Simple - right?
In a SPROC this would be 5 or so action queries.
Without a SPROC 5 complicated sub-queries
Last edited by szlamany; Mar 27th, 2021 at 11:27 AM.
-
Mar 27th, 2021, 11:26 AM
#6
Re: Very complicated but perhaps possible!
I would prefer I small subroutine instead, but that’s my personal preference
-
Mar 27th, 2021, 02:14 PM
#7
Thread Starter
Fanatic Member
Re: Very complicated but perhaps possible!
szlamany
I am so grateful to you for your interest and your time
However what you are suggesting looks out of my reach.
Arnoutdv
I thank you very much for your help
Is there a simpler approach I can follow.
thank you all
-
Mar 27th, 2021, 05:36 PM
#8
Re: Very complicated but perhaps possible!
Don't you have a unique identification for each row?
-
Mar 27th, 2021, 05:57 PM
#9
Thread Starter
Fanatic Member
Re: Very complicated but perhaps possible!
Originally Posted by dee-u
Don't you have a unique identification for each row?
Thank you for the interest sir
I have an auto increment column
Last edited by newbie2; Mar 27th, 2021 at 06:01 PM.
-
Mar 28th, 2021, 08:11 AM
#10
Re: Very complicated but perhaps possible!
What type of SQL are you using? MS SQL? MySQL?
Do you dislike making VIEW's?
Are you comfortable working with sub-queries and deeply nested queries?
Answer those three questions for me please.
-
Mar 28th, 2021, 08:35 AM
#11
Thread Starter
Fanatic Member
Re: Very complicated but perhaps possible!
What type of SQL are you using? MS SQL? MySQL?
I'm using SQlite and RC5 same as ADO
Do you dislike making VIEW's?
As far as I know views are virtual tables, but I have not enough experience with that.
Are you comfortable working with sub-queries and deeply nested queries?
Sub queries yes but nested queries no idea.
thanks
-
Mar 28th, 2021, 12:52 PM
#12
Re: Very complicated but perhaps possible!
Ok - hopefully you have some kind of query editor window that you can place this in and run it. I've got MS SQL and SQL Server Management Studio to play with here.
The example queries below build a table with your data that I can play with. I then show you the queries for steps 1, 2 and 3 that I laid out. Please note that after seeing your data more closely that the steps are similar to what I said initially, but got fine tuned as I wrote them.
Code:
Create Table #Trans (Aut int, ID int, Item_id int, price int, deposit int, Total_deposit int, remain int, acquit int)
Insert into #Trans values
(1,1,1,100,10,10,90,0),
(2,1,1,100,10,20,80,0),
(3,1,1,100,20,40,60,0),
(4,1,2,20,4,4,16,0),
(5,1,2,20,6,10,10,0),
(6,1,3,30,5,5,25,0),
(7,1,3,30,10,15,20,0),
(8,1,4,20,20,20,0,1)
Select * From #Trans
--1) Use RowNumber() to order the ItemId's - use the order you want to take them from "first" as "1"
Select Item_id,Max(Aut) "MaxAut",Row_Number() Over (Order by Item_id) "RowNum"
From #Trans Group by Item_id
--2) Sum based on the GROUP'ing of that RowNumber() column
Select X1.Item_id,TT.remain,X1.MaxAut,X1.RowNum
From (Select Item_id,Max(Aut) "MaxAut",Row_Number() Over (Order by Item_id) "RowNum"
From #Trans Group by Item_id) as X1
Left Join #Trans TT on TT.Aut=X1.MaxAut
--3) Select each Row's SUM and then "add" to that a sub-query of the "rows" that are less than this "row"
-- This is a "test" run of the query WITHOUT the "RunningRemain" field in place
Select X2.*
, X2.remain -- "(Select 0)" below is a "holding" spot for the sub-query described next
+IsNull((Select 0),0) "RunningRemain"
From (Select X1.Item_id,TT.remain,X1.RowNum
From (Select Item_id,Max(Aut) "MaxAut",Row_Number() Over (Order by Item_id) "RowNum"
From #Trans Group by Item_id) as X1
Left Join #Trans TT on TT.Aut=X1.MaxAut) as X2
-- *****************************
-- The query below is running the same query above but doing it
-- for Item_Id less then 3 and returning a SUM() of the "remains"
-- This is needed in the PRIORREMAINS sub-query spot
Select Sum(X2a.remain) "PriorRemains"
From (Select X1a.Item_id,TTa.remain,X1a.RowNum
From (Select Item_id,Max(Aut) "MaxAut",Row_Number() Over (Order by Item_id) "RowNum"
From #Trans Group by Item_id) as X1a
Left Join #Trans TTa on TTa.Aut=X1a.MaxAut) as X2a
Where X2a.Item_id<3
-- ******
-- The query below now has the PriorRemains (SELECT 0) query replaced with the SUM() above
Select X2.*
, X2.remain
+IsNull((Select Sum(X2a.remain) "PriorRemains"
From (Select X1a.Item_id,TTa.remain,X1a.RowNum
From (Select Item_id,Max(Aut) "MaxAut",Row_Number() Over (Order by Item_id) "RowNum"
From #Trans Group by Item_id) as X1a
Left Join #Trans TTa on TTa.Aut=X1a.MaxAut) as X2a
Where X2a.Item_id<X2.Item_id),0) "RunningRemain"
From (Select X1.Item_id,TT.remain,X1.RowNum
From (Select Item_id,Max(Aut) "MaxAut",Row_Number() Over (Order by Item_id) "RowNum"
From #Trans Group by Item_id) as X1
Left Join #Trans TT on TT.Aut=X1.MaxAut) as X2
The results of running the queries above gives you the following output.
Code:
(8 rows affected)
Aut ID Item_id price deposit Total_deposit remain acquit
----------- ----------- ----------- ----------- ----------- ------------- ----------- -----------
1 1 1 100 10 10 90 0
2 1 1 100 10 20 80 0
3 1 1 100 20 40 60 0
4 1 2 20 4 4 16 0
5 1 2 20 6 10 10 0
6 1 3 30 5 5 25 0
7 1 3 30 10 15 20 0
8 1 4 20 20 20 0 1
(8 rows affected)
Item_id MaxAut RowNum
----------- ----------- --------------------
1 3 1
2 5 2
3 7 3
4 8 4
(4 rows affected)
Item_id remain MaxAut RowNum
----------- ----------- ----------- --------------------
1 60 3 1
2 10 5 2
3 20 7 3
4 0 8 4
(4 rows affected)
Item_id remain RowNum RunningRemain
----------- ----------- -------------------- -------------
1 60 1 60
2 10 2 10
3 20 3 20
4 0 4 0
(4 rows affected)
PriorRemains
------------
70
(1 row affected)
Item_id remain RowNum RunningRemain
----------- ----------- -------------------- -------------
1 60 1 60
2 10 2 70
3 20 3 90
4 0 4 90
(4 rows affected)
Completion time: 2021-03-28T13:50:06.5387996-04:00
The final result set (using that final query shown) is what you need to build from your data. I would make that a VIEW so step 4 is easier for you.
Hopefully you have the time to digest and understand everything I posted and you are able to apply it to your data.
-
Mar 28th, 2021, 06:05 PM
#13
Thread Starter
Fanatic Member
Re: Very complicated but perhaps possible!
I'm so so sorry for bothering you sir
you are so helpful and so kind
I spent more than 4 hours trying to adapt the codes to sqlite but syntax seems different from MS SQL.
I must wait for someone who is talented in sqlite perhaps he can do the task.
million thanks
-
Mar 29th, 2021, 03:34 AM
#14
Re: Very complicated but perhaps possible!
I don't have much time at the moment.
I will later this day have a look at it
-
Mar 29th, 2021, 05:39 AM
#15
Thread Starter
Fanatic Member
Re: Very complicated but perhaps possible!
Originally Posted by Arnoutdv
I don't have much time at the moment.
I will later this day have a look at it
very glad to hear that
thank you very much
-
Mar 29th, 2021, 06:04 AM
#16
Re: Very complicated but perhaps possible!
Done in SQLite
SQL Code:
SELECT DISTINCT T1.aut, T2.remain FROM trans As T1 LEFT JOIN (SELECT Aut,ID, remain, Acquit,ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY Aut DESC) AS RN FROM Trans) As T2 ON T1.ID=T2.ID AND T1.Aut=T2.Aut AND T2.RN=1 AND T2.Acquit=0 WHERE T2.remain is not null
Returns
Code:
Aut |
remain |
3 |
60 |
5 |
10 |
7 |
20 |
EDIT: Now you have the Primary key to the last Record per item_id, where "remain" is not 0 (could even check in query --> Sanity-check)
Now you just have to loop through it and fire of "UPDATE"-Statements
If "remain" falls to zero set "acquit" to 1
Example:
Blanket Payment = 80
RecordSet.MoveFirst
Loop
Balance = RecordSet("remain") - Payment
If balance<=0 Then
Execute "UPDATE Transactions SET remain=0, acquit=1 WHERE Aut=" & RecordSet("Aut")
Payment = Abs(Balance)
If Payment=0 Then Exit Loop
RecordSet.MoveNext
Else
Execute "UPDATE Transactions SET remain=remain-" & Balance & " WHERE Aut=" & RecordSet("Aut")
Exit Loop
End If
End Loop
Last edited by Zvoni; Mar 29th, 2021 at 07:14 AM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Mar 29th, 2021, 11:49 AM
#17
Thread Starter
Fanatic Member
Re: Very complicated but perhaps possible!
Thank you very much Zvoni
I have been struggling to make the first query into practice but I'm facing with this error.
I made all possible change but still with no luck.
Attachment 180848
Code:
StrSql = " SELECT DISTINCT " & _
" T1.aut, " & _
" t2.remain" & _
" From" & _
" trans As t1" & _
" Left Join" & _
" (SELECT Aut,ID, remain, Acquit,ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY Aut DESC) AS RN" & _
" FROM Trans) As T2" & _
" ON" & _
" T1.ID=T2.ID AND" & _
" T1.Aut=T2.Aut AND" & _
" T2.RN=1 AND" & _
" t2.Acquit = 0" & _
" Where t2.remain Is Not Null"
Set Rs = Cnn.OpenRecordset(StrSql)
thank you
-
Mar 30th, 2021, 01:48 AM
#18
Re: Very complicated but perhaps possible!
Strange.
I did that Query in DB-Browser for SQLite (sqlitebrowser.org), and it worked.
Maybe you have a typo or missing blank/comma somewhere
Could you try to run that query in a DB-Tool like mine?
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Mar 30th, 2021, 03:10 AM
#19
Thread Starter
Fanatic Member
Re: Very complicated but perhaps possible!
Thank you sir
I run the query in DB-Browser and it is throwing the same error
This is exactly the error:
Code:
near "(": syntax error: SELECT DISTINCT
T1.aut,
t2.remain
From
trans As T1
Left Join
(SELECT Aut,ID, remain, Acquit,ROW_NUMBER() OVER(
-
Mar 30th, 2021, 03:31 AM
#20
Re: Very complicated but perhaps possible!
The table name in the detailed sample is "trans"
Your table is not called "trans" is it?
"Select * from tbl where acquit = '0' And id = '1'Group by Item_Id"
-
Mar 30th, 2021, 04:15 AM
#21
Re: Very complicated but perhaps possible!
Originally Posted by Arnoutdv
The table name in the detailed sample is "trans"
Your table is not called "trans" is it?
*groan**facepalm*
Missed that one
I actually used Arnout's CREATE TABLE/INSERT INTO Statements to create my sample data
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Mar 30th, 2021, 04:18 AM
#22
Re: Very complicated but perhaps possible!
You used the extended sample by szlamany, my SQL knowledge is limited to basic queries.
I tend to solve more complex operations using code instead of queries.
-
Mar 30th, 2021, 04:43 AM
#23
Re: Very complicated but perhaps possible!
Eh?
True.
It's time for some offtime..... overworked, underjumped, the whole slew......
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Mar 30th, 2021, 06:35 AM
#24
Thread Starter
Fanatic Member
Re: Very complicated but perhaps possible!
Originally Posted by Arnoutdv
The table name in the detailed sample is "trans"
Your table is not called "trans" is it?
I created a new table with the same fields based on szlamany demo.
thanks
-
Mar 30th, 2021, 07:15 AM
#25
Re: Very complicated but perhaps possible!
Originally Posted by newbie2
I created a new table with the same fields based on szlamany demo.
thanks
So it works?
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Mar 30th, 2021, 08:18 AM
#26
Thread Starter
Fanatic Member
Re: Very complicated but perhaps possible!
-
Mar 30th, 2021, 08:33 AM
#27
Re: Very complicated but perhaps possible!
Try it as a single line
Code:
SELECT DISTINCT T1.aut, T2.remain FROM trans As T1 LEFT JOIN (SELECT Aut, ID, remain, Acquit, ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY Aut DESC) AS RN FROM Trans) As T2 ON T1.ID=T2.ID AND T1.Aut=T2.Aut AND T2.RN=1 AND T2.Acquit=0 WHERE T2.remain is not null
It definitely works for me in SQLite
So no idea what's different with your system
SQL Code:
SELECT DISTINCT T1.aut, T2.remain FROM trans As T1 LEFT JOIN (SELECT Aut, ID, remain, Acquit, ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY Aut DESC) AS RN FROM Trans) As T2 ON T1.ID=T2.ID AND T1.Aut=T2.Aut AND T2.RN=1 AND T2.Acquit=0 WHERE T2.remain is not null
Last edited by Zvoni; Mar 30th, 2021 at 08:39 AM.
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Mar 30th, 2021, 08:45 AM
#28
Thread Starter
Fanatic Member
Re: Very complicated but perhaps possible!
Same error all the times even with single lines
This is my code for table creating
Code:
Cnn.Execute "Create Table Trans (Aut int, ID int, Item_id int, price int, deposit int, Total_deposit int, remain int, acquit int)"
Cnn.Execute " into Trans values " & _
"(1,1,1,100,10,10,90,0)," & _
" (2,1,1,100,10,20,80,0)," & _
" (3,1,1,100,20,40,60,0)," & _
" (4,1,2,20,4,4,16,0)," & _
" (5,1,2,20,6,10,10,0)," & _
" (6,1,3,30,5,5,25,0)," & _
" (7,1,3,30,10,15,20,0)," & _
" (8,1,4,20,20,20,0,1)"
End Sub
I run your code on the form or in db browser I get the error:
Code:
near "(": syntax error: SELECT DISTINCT T1.aut, T2.remain FROM trans As T1 LEFT JOIN (SELECT Aut, ID, remain, Acquit, ROW_NUMBER() OVER(
-
Mar 30th, 2021, 08:49 AM
#29
Thread Starter
Fanatic Member
Re: Very complicated but perhaps possible!
I suspect the error resides somewhere here in this piece:
Code:
LEFT JOIN
(SELECT
Aut,
ID,
remain,
Acquit,
ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY Aut DESC)
-
Mar 30th, 2021, 04:16 PM
#30
Thread Starter
Fanatic Member
Re: Very complicated but perhaps possible!
After some googling I think SQlite does not support ROW_NUMBER() OVER PARTITION
but I think there is an alternative.
However this seems very complicated regarding my modest knowledge.
-
Mar 30th, 2021, 05:00 PM
#31
Re: Very complicated but perhaps possible!
Oh yes it does:
https://sqlite.org/windowfunctions.html
https://www.sqltutorial.org/sql-wind...ql-row_number/
Which version of sqlite do you use?
Window function support was first added to SQLite with release version 3.25.0 (2018-09-15)
-
Mar 30th, 2021, 05:24 PM
#32
Thread Starter
Fanatic Member
Re: Very complicated but perhaps possible!
I was using an old version 3.22 but now I'm using the last version 3.35.3
However my trouble still continue.
When I started googling about my error, I found many people were complaining about the same error.
Perhaps these topics are old.
However I wonder for my case, why I'm still having this error which gives the impression that ROW_NUMBER() OVER PARTITION is not supported.
-
Mar 30th, 2021, 05:32 PM
#33
Thread Starter
Fanatic Member
Re: Very complicated but perhaps possible!
I followed your link and I tested the sample they provide but I 'm always having the same mistake either in db browser or on the form.
Code:
Cnn.Execute "CREATE TABLE t0(x INTEGER PRIMARY KEY, y TEXT);"
Cnn.Execute "INSERT INTO t0 VALUES (1, 'aaa'), (2, 'ccc'), (3, 'bbb')"
StrSql = "SELECT x, y, row_number() OVER (ORDER BY y) AS row_number FROM t0 ORDER BY x"
Set Rs = Cnn.OpenRecordset(StrSql)
this is the error thrown in db browser
Code:
near "(": syntax error: SELECT x, y, row_number() OVER (
-
Mar 31st, 2021, 01:54 AM
#34
Re: Very complicated but perhaps possible!
Which DB browser are you using?
And which version.
You are using vbRichClient don’t you? Then you can’t just download a new sqlite dll.
Which version of vbRichClient are you using?
Check the version you are actually using:
SELECT sqlite_version() AS version;
-
Mar 31st, 2021, 03:45 AM
#35
Re: Very complicated but perhaps possible!
Originally Posted by Arnoutdv;[URL="tel:5516462"
5516462[/URL]]Which DB browser are you using?
And which version.
You are using vbRichClient don’t you? Then you can’t just download a new sqlite dll.
Which version of vbRichClient are you using?
Check the version you are actually using:
SELECT sqlite_version() AS version;
He‘s using RC5
See post #11
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Mar 31st, 2021, 04:25 AM
#36
Re: Very complicated but perhaps possible!
Yes he's using RC5, but maybe an older release which predates the minimum SQLite version
At least version 5.0.68 is needed:
Version: 5.0.68
- vb_cairo_sqlite.dll now updated to recent SQLite 3.28.0 (with support for window-functions, plus the sqlar-extension)
- fix on cArrayList in vbVariant-Mode (when Objects were store in those Variants, and .Pop or .Dequeue Methods were called)
- smaller fixes/enhancements on cFormula (change of Operator-precedence of / and \ ... as well as support for e-Notation of numbers)
- added an additional Cairo-FontFallback in case an OutputString contains symbols in the "emoji-category"
Last edited by Arnoutdv; Mar 31st, 2021 at 04:29 AM.
-
Mar 31st, 2021, 06:43 AM
#37
Re: Very complicated but perhaps possible!
Would it explain why he fails in DB-Browser?
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Mar 31st, 2021, 06:46 AM
#38
Re: Very complicated but perhaps possible!
Also outdated??
Sorry, but I don't have clue why it doesn't work for newbie2
*edit:
I just tried it in DB Browser for SQLite Version 3.12.1 and it works fine for me too.
File -> New In-Memory Database
Tab -> Execute SQL
First:
Code:
Create Table Trans (Aut int, ID int, Item_id int, price int, deposit int, Total_deposit int, remain int, acquit int)
Second:
Code:
Insert into Trans values
(1,1,1,100,10,10,90,0),
(2,1,1,100,10,20,80,0),
(3,1,1,100,20,40,60,0),
(4,1,2,20,4,4,16,0),
(5,1,2,20,6,10,10,0),
(6,1,3,30,5,5,25,0),
(7,1,3,30,10,15,20,0),
(8,1,4,20,20,20,0,1)
Third:
Code:
SELECT DISTINCT T1.aut, T2.remain FROM trans As T1 LEFT JOIN (SELECT Aut, ID, remain, Acquit, ROW_NUMBER() OVER(PARTITION BY item_id ORDER BY Aut DESC) AS RN FROM Trans) As T2 ON T1.ID=T2.ID AND T1.Aut=T2.Aut AND T2.RN=1 AND T2.Acquit=0 WHERE T2.remain is not null
Result
Last edited by Arnoutdv; Mar 31st, 2021 at 06:59 AM.
-
Mar 31st, 2021, 08:24 AM
#39
Re: Very complicated but perhaps possible!
Well, there we go.
it‘s now on him
Last edited by Zvoni; Tomorrow at 31:69 PM.
----------------------------------------------------------------------------------------
One System to rule them all, One Code to find them,
One IDE to bring them all, and to the Framework bind them,
in the Land of Redmond, where the Windows lie
---------------------------------------------------------------------------------
People call me crazy because i'm jumping out of perfectly fine airplanes.
---------------------------------------------------------------------------------
Code is like a joke: If you have to explain it, it's bad
-
Mar 31st, 2021, 04:33 PM
#40
Thread Starter
Fanatic Member
Re: Very complicated but perhaps possible!
Last edited by newbie2; Mar 31st, 2021 at 04:43 PM.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|