SELECT STATEMENT - 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 2000 Forums

Transact-SQL (2000)

SELECT STATEMENT

Author

Topic

DENIZ3E
Yak Posting Veteran

56 Posts

Posted - 2005-06-20 : 11:51:51

FÝRSTLY I WANT TO CREATE THE SELECT STATEMENT BY USÝNG TABLE1 AND TABLE 2

SELECT TABLE1.FICHENO,TABLE1.STOCKREF,TABLE2.DUEDATE,@PROCDATE
FROM TABLE2 TABLE2

----

TABLE2.FICHENO=FICHENO(IN TABLE 2)
TABLE2.STOCKREF=STOCKREF (IN TABLE 2)
TABLE2.DUEDATE= IN TABLE2

@PROCDATE =
1. @WEEK=DATEPART(WW,TABLE2.DUEDATE) THEN
2. SELECT @DAY=W + @WEEK FROM TABLE1 WHERE W + @WEEK =@WEEK AND STOCKREF=TABLE2.STOCKREF
3. SELECT @PROCDATE=DATEADD(DD,-@DAY,TABLE2.DUEDATE)

CREATE TABLE [TABLE1] ([LOGICALREF] [int] NOT NULL ,
[STOCKREF] [int] NULL ,
[FIRE] [decimal](18, 1) NULL ,
[W1] [int] NULL ,
[W2] [int] NULL ,
[W3] [int] NULL ,
[W4] [int] NULL ,
[W5] [int] NULL ,
[W6] [int] NULL ,
[W7] [int] NULL ,
[W8] [int] NULL ,
[W9] [int] NULL ,
[W10] [int] NULL ,
[W11] [int] NULL ,
[W12] [int] NULL ,
[W13] [int] NULL ,
[W14] [int] NULL ,
[W15] [int] NULL ,
[W16] [int] NULL ,
[W17] [int] NULL ,
[W18] [int] NULL ,
[W19] [int] NULL ,
[W20] [int] NULL ,
[W21] [int] NULL ,
[W22] [int] NULL ,
[W23] [int] NULL ,
[W24] [int] NULL ,
[W25] [int] NULL ,
[W26] [int] NULL ,
[W27] [int] NULL ,
[W28] [int] NULL ,
[W29] [int] NULL ,
[W30] [int] NULL ,
[W31] [int] NULL ,
[W32] [int] NULL ,
[W33] [int] NULL ,
[W34] [int] NULL ,
[W35] [int] NULL ,
[W36] [int] NULL ,
[W37] [int] NULL ,
[W38] [int] NULL ,
[W39] [int] NULL ,
[W40] [int] NULL ,
[W41] [int] NULL ,
[W42] [int] NULL ,
[W43] [int] NULL ,
[W44] [int] NULL ,
[W45] [int] NULL ,
[W46] [int] NULL ,
[W47] [int] NULL ,
[W48] [int] NULL ,
[W49] [int] NULL ,
[W50] [int] NULL ,
[W51] [int] NULL ,
[W52] [int] NULL ,
[W53] [int] NULL
) ON [PRIMARY]
GO

CREATE TABLE [TABLE2] (
[LOGICALREF] [int] NOT NULL ,
[STOCKREF] [int] NULL ,
[ORDFICHEREF] [int] NULL ,
[DATE_] [datetime] NULL ,
[DUEDATE] [datetime] NULL ,
CONSTRAINT [TABLE2_I1] PRIMARY KEY CLUSTERED
(
[LOGICALREF]
) ON [PRIMARY]
) ON [PRIMARY]
GO

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2005-06-20 : 14:43:01

I don't know what you're trying to do, I don't even know what your question is. But heck, that's never stopped me from expressing an opinion so how about this:

Change table1 to have 53 rows instead of 53 columns.
then you can do something like this:

select PROCDATE=DATEADD(DD,-a.DayCol, b.DUEDATE)
from table1 a
join table2 b on datepart(week, b.duedate) = datepart(week, a.<dateCol>)

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources