76.

GetDate( ) processing with integral days processing

select getdate(), getdate()-1, getdate() + 365

--getdate() gives the current date, getdate() –1 gives yesterday’s.

--getdate() + 365 gives the date 365 days from now. Take note that this may or may not be one year from now (for leap years,add 366).

77.

GetDate( ) processing with fractional days increment

select getdate() + 1.5

select getdate() + 1.45

--Fractions of the days can also be added to the getdate() functions or any date expression for that matter.

78.

DateAdd( ) function processing

select hire_date,dateadd(yy,2,hire_date)

from pubs..employee

--‘yy,2’ stands for add two years in year part of the date expression.Try: yyyy. Does the same job.

79.

DateAdd( ) function processing for year increments

select '15-Aug-1930' birth_day,dateadd(yy,60,'15-Aug-1930') retirement_day

--Retirement date after 60 years is got, this is not the same as all the days for sixty years added to the birth day. Usually we retire earlier, if we count the effect of the leap year days.

--’15-Aug-1930’ is usual Oracle date format more liked by Europeans, FarEAsians,Indians & Middle Easterns.

--Look at the format of date returned by dateadd function. We will return back to formatting later.

80.

DateAdd( ) function processing for various input date formats

select dateadd(yy,60,'1930-12-13'),dateadd(yy,60,'1930.12-13') ,dateadd(yy,60,'Dec,13 1930')

--Normally you can enter the date in any which format. If you are not confused yourself with American/European formats and separators,and can seggregate them, so would not be SQL Server.

--We should specify input formats to be sure and not depend on SQL Server hidden powers. To be discussed later.

81.

DateAdd( ) function processing for days & weeks increments

select dateadd(dd,-365,hire_date)

from pubs..employee

select dateadd(ww,1000,hire_date)

from pubs..employee

--In dd,-365 syntax, the date 365 days in retrospect is printed for each of the hire_date

--In ww, 1000 th week of the service for the employee is printed

82.

DateAdd( ) function processing for months, minutes and seconds increments

Select DATEADD(mm,-3,getdate( ))

Select DATEADD(ss,86400,getdate( )) as Tommorow_Now

Select RIGHT(DATEADD(mi,150,getdate( )),8) as TestEndTime

--In mm,-3 syntax, the date 3 months in retrospect is printed from now

--In ss,86400 syntax, we add 1 day to todays date. (86400 = 60*60*12)

--In mi, 150 syntax, the date 150 minutes from now is printed. Right function is to cut the date part away.

83.

Date Conversion formats- Specifying SQL Output formats

select convert(varchar,getdate(), 1) --Output in mm/dd/yy

select convert(varchar,getdate(), 2) -- Output in yy.mm.dd

select convert(varchar,getdate(), 3) -- Output in dd/mm/yy

select convert(varchar,getdate(), 4) -- Output in dd.mm.yy

select convert(varchar,getdate(), 5) -- Output in dd-mm-yy

select convert(varchar,getdate(), 6) -- Output in dd Mon yy

select convert(varchar,getdate(), 7) -- Output in Mon dd, yy

select convert(varchar,getdate(), 8) -- Output in hh+:mi:ss

select convert(varchar,getdate(), 9) -- Output in Mon dd yyyy hh:mi:ss:mmmXM

select convert(varchar,getdate(), 10) -- Output in mm-dd-yy

select convert(varchar,getdate(), 11) -- Output in yy/mm/dd

select convert(varchar,getdate(), 12) -- Output in yymmdd

select convert(varchar,getdate(), 13) -- Output in Mon dd yyyy hh+:mi:ss:mmm

select convert(varchar,getdate(), 14) -- Output in hh+:mi:ss:mmm

--Put a Carriage Return before every select to see the effect

--The Output format can be controlled by specifying the numeric Style Ids. You probably need to cram this or refer MS Book online every time you need this

--hh+ is the airline format representation of the hour of the day (0-24). mmm is the milliseconds.

84.

Date Conversion formats in extended Year notation – specifying SQL Output formats

select convert(varchar,getdate(), 101) -- Output in mm/dd/ yyyy

select convert(varchar,getdate(),102) -- Output in yyyy.mm.dd

select convert(varchar,getdate(),103) -- Output in dd/mm/yyyy

select convert(varchar,getdate(),104) -- Output in dd.mm.yyyy

select convert(varchar,getdate(),105) -- Output in dd-mm-yyyy

select convert(varchar,getdate(),106) -- Output in dd Mon yyyy

select convert(varchar,getdate(),107) -- Output in Mon dd, yyyy

select convert(varchar,getdate(),108) -- Output in hh+:mi:ss

select convert(varchar,getdate(),109) -- Output in Mon dd yyyy hh:mi:ss.mmmXM

select convert(varchar,getdate(),110) -- Output in mm-dd-yyyy

select convert(varchar,getdate(),111) -- Output in yyyy/mm/dd

select convert(varchar,getdate(),112) -- Output in yyyymmdd

select convert(varchar,getdate(),113) -- Output in Mon dd yyyy hh+:mi:ss:mmm

select convert(varchar,getdate(),114) -- Output in hh+:mi:ss:mmm

--Put a Carriage Return before every select to see the effect

--The Output format can be controlled by specifying the numeric Style Ids.

--hh+ is the airline format representation of the hour of the day (0-24). mmm is the milliseconds.

85.

Date Conversion formats – specifying Input formats for the varchar dates

select convert(datetime,'12/11/1978',101) --Input in mm/dd/ yyyy

select convert(datetime,'1978.12.11',102) --Input in yyyy.mm.dd

select convert(datetime,'11/12/1978',103) --Input in dd/mm/yyyy

select convert(datetime,'11.12.1978',104) --Input in dd.mm.yyyy

select convert(datetime,'11-12-1978',105) --Input in dd-mm-yyyy

select convert(datetime,'11 Dec 1978',106) --Input in dd-mm-yyyy

select convert(datetime,'Dec 11,1978',107) --Input in Mon dd, yyyy

select convert(datetime,'12-11-1978',110) --Input in mm-dd-yyyy

select convert(datetime,'1978/12/11',111) --Input in yyyy/mm/dd

select convert(datetime,'19781211',112) --Input in yyyymmdd

--Put a Carriage Return before every select to see the effect

--All these conversions yield the same converted date. See the missing styles, they are for time & big date formattings .

--The Output & Input have the same numeric Style Ids.

86.

Date Conversion formats- Input Date format approximation for centuries.

select convert(datetime,'11 Dec 78',6) --Input in dd Mon yy

select convert(datetime,'11 Dec 03',6) --Input in dd Mon yy

-- The first conversion gives a date of 1978, where as the second in place of 1903 gives 2003. A special kludge. Try these-

--select convert(datetime,'11 Dec 51',6) --Input in dd Mon yy yields 1951

--select convert(datetime,'11 Dec 49',6) --Input in dd Mon yy yields 2049

87.

Date Conversion formats- Input Date format , longest forms and for timestamps .

select convert(datetime,'Dec 11 1978 22:12:12.121',113) --Input in Mon dd yyyy hh+:mi:ss:mmm

select convert(datetime,'Dec 11 1978 10:12:12.121PM',109) --Input in Mon dd yyyy hh:mi:ss.mmmXM

select convert(datetime,'22:12:12.121',114) --Input in hh+:mi:ss:mmm

select convert(datetime,'22:12:22',108) --Input in hh+:mi:ss

--Rarely systems take input in a long date format, so much typing.

-- Mostly the assignement of getDate( ) into some date field gives the timestamp to application systems.

88.

Date Conversion formats- Input Date format , longest forms and for timestamps .

select * from pubs..employee

where convert(datetime,hire_date,6) > '11 Dec 93'

select * from pubs..employee where hire_date > '11 Dec 93'

--SQL Server tries its best to find an established format for the input.Change the later to 'Dec.93,11' and it will error out.

--Automatic conversion to a valid internal format is called implicit conversion.

89.

Date Conversion formats- Input Date format , longest forms and for timestamps .

select 'VERIFIED'

where convert(datetime,'11 Dec 93',6) = convert(datetime,'19931211',112)

--The input Style Id brings the diverse formats into one single internal format for comparison.

--Change one of the figure to be different from other and you will get nothing ( A Null) in output.

90.

Date Conversion formats- the ODBC format .

select convert(varchar,hire_date, 121) from pubs..employee where hire_date > '11 Dec 93'

select substring(convert(varchar,getdate(), 120),12,24)

--120 & 121 is the Trans database communication format authorised by ODBC protocol.

91.

Small DateTime format

select getdate(), convert(smalldatetime,getdate( ))

--Conversion to smalldatetime strips away the seconds & milliseconds part. Good for date only processing like Account Opening Date, Test Date, Aircraft Departure Date etc

--This format takes lesser database space

92.

Days elasped since ‘1.1.1900’

select getdate(),convert(decimal,getdate())

select convert(int,getdate())-convert(int,hire_date) DaysInJob from pubs..employee

--This gives the dates elasped since the beginning of 20^th century

93.

Milliseconds elasped since ‘1.1.1900’

select replace(convert(varchar,getdate(), 114),':','') -- stripped time from hh+:mi:ss:mmm

select convert(varchar,getdate(), 112)+replace(convert(varchar,getdate(), 114),':','')

--Most Unique Numeric Key you can ever generate using SQL

--This gives the time in milliseconds elasped since the beginning of 20^th century

94.

