SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


attempting to recreate complex "multi-layer" Access queries in SSMS


attempting to recreate complex "multi-layer" Access queries in SSMS

Author
Message
vega805
vega805
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 245
Please excuse me if Im not usung the right terminology.

I am a SQL Server newbie and have a basic understanding of TSQL. A few chapters into TSQL fundamentals.

I am previously a heavy user of Access which is one data source I’m often pointing to in PowerPivot.

To achieve desired complex values there are some instances/queries that I have which are split into parts, that is that I may have one query referring to the output of an initial/other query (by name), I think of it as 2-layered, sometimes its “3- layered”. Usually due toaggregate functions and other calculations. I’m not sure what to refer to this as.

Issue: I want to sever myself from Access and of query designer and doing things this way, thus im trying to recreate certain Access queries in SSMS. There are straight forward queries and some with joins, but these complex “multi-layered” queries throw me. My question - how is this scenario done in SS? How would a developer achieve this? Is this stored procedure? Lots of joins? I’m a little lost here.

_____________________________________________________________________
As soon as you see something, you already start to intellectualize it. As soon as you intellectualize something, it is no longer what you saw. Suzuki-roshi
Dird
Dird
SSC-Addicted
SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)SSC-Addicted (463 reputation)

Group: General Forum Members
Points: 463 Visits: 808
It might help if you paste one of this multi-layered queries from Access so we can see it.


Dird
vega805
vega805
Valued Member
Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)Valued Member (61 reputation)

Group: General Forum Members
Points: 61 Visits: 245
So here’s an example. The query works - to derive the business logic I need for the result the query is referring to aggregations or results in two other queries. It may be a crude, but it works. I know there’s as better way, what I’d like to know how you approach queries like this, where you have to refer to other aggregations or results for the ultimate result? Is it just in parenthesis in the main query itself? So a very long query?

SELECT tlkpCompany.companyName, tblCycleRanch.hoops, tblCycleRanch.ranchNumber, tbNHC.[00002], tbNHC.[30], tbNHC.[40], tbNHC.[60], Sum(qry_13winterPlant.qtyReceived) AS SumOfqtyReceived, ([30]+[40]+(0.6*[60]))/([SumOfqtyReceived]/[TotalAc]) AS crateHC, qry_strawRanchTotAc.SumOfacres AS TotalAC, tbNHC.Date
FROM (((tlkpCompany INNER JOIN tblCycleRanch ON tlkpCompany.[companyNum] = tblCycleRanch.[companyNum]) INNER JOIN qry_13winterPlant ON tblCycleRanch.ranchNumber = qry_13winterPlant.ranchNumber) INNER JOIN qry_strawRanchTotAc ON tblCycleRanch.ranchNumber = qry_strawRanchTotAc.ranchNumber) INNER JOIN tbNHC ON tblCycleRanch.ranchNumber = tbNHC.ranchNumber
GROUP BY tlkpCompany.companyName, tblCycleRanch.hoops, tblCycleRanch.ranchNumber, tbNHC.[00002], tbNHC.[30], tbNHC.[40], tbNHC.[60], ([30]+[40]+(0.6*[60]))/([SumOfqtyReceived]/[TotalAc]), qry_strawRanchTotAc.SumOfacres, tbNHC.Date;

_____________________________________________________________________
As soon as you see something, you already start to intellectualize it. As soon as you intellectualize something, it is no longer what you saw. Suzuki-roshi
R. Brush
R. Brush
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 352
Access lets you define a calculated column in a query and then use the calculated column name in other calculated columns. SQL Server will not allow this. In your calculation for [crateHC] you will have to replace [SumOfqtyReceived] with (SUM(qry_13winterPlant.qtyReceived)), and replace [TotalAC] with (qry_strawRanchTotAc.SumOfacres). And do the same where [SumOfqtyReceived] and [TotalAC] are used in the GROUP BY clause.

Also, I hope (SUM(qry_13winterPlant.qtyReceived)) and (qry_strawRanchTotAc.SumOfacres) can never be zero since you are dividing by them.

SELECT tlkpCompany.companyName
, tblCycleRanch.hoops
, tblCycleRanch.ranchNumber
, tbNHC.[00002]
, tbNHC.[30]
, tbNHC.[40]
, tbNHC.[60]
, SUM(qry_13winterPlant.qtyReceived) AS SumOfqtyReceived
, ( [30] + [40] + ( 0.6 * [60] ) ) / ( SUM(qry_13winterPlant.qtyReceived) / qry_strawRanchTotAc.SumOfacres ) AS crateHC
, qry_strawRanchTotAc.SumOfacres AS TotalAC
, tbNHC.Date
FROM tlkpCompany
INNER JOIN tblCycleRanch
ON tlkpCompany.[companyNum] = tblCycleRanch.[companyNum]
INNER JOIN qry_13winterPlant
ON tblCycleRanch.ranchNumber = qry_13winterPlant.ranchNumber
INNER JOIN qry_strawRanchTotAc
ON tblCycleRanch.ranchNumber = qry_strawRanchTotAc.ranchNumber
INNER JOIN tbNHC
ON tblCycleRanch.ranchNumber = tbNHC.ranchNumber
GROUP BY tlkpCompany.companyName
, tblCycleRanch.hoops
, tblCycleRanch.ranchNumber
, tbNHC.[00002]
, tbNHC.[30]
, tbNHC.[40]
, tbNHC.[60]
, ( [30] + [40] + ( 0.6 * [60] ) ) / ( SUM(qry_13winterPlant.qtyReceived) / qry_strawRanchTotAc.SumOfacres )
, qry_strawRanchTotAc.SumOfacres
, tbNHC.Date ;
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16042 Visits: 19527
R. Brush (7/31/2013)
Access lets you define a calculated column in a query and then use the calculated column name in other calculated columns. SQL Server will not allow this. ...


Not in 7,2000, no. But it will in 2005 onwards.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
R. Brush
R. Brush
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 352
I stand corrected...and pleased that the functionality does exist.

Thanks.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16042 Visits: 19527
Like this:
SELECT 
c.companyName,
r.hoops,
r.ranchNumber,
tbNHC.[00002],
tbNHC.[30],
tbNHC.[40],
tbNHC.[60],
p.SumOfqtyReceived,
([30]+[40]+(0.6*[60]))/(p.[SumOfqtyReceived]/x.[TotalAc]) AS crateHC,
x.TotalAC,
tbNHC.Date
FROM tlkpCompany c
INNER JOIN tblCycleRanch r ON c.[companyNum] = r.[companyNum]
CROSS APPLY ( -- perform aggregate here instead of main query
SELECT SumOfqtyReceived = Sum(p.qtyReceived)
FROM qry_13winterPlant p
WHERE r.ranchNumber = p.ranchNumber
) p
CROSS APPLY ( -- just an example
SELECT s.SumOfacres AS TotalAC
) x
INNER JOIN qry_strawRanchTotAc s ON r.ranchNumber = s.ranchNumber
INNER JOIN tbNHC ON r.ranchNumber = tbNHC.ranchNumber
--GROUP BY
-- c.companyName,
-- r.hoops,
-- r.ranchNumber,
-- tbNHC.[00002],
-- tbNHC.[30],
-- tbNHC.[40],
-- tbNHC.[60],
-- ([30]+[40]+(0.6*[60]))/([SumOfqtyReceived]/[TotalAc]),
-- s.SumOfacres,
-- tbNHC.Date;



“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
R. Brush
R. Brush
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 352
Just a clarificaton:
An alias for a calcuated column can be used in GROUP BY or ORDER BY clauses, but not in the SELECT column list as vega805 was attempting (at least not with SQL Server 2008).

Ex.
WITH test
AS (
SELECT 1 AS ID, 10 AS [rate], 1 AS [Hours]
UNION ALL
SELECT 2, 12, 2
UNION ALL
SELECT 3, 10, 4
UNION ALL
SELECT 4, 11, 6
)

Select [ID]
, [Rate] * [Hours] AS [Amount]
, [Amount] * (.15) as [Markup]
FROM test


Subsequently using the alias [Amount] to perform another calculation does generate an error in SQL Server 2008.
ChrisM@Work
ChrisM@Work
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16042 Visits: 19527
R. Brush (7/31/2013)
Just a clarificaton:
An alias for a calcuated column can be used in GROUP BY or ORDER BY clauses, but not in the SELECT column list as vega805 was attempting (at least not with SQL Server 2008).

Ex.
WITH test
AS (
SELECT 1 AS ID, 10 AS [rate], 1 AS [Hours]
UNION ALL
SELECT 2, 12, 2
UNION ALL
SELECT 3, 10, 4
UNION ALL
SELECT 4, 11, 6
)

Select [ID]
, [Rate] * [Hours] AS [Amount]
, [Amount] * (.15) as [Markup]
FROM test


Subsequently using the alias [Amount] to perform another calculation does generate an error in SQL Server 2008.


The syntax is slightly different and conversion from one SQL flavour to the other is trivial:
WITH test AS (
SELECT 1 AS ID, 10 AS [rate], 1 AS [Hours]
UNION ALL
SELECT 2, 12, 2
UNION ALL
SELECT 3, 10, 4
UNION ALL
SELECT 4, 11, 6
)

SELECT [ID]
, x.[Amount]
, x.[Amount] * (.15) AS [Markup]
FROM test
CROSS APPLY (SELECT [Rate] * [Hours] AS [Amount]) x



WITH test AS (
SELECT 1 AS ID, 10 AS [rate], 1 AS [Hours]
UNION ALL
SELECT 2, 12, 2
UNION ALL
SELECT 3, 10, 4
UNION ALL
SELECT 4, 11, 6
)

SELECT [ID]
, x.[Amount]
, y.[Markup]
FROM test
CROSS APPLY (SELECT [Rate] * [Hours] AS [Amount]) x
CROSS APPLY (SELECT x.[Amount] * (.15) AS [Markup]) y
ORDER BY y.[Markup] DESC



CROSS APPLY without a table reference equates to calculate.

“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
Exploring Recursive CTEs by Example Dwain Camps
R. Brush
R. Brush
SSC-Enthusiastic
SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)SSC-Enthusiastic (189 reputation)

Group: General Forum Members
Points: 189 Visits: 352
ChrisM@Work,

I know how to do it. I didn't say it could not be accomplished some other way.

My original post said that an alias cannot be referred to in a subsequent calculation like MS Access allows.

You chose to take issue with that, indicating I was giving misinformation in regards to later versions of SQL Server.

The fact is...an alias cannot be referred to in a subsequent calculation in the same way that MS Access allows. Period.

In the future, please don't say I'm wrong, if I'm not.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search