derived table versus correlated subquery 1

[Ach005ki]

Hi all

I was interested in SqlSister's response to a question in which she stated that a derived table would allow returning more than one result whereas a correlated subquery precludes this (I presume unless the subquery is the data for an IN / NOT IN clause ?)

I have a query of items in an item table that has to report on several different stock-holding and sales data aggregates; I was using correlated subqueries because I can't create views and am not sure if a view would help in any case. One subquery has to return several pieces of data from the sales history table and so I strung them together in order to break them down into separate columns when the results are exported to excel.

Would a derived table serve me better, and can I make use of one?

I'm using MS Query from Office 2003 on an SQL Server 2000 installation (running on Windows Server 2003 I believe).

My problem subquery is [blue]highlighted[/blue] in the code below ... if any other parts can be tweeaked / optimised then please don't hold back; let me know!
[code:]
SELECT
InvMaster.StockCode AS 'StockCode', -- from Inventory Master file
InvMaster.ProductClass AS 'PrdClass',
SalProdClass.Description AS 'PrdClassDescription', -- from SalesAnalysis ProductClass file
InvMaster.Description AS 'Desc',
InvMaster.LongDesc AS 'ExtraDescription',
InvMaster.AlternateKey1 AS 'PackSize', -- userdefined field used to denote an items packsize, e.g compliment slips are in packs of 50, 1 unit = pack of 50
InvWarehouse.QtyOnHand AS 'InStock', -- from Inventory Warehouse file; each client's stock is held under different warehouse codes
Max(InvFifoLifo.LastReceiptDate) AS 'LastReceipt',
HaveStock =
CASE InvWarehouse.QtyOnHand
WHEN 0 THEN 0
ELSE 1
END, -- allows the summation of whether or not the item is in stock (to be used as a SUM in a pivot table for a count function)
OnWeb =
CASE AFDWeb.AlphaValue -- customform data item (criteria in where clause) from table AdmFormData which is a table used again as a nested select...
WHEN 'Y' THEN 'Yes'
WHEN 'N' THEN 'No'
ELSE '???'
END, -- allows the summation of whether or not the item is visible to client on the web (to be used as a SUM in a pivot table for a count function)

/* HERE BEGINS MY SERIES OF NESTED SELECTS - I APPRECIATE THESE MAY BE BETTER SERVED AS VIEWS (OR EVEN SPs) but I can't create views with my level of access
**
** 1st nested select (correlated subquery?) to return a count of bin locations which are pallet-spaces (others are pick-faces)
**
** Uses InvMaster.StockCode as the code for which to obtain the number of the appropriate bin locations where stock is present for that client
**
*/
(
SELECT
count(InvMultBin.Bin)
FROM
SysproCompanyH.dbo.InvMultBin InvMultBin
WHERE
InvMultBin.StockCode = InvMaster.StockCode
AND InvMultBin.Bin like 'F%'
AND InvMultBin.QtyOnHand1 >$0
) AS 'PalletCount',

/* 2nd nested select returns a subdivision of the ProductClass, and combines this with the client-owner
** (client owner of a subdivision, or of ProductClass where there is no subdivision)
**
** Product Class "subdivision" and "client owner" are new data items introduced by the client.
** Our dba advised there were no remaining fields available to link this to existing productclass data
** we had to use the CustomFormField capability of the Syspro system to introduce two extra fields (per stock item in InvMaster)
** these extra fields are stored in the AdmFormData table whose Keyfield is linked to the InvMaster.StockCode field
**
** this portion uses the AdmFormData table and links to itself as an alias to pik up both fields in the same pass
** both fields are then concatenated into one returned value passed back to the main query
*/

(
SELECT
(RTRIM(AFD.AlphaValue)+' : '+RTRIM(AFD_1.AlphaValue))
FROM
SysproCompanyH.dbo.AdmFormData AFD
INNER JOIN SysproCompanyH.dbo.AdmFormData AFD_1
ON AFD.KeyField = AFD_1.KeyField
AND AFD.FormType = AFD_1.FormType
WHERE
(
(AFD.KeyField = InvMaster.StockCode)
AND (AFD.FieldName='SubDiv')
AND (AFD_1.FieldName='Owner')
)
) AS 'PC-Ctg-Owner',

-- 3rd nested select to return the 12wkusage of the stock item, taken from the InvMovements table
(
SELECT
SUM(TrnQty)
FROM
SysproCompanyH.dbo.InvMovements InvMovements
WHERE
InvMovements.StockCode = InvMaster.StockCode
AND (MovementType = 'S')
AND (EntryDate >= DATEADD([DAY], - 83, { fn CURDATE() }))
AND (EntryDate <= DATEADD([DAY], 0, { fn CURDATE() }))
) As 'Usage12Wks',

[blue]

/* HELP REQUIRED HERE
** 4th nested select to return different elements of sales order throughput for the last 7 days
** Because nested selects (correlated subqueries) can only return one value I've had to string the values together
** and when the results are returned to excel I extrapolate the values based on the position of the changing delimiters
** delimiters are , ; : #
*/

(
SELECT
RTRIM(LTRIM(STR(SUM(SorDetail.MOrderQty))))+ ',' +
RTRIM(LTRIM(STR(SUM(SorDetail.MBackOrderQty)))) + ';' +
RTRIM(LTRIM(STR(SUM(SorDetail.MQtyDispatched)))) + ':' +
RTRIM(LTRIM(STR(SUM(SorDetail.MShipQty)))) + '#' +
RTRIM(LTRIM(STR(SUM(SorDetail.MStockQtyToShp))))
FROM
SysproCompanyH.dbo.SorDetail SorDetail
INNER JOIN SysproCompanyH.dbo.SorMaster SorMaster
ON SorDetail.SalesOrder = SorMaster.SalesOrder
AND SorMaster.Warehouse = 'BC'
WHERE
SorDetail.MStockCode = InvMaster.StockCode
AND SorMaster.Warehouse = 'BC'
AND SorMaster.OrderStatus NOT IN ('*', '\') -- these are cancelled-order statuses
AND SorDetail.MProductClass <> '_FRT'
AND (DATEDIFF(DAY, SorMaster.OrderDate, GETDATE()) BETWEEN 7 AND 13)
/*
** I'll refine this later so that it always configures the date range to be from the previous Monday to the previous Sunday's dates.
*/
) As 'Last7Days'

[/blue]

FROM
SysproCompanyH.dbo.InvMaster InvMaster

INNER JOIN SysproCompanyH.dbo.InvWarehouse InvWarehouse
ON InvMaster.StockCode = InvWarehouse.StockCode

INNER JOIN SysproCompanyH.dbo.SalProductClassDes SalProdClass
ON InvMaster.ProductClass = SalProdClass.ProductClass

LEFT OUTER JOIN SysproCompanyH.dbo.AdmFormData AFDWeb
ON InvMaster.StockCode = AFDWeb.KeyField
AND AFDWeb.FieldName = 'WEBRQD'

LEFT OUTER JOIN SysproCompanyH.dbo.InvFifoLifo InvFifoLifo
ON InvWarehouse.StockCode = InvFifoLifo.StockCode
AND InvWarehouse.Warehouse = InvFifoLifo.Warehouse

WHERE
InvWarehouse.Warehouse='BC'

GROUP BY
InvMaster.StockCode,
InvMaster.Description,
InvMaster.LongDesc,
InvMaster.AlternateKey1,
InvMaster.ProductClass,
SalProdClass.Description,
InvWarehouse.QtyOnHand,
AFDWeb.AlphaValue

ORDER BY
InvMaster.StockCode
[/code]

Mark, HH Associates

 

[gmmastros]

This is a pretty complicated query to try and modify without being able to test this. So, this may not be perfect, but I hope you can see the method used to convert your sub query to a derived table.

SELECT 
   InvMaster.StockCode AS 'StockCode', -- from Inventory Master file
   InvMaster.ProductClass AS 'PrdClass',
   SalProdClass.Description AS 'PrdClassDescription', -- from SalesAnalysis ProductClass file
   InvMaster.Description AS 'Desc', 
   InvMaster.LongDesc AS 'ExtraDescription', 
   InvMaster.AlternateKey1 AS 'PackSize', -- userdefined field used to denote an items packsize, e.g compliment slips are in packs of 50, 1 unit = pack of 50
   InvWarehouse.QtyOnHand AS 'InStock', -- from Inventory Warehouse file; each client's stock is held under different warehouse codes
   Max(InvFifoLifo.LastReceiptDate) AS 'LastReceipt', 
   HaveStock = 
    CASE InvWarehouse.QtyOnHand
        WHEN 0 THEN 0
        ELSE 1
    END, -- allows the summation of whether or not the item is in stock (to be used as a SUM in a pivot table for a count function)
   OnWeb = 
    CASE AFDWeb.AlphaValue -- customform data item (criteria in where clause) from table AdmFormData which is a table used again as a nested select...
        WHEN 'Y' THEN 'Yes'
        WHEN 'N' THEN 'No'
        ELSE '???'
    END, -- allows the summation of whether or not the item is visible to client on the web (to be used as a SUM in a pivot table for a count function)

/* HERE BEGINS MY SERIES OF NESTED SELECTS - I APPRECIATE THESE MAY BE BETTER SERVED AS VIEWS (OR EVEN SPs) but I can't create views with my level of access
** 
** 1st nested select (correlated subquery?) to return a count of bin locations which are pallet-spaces (others are pick-faces)
** 
** Uses InvMaster.StockCode as the code for which to obtain the number of the appropriate bin locations where stock is present for that client
**
*/
   (
    SELECT 
        count(InvMultBin.Bin)
    FROM 
        SysproCompanyH.dbo.InvMultBin InvMultBin 
    WHERE 
        InvMultBin.StockCode = InvMaster.StockCode
        AND InvMultBin.Bin like 'F%' 
        AND InvMultBin.QtyOnHand1 >$0 
   ) AS 'PalletCount',

/* 2nd nested select returns a subdivision of the ProductClass, and combines this with the client-owner
** (client owner of a subdivision, or of ProductClass where there is no subdivision)
**
** Product Class "subdivision" and "client owner" are new data items introduced by the client.
** Our dba advised there were no remaining fields available to link this to existing productclass data
** we had to use the CustomFormField capability of the Syspro system to introduce two extra fields (per stock item in InvMaster)
** these extra fields are stored in the AdmFormData table whose Keyfield is linked to the InvMaster.StockCode field
**
** this portion uses the AdmFormData table and links to itself as an alias to pik up both fields in the same pass
** both fields are then concatenated into one returned value passed back to the main query
*/

   (
     SELECT 
        (RTRIM(AFD.AlphaValue)+' : '+RTRIM(AFD_1.AlphaValue))
     FROM 
        SysproCompanyH.dbo.AdmFormData AFD
        INNER JOIN SysproCompanyH.dbo.AdmFormData AFD_1
          ON AFD.KeyField = AFD_1.KeyField 
          AND AFD.FormType = AFD_1.FormType
     WHERE 
        (
          (AFD.KeyField = InvMaster.StockCode) 
          AND (AFD.FieldName='SubDiv') 
          AND (AFD_1.FieldName='Owner')
        )
   ) AS 'PC-Ctg-Owner',

-- 3rd nested select to return the 12wkusage of the stock item, taken from the InvMovements table
   (
     SELECT 
        SUM(TrnQty) 
     FROM 
        SysproCompanyH.dbo.InvMovements InvMovements
     WHERE 
        InvMovements.StockCode = InvMaster.StockCode
        AND (MovementType = 'S')
        AND (EntryDate >= DATEADD([DAY], - 83, { fn CURDATE() })) 
        AND (EntryDate <= DATEADD([DAY], 0, { fn CURDATE() }))
   )  As 'Usage12Wks',
   [!]Last7Days.SumOrderQty[/!],
   [!]Last7Days.SumBackOrderQty[/!],
   [!]Last7Days.SumQtyDispatched[/!],
   [!]Last7Days.SumShipQty[/!],
   [!]Last7Days.SumStopQtyToShp[/!]

FROM 
   SysproCompanyH.dbo.InvMaster InvMaster
   
   INNER JOIN SysproCompanyH.dbo.InvWarehouse InvWarehouse 
     ON InvMaster.StockCode = InvWarehouse.StockCode 
     
   INNER JOIN SysproCompanyH.dbo.SalProductClassDes SalProdClass
     ON InvMaster.ProductClass = SalProdClass.ProductClass
     
   LEFT OUTER JOIN SysproCompanyH.dbo.AdmFormData AFDWeb
     ON InvMaster.StockCode = AFDWeb.KeyField
     AND AFDWeb.FieldName = 'WEBRQD'
     
   LEFT OUTER JOIN SysproCompanyH.dbo.InvFifoLifo InvFifoLifo 
     ON InvWarehouse.StockCode = InvFifoLifo.StockCode 
     AND InvWarehouse.Warehouse = InvFifoLifo.Warehouse
[!]   INNER JOIN 
     (
       SELECT 
          SorDetail.MStockCode,
          SUM(SorDetail.MOrderQty) As SumOrderQty,
          SUM(SorDetail.MBackOrderQty) As SumBackOrderQty,
          SUM(SorDetail.MQtyDispatched) As SumQtyDispatched,
          SUM(SorDetail.MShipQty) As SumShipQty,
          SUM(SorDetail.MStockQtyToShp) As SumStopQtyToShp
       FROM 
          SysproCompanyH.dbo.SorDetail SorDetail
          INNER JOIN SysproCompanyH.dbo.SorMaster SorMaster
          ON SorDetail.SalesOrder = SorMaster.SalesOrder
          AND SorMaster.Warehouse = 'BC'
       WHERE
          AND SorMaster.Warehouse = 'BC'
          AND SorMaster.OrderStatus NOT IN ('*', '\')  -- these are cancelled-order statuses
          AND SorDetail.MProductClass <> '_FRT'
          AND (DATEDIFF(DAY, SorMaster.OrderDate, GETDATE()) BETWEEN 7 AND 13)
     )  As Last7Days
     On InvMaster.StockCode = Last7Days.MStockCode
[/!]
WHERE 
   InvWarehouse.Warehouse='BC'  
   
GROUP BY 
   InvMaster.StockCode, 
   InvMaster.Description, 
   InvMaster.LongDesc,
   InvMaster.AlternateKey1, 
   InvMaster.ProductClass, 
   SalProdClass.Description,
   InvWarehouse.QtyOnHand, 
   AFDWeb.AlphaValue

ORDER BY 
   InvMaster.StockCode

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[Ach005ki]

Thank you George - I'll give this a whirl and post up my findings (I'm pretty hopeful of this working).

Mark

Mark, HH Associates

 

[Ach005ki]

Hi George

The derived tabe approach worked well once I'd corrected one or two problems, such as my typo on "WHERE AND SorMaster.Warehouse" - a rogue AND!, and adding thedata from the derived table to the group by list at the end, etc.

I've also gone on to modify the derived table part to always process sales order data based on the preceding week's orders, regardless of when (on what day) in the current week the report is run.

I replaced

AND (DATEDIFF(DAY, SorMaster.OrderDate, GETDATE()) BETWEEN 7 AND 13)

with

AND (SorMaster.OrderDate BETWEEN dateadd(dd, 1-datepart(weekday, getdate()), getdate()-8) 
		AND dateadd(dd, 1-datepart(weekday, getdate()), getdate()-1))

If anyone knows how to improve or simplify this (or any other part of my) code then please let your experience shine.

I'm grateful to Sqlsister for mentioning derived tables; I'm extremely grateful to you George for your time and effort in showing me how to create a derived table using my code; I'm also very grateful to SQL Server Helper for their article on returning the first day of the current week, which I used to determine the first day of the previous week:

http://www.sql-server-helper.com/functions/get-first-day-of-week.aspx

Before "stumbling" across the above article, I was trying to use a case statement in the WHERE clause - and pulling my hair out with the error messages and lack of progress. God Bless those who help others, thank you sincerely!

If anyone is interested in seeing the completed, revised code, its here; derived table in blue and first day of previous week in red:

SELECT 
	InvMaster.StockCode AS 'StockCode', -- from Inventory Master file
	InvMaster.ProductClass AS 'PrdClass',
	SalProdClass.Description AS 'PrdClassDescription', -- from SalesAnalysis ProductClass file
	InvMaster.Description AS 'Desc', 
	InvMaster.LongDesc AS 'ExtraDescription', 
	InvMaster.AlternateKey1 AS 'PackSize', -- userdefined field used to denote an items packsize, e.g compliment slips are in packs of 50, 1 unit = pack of 50
	InvWarehouse.QtyOnHand AS 'InStock', -- from Inventory Warehouse file; each client's stock is held under different warehouse codes
	Max(InvFifoLifo.LastReceiptDate) AS 'LastReceipt', 
	HaveStock = 
	 CASE InvWarehouse.QtyOnHand
		  WHEN 0 THEN 0
		  ELSE 1
	 END, -- allows the summation of whether or not the item is in stock (to be used as a SUM in a pivot table for a count function)
	OnWeb = 
	 CASE AFDWeb.AlphaValue -- customform data item (criteria in where clause) from table AdmFormData which is a table used again as a nested select...
		  WHEN 'Y' THEN 'Yes'
		  WHEN 'N' THEN 'No'
		  ELSE '???'
	 END, -- allows the summation of whether or not the item is visible to client on the web (to be used as a SUM in a pivot table for a count function)

/* HERE BEGINS MY SERIES OF NESTED SELECTS - I APPRECIATE THESE MAY BE BETTER SERVED AS VIEWS (OR EVEN SPs) but I can't create views with my level of access
** 
** 1st nested select (correlated subquery?) to return a count of bin locations which are pallet-spaces (others are pick-faces)
** 
** Uses InvMaster.StockCode as the code for which to obtain the number of the appropriate bin locations where stock is present for that client
**
*/
	(
	 SELECT 
		  count(InvMultBin.Bin)
	 FROM 
		  SysproCompanyH.dbo.InvMultBin InvMultBin 
	 WHERE 
		  InvMultBin.StockCode = InvMaster.StockCode
		  AND InvMultBin.Bin like 'F%' 
		  AND InvMultBin.QtyOnHand1 >$0 
	) AS 'PalletCount',

/* 2nd nested select returns a subdivision of the ProductClass, and combines this with the client-owner
** (client owner of a subdivision, or of ProductClass where there is no subdivision)
**
** Product Class "subdivision" and "client owner" are new data items introduced by the client.
** Our dba advised there were no remaining fields available to link this to existing productclass data
** we had to use the CustomFormField capability of the Syspro system to introduce two extra fields (per stock item in InvMaster)
** these extra fields are stored in the AdmFormData table whose Keyfield is linked to the InvMaster.StockCode field
**
** this portion uses the AdmFormData table and links to itself as an alias to pik up both fields in the same pass
** both fields are then concatenated into one returned value passed back to the main query
*/

	(
	  SELECT 
		  (RTRIM(AFD.AlphaValue)+' : '+RTRIM(AFD_1.AlphaValue))
	  FROM 
		  SysproCompanyH.dbo.AdmFormData AFD
		  INNER JOIN SysproCompanyH.dbo.AdmFormData AFD_1
			 ON AFD.KeyField = AFD_1.KeyField 
			 AND AFD.FormType = AFD_1.FormType
	  WHERE 
		  (
			 (AFD.KeyField = InvMaster.StockCode) 
			 AND (AFD.FieldName='SubDiv') 
			 AND (AFD_1.FieldName='Owner')
		  )
	) AS 'PC-Ctg-Owner',

-- 3rd nested select to return the 12wkusage of the stock item, taken from the InvMovements table
	(
	  SELECT 
		  SUM(TrnQty) 
	  FROM 
		  SysproCompanyH.dbo.InvMovements InvMovements
	  WHERE 
		  InvMovements.StockCode = InvMaster.StockCode
		  AND (MovementType = 'S')
		  AND (EntryDate >= DATEADD([DAY], - 83, { fn CURDATE() })) 
		  AND (EntryDate <= DATEADD([DAY], 0, { fn CURDATE() }))
	)  As 'Usage12Wks',
	Last7Days.SumOrderQty,
	Last7Days.SumBackOrderQty,
	Last7Days.SumQtyDispatched,
	Last7Days.SumShipQty,
	Last7Days.SumStopQtyToShp

FROM 
   SysproCompanyH.dbo.InvMaster InvMaster
   
   INNER JOIN SysproCompanyH.dbo.InvWarehouse InvWarehouse 
     ON InvMaster.StockCode = InvWarehouse.StockCode 
     
   INNER JOIN SysproCompanyH.dbo.SalProductClassDes SalProdClass
     ON InvMaster.ProductClass = SalProdClass.ProductClass
     
   LEFT OUTER JOIN SysproCompanyH.dbo.AdmFormData AFDWeb
     ON InvMaster.StockCode = AFDWeb.KeyField
     AND AFDWeb.FieldName = 'WEBRQD'
     
   LEFT OUTER JOIN SysproCompanyH.dbo.InvFifoLifo InvFifoLifo 
     ON InvWarehouse.StockCode = InvFifoLifo.StockCode 
     AND InvWarehouse.Warehouse = InvFifoLifo.Warehouse
   LEFT OUTER JOIN 
[blue]
     (
       SELECT 
          SorDetail.MStockCode AS 'OrdStockCode',
          SUM(SorDetail.MOrderQty) As SumOrderQty,
          SUM(SorDetail.MBackOrderQty) As SumBackOrderQty,
          SUM(SorDetail.MQtyDispatched) As SumQtyDispatched,
          SUM(SorDetail.MShipQty) As SumShipQty,
          SUM(SorDetail.MStockQtyToShp) As SumStopQtyToShp
       FROM 
          SysproCompanyH.dbo.SorDetail SorDetail
          INNER JOIN SysproCompanyH.dbo.SorMaster SorMaster
          ON SorDetail.SalesOrder = SorMaster.SalesOrder
          AND SorMaster.Warehouse = 'BC'
       WHERE
          SorMaster.Warehouse = 'BC'
          AND SorMaster.OrderStatus NOT IN ('*', '\')  -- these are cancelled-order statuses
          AND SorDetail.MProductClass <> '_FRT'
          AND 
[/blue][red]
	  (SorMaster.OrderDate BETWEEN dateadd(dd, 1-datepart(weekday, getdate()), getdate()-8) 
		AND dateadd(dd, 1-datepart(weekday, getdate()), getdate()-1))
[/red][blue]
		  GROUP BY
			 SorDetail.MStockCode
	
     )  As Last7Days
     On InvMaster.StockCode = Last7Days.OrdStockCode
[/blue]
WHERE 
   InvWarehouse.Warehouse='BC'  
   
GROUP BY 
   InvMaster.StockCode, 
   InvMaster.Description, 
   InvMaster.LongDesc,
   InvMaster.AlternateKey1, 
   InvMaster.ProductClass, 
   SalProdClass.Description,
   InvWarehouse.QtyOnHand, 
   AFDWeb.AlphaValue,
	Last7Days.SumOrderQty,
   Last7Days.SumBackOrderQty,
   Last7Days.SumQtyDispatched,
   Last7Days.SumShipQty,
   Last7Days.SumStopQtyToShp

ORDER BY 
   InvMaster.StockCode

Mark, HH Associates

 

[gmmastros]

There's a potential problem with that code (the part in red). If you read Books On Line carefully, it mentions the problem, but, of course, I'll also mention it. Honestly, I'm surprised that the article you referenced didn't mention this.

The problem is that there are countries around the globe that use different starting days of the week. So, depending on your regional settings, the first day of the week may not be Sunday. In the United States, the default first day of the week is 7. To determine what your server is configured for, you can run this...

Select @@DateFirst

To illustrate this problem, you can copy/paste this to Query Analyzer.

[COLOR=blue]Set[/color] [COLOR=#FF00FF]DateFirst[/color] 7
[COLOR=blue]select[/color] [COLOR=#FF00FF]dateadd[/color](dd, 1-[COLOR=#FF00FF]datepart[/color]([COLOR=#FF00FF]weekday[/color], [COLOR=#FF00FF]getdate[/color]()), [COLOR=#FF00FF]getdate[/color]()-8),[COLOR=#FF00FF]dateadd[/color](dd, 1-[COLOR=#FF00FF]datepart[/color]([COLOR=#FF00FF]weekday[/color], [COLOR=#FF00FF]getdate[/color]()), [COLOR=#FF00FF]getdate[/color]()-1)

[COLOR=blue]Set[/color] [COLOR=#FF00FF]DateFirst[/color] 3
[COLOR=blue]select[/color] [COLOR=#FF00FF]dateadd[/color](dd, 1-[COLOR=#FF00FF]datepart[/color]([COLOR=#FF00FF]weekday[/color], [COLOR=#FF00FF]getdate[/color]()), [COLOR=#FF00FF]getdate[/color]()-8),[COLOR=#FF00FF]dateadd[/color](dd, 1-[COLOR=#FF00FF]datepart[/color]([COLOR=#FF00FF]weekday[/color], [COLOR=#FF00FF]getdate[/color]()), [COLOR=#FF00FF]getdate[/color]()-1)

[COLOR=blue]Set[/color] [COLOR=#FF00FF]DateFirst[/color] 5
[COLOR=blue]select[/color] [COLOR=#FF00FF]dateadd[/color](dd, 1-[COLOR=#FF00FF]datepart[/color]([COLOR=#FF00FF]weekday[/color], [COLOR=#FF00FF]getdate[/color]()), [COLOR=#FF00FF]getdate[/color]()-8),[COLOR=#FF00FF]dateadd[/color](dd, 1-[COLOR=#FF00FF]datepart[/color]([COLOR=#FF00FF]weekday[/color], [COLOR=#FF00FF]getdate[/color]()), [COLOR=#FF00FF]getdate[/color]()-1)

You'll see that as the DateFirst is changed, you get different results. As I stated, this may not affect you because you could be writing software for a single company where you can completely control the server settings. Sadly, this isn't always true for everyone (think yours truely). Nonetheless, you can safeguard against this by including the line...
[tt][blue]Set DateFirst 7[/blue][/tt]
at the top of this query.

When you change the DateFirst setting, it only affects the query results for the duration of the connection to the database. Essentially, by setting the DateFirst, you will not cause any problems with any other queries or any other users.

As far as the rest of your query is concerned, I don't see any glaring problems. I'm sure there is probably room for improvement, but unless there are performance issues, I probably wouldn't change it.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom