Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Timeline - created and update date

Status
Not open for further replies.

ErnstNoto

Programmer
Dec 20, 2009
48
SE
Hi

I have a data table which for example have the following fields.

Id; Reg.date; Duedate; User ID; Case nbr; Update date
746056; 2015-02-10; 2015-03-12; IMA; E28896; 2015-03-13
746060; 2015-02-11; 2010-02-08; DK; C55411; 2015-03-13
746066; 2015-02-17; 2010-02-08; TBE; D88396; 2015-03-18

From this I would like to create a report that will show the nbr of new cases per year/week, and how many cases have been updated per year/week.
From the data above I would like to present this as following:

Year/Week; NewCase;Updated case
2015/7; 2; 0
2015/8; 1; 0
Etc…
2015/11; 0; 2
2015/12; 0; 1

The problem I'm faced with is to Place a Count of two different colums on the same line in two different Places in a report. I need an way of counting each ID's reg.date and update date, and then place these counts in the correct week nbr.

My initial thought was that I need to somehow create an independent timeline to place an count of each id’s register date and its update date, but I cant figure out how to do this.

I hope my explanation of my problem is understandable.

Any tips or hints on how to solve this would be gratly appreciated :)

Best regards
Ernst





Best Regards
Ernst Noto
 
My first thought was to group by week and then use 2 Running Totals. The problem though is that you can have orders that were created in one week and updated in another.

You may have to use a subreport that will select just the records that have either one of the two dates in the one week range, then set up Running Totals that count if each date falls in the range.
 
That is exactly what the problem is. I have thought of subreports, but the problem there is that, as far as I know, I can only send total sums to a sub report and not Group sums to Place inn a table.

Best Regards
Ernst Noto
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top