August 20, 2009 at 9:31 am
Ryan,
You may have just cleared up my confusion. When you join to the Service Order Jobs table, you can CROSS APPLY to a correlated subquery instead of joining to the table directly, as follows:
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],
Y.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
CROSS APPLY (
SELECT TOP 1 * FROM dbo.DW_SERVICEORDERSJOBS_F AS X
WHERE X.ServiceOrderID = dbo.DW_SERVICE_ORDERS_D.ServiceOrderUID
ORDER BY X.ScheduledDate + X.ScheduledTime DESC
) AS Y
WHERE dbo.DW_SALESINVOICES_F.Reference = '4980586'
Try that and let me know if it works, as I can only validate the T-SQL using SSMS and that means I can't see run-time errors.
PS: EDITED to correct a couple of code goofs.
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
August 20, 2009 at 9:37 am
Please see my edited post. I realized I needed a few additional changes to get it right.
Steve
(aka smunson)
:-):-):-)
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 4, 2009 at 6:50 am
Sorry for the late reply on this.
When I run that last query I get the following errors -
Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'APPLY'.
Server: Msg 156, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'ORDER'.
September 4, 2009 at 7:57 am
Ryan Keast (9/4/2009)
Sorry for the late reply on this.When I run that last query I get the following errors -
Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'APPLY'.
Server: Msg 156, Level 15, State 1, Line 36
Incorrect syntax near the keyword 'ORDER'.
Could you run these two commands (Replacing YourDatabaseName with the name of the database this query is running in) and post the results?
SELECT @@VERSION
SELECT compatibility_level FROM sys.databases WHERE [NAME] = 'YourDatabaseName'
September 7, 2009 at 2:49 am
Got the following result back -
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
However also got the following message -
(1 row(s) affected)
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.databases'.
September 7, 2009 at 6:00 am
Ok ,you are on 2000 not 2005 , Cross apply wont work,
have you tried modifying your SQL as drew suggested ? ie remove the join to DW_SERVICEORDERSJOBS_F. Looks like it should work as you need.
Again, please post some DDL if you want need further help.
September 7, 2009 at 7:26 am
Yup, that's the problem all right. You posted your question in the SQL 2005 portion of the forum. I was coding on that basis, so the CROSS APPLY isn't going to work because it's a feature exclusive to SQL 2005 and above. You also get an error message on sys.databases because SQL 2000 uses a somewhat different metadata structure than SQL 2005.
I wish I could spend the time and translate my code's structure to no longer make use of the SQL 2005 features, but I've just returned to full-time work, and I'm away from my home state on a contract, so my available time is down to near zero. I have a commitment for today, or I'd gladly work on the translation. Someone here is bound to be able to help.
Steve
(aka smunson)
:-):-):-)
Ryan Keast (9/7/2009)
Got the following result back -Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05 Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)
However also got the following message -
(1 row(s) affected)
Server: Msg 208, Level 16, State 1, Line 1
Invalid object name 'sys.databases'.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
September 7, 2009 at 9:38 am
Oh....my mistake. So sorry.
Is it posible for an admin to move my post to the correct area.
Just to be clear this is SQL 2000 not 2005 that I am wrting the query against.
Thanks for all your help.
September 8, 2009 at 10:26 am
Since it is sql 2000. try the below. It should work but I didn't test it at all.
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],
-----added based on join below
MEDT.eventDateTime
----- end
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
---below was added
LEFT OUTER JOIN
(SELECT ServiceOrderUID, eventDateTime = MAX(ScheduledDate + ScheduledTime)
FROM dbo.DW_SERVICEORDERSJOBS_F
GROUP by ServiceOrderUID) AS MEDT
ON dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID = medt.ServiceOrderUID
----- end
WHERE dbo.DW_SALESINVOICES_F.Reference = '4980586'
September 9, 2009 at 7:24 am
Still the two results?? I just can't see why none of the code will pull back the one result?
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],
-----added based on join below
MEDT.eventDateTime
----- end
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
---below was added
LEFT OUTER JOIN
(SELECT dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID, eventDateTime = MAX(dbo.DW_SERVICEORDERSJOBS_F.ScheduledDate + dbo.DW_SERVICEORDERSJOBS_F.ScheduledTime)
FROM dbo.DW_SERVICEORDERSJOBS_F
GROUP by dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID) AS MEDT
ON dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID = medt.ServiceOrderID
----- end
WHERE dbo.DW_SALESINVOICES_F.Reference = '4980586'
September 9, 2009 at 7:38 am
ok. Make sure the below query only returns 1 record per ID. If it doesn't rewrite just this portion until it only return the 1 record you need. Once rewritten just paste it back into the larger query.
If this portion only returns 1 record already then the issue is with a different join. You could remark out each join 1 at a time until you find the join that is giving you multiple results.
SELECT dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID, eventDateTime = MAX(dbo.DW_SERVICEORDERSJOBS_F.ScheduledDate + dbo.DW_SERVICEORDERSJOBS_F.ScheduledTime)
FROM dbo.DW_SERVICEORDERSJOBS_F
GROUP by dbo.DW_SERVICEORDERSJOBS_F.ServiceOrderID
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply