picking the last data in a table

[draacor]

I have a table that i want to display on a report, it is the description of a ticket. So it would be a comment someone would add to a ticket. When i put the field in the report it looks like it is just grabbing at random one of the entries. For example, say i want to report on a ticket. That ticket has 3 entries on it with comments. I want to be able to pick the latest entry, the most recent. But for some reason it just picks one of the entries at random. is there a formula that looks at the table and just picks the last one in the table that is related to that ticket?

 

[lbass]

Is there a date field or another field that is sequential so that you can identify which is the most recent ticket? Please provide actual table and field names, as in {table.field}.

-LB

 

[draacor]

I do have a timestamp field of when the comment was entered but i dont want to show that in the report. So here is a sample data:

command.ticketnumber command.summary last comment

So the above will show the ticket number which i have to group because each comment has the same ticket so i want to group it so it only shows once. Then to the right i have a bunch of data for each ticket, like type, customer, group, assignee, etc. At the end i want the last comment on the ticket, which would be {command.comment}. There is a {command.timestamp} that puts in the time of when that comment was made. I want it to display only the last comment. Hopefully this is more clear.

 

[lbass]

If you are using a command as your datasource, then build the criterion into your command, as in:

Select table.`comment`, table.`ticketnumber`, table.`summary`
From table
where table.`timestamp` =
(
select max(A.`timestamp`)
from table A
where A.`ticketnumber` = table.`ticketnumber`
)

This would return the most recent record per ticketnumber.

-LB

 

[draacor]

If i didnt want to put it into the command would there be a crystal report formula that would return the same results?

 

[draacor]

Or if it can only be done using a command here is my command and hopefully it makes a little sense hehe:

SELECT call_req.ref_num AS ticket_num, act_log.time_stamp, CAST(act_log.description AS varchar(4000)) AS act_description, ca_contact.last_name + N' ' + ca_contact.first_name AS Analyst_Name, act_type.sym AS act_Type, ca_contact.pri_phone_number AS Phone_Number, CAST(call_req.description AS varchar(8000)) AS Description, call_req.zextticket AS Partner_ticket, ca_contact.last_name + N', ' + ca_contact.first_name AS customer, call_req.active_flag, zemployer.name AS Company, call_req.[type] AS [type], attached_sla.sla_viol_status as violated, cr_stat.sym AS status, 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, attached_sla.time_to_violation, '01-01-1970')) as projected_violation_date, DATEADD(hour,(select datediff(hour,getutcdate(),getdate())),DateAdd(second, call_req.resolve_date, '01-01-1970')) as resolve_date, DATEADD(hour,(select datediff(hour,getutcdate(),getdate())),DateAdd(second, call_req.close_date, '01-01-1970')) as close_date, pri.sym AS priority, prob_ctg.sym AS category, call_req.category as category_id,
tblAssignee.last_name + N', ' + tblAssignee.first_name AS assignee, tblGroup.last_name AS [group], call_req.summary
FROM act_type RIGHT OUTER JOIN
ca_contact RIGHT OUTER JOIN
call_req INNER JOIN
act_log ON call_req.persid = act_log.call_req_id ON ca_contact.contact_uuid = act_log.analyst ON act_type.code = act_log.type LEFT OUTER JOIN
usp_contact LEFT OUTER JOIN
zemployer ON usp_contact.zemployer_id = zemployer.id RIGHT OUTER JOIN
ca_contact AS ca_contact_1 ON usp_contact.contact_uuid = ca_contact_1.contact_uuid ON
call_req.customer = ca_contact_1.contact_uuid LEFT OUTER JOIN
ca_contact AS tblGroup ON call_req.group_id = tblGroup.contact_uuid LEFT OUTER JOIN
ca_contact AS tblAssignee ON call_req.assignee = tblAssignee.contact_uuid LEFT OUTER JOIN
prob_ctg ON call_req.category = prob_ctg.persid LEFT OUTER JOIN
pri ON call_req.priority = pri.enum LEFT OUTER JOIN
attached_sla ON call_req.persid = attached_sla.mapped_cr LEFT OUTER JOIN
cr_stat ON call_req.status = cr_stat.code
WHERE (call_req.template_name is null) and (call_req.active_flag = 1) and (cr_stat.sym = 'Hold-Escalated to Vendor')
UNION
SELECT chg.chg_ref_num AS ticket_num, chgalg.time_stamp, CAST(chgalg.description AS varchar(4000)) AS act_description, ca_contact.last_name + N' ' + ca_contact.first_name AS Analyst_Name, act_type.sym AS act_Type, ca_contact.pri_phone_number AS Phone_Number, CAST(chg.description AS varchar(8000)) AS Description, 'NULL' AS Partner_ticket, ca_contact.last_name + N', ' + ca_contact.first_name AS customer, chg.active_flag, zemployer.name AS Company, 'C' AS [type], attached_sla.sla_viol_status as violated, chgstat.sym AS status, DATEADD(hour,(select datediff(hour,getutcdate(),getdate())),DateAdd(second, chg.open_date, '01-01-1970')) AS open_date, DATEADD(hour,(select datediff(hour,getutcdate(),getdate())),DateAdd(second, attached_sla.time_to_violation, '01-01-1970')) as projected_violation_date, DATEADD(hour,(select datediff(hour,getutcdate(),getdate())),DateAdd(second, chg.resolve_date, '01-01-1970')) as resolve_date, DATEADD(hour,(select datediff(hour,getutcdate(),getdate())),DateAdd(second, chg.close_date, '01-01-1970')) as close_date, pri.sym AS priority, chgcat.sym AS category, chg.category as category_id,
tblAssignee.last_name + N', ' + tblAssignee.first_name AS assignee, tblGroup.last_name AS [group], chg.summary
FROM act_type RIGHT OUTER JOIN
attached_sla RIGHT OUTER JOIN
ca_contact AS tblAssignee RIGHT OUTER JOIN
chg INNER JOIN
chgalg ON chg.id = chgalg.change_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_1 ON usp_contact.contact_uuid = ca_contact_1.contact_uuid ON
chg.affected_contact = ca_contact_1.contact_uuid LEFT OUTER JOIN
ca_contact AS tblGroup ON chg.group_id = tblGroup.contact_uuid ON tblAssignee.contact_uuid = chg.assignee LEFT OUTER JOIN
chgcat ON chg.category = chgcat.code LEFT OUTER JOIN
pri ON chg.priority = pri.enum LEFT OUTER JOIN
chgstat ON chg.status = chgstat.code ON attached_sla.mapped_chg = chg.id LEFT OUTER JOIN
ca_contact ON chgalg.analyst = ca_contact.contact_uuid ON act_type.code = chgalg.type
WHERE (chg.template_name is null) and (chg.active_flag = 1) and (chgstat.sym = 'Hold-Escalated to Vendor')

 

[lbass]

I guess you didn't write the command? A simpler way of doing it would be to insert a group on ticket number, and then go to report->selection formulas->GROUP and enter:

{command.timestamp} = maximum({command.timestamp},{command.ticketnumber})

...substituting in the correct field names. Then you would need to use running totals, if you intend to do any calculations across ticket groups, since the non-group selected records would still contribute to the more usual inserted summaries.

-LB

 

[draacor]

Cool that fixed it thanks for your help. Actually i did create the command but i used the query designer and am still learning all the ins and outs of SQL and such so a lot of things are not as clear to me as they should be had i written it without the designer