A followup question: if i only need the years span of 2000-2050, how do i select them instead of

SELECT TOP 97199 ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) FROM CTE5

Thanks

In fact, my CTE tally table is a calendar table (note: TALLY(N) AS (SELECT DATEADD(month, N, 0) FROM CTE6) ) of months starting at 1900-01-01 and ending with 9999-12-01 (total...

I don't think your table is normalized. Your have two entities, cars and parts. One car can have several parts, on part can be used in several cars. That's an...

ah, solved it with lots of unions... sometimes it helps to write it in text...

ill post the query in a sec, if someone else needs it.

UPDATE: here's the query....

Nice! This did the trick.

Oh, thought it looked easy but, your query had pretty complex sql structure (atleast for my current level).

I just have to learn CTE...*sigh*

Two companies have the same groupid, (they are a part of a bigger company), so i want to count them as one company.

groupid=0 represents "no group". So two companies with...

SELECT branch, count(groupid) as num from companies group by branch

gives this set:

branch11

branch22

branch31

branch42

btw, added another tuple to make it more clear.

CREATE TABLE [companies](

[id] [int] IDENTITY(1,1) NOT NULL,

[branch] [nvarchar](50) NULL,

[groupid] [int] NULL

) ON [PRIMARY]

GO

ALTER TABLE [companies] ADD CONSTRAINT [DF_companies_groupid] DEFAULT ((0)) FOR [groupid]

GO

INSERT INTO companies (branch, groupid) values('branch1', 0)

INSERT...

Ah thanks! Rowcount was indeed the problem, interesting that it resets with debug on.

thanks a bunch.

@SGT_squeequal - ty for the article it was very useful, im working on redoing the...

@RBarryYoung - It did'nt do any difference stabilizing it with ORDER BY and TOP 1, any other ideas?

@SGT_squeequal - The CTE solution looked really clean. Unfortunatly im not...

Hmm is it an absolute must to use the virtual tables u and uf?

Great suggestion, luckly they are constrained to 1-4.

Interesting, think it might work, ill give it a shot!

Thanks! 🙂

You are right, its nonexistant, i want to combine two columns selection and period into as number column

select cast(selection as varchar(1))+cast(period as varchar(1)) as number from #users

join.png:

Found this @ microsoft, which supports the idea that more cached data is better.

Microsoft

---

By default, SQL Server dynamically grows and shrinks the size of its buffer pool (cache) depending on...