Smart SQL Query to Get Combine Customer Age Reports from all Companies in Database

 Hi Friends,

I want to share one Smart SQL Query tto Get Combine Customer Age Reports from all Companies in Database.

It is very useful when same customer code created in multiple companies and we need to get combine age reports.

SP1:

/****** Object:  StoredProcedure [dbo].[CustomerAging_duedate_1]    Script Date: 11/17/2022 12:51:28 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Alter proc [dbo].[CustomerAging_duedate_1]

 @asondt datetime,

 @comp varchar(50)

 as

 begin

 if OBJECT_ID('#report') is not null drop table #report

 create table #report

 (

 [Company] varchar(50),

[Customer No.]  varchar(20),

[Customer Name] varchar(50),

[Currency] varchar(10),

[Posting Date] datetime,

[Document Type]  integer,

[Document No.] varchar(20),

[Description] varchar(100),

[Document Date] datetime ,

[Due Date] datetime,

[Original Amount] numeric(38,20),

[Balance] numeric(38,20),

[Balance Due] numeric(38,20),

[Outstanding Days] numeric(38,20),

[Balance (LCY)] numeric(38,20)

)

 declare @sql  nvarchar(max)

 

 set @sql = 'Insert into #report ( [Company],[Customer No.],[Customer Name],[Currency],[Posting Date],[Document Type],[Document No.],[Description],[Document Date],[Due Date],[Original Amount],[Balance],[Balance Due],[Outstanding Days],[Balance (LCY)] )'

 set @sql = @sql + 'SELECT  [Company] = '''+ @comp  + ''',[Customer No.] = c.No_ ,[Customer Name] = c.Name,[Currency] = l.[Currency Code],[Posting Date] = l.[Posting Date]  ,

[Document Type] =l.[Document Type],[Document No.] = l.[Document No_],[Description] = l.Description,[Document Date] = l.[Document Date],

[Due Date] = l.[Due Date],[Original Amount] =a.[Amount (LCY)],[Balance] = a.[Remening Amount],[Balance Due] = a.[Remening Amount (LCY)],[Outstanding Days] = DATEDIFF(dd, l.[Due Date], @asondt),

[Balance (LCY)] = a.[Remening Amount (LCY)] FROM ['+ @comp + '$Customer] c inner join [' + @comp  +'$Cust_ Ledger Entry] l on c.No_=l.[Customer No_]

OUTER APPLY  (SELECT  sum(case when [Entry Type] = 2 then 0 else [Amount (LCY)] end) [Amount (LCY)], sum([Amount (LCY)]) [Remening Amount (LCY)]

        ,sum(case when [Entry Type] = 2 then 0 else [Amount] end) [Amount], sum([Amount]) [Remening Amount]

FROM [' + @comp + '$Detailed Cust_ Ledg_ Entry]

WHERE [Cust_ Ledger Entry No_] = l.[Entry No_] and [Posting Date] <=  CONVERT(smalldatetime,@asondt ,103)  ) a where l.[Posting Date] <=  CONVERT(smalldatetime,@asondt ,103)' 

print(@sql) 

EXEC sys.sp_executesql @sql, N'@asondt smalldatetime',@asondt

--select * from #report

select 

[Company],

[Customer No.],

[Customer Name],

[Currency],

[Posting Date],

[Document Type],

[Document No.],

[Description],

[Document Date],

[Due Date],

[Original Amount],

[Balance Due] = case when [Outstanding Days] >= 0 then [Balance] else 0 end,

[Not Due] =case when [Outstanding Days] < 0 then [Balance] else 0 end,

[0 - 30 Days] = case when  [Outstanding Days] between 0 and 30 then [Balance] else 0 end,

[31 - 60 Days] = case when  [Outstanding Days] between 31 and 60 then [Balance] else 0 end,

[61 - 90 Days] = case when  [Outstanding Days] between 61 and 90 then [Balance] else 0 end, 

[Over 90 Days] = case when  [Outstanding Days] >90 then [Balance] else 0 end, 

[Balance in LCY] = [Balance (LCY)] 

from #report where Balance <>0

end

=======================

SP2:

/****** Object:  StoredProcedure [dbo].[CustomerAging_duedate_2]    Script Date: 11/17/2022 12:51:53 AM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

Create proc [dbo].[CustomerAging_duedate_2]

@asondt datetime

 as

 begin

  if OBJECT_ID('#report1') is not null drop table #report1

 --& ( ) . / \

 

  select  ROW_NUMBER () over (order by Name) as sr, * into #temp1 from Company

 declare @i integer

 declare @icnt integer

 set @i = 1

 set @icnt = (select max(sr) from #temp1)

 create table #report1 (

[Company] varchar(50),

[Customer No.]  varchar(20),

[Customer Name] varchar(50),

[Currency] varchar(10),

[Posting Date] datetime,

[Document Type]  integer,

[Document No.] varchar(20),

[Description] varchar(100),

[Document Date] datetime ,

[Due Date] datetime,

[Original Amount] numeric(38,20),

[Balance Due] numeric(38,20),

[Not Due] numeric(38,20),

[0 - 30 Days] numeric(38,20),

[31 - 60 Days] numeric(38,20),

[61 - 90 Days] numeric(38,20),

[Over 90 Days] numeric(38,20),

[Balance in LCY] numeric(38,20)

)

 declare @comp varchar(100)

 while @icnt >= @i begin

 set @comp = (select Name from #temp1 where sr = @i)

 --& ( ) . / \

set @comp = REPLACE(@comp,'&','_')

set @comp = REPLACE(@comp,'.','_')

set @comp = REPLACE(@comp,'/','_')

set @comp = REPLACE(@comp,'\','_')

print(@comp)

 insert into #report1 (

 [Company] ,

[Customer No.] ,

[Customer Name] ,

[Currency] ,

[Posting Date] ,

[Document Type] ,

[Document No.] ,

[Description] ,

[Document Date] ,

[Due Date] ,

[Original Amount] ,

[Balance Due] ,

[Not Due] ,

[0 - 30 Days],

[31 - 60 Days] ,

[61 - 90 Days] ,

[Over 90 Days] ,

[Balance in LCY] )

exec CustomerAging_duedate_1 @asondt,@comp

set @i = @i +1

end

select * from  #report1

 end

=====================================

Run Second Query only pass date to get age data

EXEC [CustomerAging_duedate_2] '2022-11-01'

It will be very useful query.

Keep Sharing....Keep Growing....