April 25, 2013 at 2:55 pm
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
June 7, 2013 at 4:08 am
It might help if you paste one of this multi-layered queries from Access so we can see it.
July 30, 2013 at 4:22 pm
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
July 31, 2013 at 7:36 am
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 ;
July 31, 2013 at 9:45 am
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.
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
July 31, 2013 at 9:50 am
I stand corrected...and pleased that the functionality does exist.
Thanks.
July 31, 2013 at 9:54 am
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;
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
July 31, 2013 at 10:15 am
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.
August 1, 2013 at 1:01 am
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.
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
August 1, 2013 at 7:35 am
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.
August 1, 2013 at 7:46 am
R. Brush (8/1/2013)
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.
ChrisM@Work (7/31/2013)
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.
CROSS APPLY lets you define a calculated column in a query and then use the calculated column name in other calculated columns.
You probably mean to say "Access allows you to perform a calculation in the output set (SELECT list) and reference the result of the calculation elsewhere in the output set". I'm ok with that. The original statement, however, is misleading and incorrect.
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
August 1, 2013 at 7:51 am
ChrisM@Work,
I meant it the exact way vega805 was trying to use it, which is the same way MS Access allows, and the exact way that SQL Server has never allowed.
Have a great day!
August 1, 2013 at 12:01 pm
R Brush and Chris M, thank you so very much for taking the time for this contribution. This is helpful. I mulled over much of the query yesterday to "see" it myself. Also introduces new clauses to me such as CROSS APPLY. So in ChrisM’s query, post 558, the CROSS APPLY part, is this equivalent to that calculated column concept? Could I also achieve this in a sub query? Purposely asking this for understanding. I sense a limitation - that I would not be able to “refer”, “point” or use the result the sub query calculates, CROSS APPLY will allow this. Ill also reference BOL, although at times the explanations are abstract for me.
Thank you again.
_____________________________________________________________________
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
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply