locked
how to get the month wise max value in sql server RRS feed

  • Question

  • hi frnz,

     i have a table where i have column checkdate ,it having the records on the daily basis ,and month basis,.

    Here i will get the records ,max month wise and max date wise to display hw can i dispaly the dates with max dats.

    example: when i put the order by clasue it will gives the records like this below.

    but i want the records like 

    CheckDate                    Checkdate-Required format

    24 Jul 2014  
    20 Oct 2014
    17 Oct 2014
    16 Oct 2014                20 Oct 2014
    13 Oct 2014                13 Oct 2014
    07 Oct 2014                01 Aug 2014 
    01 Aug 2014               24 Jul 2014  

    can you pls help me out for this 

    Tanx!



    • Edited by Ychinnari Friday, October 24, 2014 7:52 AM
    Friday, October 24, 2014 7:19 AM

Answers

  • SELECT CheckDate
    FROM
    (
    SELECT CheckDate,
    MIN(CheckDate) OVER (PARTITION BY DATEDIFF(mm,0,CheckDate)) AS MinMonDate,
    MAX(CheckDate) OVER (PARTITION BY DATEDIFF(mm,0,CheckDate)) AS MaxMonDate
    FROM Table 
    )t
    WHERE CheckDate = MinMonDate
    OR CheckDate = MaxMonDate
    ORDER BY CheckDate DESC


    Please Mark This As Answer if it solved your issue Please Mark This As Helpful if it helps to solve your issue Visakh ---------------------------- http://social.technet.microsoft.com/wiki/contents/articles/27020.user-page-visakh16.aspx http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Friday, October 24, 2014 7:29 AM
    • Marked as answer by Ychinnari Friday, October 24, 2014 9:47 AM
    Friday, October 24, 2014 7:28 AM
  • Try it with this query:

    SELECT CONVERT(nvarchar, MAX(CheckDate), 106) AS MaxCheckDate
    FROM
       (SELECT CheckDate
         FROM         TABLE1
         UNION
        SELECT CheckDate
         FROM         TABLE2
         UNION
         SELECT CheckDate
         FROM         TABLE3
         UNION
         SELECT CheckDate
         FROM         TABLE4
         UNION
         SELECT CheckDate
         FROM         TABLE5
         UNION
         SELECT CheckDate
         FROM   TABLE6) AS SUB
    GROUP BY YEAR(CheckDate), Month(CheckDate)
    ORDER BY YEAR(CheckDate), Month(CheckDate)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Ychinnari Friday, October 24, 2014 9:47 AM
    Friday, October 24, 2014 8:13 AM
    Answerer
  • Hi guys ,

    Your giving the queer's correct way ,But i did nt get my required output.The above query written by SAZ it will give's the output but here is some problem ,It wont give the all the records it will gives only one record per month,But here i have few records for every month,and the ordery is giveing also wrong..

    • Marked as answer by Ychinnari Friday, October 24, 2014 9:47 AM
    Friday, October 24, 2014 8:38 AM

All replies

  • SELECT CheckDate
    FROM
    (
    SELECT CheckDate,
    MIN(CheckDate) OVER (PARTITION BY DATEDIFF(mm,0,CheckDate)) AS MinMonDate,
    MAX(CheckDate) OVER (PARTITION BY DATEDIFF(mm,0,CheckDate)) AS MaxMonDate
    FROM Table 
    )t
    WHERE CheckDate = MinMonDate
    OR CheckDate = MaxMonDate
    ORDER BY CheckDate DESC


    Please Mark This As Answer if it solved your issue Please Mark This As Helpful if it helps to solve your issue Visakh ---------------------------- http://social.technet.microsoft.com/wiki/contents/articles/27020.user-page-visakh16.aspx http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs


    • Edited by Visakh16MVP Friday, October 24, 2014 7:29 AM
    • Marked as answer by Ychinnari Friday, October 24, 2014 9:47 AM
    Friday, October 24, 2014 7:28 AM
  • SELECT     CONVERT(nvarchar, CheckDate, 106) AS CheckDate
    FROM         TABLE1
    UNION
    SELECT     CONVERT(nvarchar, CheckDate, 106) AS CheckDate
    FROM         TABLE2
    UNION
    SELECT     CONVERT(nvarchar, CheckDate, 106) AS CheckDate
    FROM         TABLE3
    UNION
    SELECT     CONVERT(nvarchar, CheckDate, 106) AS CheckDate
    FROM         TABLE4
    UNION
    SELECT     CONVERT(nvarchar, CheckDate, 106) AS CheckDate
    FROM         TABLE5
    UNION
    SELECT   CONVERT(nvarchar, CheckDate, 106) AS CheckDate
    FROM   TABLE6
    Order by  CheckDate Desc

    yout query is not giving the right output

    This is my query now you can give me the output as per my given requirement.

    Friday, October 24, 2014 7:50 AM
  • Try it with this query:

    SELECT CONVERT(nvarchar, MAX(CheckDate), 106) AS MaxCheckDate
    FROM
       (SELECT CheckDate
         FROM         TABLE1
         UNION
        SELECT CheckDate
         FROM         TABLE2
         UNION
         SELECT CheckDate
         FROM         TABLE3
         UNION
         SELECT CheckDate
         FROM         TABLE4
         UNION
         SELECT CheckDate
         FROM         TABLE5
         UNION
         SELECT CheckDate
         FROM   TABLE6) AS SUB
    GROUP BY YEAR(CheckDate), Month(CheckDate)
    ORDER BY YEAR(CheckDate), Month(CheckDate)


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Marked as answer by Ychinnari Friday, October 24, 2014 9:47 AM
    Friday, October 24, 2014 8:13 AM
    Answerer
  • Hi guys ,

    Your giving the queer's correct way ,But i did nt get my required output.The above query written by SAZ it will give's the output but here is some problem ,It wont give the all the records it will gives only one record per month,But here i have few records for every month,and the ordery is giveing also wrong..

    • Marked as answer by Ychinnari Friday, October 24, 2014 9:47 AM
    Friday, October 24, 2014 8:38 AM
  • I don't understand what you mean? Both the detailed date list + the max date in the same result at the end of the result list?

    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Friday, October 24, 2014 8:52 AM
    Answerer