April 27, 2010 at 10:26 am
Hello I am a newbie so please keep the beatings to a min.
I have this query I am currently using in Access.
SELECT [Forecast All].[forecastall_ID#], tblEAU.EAU_ID, tblEAU.Year, Avg(Pricing.[Effective Price]) AS Price, "1H" AS Term
FROM ([Forecast All] INNER JOIN tblEAU ON [Forecast All].[Prog_ID#] = tblEAU.Prog_ID) INNER JOIN Pricing ON [Forecast All].[forecastall_ID#] = Pricing.[Forecastall_ID#]
WHERE (((Pricing.[Start Date])<="9/30/" & [year]) AND ((Pricing.[End Date])>"4/1/" & [year]))
GROUP BY [Forecast All].[forecastall_ID#], tblEAU.EAU_ID, tblEAU.Year, "1H"
ORDER BY [Forecast All].[forecastall_ID#], tblEAU.EAU_ID, tblEAU.Year;
Trying to make a SP and having problems specifying the "Year".
Table "Forecast All" is a table with Part Numbers
Table "Pricing" is a table of Prices and validity dates
Table tblEAU is a table of Estimated Quantities by year
I need to find the effective price by year semi-annually.
Example:
year - 2010
Term - 1H (4/1/2009 to 9/30/2009)
Price - $.50
I already have the SP working but I can only use specific dates and not the Year stored in the tblEAU
@StartDate = '4/1/2009"
I need it to work like
@StartDate = '4/1/' & tblEAU.[Year]
Thanks for the help
April 27, 2010 at 11:00 am
If you could post the DDL (CREATE TABLE statement(s)) for the table(s) involved, some sample data (as a series of INSERT INTO tablename statements) for the table(s) involved, the expected results based the provided sample data I'm sure we could help you with your stored procedure.
For help with this request, please read the first article I reference below in my signature block regarding asking for assistance.
April 27, 2010 at 11:37 am
I'll try. not involving any creates or inserts so i will give you brief descriptions of the tables.
Forecast all:
FID# (key)
ProgID# (reference to master table)
Part Number
other irrelevant info
Pricing:
PID# (Key)
FID# (link to Forecast all.FID#)
Start Date (datetime)
End Date (datetime)
Price
tblEAU:
EAUID# (key)
ProgID# (link to master table)
Year
Quantity
For every Part there are several prices based on a date range.
Foreall.Part#1
price.Start date: 4/1/2010
price.End Date: 12/31/2010
price.Price: .50
price.Start date: 1/1/2011
price.End Date: 12/31/2011
tblEAU.Year: 2010
tblEAU.Quantity: 100,000
tblEAU.Year: 2011
tblEAU.Quantity: 150,000
Our fiscal year runs from April to March.
So I need to identify the correct price for the 1st half of 2010 and 2011 so I can calculate the Sales Amount. right now I am just trying to identify the correct price. I will worry about the Amount later.
The forecast all table contains thousands of Parts.
The "Master" table contains Customer and Project information
So it is broken down by Customer/Project - Part Number - Price - EAU
April 27, 2010 at 11:56 am
Do you really expect me to write the create table scripts for your tables and to create sample data for you?
Please provide what has been requested. The more you do to help us help you, the better. In return for your upfront efforts you will get tested code in return.
April 27, 2010 at 12:08 pm
who is asking you to write create table scripts? or create sample data? I thought I provided some generic sample data. I need to tweak the query I have in the OP to work on the server.
I can't copy real sample data from the tables, that info is confidential. So rather I made some up.
If you can't help than thanks anyway. Can anyone understand or help?
Sorry I am causing confusion or frustration. just looking for some advise.
April 27, 2010 at 12:24 pm
jrizzo-1083740 (4/27/2010)
who is asking you to write create table scripts? or create sample data? I thought I provided some generic sample data. I need to tweak the query I have in the OP to work on the server.I can't copy real sample data from the tables, that info is confidential. So rather I made some up.
If you can't help than thanks anyway. Can anyone understand or help?
Sorry I am causing confusion or frustration. just looking for some advise.
First, please take the time to read the article I suggested in my first post. Second, we aren't expecting you to post real data, in fact we would prefer that you don't. We just need sample data that supports the problem domain so that we can write code and test code to provide you with the BEST answer (or answers) possible.
You posted descriptions of the tables, why not just post the CREATE TABLE scripts instead? You posted generic sample data, how about posting that as a series of INSERT INTO statements so we could populate the tables and actual test our code against your data?
We are volunteers here on SSC, the more work you do up front the better answers we can provide you.
April 27, 2010 at 12:59 pm
jrizzo-1083740 (4/27/2010)
Hello I am a newbie so please keep the beatings to a min.I already have the SP working but I can only use specific dates and not the Year stored in the tblEAU
@StartDate = '4/1/2009"
I need it to work like
@StartDate = '4/1/' & tblEAU.[Year]
Thanks for the help
Try this:
declare @StartDate datetime
set @StartDate = '04/01/' + convert(char(4), YEAR(GetDate())-10)
select @StartDate
To get the information from your table:
declare @StartDate datetime
select @StartDate = '04/01/' + convert(char(4), tblEAU.[Year])
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
April 27, 2010 at 1:43 pm
I understand. Hope this is what you are looking for.
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#CustProg','U') IS NOT NULL
DROP TABLE #CustProg
--===== Create the test table with
CREATE TABLE #CustProg
(
ProgID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Customer varchar(50)
Program varchar(50)
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #CustProg ON
--===== Insert the test data into the test table
INSERT INTO #CustProg
(ProgID, Customer, Program)
Select '1','Dog','Collar' UNION ALL
Select '2','Dog','Leash' UNION ALL
Select '3','Cat','Bowl' UNION ALL
Select '4','Cat','Bed'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #CustProg OFF
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Foreall','U') IS NOT NULL
DROP TABLE #Foreall
--===== Create the test table with
CREATE TABLE #Foreall
(
ForeID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
PartN Varchar(50),
ProgID INT,
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Foreall ON
--===== Insert the test data into the test table
INSERT INTO #Foreall
(ForeID, PartN, ProgID)
Select '1','ABC','1' UNION ALL
Select '2','123','1' UNION ALL
Select '3','DFG','2' UNION ALL
Select '4','456','3' UNION ALL
Select '5','XYZ','3' UNION ALL
Select '6','789','4'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Foreall OFF
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#Pricing','U') IS NOT NULL
DROP TABLE #Pricing
--===== Create the test table with
CREATE TABLE #Pricing
(
PrID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
StartDate DATETIME,
EndDate DATETIME,
Price DECIMAL(9,4),
ForID INT,
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #Pricing ON
--===== Insert the test data into the test table
INSERT INTO #Pricing
(PrID, StartDate, EndDate,Price,ForID)
Select '1','4/1/2010','3/31/2011','.50','1' UNION ALL
Select '2','4/1/2011','3/31/2012','.49','1' UNION ALL
Select '3','4/1/2010','3/31/2011','.48','2' UNION ALL
Select '4','4/1/2011','3/31/2012','.50','2' UNION ALL
Select '5','4/1/2010','3/31/2011','.49','3' UNION ALL
Select '6','4/1/2011','3/31/2012','.48','3' UNION ALL
Select '7','4/1/2010','3/31/2011','.49','4' UNION ALL
Select '8','4/1/2011','3/31/2011','.48','4' UNION ALL
Select '9','4/1/2010','3/31/2011','.49','5' UNION ALL
Select '10','4/1/2011','3/31/2011','.48','5' UNION ALL
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #Pricing OFF
--===== If the test table already exists, drop it
IF OBJECT_ID('TempDB..#tblEAU','U') IS NOT NULL
DROP TABLE #tblEAU
--===== Create the test table with
CREATE TABLE #tblEAU
(
EAUID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,
Year INT,
QTY INT,
ProgID INT
)
--===== All Inserts into the IDENTITY column
SET IDENTITY_INSERT #tblEAU ON
--===== Insert the test data into the test table
INSERT INTO #tblEAU
(EAUID, Year, QTY,ProgID)
Select '1','2010','100','1' UNION ALL
Select '2','2011','200','1' UNION ALL
Select '3','2010','50','2' UNION ALL
Select '4','2011','75','2' UNION ALL
Select '5','2010','1000','3' UNION ALL
Select '6','2011','2000','3' UNION ALL
Select '7','2010','10000','4' UNION ALL
Select '8','2011','20000','4'
--===== Set the identity insert back to normal
SET IDENTITY_INSERT #tblEAU OFF
April 27, 2010 at 1:55 pm
Just one last thing, based on the sample data, what would be the expected output?
This gives us something to test our output against.
April 27, 2010 at 2:11 pm
ProgID, ForeID, EAUID, Term, Year, Price
1, 1, 1, 1H, 2010, .50
1, 1, 1, 1H, 2011, .49
1, 2, 3, 1H, 2010, ..48
April 27, 2010 at 2:12 pm
Thanks Wayne,
Got it to work.
April 27, 2010 at 3:09 pm
jrizzo-1083740 (4/27/2010)
Thanks Wayne,Got it to work.
Great!
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
Viewing 12 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply