Share
Report
110Data Doesn't match in Dynamics GP and SSRS report. Total Due is showing different value in SSRS.
I can't find where's the error here
USE [QNAQ]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER FUNCTION [dbo].[HistoricalAging2](@x AS DATETIME)
RETURNS TABLE
AS
RETURN
WITH CTE1 AS
(
SELECT CM.CUSTNAME AS CustName,
SUM(CASE WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM
WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL > 6 THEN RM.CURTRXAM * -1
ELSE 0 END) AS 'TotalDue',
SUM(CASE WHEN DATEDIFF(DAY,RM.GLPOSTDT,@x)<31 THEN
CASE WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM
WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL > 6 THEN RM.CURTRXAM * -1
ELSE 0 END ELSE 0 END) AS 'CurrentDays',
SUM(CASE WHEN DATEDIFF(DAY,RM.GLPOSTDT,@x)>30 AND DATEDIFF(DAY,RM.GLPOSTDT,@x)<61 THEN
CASE WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM
WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL > 6 THEN RM.CURTRXAM * -1
ELSE 0 END ELSE 0 END) AS Group1,
SUM(CASE WHEN DATEDIFF(DAY,RM.GLPOSTDT,@x)>60 AND DATEDIFF(DAY,RM.GLPOSTDT,@x)<91 THEN
CASE WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM
WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL > 6 THEN RM.CURTRXAM * -1
ELSE 0 END ELSE 0 END) AS Group2,
SUM(CASE WHEN DATEDIFF(DAY,RM.GLPOSTDT,@x)>90 THEN
CASE WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM
WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL > 6 THEN RM.CURTRXAM * -1
ELSE 0 END ELSE 0 END) AS Group3
FROM RM20101 AS RM
JOIN RM00101 AS CM ON RM.CUSTNMBR = CM.CUSTNMBR
GROUP BY CM.CUSTNAME
HAVING SUM(CASE WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL < 7 THEN RM.CURTRXAM
WHEN @x >= RM.GLPOSTDT AND RM.DINVPDOF = '1900-01-01 00:00:00.000' AND RM.RMDTYPAL > 6 THEN RM.CURTRXAM * -1
ELSE 0 END)<>0
UNION
SELECT CM1.CUSTNAME AS CustName,
SUM(CASE WHEN @x>= RM1.APPLYTOGLPOSTDATE AND @x < RM1.APFRDCDT THEN RM1.APPTOAMT ELSE 0 END) AS TotalDue,
SUM(CASE WHEN DATEDIFF(DAY,RM1.APPLYTOGLPOSTDATE,@x)<31 THEN
CASE WHEN @x>= RM1.APPLYTOGLPOSTDATE AND @x < RM1.APFRDCDT THEN RM1.APPTOAMT ELSE 0 END ELSE 0 END) AS 'CurrentDays',
SUM(CASE WHEN DATEDIFF(DAY,RM1.APPLYTOGLPOSTDATE,@x)>30 AND DATEDIFF(DAY,RM1.APPLYTOGLPOSTDATE,@x)<61 THEN
CASE WHEN @x>= RM1.APPLYTOGLPOSTDATE AND @x < RM1.APFRDCDT THEN RM1.APPTOAMT ELSE 0 END ELSE 0 END) AS Group1,
SUM(CASE WHEN DATEDIFF(DAY,RM1.APPLYTOGLPOSTDATE,@x)>60 AND DATEDIFF(DAY,RM1.APPLYTOGLPOSTDATE,@x)<91 THEN
CASE WHEN @x>= RM1.APPLYTOGLPOSTDATE AND @x < RM1.APFRDCDT THEN RM1.APPTOAMT ELSE 0 END ELSE 0 END) AS Group2,
SUM(CASE WHEN DATEDIFF(DAY,RM1.APPLYTOGLPOSTDATE,@x)>90 THEN
CASE WHEN @x>= RM1.APPLYTOGLPOSTDATE AND @x < RM1.APFRDCDT THEN RM1.APPTOAMT ELSE 0 END ELSE 0 END) AS Group3
FROM RM20201 AS RM1
JOIN RM00101 AS CM1
ON RM1.CUSTNMBR = CM1.CUSTNMBR
GROUP BY CM1.CUSTNAME
HAVING SUM(CASE WHEN @x>= RM1.APPLYTOGLPOSTDATE AND @x < RM1.APFRDCDT THEN RM1.APPTOAMT ELSE 0 END) <> 0
UNION
SELECT CM2.CUSTNAME AS CustName,
SUM(CASE WHEN @x>= RM2.APPLYTOGLPOSTDATE AND @x < RM2.APFRDCDT THEN RM2.APPTOAMT ELSE 0 END) AS TotalDue,
SUM(CASE WHEN DATEDIFF(DAY,RM2.APPLYTOGLPOSTDATE,@x)<31 THEN
CASE WHEN @x>= RM2.APPLYTOGLPOSTDATE AND @x < RM2.APFRDCDT THEN RM2.APPTOAMT ELSE 0 END ELSE 0 END) AS 'CurrentDays',
SUM(CASE WHEN DATEDIFF(DAY,RM2.APPLYTOGLPOSTDATE,@x)>30 AND DATEDIFF(DAY,RM2.APPLYTOGLPOSTDATE,@x)<61 THEN
CASE WHEN @x>= RM2.APPLYTOGLPOSTDATE AND @x < RM2.APFRDCDT THEN RM2.APPTOAMT ELSE 0 END ELSE 0 END) AS Group1,
SUM(CASE WHEN DATEDIFF(DAY,RM2.APPLYTOGLPOSTDATE,@x)>60 AND DATEDIFF(DAY,RM2.APPLYTOGLPOSTDATE,@x)<91 THEN
CASE WHEN @x>= RM2.APPLYTOGLPOSTDATE AND @x < RM2.APFRDCDT THEN RM2.APPTOAMT ELSE 0 END ELSE 0 END) AS Group2,
SUM(CASE WHEN DATEDIFF(DAY,RM2.APPLYTOGLPOSTDATE,@x)>90 THEN
CASE WHEN @x>= RM2.APPLYTOGLPOSTDATE AND @x < RM2.APFRDCDT THEN RM2.APPTOAMT ELSE 0 END ELSE 0 END) AS Group3
FROM RM30201 AS RM2
JOIN RM00101 AS CM2
ON RM2.CUSTNMBR = CM2.CUSTNMBR
GROUP BY CM2.CUSTNAME
HAVING SUM(CASE WHEN @x>= RM2.APPLYTOGLPOSTDATE AND @x < RM2.APFRDCDT THEN RM2.APPTOAMT ELSE 0 END) <> 0
)
SELECT CustName, SUM(TotalDue) AS 'TotalDue', SUM(CurrentDays) AS 'Current',
SUM(Group1) AS '31-60 Days', SUM(Group2) AS '61-90 Days', SUM(Group3) AS '91+ Days'
FROM CTE1
GROUP BY CustName;
*This post is locked for comments
All responses (
Answers (
