Remove the GROUP BY. You're getting totals for every status when you only want the grand total.

Drew

Have you tried adding a ROOT directive? That seems to remove most of the xmlns prefixes, but it may not give you the structure that you need.

Drew

The xlmns is being included because you've specified the XSINIL directive. If you don't want it included, you'll have to get rid of the XSINIL directive.

Drew

RonMexico (11/23/2016)

---

whenriksen -

The example I provided is code for a report we are taking over from another group. The first goal is to convert it to ANSI-92 style joins...

As a quick test, I decided to compare doing a direct T-SQL CAST versus using XML to do the CAST. Also, the XML is essentially doing a double cast:...

That is basic SQL. You should be able...

I have to ask WHY are you doing your conversions through XML?

The problem is that column Y is in scientific notation, and the xml does not recognize scientific notation as...

sqlfriends (11/23/2016)

---

thank you. So far the last 30 days we consider it as calendar days similar like last month.

But suppose I have a school calendar table, it looks like for...

ETL is a process, not a tool. You'll need to let us know what tool you are using for your ETL process.

SELECT *

INTO TableName

FROM SourceTable

uses the same data types...

It may have "worked" but it is likely to perform horribly. You should compare it to LEAD/LAG before settling on that as the...

This language is T-SQL, not...

There are three issues that I see immediately.

1) In your CTE, you do not provide an alias for the ROW_NUMBER() expression.

2) You should be using LEAD/LAG instead of ROW_NUMBER() with...

He didn't like the answers he got on his original thread, so he started another thread. I pointed out repeatedly that he already had the answer he was seeking...

The following two expressions are essentially equivalent.

COALESCE (ISNULL(k.Application, ''), ISNULL(n.Application, ''))

ISNULL(k.Application, '')

COALESCE evaluates the expressions in order until it finds one that is not null or it runs out of...

Reported as spam.