Joe Grosskopf
asked on
Try to create a view to better visualize my data
Looking for a suggestion. Trying create a view of a table to try show item usage. I have a table that shows item usage by period and year like so:
Item, Year, Per1, Per2, Per3...
Bike, 2016, 3, 2, 6
Bike, 2017, 5, 3, 1
Bike 2018, 3, 0, 0
Skateboard, 2016, 5, 3, 1
Skateboard, 2017, 5, 2, 1
Skateboard, 2018, 6, 8, 3
The production manager is looking to run a report monthly that shows usage ove rthe last 3 months (for this month it would be Per9,10,11). I can'd figure out how to filter so it only shows 2018, 9, 10, 11. I could just look at the month number and say current year, with month greater than 8 but that wont work for jan, feb, mar. This Jan would be 2018,11,12 and 2019 1...
Is there a way to create a view that shows the data differently so we can simply say today() - 90 days or something like that?
Item, Year, Per1, Per2, Per3...
Bike, 2016, 3, 2, 6
Bike, 2017, 5, 3, 1
Bike 2018, 3, 0, 0
Skateboard, 2016, 5, 3, 1
Skateboard, 2017, 5, 2, 1
Skateboard, 2018, 6, 8, 3
The production manager is looking to run a report monthly that shows usage ove rthe last 3 months (for this month it would be Per9,10,11). I can'd figure out how to filter so it only shows 2018, 9, 10, 11. I could just look at the month number and say current year, with month greater than 8 but that wont work for jan, feb, mar. This Jan would be 2018,11,12 and 2019 1...
Is there a way to create a view that shows the data differently so we can simply say today() - 90 days or something like that?
ASKER
I am assuming that Per1 = Jan and so on.
Do you have date field in the table that you can use or is just year and per1
Do you have date field in the table that you can use or is just year and per1
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
How are you going with this ?
One of the difficulties is getting the actual column names, so had to resort to generic names. If we ran it as a stored procedure, then we could use Dynamic SQL to generate more meaningful column names.
Do you want to explore that option ? Depends a little bit on how your end users are accessing the result set....
One of the difficulties is getting the actual column names, so had to resort to generic names. If we ran it as a stored procedure, then we could use Dynamic SQL to generate more meaningful column names.
Do you want to explore that option ? Depends a little bit on how your end users are accessing the result set....
ASKER
I actually got this all finished. I had to create a view in sql changing the period number and year to a date (1-2018 would say Jan-1-2019, 2-2018 would say Feb 1, 2018). Then I created another view that allowed me to use the date variables against those dates. Probably more stapes than I actually needed but it all works. Thanks for everyone's contribution
Well done :)
And it is our pleasure to contribute :)
And it is our pleasure to contribute :)
We can calculate months based on GETDATE() and go from there.
Couple of questions for you:
1) Data source - do you have table definitions you can share ?
2) Data Sample - can you share some sample data in accordance with your data source ?
3) What version of SQL Server are you running ?