Vasc can give an elegent solution. I guess this will solve the problem.

SET NOCOUNT ON

DECLARE @F TABLE

(

[Key] INT NULL,

X1 INT NULL,

X2 INT NULL,

X3 INT NULL,

X4 INT NULL

)

INSERT @F

SELECT 1, 12,...

SET NOCOUNT ON

DECLARE @F TABLE

(

[Key] INT NULL,

X1 INT NULL,

X2 INT NULL,

X3 INT NULL,

X4 INT NULL

)

INSERT @F

SELECT 1, 12, 1, 2, null UNION ALL

SELECT 2, 1, 2, null, null UNION ALL

SELECT 12, 2, null, null, null UNION ALL

SELECT 14, null, null,...

That application resides on SQL Server 6.5

Still Formula step lenths are different some elements use all steps and some only a few.

How will you determine how many variables to declare

var1, var2 ... varn

Since you declare the variables already. I don't see a need for a loop to assign values to them.

Provide...

We have one such situation

One Invoice will have 100 or more elements upto 2000 now

We have to calculate the invoice amount based on a formula slected for each element

users...

CREATE PROCEDURE usp_WeekDays

(

@pMonth  INT = NULL,

@pYear  INT = NULL

)

AS

SET NOCOUNT ON

DECLARE @Days  INT

DECLARE @CurDate DATETIME

DECLARE @MyDates TABLE

(

MyDate DATETIME

)

SET DATEFORMAT MDY

SELECT @pMonth = COALESCE(@pMonth, DATEPART(MONTH, GETDATE()))

SELECT @pYear = COALESCE(@pYear, DATEPART(YEAR, GETDATE()))

SELECT @Days = 0

SET...

You are welcome(d).

Function returning dates is hard coded for year 2005. You might want to change it to the dates you need.

Here is the view with function

SET NOCOUNT ON

GO

CREATE FUNCTION myDates()

RETURNS @myDates TABLE

(

myDate DATETIME

)

AS

BEGIN

DECLARE @AA INT SET @AA = 0

WHILE @AA < 365

BEGIN

 INSERT @myDates VALUES (DATEADD(DAY, @AA, '01/01/2005'))

 SET @AA = @AA +...

Yes Noel considering if we have a table @MyDates with dates. The Select statement can be in a view.

SET NOCOUNT ON

DECLARE @myDates TABLE

(

myDate DATETIME

)

DECLARE @AA INT SET @AA = 0

WHILE @AA...

CREATE VIEW myView

AS

SELECT CASE WHEN (DATEPART(DAY,GETDATE()) < 8 AND DATENAME(WEEKDAY,GETDATE()) = 'Monday')

   OR

  (DATEPART(DAY, DATEADD(DAY, 7, GETDATE())) < 8 AND DATENAME(WEEKDAY,GETDATE()) = 'Friday')

      THEN 'EVENT'

 ELSE

  NULL

 END

To list all dates...

SELECT Product, SUM(SaleQty)/COUNT(DISTINCT CONVERT(VARCHAR, DATEPART(MONTH, SaleDate)) + CONVERT(VARCHAR, DATEPART(YEAR, SaleDate))) AvgSalesPerMonth

FROM

 orderdetail

GROUP BY  Product

Will be more accurate

DateDiff Remi posted is better since if there are no sales on a...

Noel is right Query should be

SELECT Product, SUM(SaleQty)/12.0 AvgSalesPerMonth

FROM

 orderdetail

GROUP BY  Product

Corrected in next post.

DECLARE @MyDate TABLE

(

DateID INT,

myDate DATE

)

DECLARE @myTime TABLE

(

DateID INT, -- Referencing MyDate.DateID

MyTime  TIME

)

Just wondering how to use Time DataType

I can think a date type. We may need to store just the date not time. I don't see a need to have just Time without a date. Is to...

You don't remember Remi. Check this.

http://www.sqlservercentral.com/forums/shwmessage.aspx?forumid=8&messageid=207434#bm207636