A simple but tactical enough, Power of Group By - I

Today my mate draw closer to me for a straight forward script, he had a table which consist of dates in int (yyyymmdd) format, his requisite was to get the latest date of each month.

For example,

Out of 20150123, 20150130, 20150127 the output should be 20150130.

Let’s stimulate the problem by taking an example and lets see how we will approach towards the solution

CREATE table DemoMax

(id int identity(1,1),

mydate int)

insert into DemoMax values (20150226)

insert into DemoMax values (20150131)

insert into DemoMax values (20150329)

insert into DemoMax values (20150429)

insert into DemoMax values (20150530)

insert into DemoMax values (20150626)

insert into DemoMax values (20150726)

insert into DemoMax values (20150826)

insert into DemoMax values(20150226)

insert into DemoMax values(20150227)

insert into DemoMax values(20150328)

Here we have table created with few rows in it.

Our requirement is to get the highest date for each month, for finding so, let’s bifurcate the myDate to yyyy, mm and dd in three separate columns.

SUBSTRING(cast(mydate as varchar),1,4),

SUBSTRING(cast(mydate as varchar),5,2),

Now the second step is to group by the result on the above bifurcated column and applying the max aggregate function to find the largest among all date for a specific yyyy and mm.

max( cast(SUBSTRING(cast(mydate as varchar),7,2) as int) )

Lets formulate the final query

SELECT SUBSTRING(cast(mydate AS VARCHAR), 1, 4) as YYYY

       ,SUBSTRING(cast(mydate AS VARCHAR), 5, 2) as MM

       ,max(cast(SUBSTRING(cast(mydate AS VARCHAR), 7, 2) AS INT)) as LatestDate

FROM DemoMax

GROUP BY SUBSTRING(cast(mydate AS VARCHAR), 1, 4)

       ,SUBSTRING(cast(mydate AS VARCHAR), 5, 2)

YYYY MM   LatestDate

---- ---- -----------

2015 01   31

2015 02   27

2015 03   29

2015 04   29

2015 05   30

2015 06   26

2015 07   26

2015 08   26

(8 row(s) affected)

Reformulate the query to get the outout in yyyymmdd format,

with LatestDateCTE

as

(

SELECT SUBSTRING(cast(mydate AS VARCHAR), 1, 4) as YYYY

       ,SUBSTRING(cast(mydate AS VARCHAR), 5, 2) as MM

       ,max(cast(SUBSTRING(cast(mydate AS VARCHAR), 7, 2) AS INT)) as LatestDate

FROM DemoMax

GROUP BY SUBSTRING(cast(mydate AS VARCHAR), 1, 4)

       ,SUBSTRING(cast(mydate AS VARCHAR), 5, 2)

)

select (YYYY + MM + LatestDate) as MyDate from LatestDateCTE

MyDate

-----------

201532

201529

201532

201533

201535

201532

201533

201534

(8 row(s) affected)

 

A cup of tea with SQL Server is the greatest combination :)

Post Reference: Vikram Aristocratic Elfin Share

How to convert scientific number to decimal?

Suppose you have number in scientific form and you want to migrate these number to a decimal field. How you approach to do this since cast function to decimal fails with scientific numbers

Now if have a number like this

'0.23e10'

Lets try to cast it to decimal(10,5)

select cast('0.23e10' as decimal(10,5))

Msg 8114, Level 16, State 5, Line 1

Error converting data type varchar to numeric.

So its fail. Ok lets try to cast it to float type.

select cast('0.00023e4' as float)

FloatCasted

----------------------

2.3

That’s great, now lets try to convert to decimal.

select cast(cast('0.00023e4' as float) as decimal(10,5)) decimalCasted

FloatCasted

---------------------------------------

2.30000

That’s workaround for scientific to decimal conversion.

Get your tea cup and start coding J

Post Reference: Vikram Aristocratic Elfin Share

Function in where clause degrade performance

When function are used in where clause it majorly affect the performance of query if query is intended to result large number of rows.

This is because function will force to evaluate for each row of table data involved to make the comparison which will force optimizer to use Index scan or Table Scan instead of Index Seek if proper index involve.

Lets see with an example how function in where clause affect performance. For demo purpose I am making use of AdventureWorks2008R2 database table Person.Person

First I am creating an Nonclustered index on FirstName column of Person table.

create index ix_person_firstname

on Person.Person(FirstName) include(LastName,MiddleName)

Command(s) completed successfully.

Now lets fire two  query, one which is using substring function in where clause and another which is simply written but both are producing the same output. And check the relative batch cost of each query. Set the execution plan on

select FirstName,LastName,MiddleName from Person.Person

where FirstName like 'AB%'

select FirstName,LastName,MiddleName from Person.Person

where substring(FirstName,0,3) = 'AB'

 Finding from execution Plan:

1        The first query use Index Seek to find the record where as the second query with Substring function in where clause uses Index scan which affect the performance as compare to index seek. Because Function in where clause force optimizer to evaluate the value of where predicate for each record in table to bring the matching criteria of records.

2.       

           If  we see the relative batch cost of both the query, then we are finding query without function in where predicate took just 3% of total batch cost and query with function in where clause took 97% of total cost of batch.

Conclusion: Query without function in where predicate perform very well as compared to query with function in where clause. Try to avoid function in where predicate.    

If there is only one thing which makes coder always chill whatever would be the situation, probably it would be code, hmm I love :)

 

Post Reference: Vikram Aristocratic Elfin Share

Printing next 5 Sunday, Part-3

Read Prev Post   : Printing next 5 Sunday, Part-1

                         : Printing next 5 Sunday, Part-2

Problem Statement was: “I want a list of next five Sunday, how I can achieve this through T-SQL”

This is again another approach to find the next 5 Sunday, here we removed the CTE which we used in the last post, and replace it with Rank function.

declare @dt  datetime

declare @howMany integer =5

Select  @dt = DATEADD(d, 6, dateadd(wk, datediff(wk, 0, getdate()), 0)) ;

select top (@howMany)  dateadd(d,7*(cast(ROW_NUMBER() over (order by name) as int) - 1),@dt) as weekdays,

DATENAME(dw, DATEADD(d, 7 * (cast(ROW_NUMBER() over (order by name) as int) - 1), @dt)) as Weekdays

from sys.objects

date                    Weekdays

----------------------- ------------------------------

2014-07-06 00:00:00.000 Sunday

2014-07-13 00:00:00.000 Sunday

2014-07-20 00:00:00.000 Sunday

2014-07-27 00:00:00.000 Sunday

2014-08-03 00:00:00.000 Sunday

Let’s dissect the code and understand each statement.

dateadd(d,7*(cast(ROW_NUMBER() over (order by name) as int) - 1),@dt)

Lets divide this statement further more

cast(ROW_NUMBER() over (order by name) as int) – 1

This will give list of numbers starts from 0,1,2,3…….

Here below line will multiply 7*(0,1,2,3….) and add the number of days in multiple of 7 to the @dt date

dateadd(d,7*(cast(ROW_NUMBER() over (order by name) as int) - 1),@dt)

So if the @dt is 2014-07-06

In first Iteration

select dateadd(d,7*(0),'2014-07-06')

-----------------------

2014-07-06 00:00:00.000

In second Iteration

select dateadd(d,7*(1),'2014-07-06')

-----------------------

2014-07-13 00:00:00.000

I am a coder, I have given my life to you, give me smile now! J

 

Post Reference: Vikram Aristocratic Elfin Share