turbow75
asked on
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
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
GRayL
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?
ASKER
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)>=[Da
GROUP BY [Active Contracts].ID, [Active Contracts].Address, [Active Contracts].ContractID
PIVOT Year([StartDate])*12+Forma
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)>=[Da
GROUP BY [Active Contracts].ID, [Active Contracts].Address, [Active Contracts].ContractID
PIVOT Year([StartDate])*12+Forma
PARAMETERS DataStartDate DateTime;
TRANSFORM First([Active Contracts].[Client Name]) AS [FirstOfClient Name]
SELECT [Active Contracts].Address,Year(Da
FROM [Active Contracts], Nums
WHERE DateSerial(Year(DataStartD
[Active Contracts].StartDate AND [Active Contracts].EndDate
GROUP BY [Active Contracts].ID, [Active Contracts].Address
Year(DateSerial(Year(DataS
PIVOT Format(DateSerial(Year(Dat
("Jan","Feb","Mar","Apr","
Give this a shot. Remember the Nums table - integer values 0 to 11 (not 12)
TRANSFORM First([Active Contracts].[Client Name]) AS [FirstOfClient Name]
SELECT [Active Contracts].Address,Year(Da
FROM [Active Contracts], Nums
WHERE DateSerial(Year(DataStartD
[Active Contracts].StartDate AND [Active Contracts].EndDate
GROUP BY [Active Contracts].ID, [Active Contracts].Address
Year(DateSerial(Year(DataS
PIVOT Format(DateSerial(Year(Dat
("Jan","Feb","Mar","Apr","
Give this a shot. Remember the Nums table - integer values 0 to 11 (not 12)
ASKER
I get the following error:
Syntax error (missing operator) in query expression 'Year(DateSerial(Year(Data
FROM [Active Contracts], Nums
WHERE DateSerial(Year(DataStartD
[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
Syntax error (missing operator) in query expression 'Year(DateSerial(Year(Data
FROM [Active Contracts], Nums
WHERE DateSerial(Year(DataStartD
[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
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(Da
FROM [Active Contracts], Nums
WHERE DateSerial(Year(DataStartD
[Active Contracts].StartDate AND [Active Contracts].EndDate
GROUP BY [Active Contracts].ID, [Active Contracts].Address
Year(DateSerial(Year(DataS
PIVOT Format(DateSerial(Year(Dat
("Jan","Feb","Mar","Apr","
PARAMETERS DataStartDate DateTime;
TRANSFORM First([Active Contracts].[Client Name]) AS [FirstOfClient Name]
SELECT [Active Contracts].Address,Year(Da
FROM [Active Contracts], Nums
WHERE DateSerial(Year(DataStartD
[Active Contracts].StartDate AND [Active Contracts].EndDate
GROUP BY [Active Contracts].ID, [Active Contracts].Address
Year(DateSerial(Year(DataS
PIVOT Format(DateSerial(Year(Dat
("Jan","Feb","Mar","Apr","
ASKER
New error
Syntax error (missing operator) in query expression '[Active Contracts].Address,Year(Da
Syntax error (missing operator) in query expression '[Active Contracts].Address,Year(Da
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Also a missing comma between Jun & Jul on the last line. But that is what I needed. Thanks for the help!
ASKER
Thanks again for your help, I've got a lot to learn about SQL
Thanks for the catch, and the grade. Glad to help.