Crystal Reports Group by EITHER of two date fields

Report Design
Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design

Topic: Group by EITHER of two date fields
	<< Prev Page of 3

Author	Message
DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 03 Mar 2014 at 9:08am
	Yes if you are running two reports and only counting open in one and closed in the other your RT's reset on change of group (if set per respective month). I thought you were still trying to get all of the numbers in this single report.
	IP Logged

martinz Newbie Joined: 27 Feb 2014 Online Status: Offline Posts: 12	Posted: 03 Mar 2014 at 9:52am
	I am, I think the thread split into different topics. So what should I be looking at to pull in these records as two different records (open and close). Or could I even connect to the DB with two different connections and link the ticket number? Which I think is where you were pointing me - to Command Connections at the same DB? Again thanks! Edited by martinz - 03 Mar 2014 at 10:01am
	IP Logged

DBlank Moderator Joined: 19 Dec 2008 Online Status: Offline Posts: 9053	Posted: 04 Mar 2014 at 4:08am
	I again would just use a command with a union in it. You can use paramteters in the command if you need to set the date ranges at run time. This will basically just be two SQL select statments with a UNION between them. Make sure you set theorder the columns inteh same way in each statement part. Something like Select 'Open' as combinedType,opendate as CombinedDate, ticketnumber, ticketType, etc. from TicketsTable where opendate is between x and y UNION Select 'Closed' as combinedType,closedate as CombinedDate, ticketnumber, ticketType, etc. from TicketsTable where closedate is between x and y Edited by DBlank - 04 Mar 2014 at 4:11am
	IP Logged

martinz Newbie Joined: 27 Feb 2014 Online Status: Offline Posts: 12	Posted: 04 Mar 2014 at 4:56am
	I think I finally understand Thanks, I'll post back just for the record...
	IP Logged

martinz Newbie Joined: 27 Feb 2014 Online Status: Offline Posts: 12	Posted: 05 Mar 2014 at 2:58am
	Well I thought I understood but DBLANK worked it out for me! I'm posting the SQL command statement here in case it might help another down the road. Thank you to all for helping this newbie and to DBlank for taking time to look at my missed shot and correcting the aim! SELECT 'Open' as CombinedType ,"Work_Orders"."Open Date & Time" as CombinedDate ,"Work_Orders"."Open Date & Time" , "Work_Orders"."Work Order #" , "Work_Orders"."Close Date & Time" , "Work_Orders"."Open Date" , "Work_Orders"."Close Date" , "Work_Orders"."Group Name" , "Work_Orders"."Work Order Type" , "Work_Orders"."State:" as combinedState , "Work_Orders"."Incident Description" FROM "MyDB"."MyOffice"."Work Orders" "Work_Orders" WHERE "Work_Orders"."Group Name"=N'MyOffice' UNION SELECT 'Closed' as CombinedType ,"Work_Orders"."Close Date & Time" as CombinedDate ,"Work_Orders"."Open Date & Time" , "Work_Orders"."Work Order #" , "Work_Orders"."Close Date & Time" , "Work_Orders"."Open Date" , "Work_Orders"."Close Date" , "Work_Orders"."Group Name" , "Work_Orders"."Work Order Type" , "Work_Orders"."State:" as combinedState , "Work_Orders"."Incident Description" FROM "MyDB"."MyOffice"."Work Orders" "Work_Orders" WHERE "Work_Orders"."Group Name"=N'MyOffice' AND "Work_Orders"."Close Date" IS NOT NULL
	IP Logged

<< Prev Page of 3

Post Reply

Post New Topic

Printable version

Printable version

Forum Jump

You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

This page was generated in 0.016 seconds.