Information générale
Forum:
Microsoft SQL Server
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
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement