Sql-server – Query Require for Opening and Closing

sql server 2014

I want to Calculate opening and Closing between two table.
Below is Data and expected output

 Create table #tbl_Receivable (Rec_ID int,Inv_ID int ,Customer_ID int,Rec_Date date,Rec_Amount varchar(50),Inv_type varchar(50),item_Weight int);
  Create table #tbl_Shop_InvM (Inv_ID int ,Customer_ID int,E_Date date,Rec_Amount varchar(50),Inv_type varchar(50),item_Weight int);
   Create table #tbl_Customer ( Customer_ID int ,Customer_Name varchar(50));

 insert into #tbl_Customer values (1,'Akhter'), (2,'Hussian');

INSERT INTO #tbl_Shop_InvM VALUES
(211,1,'2020-03-06',5000,'Payable',0), 
(222,1,'2020-03-08',8000,'Payable',0),
(223,2,'2020-03-08',2000,'Payable',0),
(224,1,'2020-03-09',5500,'Payable',0),
(225,2,'2020-03-10',15000,'Payable',500)



INSERT INTO #tbl_Receivable VALUES
   (112,211,1,'2020-03-07',2000,'Received',0),
(115,222,1,'2020-03-09',4000,'Received',0),
(116,222,1,'2020-03-09',2000,'Received',0),
(117,211,1,'2020-03-12',1000,'Received',0),
(118,225,2,'2020-03-11',15000,'Received',0);

I want Below ouput in which Date is giving from 06-03-2020 to 12-03-2020 ,then opending will be 0,because there is not invoice before 06-03-2020 ,
if Date is giving from 12-06-2020 to 12-06-2020 ,then opening will be 09-03-2020 balance 10500

Desire Output

Best Answer

First of all, your Rec_Amount columns should be changed from varchar to something numerical like float.

Secondly, i think you wrote the wrong range in this sentence so i don't understand what you mean here.

if Date is giving from 12-06-2020 to 12-06-2020 ,then opening will be 09-03-2020 balance 10500

I believe this query gives the desired output:

SELECT q.Customer_ID, q.E_Date, q.Payable, q.Received, q.Balance
FROM
(SELECT Customer_ID, E_Date, Payable, Received, SUM(CAST(ISNULL(Payable, 0) AS float)-CAST(ISNULL(Received, 0) AS float)) OVER(PARTITION BY Customer_ID ORDER BY E_Date) AS Balance
FROM
(SELECT s.Customer_ID, s.E_Date, s.Rec_Amount, s.Inv_type
FROM #tbl_Shop_InvM s 
UNION ALL 
SELECT r.Customer_ID, r.Rec_Date, r.Rec_Amount, r.Inv_type
FROM #tbl_Receivable r) AS a 
PIVOT
(MAX(Rec_Amount)
FOR Inv_type IN ([Payable],[Received])) AS p) AS q

/*Set date range */
--WHERE q.E_Date BETWEEN '2020-03-07' AND '2020-03-11'

Output:

Related Solutions

SSMS 2014 Installation – Fixing ‘Invalid License Data’ Error When Opening ASP.NET Files

Visual Studio Shell is just the plumbing required to run the shared components within SSMS. If you want to manage .aspx files etc., you'll need to install the free Visual Studio Express (or a higher edition if Express doesn't have all the functionality you need).

SQL Server – Strange Results for DELETE with ROW_NUMBER

This is expected behaviour at the moment

the function gets evaluated on the DELETE stream.

So it actually behaves like this (pseudo code)

DELETE k
OUTPUT Deleted.name, 
       ROW_NUMBER() OVER (ORDER BY Deleted.object_id) as r
FROM (
    SELECT k.*, ROW_NUMBER() OVER (ORDER BY object_id) r
    FROM #o k
) k
WHERE r <> 1 --OUTPUT returns rows with (r = 1)

Although this is the currently defined expected behaviour it isn't really reasonable and they say

Long term, we need to actually fix the behavior of OUTPUT clause to match that of the ANSI SQL standard which will result in change of results. So we will look at the correct semantics for a future version of SQL Server since there might be apps that rely on the current behavior.

I haven't tested on SQL Server 2014 but on 2012 the plan looks as the below.

enter image description here

After the delete operator (to the left) the column values from the deleted rows are sorted back into object_id order and the row_number re-applied.

It looks like the same is happening in your case from the results. (temp tables have a negative id and are sorted first before sysrscols which has a low positive object_id of 3).

As well as the dubious semantics of the result the second sort by object_id doesn't seem strictly necessary in this plan as it looks likely that they will already be sorted in that order in any event.

Regarding workarounds for this specific case changing the output clause to OUTPUT Deleted.name, 1 + Deleted.r AS r would work.

For more complicated WHERE clauses I think you'd need a pass to calculate the row_number and then a join. e.g.

ALTER TABLE #o
  ADD CONSTRAINT PK PRIMARY KEY (object_id);

WITH k AS
(
 SELECT *, 
        ROW_NUMBER() OVER (ORDER BY object_id) r
FROM #o
)
MERGE k AS k1
using k AS k2
ON k1.object_id = k2.object_id
WHEN matched AND k2.r <> 1 THEN
  DELETE
OUTPUT Deleted.name,
       k2.r;

enter image description here

Results

enter image description here

Best Answer

Related Solutions

SSMS 2014 Installation – Fixing ‘Invalid License Data’ Error When Opening ASP.NET Files

SQL Server – Strange Results for DELETE with ROW_NUMBER

Related Question