How can separate morning and evening shifts patients with gender wise using SQL store procedure-CodePudding

I want to separate the morning and evening patient checked-in count gender-wise and also if gender age less then show the Female child and M child on the basis of gender.

SELECT count(ch.EnteredOn) AS counter, convert(date, ch.EnteredOn) AS sessionDay
,sum(CASE WHEN CAST( CONVERT(CHAR(2), ch.EnteredOn, 108) AS INT) <12 THEN 1 ELSE 0 end ) as 'Morninig'
,sum(case when CAST( CONVERT(CHAR(2), ch.EnteredOn, 108) AS INT) >=12 THEN 1 ELSE 0 end) as 'Evening'
,sum(case when p.Gender=1 Then 1 ELSE 0 end) as Male
,sum(case when p.Gender=2 Then 1 ELSE 0 end) as Fmale
,Sum( case when DATEDIFF(hour,P.DOB,GETDATE())/8766<=18 AND P.Gender=1 Then 1 ELSE 0 end ) as MChiled
,Sum( case when DATEDIFF(hour,P.DOB,GETDATE())/8766<=18 AND P.Gender=2 Then 1 ELSE 0 end ) as FChiled
FROM Patient.CheckIn ch

inner join Patient.Patients P on ch.PatientId=p.PatientId Group by Ch.EnteredOn

I have only three fields like Gender, Time, and DOB. Gender id 1 shows the male and Gender id 2 is using for females. enter image description here

CodePudding user response：

SELECT convert(date, ch.EnteredOn) AS sessionDay, CAST( CONVERT(CHAR(2), ch.EnteredOn, 108) AS INT) <12 as morning, count(ch.EnteredOn) AS counter
,sum(case when p.Gender=1 Then 1 ELSE 0 end) as Male
,sum(case when p.Gender=2 Then 1 ELSE 0 end) as Fmale
,Sum( case when DATEDIFF(hour,P.DOB,GETDATE())/8766<=18 AND P.Gender=1 Then 1 ELSE 0 end ) as MChiled
,Sum( case when DATEDIFF(hour,P.DOB,GETDATE())/8766<=18 AND P.Gender=2 Then 1 ELSE 0 end ) as FChiled
FROM Patient.CheckIn ch
inner join Patient.Patients P on ch.PatientId=p.PatientId 
Group by Ch.EnteredOn

Group BY can be

Group by convert(date, ch.EnteredOn) AS sessionDay, CAST( CONVERT(CHAR(2), ch.EnteredOn, 108) AS INT) <12