Or this:

SELECT

active_flag = CASE WHEN c.active_flag = 1 THEN 'Active' ELSE 'Inactive' END,

c.name,

c.company_code,

d.active_users,

d.new_users,

d.inactive_users,

test_company_flag,

parent_company_flag

FROM dbo.company c

LEFT JOIN (

SELECT

company_code,

active_users= SUM(CASE WHEN active_flag = 1 AND unregister_flag = 0 THEN 1...

Divya Agrawal (4/18/2013)

---

If there is a csv column having different number of comma's in it, how would CCA help in it. In the examples provided by you we can split...

-- Group the data by month and by weeks, where a week runs from sunday to saturday

-- account for a week spanning two months

SELECT

v.SchedProv, x.Weekno, x.[Month],

EarliestApptForWeek =...

Dwain, Jeff - you guys crack me up! As if I didn't have enough pressure from the escaped dust bunnies 😀

Thank you very much, Divya!

Can you show what you mean by...

The simple answer is -...

Tim Walker. (4/16/2013)

---

Interesting, entertaining and educational with worked examples and performance statistics make this a great read.

The fact that it is also genuinely very useful and will help me to...

Thanks Jeff! And thank you for the encouragement to put pen to paper. DC's been nagging me for ages. Once you stepped in...

Here's an iTVF which might do what you want. You will need to check that the xml concatenation can deal with the characters you wish to include:

ALTER FUNCTION [dbo].[ifn_RemoveUnwantedChars]

(@strTarget...

Pothiraj R (4/16/2013)

---

Hi Chris,

Thanks for the query. I didn't use the syntax like this.

FROM (VALUES ('ooo'),('bbb'),('ccc'),('ddd'),('eee'),('fff'),('ggg'),('hhh'),('iii'),('jjj'),('kkk'),('lll')) d (name)

In the same scenario, I will use UNION ALL with SELECT or insert...

Any time.

mrinal.technology (4/16/2013)

---

Hi,

I have a SQL query with couple of nested queries (query at the bottom for the reference), now the very first time I execute the query on a SQL...

pnr8uk (4/16/2013)

---

Well it works beautifully so does that mean the productid on the Catalogue table is messed up?

The script looks like a thing of beauty and works nicely....

Intrigued...

pnr8uk (4/16/2013)

---

Will do Chris - sorry didn't realise there was such a great article as yours, I wondered how so many people got such neat data in the forum.

Will be...