July 7, 2011 at 12:21 am
Thanks David and opc.three you guys are awesome, it seem to give the basic structure I need even though I don't understand the code that well.
A couple of questions
1. where in the code do I put the alias i need for the column names, eg. issue date as Invoice Date.
2. I need to add a whole bunch of invoice_head and invoice_line columns how do i this. I have tried playing with the code and haven't been able to get it to work.
3. Can you explain how the case works only looking at rowid =1. Is it only assigning a value to the row and not actually looking at the invoice_line.line_id?
July 7, 2011 at 2:00 am
1. where in the code do I put the alias i need for the column names, eg. issue date as Invoice Date.
Just add the alias to the end of the column select, ie
CASE WHEN RowId = 1 THEN CAST(IssueDate AS VARCHAR(20)) ELSE '' END AS [Invoice Date]
2. I need to add a whole bunch of invoice_head and invoice_line columns how do i this. I have tried playing with the code and haven't been able to get it to work.
You will need to add them to the select in the cte, the declaration for the cte and the final select (also see below).
3. Can you explain how the case works only looking at rowid =1. Is it only assigning a value to the row and not actually looking at the invoice_line.line_id?
It is using ROW_NUMBER (aliased as RowId) to assign a sequential number to each line starting at 1 for each unique invoice_id and therefore RowId 1 is the first line of the invoice.
I noticed that your DDL has Line_id in the Table Invoice Lines. Is this the same as the ROW_NUMBER described above, ie starting at 1 for each invoice, If so then you do not need a cte and the query can be simplified.
Far away is close at hand in the images of elsewhere.
Anon.
July 7, 2011 at 6:01 am
ringovski (7/6/2011)
Not sure you mean by DDL & DML, but here are the table specs.
Take a look at the first link in my signature line below... it'll help you get tested code for answers very quickly. 🙂
--Jeff Moden
July 7, 2011 at 8:54 pm
Jeff Moden (7/7/2011)
ringovski (7/6/2011)
Not sure you mean by DDL & DML, but here are the table specs.Take a look at the first link in my signature line below... it'll help you get tested code for answers very quickly. 🙂
Jeff thanks for etiquette tips I will try to post things in a clear way, if they haven't been already.
July 7, 2011 at 9:21 pm
David Burrows (7/7/2011)
1. where in the code do I put the alias i need for the column names, eg. issue date as Invoice Date.
Just add the alias to the end of the column select, ie
CASE WHEN RowId = 1 THEN CAST(IssueDate AS VARCHAR(20)) ELSE '' END AS [Invoice Date]
2. I need to add a whole bunch of invoice_head and invoice_line columns how do i this. I have tried playing with the code and haven't been able to get it to work.
You will need to add them to the select in the cte, the declaration for the cte and the final select (also see below).
3. Can you explain how the case works only looking at rowid =1. Is it only assigning a value to the row and not actually looking at the invoice_line.line_id?
It is using ROW_NUMBER (aliased as RowId) to assign a sequential number to each line starting at 1 for each unique invoice_id and therefore RowId 1 is the first line of the invoice.
I noticed that your DDL has Line_id in the Table Invoice Lines. Is this the same as the ROW_NUMBER described above, ie starting at 1 for each invoice, If so then you do not need a cte and the query can be simplified.
To answer you question yes I think it is.The invoice_id shows the same invoice number in both tables invoice_head and invoice_line.
When a invoice has only one purchase order the invoice_line.line_id is '1', easy no dramas.
But a single invoice can many purchase orders, so the line_id can be 1 to any number. When there is more than one purchase order I don't the invoice_head columns again. So there is only one row of invoice_head.invoice_id & columns......, with all the invoice_lines columns for each purchase order.
Hope this is clear and I haven't confused you 🙂
July 7, 2011 at 9:31 pm
Can you explain the end of the second case statement, why the it has
'END AS [Invoice Date], Account,ApproveDate', but the first case only has [Invoice Date]?(what do you have to do to get the formating right, it looks fine in preview, then goes all straight:crazy:).
WITH cte ( Invoice_id, IssueDate, Account, ApproveDate, RowId )
AS (
SELECT h.Invoice_id,h.IssueDate,l.Account,l.ApproveDate,
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 = 32 )
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 Date],
CASE WHEN RowId = 1 THEN CAST(IssueDate AS VARCHAR(20))
ELSE ''
END AS [Invoice Date], Account,ApproveDate
FROM cte
July 7, 2011 at 10:56 pm
ringovski (7/7/2011)
David Burrows (7/7/2011)
1. where in the code do I put the alias i need for the column names, eg. issue date as Invoice Date.
Just add the alias to the end of the column select, ie
CASE WHEN RowId = 1 THEN CAST(IssueDate AS VARCHAR(20)) ELSE '' END AS [Invoice Date]
2. I need to add a whole bunch of invoice_head and invoice_line columns how do i this. I have tried playing with the code and haven't been able to get it to work.
You will need to add them to the select in the cte, the declaration for the cte and the final select (also see below).
3. Can you explain how the case works only looking at rowid =1. Is it only assigning a value to the row and not actually looking at the invoice_line.line_id?
It is using ROW_NUMBER (aliased as RowId) to assign a sequential number to each line starting at 1 for each unique invoice_id and therefore RowId 1 is the first line of the invoice.
I noticed that your DDL has Line_id in the Table Invoice Lines. Is this the same as the ROW_NUMBER described above, ie starting at 1 for each invoice, If so then you do not need a cte and the query can be simplified.
To answer you question yes I think it is.The invoice_id shows the same invoice number in both tables invoice_head and invoice_line.
When a invoice has only one purchase order the invoice_line.line_id is '1', easy no dramas.
But a single invoice can many purchase orders, so the line_id can be 1 to any number. When there is more than one purchase order I don't the invoice_head columns again. So there is only one row of invoice_head.invoice_id & columns......, with all the invoice_lines columns for each purchase order.
Hope this is clear and I haven't confused you 🙂
ringovski, no offense, but we can't see what's in your head or on your screen. This is why we ask for sample data and your expected results from that sample data. I know you provided some expected results, but without both sides we're somewhat grasping at straws. Having the tables, your data and the expected results helps us infer what it is that you are after, and we can then reverse-engineer the logic and queries required to produce those results.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
July 7, 2011 at 11:51 pm
Sorry I thought it was clear, the previous provided SQL script is almost exactly what I need.
I have attached a excel spreadsheet that show the format of invoice that I need. It shows my current SQL output, the require SQL output and your SQL output which is very close.
Columns A-V from Invoice_head
Columns W-AL from Invoice_line
(some of the headings in excel are changed from table name)
You can see the difference in invoice number 131968, thanks.
July 8, 2011 at 2:26 am
ringovski (7/7/2011)
But a single invoice can many purchase orders, so the line_id can be 1 to any number. When there is more than one purchase order I don't the invoice_head columns again. So there is only one row of invoice_head.invoice_id & columns......, with all the invoice_lines columns for each purchase order.
So is the data like this
Invoiceid PO Line_id
1 1 1
1 2 1
1 2 2
1 3 1
2 4 1
2 4 2
or this
Invoiceid PO Line_id
1 1 1
1 2 2
1 2 3
1 3 4
2 4 1
2 4 2
If it is the second then try this
SELECT h.Invoice_id,
h.IssueDate AS [Invoice Date],
h.Optional_1 AS [Division Buyer],
h.ScanDate AS [Scan Date],
h.Supplier AS [Supplier/Vendor],
h.Optional_2 AS [PO Number],
h.InvoiceNumber AS [Invoice Number],
h.Rejected,
h.Completed AS [Transaction Date],
h.Notified,
h.Comments,
h.Transferred,
h.Optional_3 AS [Invoice Type],
h.InvoiceTotal AS [Total Invoice Amount],
h.Optional_4 AS [TFN],
h.Optional_5 AS [Orginator],
h.Amount_1 AS [GST Total Amount],
h.Responsible,
h.IsCredit AS [Credit Note],
h.Currency,
h.PaymentDate AS [BCC Upload Date1],
h.Optional_7 AS [Supplier Code],
l.Account AS [Account Code],
l.Optional_6 AS [Job Number],
l.Optional_8 AS [Cost Type Code],
l.Department AS [Department Code],
l.Optional_7 AS [Employee Code],
l.Optional_1 AS [Product Code],
l.Optional_9 AS [Campaign Code],
l.Optional_10 AS [Optional],
l.LineText AS [Description],
l.Approver AS [Completed By],
l.Optional_3 AS [Division Code],
l.ApproveDate AS [BCC Upload Date],
l.Optional_5 AS [Client Code],
l.Responsible AS [Previous Approvers],
l.Remarks,
l.Amount_2 AS [Total Excl GST],
l.Amount_3 AS [GST],
l.LineTotal AS [Total Inc GST]
FROM Companies c
JOIN Invoice_Head x
ON x.company_id = c.company_id
JOIN Invoice_Lines l
ON l.Invoice_id = x.Invoice_id
LEFT JOIN Invoice_Head h
ON h.Invoice_id = l.Invoice_id
AND l.line_id = 1
WHERE c.company_id = 44
AND c.companyname = h.Optional_1
AND l.Invoice_id ='135291'
AND l.ApproveDate >= CASE WHEN DATEPART(dw, GETDATE()) = 2 THEN (GETDATE() - 3) ELSE (GETDATE() - 1) END
ORDER BY l.Invoice_id ASC, l.Line_id ASC
*Edited to correct syntax errors
Far away is close at hand in the images of elsewhere.
Anon.
July 8, 2011 at 7:05 am
ringovski (7/7/2011)
Jeff Moden (7/7/2011)
ringovski (7/6/2011)
Not sure you mean by DDL & DML, but here are the table specs.Take a look at the first link in my signature line below... it'll help you get tested code for answers very quickly. 🙂
Jeff thanks for etiquette tips I will try to post things in a clear way, if they haven't been already.
It's not just a "clear way"... it makes it all easier on the people that try to help. With some exceptions, I'll normally help the people who have taken the time to create readily comsumable data, first. If I don't get to the others, oh well. 😉
--Jeff Moden
July 10, 2011 at 7:31 pm
thanks for update Jeff, it is more like.
invoice_id Line_id
1..............1
1..............2
1..............3
2..............1
2..............2
3..............1
4..............1
5..............1
6..............1
6..............2
And you got gets an error when I compile it;
Msg 4145, Level 15, State 1, Line 22
An expression of non-boolean type specified in a context where a condition is expected, near 'WHERE'. Thanks.
July 10, 2011 at 11:38 pm
I definitely have some issues with the script. I cleaned it up as much as possible without actually looking at the data, but I think you have some inherent flaws:
1. The Companies table should have an inner join on your invoice header record. The invoice has to be tied to some company record. A cross join is extremely poor but I stuck it in...
2. If your sub query is only one table, don't use an alias if is is not needed.
3. Any time you have a grouping, you have to list EVERY field in the selection in the grouping unless you are using key words like "MAX" and "MIN".
4. You are grouping your invoice line item detail, but you are not summing amounts, only grouping them so that will likely lead to an inaccurate result set. In your current query, two identical lines on one invoice are treated only as one line, thereby making the amounts incorrect.
5. Your last "Group By" seems to be more likely an "Order By" so I change it.
I think you need to better analyze your data records. I would expect a lot of changes to the script.
Good Luck.
SELECT TOP 1
h.Invoice_id AS Invoice_Id,
h.IssueDate AS [Invoice Date],
h.Optional_1 AS [Division Buyer],
h.ScanDate AS ScanDate],
h.Supplier AS [Supplier/Vendor],
h.Optional_2 AS [PO Number],
h.InvoiceNumber AS [Invoice Number],
h.Rejected,
h.Completed AS [Transaction Date],
h.Notified,
h.Comments,
h.Transferred,
h.Optional_3 AS [Invoice Type],
h.InvoiceTotal AS [Total Invoice Amount],
h.Optional_4 AS TFN,
h.Optional_5 AS Orginator,
h.Amount_1 AS [GST Total Amount],
h.Responsible,
h.IsCredit AS [Credit Note],
h.Currency,
h.PaymentDate AS [BCC Upload Date1],
h.Optional_7 AS [Supplier Code]
FROM
Invoice_head h INNER JOIN (
SELECT
Invoice_id,
Account AS [Account Code],
Optional_6 AS [Job Number],
Optional_8 AS [Cost Type Code],
Department AS [Department Code],
Optional_7 AS [EmployeeCode],
Optional_1 AS [Product Code],
Optional_9 AS [Campaign Code],
Optional_10 AS Optional,
LineText AS Description,
Approver AS [Completed By],
Optional_3 AS [Division Code],
ApproveDate AS [BCC Upload Date],
Optional_5 AS [Client Code],
Responsible AS [Previous Approvers],
Remarks,
Amount_2 AS [Total Excl GST],
Amount_3 AS GST,
LineTotal AS [Total Inc GST]
FROM
Invoice_lines
GROUP BY
Invoice_id,
Account,
Optional_6,
Optional_8,
Department,
Optional_7,
Optional_1,
Optional_9,
Optional_10,
LineText,
Approver,
Optional_3,
ApproveDate,
Optional_5,
Responsible,
Remarks,
Amount_2,
Amount_3,
LineTotal) l ON
h.invoice_id = l.invoice_id
CROSS JOIN Companies c
WHERE
c.company_id = 44 AND
l.Invoice_id ='135291' AND
l.ApproveDate >= (CASE
WHEN datepart(dw, getdate()) = 2
THEN (getdate() - 3)
ELSE (getdate() - 1)END))
ORDER BY
h.invoice_id
July 11, 2011 at 2:18 am
ringovski (7/10/2011)
And you got gets an error when I compile it;Msg 4145, Level 15, State 1, Line 22
An expression of non-boolean type specified in a context where a condition is expected, near 'WHERE'. Thanks.
Sorry about that, fixed the code in my post.
Far away is close at hand in the images of elsewhere.
Anon.
July 12, 2011 at 12:32 am
Many thanks for your help :-D, but it is still not what I need.
I have uploaded a spreadsheet that shows the difference. Invoice ID 135393 only has one purchase order with
your code
invoice id 135393 -----account 740506(purchase order)
-----------------------------account 217030(missing purchase order)
my code has two accounts
invoice 135393-----account 740506(purchase order)
-----------------------account 217030(purchase order)
Though my code is also wrong becuase the invoice head columns are repeated for each purchase order. This is the problem, a way to get each invoice ID and it's purchase orders; if it has any, without repeating the invoice head data.
Maybe another way to think of it is invoice head is the parent and invoice line is the child. Show every child record but only show the parent data(invoice head) for the first child record(invoice line).
e.g.
Head----Line
1--------1
null------2
null------3
2--------1
3--------1
4--------1
null------2
Cheers
July 12, 2011 at 2:50 am
ringovski (7/12/2011)
Many thanks for your help :-D, but it is still not what I need.I have uploaded a spreadsheet that shows the difference. Invoice ID 135393 only has one purchase order with ...
I reformatted your data in the spreadsheet to your DDL and ran my query against it it it produced the desired result.
As others have asked you need to provide more accurate information such as
working DDL to create the tables
working DML INSERT statements to create sample data
and expected results
otherwise it is difficult for us to validate the query to see where there are any problems
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 16 through 30 (of 31 total)
You must be logged in to reply to this topic. Login to reply