-- John Aromando -- SQL Conversion Functions --

use ogcPubLev3

--comparison of conversion functions

select bktitle, devcost 
as Pub_Cost from titles

--devcost is data type of money / displays 2 decimals
select bktitle, '$ '+devcost --money data type does not allow concatenation 
as Pub_Cost from titles

--string function
select bktitle, '$ '+str(devcost) 
as Pub_Cost from titles

--str function converts to string data type, rounds to whole number and allows concatenation
select bktitle, '$ '+str(devcost)/3 --once data has been converted to text, math cannot be performed
as Pub_Cost from titles

--fix
select bktitle, '$ '+str(devcost/3)
as Pub_Cost from titles

select bktitle, '$ '+str(devcost/3,8,2) --str function also has decimal arguments (max Characters and how many decimals)
as Pub_Cost from titles

--cast function
select bktitle, cast(devcost/3 as DECIMAL(14,10)) --money divided by 3 will give extended decimals 
as Pub_Cost from titles
--Concatenation not possible as data type is numeric

select bktitle, '$'+cast(devcost/3 as nvarchar) --cast function can convert to various data types, some of which are truncated, or rounded if converted to text data type concatenation is allowed
as Pub_Cost from titles

--convert function
select bktitle, '$'+convert(nvarchar,devcost/3) 
as Pub_Cost from titles

--convert function similar to cast
select bktitle, '$'+convert(nvarchar,devcost/3,1) 
as Pub_Cost from titles

--convert function can also insert commas per 3 digets / 3rd argument(,1)
select bktitle, '$'+convert(nvarchar,round(devcost/3,6),1) 
as Pub_Cost from titles

--round function rounds to the desired decimal accuracy (1 = comma delimiter, 2 decimals)
select bktitle, '$'+convert(nvarchar,round(devcost/3,6),2) 
as Pub_Cost from titles


--nested cast & round functions
select 
	bktitle as Titles,
	slprice as SRP,
	slprice*(1-.25)as SalePrice,
	ROUND(slprice*(1-.25),2)as Rounded_Sale_Price,
	cast(slprice*(1-.25) as DECIMAL(9,2))as Trimmed_Sale_Price
from titles 

--nested string function & concatenations to create a currency format
select  
	bktitle as Title,
	slprice as SRP, 
	'$'+str(round(slprice*1.15,2),5,2) as 'Price',
	'$   '+str(round(slprice*1.15,2),5,2) as 'Price w Space'
		--spaces after '$' create gap between $ and number
from titles
 
--nested string function & concatenations to create a percentage format*/
select 
	lname, commrate as 'Current Rate', 
	str(round(commrate,3)*100,3,2)+'  %' as 'Current Rate as Percentage', 
	str(round(commrate*100+.2,3),3,2)+'  %'	as 'Commission Rate Increase'
from slspers
/*times 100 moves decimal point for proper format 
of percentage / adjust decimal position in round 
function or in str function and add '%' symbol*/

--convert function for date/time domestic format
select
	sldate as 'Original Date', 
	CONVERT(nVARCHAR(8),sldate,10) as 'Domestic Date Hyphen', --We want 8 character output, 10 equals hyphen seperator
	ordnum as Ord#,
	partnum as Part#,
	qty as Quantity
from sales 

--abbreviated format
select 
	sldate as 'Original Date', 
	CONVERT(nVARCHAR(8),sldate,1) as 'Domestic Date Slash',
	ordnum as Ord#,
	partnum as Part#,
	qty as Quantity
from sales

/*Summarize all order totals by customers.
Since this is only for analysis purposes we  
wish to only see 0 decimals values.
Currency symbols and commas need to be shown.*/
select 
	c.Custnum as CustID, 
	Custname as Customer,
	'$  '+convert(nvarchar,round(SUM(qty*slprice),0),1)
		as 'Customer Totals'
from Customers as c, Sales as s, Titles as t
Where c.custnum = s.custnum and t.partnum = s.partnum
group by c.Custnum, custname
order by CustID