September 22, 2014 at 1:42 pm
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
September 24, 2014 at 5:26 am
Hi,
I would suggest using a Tally-table (numbers-table) for this:
-- table definition and data
declare @CallDetailReport table
(
CallStartDateTime datetime,
NodeTraversed nvarchar(MAX)
)
insert into @CallDetailReport (CallStartDateTime, NodeTraversed)
select '2014-09-22 03:44:33', 'Srinivas;vasu;lakshmi;srini;srini'
union all select '2014-09-20 09:42:00', 'vasu;kumar;raj;'
union all select '2014-09-21 23:43:11', 'Srinivas;srini'
union all select '2014-09-22 12:33:44', 'krishh;raj;kumar;Srinivas;srini;'
union all select '2014-09-22 01:33:33', 'vasu;srini;lakshmi;raj;krishh;Srinivas;'
union all select '2014-09-21 05:11:09', 'krishh;raj;srini'
--=============================================================================
-- Create and populate a Tally table
-- (code-snippet from http://www.sqlservercentral.com/articles/T-SQL/62867/)
--=============================================================================
--===== Conditionally drop
IF OBJECT_ID('dbo.Tally') IS NOT NULL
DROP TABLE dbo.Tally
--===== Create and populate the Tally table on the fly
SELECT TOP 11000 --equates to more than 30 years of dates
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
--===== Add a Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
--=============================================================================
-- Main query to group by date (and remove nodes visited only once for the date)
select convert(char(10), CallStartDateTime, 120), Node, count(*)
from (
-- Find words separated by ";" in NodeTraversed
select CallStartDateTime, substring(R.NodeTraversed, T.N, MIN(T2.N-2) - T.N + 1) AS Node
from @CallDetailReport R
inner join dbo.Tally T
on substring(';' + R.NodeTraversed, T.N, 1) = ';'
and T.N < len(R.NodeTraversed) -- ignore when ";" is last character
inner join dbo.Tally T2
on substring(';'+ R.NodeTraversed + ';', T2.N, 1) = ';'
and T2.N > T.N
group by CallStartDateTime, R.NodeTraversed, T.N
) X
group by convert(char(10), CallStartDateTime, 120), Node
having count(*) > 1 -- remove nodes visited only once for the date
order by 1, 2
Good luck!
/M
September 24, 2014 at 9:50 am
Hi
Thank you very much.The above query is working fine.
Now the problem it is taking more time to execute 2lakhs of records per day.
For 60rows = 6sec
200000rows=approximately 4hrs
Please help me now in this.
how can we fix the above query to execute faster.
Once again Thank you very much for your time and valuable query.
Regards
Srinivas.
September 26, 2014 at 7:24 am
Thanks a lot.We have changed the tally table query which is going inside twice.
As of now it is working fine.Its just taking 2min for executing 2lakhs records.
Thank you very much for your help.
Regards
Srinivas.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply