August 18, 2010 at 12:11 am
Hi All,
I am new to T-SQL.
My query is to insert a new record against each unique column value in a table through stored procedure.
Example
Current table data
Name Item Effective Date Price
------- -------- ---------------- -------
ABC IT01 01/01/2008 1000
ABC IT01 01/02/2008 2000
ABC IT01 01/03/2008 3000
DEF IT02 01/01/2008 12000
DEF IT02 01/02/2008 13000
DEF IT02 01/03/2008 14000
Expected Table Data
Name Item Effective Date Price
------- -------- ---------------- -------
ABC IT01 01/01/2008 1000
ABC IT01 01/02/2008 2000
ABC IT01 01/03/2008 3000
ABC IT01 01/04/2008 4000
DEF IT02 01/01/2008 12000
DEF IT02 01/02/2008 13000
DEF IT02 01/03/2008 14000
DEF IT02 01/04/2008 15000
August 18, 2010 at 12:25 am
Can you be more specific.
Is the next record always an increment by one month and increment of 1000 price of the previous record. What will be the input parameters to your stored procedure.
What is the primary key on your table?
August 18, 2010 at 12:38 am
Not Sure if this is what you are looking at. replace the temp table with your actual table.
Here is a sample code
[Code]
CREATE TABLE #Inv
(
Name VARCHAR(10),
Item VARCHAR(10),
EffectiveDate DATETIME,
Price int
)
INSERT INTO #Inv
SELECT
'ABC', 'IT01', '01/01/2008', 1000
UNION ALL
SELECT
'ABC', 'IT01', '01/02/2008', 2000
UNION ALL
SELECT
'ABC', 'IT01', '01/03/2008', 3000
UNION ALL
SELECT
'DEF','IT02', '01/01/2008', 12000
UNION ALL
SELECT
'DEF','IT02', '01/02/2008', 13000
UNION ALL
SELECT
'DEF','IT02', '01/03/2008', 14000
INSERT INTO #Inv
(
Name,
Item,
EffectiveDate,
Price
)
SELECT
UniqueRecords.Name,
UniqueRecords.Item,
DATEADD(d, 1, MaxEffectiveDate),
MaxPrice.Price+1000
FROM
(
SELECT
Name,
Item,
MAX(EffectiveDate) AS MaxEffectiveDate
FROM
#Inv
GROUP BY
Name,
Item
)UniqueRecords
JOIN
(
SELECT
Name,
Item,
EffectiveDate,
Price
FROM
#Inv
)
MaxPrice
ONMaxPrice.EffectiveDate = UniqueRecords.MaxEffectiveDate
AND MaxPrice.Name = UniqueRecords.Name
AND MaxPrice.Item = UniqueRecords.Item
SELECT * FROM #Inv
DROP TABLE #Inv
[/Code]
August 18, 2010 at 1:43 am
It is like when ever the user wants to add new record aginst each item by the (latestprice +10%) and one effectivedate.
August 18, 2010 at 5:21 pm
In that case the above code should work, just change MaxPrice.Price+1000 to
MaxPrice.Price + (0.1*MaxPrice.Price). Test it and let me know if this works for you.
August 19, 2010 at 2:32 am
Hi there,
Mukti Ranjan (8/18/2010)
It is like when ever the user wants to add new record aginst each item by the (latestprice +10%) and one effectivedate.
In ABC/IT01, the latest price is 3000.. I believe the record to be inserted is 3000 + (3000*.10) = 3300..
Here's the code to achieve that result:
DECLARE @Table TABLE (Name VARCHAR(10),Item VARCHAR(10),EffectiveDate DATETIME,Price INT)
INSERT INTO @Table
SELECT 'ABC', 'IT01', '01/01/2008', 1000 UNION ALL
SELECT 'ABC', 'IT01', '01/02/2008', 2000 UNION ALL
SELECT 'ABC', 'IT01', '01/03/2008', 3000 UNION ALL
SELECT 'DEF', 'IT02', '01/01/2008', 12000 UNION ALL
SELECT 'DEF', 'IT02', '01/02/2008', 13000 UNION ALL
SELECT 'DEF', 'IT02', '01/03/2008', 14000
INSERT INTO @Table
SELECT Name,Item,DATEADD(dd,1,MAX(EffectiveDate)),CAST(1.1*MAX(Price) AS INT)
FROM @Table
GROUP BY Name,Item
SELECT * FROM @table
Note that this will truncate the result if it contains decimal numbers.
Just reply if u have questions.. I hope this helps 🙂
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply