Case when using COUNT

Hello,

I currently have a field called "EmailOut" which shows a 1 or 0 as to whether there are comms between two dates however I want to show the number of comms, I can't work out how to adapt my query to count the occurences.

Does anyone know? Thanks for your help!

With Enquiry_date as
(select crms_number, date_created
from dbo.ZZ_crms_student
where client_id = 'huk_XXX'),

Application_date as
(select crms_number, date_modified,
ROW_NUMBER() over (partition by crms_number
order by crms_number, date_modified) Seq
from dbo.ZZ_crms_student_status_history
where client_id = 'huk_XXX' and (student_status = 'Applied' or student_status = 'Application Incomplete'))

select client_id + ' ' + convert(varchar, s.crms_number) as [Unique ID], s.crms_number, student_status
, case when s.crms_number in (select crms_number from dbo.ZZ_crms_student_status_history
where client_id = 'XXX' and student_status = 'Enquiry') then 1 else 0 end as Enquiry
, convert(date, es.date_created) Enq_date
, case when s.crms_number in (select crms_number from dbo.ZZ_crms_student_status_history
where client_id = 'XXX' and (student_status = 'Applied' or student_status = 'Application Incomplete')) then 1 else 0 end as Applied
, convert(date, ap.date_modified) App_date
, case when s.crms_number in (select crms_number from dbo.ZZ_crms_communication
where client_id = 'XXX' and class = 'email' and in_out = 'out' and
convert(date, date_opened) >= convert(date, es.date_created) and
convert(date, date_opened) <= convert(date, ap.date_modified)
group by crms_number) then 1 else 0 end as EmailOut
from dbo.ZZ_crms_student s
left join Enquiry_date es on es.crms_number = s.crms_number
left join Application_date ap on ap.crms_number = s.crms_number and ap.Seq = 1
where client_id = 'XXX' and dead = 0

Could you post some sample data?

Hello Graz,

I tried to upload an Excel file but I can't. The comms table looks like this:

the student status table looks like this:

then I also need the student table to get the enquiry date:

Is that helpful?

What are Comms? And I'm not understanding why that's called EMAILOUT?

Sorry for not answering but it may help for understanding.

Sorry for not being clearer, comms are communications and there are various types of communications e.g. email in, email out, phone in etc.

This query looks to count each type of communication between two dates and these two dates vary by each record.

Does that help? Thanks for trying to advise, I am very stuck!

With Enquiry_date as
	(select crms_number, date_created
	 from dbo.ZZ_crms_student
	where client_id = 'huk_XXX'),

 Application_date as
	(select crms_number, date_modified,
	ROW_NUMBER() over (partition by crms_number
	order by crms_number, date_modified) Seq
	 from dbo.ZZ_crms_student_status_history
	where client_id = 'huk_XXX' and (student_status = 'Applied' or student_status = 'Application Incomplete'))

 select client_id + ' ' + convert(varchar, s.crms_number) as [Unique ID], s.crms_number, student_status
 , case when s.crms_number in (select crms_number from dbo.ZZ_crms_student_status_history
	where client_id = 'XXX' and student_status = 'Enquiry') then 1 else 0 end as Enquiry
 , convert(date, es.date_created) Enq_date
 , case when s.crms_number in (select crms_number from dbo.ZZ_crms_student_status_history
	where client_id = 'XXX' and (student_status = 'Applied' or student_status = 'Application Incomplete')) then 1 else 0 end as Applied
 , convert(date, ap.date_modified) App_date
 , case when s.crms_number in (select crms_number from dbo.ZZ_crms_communication
	where client_id = 'XXX' and class = 'email' and in_out = 'out' and
	convert(date, date_opened) >= convert(date, es.date_created) and 
	convert(date, date_opened) <= convert(date, ap.date_modified)
	group by crms_number) then 1 else 0 end as EmailOut
 from dbo.ZZ_crms_student s
	 left join Enquiry_date es on es.crms_number = s.crms_number
	 left join Application_date ap on ap.crms_number = s.crms_number and ap.Seq = 1
 where client_id = 'XXX' and dead = 0

Part of this is it was hard to read. But maybe that wasn't your fault, the format button makes it clearer now!

instead of 
  case when s.crms_number in (select crms_number from dbo.ZZ_crms_communication...
can you do 
  (select count (*) from ... ) as EmailOut` (no group by) ?

Hello,

Thanks for this suggestion but the reason this doesn't work is that I want to show the result to relate to each record row rather than all records which is currently what that syntax would provide.

If I use this methodology at the moment, I get the same result for several records where the dates are the same.

Hoping that you understand and there is another way!

Thanks

Sorry, I'm having trouble understanding that. That sounds like in one row, there are 2 dates and you want to count the occurrences between those 2 dates. But in your sample data, you show only one date per table.

Hello,

Each record will have an es.date_created (Enquiry Date) and an ap.date_modified (Application Date) and each communication has a date_opened (Date Sent) so I want to count the number of communications which were sent between the Enquiry Date and the Application Date for each record (these dates will differ for each record).

The Enquiry Date, Application Date and Date Sent all come from different tables so it is quite complicated!

Thanks in advance

Maybe I understand, but I'm not sure. By posting your entire query, it's not easy. It is hard to tell what is relevant to your challenge.

Is this in the right direction?

select *
into #Enquiry_date
from
(select 8 crms_number, cast('2017-1-1' as datetime) date_created union all
 select 101 crms_number, '2017-1-2' date_created ) x

select *
into #Application_date
from
(select 8 crms_number, cast('2017-1-10' as datetime) date_modified union all
 select 101 crms_number, '2017-1-9' date_modified ) x

select *
into #ZZ_crms_communication
from
(select 8 crms_number, cast('2017-1-3' as datetime) date_opened , 'Email' class, 'OUT' in_out union all
select 8 crms_number, '2017-1-4' date_opened , 'Email' class, 'OUT' in_out union all
select 8 crms_number, '2017-1-15' date_opened , 'Email' class, 'OUT' in_out union all
select 101 crms_number, '2017-1-7' date_opened , 'Email' class, 'OUT' in_out ) x

select *
into #ZZ_crms_student
from
(select 8 crms_number union all select 101 crms_number) x

select S.*, date_created, date_modified,
	(select count(crms_number) from #ZZ_crms_communication c
		where class = 'email' and in_out = 'out' and
		c.crms_number = s.crms_number and
		date_opened >= es.date_created and 
		date_opened <= ap.date_modified) as EmailOut 
 from #ZZ_crms_student s
	 left join #Enquiry_date es on es.crms_number = s.crms_number
	 left join #Application_date ap on ap.crms_number = s.crms_number

go
drop table #ZZ_crms_communication
drop table #Application_date
drop table #Enquiry_date
drop table #ZZ_crms_student

I am sorry that I haven't been clear, maybe this is too complicated for a forum post. I believe the idea is quite simple though, I need to summarise the number of records from one table (comms) which occur between two dates coming from two other tables.

In this example, would I need to manually write in each date? This solution wouldn't be feasible as there are many records and this needs to be automated to run weekly.

Thanks for your help though

Sorry, I thought I had a solution.

In what way is this not that?

	(select count(crms_number) from #ZZ_crms_communication c
		where class = 'email' and in_out = 'out' and
		c.crms_number = s.crms_number and
		date_opened >= es.date_created and 
		date_opened <= ap.date_modified) as EmailOut 

Do you mean those inserts? Of course not. This is just so it's something we can run, an actual example.

Thanks for your help, Denis.

Unfortunately, I don't understand how to try and make what you have given me work. I don't know how to automate the insertion of those crms_numbers or dates.

Uganda - You don't need to automate the inserts. Like I told you, this is just an EXAMPLE. To show you the query does what YOU DESCRIBED.

Hopefully someone else can have a look at this...