September 19, 2005 at 12:52 pm
Hi all!
I have a table like this. I need to insert these table values in to another table depending on the quantity, date. If the quantity is 2, then I need to insert the row twice. This procedure should run daily and the procedure should only copy the rows in to another table where date = getdate( )
Order_table
Ordered Quantity Fname Lname date
1 2 C Y 9-19-05
2 3 S Y 9-19-05
3 4 f y 9-16-05
the result for 9-19-05 date should be
ID Oid Quan Fname lname
1 1 2 C Y
2 1 2 C Y
3 2 3 S Y
4 2 3 S Y
5 2 3 S Y
Any help will be appreciated, Thanks.
September 19, 2005 at 1:00 pm
Quite simple to do actually... but why do you need to have a model like this? Why can't you just use the quantity and date data as is?
September 19, 2005 at 1:07 pm
Even I don't have any idea Remi!
Actually this is for my friend and she told me that she is generating some reports. I tried to do it this way, but it doesn't work if there are multiple OrderIDs in a particular day.
AS
BEGIN
DECLARE @count int
set @count = (select quantity from tempordertable
where orderid = @oid AND CONVERT(VARCHAR(10),UPDATEDON,101) = CONVERT(VARCHAR(10),GETDATE(),101) )
WHILE @count >0
BEGIN
INSERT INTO temptable(oid, quan, fname, lname)
SELECT orderid, quantity, fname, lname
FROM
tempordertable where Orderid = @oid
SET @count = @count - 1
END
END
go
September 19, 2005 at 1:27 pm
Here's how I'd do it. The only missing part is the insert to input the data into a new table.
Declare @demo table (OrderId int not null, ProductID int not null, Qty int not null, OrderDate datetime not null, primary key clustered (OrderDate, OrderId, ProductId, Qty))
Declare @StartDate as datetime
Declare @EndDate as datetime
SET @StartDate = DATEADD(D, 0, DateDiff(D, 0, GetDate()))
SET @EndDate = DATEADD(D, 1, @StartDate)
Insert into @demo (OrderId, ProductID, Qty, OrderDate) values (1, 1, 1, GetDate())
Insert into @demo (OrderId, ProductID, Qty, OrderDate) values (1, 2, 4, GetDate())
Insert into @demo (OrderId, ProductID, Qty, OrderDate) values (1, 3, 2, GetDate())
Insert into @demo (OrderId, ProductID, Qty, OrderDate) values (2, 5, 3, DateAdd(D, -1, GetDate()))
Insert into @demo (OrderId, ProductID, Qty, OrderDate) values (3, 1, 1, GetDate())
Select D.*, N.PkNumber as ProductCount from @demo D inner join dbo.Numbers N on N.PkNumber = @StartDate and D.OrderDate < @EndDate
September 19, 2005 at 1:34 pm
Sorry I completly forgot to include the script for the numbers table.
IF Object_id('Numbers') > 0
DROP TABLE dbo.Numbers
GO
CREATE TABLE dbo.Numbers (PkNumber int identity(1,1) primary key clustered, dude bit null)
GO
INSERT INTO dbo.Numbers (dude)
SELECT NULL FROM (SELECT TOP 100 NULL AS A FROM master.dbo.spt_values) dt100 cross join (SELECT TOP 80 null AS A FROM master.dbo.spt_values) dt80
GO
ALTER TABLE dbo.Numbers
DROP COLUMN dude
GO
--Select min(PkNumber) as MinA, max(PkNumber) as MaxA from dbo.Numbers
--1, 8000
GO
--DROP TABLE dbo.Numbers
September 19, 2005 at 2:55 pm
WORKED ME GREAT REMI, THANKS
September 19, 2005 at 3:01 pm
HTH, make sure that the numbers table is high enough. I'm assuming here that the qty of products sold on a single order may go higher than 8000.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply