Facebook Sign in | Join
Pushing database changes needn't be hard work with SQL Compare
Getting Started with Adobe After Effects - Part 6: Motion Blur
First Time? You can support us by signing up. It takes only 5 seconds. Click here to sign up. If you already have an account, click here to login.

TSQL Challenge 6 - Solution by Cleber Augusto Martins



Cleber is a new challenger. He provides us a nice and readable solution using UNION and CROSS JOIN.
Cleber Martins works for ACI Worldwide as a Sr. Business Solutions Analyst for the Risk products. Cleber is  based in Brazil and has 8 years of experience supporting financial institutions to prevent fraud and money laundering activities with neural models, rules based technology and consulting services.
Cleber's experience with SQL Server is very successful in transactional warehouses handling 1/2Billion transactions a month on terabyte databases where peaks can go over 4MM transactions per hour.

Cleber said about TSQL Challenges:
"I've been on engagements where customers want to extract very complex and detailed information from our product's databases, so once I'm not a SQL professional then I'm always researching the internet for clues. Now that I found beyondrelational.com and the challenges then I'm able to grow my knowledge on weekly basis and do my job better everyday."

 

Here is TSQL Challenge 6 Cleber’s comments and code:

My hundreds first thoughts for the Challenge 6 was to do multiple queries, but once the challenge stated that one query would be better score then I've assumed that I should be able to do it. I've take advantage of the unique ID's, so I've joined the table with itself what allowed me to use the first tree ID's to get the results for each of the consolidations.

SET NOCOUNT ON
 
SET DATEFORMAT YMD
 
SET DATEFIRST 1
 
DECLARE @T TABLE (ID INT IDENTITY(1,1), VisitDate DATETIME, Page NVARCHAR(128),
NbVisitors INT)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-23','Home',10)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-24','Home',14)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-26','Home',22)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-27','Home',3)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-29','Home',4)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-04-01','Home',33)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-04-03','Home',2)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-26','Contact',22)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-27','Contact',10)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-29','Contact',35)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-30','Contact',13)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-27','Products',8)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-03-29','Products',12)
INSERT INTO @T (VisitDate,Page,NbVisitors) VALUES ('2009-04-01','Products',16)
 
select  "PERIOD_ID"
    = case a.ID
      when 1 then convert(varchar, datepart(yy, min(b.VisitDate))) + 
        convert(varchar, datepart(ww, min(b.VisitDate))) + 
        convert(varchar, datepart(dw, min(b.VisitDate)))
      when 2 then convert(varchar, datepart(yy, min(b.VisitDate))) + 
        convert(varchar, datepart(ww, min(b.VisitDate))) + 'T'
      when 3 then convert(varchar, datepart(yy, min(b.VisitDate))) + 'T'
      end
    ,"LEVEL"
    = a.ID -1
from      @T a, @T b
where      a.ID <= 3
 
select     
    "PERIOD_ID"
    = case a.ID
      when 1 then convert(varchar, datepart(yy, min(b.VisitDate))) + 
        convert(varchar, datepart(ww, min(b.VisitDate))) + 
        convert(varchar, datepart(dw, min(b.VisitDate)))
      when 2 then 
        convert(varchar, datepart(yy, min(b.VisitDate))) + 
        convert(varchar, datepart(ww, min(b.VisitDate))) + 'T'
      when 3 then convert(varchar, datepart(yy, min(b.VisitDate))) + 'T'
      end
    ,"LEVEL"
    = a.ID -1
    ,"PERIOD"
    = case a.ID
      when 1 then 
        case datepart(dw, min(b.VisitDate)) 
          when 1 then 'MON' when 2 then 'TUE' when 3 then 'WED' when 4 then 'THU' 
          when 5 then 'FRI' when 6 then 'SAT' when 7 then 'SUN' 
        end
        + ' ' + convert(varchar, datepart(dd, min(b.VisitDate))) + '/' + 
                convert(varchar, datepart(mm, min(b.VisitDate)))
      when 2 then 
        'TOTAL WEEK ' + convert(varchar, datepart(ww, min(b.VisitDate)))
      when 3 then 
        'TOTAL YEAR ' + convert(varchar, datepart(yy, min(b.VisitDate)))
      end
    ,"HOME"
    = sum(case when b.Page = 'Home'     then b.NbVisitors else 0 end)
    ,"CONTACT"
    = sum(case when b.Page = 'Contact'  then b.NbVisitors else 0 end)
    ,"PRODUCTS"
    = sum(case when b.Page = 'Products' then b.NbVisitors else 0 end)
from      @T a, @T b
where      a.ID <= 3
group by  
    a.ID
    , case a.ID
      when 1 then b.VisitDate
      when 2 then 
        dateadd(ww, datepart(ww, b.VisitDate), datepart(yy, b.VisitDate))
      when 3 then datepart(yy, b.VisitDate)
      end
order by "PERIOD_ID"

 

 

If you have questions or comments about this solution, discuss it on the dedicated forum.

If you have generic questions or comments about the TSQL Challenge 6 you can go to this forum.

Stay tunned and don't forget to check the current TSQL Challenge 9.

Congratulations Cleber for this nice solution and thanks for sharing it with us!


 Share

Related Posts

Aurelien and Matthieu have fun with TSQL Challenges

TSQL Challenge 6 - Solution by Arnaud Lhopiteau

TSQL Challenge 6 - Solution by Leonid Koyfman

TSQL Challenge 6 - Solution by Aurelien Verla

TSQL Challenge 6 Winners

TSQL Challenge 6

Copyright © Rivera Informatic Private Ltd.