manual cross tab w/ date columns

[MSBrady]

CR 10
SQL 2005

I am fixing a estimate report I inherited. It's a manual cross tab that I need to be able to populate date columns for. Say I have a job# and this job# has 4 possible revisions. Each revision has a different DateTime and amount. I can figure min and max easy enough for Date1 and Date4, but how do I figure out Date2 and Date3? I have 4 date formulas and 4 amount formulas. The amount formulas are like this:

//@Amount1
if ToText({a_datetime}, "MM/dd/yyyy") = {@Date1}
then {Amount}
else 0

I am trying to figure out the date formulas.

Job# ABC123
REVID:04

TASK: DATE1: July 6,1999 DATE2: DATE3: DATE4: April 5, 2000
Art
AMOUNT: 123.00 321.00
Talent
AMOUNT: 1,345.00 2,678.00
Radio
AMOUNT: 45.00 55.00
Travel
AMOUNT: 4,000.00 3250.00

 

[TheBlondOne]

What do you need your other dates to be? What should be the next date after July 6 1999 and what should be the date before April 5 2000?

Once you know what the dates should be you can use the datediff function to work out the other 2.

-Steve


"if at first you don't succeed, sky diving is not for you"!!! )

 

[lbass]

Can't really help you with this without knowing whether you are working with only one date field and one amount field, or whether there are multiple fields.

You also mention min and max without explaining their relevance.

Please show how the data looks in its raw form at the detail level--before you attempt the crosstab.

-LB

 

[MSBrady]

Thanks for your feedback Steve & LB!
Steve, the dates aren't known ahead of time.
LB, I use Min to determine the lowest and Max to determine the highest, but there may be 2 other dates that fall in between them Date2 and Date3. There is one field for date and one for amount. The header table has one row for each project and revid, and there could be many detail records. Oh! Min won't work as there may be more than 4 detail records.
So I need the 4 most recent dates for the report.

 

[lbass]

Try inserting a crosstab that uses {table.date} as the column field, {table.task} as the row field, and amount as the summary. Place the crosstab in the report header or report footer.

-LB