• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1100
  • Last Modified:

Converting Date range to monthly report

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
0
turbow75
Asked:
turbow75
  • 5
  • 5
1 Solution
 
GRayLCommented:
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?
0
 
turbow75Author 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);
0
 
GRayLCommented:
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)
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
turbow75Author 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
0
 
GRayLCommented:
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")
0
 
turbow75Author Commented:
New error

Syntax error (missing operator) in query expression '[Active Contracts].Address,Year(DateSerial(Year(DataStartDate),Month(DataStartDate)+Num,15))'.
0
 
GRayLCommented:
Comma after Address end of line 7 is all I can see.
0
 
turbow75Author Commented:
Also a missing comma between Jun & Jul on the last line.  But that is what I needed.  Thanks for the help!
0
 
turbow75Author Commented:
Thanks again for your help, I've got a lot to learn about SQL
0
 
GRayLCommented:
Thanks for the catch, and the grade.  Glad to help.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now