-- John Aromando -- SQL Calculations Logic Statements --

use ogcPubLev3

select 
	bktitle as Books, 
	devcost as Cost, 
	pubdate as Date,
	DATEPART(year,pubdate) as Year,
	DATEPART(month,pubdate) as Month,
	DATEPART(day,pubdate) as Day
from titles
where DATEPART(year,pubdate) > 2007 and DATEPART(year,pubdate) < 2009
order by pubdate, devcost desc

--DateAdd: add or subtract date units to a datepart
select ordnum, sldate,
	sldate+30 as ShipDate,
	DATEADD(MONTH, 6, sldate) as 'No interest if Paid in Full by'
from Sales

select 
	bktitle as Title, 
	round(datediff(day,pubdate,getdate())/365.25,1) as 'Years in Print'
from obsolete_titles


--text functions

select 
	repid as Emp#,
	RTRIM(lname)+', '+RTRIM(fname) as Full_Name,
	commrate as Commission_Rate
from Slspers
order by lname

select 
	RTRIM(lname)+', '+left(fname,1)+' - '+repid as Sales_Rep,
	'[     ]' as Male, '[     ]' as Female,
	'' as Please_Check_Gender
from Slspers
order by lname

select bktitle, '$  '+replace(convert(nvarchar,round(devcost/3,0),1),'.00','  in gold') 
as Pub_Cost  from titles

select bktitle, replace(bktitle,',','|') 
as No_Commas  from titles

--replace function can replace the decimals or other text with other characters

select 
	lname as Last_Name,
	ltrim(reverse(lname))as Reversed,
	'a' as Search_For, 
	PATINDEX('%a%',lname) as In_Position,
	SUBSTRING(lname,PATINDEX('%a%',lname),10) as Extract_Balance,
	upper(RIGHT(rtrim(lname),1)) as Last_Char,
	lower(REPLICATE(LEFT(lname,1),len(lname))) as Replicate_Initial_Char,
	str(commrate * 100) + '.0 %' as Com_Rate,
	replicate('$',commrate*100) as Graph
from slspers
	where PATINDEX('%a%',lname) > 0

select bktitle, slprice, replicate('&',slprice) as graph, 
	len(replicate('&',slprice)) as #_of_Chars
from titles

--Create email addresses for all employees along side their
--ID numbers - result should look like the following example:
--r.gibson@ogcbooks.com
select 
	repid as Emp#,
	RTRIM(lname)+', '+fname as Full_Name,
	LOWER(left(fname,1)+'.'+rtrim(lname)+'@ogcPub.com') as eMail
from slspers

--increase cheap books (<20) by 1 dollar
--decrease expensive books (>50) by 1 dollar
select bktitle as Book, slprice as Price,
	case 
		when slprice < 20 then slprice+1 
		when slprice > 50 then slprice-1
		else slprice 
	end
	as NewPrice
from Titles
order by Price

--break books into pricing category
--alias placed before logical (alternate alias method)
create view PricingCategories
as
SELECT Book_ID = partnum, Book = bktitle, Price = slprice, Price_Range = 
      CASE 
         WHEN slprice < 20 THEN '$0 to $20'
         WHEN slprice >= 20 and slprice < 30 THEN '$20 to $30'
         WHEN slprice >= 30 and slprice < 40 THEN '$30 to $40'
         WHEN slprice >= 40 and slprice < 50 THEN '$40 to $50'
         ELSE '$50 to $100'
      END
FROM Titles

--group by pricing range and count the books in each
select Price_Range, COUNT(book_Id)#_of_books
from pricingCategories
group by price_range