SQL Table lookup - 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)

SQL Table lookup

Author

Topic

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-04-22 : 06:34:20

Hi,

I have the following table:

Date, Week_No
08/07/2008, 1
15/07/2008, 2

I would like to create a new field called 'day' which looks like the following:

Date, Week_No, Day
08/07/2008, 1, 1
09/07/2008, 1, 2
10/07/2008, 1, 3
ETC
15/07/2008, 2

Could anyone offer any advice on how to write a query on how to do this? Would be most greatful.

Thanks

Cipriani

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 06:41:03

UPDATE f
SET Day = recID
FROM (
SELECT Day, ROW_NUMBER() OVER (PARTITION BY Week_no ORDER BY Date) AS recID
FROM Table1
) AS f

E 12°55'05.63"
N 56°04'39.26"

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-04-22 : 06:49:25

Hi,

Thanks for the reply, i get error saying 'error invalid column name 'day''

Any ideas?

quote:
Originally posted by Peso

UPDATE f
SET Day = recID
FROM (
SELECT Day, ROW_NUMBER() OVER (PARTITION BY Week_no ORDER BY Date) AS recID
FROM Table1
) AS f

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 06:50:16

Have you created the column Day in your table yet?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 06:50:57

ALTER TABLE MyTable
ADD [Day] TINYINT
GO

E 12°55'05.63"
N 56°04'39.26"

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-04-22 : 06:55:23

hi,

that didnt work, it just added '1' to every column

quote:
Originally posted by Peso

Have you created the column Day in your table yet?

E 12°55'05.63"
N 56°04'39.26"

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-04-22 : 07:03:49

UPDATE f
SET days = recID
FROM (
SELECT days, ROW_NUMBER() OVER (PARTITION BY acad_week ORDER BY week_comm) AS recID
FROM dates08
) AS f

That was my query, and i basically got;

Date, Week_No, day
08/07/2008, 1, 1
15/07/2008, 2, 1

What im after is;
Date, Week_No, day
08/07/2008, 1, 1
09/07/2008, 1, 2
10/07/2008, 1, 3
-> (Right upto the next date below)
15/07/2008, 2

Any ideas?

quote:
Originally posted by Peso

UPDATE f
SET Day = recID
FROM (
SELECT Day, ROW_NUMBER() OVER (PARTITION BY Week_no ORDER BY Date) AS recID
FROM Table1
) AS f

E 12°55'05.63"
N 56°04'39.26"

aprichard
Yak Posting Veteran

62 Posts

Posted - 2009-04-22 : 07:41:45

The posted data are not in clear to understand , can u post the date with clear format

cipriani1984
Constraint Violating Yak Guru

304 Posts

Posted - 2009-04-22 : 07:46:10

week_comm, acad_week
2008-09-08 00:00:00.000, 7
2008-09-15 00:00:00.000, 8
2008-09-22 00:00:00.000, 9

and then id like the table to look like;

week_comm, acad_week, days
2008-09-08 00:00:00.000, 7, 1
2008-09-09 00:00:00.000, 7, 2
2008-09-10 00:00:00.000, 7, 3
AND SO ON...
2008-09-15 00:00:00.000, 8
2008-09-22 00:00:00.000, 9

Thanks

quote:
Originally posted by aprichard

The posted data are not in clear to understand , can u post the date with clear format

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 07:51:01

Oh, you want more records too?

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-04-22 : 07:56:22

[code]CREATE TABLE #Sample
(
week_comm DATETIME,
acad_week TINYINT
)

INSERT #Sample
SELECT '2008-09-08', 7 UNION ALL
SELECT '2008-09-15', 8 UNION ALL
SELECT '2008-09-22', 9

SELECT *
FROM #Sample

-- Suggestion starts here
ALTER TABLE #Sample
ADD [Day] TINYINT
GO

INSERT #Sample
(
week_comm,
acad_week,
[Day]
)
SELECT DATEADD(DAY, v.Number - 1, s.week_comm),
s.acad_week,
v.Number
FROM #Sample AS s
INNER JOIN master..spt_values AS v ON v.Type = 'P'
WHERE Number BETWEEN 2 AND 7

UPDATE #Sample
SET [Day] = 1
WHERE [Day] IS NULL
-- Suggestion ends here

SELECT *
FROM #Sample
ORDER BY acad_week,
[Day]

DROP TABLE #Sample[/code]

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources