August 13, 2009 at 7:05 am
Hi there I have the following Code
SELECT dbo.DW_SALESINVOICES_F.Reference AS [Sih.Reference],
dbo.DW_SALESINVOICES_F.InvoiceDate AS [Sih.InvoiceDate],
DATEPART(YYYY, dbo.DW_SALESINVOICES_F.InvoiceDate) AS [Sih.InvoiceYear],
DATEPART(MM, dbo.DW_SALESINVOICES_F.InvoiceDate) AS [Sih.InvoiceMonth],
dbo.ETL_COSTCHARGE_DW.ServiceOrderCategory AS [Ser.ServiceOrderCategory],
dbo.DW_CUSTOMERS_D.ParentCustomerShortName AS [Cus.ParentCustomerShortName],
DW_CUSTOMERS_D_1.ShortName AS [Cus.ShortName],
dbo.DW_SALESINVOICES_F.NetTotal AS [Sih.NetTotal],
dbo.DW_SALESINVOICES_F.CustomerOrderRef AS [Sih.CustomerOrderRef],
dbo.DW_SALESINVOICES_F.SalesInvType AS [Sih.SalesInvType],
dbo.DW_SALESORDERS_F.Reference AS [SalesOrderReference],
(SELECT MAX (dbo.DW_SERVICEORDERSJOBS_F.ScheduledDate)
FROM dbo.DW_SERVICEORDERSJOBS_F
WHERE dbo.DW_SERVICEORDERS_D.ServiceOrderUID = dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID)AS [Completed Date]
FROM dbo.DW_CUSTOMERS_D
INNER JOIN
dbo.ETL_COSTCHARGE_DW
ON dbo.DW_CUSTOMERS_D.CustomerUID = dbo.ETL_COSTCHARGE_DW.CustomerID
INNER JOIN
dbo.DW_SALESINVOICES_F
ON dbo.ETL_COSTCHARGE_DW.DocumentID = dbo.DW_SALESINVOICES_F.DocumentID
AND dbo.ETL_COSTCHARGE_DW.DocumentTypeID = dbo.DW_SALESINVOICES_F.DocumentTypeID
INNER JOIN
dbo.DW_CUSTOMERS_D DW_CUSTOMERS_D_1
ON dbo.DW_SALESINVOICES_F.CustomerSiteID = DW_CUSTOMERS_D_1.CustomerUID
LEFT OUTER JOIN
dbo.DW_SALESORDERS_F
ON dbo.DW_SALESINVOICES_F.DocumentID = dbo.DW_SALESORDERS_F.SalesOrderUID
LEFT OUTER JOIN
dbo.DW_SERVICEORDERS_D
ON DW_SALESORDERS_F.SalesOrderUID = dbo.DW_SERVICEORDERS_D.DocumentID
LEFT OUTER JOIN
dbo.DW_SERVICEORDERSJOBS_F
ON dbo.DW_SERVICEORDERS_D.ServiceOrderUID = dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID
WHERE dbo.DW_SALESINVOICES_F.Reference = '4980586'
When I run this code I am getting three results for Sales Invoice Reference 4980586.
I expect this as I am linking to the dbo.DW_SERVICEORDERSJOBS_F table and that invoice has three Service Orders attached.
The three dbo.DW_SERVICEORDERSJOBS_F.ScheduledDates that are being pulled back are
2 at 17 JAN 2008
and
1 at 02 JUNE 2008
I am only interested in pulling back the one Service Order Job for each Invoice and in this case the 02 JUNE 2008 one as it is the most recent date. However I am still returning three?
What am I doing wrong?
Thanks
August 13, 2009 at 7:15 am
Try it like this:
SELECT dbo.DW_SALESINVOICES_F.Reference AS [Sih.Reference],
dbo.DW_SALESINVOICES_F.InvoiceDate AS [Sih.InvoiceDate],
DATEPART(YYYY, dbo.DW_SALESINVOICES_F.InvoiceDate) AS [Sih.InvoiceYear],
DATEPART(MM, dbo.DW_SALESINVOICES_F.InvoiceDate) AS [Sih.InvoiceMonth],
dbo.ETL_COSTCHARGE_DW.ServiceOrderCategory AS [Ser.ServiceOrderCategory],
dbo.DW_CUSTOMERS_D.ParentCustomerShortName AS [Cus.ParentCustomerShortName],
DW_CUSTOMERS_D_1.ShortName AS [Cus.ShortName],
dbo.DW_SALESINVOICES_F.NetTotal AS [Sih.NetTotal],
dbo.DW_SALESINVOICES_F.CustomerOrderRef AS [Sih.CustomerOrderRef],
dbo.DW_SALESINVOICES_F.SalesInvType AS [Sih.SalesInvType],
dbo.DW_SALESORDERS_F.Reference AS [SalesOrderReference],
dbo.DW_SERVICEORDERSJOBS_F.ScheduledDate AS [Completed Date]
FROM dbo.DW_CUSTOMERS_D
INNER JOIN dbo.ETL_COSTCHARGE_DW
ON dbo.DW_CUSTOMERS_D.CustomerUID = dbo.ETL_COSTCHARGE_DW.CustomerID
INNER JOIN dbo.DW_SALESINVOICES_F
ON dbo.ETL_COSTCHARGE_DW.DocumentID = dbo.DW_SALESINVOICES_F.DocumentID
AND dbo.ETL_COSTCHARGE_DW.DocumentTypeID = dbo.DW_SALESINVOICES_F.DocumentTypeID
INNER JOIN dbo.DW_CUSTOMERS_D DW_CUSTOMERS_D_1
ON dbo.DW_SALESINVOICES_F.CustomerSiteID = DW_CUSTOMERS_D_1.CustomerUID
LEFT OUTER JOIN dbo.DW_SALESORDERS_F
ON dbo.DW_SALESINVOICES_F.DocumentID = dbo.DW_SALESORDERS_F.SalesOrderUID
LEFT OUTER JOIN dbo.DW_SERVICEORDERS_D
ON DW_SALESORDERS_F.SalesOrderUID = dbo.DW_SERVICEORDERS_D.DocumentID
LEFT OUTER JOIN dbo.DW_SERVICEORDERSJOBS_F
ON dbo.DW_SERVICEORDERS_D.ServiceOrderUID = dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID
AND dbo.DW_SERVICEORDERSJOBS_F.ScheduledDates = (
SELECT MAX j2.ScheduledDates
FROM dbo.DW_SERVICEORDERSJOBS_F j2
WHERE j2.ServiceOrderID = dbo.DW_SERVICEORDERS_D.ServiceOrderUID)
WHERE dbo.DW_SALESINVOICES_F.Reference = '4980586'
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 13, 2009 at 7:31 am
Thanks for the reply - however I am getting the following error -
Server: Msg 170, Level 15, State 1, Line 28
Line 28: Incorrect syntax near '.'.
August 13, 2009 at 9:10 am
Ryan Keast (8/13/2009)
When I run this code I am getting three results for Sales Invoice Reference 4980586.I expect this as I am linking to the dbo.DW_SERVICEORDERSJOBS_F table and that invoice has three Service Orders attached.
I don't think that you need the join to the dbo.DW_SERVICEORDERSJOBS_F table. Try simply deleting that join statement.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 13, 2009 at 11:01 am
Ryan Keast (8/13/2009)
Thanks for the reply - however I am getting the following error -Server: Msg 170, Level 15, State 1, Line 28
Line 28: Incorrect syntax near '.'.
Yep, small typo. This should fix it:
SELECT dbo.DW_SALESINVOICES_F.Reference AS [Sih.Reference],
dbo.DW_SALESINVOICES_F.InvoiceDate AS [Sih.InvoiceDate],
DATEPART(YYYY, dbo.DW_SALESINVOICES_F.InvoiceDate) AS [Sih.InvoiceYear],
DATEPART(MM, dbo.DW_SALESINVOICES_F.InvoiceDate) AS [Sih.InvoiceMonth],
dbo.ETL_COSTCHARGE_DW.ServiceOrderCategory AS [Ser.ServiceOrderCategory],
dbo.DW_CUSTOMERS_D.ParentCustomerShortName AS [Cus.ParentCustomerShortName],
DW_CUSTOMERS_D_1.ShortName AS [Cus.ShortName],
dbo.DW_SALESINVOICES_F.NetTotal AS [Sih.NetTotal],
dbo.DW_SALESINVOICES_F.CustomerOrderRef AS [Sih.CustomerOrderRef],
dbo.DW_SALESINVOICES_F.SalesInvType AS [Sih.SalesInvType],
dbo.DW_SALESORDERS_F.Reference AS [SalesOrderReference],
(SELECT MAX (dbo.DW_SERVICEORDERSJOBS_F.ScheduledDate)
FROM dbo.DW_SERVICEORDERSJOBS_F
WHERE dbo.DW_SERVICEORDERS_D.ServiceOrderUID = dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID)AS [Completed Date]
FROM dbo.DW_CUSTOMERS_D
INNER JOIN dbo.ETL_COSTCHARGE_DW
ON dbo.DW_CUSTOMERS_D.CustomerUID = dbo.ETL_COSTCHARGE_DW.CustomerID
INNER JOIN dbo.DW_SALESINVOICES_F
ON dbo.ETL_COSTCHARGE_DW.DocumentID = dbo.DW_SALESINVOICES_F.DocumentID
AND dbo.ETL_COSTCHARGE_DW.DocumentTypeID = dbo.DW_SALESINVOICES_F.DocumentTypeID
INNER JOIN dbo.DW_CUSTOMERS_D DW_CUSTOMERS_D_1
ON dbo.DW_SALESINVOICES_F.CustomerSiteID = DW_CUSTOMERS_D_1.CustomerUID
LEFT OUTER JOIN dbo.DW_SALESORDERS_F
ON dbo.DW_SALESINVOICES_F.DocumentID = dbo.DW_SALESORDERS_F.SalesOrderUID
LEFT OUTER JOIN dbo.DW_SERVICEORDERS_D
ON DW_SALESORDERS_F.SalesOrderUID = dbo.DW_SERVICEORDERS_D.DocumentID
LEFT OUTER JOIN dbo.DW_SERVICEORDERSJOBS_F
ON dbo.DW_SERVICEORDERS_D.ServiceOrderUID = dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID
AND dbo.DW_SERVICEORDERSJOBS_F.ScheduledDates = (
SELECT MAX(j2.ScheduledDates)
FROM dbo.DW_SERVICEORDERSJOBS_F j2
WHERE j2.ServiceOrderID = dbo.DW_SERVICEORDERS_D.ServiceOrderUID)
WHERE dbo.DW_SALESINVOICES_F.Reference = '4980586'
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
August 18, 2009 at 4:26 am
SELECT dbo.DW_SALESINVOICES_F.Reference AS [Sih.Reference],
dbo.DW_SALESINVOICES_F.InvoiceDate AS [Sih.InvoiceDate],
DATEPART(YYYY, dbo.DW_SALESINVOICES_F.InvoiceDate) AS [Sih.InvoiceYear],
DATEPART(MM, dbo.DW_SALESINVOICES_F.InvoiceDate) AS [Sih.InvoiceMonth],
dbo.ETL_COSTCHARGE_DW.ServiceOrderCategory AS [Ser.ServiceOrderCategory],
dbo.DW_CUSTOMERS_D.ParentCustomerShortName AS [Cus.ParentCustomerShortName],
DW_CUSTOMERS_D_1.ShortName AS [Cus.ShortName],
dbo.DW_SALESINVOICES_F.NetTotal AS [Sih.NetTotal],
dbo.DW_SALESINVOICES_F.CustomerOrderRef AS [Sih.CustomerOrderRef],
dbo.DW_SALESINVOICES_F.SalesInvType AS [Sih.SalesInvType],
dbo.DW_SALESORDERS_F.Reference AS [SalesOrderReference],
(SELECT MAX (dbo.DW_SERVICEORDERSJOBS_F.ScheduledDate)
FROM dbo.DW_SERVICEORDERSJOBS_F
WHERE dbo.DW_SERVICEORDERS_D.ServiceOrderUID = dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID)AS [Completed Date]
FROM dbo.DW_CUSTOMERS_D
INNER JOIN dbo.ETL_COSTCHARGE_DW
ON dbo.DW_CUSTOMERS_D.CustomerUID = dbo.ETL_COSTCHARGE_DW.CustomerID
INNER JOIN dbo.DW_SALESINVOICES_F
ON dbo.ETL_COSTCHARGE_DW.DocumentID = dbo.DW_SALESINVOICES_F.DocumentID
AND dbo.ETL_COSTCHARGE_DW.DocumentTypeID = dbo.DW_SALESINVOICES_F.DocumentTypeID
INNER JOIN dbo.DW_CUSTOMERS_D DW_CUSTOMERS_D_1
ON dbo.DW_SALESINVOICES_F.CustomerSiteID = DW_CUSTOMERS_D_1.CustomerUID
LEFT OUTER JOIN dbo.DW_SALESORDERS_F
ON dbo.DW_SALESINVOICES_F.DocumentID = dbo.DW_SALESORDERS_F.SalesOrderUID
LEFT OUTER JOIN dbo.DW_SERVICEORDERS_D
ON DW_SALESORDERS_F.SalesOrderUID = dbo.DW_SERVICEORDERS_D.DocumentID
LEFT OUTER JOIN dbo.DW_SERVICEORDERSJOBS_F
ON dbo.DW_SERVICEORDERS_D.ServiceOrderUID = dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID
AND dbo.DW_SERVICEORDERSJOBS_F.ScheduledDate = (
SELECT MAX(j2.ScheduledDate)
FROM dbo.DW_SERVICEORDERSJOBS_F j2
WHERE j2.ServiceOrderID = dbo.DW_SERVICEORDERS_D.ServiceOrderUID)
WHERE dbo.DW_SALESINVOICES_F.Reference = '4980586'
Hi there,
Just had a chance to test out your code and I am still pulling back the three results as originally posted.
Any more help would be appreciated.
Thanks
August 18, 2009 at 4:43 am
This may not be exactly right as i dont know your data but try
with cteInvoices(Reference,InvoiceDate,InvoiceYear,InvoiceMonth,ServiceOrderCategory,
ParentCustomerShortName,ShortName,NetTotal,
CustomerOrderRef,SalesInvType,SalesOrderReference,
ScheduledDate)
as(
SELECT dbo.DW_SALESINVOICES_F.Reference AS [Sih.Reference],
dbo.DW_SALESINVOICES_F.InvoiceDate AS [Sih.InvoiceDate],
DATEPART(YYYY, dbo.DW_SALESINVOICES_F.InvoiceDate) AS [Sih.InvoiceYear],
DATEPART(MM, dbo.DW_SALESINVOICES_F.InvoiceDate) AS [Sih.InvoiceMonth],
dbo.ETL_COSTCHARGE_DW.ServiceOrderCategory AS [Ser.ServiceOrderCategory],
dbo.DW_CUSTOMERS_D.ParentCustomerShortName AS [Cus.ParentCustomerShortName],
DW_CUSTOMERS_D_1.ShortName AS [Cus.ShortName],
dbo.DW_SALESINVOICES_F.NetTotal AS [Sih.NetTotal],
dbo.DW_SALESINVOICES_F.CustomerOrderRef AS [Sih.CustomerOrderRef],
dbo.DW_SALESINVOICES_F.SalesInvType AS [Sih.SalesInvType],
dbo.DW_SALESORDERS_F.Reference AS [SalesOrderReference],
dbo.DW_SERVICEORDERSJOBS_F.ScheduledDate as [ScheduledDate],
RowNum = row_number() over(Partition by dbo.DW_SALESINVOICES_F.Reference order by dbo.DW_SERVICEORDERSJOBS_F.ScheduledDate desc)
FROM dbo.DW_CUSTOMERS_D
INNER JOIN
dbo.ETL_COSTCHARGE_DW
ON dbo.DW_CUSTOMERS_D.CustomerUID = dbo.ETL_COSTCHARGE_DW.CustomerID
INNER JOIN
dbo.DW_SALESINVOICES_F
ON dbo.ETL_COSTCHARGE_DW.DocumentID = dbo.DW_SALESINVOICES_F.DocumentID
AND dbo.ETL_COSTCHARGE_DW.DocumentTypeID = dbo.DW_SALESINVOICES_F.DocumentTypeID
INNER JOIN
dbo.DW_CUSTOMERS_D DW_CUSTOMERS_D_1
ON dbo.DW_SALESINVOICES_F.CustomerSiteID = DW_CUSTOMERS_D_1.CustomerUID
LEFT OUTER JOIN
dbo.DW_SALESORDERS_F
ON dbo.DW_SALESINVOICES_F.DocumentID = dbo.DW_SALESORDERS_F.SalesOrderUID
LEFT OUTER JOIN
dbo.DW_SERVICEORDERS_D
ON DW_SALESORDERS_F.SalesOrderUID = dbo.DW_SERVICEORDERS_D.DocumentID
LEFT OUTER JOIN
dbo.DW_SERVICEORDERSJOBS_F
ON dbo.DW_SERVICEORDERS_D.ServiceOrderUID = dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID
WHERE dbo.DW_SALESINVOICES_F.Reference = '4980586'
)
select * from cteInvoices where RowNum = 1
If its not right modify the partition by clause
August 18, 2009 at 5:23 am
If Afraid i'm getting the following error when I run that code -
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'order'.
Being a bit crap at TSQL I'm not understanding the logic of the code you have suplied - sorry.
I thought you were able to do this via the MAX syntax - but of course I could be wrong.
Thanks again for any one that can help
August 18, 2009 at 5:30 am
the main reason why you're getting problems trying to execute the code that people have been pasting here to assist you, is because they can't test it themselves before giving you the code.
you should always include table structures and sample data when you post questions, so that we can try things out before letting you know a solution.
as for your question - the reason why you're getting multiple rows is because the MAX query is a subquery of your rows. That means your outer result set contains three rows, and for each row you are querying for a MAX value.
if you wanted only one row, you would have to change your outer query to return only one row.
August 18, 2009 at 8:46 am
Ryan Keast (8/18/2009)
Just had a chance to test out your code and I am still pulling back the three results as originally posted.Any more help would be appreciated.
As I said earlier, you have an extra join in there that is causing the duplicates. Removing that join should fix the problem.
SELECT dbo.DW_SALESINVOICES_F.Reference AS [Sih.Reference],
dbo.DW_SALESINVOICES_F.InvoiceDate AS [Sih.InvoiceDate],
DATEPART(YYYY, dbo.DW_SALESINVOICES_F.InvoiceDate) AS [Sih.InvoiceYear],
DATEPART(MM, dbo.DW_SALESINVOICES_F.InvoiceDate) AS [Sih.InvoiceMonth],
dbo.ETL_COSTCHARGE_DW.ServiceOrderCategory AS [Ser.ServiceOrderCategory],
dbo.DW_CUSTOMERS_D.ParentCustomerShortName AS [Cus.ParentCustomerShortName],
DW_CUSTOMERS_D_1.ShortName AS [Cus.ShortName],
dbo.DW_SALESINVOICES_F.NetTotal AS [Sih.NetTotal],
dbo.DW_SALESINVOICES_F.CustomerOrderRef AS [Sih.CustomerOrderRef],
dbo.DW_SALESINVOICES_F.SalesInvType AS [Sih.SalesInvType],
dbo.DW_SALESORDERS_F.Reference AS [SalesOrderReference],
(SELECT MAX (dbo.DW_SERVICEORDERSJOBS_F.ScheduledDate)
FROM dbo.DW_SERVICEORDERSJOBS_F
WHERE dbo.DW_SERVICEORDERS_D.ServiceOrderUID = dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID)AS [Completed Date]
FROM dbo.DW_CUSTOMERS_D
INNER JOIN dbo.ETL_COSTCHARGE_DW
ON dbo.DW_CUSTOMERS_D.CustomerUID = dbo.ETL_COSTCHARGE_DW.CustomerID
INNER JOIN dbo.DW_SALESINVOICES_F
ON dbo.ETL_COSTCHARGE_DW.DocumentID = dbo.DW_SALESINVOICES_F.DocumentID
AND dbo.ETL_COSTCHARGE_DW.DocumentTypeID = dbo.DW_SALESINVOICES_F.DocumentTypeID
INNER JOIN dbo.DW_CUSTOMERS_D DW_CUSTOMERS_D_1
ON dbo.DW_SALESINVOICES_F.CustomerSiteID = DW_CUSTOMERS_D_1.CustomerUID
LEFT OUTER JOIN dbo.DW_SALESORDERS_F
ON dbo.DW_SALESINVOICES_F.DocumentID = dbo.DW_SALESORDERS_F.SalesOrderUID
LEFT OUTER JOIN dbo.DW_SERVICEORDERS_D
ON DW_SALESORDERS_F.SalesOrderUID = dbo.DW_SERVICEORDERS_D.DocumentID
/*
This entire section of code below is completely unnecessary.
Removing that section should fix the problem.
*/
--LEFT OUTER JOIN dbo.DW_SERVICEORDERSJOBS_F
--ON dbo.DW_SERVICEORDERS_D.ServiceOrderUID = dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID
--AND dbo.DW_SERVICEORDERSJOBS_F.ScheduledDate = (
--SELECT MAX(j2.ScheduledDate)
--FROM dbo.DW_SERVICEORDERSJOBS_F j2
--WHERE j2.ServiceOrderID = dbo.DW_SERVICEORDERS_D.ServiceOrderUID)
WHERE dbo.DW_SALESINVOICES_F.Reference = '4980586'
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 19, 2009 at 6:18 am
Since you only want to pull the max date from the dbo.DW_SERVICEORDERSJOBS_F table but still want to use a left join try something like the below.
Left Join (select [someIDfield], max([someDateField]) as recentdate from dbo.DW_SERVICEORDERSJOBS_F) tableMax
ON mainQuery.ID = tablemax.[someIdField]
this should only give you teh most recent date for each serviceOrder.
August 19, 2009 at 2:55 pm
I hate to rain on the MAX of the DATE party, but just using MAX alone isn't going to get the results needed, as what happens when there's more than one service order on the date that MAX returns? I'd still use MAX in a single query that determines that date value, but assigns it to a variable, which can then be part of the WHERE clause, and then use TOP 1 as part of the SELECT, with ORDER BY to ensure that the correct service order (the most recent) is chosen.
Steve
(aka smunson)
:-):-):-)
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
August 19, 2009 at 3:30 pm
smunson (8/19/2009)
I hate to rain on the MAX of the DATE party, but just using MAX alone isn't going to get the results needed, as what happens when there's more than one service order on the date that MAX returns? I'd still use MAX in a single query that determines that date value, but assigns it to a variable, which can then be part of the WHERE clause, and then use TOP 1 as part of the SELECT, with ORDER BY to ensure that the correct service order (the most recent) is chosen.Steve
(aka smunson)
:-):-):-)
Umm, it's irrelevant, because you're looking for the max date for each service order, not just the most recent service order.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
August 19, 2009 at 8:22 pm
I must be confused... I was going on the basis of the OP's original statement:
I am only interested in pulling back the one Service Order Job for each Invoice and in this case the 02 JUNE 2008 one as it is the most recent date. However I am still returning three?
Since I can't exactly test the code, I may have made the wrong assumption. I was thinking that it appeared to be a situation where the join to the table for service order jobs was not excluding the jobs associated with any date other than the very last one, but I was also concerned that there might be the possibility of date data without time values being able to make a difference, and then having the potential for more than one service order job present for that MAX date.
Can you explain where I went astray? I always like to learn from my mistakes. Thanks!
Steve
(aka smunson)
:-):-):-)
drew.allen (8/19/2009)
smunson (8/19/2009)
I hate to rain on the MAX of the DATE party, but just using MAX alone isn't going to get the results needed, as what happens when there's more than one service order on the date that MAX returns? I'd still use MAX in a single query that determines that date value, but assigns it to a variable, which can then be part of the WHERE clause, and then use TOP 1 as part of the SELECT, with ORDER BY to ensure that the correct service order (the most recent) is chosen.Steve
(aka smunson)
:-):-):-)
Umm, it's irrelevant, because you're looking for the max date for each service order, not just the most recent service order.
Drew
Steve?(aka sgmunson)?:) 🙂 :)?
Health & Nutrition
Rent Servers for Income (picks and shovels strategy)
August 20, 2009 at 3:43 am
Morning Guys,
Ok I am now down to two results -
Results as follows (I'm just including the Bolded options in my Select statement below)
Result 1
sih.Reference = 4980586
EventDateTime = 2008-01-15 15:12:25
Result 2
sih.Reference = 4980586
EventDateTime = 2008-05-31 13:23:53
SELECT DISTINCT dbo.DW_SALESINVOICES_F.Reference AS [Sih.Reference],
dbo.DW_SALESINVOICES_F.InvoiceDate AS [Sih.InvoiceDate],
DATEPART(YYYY, dbo.DW_SALESINVOICES_F.InvoiceDate) AS [Sih.InvoiceYear],
DATEPART(MM, dbo.DW_SALESINVOICES_F.InvoiceDate) AS [Sih.InvoiceMonth],
dbo.ETL_COSTCHARGE_DW.ServiceOrderCategory AS [Ser.ServiceOrderCategory],
dbo.DW_CUSTOMERS_D.ParentCustomerShortName AS [Cus.ParentCustomerShortName],
DW_CUSTOMERS_D_1.ShortName AS [Cus.ShortName],
dbo.DW_SALESINVOICES_F.NetTotal AS [Sih.NetTotal],
dbo.DW_SALESINVOICES_F.CustomerOrderRef AS [Sih.CustomerOrderRef],
dbo.DW_SALESINVOICES_F.SalesInvType AS [Sih.SalesInvType],
dbo.DW_SALESORDERS_F.Reference AS [SalesOrderReference],
dbo.DW_SERVICEORDERSJOBS_F.ScheduledDate AS [Completed Date],
(SELECT MAX(dbo.DW_SERVICEORDERSJOBS_F.ScheduledDate+dbo.DW_SERVICEORDERSJOBS_F.ScheduledTime)
FROM dbo.DW_SERVICEORDERSJOBS_F
WHERE dbo.DW_SERVICEORDERS_D.ServiceOrderUID = dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID) AS EventDatetime
FROM dbo.DW_CUSTOMERS_D
INNER JOIN
dbo.ETL_COSTCHARGE_DW
ON dbo.DW_CUSTOMERS_D.CustomerUID = dbo.ETL_COSTCHARGE_DW.CustomerID
INNER JOIN
dbo.DW_SALESINVOICES_F
ON dbo.ETL_COSTCHARGE_DW.DocumentID = dbo.DW_SALESINVOICES_F.DocumentID
AND dbo.ETL_COSTCHARGE_DW.DocumentTypeID = dbo.DW_SALESINVOICES_F.DocumentTypeID
INNER JOIN
dbo.DW_CUSTOMERS_D DW_CUSTOMERS_D_1
ON dbo.DW_SALESINVOICES_F.CustomerSiteID = DW_CUSTOMERS_D_1.CustomerUID
LEFT OUTER JOIN
dbo.DW_SALESORDERS_F
ON dbo.DW_SALESINVOICES_F.DocumentID = dbo.DW_SALESORDERS_F.SalesOrderUID
LEFT OUTER JOIN
dbo.DW_SERVICEORDERS_D
ON DW_SALESORDERS_F.SalesOrderUID = dbo.DW_SERVICEORDERS_D.DocumentID
LEFT OUTER JOIN
dbo.DW_SERVICEORDERSJOBS_F
ON dbo.DW_SERVICEORDERS_D.ServiceOrderUID = dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID
WHERE dbo.DW_SALESINVOICES_F.Reference = '4980586'
The DISTINCT has limited the results from the original three results to the two as I had two Service Order Jobs with same sih.Reference and EventDateTime.
So now all I need to achieve is just pull back Result 2, sih.Reference = 4980586, EventDateTime = 2008-05-31 13:23:53 as it is Service Order Job that has the most recent date.
A Sales Order, will have one Service Order attached but can have several Service Order Jobs attached to it.
The joins that deal with this are as below -
dbo.DW_SALESORDERS_F
ON dbo.DW_SALESINVOICES_F.DocumentID = dbo.DW_SALESORDERS_F.SalesOrderUID
LEFT OUTER JOIN
dbo.DW_SERVICEORDERS_D
ON DW_SALESORDERS_F.SalesOrderUID = dbo.DW_SERVICEORDERS_D.DocumentID
LEFT OUTER JOIN
dbo.DW_SERVICEORDERSJOBS_F
ON dbo.DW_SERVICEORDERS_D.ServiceOrderUID = dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply