I am not criticizing the people here, they certainly know more
about SQL Server than I do. All of the code they write is similar
to the code below. I am supposed to modified this. I was hired as
a FoxPro programmer, there is no more FoxPro to do so they have me
doing SQL Server, which I don't mind.
They use temporay tables in most of their programs. Would views be better?
I am trying to learn how to index views, and cons to this?
They warehouse for a lot of big companies. The servers are used to the max.



PROCEDURE dbo.pr_Report_Ops_TotalPicksPerSKU

@fulfillmentid int,
@whseid varchar(3),
@StartDate as datetime,
@EndDate as datetime

AS

-- Data Source for the Total Picks Per SKU Report

DECLARE @ShortName varchar(30)

SET @StartDate= CONVERT(datetime,CONVERT(varchar,@StartDate,101) + ' 12:00:00 AM')
SET @EndDate = CONVERT(datetime,CONVERT(varchar,@EndDate,101) + ' 11:59:59 PM')

SET ROWCOUNT 0

SET @ShortName = (SELECT short_name FROM fulfillment WHERE fulfillment_id = @fulfillmentid)

SELECT
convert( varchar,endtime,101) as pickdate, sku, qty as quantity, fromloc

INTO
#Tempfile
FROM
wms_taskdetail with (nolock)
WHERE
whseid = @whseid
AND storerkey = @ShortName
AND endtime between @StartDate and @EndDate
and tasktype = 'PK'
ORDER BY
convert( varchar,endtime,101), sku

SELECT
pickdate, sku, fromloc, count(sku) as picks, sum(quantity) as quantity
INTO
#tempfile2
FROM
#tempfile
GROUP BY
pickdate, sku, fromloc
ORDER BY
pickdate, sku

SELECT
t.sku, t.fromloc, sum(t.picks) as picks, sum(t.quantity) as quantity, s.skugroup
FROM
#tempfile2 t, wms_sku s with (nolock)
WHERE
t.sku = s.sku
GROUP BY
t.sku, t.fromloc, s.skugroup
ORDER BY
t.picks desc, t.sku asc