CODEWITHSUNDEEP
sqlsql-server
user5021612
user5021612

Reputation:

How to find the highest value in a year and in all months

I want to return a year in which was the most contracts made and a month throughout all years - in which month the highest number of contracts is made.

I've tried:

SELECT
     cal.CalendarYear
    ,cal.MonthOfYear
    ,COUNT(*) AS Cnt
FROM dim.Application app
    JOIN dim.Calendar cal ON app.ApplicationDateID = cal.DateId
--WHERE (CalendarYear IS NULL) OR (MonthOfYear IS NULL)
GROUP BY
     cal.CalendarYear
    ,cal.MonthOfYear
WITH CUBE
ORDER BY COUNT(*) DESC

and...

--;WITH maxYear AS (
    SELECT TOP 1
        cal.CalendarYear AS [Year]
        ,0               AS [Month]
        ,COUNT(*) AS Cnt
    FROM dim.Application app
        JOIN dim.Calendar cal ON app.ApplicationDateID = cal.DateId
    GROUP BY cal.CalendarYear
--  ORDER BY COUNT(*) DESC
--)
UNION ALL
--,maxMonth AS (
    SELECT TOP 1
         0                      AS [Year]
        ,cal.MonthOfYear        AS [Month]
        ,COUNT(*)               AS Cnt
    FROM dim.Application app
        JOIN dim.Calendar cal ON app.ApplicationDateID = cal.DateID
    GROUP BY cal.MonthOfYear
    ORDER BY COUNT(*) DESC
--)

Any help would be appreciated. Thanks.

Upvotes: 0

Views: 112

Answers (3)

Marek Vitek
Marek Vitek

Reputation: 1633

EDIT: This might be what you want, unless you want it on single line:

select calendaryear AS 'year/month', cnt from (
SELECT TOP 1
     cal.CalendarYear
    ,COUNT(*) AS Cnt
FROM dim.Application AS app
    JOIN dim.Calendar AS cal ON app.ApplicationDateID = cal.DateId
GROUP BY
     cal.CalendarYear
ORDER BY COUNT(*) DESC
) as year
UNION ALL
select MonthOfYear, Cnt FROM ( 
SELECT TOP 1
     cal.CalendarYear
    ,cal.MonthOfYear
    ,COUNT(*) AS Cnt
FROM dim.Application AS app
    JOIN dim.Calendar AS cal ON app.ApplicationDateID = cal.DateId
GROUP BY
     cal.CalendarYear
    ,cal.MonthOfYear
ORDER BY COUNT(*) DESC
) AS month

It returns following result where month 3 is in fact 2016:

year/month  cnt
2017        4
3           2

I have used following data as input

create table #calendar (DateId int, calendaryear int, monthofyear int)
create table #application (applicationdateId int)

insert into #calendar values (1,2017,01)
insert into #calendar values (2,2017,02)
insert into #calendar values (3,2017,03)
insert into #calendar values (4,2016,01)
insert into #calendar values (5,2016,03)

insert into #application values (1)
insert into #application values (1)
insert into #application values (2)
insert into #application values (3)
insert into #application values (4)
insert into #application values (5)
insert into #application values (5)

Upvotes: 0

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Get the counts per year and month and use row_number to get the year and month with the highest contracts.

SELECT 
MAX(CASE WHEN year_rank=1 then Year END) as Highest_contracts_year,
MAX(CASE WHEN year_rank=1 then Year_count END) as Year_Contracts_count
MAX(CASE WHEN month_year_rank=1 then Month END) as Highest_contracts_Month,
MAX(CASE WHEN month_year_rank=1 then Month_Year_count END) as MonthYear_Contracts_count    
FROM (SELECT T.*
      ,ROW_NUMBER() OVER(ORDER BY Year_Cnt DESC) as Year_rank
      ,ROW_NUMBER() OVER(ORDER BY Month_Year_Cnt DESC) as Month_Year_rank
      FROM (SELECT 
            cal.CalendarYear AS [Year]
            ,cal.MonthOfYear AS [Month]
            ,COUNT(*) OVER(PARTITION BY cal.CalendarYear) AS Year_Cnt
            ,COUNT(*) OVER(PARTITION BY cal.MonthOfYear) AS Month_Year_Cnt
            FROM dim.Application app
            JOIN dim.Calendar cal ON app.ApplicationDateID = cal.DateId
           ) T
      ) T

You should specify what needs to be done when there are ties for highest counts. Assuming you need all highest count rows when there are ties, use

With ranks as 
         (SELECT T.*
          ,RANK() OVER(ORDER BY Year_Cnt DESC) as Year_rank
          ,RANK() OVER(ORDER BY Month_Year_Cnt DESC) as Month_Year_rank
          FROM (SELECT 
                cal.CalendarYear AS [Year]
                ,cal.MonthOfYear AS [Month]
                ,COUNT(*) OVER(PARTITION BY cal.CalendarYear) AS Year_Cnt
                ,COUNT(*) OVER(PARTITION BY cal.MonthOfYear) AS Month_Year_Cnt
                FROM dim.Application app
                JOIN dim.Calendar cal ON app.ApplicationDateID = cal.DateId
               ) T
          ) 
SELECT *
FROM (SELECT DISTINCT Year,Year_Cnt FROM RANKS WHERE Year_rank=1) ry
CROSS JOIN (SELECT DISTINCT Month,Month_Year_Cnt FROM RANKS WHERE Month_Year_rank=1) rm

Upvotes: 1

bastos.sergio
bastos.sergio

Reputation: 6764

This will ORDER BY each portion of the UNION independently, and still have the results joined in one SELECT...

SELECT x.* FROM (
    SELECT TOP 1
        cal.CalendarYear AS [Year]
        ,0               AS [Month]
        ,COUNT(*) AS Cnt
    FROM dim.Application app
        JOIN dim.Calendar cal ON app.ApplicationDateID = cal.DateId
    GROUP BY cal.CalendarYear
    ORDER BY COUNT(*) DESC
) x

UNION ALL

SELECT x.* FROM (
    SELECT TOP 1
         0                      AS [Year]
        ,cal.MonthOfYear        AS [Month]
        ,COUNT(*)               AS Cnt
    FROM dim.Application app
        JOIN dim.Calendar cal ON app.ApplicationDateID = cal.DateID
    GROUP BY cal.MonthOfYear
    ORDER BY COUNT(*) DESC
) x

Upvotes: 1

Related Questions