Comma Separated values using XML PATH in SQL Query

5 pts.
Tags:
SQL Query
XMLPATH
Hi This is my first time to post my complex query here I am facing the critical problem for comma separated values in the column using XML PATH Let me explain you in brief now I have a table CallDetailReport table with below mentioned structure which stores values like
CallStartDateTime[DateTime] NodeTraversed[NVarchar(MAX)]

2014-09-22 03:44:33 Srinivas;vasu;lakshmi;srini;srini
2014-09-20 09:42:00 vasu;kumar;raj;
2014-09-21 23:43:11 Srinivas;srini
2014-09-22 12:33:44 krishh;raj;kumar;Srinivas;srini;
2014-09-22 01:33:33 vasu;srini;lakshmi;raj;krishh;Srinivas;
2014-09-21 05:11:09 krishh;raj;srini
The above table contains lakhs of records per day. I need the output like getdate()-1 records to "NodeTraversed" table NoOfHits in below is nothing but count of each NodeTraversed
NodeTraversed Table -output required below:
-----------------------------------------------------------
CallStartDateTime[varchar] MenuName[varchar(100)] NoOfHits[INT]

2014-09-22 Srinivas 3

2014-09-22 vasu 2

2014-09-22 lakshmi 2

2014-09-22 srini 4
Every time i will get previous day records in this way. Please help me how to write query for this type of output. The below query which is mentioned below is working only for few records but not for lakhs of records.And the count is getting completely wrong with below query.
select * into #temp
from
(
SELECT convert(varchar,t1.CallStartDateTime,101) as date,
NodeTraversed=STUFF(
(SELECT ';' + NodeTraversed
FROM Call_detail_Report t2
WHERE convert(varchar,t1.CallStartDateTime,101) = convert(varchar,t2.CallStartDateTime,101)
FOR XML PATH (''))
, 1, 1, '')
FROM Call_detail_Report t1 where convert(varchar,CallStartDateTime,112) = convert(varchar,getdate()-1,112)
GROUP BY convert(varchar,t1.CallStartDateTime,101)
)t

IF DATEPART (HH,GETDATE()) in
(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20,21,22,23)
INSERT INTO [UIDAI_IVR_DB].[dbo].[Node_Traversed_Summary_Report]
select t2.date as CallStartDate,t2.item as MenuName,count(t2.item)as NoOfHits
FROM
(
SELECT * from #temp
cross apply dbo.DelimitedSplit8K_T1(#temp.NodeTraversed,';')
)t2
group by t2.date,t2.item
order by t2.date

ELSE IF DATEPART (HH,GETDATE()) in (0)
INSERT INTO [UIDAI_IVR_DB].[dbo].[Node_Traversed_Summary_Report]
select t2.date as CallStartDate,t2.item as MenuName,count(t2.item)as NoOfHits
FROM
(
SELECT * from #temp
cross apply dbo.DelimitedSplit8K_T1(#temp.NodeTraversed,';')
)t2
group by t2.date,t2.item
order by t2.date
drop table #temp


Software/Hardware used:
SQL query
0

Answer Wiki

Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Discuss This Question:  

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.

Following

Share this item with your network: