May 22, 2008 at 1:27 am
**Sorry for the double post but i think i put this in the wrong place originally**
Hi,
I am totally new to SQL Server 2000 - i have breif experience with oracle and SQL Server 2005 but only in simple terms.
I have been thrown into using SQL Server 2000 (Via Terradata Queryman - which isnt a very newbie friendly interface ) for a retail fashion store. To say i'm a bit lost would be an understatement!
I had a (to me a least) complex query to write, which none of the methods i learned in my other breif database experience worked for - CTE & RANK () etc didn't seem to work and the sub query's i wrote seemed to get "skipped" when i executed the query - the results returned as if i hadn't included a sub query...
So i was quite pleased to actually get the query written and working although i know to experts it's probably as horrific to look at my code as it is to look at herpes or something!
Any pointers on how to tidy it up would be a great help in understanding how to write better code for the future.
DECLARE @WrkWK1 INT
DECLARE @WrkWK2 INT
DECLARE @WrkWK3 INT
DECLARE @WrkWK4 INT
SET @WrkWK1 =
(
SELECT cal.wm_yr_wk
FROM t_calendar cal
WHERE CONVERT(varchar(12),cal.gregorian_date,101) = dateadd(wk,datediff(wk,7,getdate()),0)
)
SET @WrkWK1 =
(
SELECT CAST(@WrkWK1 AS INT)
)
SET @WrkWK2 =
(
SELECT CAST(@WrkWK1 AS INT) - 1
)
SET @WrkWK3 =
(
SELECT CAST(@WrkWK2 AS INT) - 1
)
SET @WrkWK4 =
(
SELECT CAST(@WrkWK3 AS INT) - 1
)
SELECT epos.Itemnbr, SUM(epos.EposUnits) AS EU, SUM(epos.EposRetail) AS ER, SUM(epos.EposCost) AS EC, MIN(epos.wrkwk) AS MIN, MAX(epos.wrkwk) AS MAX, d.itemdesc
INTO #WK1
FROM T_EposHist epos, t_itemdetail d
WHERE epos.wrkwk = @WrkWK1
AND epos.itemnbr = d.primeitemnbr
GROUP BY epos.itemnbr, d.itemdesc
ORDER BY EU DESC
SELECT epos.Itemnbr, SUM(epos.EposUnits) AS EU, SUM(epos.EposRetail) AS ER, MIN(epos.wrkwk) AS MIN, MAX(epos.wrkwk) AS MAX
INTO #WK2
FROM T_EposHist epos, #WK1 w
WHERE epos.wrkwk = @WrkWK2
AND epos.ItemNbr = w.ItemNbr
GROUP BY epos.itemnbr
ORDER BY EU DESC
SELECT epos.Itemnbr, SUM(epos.EposUnits) AS EU, SUM(epos.EposRetail) AS ER, MIN(epos.wrkwk) AS MIN, MAX(epos.wrkwk) AS MAX
INTO #WK3
FROM T_EposHist epos, #WK1 w
WHERE epos.wrkwk = @WrkWK3
AND epos.ItemNbr = w.ItemNbr
GROUP BY epos.itemnbr
ORDER BY EU DESC
SELECT epos.Itemnbr, SUM(epos.EposUnits) AS EU, SUM(epos.EposRetail) AS ER, MIN(epos.wrkwk) AS MIN, MAX(epos.wrkwk) AS MAX
INTO #WK4
FROM T_EposHist epos, #WK1 w
WHERE epos.wrkwk = @WrkWK4
AND epos.ItemNbr = w.ItemNbr
GROUP BY epos.itemnbr
ORDER BY EU DESC
SELECT w.Itemnbr, d.itemdesc, d.ItemColour, w.EU, w.ER, w.EC, w.MIN, w.MAX, d.DeptNbr
INTO #WK11
FROM #WK1 w, T_itemdetail d
WHERE d.PrimeItemNbr = w.ItemNbr
AND d.deptnbr IN (1, 3, 7, 18)
ORDER BY EU DESC
SELECT w.Itemnbr, w.EU, w.ER, w.MIN, w.MAX, d.DeptNbr
INTO #WK22
FROM #WK2 w, T_itemdetail d
WHERE d.PrimeItemNbr = w.ItemNbr
AND d.deptnbr IN (1, 3, 7, 18)
ORDER BY EU DESC
SELECT w.Itemnbr, w.EU, w.ER, w.MIN, w.MAX, d.DeptNbr
INTO #WK33
FROM #WK3 w, T_itemdetail d
WHERE d.PrimeItemNbr = w.ItemNbr
AND d.deptnbr IN (1, 3, 7, 18)
ORDER BY EU DESC
SELECT w.Itemnbr, w.EU, w.ER, w.MIN, w.MAX, d.DeptNbr
INTO #WK44
FROM #WK4 w, T_itemdetail d
WHERE d.PrimeItemNbr = w.ItemNbr
AND d.deptnbr IN (1, 3, 7, 18)
ORDER BY EU DESC
DROP TABLE #WK1
DROP TABLE #WK2
DROP TABLE #WK3
DROP TABLE #WK4
SELECT TOP 20 w.Itemnbr, w.itemdesc, w.ItemColour, s.OnHandUnits, t.TraitedStores, t.ValidTraitedStores, w.EU, w.ER, w.EC, w.MIN, w.MAX, w.DeptNbr
INTO #WK1TOP20
FROM #WK11 w, t_StoreStockHist s, t_Traits t
WHERE (s.wrkwk = w.max
AND s.ItemNbr = w.ItemNbr)
AND t.PrimeItemNbr = w.ItemNbr
ORDER BY w.EU DESC
SELECT TOP 20 w.Itemnbr, w.itemdesc, w.ItemColour, w.OnHandUnits, w.TraitedStores, w.ValidTraitedStores, sh.Traits, sh.instock, w.EU, w.ER, w.EC, w.MIN, w.MAX, w.DeptNbr
FROM #WK1TOP20 w, t_InstockHist sh
WHERE (sh.wrkwk = @wrkwk1 AND sh.itemNbr = w.ItemNbr)
ORDER BY w.EU DESC
SELECT w.Itemnbr, w.EU, w.ER, w.MIN, w.MAX, w.DeptNbr
FROM #WK22 w, #WK1TOP20 t
WHERE t.ItemNbr = w.ItemNbr
ORDER BY w.EU DESC
SELECT w.Itemnbr, w.EU, w.ER, w.MIN, w.MAX, w.DeptNbr
FROM #WK33 w, #WK1TOP20 t
WHERE w.ItemNbr = t.ItemNbr
ORDER BY w.EU DESC
SELECT TOP 20 w.Itemnbr, w.EU, w.ER, w.MIN, w.MAX, w.DeptNbr
FROM #WK44 w, #WK1TOP20 t
WHERE w.ItemNbr = t.ItemNbr
ORDER BY w.EU DESC
DROP TABLE #WK1TOP20
DROP TABLE #WK11
DROP TABLE #WK22
DROP TABLE #WK33
DROP TABLE #WK44
Oh queryman lets you run multiple select's and returns the information on seperate tabs - hence the 4 select's at the end. I have also just been told that in future it may need to use a user inputted date from excel as @WrkWK1 then the other 3 @WrkWK's would calculate from that.
HUGE thanks in advance
Viewing post 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply