Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- Company | UID | Approver | Due | InvoiceDate | InvoiceNumber | Vendor | Created
- S0009 | USR1 | Random Guy | ... | 20.10.2016 | 123456 | Random Vendor1 | 2016-10-30 10:00:22.000
- S0009 | USR1 | Random Guy | ... | 20.10.2016 | 123456 | Random Vendor1 | 2016-10-30 10:00:22.000
- S0009 | USR2 | Some Guy | ... | 01.01.2000 | 123456 | Random Vendor1 | 2016-11-01 12:30:16.000
- S0009 | USR2 | Some Guy | ... | 01.01.2000 | 123456 | Random Vendor1 | 2016-11-01 12:30:16.000
- Company | UID | Approver | Due | InvoiceDate | InvoiceNumber | Vendor | Created
- S0009 | USR2 | Some Guy | ... | 01.01.2000 | 123456 | Random Vendor1 | 2016-11-01 12:30:16.000
- SELECT
- Upper(WFI.COMPANYID) as Company,
- WFI.USERID as UID,
- INF.NAME as Approver,
- CONVERT(varchar(10),duedatetime,4) as Due,
- right(left(Document,20),10) as InvoiceDate,
- SUBSTRING(DOCUMENT,22,charindex(' ',RIGHT(DOCUMENT,len(DOCUMENT)-21))) as InvoiceNumber,
- Right(document,len(Document)-(20+charindex(' ',RIGHT(DOCUMENT,len(DOCUMENT)-21)))) as Vendor,
- wfi.CREATEDDATETIME as Created
- FROM
- [TEST].[tst].[WORKFLOWWORKITEMTABLE] WFI
- INNER JOIN [TEST].[tst].[Workflowtrackingstatustable] WFS ON WFI.CORRELATIONID=WFS.CORRELATIONID
- INNER JOIN [TEST].[tst].[HCMWORKER] HCM on WFI.USERID=HCM.PERSONNELNUMBER
- INNER JOIN [TEST].[tst].[DIRPERSONNAME] DPN ON DPN.PERSON=HCM.PERSON
- INNER JOIN [TEST].[tst].[LEDGERJOURNALTABLE] LJT ON WFI.REFRECID = LJT.RECID
- INNER JOIN [TEST].[tst].[USERINFO] INF ON WFI.USERID = INF.ID
- WHERE
- DATASOURCENAME Like 'Ledgerjourna%'
- AND Datediff(day,Duedatetime,getdate())>3
- AND WFS.DOCUMENTTYPE='Special'
- ORDER BY
- WFI.COMPANYID asc
- SELECT main.* FROM
- (
- SELECT
- Upper(WFI.COMPANYID) as Company
- WFI.USERID as UID,
- INF.NAME as Approver,
- CONVERT(varchar(10),duedatetime,4) as Due,
- right(left(Document,20),10) as InvoiceDate,
- SUBSTRING(DOCUMENT,22,charindex(' ',RIGHT(DOCUMENT,len(DOCUMENT)-21))) as InvoiceNumber,
- Right(document,len(Document)-(20+charindex(' ',RIGHT(DOCUMENT,len(DOCUMENT)-21)))) as Vendor,
- wfi.CREATEDDATETIME as Created,
- --Added RowNumber Function Below
- ROW_NUMBER() OVER (PARTITION BY **InsertYourKeyToAUniqueRecordHere** ORDER BY wfi.CREATEDDATETIME DESC) AS ROWNUM
- FROM
- [TEST].[tst].[WORKFLOWWORKITEMTABLE] WFI
- INNER JOIN [TEST].[tst].[Workflowtrackingstatustable] WFS ON WFI.CORRELATIONID=WFS.CORRELATIONID
- INNER JOIN [TEST].[tst].[HCMWORKER] HCM on WFI.USERID=HCM.PERSONNELNUMBER
- INNER JOIN [TEST].[tst].[DIRPERSONNAME] DPN ON DPN.PERSON=HCM.PERSON
- INNER JOIN [TEST].[tst].[LEDGERJOURNALTABLE] LJT ON WFI.REFRECID = LJT.RECID
- INNER JOIN [TEST].[tst].[USERINFO] INF ON WFI.USERID = INF.ID
- WHERE
- DATASOURCENAME Like 'Ledgerjourna%'
- AND Datediff(day,Duedatetime,getdate())>3
- AND WFS.DOCUMENTTYPE='Special'
- ORDER BY
- )main
- WHERE main.ROWNUM =1 --Add this clause to only return the first record
- main.Company asc
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement