Using SQL Alieses or Variables

Hi Experts,

As you'll see in my query below, on lines 26-31 I'm calculating costs for individual categories (fam_cost, ad_cost, ...)

On lines 34-39, as I know I can't use aliases, I'm basically repeating my code to get total_cost, instead of simply doing [fam_cost] + [ad_cost] +..., which would be so much easier and quicker.

Now, even though above I'm answering my own question, I'm still wondering if I could use [fam_cost] + [ad_cost] +... in order to get total_cost, because in later calculations I need to use total_cost to calculate my tax and commissions for example, so my formulas will get longer and longer unnecessarily.

As I have used SQL Variables once or twice before, and I am not sure if they would apply here, but if there any way for me to accomplish shorter formulas?

SELECT fldReservID, fldUserInit, fldUser, fldTripType, fldPickupStat, fldAssocResID,
	   fldTripSubType, fldArchived, 
	
	/*DATE Format*/
	   CONVERT(VARCHAR(10), CAST([fldBookDate] AS DATE),120) AS [Booking_Date], 
	/*TIME Format*/
	   CONVERT(VARCHAR(10), CAST([fldBookTime] AS TIME),114) AS [Booking_Time], 
	
	   fldAgent, fldBookLoc, fldPickupLoc,
	
	/*DATE Format*/
	   CONVERT(VARCHAR(10), CAST([fldPickupDate] AS DATE),120) AS [Pickup_Date], 
	/*TIME Format*/
	   CONVERT(VARCHAR(10), CAST([fldPickupTime] AS TIME),114) AS [Pickup_Time],
	   
	   fldDriver, fldVehicle, fldLName, fldRoomNo, fldReservComment,
	   
	/*QUANTITIES*/
	  	 fldFamQty,		  fldAdultQty,   fldSenQty, fldStudQty, fldChildQty, fldInfQty,
		((fldFamQty * 4) + fldAdultQty + fldSenQty + fldStudQty + fldChildQty) AS [total_pax],

	/*RATES*/
	  	 fldFamRate, fldAdultRate, fldSenRate, fldStudRate, fldChildRate, fldInfRate,
	  	 
	/*COSTS*/
		(fldFamQty		* fldFamRate)	AS [fam_cost],
		(fldAdultQty	* fldAdultRate) AS [ad_cost],
		(fldSenQty		* fldSenRate)	AS [sn_cost],
		(fldStudQty		* fldStudRate)	AS [st_cost],
		(fldChildQty	* fldChildRate) AS [ch_cost],
		(fldInfQty		* fldInfRate)	AS [inf_cost],
		
		(
			(fldFamQty		* fldFamRate)	+
			(fldAdultQty	* fldAdultRate) +
			(fldSenQty		* fldSenRate)	+
			(fldStudQty		* fldStudRate)	+
			(fldChildQty	* fldChildRate) +
			(fldInfQty		* fldInfRate)	
		
		) AS [total_cost]
		
	
FROM tblReservations 
WHERE (fldTripType IS NOT NULL) OR (fldBookTime IS NOT NULL) OR (fldBookTime IS NOT NULL) 
	  OR (fldAgent IS NOT NULL)
ORDER BY fldReservID

Open in new window


Thank you
APD TorontoAsked:
Who is Participating?
 
Mike EghtebasDatabase and Application DeveloperCommented:
Try the following. Obviously, I cannot test it but I know exactly what to do so you can reuse aliases. If there is an error, make a very simple query with fewer column names and one calculation so we can work on it:
Select fldReservID, fldUserInit, fldUser, fldTripType, fldPickupStat, fldAssocResID,
	   fldTripSubType, fldArchived, Booking_Date, Booking_Time, fldAgent, fldBookLoc, fldPickupLoc
	   , Pickup_Date, Pickup_Time, fldDriver, fldVehicle, fldLName, fldRoomNo, fldReservComment
	   , fldFamQty,		  fldAdultQty,   fldSenQty, fldStudQty, fldChildQty, fldInfQty, total_pax
	   , fam_cost + ad_cost + sn_cost + st_cost + ch_cost + inf_cost As total_cost
From (SELECT fldReservID, fldUserInit, fldUser, fldTripType, fldPickupStat, fldAssocResID,
	   fldTripSubType, fldArchived, 
	
	/*DATE Format*/
	   CONVERT(VARCHAR(10), CAST([fldBookDate] AS DATE),120) AS [Booking_Date], 
	/*TIME Format*/
	   CONVERT(VARCHAR(10), CAST([fldBookTime] AS TIME),114) AS [Booking_Time], 
	
	   fldAgent, fldBookLoc, fldPickupLoc,
	
	/*DATE Format*/
	   CONVERT(VARCHAR(10), CAST([fldPickupDate] AS DATE),120) AS [Pickup_Date], 
	/*TIME Format*/
	   CONVERT(VARCHAR(10), CAST([fldPickupTime] AS TIME),114) AS [Pickup_Time],
	   
	   fldDriver, fldVehicle, fldLName, fldRoomNo, fldReservComment,
	   
	/*QUANTITIES*/
	  	 fldFamQty,		  fldAdultQty,   fldSenQty, fldStudQty, fldChildQty, fldInfQty,
		((fldFamQty * 4) + fldAdultQty + fldSenQty + fldStudQty + fldChildQty) AS [total_pax],

	/*RATES*/
	  	 fldFamRate, fldAdultRate, fldSenRate, fldStudRate, fldChildRate, fldInfRate,
	  	 
	/*COSTS*/
		(fldFamQty		* fldFamRate)	AS [fam_cost],    
		(fldAdultQty	* fldAdultRate) AS [ad_cost],
		(fldSenQty		* fldSenRate)	AS [sn_cost],
		(fldStudQty		* fldStudRate)	AS [st_cost],
		(fldChildQty	* fldChildRate) AS [ch_cost],
		(fldInfQty		* fldInfRate)	AS [inf_cost],
		
		/*(
			(fldFamQty		* fldFamRate)	+
			(fldAdultQty	* fldAdultRate) +
			(fldSenQty		* fldSenRate)	+
			(fldStudQty		* fldStudRate)	+
			(fldChildQty	* fldChildRate) +
			(fldInfQty		* fldInfRate)	
		
		) AS [total_cost])*/
		
	
FROM tblReservations 
WHERE (fldTripType IS NOT NULL) OR (fldBookTime IS NOT NULL) OR (fldBookTime IS NOT NULL) 
	  OR (fldAgent IS NOT NULL)) As myTable (fldReservID, fldUserInit, fldUser, fldTripType, fldPickupStat, fldAssocResID,
	   fldTripSubType, fldArchived, Booking_Date, Booking_Time, fldAgent, fldBookLoc, fldPickupLoc
	   , Pickup_Date, Pickup_Time, fldDriver, fldVehicle, fldLName, fldRoomNo, fldReservComment
	   , fldFamQty,		  fldAdultQty,   fldSenQty, fldStudQty, fldChildQty, fldInfQty
	   , total_pax, fldFamRate, fldAdultRate, fldSenRate, fldStudRate, fldChildRate, fldInfRate
	   , fam_cost, ad_cost, sn_cost, st_cost, ch_cost, inf_cost)
ORDER BY fldReservID

Open in new window



Mike
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Here is simplified version of it:

Select ReservID
     , UserInit
     , UserName 
     , fam_cost
     , ad_cost, fam_cost + ad_cost As total_cost
From (SELECT fldReservID
           , fldUserInit
		   , fldUser
		   , (fldFamQty	* fldFamRate)	AS fam_cost
		   , (fldAdultQty	* fldAdultRate) AS ad_cost
	
FROM tblReservations 
WHERE (fldTripType IS NOT NULL) OR (fldBookTime IS NOT NULL) OR (fldBookTime IS NOT NULL) 
	  OR (fldAgent IS NOT NULL)) As myTable (ReservID
	                                       , UserInit
	                                       , UserName 
	                                       , fam_cost
	                                       , ad_cost)
ORDER BY fldReservID

Open in new window


This is called external derived table. Although a typical query is written in the following order:

Select
From
Where
Order By

But the logical order of execution is:
From
Where
Select
Order By

So, alias in from could be used in select.
Alias in select could be use in Order By  (not in where)
Therefor, if in From clause we could place a select ... as derived table, aliases within can be used in the outer query.

Note: In SQL Server, aliases defined in any select clause is not available for use in the same select clause. This is called all-at-once ...

However, in MS Access, I have tested it, aliases within the same select is available for use again.

Here is a simple example: http://logicalread.solarwinds.com/when-to-apply-sql-server-derived-tables-mc03/#.VUQwUCFVhBc

Mike
0
 
APD TorontoAuthor Commented:
What about using SQL Variables?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Mike EghtebasDatabase and Application DeveloperCommented:
APD_Toronto,

Why you want use variables if it could be done via a single query? The query type that is designed particularly for this purpose.

With variables, for re-usability,  you have to put all in a stored procedure more work and effort.

Are you looking for a solution or are interested in a solution using variables?  

Mike
0
 
APD TorontoAuthor Commented:
Mike,

Your second example gives me access to ad_cost and fam_cost, so that I can use in the sub-query to get total_cost, OK.  Later, as per my original post, I need my total_cost to get my tax_cost and commission_cost, then a sub-sub-sub-query to get my net.  This can get out of hand.

As in programming, I thought you could simplify this using variables, but maybe SQL Variables do not work the same way?
0
 
APD TorontoAuthor Commented:
I'm not sure how to accomblish this, but for example:

@fam_cost = fldFamQty * fldFamRate
@ad_cost = fldAdQty * fldAdRate

@tot_cost = @fam_cost + @ad_cost

SELECT fldReservID, @tot_cost AS [tot_cost], @fam_cost AS [fam_cost],  @ad_cost AS [tot_cost]

FROM ...
WHERE ...
ORDER BY ...

Open in new window

0
 
Mike EghtebasDatabase and Application DeveloperCommented:
In the derived table you get sub totals using:

/*COSTS*/
            (fldFamQty            * fldFamRate)      AS [fam_cost],    
            (fldAdultQty      * fldAdultRate) AS [ad_cost],
            (fldSenQty            * fldSenRate)      AS [sn_cost],
            (fldStudQty            * fldStudRate)      AS [st_cost],
            (fldChildQty      * fldChildRate) AS [ch_cost],
            (fldInfQty            * fldInfRate)      AS [inf_cost],

In the outer query, you sum them up to get total using:

fam_cost + ad_cost + sn_cost + st_cost + ch_cost + inf_cost As total_cost

So far all is under control, right?

We have two choices here to do the rest of the calculations (to perform your other tax calculations) in the outer query. Instead of using  total_cost use (fam_cost + ad_cost + sn_cost + st_cost + ch_cost + inf_cost ) for these calculations for now.

This is not as ideal but better than repeating calculation you had originally. After you do this, we can further optimize the process using multiple CTEs where we just use total_cost in the outer query not its extended version of it (fam_cost + ad_cost + sn_cost + st_cost + ch_cost + inf_cost ).

Mike
0
 
Mike EghtebasDatabase and Application DeveloperCommented:
Hi APD_Toronto,

You probably were looking for a solution and I was trying just to learn. What you have accepted could be improved doing CTEs or instead derived table and also using stored procedure. If you have the time and are interested new ways of doing it we can work together to lean something new. I am preparing for query certification and would like to do this if you want to play and learn.

Mike
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.