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
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) ?
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!
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.
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!
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.
(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.
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.