How to count rows given a result set but subtotal by column

Question

Good morning!

Please forgive my English.

I have the nex "simplified" table (Simplified because I'm using a Calendar table)

DECLARE @Table AS TABLE
(
 "Date" "date" NOT NULL,
 "Day" "tinyint" NOT NULL,
 "Month" "tinyint" NOT NULL,
 "Q" "tinyint" NOT NULL,
 "Year" "smallint" NOT NULL
)

INSERT INTO @Table
(
 "Date",
 "Day",
 "Month",
 "Q",
 "Year"
)
VALUES
('2022-01-13', 13, 1, 1, 2022),
('2022-01-13', 13, 1, 1, 2022),
('2022-01-14', 14, 1, 1, 2022),
('2022-01-14', 14, 1, 1, 2022),
('2022-01-14', 14, 1, 1, 2022),
('2022-02-21', 21, 2, 1, 2022),
('2022-02-22', 22, 2, 1, 2022),
('2022-02-22', 22, 2, 1, 2022),
('2022-02-23', 23, 2, 1, 2022),
('2022-02-23', 23, 2, 1, 2022),
('2022-02-23', 23, 2, 1, 2022),
('2022-02-24', 24, 2, 1, 2022),
('2022-02-24', 24, 2, 1, 2022),
('2022-02-24', 24, 2, 1, 2022),
('2022-02-24', 24, 2, 1, 2022),
('2022-06-10', 10, 6, 2, 2022);

SELECT
 *
FROM
 @Table AS "t";

Now, given a Date, I need to count how many rows there are in the same day, month, quarter and year. Something like this:

/*Desired result (Given a Date = '2022-02-24') Returns how many rows there are for each column.
--------------------
|Type |Quantity|
--------------------
|Day  |       4|  ---> In '2022-02-24' there are 4 rows.
|Month|      10|  ---> In February ('2022-02-24' belongs to February) there are 10 rows.
|Q    |      15|  ---> In First Quarter ('2022-02-24' belongs to firts quarter) there are 15 rows.
|Year |      16|  ---> In Year 2022 ('2022-02-24' belongs to year 2022) there are 16 rows.
*/

I tryied using GROUP BY, and clause OVER, and using ROLLUP and PIVOT, but I can't do it work as I want.

Thanks a lot for your valuable help.

Answer 1

Sorry, I misread your question. Try
Declare @apl date;
Set @apl = '20220114'
;With cte As
(Select Count(Case When Date = @apl Then 1 End) Over () As DayCount,
Count(Case When Month(Date) = Month(@apl ) And Year(@apl ) = Year(Date) Then 1 End) Over () As MonthCount,
Count(Case When (Month(Date)+2)/3 = (Month(@apl )+2)/3 And Year(@apl ) = Year(Date) Then 1 End) Over () As QtrCount,
Count(Case When Year(Date) = Year(@apl ) Then 1 End) Over () As YearCount
From @Table)
Select Type, Quantity
From (Select
Max(DayCount) As Day,
Max(MonthCount) As Month,
Max(QtrCount) As Q,
Max(YearCount) As Year
From cte) p
Unpivot (Quantity For Type In ([Day], [Month], [Q], [Year])) As Unpvt;

Tom

Answer 2

I did it!

DECLARE @Date AS "date" = '2022-02-24';
WITH "Data"
(
 "Date",
 "Day",
 "Month",
 "Q",
 "Year"
) AS
(
 SELECT DISTINCT
 "Date",
 COUNT(1) OVER(PARTITION BY "Day") AS "Day",
 COUNT(1) OVER(PARTITION BY "Month") AS "Month",
 COUNT(1) OVER(PARTITION BY "Q") AS "Q",
 COUNT(1) OVER(PARTITION BY "Year") AS "Year"
 FROM
 @Table AS "t"
 WHERE
 "Year" = YEAR(@Date)
), "Row"
(
 "Day",
 "Month",
 "Q",
 "Year"
) AS
(
 SELECT
 "Day",
 "Month",
 "Q",
 "Year"
 FROM
 "Data" AS "d"
 WHERE
 "d"."Date" = @Date
)
SELECT
 "Type",
 "Quantity"
FROM
 "Row" AS "d"
UNPIVOT
(
 "Quantity" FOR "Type" IN
 (
 "Day",
 "Month",
 "Q",
 "Year"
 )
) AS "End";

Is there an easier way?

Answer 3

One way

With cte As 
(Select Count(*) Over (Partition By Date) As DayCount,
  Count(*) Over (Partition By Month(Date)) As MonthCount,
  Count(*) Over (Partition By (Month(Date)+2)/3) As QtrCount,
  Count(*) Over (Partition By Year(Date)) As YearCount
From @Table)
Select Type, Quantity
From (Select
  Max(DayCount) As Day,
  Max(MonthCount) As Month, 
  Max(QtrCount) As Q, 
  Max(YearCount) As Year
From cte) p
Unpivot (Quantity For Type In ([Day], [Month], [Q], [Year])) As Unpvt;

Tom

Answer 4

Maybe this is easier:

select 'Day' as [Type], count(*) as Quantity
from @Table where [Date] = @Date
union all
select 'Month', count(*)
from @Table where [Year] = year(@Date) and [Month] = month(@Date)
union all
select 'Quarter', count(*)
from @Table where [Year] = year(@Date) and ([Month]-1) / 3 = (month(@Date)-1) / 3
union all
select 'Year', count(*)
from @Table where [Year] = year(@Date)