not able to show data for two SQL Commands

[draacor]

I am trying to a report that shows details about the surveys we send out for tickets that get created. The survey data is located on a specific table. I have created the following command to get this particular data as follows:

SELECT CAST(call_req.persid AS nvarchar(30)) AS persid, call_req.ref_num, ca_contact_1.last_name + N', ' + ca_contact_1.first_name AS Customer, DATEADD(hour,(select datediff(hour,getutcdate(),getdate())),DateAdd(second, call_req.open_date, '01-01-1970')) as Open_Date,
DATEADD(hour,(select datediff(hour,getutcdate(),getdate())),DateAdd(second, call_req.resolve_date, '01-01-1970')) as Resolve_date, survey.id AS survey_id, survey.description AS survey_description, survey.sym AS survey_sym,
survey.comment_label AS survey_comment_label, survey.nx_comment AS survey_nx_comment, survey.object_type AS survey_object_type,
survey.object_id AS survey_object_id, DATEADD(hour,(select datediff(hour,getutcdate(),getdate())),DateAdd(second, survey.last_mod_dt, '01-01-1970')) as survey_last_mod_dt, survey_answer.txt AS survey_answer_txt,
survey_answer.sequence AS survey_answer_sequence, survey_answer.selected AS survey_answer_selected,
survey_question.txt AS survey_question_txt, survey_question.sequence AS survey_question_sequence,
survey_question.qcomment_label AS survey_question_qcomment_label, survey_question.qcomment AS survey_question_qcomment,
ca_contact.last_name + N', ' + ca_contact.first_name AS Log_Agent, zemployer.name AS Employer, zemployer.id AS Employer_ID, ca_contact_2.Alternate_Identifier AS Employee_ID, ca_contact_3.last_name AS [Group],
call_req.summary, call_req.description, ca_contact.contact_uuid AS Log_Agent_ID
FROM survey INNER JOIN
survey_question ON survey.id = survey_question.owning_survey INNER JOIN
survey_answer ON survey_question.id = survey_answer.own_srvy_question LEFT OUTER JOIN
ca_contact RIGHT OUTER JOIN
ca_contact AS ca_contact_3 RIGHT OUTER JOIN
call_req ON ca_contact_3.contact_uuid = call_req.group_id LEFT OUTER JOIN
usp_contact LEFT OUTER JOIN
zemployer ON usp_contact.zemployer_id = zemployer.id RIGHT OUTER JOIN
ca_contact AS ca_contact_2 ON usp_contact.contact_uuid = ca_contact_2.contact_uuid ON call_req.customer = ca_contact_2.contact_uuid ON
ca_contact.contact_uuid = call_req.log_agent ON survey.object_id = call_req.id LEFT OUTER JOIN
ca_contact AS ca_contact_1 ON survey.last_mod_by = ca_contact_1.contact_uuid
WHERE (survey_answer.selected = 1) AND ((survey.last_mod_dt > (DATEDIFF(second,'1/1/1970',{?01StartDate}-84) + (datediff(second,getutcdate(),getdate()) - (datediff(second,getutcdate(),getdate()) * 2))))
and
(survey.last_mod_dt < (DATEDIFF(second,'1/1/1970',{?02EndDate}+1) + (datediff(second,getutcdate(),getdate()) - (datediff(second,getutcdate(),getdate()) * 2)))))


This Data gives me the details except for one thing. It doesnt tell me the number of surveys that were sent. So i created another command to get this data because it is stored in our activity log table.

select id, CAST(substring(call_req_id,4,len(call_req_id)) AS nvarchar(30)) AS Call_Req_ID, type from act_log where type in ('SRVY_SENT','SRVY_RCVD')

I then linked the persid of the first command to the call_req_id of the second command.

At first i tried combining this into one command but it was causing massive duplicates in the data as well as incorrect data at that.

When i link them i should be able to do a formula of:

if type = 'SRVY_SENT' then 1.

This should do a count but everytime i try it comes up blank. Hopefully someone has suggestions on how to fix this. I would greatly appreciate it. Let me know if you need more info too.

 

[beltmanjr]

Hi,
I suggest you first try combining the queries. Think about making the second one a subquery or the other way round.

Using 2 commands means crystal will pull down all data for both commands. Not very good for speed.

If the idea of combining the 2 queries doesn't go well, try using 1 query in a main report and the other in a subreport. Linking them on your common key.

Still, it seems best to only use one command for all.