Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

attempting to recreate complex "multi-layer" Access queries in SSMS Expand / Collapse
Author
Message
Posted Thursday, April 25, 2013 2:55 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 4:56 PM
Points: 10, Visits: 155
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
Post #1446723
Posted Friday, June 7, 2013 4:08 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 1, 2014 3:20 AM
Points: 196, Visits: 650
It might help if you paste one of this multi-layered queries from Access so we can see it.


Dird // Junior DBA
11g OCA
10.5 newbie
Post #1461018
Posted Tuesday, July 30, 2013 4:22 PM


Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, July 8, 2014 4:56 PM
Points: 10, Visits: 155
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
Post #1479219
Posted Wednesday, July 31, 2013 7:36 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:39 AM
Points: 101, Visits: 317
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 ;
Post #1479482
Posted Wednesday, July 31, 2013 9:45 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 7,106, Visits: 13,459
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
Post #1479553
Posted Wednesday, July 31, 2013 9:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:39 AM
Points: 101, Visits: 317
I stand corrected...and pleased that the functionality does exist.

Thanks.
Post #1479558
Posted Wednesday, July 31, 2013 9:54 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 7,106, Visits: 13,459
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
Post #1479561
Posted Wednesday, July 31, 2013 10:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:39 AM
Points: 101, Visits: 317
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.
Post #1479581
Posted Thursday, August 1, 2013 1:01 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 7:58 AM
Points: 7,106, Visits: 13,459
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
Post #1479804
Posted Thursday, August 1, 2013 7:35 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, July 15, 2014 6:39 AM
Points: 101, Visits: 317
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.
Post #1479943
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse