March 31, 2004 at 12:35 pm
Hi
I am currently working on writing a SQL Server Database from an Access one. There is a query in the Access database that I wish to use as a view. However, the syntax is incorrect for SQL Server. Can anyone help me convert this Acess query to SQL Server Syntax please?
SELECT [Stock Items].[Stock Ref], [Stock Items].Description, Locations.Location,
DSum("Quantity - [Quantity Returned]","Movements","[Location Moved To] = " & CStr([Location ID]) & " AND [Stock Ref] = '" & [Stock Ref] & "'") AS [Total In],
DSum("Quantity- [Quantity Returned]","Movements","[Location Taken From] = " & CStr([Location ID]) & " AND [Stock Ref] = '" & [Stock Ref] & "'") AS [Total Out],
DSum("[Adjustment + or -]","Stock Adjustments","[Location ID] = " & CStr([Location ID]) & " AND [Stock Ref] = '" & [Stock Ref] & "'") AS [Total Adjustment],
IIf(IsNull([Total In]),0,[Total In])-IIf(IsNull([Total Out]),0,[Total Out])+IIf(IsNull([Total Adjustment]),0,[Total Adjustment]) AS [Stock At Location], Locations.[Location ID] FROM [Stock Items], Locations WHERE (((Locations.[Location ID]) Not In (7,11,12,13))) ORDER BY [Stock Items].[Stock Ref], Locations.Location
Thank you in advance
March 31, 2004 at 1:30 pm
This should get you started but there were a few things I was unsure of and had no idea the relationship between Location and Stock. Also no way to test results to see if is exatc on and thayt I don't have a syntax issue.
SELECT
SI.[Stock Ref],
SI.[Description],
L.Location,
SUM(IsNull(SI.Quantity - (CASE WHEN L.[Location ID] = M.[Location Moved To] AND M.[Stock Ref] = SI.[Stock Ref] THEN M.[Quantity Returned] ELSE 0 END),0)) AS [Total In],
SUM(IsNull(SI.Quantity - (CASE WHEN L.[Location ID] = M.[Location Taken From] AND M.[Stock Ref] = SI.[Stock Ref] THEN M.[Quantity Returned] ELSE 0 END),0)) AS [Total Out],
SUM(IsNull(SA.[Adjustment + or -],0)) AS [Total Adjustment],
SUM(IsNull(SI.Quantity - (CASE WHEN L.[Location ID] = M.[Location Moved To] AND M.[Stock Ref] = SI.[Stock Ref] THEN M.[Quantity Returned] ELSE 0 END),0)) -
SUM(IsNull(SI.Quantity - (CASE WHEN L.[Location ID] = M.[Location Taken From] AND M.[Stock Ref] = SI.[Stock Ref] THEN M.[Quantity Returned] ELSE 0 END),0)) +
IsNull(SA.[Adjustment + or -],0) AS [Stock At Location],
L.[Location ID]
FROM
[Stock Items] SI
(JOIN TYPE HERE FOR Stock Items and Locations)
Locations L
(ON CONDITION HERE FOR Stock Items And Locations)
LEFT JOIN
Movements M
ON
L.[Location ID] IN (M.[Location Moved To], M.[Location Taken From]) AND
M.[Stock Ref] = SI.[Stock Ref]
LEFT JOIN
[Stock Adjustments] SA
ON
SA.[Location ID] = L.[Location ID] AND
SA.[Stock Ref] = SI.[Stock Ref]
WHERE
L.[Location ID] Not In (7,11,12,13)
GROUP BY
SI.[Stock Ref],
SI.[Description],
L.Location,
L.[Location ID]
ORDER BY
SI.[Stock Ref],
L.Location
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply