Use ROW_NUMBER and FULL JOIN.

It will be easier if you post your three view definitions and we might be able to come up with something clever for you.

Will not work for "small" times, as 115 (00:01:15)

Use this

DECLARE@rundate INT,

@runtime INT,

@duration INT

SELECT@rundate = 20080504,-- May 4, 2008

@runtime = 42412,-- 4:24:12 AM,

@duration = 115-- 00:01:15

SELECTrundate,

runtime,

duration,

rundate + runtime AS start,

rundate +...

Use this function to create the calendar table

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=61519

It has tons of date limits to try out.

Is week number really OK?

I guess I am spoiled and used to ISO standard for week numbering.

CREATE TABLE #T (DT DATETIME)

INSERT INTO #T(DT)

SELECT '2008-12-30'

UNION SELECT '2008-12-31'

UNION SELECT '2008-12-29'

UNION SELECT '2008-12-28'

UNION...

bdewulf (1/13/2009)

---

I'm getting an incorrect syntax error when I do From dbo.Calendar. I am using MS Sql 2000 and I don't see this table.

Just to clarify my original post....

Not to mention 6.5 which had no support for TOP operator... 😀

First OP have to define what a week means to him/her.

Thank you!

I have that link on my favorites sites collection.

BTW, are yo having a bad hair day? (Just saw your avatar) 😉

Lynn Pettis (1/8/2009)

---

Didn't someone write an article that talked about how DATEDIFF and DATEADD worked? I can't find one on SSC, so it may have been an article on...

New requirements?

SELECTModules.ID.value('.', 'VARCHAR(20)') AS Module123,

Modules.ID.value('(../../Feature)[1]', 'VARCHAR(50)') AS Feature123a,

Modules.ID.value('(../../Feature)[2]', 'VARCHAR(50)') AS Feature123b,

Modules.ID.value('(../../Access)[1]', 'VARCHAR(50)') AS Access123a,

Modules.ID.value('(../../Access)[2]', 'VARCHAR(50)') AS Access123b

FROM@UserLFAMap_Rejected.nodes('/Modules/Module/ID') as Modules(ID)

Well, that is something different if you have different clients, not customers.

If you host your application to HMV and to VirginStore, there is a good reason to have the two...

I would have all customers and all inventory in one table and have date as one key column and customer id as one key column.

See here http://www.sqlteam.com/article/datediff-function-demystified how DATEDIFF works.

SELECT CASE DATEDIFF(MONTH, Col1, GETDATE())

WHEN 0 THEN 'Same month'

WHEN 1 THEN 'Prior month'

ELSE 'Some other month'

END AS MonthDescription

FROM Table1

Have one table and add a "date" column to it.

Deltas are easily managed anyway with this

select *

from (select * from table1 where date = ... ) as t1

full join...

I would try to install 2008 on all server, so that development-, test- and production-environments have same platform.