Clustered Index
-
I have a stored procedure that creates a clustered index on a temp table which works great. Now I want to change the index to be able to use it in another stored procedure but I need for one of the columns to be DESC.
Here is the clustered index:
create clustered index pn_loc_recvdate on #ReceiptsPhysical(part_no, location, recv_date)
what I want is the recv_date to be descending so I tried several different ways and it didn't work. Can anyone please tell me how to do this?
Thanks for any help.
Melanie Norton
-
This seemed to work
create table #ReceiptsPhysical
(part_no int
, location char( 2)
, recv_date datetime
)
create clustered index pn_loc_recvdate on #ReceiptsPhysical(part_no, location, recv_date desc)
insert #ReceiptsPhysical
select 1, 'A', getdate()
insert #ReceiptsPhysical
select 2, 'B', getdate()
insert #ReceiptsPhysical
select 3, 'C', getdate()
insert #ReceiptsPhysical
select 4, 'D', getdate()
insert #ReceiptsPhysical
select 1, 'A', getdate()
select *
from #ReceiptsPhysical
Steve Jones
-
can you explain why you want it in descending order ?
-
Steve,
I tried your suggestion and I still keep getting the same error message. I'm thinking it's maybe the version that I'm use of SQL Server. I'm using 6.5 until we upgrade to 7.0. I'm enclosing my stored procedure. Maybe you can see something that I can't. Thanks for any help you can provide.
create procedure ih_fifo_calculation @begindate datetime, @enddate datetime
as
set nocount on
--insert into #ReceiptsPhysical
create table #ReceiptsPhysical
(po_no varchar (12) NULL,
part_no varchar (30) NOT NULL ,
location varchar (10) NOT NULL ,
recv_date datetime NOT NULL ,
cost real NULL ,
quantity decimal(20, 8) NOT NULL ,
conv_factor decimal(20,8) NOT NULL,
qty_physical decimal(20, 8) NOT NULL ,
balance real NULL )
create clustered index pn_loc_recvdate on #ReceiptsPhysical(part_no, location, recv_date desc)
Insert into #ReceiptsPhysical
select distinct r.po_no,r.part_no,r.location,r.recv_date,r.unit_cost,r.quantity,r.conv_factor,p.qty as phy_qty, r.quantity*0.00 as balance
from receipts r
join physical p
on r.part_no = p.part_no and r.location = p.location
where recv_date between @begindate and @enddate AND p.qty > 0.0
/*The cursor below loops through the records to calculate the balance for the records
to be used in calculating fifo*/
--declare the cursor
DECLARE pn_loc_cursor CURSOR
FOR
SELECT part_no,location,quantity,conv_factor,phy_qty FROM #ReceiptsPhysical
--declare the variables to be used inside the cursor
DECLARE @part_no varchar(30),
@location varchar(10),
@quantity decimal(20,8),
@conv_factor decimal(20,8),
@phy_qty decimal(20,8),
@Balance decimal(20,8),
@bal decimal(20,8),
@cumbal decimal(20,8),
@pn varchar(30),
@loc varchar(10)
--open the cursor
OPEN pn_loc_cursor
--get the first row
FETCH NEXT FROM pn_loc_cursor INTO @part_no,@location,@quantity,@conv_factor,@phy_qty
select @pn = @part_no
select @loc = @location
select @cumbal = @phy_qty
WHILE (@@fetch_status <> -1)
BEGIN
IF @pn = @part_no and @loc = @location --detects if on a new part_no/location
BEGIN
IF @cumbal > @quantity*@conv_factor
Begin
Select @bal = @quantity*@conv_factor
Select @cumbal = @cumbal - (@quantity*@conv_factor)
End
ELSE
Begin
Select @bal = @cumbal
Select @cumbal = 0.0
End
UPDATE #ReceiptsPhysical
SET balance = @bal
WHERE CURRENT OF pn_loc_cursor
FETCH NEXT FROM pn_loc_cursor INTO @part_no,@location,@quantity,@conv_factor,@phy_qty
END
ELSE
BEGIN
SELECT @pn = @part_no
SELECT @loc = @location
SELECT @cumbal = @phy_qty
IF @cumbal > @quantity*@conv_factor
Begin
Select @bal = @quantity*@conv_factor
Select @cumbal = @cumbal - (@quantity*@conv_factor)
End
ELSE
Begin
Select @bal = @cumbal
Select @cumbal = 0.0
End
UPDATE #ReceiptsPhysical
SET balance = @bal
WHERE CURRENT OF pn_loc_cursor
FETCH NEXT FROM pn_loc_cursor INTO @part_no,@location,@quantity,@conv_factor,@phy_qty
END
END
DEALLOCATE pn_loc_cursor
--Get the records needed for fifo that equal the quantity on hand
select part_no,
location,
po_no,
recv_date,
(quantity * conv_factor) as recv_qty,
balance as on_hand,
(case when conv_factor > 1 then (unit_cost/conv_factor) else (conv_factor*unit_cost)end)as cost,
(case when conv_factor > 1 then (unit_cost/conv_factor) else (conv_factor*unit_cost)end)* balance as extended
into #fifotable
from #ReceiptsPhysical
where balance > 0.0
create clustered index pn_loc_recvdate on #fifotable(part_no, location, recv_date)
--Get the records from previous end of year for items that don't have any purchases this year
insert into #fifotable
select distinct p.part_no,
p.location,
'PreviousYr' as po_no,
costdate as recv_date,
p.qty as recv_qty,
p.qty as on_hand,
a.avg_cost as cost,
p.qty*a.avg_cost as extended
from physical p
join inv_master i
on p.part_no = i.part_no
join atco_year_end_costs a
on p.part_no = a.part_no and p.location = a.location
left join #ReceiptsPhysical t
on p.part_no = t.part_no and p.location = t.location
where t.part_no is null and p.qty > 0 and i.category = 'R'
truncate table ih_lifo_fifo_temp
--return the lifo results
insert into ih_lifo_fifo_temp
select distinct part_no,
location,
'NO DESC',
recv_date,
on_hand,
recv_qty,
cost,
po_no,
extended
from #fifotable
--added into a permanent table and to add description
update ih_lifo_fifo_temp
set description = inv_master.description
from inv_master
where inv_master.part_no = ih_lifo_fifo_temp.part_no
select * from ih_lifo_fifo_temp
order by part_no, location, recv_date DESC
GO
Thanks,
Melanie Norton
-
Hey Steve,
I just tried it on our test server that we are performing our upgrade to SQL Server 7.0 on and the stored procedure works. Thanks for your help.
Melanie Norton
-
-
Hey Steve,
Have you by chance written any sql books?
Thanks,
Melanie
-
lol, I wrote the Sybex SQL 2000 Design Study Guide:http://www.amazon.com/exec/obidos/ASIN/0782129420/qid%3D991345443/sr%3D1-8/ref%3Dsc%5Fb%5F8/102-5028696-8717706, Actually I wrote half of it. (second half)
Of course, if you read some of the user reviews of the Advanced T-SQL book and compare it to mine this week, you might not think I know anything.
. Oh well. We can't all agree on things.I assume this is a complement and not a warning to avoid my work.
Steve Jones
-
Steve,
Everyone is entitled to their opinion. Based on your opinion I may order the book because I've been looking for an advanced T-SQL book. Thanks for all of your help.
Melanie
-
. Oh well. We can't all agree on things.Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply