Sql Group By Multiple Clause-CodePudding

I have this data

UserId   EntryDate       OutDate
100       1.1.2022      3.1.2022
100       2.1.2022      4.1.2022  
101       3.1.2022         -
101       4.1.2022      4.1.2022  
101       5.1.2022      5.1.2022

and i need this output

DateInfo     EntryCount     OutCount
 01.2022         1             0
 02.2022         1             0
 03.2022         1             1
 04.2022         1             2 
 05.2022         1             1

I've used MM.dd.yyyy format as default mssql format. I grouped by with multiple columns but result is not correct. Could you help me about sql query

CodePudding user response：

We can use 2 queries UNION ALL to extract incoming and going and then COUNT() and GROUP BY to present the information.

create table EntryOut(
UserId  int, 
EntryDate  varchar(10),     
OutDate varchar(10));

insert into EntryOut values
(100,'1.1.2022','3.1.2022'),
(100,'2.1.2022','4.1.2022'),
(101,'3.1.2022',null),  
(101,'4.1.2022','4.1.2022'),  
(101,'5.1.2022','5.1.2022');

with movements as
(select EntryDate, UserID IDin, null IDout from EntryOut 
union all 
select OutDate, null, UserID from EntryOut )
select
  EntryDate,
  count(IDin) as In_,
  count(Idout) as Out_
from movements
where EntryDate is not null
group by EntryDate;

EntryDate | In_ | Out_
:-------- | --: | ---:
1.1.2022  |   1 |    0
2.1.2022  |   1 |    0
3.1.2022  |   1 |    1
4.1.2022  |   1 |    2
5.1.2022  |   1 |    1

db<>fiddle here

CodePudding user response：

You need a calendar table to get stat on every period. You can use a conditional aggregation then.

Creating a sample calendar table for 100 month entries

create table calendar (
   d       date not null,
   d_year  int  not null,
   d_month int  not null);
with t0(n) as (
 select n 
 from (
    values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10)
    ) t(n)
),ns as(
   select row_number() over(order by t1.n) - 1 n
   from t0 t1, t0 t2
)
insert calendar(d, d_year, d_month)
select DATEADD(month,n,'2022-01-01' ), year(DATEADD(month,n,'2022-01-01')), month( DATEADD(month,n,'2022-01-01'))
from ns;

The query, assuming the EntryDate and OutDate columns are of date type

select d_year, d_month,
    count(case when year(EntryDate) = d_year and month(EntryDate) = d_month then 1 end) EntryCnt,
    count(case when year(OutDate) = d_year and month(OutDate) = d_month then 1 end) OutCnt
from calendar
left join tbl on year(EntryDate) = d_year and month(EntryDate) = d_month 
               or year(OutDate) = d_year and month(OutDate) = d_month
group by d_year, d_month
order by d_year, d_month;

CodePudding user response：

I think following query may result what you want to do.

with table1 as (select UserID,EntryDate,OutDate from EntryTable),
table2 as (select count(UserID) as EntryCount, EntryDate  from table1 group 
by EntryDate),
table3 as (select count(UserID) as OutCount, EntryDate from table1 group by 
OutCount)
select table1.EntryDate,table2.EntryCount,table3.OutCount from table1
left join table2 on table2.EntryDate = table1.EntryDate
left join table3 on table3.EntryDate = table1.EntryDate

CodePudding user response：

First, as many of us say your date format is not correct. Second, you need to aggregate every month for checking if there is entry or not. Finally, you can sum up.

Try this:

WITH tab123
     AS (SELECT entrydate,
                outdate,
                CASE
                  WHEN entrydate IS NOT NULL THEN Dense_rank()
                                                    OVER(
                                                      partition BY entrydate
                                                      ORDER BY entrydate DESC)
                  ELSE NULL
                END e,
                CASE
                  WHEN outdate IS NOT NULL THEN Dense_rank()
                                                  OVER(
                                                    partition BY outdate
                                                    ORDER BY outdate DESC)
                  ELSE NULL
                END o
         FROM   test),
     entrytab
     AS (SELECT Concat(Concat(Month(entrydate), '.'), Year(entrydate)) tab1,
                Sum(e)                                                 e
         FROM   tab123
         GROUP  BY Concat(Concat(Month(entrydate), '.'), Year(entrydate))),
     outtab
     AS (SELECT Concat(Concat(Month(outdate), '.'), Year(outdate)) tab2,
                Sum(o)                                             o
         FROM   tab123
         GROUP  BY Concat(Concat(Month(outdate), '.'), Year(outdate)))
SELECT e.tab1,
       e.e,
       o.o
FROM   entrytab e
       FULL OUTER JOIN outtab o
                    ON e.tab1 = o.tab2
ORDER  BY 1;

DB Fiddle: