capture non existing data

Hemant Karnik 121

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

Ronen Ariely 15,096 Reputation points

2022-01-24T13:43:16.8+00:00

Hi,

First step, you need a table with data

Please provide queries to create the relevant table and insert some sample rows

According to the sample data please describe what is the requested result
Yitzhak Khabinsky 25,116 Reputation points

2022-01-24T13:44:11.567+00:00

While asking a question you need to provide a minimal reproducible example:
(1) DDL and sample data population, i.e. CREATE table(s) plus INSERT, T-SQL statements.
(2) What you need to do, i.e. logic, and your attempt implementation of it in T-SQL.
(3) Desired output based on the sample data in the #1 above.
(4) Your SQL Server version (SELECT @@version;)
Hemant Karnik 121 Reputation points

2022-01-24T14:09:08.937+00:00

yes sir

I want to send table structure and sample data
hoe can send it
sir ?

8 answers

Hemant Karnik 121 Reputation points

2022-01-25T06:50:37.027+00:00

here is a sample data
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
Please sign in to rate this answer.

0 comments No comments
Sign in to comment
Hemant Karnik 121 Reputation points

2022-01-25T07:10:42.647+00:00

let me try again
Please sign in to rate this answer.
LiHong-MSFT 10,046 Reputation points

2022-01-25T08:16:36.203+00:00

And what result you want according to this sample data.

Tips: Before you submit,click "Preview"button,and have a check.

Hemant Karnik 121 Reputation points

2022-01-25T08:40:05.527+00:00

same result sir
but upto yesterday
the thing is
in my table i do not have records till yesteday i.e. 24 -jan-2022
how can i add these recrods in my table?

Hemant Karnik 121 Reputation points

2022-01-25T11:35:10.603+00:00

select top 3 * from limit_master

LiHong-MSFT 10,046 Reputation points

2022-01-26T07:55:03.507+00:00

how can i add these recrods in my table?

You want to fill in missing dates,with other columns NULL ?

Ronen Ariely 15,096 Reputation points

2022-01-27T11:16:24.547+00:00

Your image cannot be see unless we open it in a new windows. You could publish it like bellow for example
Sign in to comment

Hemant Karnik 121

with main as (
select lbrcode,efdate,limit from limit_master
)
,
hrk as (
select main.lbrcode,level effdate,limit  from dual
connect by level<='30-jun-2021'-max(main.effdate)
)
select a.*,b.* from main a ,hrk b

is this possible? sir please help