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
