Group By DatePart

Posted on 2007-07-31
Last Modified: 2008-09-08
I've got the following SP, my intention is to pull out all the unique minutes (distinct) and i don't want duplicates, but i'm getting them!

DECLARE @Language_IKey INT
SET @Language_IKey =2
SELECT CASE WHEN @Language_IKey=2 THEN Sector_A ELSE Sector_E END AS Sector,Max(ValueDate) ValueDate,Max(Last) Last,Max(LastW) LastW
FROM Feed_KSE_KARS_Indices
WHERE SectorID=0
GROUP BY ValueDate,Sector_A,Sector_E,DATEPART(MINUTE,ValueDate)
ORDER BY ValueDate
Question by:feesu
    LVL 8

    Expert Comment

    look at every column , it wont be exact duplicates.
    LVL 142

    Expert Comment

    by:Guy Hengel [angelIII / a3]
    please try this:

    DECLARE @Language_IKey INT
    SET @Language_IKey =2

    SELECT CASE WHEN @Language_IKey=2 THEN Sector_A ELSE Sector_E END AS Sector
    ,Max(ValueDate) ValueDate
    ,Max(Last) Last
    ,Max(LastW) LastW
    FROM Feed_KSE_KARS_Indices
    WHERE SectorID=0
    GROUP BY CASE WHEN @Language_IKey=2 THEN Sector_A ELSE Sector_E END , DATEPART(MINUTE,ValueDate)
    ORDER BY MAX(ValueDate)

    Author Comment

    I finally figured it out.
    I had to modify the grouping to:
    GROUP BY DATEPART(HOUR,ValueDate),Sector_A,Sector_E,DATEPART(MINUTE,ValueDate)

    It worked now.
    Thanks alot.
    LVL 1

    Accepted Solution

    PAQed with points refunded (250)

    EE Admin

    Featured Post

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    Let's review the features of new SQL Server 2012 (Denali CTP3). It listed as below: PERCENT_RANK(): PERCENT_RANK() function will returns the percentage value of rank of the values among its group. PERCENT_RANK() function value always in be…
    This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
    Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now