AnsweredAssumed Answered

SQL needed that has by resource YTD ETC & actuals on same row

Question asked by AlexSch on Jun 27, 2012
Latest reply on Jun 29, 2012 by Dave
Help.

I'm kind of new to Clarity (b.1) and my users have requested a report I don't know how to write the SQL for. What they are requesting is a report, with a parm that will be either a resource obs, or resource manager, that will group by resource under either of the parms above.

Each row will include all of the ETC's for that resource, totaled by month and actuals totaled by month. Here's an example:


january
january
feb
feb
mar
mar
apr
apr
may
may
jun
jun
july
july
augst
augst
sept
sept
oct
oct
nov
nov
dec
dec

etc
actual
etc
actual
etc
actual
etc
actual
etc
actual
etc
actual
etc
actual
etc
actual
etc
actual
etc
actual
etc
actual
etc
actual
fred
0
123
0
345
0
456
0
123
0
345
45
23
77
0
67
0
87
0
45
0
54
0
23
0
lucy
0
444
0
876
0
678
0
908
0
753
34
663
234
0
432
0
43
0
53
0
23
0
18
0


so, for each resource there would be ETC totals for each month that there are no actuals for and for each month that you have actuals for, there wold be no etc's.

Sounds simple. I just know someone out there has written the SQL code to do this. Being a semi-experienced SQL coder I haven't been able to figure out how to combine the (2) different sets of data into (1) row per resource and total at the month level. I've written SQL code to get a monthly total but not a YTD across the months total by resource.

I'll take any code anyone can send me so that I can dump this into BO and create a Crystal Report off of this SQL.

Please help.

Alex Schulman

Outcomes