Date Question - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Date Question

Author

Topic

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-06-01 : 14:52:53

Hi Guys

I am currently using a table to reference dates from during the production of a query.

The table has 3 columns, all with a datetime format. The table resembles
ID, BeginDate, EndDate
01/01/2008,01/01/2008,25/01/2008
01/02/2008,01/02/2008,29/02/2008
01/03/2008,03/03/2008,28/03/2008
...
...

In the following query I am referencing the table to check data between the Begin and End fields depending on the ID field.

Create PROCEDURE [dbo].[getNumberOfOrderLinesPerOrder]
(@Period DATETIME)
AS

DECLARE @BeginDate DATETIME
DECLARE @EndDate DATETIME

SELECT @BeginDate = [BeginDate] FROM DatePeriods AS dp WHERE dp.ID = @Period
SELECT @EndDate = [EndDate] FROM DatePeriods AS dp WHERE dp.ID = @Period

SELECT
1
,'1 Order Line' AS 'Number of Copies'
,'BERR' AS 'Family'
,COUNT(*) AS 'Orderlines'
FROM(

SELECT
(oh.[Sales Order Reference])
,COUNT(ol.[Sales Order Reference]) AS 'Orderlines'
FROM dbo.OrderHeaders AS oh
LEFT JOIN dbo.OrderLines AS ol ON oh.[Sales Order Reference] = ol.[Sales Order Reference]
LEFT JOIN dbo.StockHeaders AS sh ON ol.[Part Number] = sh.[Part Number]
LEFT JOIN dbo.StockCategories AS sc ON sh.[Stock Category] = sc.[Stock Category]
WHERE oh.[Order Date] BETWEEN @BeginDate AND @EndDate
AND sc.[Stock Group] IN (SELECT sg.[Group] FROM dbo.StockGroup AS sg WHERE sg.[ID] = 'B')
AND ol.[Sales Order Reference] NOT IN (SELECT col.[Sales Order Reference]
FROM dbo.CancelledOrderLines AS col
WHERE col.[Part Number] = ol.[Part Number])

GROUP BY oh.[Sales Order Reference]
HAVING COUNT (*) = 1
) AS OrderLines

As you can see the @BeginDate variable is referencing the BeginDate field of ID.

The thing I am trying to do is reference the BeginDate 11 months back of the ID chosen. So if i chose the ID of 01/04/2008 it would use the BeginDate of the ID 01/05/2008 (i hope that makes sense :D)

Is this possible?

Thanking you in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-01 : 14:56:13

Is this what you're looking at?

SELECT @BeginDate = [BeginDate] FROM DatePeriods AS dp WHERE dp.ID = DATEADD(mm,-11,@Period)

rcr69er
Constraint Violating Yak Guru

327 Posts

Posted - 2008-06-05 : 04:03:43

Hey

Thanks for that!!!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-05 : 04:18:04

quote:
Originally posted by rcr69er

Hey

Thanks for that!!!

You're welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources