SQL Data Grouping

[Ringers]

Hi,

I am trying to get the invoice data from the invoice_head table(single row) with the invoice_lines(purchase orders) on certain conditions.

The where is fine but SQL doesn't like binding any of the invoice_lines columns.Which why I have tried putting in a nested select statement.

SELECT TOP 1 h.Invoice_id AS 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]
FROM
Invoice_head h
INNER JOIN
(SELECT
l.Invoice_id, 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
Invoice_lines
GROUP BY
Invoice_id) l
ON h.invoice_id = l.invoice_id
WHERE (companies.company_id = 44)AND (Invoice_lines..Invoice_id ='135291') AND (invoice_lines.ApproveDate >= (CASE WHEN datepart(dw, getdate()) = 2 THEN (getdate() - 3) ELSE (getdate() - 1)
END))
GROUP BY h.invoice_id

ERROR: Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "l.Invoice_id" could not be bound. etc...




I have attached a mediafire file showing exactly what I trying to get SQL to do.

 

[Ringers]

This might be a "Concatenating values when the number of items is not known ".

Never done that before.

 

[bborissov]

In your WHERE clause you should use the table ALIAS also, not the name of the tables.

WHERE (companies.company_id = 44) AND
      (l.Invoice_id ='135291')    AND
      (l.ApproveDate >= (CASE WHEN datepart(dw, getdate()) = 2 
                                   THEN (getdate() - 3)
                              ELSE (getdate() - 1) END))

BTW from where [companies] comes from?
You didn't JOIN this table.

Also in your derived tables you should either alias Invoice_lines as L either you should use the whole table name in the SELECT clause.

Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Ringers]

ok i have updated the where statement but it still doesn't work. Can you have a look at the image i attached. Is such a formating structure possible in SQL?

SELECT h.Invoice_id AS 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]
FROM
Invoice_head h, Companies
INNER JOIN
(SELECT
l.Invoice_id,
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
Invoice_lines
GROUP BY
Invoice_id) l
ON
h.invoice_id = l.invoice_id
INNER JOIN
companies ON h.company_id = companies.company_id AND h.Optional_1 = companies.companyname
WHERE (companies.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 invoice_head.invoice_id

 

[Ringers]

A rough example of what I am expecting;
Invoice_head, invoice_id col2 col 3 col4 etc... Invoice_line, col1 col2 col3 col4.....
1234, 01072011, acme pty, jones, PO1, 01112011, acme pty, smith
PO2, 01112011, harrys pty, williams
PO3, 01112011, zenith pty, kate
11221,01072011, starcom pty, ian,PO1, 01072011, SS pty, jones

So invoice 1234 has one row for invoice_head columns and 1 or more rows for invoice_lines\purchase orders columns. Though invoice 11221 doesn't have any po's.

 

[Ringers]

Anyone?

 

[Ringers]

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_id],
CASE WHEN RowId = 1 THEN CAST(IssueDate AS VARCHAR(20))
ELSE ''
END AS [IssueDate], Account,ApproveDate
FROM cte
This code seems to do it, but I need to add all the missing colunms.