Converting Access Query to TSQL
-
I'm pretty new at this but one of our developers wrote a program using links in Access and now they want to go live on our SQL Server. They need me to convert the Access query to TSQL and I'm not sure where to even begin. I've tried a bunch of things but don't know if I'm even getting close. Any suggestions would be appreciated. Thanks.
Here is the query as it sits in Access
SELECT IIf(UCase([AR4_OpenInvoice]![Salespersoncode])="SWI" Or UCase([AR4_OpenInvoice]![Salespersoncode])="SCR" Or UCase([AR4_OpenInvoice]![Salespersoncode])="CENT","Central Region",IIf(UCase([AR4_OpenInvoice]![Salespersoncode])="SEL" Or UCase([AR4_OpenInvoice]![Salespersoncode])="NEA" Or UCase([AR4_OpenInvoice]![Salespersoncode])="NEAT","East Region",IIf(UCase([AR4_OpenInvoice]![Salespersoncode])="WES" Or UCase([AR4_OpenInvoice]![Salespersoncode])="WEST","West Region",IIf(UCase([AR4_OpenInvoice]![Salespersoncode])="NATL","Federal Accounts",[dbo_company]![CoTeam])))) AS Team, [AR1_CustomerMaster].[CustomerName], [AR1_CustomerMaster].[MasterFileComment] AS [Brock ID], [AR4_OpenInvoice].[Comment] AS [Referecne Number], [AR4_OpenInvoice].[InvoiceNumber], [AR4_OpenInvoice].[InvoiceDate], [AR4_OpenInvoice].[InvoiceDueDate], [AR4_OpenInvoice].[Balance], DateDiff("d",[InvoiceDueDate],Now()) AS [Days Past Due]
FROM (AR4_OpenInvoice INNER JOIN AR1_CustomerMaster ON [AR4_OpenInvoice].[CustomerNumber]=[AR1_CustomerMaster].[CustomerNumber]) INNER JOIN dbo_company ON [AR1_CustomerMaster].[MasterFileComment]=[dbo_company].[coid]
WHERE ((([AR4_OpenInvoice].[InvoiceDueDate])<Now()) And (([AR4_OpenInvoice].[Balance])<>0) And ((DateDiff("d",[InvoiceDueDate],Now()))>=90));
-
Those Iif statements are a pain, and the best way I can think of doing this is to create a table called PersonCodeArea with two columns called SalesPersonCode and Region.
Fill the table with data in the IIf statements so you get
SalesPersonCode Region.
SWI Central Region
CENT Central Region
SCR Central Region
SEL East Region
...
In the join statement add "INNER JOIN PersonCodeArea pca on pca.SalesPersonCode = AR4_OpenInvoice.SalesPersonCode"
Then remove all the "IIF(Ucase..." statements with the line pca.Region
This should give you....
select pca.Region,
select case oi.Salespersoncode
when 'NATL' then 'Federal Accounts'
else dbo_company.CoTeam
end as Team,
.....(all them other statements)
from AR4_OpenInvoice oi /* oi is a table alias used to create more readable SQL */
INNER JOIN PersonCodeArea pca on pca.SalesPersonCode = oi.SalesPersonCode
... followed by the rest of the SQL
Use SQL Server books online to look up the use of DateDiff in T-SQL as well.
Have fun...
-
I deal with this problem on a daily basis and the best way I have found to handle it is to convert the IIF statements into CASE.
IIF(Condition,True Part, ELSE PART)
Case
When Condition THEN TRUE PART
ELSE
ELSE PART
END
Have Fun
-
Would this help
SELECT COALESCE(t.Region,d.CoTeam) AS Team, c.CustomerName,
c.MasterFileComment AS [Brock ID], i.Comment AS [Reference Number],
i.InvoiceNumber, i.InvoiceDate, i.InvoiceDueDate, i.Balance,
DATEDIFF(d,InvoiceDueDate,GETDATE()) AS [Days Past Due]
FROM AR4_OpenInvoice i INNER JOIN AR1_CustomerMaster ON i.CustomerNumber
= c.CustomerNumber
LEFT OUTER JOIN
(SELECT 'SWI' Code, 'Central Region' Region
UNION ALL SELECT 'SCR', 'Central Region'
UNION ALL SELECT 'CENT', 'Central Region'
UNION ALL SELECT 'SEL', 'East Region'
UNION ALL SELECT 'NEA', 'East Region'
UNION ALL SELECT 'NEAT', 'East Region'
UNION ALL SELECT 'WES', 'West Region'
UNION ALL SELECT 'WEST', 'West Region'
UNION ALL SELECT 'NATL', 'Federal Accounts') t ON i.SalespersonCode =
t.Code
INNER JOIN Dbo_Company d ON c.MasterFileComment = d.CoId
WHERE i.InvoiceDueDate < GETDATE()
AND i.Balance <> 0 AND DATEDIFF(d,InvoiceDueDate,GETDATE()) >= 90;
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
I agree with Keith on this. I think I would make a new table or add a column so that the abreviation of the region could be mapped to the actual region. However to just change this to a SQL compliant query here is what I came up with.
SELECT
Team = CASE UPPER(AR4.Salespersoncode)
WHEN "SWI" THEN "Central Region"
WHEN "SCR" THEN "Central Region"
WHEN "CENT" THEN "Central Region"
WHEN "SEL" THEN "East Region"
WHEN "NEA" THEN "East Region"
WHEN "NEAT" THEN "East Region"
WHEN "WES" THEN "West Region"
WHEN "WEST" THEN "West Region"
WHEN "NATL"THEN "Federal Accounts"
ELSE C.CoTeam
END
, AR1.CustomerName
, AR1.MasterFileComment AS [Brock ID]
, AR4.Comment AS [Referecne Number]
, AR4.InvoiceNumber
, AR4.InvoiceDate
, AR4.InvoiceDueDate
, AR4.Balance
, DateDiff("d",InvoiceDueDate,GetDate()) AS [Days Past Due]
FROM AR4_OpenInvoice AR4
INNER JOIN AR1_CustomerMaster ON AR4.CustomerNumber = AR1.CustomerNumber
INNER JOIN dbo_company ON AR1.MasterFileCommen] = C.coid
WHERE AR4.InvoiceDueDate < GetDate()
And AR4.Balance <> 0
And DateDiff("d",InvoiceDueDate,GetDate()) >= 90
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB EngineerThis posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
-
Thanks everyone. Based on your input I was able to change the query so it was SQL compliant. A couple minor changes on the queries Frank and Gary wrote and I was in business. This saved me a lot of time and frustration.
Thanks again.
-
Just out of curiosity, can you post the end result?
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url] -
Here you go. Both of these work. The minor changes were really just trying to figure out the company table because it is set up as a linked server. (That's new to us around here as well.)
Once again, thanks for the help.
Version 1
SELECT
Team = CASE UPPER(AR4.Salespersoncode)
WHEN "SWI" THEN "Central Region"
WHEN "SCR" THEN "Central Region"
WHEN "CENT" THEN "Central Region"
WHEN "SEL" THEN "East Region"
WHEN "NEA" THEN "East Region"
WHEN "NEAT" THEN "East Region"
WHEN "WES" THEN "West Region"
WHEN "WEST" THEN "West Region"
WHEN "NATL"THEN "Federal Accounts"
ELSE C.CoTeam
END
, AR1.CustomerName
, AR1.MasterFileComment AS [Brock ID]
, AR4.Comment AS [Referecne Number]
, AR4.InvoiceNumber
, AR4.InvoiceDate
, AR4.InvoiceDueDate
, AR4.Balance
, DateDiff(dd,AR4.InvoiceDueDate,GetDate()) AS [Days Past Due]
FROM AR4_OpenInvoice AR4
INNER JOIN AR1_CustomerMaster AR1 ON AR4.CustomerNumber = AR1.CustomerNumber
INNER JOIN Civil.Civil.dbo.company C ON AR1.MasterFileComment = C.coid
WHERE AR4.InvoiceDueDate < GetDate()
And AR4.Balance <> 0
And DateDiff(d,AR4.InvoiceDueDate,GetDate()) >= 90
Version 2
SELECT COALESCE(t.Region,d.CoTeam) AS Team, c.CustomerName, c.MasterFileComment AS [Brock ID],
i.Comment AS [Reference Number], i.InvoiceNumber, i.InvoiceDate, i.InvoiceDueDate, i.Balance,
DATEDIFF(d,InvoiceDueDate,GETDATE()) AS [Days Past Due]
FROM AR4_OpenInvoice i INNER JOIN AR1_CustomerMaster c ON i.CustomerNumber
= c.CustomerNumber LEFT OUTER JOIN
(SELECT 'SWI' Code, 'Central Region' Region
UNION ALL SELECT 'SCR', 'Central Region'
UNION ALL SELECT 'CENT', 'Central Region'
UNION ALL SELECT 'SEL', 'East Region'
UNION ALL SELECT 'NEA', 'East Region'
UNION ALL SELECT 'NEAT', 'East Region'
UNION ALL SELECT 'WES', 'West Region'
UNION ALL SELECT 'WEST', 'West Region'
UNION ALL SELECT 'NATL', 'Federal Accounts')
t ON i.SalespersonCode = t.Code INNER JOIN Civil.Civil.dbo.Company d ON c.MasterFileComment = d.CoId
WHERE i.InvoiceDueDate < GETDATE()AND i.Balance <> 0 AND DATEDIFF(d,InvoiceDueDate,GETDATE()) >= 90;
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply