August 20, 2015 at 12:55 pm
Hi All,
I am creating a report query that returns all unreconciled P/O lines. I am near completion but could do with some help with the last part. I am unable to find a way to remove the reconciled
records.
I have included a script to produce some sample table, data & query.
The recordset dispalys 6 rows. All reconciled Supplier Invoices are duplicated and have transaction codes 40, 50 and reconcile code of 9 (5024, 921689471).
All unreconciled only appear once and have transaction codes 40 and reconcile code of 0 (4835 & 921978016). These are the only records that I want to show.
CREATE TABLE [dbo].[Purch_Ledger](
[EPDIVI] [nvarchar](3) NULL,
[EPSUNO] [nvarchar](10) NULL,
[EPSINO] [nvarchar](24) NULL,
[EPDUDT] [numeric](8, 0) NULL,
[EPTRCD] [numeric](2, 0) NULL,
[EPRECO] [numeric](1, 0) NULL,
[EPCUAM] [numeric](15, 2) NULL,
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Purch_Ledger]
([EPDIVI],[EPSUNO],[EPSINO],[EPDUDT],[EPTRCD],[EPRECO],[EPCUAM])
VALUES ('2HI','S100000','4835','20140501','40',0,'-307.87'),
('2HI','S100000','5024','20140928','40',0,'-138.53'),
('2HI','S100000','5024','20140928','50','9','138.53'),
('2HI','S100002','921689471','20140614','40','0','-401.34'),
('2HI','S100002','921689471','20140614','50','9','401.34'),
('2HI','S100002','921978016','20140614','40',0,'-113.94')
GO
SELECT
EPDIVI AS DIV
,EPSUNO AS 'Supplier No'
,EPSINO AS 'Suppier Inv No'
,CONVERT(DATE,CAST(EPDUDT AS VARCHAR(8)),112) AS 'Invoice Due Date'
,EPTRCD AS 'Transaction Code'
,EPRECO 'Reconcile Code'
,DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) AS 'Age In Days'
,SUM(CASE WHEN DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) > -30 AND DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) <= -1 THEN EPCUAM ELSE 0 END) AS [0 to 30 Days Overdue]
,SUM(CASE WHEN DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) >= -31 AND DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) <= -60 THEN EPCUAM ELSE 0 END) AS [> 31 to 60 Days Overdue]
,SUM(CASE WHEN DATEDIFF(DAY,CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)),112), GETDATE()) > 60 THEN EPCUAM ELSE 0 END) AS [> 60 Days Overdue]
,SUM(EPCUAM) AS 'Total Debt'
,SUM(CASE WHEN DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) > -1 AND DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) <= 30 THEN EPCUAM ELSE 0 END) AS [> 0 to 30 Days Due]
,SUM(CASE WHEN DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) >= 31 AND DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) <= 60 THEN EPCUAM ELSE 0 END) AS [> 31 to 60 Days Due]
,SUM(CASE WHEN DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) >= 60 THEN EPCUAM ELSE 0 END) AS [60 > Days Due]
FROM [dbo].[Purch_Ledger]
GROUP BY EPSUNO,EPSINO,EPDUDT,EPTRCD,EPRECO,EPCUAM,EPDIVI
HAVING SUM(EPCUAM)<>0
ORDER BY EPSINO,EPTRCD
Whatever I try I cant find a way to get rid of the unwanted records. Any assistance will be appreciated.
Thanks for taking the time to read my dilemma & hopefully somebody has a fix
Regards
August 20, 2015 at 1:11 pm
Is this what you are trying to achieve?
SELECT
U.EPDIVI AS DIV
,U.EPSUNO AS 'Supplier No'
,U.EPSINO AS 'Suppier Inv No'
,CONVERT(DATE,CAST(U.EPDUDT AS VARCHAR(8)),112) AS 'Invoice Due Date'
,U.EPTRCD AS 'Transaction Code'
,U.EPRECO 'Reconcile Code'
,DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(U.EPDUDT AS VARCHAR(8)), 112)) AS 'Age In Days'
,SUM(CASE WHEN DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(U.EPDUDT AS VARCHAR(8)), 112)) > -30 AND DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(U.EPDUDT AS VARCHAR(8)), 112)) <= -1 THEN U.EPCUAM ELSE 0 END) AS [0 to 30 Days Overdue]
,SUM(CASE WHEN DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(U.EPDUDT AS VARCHAR(8)), 112)) >= -31 AND DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(U.EPDUDT AS VARCHAR(8)), 112)) <= -60 THEN U.EPCUAM ELSE 0 END) AS [> 31 to 60 Days Overdue]
,SUM(CASE WHEN DATEDIFF(DAY,CONVERT(DATE, CAST(U.EPDUDT AS VARCHAR(8)),112), GETDATE()) > 60 THEN U.EPCUAM ELSE 0 END) AS [> 60 Days Overdue]
,SUM(U.EPCUAM) AS 'Total Debt'
,SUM(CASE WHEN DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(U.EPDUDT AS VARCHAR(8)), 112)) > -1 AND DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(U.EPDUDT AS VARCHAR(8)), 112)) <= 30 THEN U.EPCUAM ELSE 0 END) AS [> 0 to 30 Days Due]
,SUM(CASE WHEN DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(U.EPDUDT AS VARCHAR(8)), 112)) >= 31 AND DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(U.EPDUDT AS VARCHAR(8)), 112)) <= 60 THEN U.EPCUAM ELSE 0 END) AS [> 31 to 60 Days Due]
,SUM(CASE WHEN DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(U.EPDUDT AS VARCHAR(8)), 112)) >= 60 THEN U.EPCUAM ELSE 0 END) AS [60 > Days Due]
FROM [dbo].[Purch_Ledger] U
Left join [dbo].[Purch_Ledger] R on U.EPSINO = R.EPSINO and R.EPTRCD = 50
where U.EPTRCD = 40
and R.EPDIVI is null
GROUP BY U.EPSUNO,U.EPSINO,U.EPDUDT,U.EPTRCD,U.EPRECO,U.EPCUAM,U.EPDIVI
HAVING SUM(U.EPCUAM)<>0
ORDER BY U.EPSINO,U.EPTRCD
August 20, 2015 at 1:31 pm
Hi,
Just what I needed. Many Thanks! 🙂
August 20, 2015 at 2:48 pm
Pack_Star (8/20/2015)
Hi All,I am creating a report query that returns all unreconciled P/O lines. I am near completion but could do with some help with the last part. I am unable to find a way to remove the reconciled
records.
I have included a script to produce some sample table, data & query.
The recordset dispalys 6 rows. All reconciled Supplier Invoices are duplicated and have transaction codes 40, 50 and reconcile code of 9 (5024, 921689471).
All unreconciled only appear once and have transaction codes 40 and reconcile code of 0 (4835 & 921978016). These are the only records that I want to show.
CREATE TABLE [dbo].[Purch_Ledger](
[EPDIVI] [nvarchar](3) NULL,
[EPSUNO] [nvarchar](10) NULL,
[EPSINO] [nvarchar](24) NULL,
[EPDUDT] [numeric](8, 0) NULL,
[EPTRCD] [numeric](2, 0) NULL,
[EPRECO] [numeric](1, 0) NULL,
[EPCUAM] [numeric](15, 2) NULL,
) ON [PRIMARY]
GO
INSERT INTO [dbo].[Purch_Ledger]
([EPDIVI],[EPSUNO],[EPSINO],[EPDUDT],[EPTRCD],[EPRECO],[EPCUAM])
VALUES ('2HI','S100000','4835','20140501','40',0,'-307.87'),
('2HI','S100000','5024','20140928','40',0,'-138.53'),
('2HI','S100000','5024','20140928','50','9','138.53'),
('2HI','S100002','921689471','20140614','40','0','-401.34'),
('2HI','S100002','921689471','20140614','50','9','401.34'),
('2HI','S100002','921978016','20140614','40',0,'-113.94')
GO
SELECT
EPDIVI AS DIV
,EPSUNO AS 'Supplier No'
,EPSINO AS 'Suppier Inv No'
,CONVERT(DATE,CAST(EPDUDT AS VARCHAR(8)),112) AS 'Invoice Due Date'
,EPTRCD AS 'Transaction Code'
,EPRECO 'Reconcile Code'
,DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) AS 'Age In Days'
,SUM(CASE WHEN DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) > -30 AND DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) <= -1 THEN EPCUAM ELSE 0 END) AS [0 to 30 Days Overdue]
,SUM(CASE WHEN DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) >= -31 AND DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) <= -60 THEN EPCUAM ELSE 0 END) AS [> 31 to 60 Days Overdue]
,SUM(CASE WHEN DATEDIFF(DAY,CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)),112), GETDATE()) > 60 THEN EPCUAM ELSE 0 END) AS [> 60 Days Overdue]
,SUM(EPCUAM) AS 'Total Debt'
,SUM(CASE WHEN DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) > -1 AND DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) <= 30 THEN EPCUAM ELSE 0 END) AS [> 0 to 30 Days Due]
,SUM(CASE WHEN DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) >= 31 AND DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) <= 60 THEN EPCUAM ELSE 0 END) AS [> 31 to 60 Days Due]
,SUM(CASE WHEN DATEDIFF(DAY, GETDATE(),CONVERT(DATE, CAST(EPDUDT AS VARCHAR(8)), 112)) >= 60 THEN EPCUAM ELSE 0 END) AS [60 > Days Due]
FROM [dbo].[Purch_Ledger]
GROUP BY EPSUNO,EPSINO,EPDUDT,EPTRCD,EPRECO,EPCUAM,EPDIVI
HAVING SUM(EPCUAM)<>0
ORDER BY EPSINO,EPTRCD
Whatever I try I cant find a way to get rid of the unwanted records. Any assistance will be appreciated.
Thanks for taking the time to read my dilemma & hopefully somebody has a fix
Regards
Are you by chance using M3? These columns names look might familiar as of late.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply