capture non existing data

Hemant Karnik 121 Reputation points
2022-01-24T13:32:37.803+00:00

hi
I have a table name limit_master and we are string only one product in this table
currently i am having 50 branches and we are maintaining limits of each branch differently
Secondly we have data upto '31-mar-2010' in this table

However I want to generate report up to getdate() in below format

lbrcode         effdate        limit_amt

how can we do it ?
please help

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,617 questions
Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,546 questions
{count} votes

8 answers

Sort by: Most helpful
  1. Hemant Karnik 121 Reputation points
    2022-01-24T14:32:02.857+00:00

    create table
    LBrCode int no 4 10 0 no (n/a) (n/a) NULL
    PrdCd char no 8 no no no SQL_Latin1_General_CP850_BIN2
    EffDate datetime no 8 no (n/a) (n/a) NULL
    Limit float no 8 53 NULL no (n/a) (n/a) NULL
    DbtrAddMk int no 4 10 0 no (n/a) (n/a) NULL
    DbtrAddMb int no 4 10 0 no (n/a) (n/a) NULL
    DbtrAddMs smallint no 2 5 0 no (n/a) (n/a) NULL
    DbtrAddMd datetime no 8 no (n/a) (n/a) NULL
    DbtrAddMt datetime no 8 no (n/a) (n/a) NULL
    DbtrAddCk int no 4 10 0 no (n/a) (n/a) NULL
    DbtrAddCb int no 4 10 0 no (n/a) (n/a) NULL
    DbtrAddCs smallint no 2 5 0 no (n/a) (n/a) NULL
    DbtrAddCd datetime no 8 no (n/a) (n/a) NULL
    DbtrAddCt datetime no 8 no (n/a) (n/a) NULL
    DbtrLupdMk int no 4 10 0 no (n/a) (n/a) NULL
    DbtrLupdMb int no 4 10 0 no (n/a) (n/a) NULL
    DbtrLupdMs smallint no 2 5 0 no (n/a) (n/a) NULL
    DbtrLupdMd datetime no 8 no (n/a) (n/a) NULL
    DbtrLupdMt datetime no 8 no (n/a) (n/a) NULL
    DbtrLupdCk int no 4 10 0 no (n/a) (n/a) NULL
    DbtrLupdCb int no 4 10 0 no (n/a) (n/a) NULL
    DbtrLupdCs smallint no 2 5 0 no (n/a) (n/a) NULL
    DbtrLupdCd datetime no 8 no (n/a) (n/a) NULL
    DbtrLupdCt datetime no 8 no (n/a) (n/a) NULL
    DbtrTAuthDone smallint no 2 5 0 no (n/a) (n/a) NULL
    DbtrRecStat tinyint no 1 3 0 no (n/a) (n/a) NULL
    DbtrAuthDone tinyint no 1 3 0 no (n/a) (n/a) NULL
    DbtrAuthNeeded tinyint no 1 3 0 no (n/a) (n/a) NULL
    DbtrUpdtChkId smallint no 2 5 0 no (n/a) (n/a) NULL
    DbtrLHisTrnNo int no 4 10 0 no (n/a) (n/a) NULL
    Remarks char no 30 no no no SQL_Latin1_General_CP850_BIN2

    0 comments No comments

  2. Hemant Karnik 121 Reputation points
    2022-01-24T16:31:44.817+00:00
    SELECT * FROM limit_masterINSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
    VALUES (1, 'CASH', '2010-08-01', 30000000, 999, 1, 3, '2010-08-04', '1899-12-30 18:06:07', 0, 0, 0, '1900-01-01', '1899-12-30', 0, 0, 0, '1900-01-01', '1899-12-30', 0, 0, 0, '1900-01-01', '1899-12-30', 0, 0, 0, 1, 1, 0, '')
    GO
    
    INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
    VALUES (1, 'CASH', '2011-04-01', 30000000, 999, 5, 15, '2012-05-05', '1900-01-01 11:09:19', 18, 999, 10, '2013-04-03', '1900-01-01 12:50:50', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 1, 0, 1, 0, 2, 7, 'CASH LIMIT')
    GO
    
    INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
    VALUES (1, 'CASH', '2013-03-31', 50000000, 282, 999, 74, '2013-04-03', '1900-01-01 12:50:22', 18, 999, 10, '2013-04-03', '1900-01-01 12:50:47', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 1, 0, 1, 0, 2, 6, '')
    GO
    
    INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
    VALUES (1, 'CASH', '2014-05-01', 50000000, 282, 999, 74, '2014-06-18', '1900-01-01 13:34:55', 247, 999, 4, '2014-06-18', '1900-01-01 15:25:38', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 1, 0, 1, 0, 2, 58, 'REVISED CASH RETENTION LIMIT')
    GO
    
    INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
    VALUES (1, 'CASH', '2018-05-08', 70000000, 4, 999, 63, '2018-09-19', '1900-01-01 17:52:54', 211, 999, 63, '2018-09-19', '1900-01-01 17:54:32', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 1, 0, 1, 0, 2, 218, 'REVISED')
    GO
    
    INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
    VALUES (1, 'CASH', '2019-04-09', 70000000, 4, 999, 63, '2019-04-09', '1900-01-01 16:56:29', 211, 999, 260, '2019-04-09', '1900-01-01 17:18:51', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 1, 0, 1, 0, 2, 360, 'CRL FOR F.Y. 2019-2020')
    GO
    
    INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
    VALUES (2, 'CASH', '2010-08-01', 30000000, 999, 1, 3, '2010-08-04', '1900-01-01 18:06:07', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, 1, 1, 0, '')
    GO
    
    INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
    VALUES (2, 'CASH', '2011-04-01', 30000000, 999, 5, 15, '2012-05-05', '1900-01-01 11:09:19', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, 1, 1, 0, 'CASH LIMIT')
    GO
    
    INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
    VALUES (2, 'CASH', '2014-05-01', 30000000, 282, 999, 74, '2014-06-18', '1900-01-01 13:35:20', 247, 999, 4, '2014-06-18', '1900-01-01 15:25:49', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 1, 0, 1, 0, 2, 59, 'REVISED CASH RETENTION LIMIT')
    GO
    
    INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
    VALUES (2, 'CASH', '2018-05-08', 30000000, 4, 999, 63, '2018-09-20', '1900-01-01 10:36:59', 211, 999, 119, '2018-09-20', '1900-01-01 11:20:50', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 1, 0, 1, 0, 2, 268, 'Cash retention limit')
    GO
    
    INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
    VALUES (2, 'CASH', '2019-04-09', 30000000, 4, 999, 63, '2019-04-09', '1900-01-01 16:58:08', 211, 999, 260, '2019-04-09', '1900-01-01 17:19:17', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 1, 0, 1, 0, 2, 361, 'CRL FOR F.Y. 2019-2020')
    GO
    
    INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
    VALUES (3, 'CASH', '2010-08-01', 30000000, 999, 1, 3, '2010-08-04', '1900-01-01 18:06:07', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, 1, 1, 0, '')
    
    0 comments No comments

  3. Ronen Ariely 15,096 Reputation points
    2022-01-24T22:59:00.033+00:00

    Hi :-)

    I fixed your queries as below and now we have a table with sample data. Can you please elaborate what is your expected result according to the sample data?

    And please describe how you got to the result (a few rows for example)

    So, here are you queries to create the table and insert the data, after I fixed them


    use tempdb
    GO
    
    create table limit_master(
    LBrCode int NULL,
    PrdCd char (10),
    EffDate datetime NULL,
    Limit float NULL,
    DbtrAddMk int NULL,
    DbtrAddMb int NULL,
    DbtrAddMs smallint NULL,
    DbtrAddMd datetime NULL,
    DbtrAddMt datetime NULL,
    DbtrAddCk int NULL,
    DbtrAddCb int NULL,
    DbtrAddCs smallint NULL,
    DbtrAddCd datetime NULL,
    DbtrAddCt datetime NULL,
    DbtrLupdMk int NULL,
    DbtrLupdMb int NULL,
    DbtrLupdMs smallint NULL,
    DbtrLupdMd datetime NULL,
    DbtrLupdMt datetime NULL,
    DbtrLupdCk int NULL,
    DbtrLupdCb int NULL,
    DbtrLupdCs smallint NULL,
    DbtrLupdCd datetime NULL,
    DbtrLupdCt datetime NULL,
    DbtrTAuthDone smallint NULL,
    DbtrRecStat tinyint NULL,
    DbtrAuthDone tinyint NULL,
    DbtrAuthNeeded tinyint NULL,
    DbtrUpdtChkId smallint  NULL,
    DbtrLHisTrnNo int NULL,
    Remarks char(30)
    )
    GO
    
     INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
     VALUES (1, 'CASH', '2010-08-01', 30000000, 999, 1, 3, '2010-08-04', '1899-12-30 18:06:07', 0, 0, 0, '1900-01-01', '1899-12-30', 0, 0, 0, '1900-01-01', '1899-12-30', 0, 0, 0, '1900-01-01', '1899-12-30', 0, 0, 0, 1, 1, 0, '')
     GO
    
     INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
     VALUES (1, 'CASH', '2011-04-01', 30000000, 999, 5, 15, '2012-05-05', '1900-01-01 11:09:19', 18, 999, 10, '2013-04-03', '1900-01-01 12:50:50', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 1, 0, 1, 0, 2, 7, 'CASH LIMIT')
     GO
    
     INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
     VALUES (1, 'CASH', '2013-03-31', 50000000, 282, 999, 74, '2013-04-03', '1900-01-01 12:50:22', 18, 999, 10, '2013-04-03', '1900-01-01 12:50:47', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 1, 0, 1, 0, 2, 6, '')
     GO
    
     INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
     VALUES (1, 'CASH', '2014-05-01', 50000000, 282, 999, 74, '2014-06-18', '1900-01-01 13:34:55', 247, 999, 4, '2014-06-18', '1900-01-01 15:25:38', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 1, 0, 1, 0, 2, 58, 'REVISED CASH RETENTION LIMIT')
     GO
    
     INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
     VALUES (1, 'CASH', '2018-05-08', 70000000, 4, 999, 63, '2018-09-19', '1900-01-01 17:52:54', 211, 999, 63, '2018-09-19', '1900-01-01 17:54:32', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 1, 0, 1, 0, 2, 218, 'REVISED')
     GO
    
     INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
     VALUES (1, 'CASH', '2019-04-09', 70000000, 4, 999, 63, '2019-04-09', '1900-01-01 16:56:29', 211, 999, 260, '2019-04-09', '1900-01-01 17:18:51', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 1, 0, 1, 0, 2, 360, 'CRL FOR F.Y. 2019-2020')
     GO
    
     INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
     VALUES (2, 'CASH', '2010-08-01', 30000000, 999, 1, 3, '2010-08-04', '1900-01-01 18:06:07', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, 1, 1, 0, '')
     GO
    
     INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
     VALUES (2, 'CASH', '2011-04-01', 30000000, 999, 5, 15, '2012-05-05', '1900-01-01 11:09:19', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, 1, 1, 0, 'CASH LIMIT')
     GO
    
     INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
     VALUES (2, 'CASH', '2014-05-01', 30000000, 282, 999, 74, '2014-06-18', '1900-01-01 13:35:20', 247, 999, 4, '2014-06-18', '1900-01-01 15:25:49', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 1, 0, 1, 0, 2, 59, 'REVISED CASH RETENTION LIMIT')
     GO
    
     INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
     VALUES (2, 'CASH', '2018-05-08', 30000000, 4, 999, 63, '2018-09-20', '1900-01-01 10:36:59', 211, 999, 119, '2018-09-20', '1900-01-01 11:20:50', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 1, 0, 1, 0, 2, 268, 'Cash retention limit')
     GO
    
     INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
     VALUES (2, 'CASH', '2019-04-09', 30000000, 4, 999, 63, '2019-04-09', '1900-01-01 16:58:08', 211, 999, 260, '2019-04-09', '1900-01-01 17:19:17', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 1, 0, 1, 0, 2, 361, 'CRL FOR F.Y. 2019-2020')
     GO
    
     INSERT INTO dbo.limit_master (LBrCode, PrdCd, EffDate, Limit, DbtrAddMk, DbtrAddMb, DbtrAddMs, DbtrAddMd, DbtrAddMt, DbtrAddCk, DbtrAddCb, DbtrAddCs, DbtrAddCd, DbtrAddCt, DbtrLupdMk, DbtrLupdMb, DbtrLupdMs, DbtrLupdMd, DbtrLupdMt, DbtrLupdCk, DbtrLupdCb, DbtrLupdCs, DbtrLupdCd, DbtrLupdCt, DbtrTAuthDone, DbtrRecStat, DbtrAuthDone, DbtrAuthNeeded, DbtrUpdtChkId, DbtrLHisTrnNo, Remarks)
     VALUES (3, 'CASH', '2010-08-01', 30000000, 999, 1, 3, '2010-08-04', '1900-01-01 18:06:07', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, '1900-01-01', '1900-01-01', 0, 0, 0, 1, 1, 0, '')
     GO
    
     SELECT * FROM limit_master
    GO
    
    0 comments No comments

  4. Hemant Karnik 121 Reputation points
    2022-01-25T05:03:15.41+00:00

    hi
    expected answer is

    LBrCode EffDate limit

    the rows i expect to continue till yesterday keeping the limit restrictions as of old one


  5. Hemant Karnik 121 Reputation points
    2022-01-25T05:18:37.403+00:00
    LBrCode PrdCd   EffDate Limit   DbtrAddMk   DbtrAddMb   DbtrAddMs   DbtrAddMd   DbtrAddMt   DbtrAddCk   DbtrAddCb   DbtrAddCs   DbtrAddCd   DbtrAddCt   DbtrLupdMk  DbtrLupdMb  DbtrLupdMs  DbtrLupdMd  DbtrLupdMt  DbtrLupdCk  DbtrLupdCb  DbtrLupdCs  DbtrLupdCd  DbtrLupdCt  DbtrTAuthDone   DbtrRecStat DbtrAuthDone    DbtrAuthNeeded  DbtrUpdtChkId   DbtrLHisTrnNo   Remarks
    1   CASH        2010-08-01 00:00:00.000 30000000    999 1   3   2010-08-04 00:00:00.000 1899-12-30 18:06:07.000 0   0   0   1900-01-01 00:00:00.000 1899-12-30 00:00:00.000 0   0   0   1900-01-01 00:00:00.000 1899-12-30 00:00:00.000 0   0   0   1900-01-01 00:00:00.000 1899-12-30 00:00:00.000 0   0   0   1   1   0                                 
    1   CASH        2011-04-01 00:00:00.000 30000000    999 5   15  2012-05-05 00:00:00.000 1900-01-01 11:09:19.000 18  999 10  2013-04-03 00:00:00.000 1900-01-01 12:50:50.000 0   0   0   1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 0   0   0   1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1   0   1   0   2   7   CASH LIMIT                    
    1   CASH        2013-03-31 00:00:00.000 50000000    282 999 74  2013-04-03 00:00:00.000 1900-01-01 12:50:22.000 18  999 10  2013-04-03 00:00:00.000 1900-01-01 12:50:47.000 0   0   0   1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 0   0   0   1900-01-01 00:00:00.000 1900-01-01 00:00:00.000 1   0   1   0   2   6                                 
    
    0 comments No comments