We help IT Professionals succeed at work.

Converting Date range to monthly report

turbow75
turbow75 asked
on
Medium Priority
1,121 Views
Last Modified: 2010-04-21
I need to create a resource available database.  The tables I have are the resources items, the user info, and various other information tables.

I have crosstab Query that will show me the information almost how I want it.  The user table has a start date and end date info.  The issue I have is I've only gotten the crosstab query to show the start date or end date.  What I want is for it to show me the months the resource is used.

Data is entered as
Resource:   Item 1
User:  Joe
Start date:   2/1/08
End date: 4/1/08

Resource: Item 2
User: Amy
Start date: 1/1/08
End date:  5/1/08

Crosstab shows like this
                Jan     Feb     Mar     Apr     May     Jun     Jul     Aug     Sep     Oct     Nov     Dec
Item 1                 Joe
Item 2     Amy


Want it to look like this:
                Jan     Feb    Mar    Apr    May
Item 1                 Joe    Joe     Joe
Item 2      Amy    Amy   Amy   Amy  Amy

I thought if I could create a table from the information entered that would list all of the months the resource is used, the crosstab query would work.  It is creating that table I am having an issue with, and is that the best route.

I do not need to edit the data as I want it to look, will require the user to go back and change the start/end dates.

Thanks,
Shane
Comment
Watch Question

Commented:
You need a small Nums table containing the integer field Num with values 0 to 12.  Then use that table in you crosstab as a Cartesian Join.  Post the SQL from your crosstab and I'll show you what to do.  All start and end dates are alway firsts of the month?  What about different years?

Author

Commented:
Here is the SQL.  The DataStartDate parameter was to just so I could change when the 12 month data was shown (will always be 1/1/XX)

Yes, most start/end dates are on the first.  There could be a case where the start date is mid-month, but the asset is still tied up for that month.

Yes, Start/End dates could also cross over years

PARAMETERS DataStartDate DateTime;
TRANSFORM First([Active Contracts].[Client Name]) AS [FirstOfClient Name]
SELECT [Active Contracts].Address
FROM [Active Contracts]
WHERE ((([Active Contracts].StartDate)>=[DataStartDate]))
GROUP BY [Active Contracts].ID, [Active Contracts].Address, [Active Contracts].ContractID
PIVOT Year([StartDate])*12+Format([StartDate],"mm")-(Year([DataStartDate])*12+Format([DataStartDate],"mm"))+1 In (1,2,3,4,5,6,7,8,9,10,11,12);

Commented:
PARAMETERS DataStartDate DateTime;
TRANSFORM First([Active Contracts].[Client Name]) AS [FirstOfClient Name]
SELECT [Active Contracts].Address,Year(DateSerial(Year(DataStartDate,Month(DataStartDate)+Num,15)) as ConYear
FROM [Active Contracts], Nums
WHERE DateSerial(Year(DataStartDate,Month(DataStartDate)+Num,15) BETWEEN
[Active Contracts].StartDate AND [Active Contracts].EndDate
GROUP BY [Active Contracts].ID, [Active Contracts].Address
Year(DateSerial(Year(DataStartDate,Month(DataStartDate)+Num,15))
PIVOT Format(DateSerial(Year(DataStartDate,Month(DataStartDate)+Num,15),"mmm") IN
("Jan","Feb","Mar","Apr","May","Jun""Jul","Aug","Sep","Oct","Nov","Dec")

Give this a shot.  Remember the Nums table - integer values 0 to 11 (not 12)

Author

Commented:
I get the following error:

Syntax error (missing operator) in query expression 'Year(DateSerial(Year(DataStartDate,Month(DataStartDate)+Num,15)) as ConYear
FROM [Active Contracts], Nums
WHERE DateSerial(Year(DataStartDate,Month(DataStartDate)+Num,15) BETWEEN
[Active Contracts].StartDate AND [Active Contracts].EndDate
GROUP BY ['.

Slightly familiar with SQL, does it matter what line they are on?  

I have the table Nums with on item Num integer 0-11

Commented:
It doesn't matter what line.  I missed a closing parenthesis in the Year() function:

PARAMETERS DataStartDate DateTime;
TRANSFORM First([Active Contracts].[Client Name]) AS [FirstOfClient Name]
SELECT [Active Contracts].Address,Year(DateSerial(Year(DataStartDate),Month(DataStartDate)+Num,15)) as ConYear
FROM [Active Contracts], Nums
WHERE DateSerial(Year(DataStartDate),Month(DataStartDate)+Num,15) BETWEEN
[Active Contracts].StartDate AND [Active Contracts].EndDate
GROUP BY [Active Contracts].ID, [Active Contracts].Address
Year(DateSerial(Year(DataStartDate),Month(DataStartDate)+Num,15))
PIVOT Format(DateSerial(Year(DataStartDate),Month(DataStartDate)+Num,15),"mmm") IN
("Jan","Feb","Mar","Apr","May","Jun""Jul","Aug","Sep","Oct","Nov","Dec")

Author

Commented:
New error

Syntax error (missing operator) in query expression '[Active Contracts].Address,Year(DateSerial(Year(DataStartDate),Month(DataStartDate)+Num,15))'.
Commented:
Comma after Address end of line 7 is all I can see.

Author

Commented:
Also a missing comma between Jun & Jul on the last line.  But that is what I needed.  Thanks for the help!

Author

Commented:
Thanks again for your help, I've got a lot to learn about SQL

Commented:
Thanks for the catch, and the grade.  Glad to help.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.