Converting ACCESS View to TSQL

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Converting ACCESS View to TSQL

Author

Topic

junior6202
Starting Member

45 Posts

Posted - 2014-12-12 : 16:57:30

Hi All,

So I've been tasked to recreate an Access view into SQl ive tried just about everything but the SQL view keeps on timing out. I believe is how I have written both of the expressions because when I take the expressions out it returns data just fine. Any help will be appreciated.

ACCESS Query :

SELECT Part_List.ITEMNMBR, Part_List.FAMILY, Sum(nz([QTY])) AS TOTAL_ORDERS, [TOTAL_ORDERS]/15 AS DAILY_NEEDS, Part_List.USCATVLS_2, Part_List.STAT_CODE
FROM Part_List LEFT JOIN CWO_Total_Orders_History ON Part_List.ITEMNMBR = CWO_Total_Orders_History.ITEMNMBR
GROUP BY Part_List.ITEMNMBR, Part_List.FAMILY, Part_List.USCATVLS_2, Part_List.STAT_CODE
ORDER BY Part_List.ITEMNMBR;

SQL Query :

SELECT dbo.Part_List.ITEMNMBR, dbo.Part_List.Family, SUM(ISNULL(dbo.CWO_Total_Orders_History.Qty, 0)) AS Total_Orders, ISNULL(SUM(dbo.CWO_Total_Orders_History.Qty), 0)/ 15 AS Daily_Needs,
dbo.Part_List.USCATVLS_2, dbo.Part_List.STAT_CODE
FROM dbo.Part_List LEFT OUTER JOIN dbo.CWO_Total_Orders_History ON dbo.Part_List.ITEMNMBR = dbo.CWO_Total_Orders_History.ITEMNMBR
GROUP BY dbo.Part_List.Family, dbo.Part_List.USCATVLS_2, dbo.Part_List.STAT_CODE, dbo.Part_List.ITEMNMBR

Any help will be appreciated. Thanks in advance.

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2014-12-12 : 20:31:53

In SQL, are the join columns indexed

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources