November 22, 2010 at 9:13 am
Hello. I am working on a project for my work and I need some help. I am fairly new at SQL. Been at it a week, but they are paying me to learn it.
I am suppose to make a buy analysis for them. They want the following fields:
Part No.
Description
QTY Purchased Last year
Value Purchased Last Year.
QTY Purchased 3 years.
Value Purchased 3 years
QTY Purchased 5 Years
Value Purchased 5 years.
I am using these columns from these tables:
fpartno - poitem - Part
fdescript - poitem - Description
fordrqty - poitem - Total Quantity
forddate - pomast - using "YEAR(dbo.pomast.forddate)" to grab just the year the order was made.
So far I have made a new View to that grabs Part No. , Description , Total Quantity , and Year Ordered
SELECT
TOP 100 PERCENT dbo.poitem.fpartno AS Part,
dbo.poitem.fdescript AS Description,
dbo.poitem.fordqty AS [Quantity Purchased],
YEAR(dbo.pomast.forddate) AS [Year Purchased],
dbo.poitem.fucost
FROM
dbo.poitem INNER JOIN
dbo.inmast ON dbo.poitem.fpartno = dbo.inmast.fpartno AND
dbo.poitem.frev = dbo.inmast.frev INNER JOIN
dbo.pomast ON dbo.poitem.fpono = dbo.pomast.fpono
Now I know that to get the value I would use (fucost * ordqty) AS Value. But I dont know how or where to put this statment.
So.. how on earth do I get Total Value and QTY Purchased into 1 year , 3 year , 5 year columns? Btw, the end user will pull the data using Excel ODBC.
Thanks!
November 22, 2010 at 9:29 am
you can put an expression, just like you were planning, in the query, and it will appear as results int he query.
here's your specific example: note i simply added the calcualtion you identified and gave it an Alias:
(dbo.poitem. * dbo.poitem.fordqty) AS TheValue.
SELECT
TOP 100 PERCENT dbo.poitem.fpartno AS Part,
dbo.poitem.fdescript AS Description,
dbo.poitem.fordqty AS [Quantity Purchased],
YEAR(dbo.pomast.forddate) AS [Year Purchased],
dbo.poitem.fucost,
(dbo.poitem. * dbo.poitem.fordqty) AS TheValue.
FROM
dbo.poitem INNER JOIN
dbo.inmast ON dbo.poitem.fpartno = dbo.inmast.fpartno AND
dbo.poitem.frev = dbo.inmast.frev INNER JOIN
dbo.pomast ON dbo.poitem.fpono = dbo.pomast.fpono
Lowell
November 22, 2010 at 9:38 am
Awesome, so the Total Value works now.
Now I just need the Total Value and Quantity Purchased for Last Year , 3 Years , and 5 Years. Each in seperate columns then I should be good to go.
I just have no clue how to do it though!
November 22, 2010 at 10:21 am
Basically, you're going to pivot this data. Read the two cross-tab/pivot tables articles in my signature.
I think that this will do it:
;WITH CTE AS
(
SELECT dbo.poitem.fpartno AS Part,
dbo.poitem.fdescript AS Description,
dbo.poitem.fordqty AS [Quantity Purchased],
YEAR(dbo.pomast.forddate) AS [Year Purchased],
(dbo.poitem.fucost * dbo.poitem.fordqty) AS TheValue
FROM dbo.poitem
JOIN dbo.inmast
ON dbo.poitem.fpartno = dbo.inmast.fpartno
AND dbo.poitem.frev = dbo.inmast.frev
JOIN dbo.pomast
ON dbo.poitem.fpono = dbo.pomast.fpono
)
SELECT Part, [Description],
Last1YearQty = CASE WHEN [Year Purchased] = year(getdate())-1 THEN [QuantityPurchased] ELSE 0 END,
Last3YearQty = CASE WHEN [Year Purchased] = year(getdate())-3 THEN [QuantityPurchased] ELSE 0 END,
Last5YearQty = CASE WHEN [Year Purchased] = year(getdate())-5 THEN [QuantityPurchased] ELSE 0 END,
Last1YearTot = CASE WHEN [Year Purchased] = year(getdate())-1 THEN [TheValue] ELSE 0 END,
Last3YearTot = CASE WHEN [Year Purchased] = year(getdate())-3 THEN [TheValue] ELSE 0 END,
Last5YearTot = CASE WHEN [Year Purchased] = year(getdate())-5 THEN [TheValue] ELSE 0 END
FROM CTE
GROUP BY Part, [Description];
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 22, 2010 at 10:28 am
Hey thanks alot. Havent learned and thing about Cross Tabs and Pivot Tables. I am checking you article out now 🙂
Thanks for the code. I will try it tommorow at work.
November 22, 2010 at 10:36 am
jguerra2 (11/22/2010)
Hey thanks alot. Havent learned and thing about Cross Tabs and Pivot Tables. I am checking you article out now 🙂Thanks for the code. I will try it tommorow at work.
Actually, they're not my articles. A great guy by the name of Jeff Moden wrote them (and many others). All of his articles are worth reading and learning from - check them out here[/url]
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 23, 2010 at 5:18 am
Had some problems with the code you wrote for me. So I had to change a few things around. Please check out my next post and the picture with it.
November 23, 2010 at 7:42 am
Okay well I've been trying to figure this out. As of now here is main View for this project. It pulls all the values fine. Please look at the following picture and you'll understand what i'm talking about.
http://www.mediafire.com/i/?bi93obm1b0jl4zc
Here is a seperate view made to get the Case Statement working. It runs but no results.
SELECT
TOP 100 PERCENT Part, Description, QTYPurch, fucost, [Year Purchased], Value,
CASE WHEN [Year Purchased] = DATEADD(year, - 1, GETDATE()) THEN [Value] ELSE 0 END AS Last1YearTot,
CASE WHEN [Year Purchased] = DATEADD(year, - 1, GETDATE()) THEN [QTYPurch] ELSE 0 END AS Last1YearQty
FROM jeffguerra.qBuyAnValueQTY
It runs but no results for Last1YearTot Or Qty Just all 00000's
[Year Purchased] is YEAR(dbo.pomast.forddate) - Only the Year of the Order Date
[QTY Purch] is dbo.poitem.fordqty - This is the Order Quantity.
So in english what I think its doing is grabbing the year of the Order Date, and if it equals (=) the current Year -1 then it will show the results of the Order QTY for that year, per Item.
We have multiple Purchase Orders made for the same items. I need to be able to see the Total Quantity Purchased for that item in a particular year.
Any help would be much appreciated. Thanks
November 23, 2010 at 11:14 am
The problem is what you are doing in your case statement... specifically your date math.
You are taking the value returned from the GetDate() function, and subtracting one year from it.
So, if GetDate() returns '2010-11-22 12:46:52.658', your date math will return '2009-11-22 12:46:52.658'.
You are then comparing the [Year Purchased] value to this date, and you are not finding any matches. So, you are counting records that have a [Year Purchased] equal to this date.
All of this is assuming that [Year Purchased] is actually a date. If it's not a date, it will be implicitly converted into a date. Assuming that [Year Purchased] = 2010, this will be converted to 1905-07-04. With the date "0" representing 1900-01-01, this is 2010 days from that date. Which isn't what you want.
Either way, you aren't going to find a match with the way that the query is written.
I can't view the url that you posted... work is blocking it. Can you describe what your problem is? I should be able to help you through it.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 23, 2010 at 11:15 am
Oh - you don't need the TOP 100 PERCENT on all your queries - all you're doing is making SQL work harder / take longer to return your results.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 23, 2010 at 10:36 pm
WayneS (11/22/2010)
jguerra2 (11/22/2010)
Hey thanks alot. Havent learned and thing about Cross Tabs and Pivot Tables. I am checking you article out now 🙂Thanks for the code. I will try it tommorow at work.
Actually, they're not my articles. A great guy by the name of Jeff Moden wrote them (and many others). All of his articles are worth reading and learning from - check them out here[/url]
:blush:Thanks, Wayne.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 24, 2010 at 5:00 am
WayneS (11/23/2010)
The problem is what you are doing in your case statement... specifically your date math.You are taking the value returned from the GetDate() function, and subtracting one year from it.
So, if GetDate() returns '2010-11-22 12:46:52.658', your date math will return '2009-11-22 12:46:52.658'.
You are then comparing the [Year Purchased] value to this date, and you are not finding any matches. So, you are counting records that have a [Year Purchased] equal to this date.
All of this is assuming that [Year Purchased] is actually a date. If it's not a date, it will be implicitly converted into a date. Assuming that [Year Purchased] = 2010, this will be converted to 1905-07-04. With the date "0" representing 1900-01-01, this is 2010 days from that date. Which isn't what you want.
Either way, you aren't going to find a match with the way that the query is written.
I can't view the url that you posted... work is blocking it. Can you describe what your problem is? I should be able to help you through it.
Hey wayne, I appreciate your help. I just ran a simple query for YEAR(getdate()) and [Year Purchased] , You were right, the format is different, but how I am selecting yields similar results. dbo.pomast.forddate is our order dates. it is in the format 2010-11-23-00:00:00:000, I had changed the statment do just select the year. Here are the results:
YEAR(getdate()) returns '2010'
[Year Purchased] = YEAR(dbo.pomast.forddate) it returns '2010'
So if I understand you correctly, I think it should work. I'll change my case statments to YEAR(getdate()) and see the results. I'll post more in a bit.
November 24, 2010 at 5:15 am
jguerra2 (11/24/2010)
WayneS (11/23/2010)
The problem is what you are doing in your case statement... specifically your date math.You are taking the value returned from the GetDate() function, and subtracting one year from it.
So, if GetDate() returns '2010-11-22 12:46:52.658', your date math will return '2009-11-22 12:46:52.658'.
You are then comparing the [Year Purchased] value to this date, and you are not finding any matches. So, you are counting records that have a [Year Purchased] equal to this date.
All of this is assuming that [Year Purchased] is actually a date. If it's not a date, it will be implicitly converted into a date. Assuming that [Year Purchased] = 2010, this will be converted to 1905-07-04. With the date "0" representing 1900-01-01, this is 2010 days from that date. Which isn't what you want.
Either way, you aren't going to find a match with the way that the query is written.
I can't view the url that you posted... work is blocking it. Can you describe what your problem is? I should be able to help you through it.
Hey wayne, I appreciate your help. I just ran a simple query for YEAR(getdate()) and [Year Purchased] , You were right, the format is different.
YEAR(getdate()) returns '2010' and [Year Purchased] is actually the year, month and day the order was made. It returns ' 2010-11-23 00:00:00:000 ' dating all the way back to 2000. So if I understand you correctly, I just need to get my formatting correct and it will yield correct results.
Okay, since we now know that [Year Purchased] is actually a date, then this would seem to be your solution:
DECLARE @StartOfThisYear datetime,
@StartOfNextYear datetime;
SELECT @StartOfThisYear = DateAdd(year, DateDiff(year, 0, GetDate()),0),
@StartOfNextYear = DateAdd(year, 1, @StartOfThisYear);
SELECT @StartOfThisYear, @StartOfNextYear;
SELECT Part, Description, QTYPurch, fucost, [Year Purchased], Value,
CASE WHEN [Year Purchased] >= @StartOfThisYear and [Year Purchased] < @StartOfNextYear
THEN [Value] ELSE 0 END AS Last1YearTot,
CASE WHEN [Year Purchased] >= @StartOfThisYear and [Year Purchased] < @StartOfNextYear
THEN [QTYPurch] ELSE 0 END AS Last1YearQty
FROM jeffguerra.qBuyAnValueQTY
BTW, is the [Year Purchased] column a datetime data type? Based on what you said it's returning, it appears to be so.
Edit: Note that I'm not using YEAR([Year Purchased]). Since you are using this conversion in the columns being returned, and not in the where/join clauses, it won't make a difference. However, if you are using this in either the where or join clauses, the way that I'm doing it will allow an index seek to work - a function on a column in those column will prevent the index from being used.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
November 24, 2010 at 5:49 am
Well your code returns all 0's for one year value and one year WTY. But another problem I seem to be facing is multiple orders of the same part. The only way I can see results is if I filter the Case Statment in previous posts to = '2009'.
Also, I removed the Year from [Year Purchased] in the main query and the results I recieve are actually in a differnt format:
6/18/2001 12:00:00 AM
When I use your code posted above, [Year Purchased] returns 2005-11-16 00:00:00:000 ect.
It seems to be two differnt formats... Idk you're losing me 😀
Is there a faster way I can reach you? MSN?
November 24, 2010 at 10:00 am
Finished the Project! I'm pumped haha. I'll post the code on monday and tell ya how I did it. Have a good holiday! 😀
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply