Require Expected Output

Transact-SQL
1

I want ,when Opening_value and Opening_Date exist in table #tbl_Account_L_Four ,then opening_Value will get from #tbl_Account_L_Four and after opening_date value will get from #tbl_Transection table on wards,if Opening_Date is null then data will get from #tbl_transection table.

as you can see below image ,in which opening_Value of against Level_Four_ID(1222) in a table #tbl_Account_L_Four exist.

Create table #tbl_Account_L_Four (Level_Four_ID int,Level_Four_Name 
varchar(50),Opening_Value  decimal(10,2),Opening_Date date)

Create table #tbl_trans_type (Trans_Type_ID int,trans_type_name varchar(50))
Create table #tbl_transection (Trans_ID int,Level_Four_ID_C int,Level_Four_ID_D int,Trans_Amount Decimal(10,2),Trans_date date,Trans_Type_ID int,sell_ID int)
INSERT INTO #tbl_Account_L_Four VALUES(1231,'Abdul Rauf',null,null)
INSERT INTO #tbl_Account_L_Four VALUES(1222,'Cheque In Hand',5000,'2021-01-18')
INSERT INTO #tbl_Account_L_Four VALUES(1215,'MBL 833968',null,null)


insert into #tbl_trans_type VALUES(1,'Online')
insert into #tbl_trans_type VALUES(2,'Cheque')
insert into #tbl_trans_type VALUES(3,'Deposite')
insert into #tbl_trans_type VALUES(4,'Tranfer')
insert into #tbl_trans_type VALUES(5,'Return')

INSERT INTO #tbl_transection VALUES(1,1231,1222,50000,'2021-01-18',2,null)
INSERT INTO #tbl_transection VALUES(2,1231,1222,50000,'2021-01-18',2,null)
INSERT INTO #tbl_transection VALUES(3,1222,1215,44444,'2021-01-18',3,null) 
INSERT INTO #tbl_transection VALUES(4,1215,1222,44444,'2021-01-18',5,null)
INSERT INTO #tbl_transection VALUES(5,1222,1231,44444,'2021-01-19',2,null)  
 INSERT INTO #tbl_transection VALUES(6,1231,1222,500,'2021-01-20',2,null)

Note : Output filter on date .secondly 1231 ID which is in tbl_Account_L_Four .

image
image888×193 4.72 KB

2

How do you get Opening of 5000 for Code 1231 ?

3

sorry it was 1222

4

Can you update the expected result to show the correct value ? Also for the Opening_Value, does it includes the transactions where Trans_date = Opening_Date?

For this question you can use similar technique as your previous question. Have you try anything ?

1 Like
5

same output,opening will be 5000 before opening date , and before trans_date.

yes it will firstly check ,if opening_date and Opening_Value exist then get opening_value after word it will proceed into tbl_transction table ,then check trans_date ,

i tried ,but it is not displaying individual balance against trasection.

image
image888×193 4.72 KB

6

can you post what you've tried? Your requirements don't make sense. What are Level_Four_ID_C and Level_Four_ID_D? how do you know they are the same transaction?

1 Like
7

@mike01
i tried below ,please make it check at your end ,and give me expert opinion about below query

Create table #tbl_Account_L_Four (Level_Four_ID int,Level_Four_Name 
varchar(50),Opening_Value  decimal(10,2),Opening_Date date)

Create table #tbl_trans_type (Trans_Type_ID int,trans_type_name varchar(50))
Create table #tbl_transection (Trans_ID int,Level_Four_ID_C int,Level_Four_ID_D int,Trans_Amount Decimal(10,2),Trans_date date,Trans_Type_ID int,sell_ID int)
INSERT INTO #tbl_Account_L_Four VALUES(1231,'Abdul Rauf',null,null)
INSERT INTO #tbl_Account_L_Four VALUES(1222,'Cheque In Hand',5000,'2023-10-18')
INSERT INTO #tbl_Account_L_Four VALUES(1215,'MBL 833968',null,null)


insert into #tbl_trans_type VALUES(1,'Online')
insert into #tbl_trans_type VALUES(2,'Cheque')
insert into #tbl_trans_type VALUES(3,'Deposite')
insert into #tbl_trans_type VALUES(4,'Tranfer')
insert into #tbl_trans_type VALUES(5,'Return')

INSERT INTO #tbl_transection VALUES(1,1231,1222,50000,'2023-10-18',2,null)
INSERT INTO #tbl_transection VALUES(2,1231,1222,50000,'2023-10-18',2,null)
INSERT INTO #tbl_transection VALUES(3,1222,1215,44444,'2023-10-18',3,null) 
INSERT INTO #tbl_transection VALUES(4,1215,1222,44444,'2023-10-18',5,null)
INSERT INTO #tbl_transection VALUES(5,1222,1231,44444,'2023-10-19',2,null)  
 INSERT INTO #tbl_transection VALUES(6,1231,1222,500,'2023-10-20',2,null)

 declare @C_ID as int = 1222
declare @start as date = '2023-10-19'
declare @end as date = '2023-10-20'

; WITH Unio AS (
SELECT NULL AS Trans_ID, Opening_Date AS E_Date,
IIF(Opening_value > 0, Opening_value, 0) AS Debit,
IIF(Opening_value < 0, Opening_value, 0) AS Credit,
Opening_value AS Amount
FROM #tbl_Account_L_Four
WHERE Level_Four_ID = @C_ID
UNION ALL
SELECT Trans_ID, Trans_date, IIF(Level_Four_ID_D = @C_ID, Trans_Amount, 0),
IIF(Level_Four_ID_C = @C_ID, Trans_Amount, 0),
CASE WHEN Level_Four_ID_D = @C_ID THEN Trans_Amount
WHEN Level_Four_ID_c = @C_ID THEN -1 * Trans_Amount
END
FROM #tbl_transection
WHERE Trans_date > (SELECT Opening_Date FROM #tbl_Account_L_Four WHERE Level_Four_ID = @C_ID) or Trans_date>'2023-10-01'
),
runsum AS (
SELECT Trans_ID, E_Date, Debit, Credit,
SUM(Amount) OVER(ORDER BY E_Date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS Balance
FROM Unio
),
prevbal AS (

select Top(1)BALANCE prevbal from runsum
where E_Date<@start
Order by E_Date desc,Trans_ID desc)
SELECT NULL AS ID, NULL AS E_Date, 'Opening' AS Remarks,
NULL AS Debit, NULL AS Credit, prevbal AS Balance
FROM prevbal

UNION ALL
SELECT Trans_ID, E_Date, '' AS Remarks, Debit, Credit, Balance
FROM runsum
WHERE E_Date BETWEEN @start AND @end
ORDER BY E_Date asc
8

Similar technique as your last question. The following solution uses CROSS APPLY to unpivot the tbl_transaction. And then UNION ALL to combine with the opening balance. And for the running balance, use sum() over () to calculate it

declare @C_ID as int = 1222
declare @start as date = '2023-10-19'
declare @end as date = '2023-10-20';

with cte as
(
  select C_ID    = a.Level_Four_ID,
         Trans_ID = 0,
         Remarks = 'Opening',
         Debit   = null,
         Credit  = null,
         Balance = a.Opening_Value
  from   #tbl_Account_L_Four a
  where  a.Level_Four_ID = @C_ID

  union all
  
  select C_ID    = a.Level_Four_ID,
         t.Trans_ID,
         Remarks = '',
         Debit   = case when t.Trans_Amount > 0 then +Trans_Amount else 0 end,
         Credit  = case when t.Trans_Amount < 0 then -Trans_Amount else 0 end,
         Balance = t.Trans_Amount
  from   #tbl_Account_L_Four a
         inner join 
         (
             select v.Level_Four_ID, t.Trans_ID, t.Trans_date, v.Trans_Amount
             from   #tbl_transection t 
                    cross apply
                    (
                        values
                        (Level_Four_ID_C, -Trans_Amount),
                        (Level_Four_ID_D, +Trans_Amount)
                    ) v (Level_Four_ID, Trans_Amount)
         ) t           on a.Level_Four_ID = t.Level_Four_ID
  where  a.Level_Four_ID = @C_ID
  and    t.Trans_date >= @start
  and    t.Trans_date <= @end
)
select C_ID, Trans_ID, Remarks, Debit, Credit, 
       Balance = sum(Balance) over (partition by C_ID order by Trans_ID)
from   cte
order by C_ID, Trans_ID

db<>fiddle demo

9

I have added one row in tbl_transection table

INSERT INTO #tbl_transection VALUES(0,1231,1222,500,'2023-10-17',2,null)

Check Above link,

image

17-10-2023 row is displaying in output ,which should not be,becasue opening_date of 1222 is 2023-10-18.so on ward transection must display

10

you have @start which is earlier than the opening date. Use a case expression to check for that and adjust the @start accordingly

select @start = case when @start < Opening_Date then Opening_Date else @start end
from   #tbl_Account_L_Four
where ...
11

please can you provide in fiddle ,with whole query,it will be easy for me to understand

12

Add these after the DECLARE before the CTE

select @start = case when @start < Opening_Date then Opening_Date else @start end
from   #tbl_Account_L_Four a
where  a.Level_Four_ID = @C_ID;
13

if i use it before CTE ,then how to join it with below cte

14

You don't need to. This is a complete statement.



declare @C_ID as int = 1222
declare @start as date = '2023-10-19'
declare @end as date = '2023-10-20';

-- insert the select @start here

with cte as
(
. . .. 
)