Why don't you use the DATEDIFF() function?

What's the goal you're trying to achieve?

Thomas Abraham (1/20/2012)

---

...

Funny you should mention that. I'm taking my wife to Greece this Summer for two weeks. (Trying to talk her into retiring there.) That's where I would have...

Something like this?

DECLARE @xml XML

SET @xml='<Policies>

<Policy>

<GroupUserName>BUILTIN\Administradores</GroupUserName>

<GroupUserId>AQIAAAAAAAUgAAAAIAIAAA==</GroupUserId>

<Roles>

<Role><Name>Administrador de contenido</Name></Role>

</Roles>

</Policy>

<Policy>

<GroupUserName>MEMSYS\EMEJIA</GroupUserName>

<GroupUserId>AQUAAAAAAAUVAAAAVss+Zs3s+svCUho7wgQAAA==</GroupUserId>

<Roles>

<Role><Name>Administrador de contenido</Name></Role>

<Role><Name>Explorador</Name></Role>

<Role><Name>Generador de informes</Name></Role>

<Role><Name>Mis informes</Name></Role>

<Role><Name>Publicador</Name></Role>

</Roles>

</Policy>

</Policies>'

;

WITH cte AS

(

SELECT

c.value ('(GroupUserName/text())[1]','VARCHAR(255)') GroupUserName,

v.value ('(Name/text())[1]','VARCHAR(255)') Name

FROM @xml.nodes ('Policies/Policy') T(c)

CROSS APPLY c.nodes('Roles/Role') U(v)

)

SELECT

GroupUserName,

STUFF(

(SELECT ',...

Referring to your first link: He asked for a...

There are a few thing I noticed:

a) In your WHERE clause you use CAST to convert receiptdate into a char value and compare it with @FromDate and @ToDate.

It's already in...

Is there any chance for you to post the actual execution plan as asked for before?

Otherwise all we can do is guessing and provide some rather vague recommendation...

meelan (1/21/2012)

So, 96GB is going to be just luxury and not used by Sql. Since the price difference between 64Gb and 96GB is not much here, i will choose 96GB...

Another reason might be the varchar value is in a different date format than the DATEFORMAT setting of SQL Server for the code block/session the query is used in.

Example: the...

Depending on the SQL Server version you're planning to use, not all of the RAM can be used (e.g. Standard edition is limited to 64GB RAM as per Microsoft).

The next...

Please have a look at the CrossTab and DynamicCrossTab article referenced in my signature.

Those might help you to find a solution.

However, I second Gus: it should be done at the...

I'd start with first shredding the xml document:

DECLARE @xml XML

SET @xml=

'<STUDENTLIST>

<STUDENT>

<NAME>Doe</NAME>

<COURSE>Math</COURSE>

<COURSE>Computers</COURSE>

</STUDENT>

<STUDENT>

<NAME>Rick</NAME>

<COURSE>Science</COURSE>

<COURSE>Math</COURSE>

<COURSE>English</COURSE>

</STUDENT>

</STUDENTLIST>'

SELECT

T.c.value('(NAME/text())[1]','VARCHAR(30)') AS StudentName,

U.v.value('(./text())[1]','VARCHAR(30)') AS StudentCourse

INTO #temp

FROM @xml.nodes('STUDENTLIST/STUDENT') T(c)

CROSS APPLY T.c.nodes('COURSE') U(v)

Based on that I'd insert the grouped values for...

I think you could wrap the original call into a TRY ... CATCH block and don't raise an error at the CATCH section.

jpdp101 (1/14/2012)

---

Thanks for replies..

I think i forgot to mention one important thing, Table_Main has an unique index on for Col_A and Col_B..

does this mean the query did an Index...

Ok, 2008 version definitely makes it a little easier...

Here's my completed version. Please note that I didn't use your latest sample data since it would have taken too long to...

Steve Jones - SSC Editor (1/14/2012)

---

shivani.kataria (1/13/2012)

---

...

Year is part of date and records are just seven days apart, and that makes it weekly. 14th Jan is missing for both and...