Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2011
    Posts
    27

    Unanswered: Tricky Logic using Group by

    Hi,

    Below is my Sample table, Data

    Code:
    create table #User(IdUser int primary key identity(1,1),UserCompanyId int,username varchar(50));
    
    insert into #User values(100,'Jhon'),(100,'Dean'),(101,'Smith'),(103,'Billy');
    
    create table #UserCompany(UserCompanyId int primary key identity(100,1),CompanyName varchar(50));
    
    insert into #UserCompany values('Dominos'),('PizzaHut'),('Papachinos'),('McD');
    
    Create table #Response(IdResponse int primary key identity(1,1),IdUser int,PageName varchar(30),PageStartDate datetime,PageEndDate datetime);
    
    
    
    Insert into #Response values(1,'Login','Mar 26 2014  6:18PM','Mar 26 2014  6:18PM'),
    (1,'InBound Report','2014-03-26 18:18:26.967','2014-03-26 18:18:30.040'),
    (1,'OutBound Report','2014-03-26 18:19:56.873','2014-03-26 18:19:57.077'),
    (1,'Invoice Report','2014-03-26 18:22:09.990','2014-03-26 18:22:10.770'),
    (1,'Monthly Report','2014-03-26 18:22:23.423','2014-03-26 18:22:28.587'),
    (2,'InBound Report','2014-03-26 18:24:18.257','2014-03-26 18:24:18.770'),
    (2,'OutBound Report','2014-03-26 18:24:22.780','2014-03-26 18:24:23.840'),
    (2,'Invoice Report','2014-03-26 18:26:13.813','2014-03-26 18:26:14.967'),
    (1,'InBound Report','2014-02-26 18:18:26.967','2014-02-26 18:18:30.040'),
    (1,'OutBound Report','2014-02-26 18:19:56.873','2014-02-26 18:19:57.077'),
    (1,'Invoice Report','2014-02-26 18:22:09.990','2014-02-26 18:22:10.770'),
    (1,'Monthly Report','2014-02-26 18:22:23.423','2014-02-26 18:22:28.587'),
    (2,'InBound Report','2014-02-26 18:24:18.257','2014-02-26 18:24:18.770'),
    (2,'OutBound Report','2014-02-26 18:24:22.780','2014-02-26 18:24:23.840'),
    (2,'Invoice Report','2014-02-26 18:26:13.813','2014-02-26 18:26:14.967'),
    (2,'Monthly Report','2014-02-26 18:26:24.810',	'2014-02-26 18:26:25.387'),
    (3,'Login','Mar 26 2014  6:18PM','Mar 26 2014  6:18PM'),
    (3,'InBound Report','2014-03-26 18:18:26.967','2014-03-26 18:18:30.040'),
    (3,'OutBound Report','2014-03-26 18:19:56.873','2014-03-26 18:19:57.077'),
    (3,'Invoice Report','2014-03-26 18:22:09.990','2014-03-26 18:22:10.770'),
    (3,'Monthly Report','2014-03-26 18:22:23.423','2014-03-26 18:22:28.587'),
    (3,'InBound Report','2014-03-26 18:24:18.257','2014-03-26 18:24:18.770'),
    (3,'OutBound Report','2014-03-26 18:24:22.780','2014-03-26 18:24:23.840'),
    (3,'Invoice Report','2014-03-26 18:26:13.813','2014-03-26 18:26:14.967');
    SQL logic

    Code:
    ;with data as (
    Select L.PageName,UC.CompanyName,Datediff(ms,PageStartDate,PageEndDate)/1000 [diff],DATENAME(month,PageStartDate)[Month] from #Response L join #User U 
    on(L.IdUser= U.IdUser) join #UserCompany UC on(U.UserCompanyId= UC.UserCompanyId) 
    
    )
    SELECT distinct PageName, [0-1]  = SUM(CASE WHEN diff >=0   AND diff <2 THEN 1 ELSE 0 END)
            ,[1-2] = SUM(CASE WHEN diff >1  AND diff <3 THEN 1 ELSE 0 END)
            ,[2-3]   = SUM(CASE WHEN diff >2 AND diff <4 THEN 1 ELSE 0 END)
            ,[3-4]   = SUM(CASE WHEN diff >3 AND diff <5 THEN 1 ELSE 0 END)
             ,[4-5]   = SUM(CASE WHEN diff > 4 aND diff <6 THEN 1 ELSE 0 END)
             ,[5-6]   = SUM(CASE WHEN diff > 5 aND diff <7 THEN 1 ELSE 0 END)
             ,[6-7]   = SUM(CASE WHEN diff > 6 aND diff <7 THEN 1 ELSE 0 END)
             ,[7+]   = SUM(CASE WHEN diff >= 7 THEN 1 Else 0 END),CompanyName,MONTH from data D        
              
            group by PageName,D.CompanyName,d.Month order by Month,CompanyName
    If i do pass the parameters for filter, here is my logic

    Code:
    Declare @year int = 2014, @month int = 3, @UserCompanyId int = 100
    
    ;with data as (
    Select L.PageName,UC.CompanyName,Datediff(ms,PageStartDate,PageEndDate)/1000 [diff],DATENAME(month,PageStartDate)[Month] from #Response L join #User U 
    on(L.IdUser= U.IdUser) join #UserCompany UC on(U.UserCompanyId= UC.UserCompanyId) 
    where  YEAR(PageStartDate) = @year and YEAR(PageEndDate) = @year and MONTH(PageStartDate) = @month and Uc.UserCompanyId = @UserCompanyId
    )
    SELECT distinct PageName, [0-1]  = SUM(CASE WHEN diff >=0   AND diff <2 THEN 1 ELSE 0 END)
            ,[1-2] = SUM(CASE WHEN diff >1  AND diff <3 THEN 1 ELSE 0 END)
            ,[2-3]   = SUM(CASE WHEN diff >2 AND diff <4 THEN 1 ELSE 0 END)
            ,[3-4]   = SUM(CASE WHEN diff >3 AND diff <5 THEN 1 ELSE 0 END)
             ,[4-5]   = SUM(CASE WHEN diff > 4 aND diff <6 THEN 1 ELSE 0 END)
             ,[5-6]   = SUM(CASE WHEN diff > 5 aND diff <7 THEN 1 ELSE 0 END)
             ,[6-7]   = SUM(CASE WHEN diff > 6 aND diff <7 THEN 1 ELSE 0 END)
             ,[7+]   = SUM(CASE WHEN diff >= 7 THEN 1 Else 0 END),CompanyName,MONTH from data D        
              
            group by PageName,D.CompanyName,d.Month order by Month,CompanyName
    I am storing the pageload and unload date and time for calcualting how much time it takes.

    Requirement:

    Step1: Get the time time difference in ms and convert into seconds and sum count falls in time range
    0-1 seconds
    1-2 seconds
    2-3 seconds
    3-4 seconds
    5-6 seconds
    6-7 seconds
    >= 7 seconds
    order by month and IDUser

    condition : i can pass the date and CompanyName as Paramenter to filter the data based on monthwise and companyName wise report

    The above query works fine. Just wondering is there any better way to achieve this. i am a learning guy and would like to better way always because Unknown is Ocean.

    Thanks in Advance to help on this post.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    It is good you have put the filter in the CTE. This limits the number of records it will return to be processed later. If you have an index on the PageStartDate field, however, the CTE will still generate a table scan. You may want to rewrite the where clause so that the PageStartDate field is not in the middle of a function (YEAR, MONTH). Assemble the date from the variables (using 01 for the day portion), and in the CTE have
    Code:
    declare @dt date
    declare @year char(4) = '2014', 
    	@month char(2) = '03'
    
    set @dt = @year + '-' + @month + '-01'
    
    ....(snip)....
    
    where  PageStartDate > @dt and PageStartDate < dateadd(mm, 1, @dt)
      and PageEndDate > @dt and PageEndDate < dateadd(mm, 1, @dt)
      and Uc.UserCompanyId = @UserCompanyId
    Have you given any thought as to what happens to requests that begin at 23:59:59 on 2/28/2014, and end at 00:00:01 on 3/1/2014? Does it matter much in your case?
    Last edited by MCrowley; 04-18-14 at 14:25. Reason: Fixed a typo

  3. #3
    Join Date
    Apr 2011
    Posts
    27
    Hi MCrowley,

    You are correct. I need to think about it. It does matter in my case. Any suggestions or sample please

  4. #4
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,799
    Provided Answers: 11
    If they matter, I would probably just comment out the conditions on the PageEndDate, and only use those for the calcuation, as opposed to the filter.

  5. #5
    Join Date
    Apr 2011
    Posts
    27
    Hi Mcrowly,

    Thanks for your time on this.

    How about having the @dt between start and enddate condition

  6. #6
    Join Date
    Apr 2011
    Posts
    27
    Hi Celko,

    Thanks for the reply and this is how i achieved my requirement.

    Code:
    SELECT 
        PageName, [0-1], [1-2], [2-3], [3-4], [4-5], [5-6], [6-7], [7+], 
        CompanyName, DATENAME(MONTH, [Month#]) AS [Month]
    FROM (
        SELECT
            L.PageName
                ,[0-1] = SUM(CASE WHEN diff BETWEEN 0 AND 1 THEN 1 ELSE 0 END)
                ,[1-2] = SUM(CASE WHEN diff BETWEEN 1 AND 2 THEN 1 ELSE 0 END)
                ,[2-3] = SUM(CASE WHEN diff BETWEEN 2 AND 3 THEN 1 ELSE 0 END)
                ,[3-4] = SUM(CASE WHEN diff BETWEEN 3 AND 4 THEN 1 ELSE 0 END)
                ,[4-5] = SUM(CASE WHEN diff BETWEEN 4 AND 5 THEN 1 ELSE 0 END)
                ,[5-6] = SUM(CASE WHEN diff BETWEEN 5 AND 6 THEN 1 ELSE 0 END)
                ,[6-7] = SUM(CASE WHEN diff BETWEEN 6 AND 7 THEN 1 ELSE 0 END)
                ,[7+]  = SUM(CASE WHEN diff >= 7 THEN 1 Else 0 END),
            UC.CompanyName, DATEADD(MONTH, DATEDIFF(MONTH, 0, L.PageStartDate), 0) AS [Month#]
        FROM #Response L 
        INNER JOIN #User U on (L.IdUser= U.IdUser) 
        INNER JOIN #UserCompany UC on (U.UserCompanyId= UC.UserCompanyId)
        CROSS APPLY (
            SELECT Datediff(ms,PageStartDate,PageEndDate)/1000 AS [diff]
        ) AS ca1
        WHERE
            L.PageStartDate >= DATEADD(MONTH, @month - 1, CAST(CAST(@year AS CHAR(4)) + '0101' AS datetime)) AND
            L.PageStartDate < DATEADD(MONTH, @month, CAST(CAST(@year AS CHAR(4)) + '0101' AS datetime)) AND
            UC.UserCompanyId = @UserCompanyId
        GROUP BY
            UC.CompanyName, DATEADD(MONTH, DATEDIFF(MONTH, 0, L.PageStartDate), 0), L.PageName
            --PageName, UC.CompanyName, DATEADD(MONTH, DATEDIFF(MONTH, 0, L.PageStartDate), 0) 
    ) AS derived
    ORDER BY
        CompanyName, [Month#]

  7. #7
    Join Date
    Jan 2013
    Posts
    354
    Provided Answers: 1
    SUM(CASE WHEN diff BETWEEN 0 AND 1 THEN 1 ELSE 0 END) AS "[0-1]",
    SUM(CASE WHEN diff BETWEEN 1 AND 2 THEN 1 ELSE 0 END) AS "[1-2]",
    I had to re-write this fragment into ANSI/ISO Standard SQL. I would also have used "page_delta" instead of "diff" for a name. I would not kludge a report and return a non-normalized query result, treat temporal data as strings (COBOL anyone?), replaced the procedural computations with a timeslot table, etc.

    But what happens when diff =1? It falls into two buckets! You are doing integer math. Is that what you want?

  8. #8
    Join Date
    Apr 2011
    Posts
    27
    Yes Ofcourse. Can you post your modified logic please

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •