Opening and closing with date filter is not giving me correctly output
-
I want ,when Opening_value and Opening_Date exist in tabel #tbl_Account_L_Four ,then opening_Value will be get from #tbl_Account_L_Four ,if Opening_Date is null then data will get from #tbl_transection table.
Secondly Opening_Date if exists,then data from #tbl_transection table ,onward Opening_Date will be calculated.
as you can see below image ,in which opening_Value of against Level_Four_ID(1222) in a table #tbl_Account_L_Four exist,but current query is calculating '2021-01-18' data,,which should not
be calculated,
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(5,1231,1222,500,'2021-01-20',2,null)
Declare @startDate date='2021-01-12'
Declare @EndDate date='2021-01-20'
Declare @Level_Four_ID int =1222
;With initaltransactions
As(
Select Trans_ID = 0,
Trans_Type = Null
, TransDate = Null,
Trans_Remarks = 'Opening'
, Code = Null, Head = Null
, Debit = iif(coa.Opening_value > 0, coa.Opening_value, 0.00)
, Credit = iif(coa.Opening_value < 0, -coa.Opening_value, 0.00)
From #tbl_Account_L_Four coa
Where coa.Level_Four_ID = @Level_Four_ID and coa.Opening_Date>=@StartDate
Union All
Select t.Trans_ID, Trans_Type = ty.trans_type_name
, TransDate = convert(char(10), t.Trans_Date, 101)
, Trans_Remarks = --(CONCAT( T.Cheque_No ,' ', T.Cheque_Bank ,' ' ,'Cheque',' Date', ' ' ,T.Cheque_Date, ' ' ,'Branch',' ' ,T.Cheque_Branch , ' ','Rs.',CONVERT(varchar(50), CAST(T.Trans_Amount AS money),1),' ','Received From ' ,COAc.Level_Four_Name )) as Trans_Remarks
Case when (t.trans_type_ID=2 ) then concat('Rs.',CONVERT(Varchar(50), Cast(T.trans_Amount as money),1), coac.Level_Four_Name)
when
(t.Trans_Type_ID=7 and coac.Level_Four_ID=410101) then concat('Cash Received From',' ','Walking',',',' Invoice No' ,' ',T.Sell_ID ,',Amount=',T.Trans_Amount)
when
(t.Trans_Type_ID=7 and coac.Level_Four_ID!=410101) then concat('Cash Received From',' ',coac.Level_Four_Name,',',' Invoice No' ,' ',T.Sell_ID ,',Amount=',T.Trans_Amount)
when
(t.Trans_Type_ID=8 ) then concat('Sales To',' ',coa.Level_Four_Name,',',' Invoice No' ,' ',T.Sell_ID ,',Amount=',T.Trans_Amount)
When
(t.Trans_Type_ID=2) then concat(COA.Level_Four_Name ,'
From',' ',Coac.Level_Four_Name, '',T.Trans_Amount)end
, Code = iif(t.Level_Four_ID_C = @Level_Four_ID, t.Level_Four_ID_D, t.Level_Four_ID_C)
, Head = iif(t.Level_Four_ID_C = @Level_Four_ID, coa.Level_Four_Name, coac.Level_Four_Name)
, Debit = iif(t.Level_Four_ID_D = @Level_Four_ID, t.Trans_Amount, 0.00)
, Credit = iif(t.Level_Four_ID_C = @Level_Four_ID, t.Trans_Amount, 0.00)
From #tbl_transection t
Inner Join #tbl_trans_type ty On ty.trans_type_ID = t.Trans_Type_ID
Left Join #tbl_Account_L_Four coa On coa.Level_Four_ID = t.Level_Four_ID_D
Left Join #tbl_Account_L_Four coac On coac.Level_Four_ID = t.Level_Four_ID_C
Where @Level_Four_ID In (t.Level_Four_ID_C, t.Level_Four_ID_D) and t.Trans_Date <= @EndDate
)
, transactions as (
-- Get opening balance
Select Trans_ID = 0, Trans_Type = Null, TransDate = Null
, Trans_Remarks = 'Opening', Code = Null, Head = Null
, Debit = iif(sum(tn.Debit - tn.Credit)>0, sum(tn.Debit - tn.Credit), 0.00)
, Credit = iif(sum(tn.Debit - tn.Credit)<0, -sum(tn.Debit - tn.Credit), 0.00)
From initaltransactions tn
WHERE tn.TransDate < @startDate OR tn.Trans_ID = 0
UNION ALL
Select tn.Trans_ID, tn.Trans_Type, tn.TransDate
, tn.Trans_Remarks , tn.Code, tn.Head
, tn.Debit, tn.Credit
From initaltransactions tn
WHERE tn.TransDate BETWEEN @startDate AND @EndDate
)
,cte2
as(Select tn.Trans_ID, tn.Trans_Type, tn.TransDate
, tn.Trans_Remarks, tn.Code, tn.Head
, CASE WHEN Trans_ID = 0 THEN NULL ELSE tn.Debit END as Debit
, CASE WHEN Trans_ID = 0 THEN NULL ELSE tn.Credit END AS Credit
, Balance = sum(tn.Debit - tn.Credit) over(Order By tn.Trans_Id)
From transactions tn
Union All
Select Trans_ID = 9999, Trans_Type = Null , Trans_Date = Null
, Trans_Remarks = 'Total', Code = Null, Head = Null
, Debit = sum(tn.Debit), Credit = sum(tn.Credit)
, Balance = sum(tn.Debit) - sum(tn.Credit)
From transactions tn)
select * from cte2
order by case when Trans_ID =0 then '01/01/1900'
when Trans_ID =9999 then '12/31/9999'
else TransDate end,Trans_ID;
DROP TABLE #tbl_Account_L_Four;
DROP TABLE #tbl_trans_type;
DROP TABLE #tbl_transection;
- akhterhussain80 wrote:
I want ...
Keep at it you're on the right track. Your query doesn't seem to return any error so what's the question? What's Wrong Output and Correct Output seems to depend on your viewpoint and not SQL Server
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
HI Steve Collins,
can you use below date filter with above query then check correct output ,that query is giving same output
Declare @startDate date='2021-01-19'
Declare @EndDate date='2021-01-20'i am having issue on below row
INSERT INTO #tbl_Account_L_Four VALUES(1222,'Cheque In Hand',5000,'2021-01-18')
Query will first check in table #tbl_Account_L_Four that ,if opening_value is exist and opening_date ,then query will get opening and later on after opening_date will get data from #tbl_transection table
-
Ok the set up and explanation are appreciated. Right off the bat tho there's an issue (which is hopefully a typo) with the sample data. In the #tbl_transection table in the last row of input there's a duplicate Trans_ID. Because the first 2 rows of #tbl_transection are identical but for the Trans_ID and because your query will need to select 1 and only 1 row to serve as @startDate, a primary key in #tbl_transection is required imo. Also, it would be helpful to know if the Level_Four_ID column is primary key of #tbl_Account_L_Four. Assuming these things as resolved the set up I'm working with is as follows
drop table if exists #tbl_Account_L_Four;
go
Create table #tbl_Account_L_Four (
Level_Four_ID int primary key not null,
Level_Four_Name varchar(50),
Opening_Value decimal(10,2),
Opening_Date date);
drop table if exists #tbl_trans_type;
go
Create table #tbl_trans_type (
Trans_Type_ID int,
trans_type_name varchar(50));
drop table if exists #tbl_transection;
go
Create table #tbl_transection (
Trans_ID int primary key not null,
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)
,(1222,'Cheque In Hand',5000,'2021-01-18')
,(1215,'MBL 833968',null,null);
insert into #tbl_trans_type VALUES
(1,'Online')
,(2,'Cheque')
,(3,'Deposite')
,(4,'Tranfer')
,(5,'Return');
INSERT INTO #tbl_transection VALUES
(1,1231,1222,50000,'2021-01-18',2,null)
,(2,1231,1222,50000,'2021-01-18',2,null)
,(3,1222,1215,44444,'2021-01-18',3,null)
,(4,1215,1222,44444,'2021-01-18',5,null)
,(5,1222,1231,44444,'2021-01-19',2,null)
--,(5,1231,1222,500,'2021-01-20',2,null);
,(6,1231,1222,500,'2021-01-20',2,null);
Declare @startDate date='2021-01-12'
Declare @EndDate date='2021-01-20'
--Declare @Level_Four_ID int =1222
-- set 1
select *
from #tbl_Account_L_Four
where Opening_Date is not null;
-- set 2
select *
from #tbl_Account_L_Four
where Opening_Date is null;
/* to get transactions for set 1 */
with set_1_cte as (
select *
from #tbl_Account_L_Four
where Opening_Date is not null)
select t.*
from #tbl_transection t
cross join set_1_cte s1
where t.Trans_date>s1.Opening_Date;The set 1 grouping corresponds to the @Level_Four_ID declared variable. This is the straightforward case. The set 2 grouping is more challenging. Also, your summary rows could be generated using GROUPING SETs. It would be helpful to confirm the set up so we might know the primary key(s) of your tables
Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können
-
Trans_ID is primary key in #tbl_transection,i could not more understand ,which you mentioned above.any query please ask to me.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply