July 17, 2011 at 6:48 pm
Hi,
I have 3 tables, invoice_head, invoice_lines and Companies. Invoice_lines is basically a order for every invoice(invoice_head). Each invoice has at least one order but can have many. Each order is identified by the dual PK Line_id.
Table Invoice_head
PK invoice_id
Table Invoice_lines
PK invoice_id
PK Line_id
Table Companies
Companies_id
I would like SQL to show one row for the invoice_head and each row invoice_line(order) if it exists, when the company is =xx. I have written a SQL code but it's repeats the invoice_head row each time there is an invoice_line(order).
Ideally it should also do it for where the invoice_lines.approvalDate is equal to the previous buisness day, e.g approvalDate= Friday when it's Monday and approvalDate=Monday when it's Tuesday etc...
e.g. Table Head columns----------------Table Line columns
invoice.id 1-----------------------line.id 1
null------------------------------line.id 2
null------------------------------line.id 3
invoice.id 2-----------------------line.id 1
invoice.id 3-----------------------line.id 1
invoice.id 4-----------------------line.id 1
null-------------------------------line.id 2
I have attached excel spreadsheets showing the current extract and expected extract. Plus the SQL code to create these tables and data. I have highlighted invoice number 132887 as a example in the spreadsheets,
(i raised this in a previous topic but it was getting to long and confusing for everyone);-)
July 17, 2011 at 7:48 pm
This is possible using some xml tricks and the like, but really, it's not what the tool wants to do. It's a reporting/subreport organization, and SQL doesn't do that naturally. This is more along the lines of the old mainframe feeder files that are position dependent.
What end result are you trying to achieve? Not just getting it to format in SQL server, but where is this going when you're done with it? Doing this in SQL Server is one of the hardest approaches available.
Also, if you can provide DDL and sample data in the method you'll find in the first link in my signature, you'll have a better chance at getting help with readily consumable testing structures.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
July 17, 2011 at 9:40 pm
How are you going to Join the Invoice_Head Table to the Company Table if you do not store the Company_ID in the Invoice_Head Table?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
July 17, 2011 at 9:44 pm
I'd suggest you write a SQL Server Reporting Services report that does this (not at all hard) and then you can just dump the report results into Excel. Turn the company into a selectable (drop down) parameter.
July 18, 2011 at 1:05 am
I have finally got it after a couple of weeks, here's the code if anyone is interested;
[Code="SQL"]
WITH cte ( Invoice_id, IssueDate,optional_1,scandate,supplier,optional_2,invoicenumber,rejected,completed,notified,comments,transferred,
optional_3,invoiceTotal,optional_4,optional_5, amount_1,responsible,creditnote,currency,paymentdate,optional_7,
Account, Optional_6, optional_8,department,optional_7l,optional_1l,linetext,approver,optional_3l,approvedatel,optional_5l,
responsiblel,remarks,amount_2l,amount3l,linetotal,RowId)
AS (
SELECT h.Invoice_id,h.IssueDate,h.optional_1,h.scandate,h.supplier,h.optional_2,h.invoicenumber,h.rejected,
h.completed,h.notified,h.comments,h.transferred, h.optional_3,h.InvoiceTotal,h.optional_4,h.optional_5,h.amount_1,h.responsible,h.iscredit,
h.currency,h.paymentdate,h.optional_7, l.Account,l.optional_6,l.optional_8,l.Department,l.Optional_7,l.optional_1,
l.linetext,l.approver,l.optional_3,l.approveDate,l.optional_5,l.Responsible,remarks,l.amount_2,l.amount_3,l.linetotal,
ROW_NUMBER() OVER ( PARTITION BY h.Invoice_id ORDER BY h.Invoice_id ASC )
FROM Companies c
JOIN Invoice_head h ON h.company_id = c.company_id
JOIN Invoice_lines l ON l.invoice_id = h.invoice_id
WHERE ( c.company_id = 35 )
AND ( l.ApproveDate >= ( CASE WHEN DATEPART(dw, GETDATE()) = 2 THEN ( GETDATE() - 3 )
ELSE ( GETDATE() - 1 )
END ) )
)
SELECT CASE WHEN RowId = 1 THEN CAST(Invoice_id AS VARCHAR(20))
ELSE ''
END AS [Invoice_id],
CASE WHEN RowId = 1 THEN CAST(IssueDate AS VARCHAR(20))
ELSE ''
END AS [Inovice Date],
CASE WHEN RowId = 1 THEN CAST(optional_1 AS VARCHAR(50))
ELSE ''
END AS [Division\Buyer],
CASE WHEN RowId = 1 THEN CAST(ScanDate AS VARCHAR(20))
ELSE ''
END AS [Scan Date],
CASE WHEN RowId = 1 THEN CAST(Supplier AS VARCHAR(50))
ELSE ''
END AS [Supplier\Vendor],
CASE WHEN RowId = 1 THEN CAST(optional_2 AS VARCHAR(50))
ELSE ''
END AS [PONumber],
CASE WHEN RowId = 1 THEN CAST(invoicenumber AS VARCHAR(50))
ELSE ''
END AS [Invoice Number],
CASE WHEN RowId = 1 THEN CAST(rejected AS VARCHAR(20))
ELSE ''
END AS [Rejected],
CASE WHEN RowId = 1 THEN CAST(completed AS VARCHAR(20))
ELSE ''
END AS [Transaction Date],
CASE WHEN RowId = 1 THEN CAST(Notified AS VARCHAR(20))
ELSE ''
END AS [Notified],
CASE WHEN RowId = 1 THEN CAST(Comments AS VARCHAR(500))
ELSE ''
END AS [Comments],
CASE WHEN RowId = 1 THEN CAST(Transferred AS VARCHAR(20))
ELSE ''
END AS [Transferred],
CASE WHEN RowId = 1 THEN CAST(Optional_3 AS VARCHAR(50))
ELSE ''
END AS [InvoiceType],
CASE WHEN RowId = 1 THEN CAST(ScanDate AS VARCHAR(20))
ELSE ''
END AS [Scan Date],
CASE WHEN RowId = 1 THEN CAST(InvoiceTotal AS VARCHAR(20))
ELSE ''
END AS [Total Invoice Amount],
CASE WHEN RowId = 1 THEN CAST(Optional_4 AS VARCHAR(20))
ELSE ''
END AS [TFN],
CASE WHEN RowId = 1 THEN CAST(Optional_5 AS VARCHAR(50))
ELSE ''
END AS [Orginator],
CASE WHEN RowId = 1 THEN CAST(Amount_1 AS VARCHAR(20))
ELSE ''
END AS [GST Total Amount],
CASE WHEN RowId = 1 THEN CAST(Responsible AS VARCHAR(20))
ELSE ''
END AS [Responsible],
CASE WHEN RowId = 1 THEN CAST(creditnote AS VARCHAR(20))
ELSE ''
END AS [Credit Note],
CASE WHEN RowId = 1 THEN CAST(Currency AS VARCHAR(20))
ELSE ''
END AS [Currency],
CASE WHEN RowId = 1 THEN CAST(PaymentDate AS VARCHAR(20))
ELSE ''
END AS [BCC Upload Date 1],
CASE WHEN RowId = 1 THEN CAST(Optional_7 AS VARCHAR(20))
ELSE ''
END AS [Supplier Code],Account,Optional_6 AS [Job Number], Optional_8 AS [Cost Type Code],Department AS [Department Code],
Optional_7l AS [Employee Code], optional_1l AS [Product Code],linetext AS [Description], Approver AS [Completed By],
optional_3l AS [Division Code], approvedatel AS [BCC Upload Date], optional_5l AS [Client Code], Responsiblel AS [Responsible],remarks,
amount_2l AS [Total Excl GST], amount3l AS [GST], linetotal AS [Total Incl GST]
FROM cte
[/CODE]
😛
Though there were is not completely right.
July 18, 2011 at 3:40 am
Wow. It would have been way easier to do this in SSRS. Is there some reason why it has to be done in SQL?
July 18, 2011 at 4:35 pm
I am adding the code to package flow I have designed that will be added to a SQL job so it can run automatically everyday. I tried doing it in SSRS and couldn't work out how to get the single row for the first half of the output.
July 18, 2011 at 4:47 pm
ringovski (7/18/2011)
I am adding the code to package flow I have designed that will be added to a SQL job so it can run automatically everyday. I tried doing it in SSRS and couldn't work out how to get the single row for the first half of the output.
I believe that you are referring to the Headers, i.e Report, Page & Group Headers?
Once you get used to it you will appreciate how much it makes your lie easier. 😎
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply