November 1, 2010 at 7:48 am
Hi There,
I have the following query -
SELECT INV.Reference AS 'Invoice Reference'
,INV.InvoiceDate AS 'Invoice Date'
,DATEPART(YYYY,INV.InvoiceDate) AS [InvoiceYear]
,DATEPART(MM, INV.InvoiceDate) AS [InvoiceMonth]
,DATEPART(WW, INV.InvoiceDate) AS [InvoiceCalWeek]
,SERV.CreatedDate
FROM SalesInvoices AS INV
INNER JOIN
SalesOrders AS SALES
ON
INV.DocumentID = SALES.UID
INNER JOIN
ServiceOrders AS SERV
ONSales.UID = SERV.DocumentID
WHERE INV.InvoiceDate >= CONVERT(DATETIME, '2010-04-01 00:00:00', 102)
AND INV.InvoiceDate <= CONVERT(DATETIME, '2011-03-31 00:00:00', 102)
ANDINV.Reference = '4146245'
It pulls me back two results as follows -
Invoice Reference Invoice Date InvoiceYear InvoiceMonth InvoiceCalWeek CreatedDate
-------------------- ------------------------------------------------------ ----------- ------------ -------------- ------------------------------------------------------
4146245 2010-04-06 00:00:00.000 2010 4 15 2010-02-03 00:00:00.000
4146245 2010-04-06 00:00:00.000 2010 4 15 2010-01-20 00:00:00.000
(2 row(s) affected)
I know why it is pulling two lines back as there are two Service Orders linked to a Sales Order.
All I want to pull back is one result. It doesn't matter if it is the MAX Created Date or the MIN Created Date. I have gone totally blank as to how to achieve this.
Please help.
Thanks
November 1, 2010 at 8:38 am
Here's one way......given I don't know anything about your table structure, size, or indexing, this will work. If it does not perform well, post your table DDL and some sample rows and I'll try again. Please review the thread in my signature line if you do so.
SELECTINV.Reference AS 'Invoice Reference'
,INV.InvoiceDate AS 'Invoice Date'
,DATEPART(YYYY,INV.InvoiceDate) AS [InvoiceYear]
,DATEPART(MM, INV.InvoiceDate) AS [InvoiceMonth]
,DATEPART(WW, INV.InvoiceDate) AS [InvoiceCalWeek]
,SERV.CreatedDate
FROMSalesInvoices AS INV
INNER JOINSalesOrders AS SALES ON INV.DocumentID = SALES.UID
INNER JOINServiceOrders AS SERV ON Sales.UID = SERV.DocumentID
INNER JOIN(
SELECTINV.Reference,
MAX(SERV.CreatedDate) as LastDate
FROMSalesInvoices AS INV
INNER JOINSalesOrders AS SALES ON INV.DocumentID = SALES.UID
INNER JOINServiceOrders AS SERV ON Sales.UID = SERV.DocumentID
GROUP BYINV.Reference
) grp ON grp.Reference = INV.Reference AND grp.LastDate = SERV.CreatedDate
WHEREINV.InvoiceDate >= CONVERT(DATETIME, '2010-04-01 00:00:00', 102)
AND INV.InvoiceDate <= CONVERT(DATETIME, '2011-03-31 00:00:00', 102)
AND INV.Reference = '4146245'
November 1, 2010 at 9:54 am
How about using GROUP BY?
SELECT INV.Reference AS 'Invoice Reference'
,INV.InvoiceDate AS 'Invoice Date'
,DATEPART(YYYY,INV.InvoiceDate) AS [InvoiceYear]
,DATEPART(MM, INV.InvoiceDate) AS [InvoiceMonth]
,DATEPART(WW, INV.InvoiceDate) AS [InvoiceCalWeek]
,MIN(SERV.CreatedDate)
FROM YourQuery
GROUP BY [Invoice Reference], [Invoice Date], [InvoiceYear], [InvoiceMonth], [InvoiceCalWeek]
November 1, 2010 at 9:58 am
Duh.....I didn't notice that all of the other column values were the same.....GROUP BY would definately be the best option. Thanks Lutz!
November 5, 2010 at 4:46 am
SELECT INV.Reference AS 'Invoice Reference'
,INV.InvoiceDate AS 'Invoice Date'
,DATEPART(YYYY,INV.InvoiceDate) AS [InvoiceYear]
,DATEPART(MM, INV.InvoiceDate) AS [InvoiceMonth]
,DATEPART(WW, INV.InvoiceDate) AS [InvoiceCalWeek]
,MIN(SERV.CreatedDate)
FROM SalesInvoices AS INV
INNER JOIN
SalesOrders AS SALES
ON
INV.DocumentID = SALES.UID
INNER JOIN
ServiceOrders AS SERV
ON Sales.UID = SERV.DocumentID
WHERE INV.InvoiceDate >= CONVERT(DATETIME, '2010-04-01 00:00:00', 102)
AND INV.InvoiceDate <= CONVERT(DATETIME, '2011-03-31 00:00:00', 102)
AND INV.Reference = '4146245'
GROUP BY [Invoice Reference], [Invoice Date], [InvoiceYear], [InvoiceMonth], [InvoiceCalWeek]
Really sorry but the following script will still not work - getting the following -
Server: Msg 207, Level 16, State 3, Line 1
Invalid column name 'Invoice Reference'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'Invoice Date'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'InvoiceYear'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'InvoiceMonth'.
Server: Msg 207, Level 16, State 1, Line 1
Invalid column name 'InvoiceCalWeek'.
November 5, 2010 at 10:30 am
:blush:OUCH!!:blush:
I must have been totally out of my mind when replying... Of course, it's not possible to use column alias from a SELECT clause in GROUP BY, since the grouping is done before the alias is assigned.
Therefore, the GROUP BY should be
GROUP BY INV.Reference, INV.InvoiceDate, DATEPART(YYYY,INV.InvoiceDate), DATEPART(MM, INV.InvoiceDate), DATEPART(WW, INV.InvoiceDate)
Again, I'm sorry!!
November 8, 2010 at 4:39 am
SELECTinv.Reference AS 'Invoice Reference',
inv.InvoiceDate AS 'Invoice Date',
DATEPART(YEAR, inv.InvoiceDate) AS InvoiceYear,
DATEPART(MONTH, inv.InvoiceDate) AS InvoiceMonth,
DATEPART(WEEK, inv.InvoiceDate) AS InvoiceCalWeek,
MAX(serv.CreatedDate)
FROMdbo.SalesInvoices AS inv
INNER JOINdbo.SalesOrders AS sales ON sales.[UID] = inv.DocumentID
INNER JOINdbo.ServiceOrders AS serv ON serv.DocumentID = sales.[UID]
WHEREinv.InvoiceDate >= CONVERT(DATETIME, '20100401', 112)
AND inv.InvoiceDate < CONVERT(DATETIME, '20110401', 112)
AND inv.Reference = '4146245'
GROUP BYinv.Reference,
inv.InvoiceDate,
DATEPART(YEAR, inv.InvoiceDate),
DATEPART(MONTH, inv.InvoiceDate),
DATEPART(WEEK, inv.InvoiceDate)
N 56°04'39.16"
E 12°55'05.25"
November 9, 2010 at 6:18 am
Thanks for this.
Done the trick
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply