question

akhterhussain-3167 avatar image
0 Votes"
akhterhussain-3167 asked EchoLiu-msft edited

Expected Output Required

I want Pending amount to calculate Correctly, Below is Data with expected output.
Note: in Invoice table when ever rate will be same then that invoice qty will be sum and multiple to rate, if rate will not be same then it display separately .
As you can see invoice no.104 and 105 rate is same ,then their qty get sum accordingly same rate, those invoice in which rate is not same then it will display their rate separately, as you can see invoice 103.

I am retrieving data between two date from '2021-05-01' to '2021-05-30'. before date '2021-05-01' there is two invoice which amount total is 3600 and i received amount in May 3000 ,then Pending amount must be 600 show



  Create table #tbl_item (item_ID int,Name varchar(50))
  Create table #tbl_Account_L_Four (Level_Four_ID int,Level_Four_Name varchar(50))
  CREATE TABLE #tbl_InvoiceM(Inv_ID INT,Level_Four_ID int,Inv_date date,inv_amt Decimal(10,2))
  Create Table #tbl_INvoiceD (DID int,item_ID int,INV_ID int, qty int,Rate int)
  Create table #tbl_Received (R_ID int,Level_Four_ID int,Rec_AMt int,R_Date date);
  insert into #tbl_item values (11,'19Liter')
  Insert into #tbl_Account_L_Four values (1001,'akhter')
  insert into #tbl_InvoiceM values(101,1,'2021-04-25',2000)
  insert into #tbl_InvoiceD values(1,11,101,4,500)
  insert into #tbl_InvoiceM values(102,1,'2021-04-28',1600)
  insert into #tbl_InvoiceD values(1,11,102,8,200)
    
  insert into #tbl_InvoiceM values(103,1,'2021-05-01',550)
  insert into #tbl_InvoiceD values(1,11,103,2,150),
  (2,11,101,5,50)
    
  insert into #tbl_InvoiceM values(104,1,'2021-05-02',4900)
  insert into #tbl_InvoiceD values(1,11,104,5,500),
  (2,11,104,4,600)
   insert into #tbl_InvoiceM values(105,1,'2021-05-04',4900)
  insert into #tbl_InvoiceD values(1,11,105,5,500),
  (2,11,105,4,600)
    
  insert into #tbl_Received values(1,2000,1001,'2021-05-02')
   insert into #tbl_Received values(1,1000,1001,'2021-05-03');

93134-image.png


sql-server-generalsql-server-transact-sql
image.png (6.6 KiB)
· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @akhterhussain-3167

You mentioned 104 and 105, but I don’t seem to find 105 in your sample data. Could you please explain?
In addition, why 200 is not included in the rate column in your expected results? The qty corresponding to rate=500 should be 14, but your result is 10, please help explain?

Echo

0 Votes 0 ·

You mentioned 104 and 105, but I don’t seem to find 105 in your sample data. Could you please explain?

i have update data , above

  insert into #tbl_InvoiceM values(104,1,'2021-05-02',4900)
   insert into #tbl_InvoiceD values(1,11,104,5,500),
   (2,11,104,4,600)
    insert into #tbl_InvoiceM values(105,1,'2021-05-04',4900)
   insert into #tbl_InvoiceD values(1,11,105,5,500),
   (2,11,105,4,600)

In addition, why 200 is not included in the rate column in your expected results?
Bc it is previous Month data,i am retrieving data from 01-05-2021 to 30-05-2021.


The qty corresponding to rate=500 should be 14, but your result is 10, please help explain?
i mentioned above, that same rate qty will get sum from any invoice other wise not, in last row where 8 is coming in result ,it will be 8,bc rate are same ,just qty will get sum not rate,where rate will not same in invoice then qty will not sum.


0 Votes 0 ·

Pending will be subtract form #tbl_Received table amount, pending amount will be 800

0 Votes 0 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered EchoLiu-msft edited