Unique string generation based on time elasped in milliseconds(?) from absolute zero.

select convert(timestamp,getdate)

--This gives a unique hexadecimal string guaranteed to be unique across the database.

--This is used by the database to give internal ids to objects not explicitly created by users.

95.

Finding the difference in two dates

select datediff(dd,hire_date,getdate()) EmploymentINDays from pubs..employee

select datediff(mm,hire_date,getdate()) EmploymentINMons from pubs..employee

select datediff(yy,hire_date,getdate()) EmploymntINYers from pubs..employee

select datediff(ww,hire_date,getdate()) EmploymntINWeeks from pubs..employee

select datediff(mi,hire_date,getdate()) EmploymntINMinutes from pubs..employee

--DateDiff gives the difference intwo dates in the units specified

--ww stands for weeks & mi stands for minutes

96.

Finding Anniversaries in this year

select dateadd(yy,datediff(yy,hire_date,getdate()),hire_date) JOBANNIVERSARY

from pubs..employee

select convert(varchar(6),dateadd(yy,datediff(yy,hire_date,getdate()),hire_date)) JOBANNIVERSARY

from pubs..employee

--Here we want the Job Anniversary in this current year. Suits well for listing birthdays of employees in this year too..

97.

Finding Job Lengths or duration from a fixed past date

select *,datediff(yy,hire_date,getdate()) AS JobYears

from pubs..employee

where datediff(yy,hire_date,getdate()) > 14

--Datediff returns an integer so it can be used in any where,and, or clause filter.

98.

Finding the Year, Month & Date parts from a date expression

select DAY(hire_date) from employee

select MONTH(hire_date) from employee

select YEAR(hire_date) from employee

--These functions give the splits of the dates content in a very straightforward way.

99.

Date constituent functions properties

SELECT MONTH(0), DAY(0), YEAR(0)

--This returns 1, 1 & 1900 respectively. Don’t ask why. Year(0) as 1900 is used very often.

100.

DatePart functions

select DATEPART(dw,hire_date) from employee -- Day of week when Joining

select DATEPART(dy,hire_date) from employee -- Nth Day of Year on Joining

select DATEPART(qq,hire_date) from employee -- Quarter of Year at Joining

select DATEPART(mm,hire_date) from employee -- Month of Year at Joining

select DATEPART(dd,hire_date) from employee -- Day of Month in the year of Joining

-- Datepart returns integer value representation of the part

--By default day of week (dw) starts from Sunday. So Sunday=1, Monday = 2 …

101.

Finding the Grouped SUM based on several filtering criteria

SELECT STOR_ID [Store Id],SUM(QTY) [Total Quantity]

FROM sales

WHERE title_id like 'PC%'

AND Stor_id between 7000 and 8000

OR ord_date between '1994-01-01' and '1995-01-01'

GROUP BY STOR_ID

--SUM acts just like Count, but instead of number of rows, accumulates the content of any numeric field like QTY here.

102.

Finding the Average based on several filtering criteria

select avg(discount) avg_discount from discounts

go

SELECT PAYTERMS,AVG(QTY) [Average Quantity]

FROM sales

where ord_date < '1994-01-01'

GROUP BY PAYTERMS

--AVG is also a grouping function and would average out the quantity against the grouping parameter..

103.

Grouping By an expression containing a column

select distinct(Left(upper(name),1)) TABLE_INITIALS, count(*) OCCURENCES

from SysObjects

where xtype = 'U'

group by Left(upper(name),1)

104.

Assigning priorities in filtering a query with the help of proper bracketting (parenthesis)

SELECT * from Pubs..sales

WHERE title_id like 'PC%'

AND (

Stor_id between 7000 and 8000

OR ord_date between '1994-01-01' and '1995-01-01'

)

-----

SELECT * from Pubs..sales

WHERE (

title_id like 'PC%'

and Stor_id between 7000 and 8000

)

OR ord_date between '1994-01-01' and '1995-01-01'

--In the first query the bracketted WHERE clause gives 2 results and the AND clause gives 19 results. (select * from Pubs..sales where Stor_id between 7000 and 8000 OR ord_date between '1994-01-01' and '1995-01-01' gives 19 results). There intersection gives only 1 result as common

--In the second query the bracketted WHERE clause gives 1 result and the OR clause gives 8 results.Since this is a cumulative OR clause, we would get 19 rows in total.

105.

Joining two tables without a clause i.e, cross products for two tables

USE Pubs

SELECT * from authors,titleauthor

GO

SELECT * from authors cross join titleauthor

GO

--This query is not actually joined on any common clause. It is a cartesian product, so if there are 20 records in first table and 30 records in the second we will get a total of 600 records juxtaposed to each other

--Not putting a where clause is same as a CROSS JOIN.

106.

Using cross products to generate unique derived fields

USE Pubs

