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