Please try:

   ;WITH cte
     as(SELECT *,Total=QTY*Rate,
     (SELECT (SELECT SUM(CAST(inv_amt AS INT)) 
     FROM #tbl_InvoiceM 
     WHERE Inv_date<'2021-05-01')
     -
     (SELECT SUM(Level_Four_ID)
     FROM #tbl_Received 
     WHERE R_Date BETWEEN '2021-05-01' AND '2021-05-30')) Pending
     FROM (SELECT DISTINCT SUM(QTY) OVER(PARTITION BY Rate ORDER BY Rate) QTY,Rate,inv_amt
     FROM #tbl_INvoiceD t1
     JOIN #tbl_InvoiceM t2 ON t1.Inv_ID=t2.Inv_ID
     WHERE t2.Inv_date BETWEEN '2021-05-01' AND '2021-05-30' OR t1.DID=2) t)
        
     SELECT QTY,Rate,Total,
     CASE WHEN Rate<>600 THEN '' ELSE CAST(Pending AS VARCHAR) END Pending
     FROM cte
     ORDER BY Rate

Output:
93302-image.png

93311-image.png
Pending is obtained by calculation, this statement is to present the Pending column in the form you expect.
If you remove this statement, the result will be:

     ;WITH cte
     as(SELECT *,Total=QTY*Rate,
     (SELECT (SELECT SUM(CAST(inv_amt AS INT)) 
     FROM #tbl_InvoiceM 
     WHERE Inv_date<'2021-05-01')
     -
     (SELECT SUM(Level_Four_ID)
     FROM #tbl_Received 
     WHERE R_Date BETWEEN '2021-05-01' AND '2021-05-30')) Pending
     FROM (SELECT DISTINCT SUM(QTY) OVER(PARTITION BY Rate ORDER BY Rate) QTY,Rate,inv_amt
     FROM #tbl_INvoiceD t1
     JOIN #tbl_InvoiceM t2 ON t1.Inv_ID=t2.Inv_ID
     WHERE t2.Inv_date BETWEEN '2021-05-01' AND '2021-05-30' OR t1.DID=2) t)
        
     SELECT QTY,Rate,Total,
     Pending 
     FROM cte
     ORDER BY Rate

Output:
93272-image.png





image.png (10.2 KiB)
image.png (4.4 KiB)
image.png (4.2 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

why 600 is coming with each row

0 Votes 0 ·

When adding a column to SQL, there will always be the number of rows corresponding to the original rows in the table. The pending we calculated has only one number, and if we want to add it to the last column, the same number will appear in each row.


Echo

1 Vote 1 ·
EchoLiu-msft avatar image
0 Votes"
EchoLiu-msft answered akhterhussain-3167 commented

Rate=50 also seems to be the last month's data?

 Please try:
     ;WITH cte
     as(SELECT *,Total=QTY*Rate,
     (SELECT (SELECT SUM(CAST(inv_amt AS INT)) 
     FROM #tbl_InvoiceM 
     WHERE Inv_date<'2021-05-01')
     -
     (SELECT SUM(Level_Four_ID)
     FROM #tbl_Received 
     WHERE R_Date BETWEEN '2021-05-01' AND '2021-05-30')) Pending
     FROM (SELECT DISTINCT SUM(QTY) OVER(PARTITION BY Rate ORDER BY Rate) QTY,Rate,inv_amt
     FROM #tbl_INvoiceD t1
     JOIN #tbl_InvoiceM t2 ON t1.Inv_ID=t2.Inv_ID
     WHERE t2.Inv_date BETWEEN '2021-05-01' AND '2021-05-30') t)
        
     SELECT QTY,Rate,Total,
     CASE WHEN Rate<>600 THEN '' ELSE CAST(Pending AS VARCHAR) END Pending
     FROM cte
     ORDER BY Rate

Output:
93176-image.png




image.png (3.7 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

No,50 is Current month.
Second thing ,why you are fixing 600 ,query should itself calculating pending amount.

   SELECT QTY,Rate,Total,
      CASE WHEN Rate<>600 THEN '' ELSE CAST(Pending AS VARCHAR) END Pending
      FROM cte
      ORDER BY Rate
0 Votes 0 ·