SELECT distinct (stor_id + '-' + au_lname) compfld

from stores cross join authors

order by compfld

--‘+’ operators concatenates two fields for composite field generation

-- Primary key of two different tables can be used to create a composite key in a link table this way

107.

Using Inner Joins to find exact matching rows

SELECT au_id,pubdate

from titleauthor inner join titles

ON titleauthor.title_id = titles.title_id

--INNER JOIN exactly matches the common key on either sides of the Join

108.

Conventional Inner Joins

SELECT au_id,pubdate

from titleauthor, titles

WHERE titleauthor.title_id = titles.title_id

--Conventional equi joins are difficult to read if there are many tables involved in a join. So use style in Snip 82

109.

Inner Joins having more than two tables

SELECT au_id,UPPER(pub_name) PUBLISHER,pubdate [Published On]

from titleauthor

INNER JOIN titles

ON titleauthor.title_id = titles.title_id

JOIN publishers

ON publishers.pub_id = titles.pub_id

ORDER BY PUBLISHER

--INNER JOIN is the default type of Join. Specifying it is not always needed

110.

Inner Joins having more than two tables refined with WHERE, AND & OR

SELECT au_id AUTHOR,UPPER(pub_name) PUBLISHER,pubdate [Sys Pub Date],Convert(char(12),pubdate) [PUBLISHED ON]

from titleauthor

JOIN titles

ON titleauthor.title_id = titles.title_id

JOIN publishers

ON publishers.pub_id = titles.pub_id

WHERE pub_name like 'ALGO%'

AND au_id >= '3%'

--Multiple Inner Joins further refined by a Where / And Clause

--Convert function gives the date in a more friendly format. Try : Convert(char(20),pubdate) or Convert(char(6),pubdate)

-- ‘>=3%’ will give all the results with au_id starting from 3??-??-???? and above in a lexical order

111.

Left Join having two tables

select a.title_id,a.title ,b.au_ord ,b.au_id

from titles a

left join titleauthor b

ON a.title_id = b.title_id

order by a.title,b.au_ord

--Left Join brings all the rows from table appearing first in the JOIN clause, stuffing NULLs for non matching rows, like here for the title ‘The Psychology of Computer Cooking’

112.

Right Join having two tables

select a.title_id,a.title ,b.au_ord ,b.au_id

from titleauthor b

right join titles a

ON a.title_id = b.title_id

order by a.title,b.au_ord

--Right Join brings all the rows from table appearing second in the JOIN clause, stuffing NULLs for non matching rows, like here for the title ‘The Psychology of Computer Cooking’

--If we swap table positions, a Left Join becomes a Right Join, as evident from previous example.

113.

LeftJoin in two tables having a filter

select stor_name,CAST(discount AS VARCHAR) + '%' '%'

from discounts

LEFT JOIN stores

ON discounts.stor_id = stores.stor_id

WHERE discounts.stor_id IS NOT NULL

--The results can be filtered using a trailing Where.

-- Left join brings all the records from discounts & nulls for unrelated stores records

--CAST does the same jobs as convert but lacks formatting options

--Casting needed as % is a character. Here first ‘%’ becomes part of the data & the second becomes header of the column.

114.

LeftJoin in two tables yielding a grouped sum

select ISNULL(stor_name,'NET-DEAL') [Book Source],

CAST(SUM(discount) AS VARCHAR) + '%' '% Discount'

from discounts

LEFT OUTER JOIN stores

ON discounts.stor_id = stores.stor_id

GROUP BY stor_name

--In a join result a column can be null for- 1.No data in the column or 2. No qualifying row in the left join

--In the above join two rows did not have related stores record. The stor names for these have been aliased to Net-Deal. After this a total has been taken.

115.

LeftJoin in two tables with UNION Operator

Use NorthWind

SELECT CUSTOMERS.,'No Matching Orders --->' DIVIDER,ORDERS. FROM CUSTOMERS

LEFT JOIN ORDERS

ON CUSTOMERS.CustomerId = ORDERS.CustomerId

WHERE ORDERS.CustomerId IS NULL

UNION

SELECT CUSTOMERS.,'Matching Orders --->' DIVIDER ,ORDERS. FROM CUSTOMERS

LEFT JOIN ORDERS

ON CUSTOMERS.CustomerId = ORDERS.CustomerId

WHERE ORDERS.CustomerId IS NOT NULL

ORDER BY DIVIDER DESC

--First Left Join is filtered for non matching customers records. This yields three records in all.

--A Union combines the results from two similar queries into one result set. Order by has to be specified once only though.

--Second Left Join is filtered for matching (existing) records betweenCustomers & Orders. There are 830 such records.

--Order By Desc is done so that ‘No Matching …’ takes precedence over ‘Matching Order..’ in reverse alphabetical sequence