Sql Comparing two tables: guys

Showing posts with label guys. Show all posts

Saturday, February 25, 2012

Complex Query

Hi Guys

Hope you can help with this - it's certainly got me scratching my head.

I'm Querying a Call Centre Database

I have a Table of Call Data with a Start Time & End Time of the Call.

(CallID,Started,Ended)
10942086 2007-04-01 00:01:09.000 2007-04-01 00:11:31.000
1003855355 2007-04-01 00:01:24.000 2007-04-01 00:01:24.000
10942071 2007-04-01 00:01:25.000 2007-04-01 00:02:43.000
10942271 2007-04-01 00:02:57.000 2007-04-01 00:05:01.000
10942283 2007-04-01 00:05:54.000 2007-04-01 00:06:50.000
10942079 2007-04-01 00:07:15.000 2007-04-01 00:07:46.000
10942287 2007-04-01 00:07:30.000 2007-04-01 00:08:12.000
10942289 2007-04-01 00:07:49.000 2007-04-01 00:08:33.000
I'm trying to produce Stats that tell me how many Calls were live in any one given minute.

Ultimately I will be producing a Line Graph of No of Calls Connected grouped by Minute.

I've gone as far as creating a temp table with every minute in a month with the following query maybe to join to but not sure if this will help me.

WHILE(@.cnt <= 43200)
BEGIN
SELECT @.MaxDate =DATEADD(mi,1,MAX(DTBlock))FROM AprilMinutes
INSERTINTO AprilMinutes VALUES(@.MaxDate,NULL)
SET @.cnt = @.Cnt +1
END
Which produces a nifty little table with
01/04/2007 00:09:00
01/04/2007 00:10:00
01/04/2007 00:11:00
01/04/2007 00:12:00
01/04/2007 00:13:00
01/04/2007 00:14:00
01/04/2007 00:15:00
01/04/2007 00:16:00

If one individual Call Spans 2 minutes I'll count it as 1 in the first minute & 1 in the second minute.

Overall I'm trying analyze how many telephone lines we need

Any Help much, much appreciated

Thanks

GWselect AprilMinutes.datetimecolumn
, count(CallData.StartTime) as active_calls
from AprilMinutes
left outer
join CallData
on AprilMinutes.datetimecolumn
between CallData.StartTime
and CallData.EndTime
group
by AprilMinutes.datetimecolumn|||Tehe

Thanks r937 - Though art a veritable GURU of SQL'ness

My meager Server is crunching the TOP 100 as we speak (creak, grind)

However :eek:

What will happen if a call arrives at eg. 00:05:10 and Ends at 00:05:55

methinks it will not get counted ?

please tell me i'm wrong|||please tell me i'm wrongnope, thou art correct

here's an idea (too busy to test it meself): in the query round the start time down to the nearest previous minute, and round the end time up to the next minute, then instead of BETWEEN, use >= and <|||Whooooaa

Close but no Bannana's

The suggestion produces

(DateTimeBlock,CountOf)
2007-04-01 00:00:00.000 2
2007-04-01 00:01:00.000 4
2007-04-01 00:02:00.000 5
2007-04-01 00:03:00.000 3
2007-04-01 00:04:00.000 7
2007-04-01 00:05:00.000 8
2007-04-01 00:06:00.000 8
2007-04-01 00:07:00.000 8
2007-04-01 00:08:00.000 8
2007-04-01 00:09:00.000 7

From
(CallID,Start,End,FormatedStart,FormatedEnd)
10942086 2007-04-01 00:01:09.000 2007-04-01 00:11:31.000 2007-04-01 00:01:00.000 2007-04-01 00:12:00.000
10038553 2007-04-01 00:01:24.000 2007-04-01 00:01:24.000 2007-04-01 00:01:00.000 2007-04-01 00:02:00.000
10942071 2007-04-01 00:01:25.000 2007-04-01 00:02:43.000 2007-04-01 00:01:00.000 2007-04-01 00:03:00.000
10942271 2007-04-01 00:02:57.000 2007-04-01 00:05:01.000 2007-04-01 00:02:00.000 2007-04-01 00:05:00.000
10942283 2007-04-01 00:05:54.000 2007-04-01 00:06:50.000 2007-04-01 00:05:00.000 2007-04-01 00:07:00.000
10942079 2007-04-01 00:07:15.000 2007-04-01 00:07:46.000 2007-04-01 00:07:00.000 2007-04-01 00:08:00.000
10942287 2007-04-01 00:07:30.000 2007-04-01 00:08:12.000 2007-04-01 00:07:00.000 2007-04-01 00:09:00.000
10942289 2007-04-01 00:07:49.000 2007-04-01 00:08:33.000 2007-04-01 00:07:00.000 2007-04-01 00:09:00.000
10942090 2007-04-01 00:09:52.000 2007-04-01 00:13:40.000 2007-04-01 00:09:00.000 2007-04-01 00:14:00.000
10942094 2007-04-01 00:15:18.000 2007-04-01 00:15:45.000 2007-04-01 00:15:00.000 2007-04-01 00:16:00.000
10942306 2007-04-01 00:15:37.000 2007-04-01 00:16:26.000 2007-04-01 00:15:00.000 2007-04-01 00:17:00.000
10942104 2007-04-01 00:17:51.000 2007-04-01 00:18:29.000 2007-04-01 00:17:00.000 2007-04-01 00:19:00.000

I Estimate it should produce2007-04-01 00:00:00.000 0
2007-04-01 00:01:00.000 3
2007-04-01 00:02:00.000 3
2007-04-01 00:03:00.000 2
2007-04-01 00:04:00.000 2
2007-04-01 00:05:00.000 3

Do you think it will help if I post some code to create Test Tables n Data

I had hoped maybe someone familiar with a Call Centre / telephone type operation would have done this kind of thing.

I'm not sure why your suggestion does'nt work - I'll continue studying it.

:confused:

Thanks|||Oh NO !!

Everyone seems to have given up :eek: :eek: :eek: :eek:

Surely there's a SQL God out there who could make a constructive suggestion.

Come on Chaps OR I'll break out the CURSORS - tehe.

Only Joking.

SELECT CASE WHEN @.GoodSugestions >= 1
THEN
'Thankyou'
ELSE
'Struggle On'
END
Thanks to r937, you've brought me very close.

GW|||can you show the query you developed based on my suggestion in post #4|||Here's the RAW code exactly as used

select top 10 AprilMinutes.DTBlock
,count(callActionSummary.NotiTime)as active_calls
from AprilMinutes
left outer
join callActionSummary
on AprilMinutes.DTBlock >=LEFT(dbo.FormatDate(NotiTime,'yyyy-mm-dd hh:mm:ss.Ms'),16)+':00.000'
and AprilMinutes.DTBlock <DATEADD(mi,1,LEFT(dbo.FormatDate(DATEADD(ss,ssopco nn,NotiTime),'yyyy-mm-dd hh:mm:ss.Ms'),16)+':00')
group
by AprilMinutes.DTBlock
orderby AprilMinutes.DTBlock

Do you think possibly the Date manipulation / dbo.FormatDate is affecting it ?

ssopconn is Int (operator connected seconds)
NotiTime is Datetime (Time Call arrived)
dbo.FormatDate returns nVarChar (custom UDF) hopefuly implicit conversion to date ??

GW|||Do you think possibly the Date manipulation / dbo.FormatDate is affecting it ?it's a possibility ;)

actually, now that i think about it, you would only have to round the start time down to the current minute, to have DTBlock >= pick it up

then if ssopconn doesn't extend the time into the next minute, the call will still be counted in that minute

anyhow, there is a much better way to round down (would've mentioned it yesterday but i was too busy to look it up)

SELECT DATEADD(<datepart>, DATEDIFF(<datepart>, <refdatetime>, <datetime>), <refdatetime>)

which simply adds back the same number of whole dateparts to the reference datetime as there are between the reference datetime and the given datetime

the excess just disappears ;)

example:

SELECT DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) returns midnight of first day of current month (try it :))

reference date of 0 equals the sql server zero datetime, whatever that may be, jan 1 1900 or whatever

it doesn't actually matter what the exact zero datetime is, because it subtracts back out ;)

expression for your situation:

DATEADD(mi, DATEDIFF(mi, '2000-01-01', NotiTime), '2000-01-01')

reference date is chosen closer to expected values, although zero still works

this expression rounds NotiTime down to current minute and is a lot more efficient than string manipulation

:)|||select a.datetimecolumn, count(b.StartTime)
from AprilMinutes a left join CallData b
on a.datetimecolumn between convert(varchar(17),b.StartTime ,113)
and b.EndTime --dateadd(ms,59997, convert(varchar(17),b.EndTime,113) )
group by a.datetimecolumn|||Sorry Chaps

Not had time to test these yet but will do tomorrow

Someone decided to change the CodePages on the Production SyBase servers from 850 to 1252 last night & hoped it would'nt have much of an effect - :shocked: - lol

I'll keep you posted re results of your suggestions

thanks

GW|||expression for your situation:
DATEADD(mi, DATEDIFF(mi, '2000-01-01', NotiTime), '2000-01-01')
is a lot more efficient than string manipulation
Revised queryselect a.DTBlock, count(b.StartTime)
from AprilMinutes a
left join CallData b
on a.DTBlock between dateadd(mi,datediff(mi,0,b.StartTime),0)
and b.EndTime
group by a.DTBlock|||WOW :D :D :D

r937 & pdreyer - Though art Veritable GODS of SQL ness

Worked a Treat & triple fast too.

r937 had it with the Left Outer and pdreyer swooped in with the
between dateadd(mi,datediff(mi,0,b.StartTime),0) and b.EndTime

I'm humbled in your presence - Grovel, Snivel

Many Thanks to the both of you - tis muchly appreciated

Grant|||Just to Round things Off

This is what I did with the Code

ALTERPROCEDURE LineUtilisation @.FromDate DateTime, @.ToDate DateTime
AS
SETNOCOUNTON
DECLARE @.TimeBlock TABLE(DTBlock DateTime)
DECLARE @.CallData TABLE(Call_Def Int,StartTime DateTime,EndTime DateTime)
DECLARE @.MinReq Int

INSERTINTO @.TimeBlock SELECT @.FromDate
SET @.MinReq =DATEDIFF(mi,@.FromDate,@.ToDate)
WHILE(@.MinReq > 1)
BEGIN
INSERTINTO @.TimeBlock
SELECTDATEADD(mi,1,MAX(DTBlock))FROM @.TimeBlock
SET @.MinReq = @.MinReq - 1
END
INSERTINTO @.CallData
SELECT
Call_Def,
NotiTime StartTime,
DATEADD(ss,SSTotConn,NotiTime) EndTime
FROM CallActionSummary
WHERE NotiTime between @.FromDate AND @.ToDate
ANDRTRIM(LTRIM(CallCode ))NOTIN('','B','F','4','Q','H','O','P','16')-- Background
AND ssOpConn > 0
AND ReasonRef <> 5
ORDERBY 2
SELECT a.DTBlock,COUNT(b.StartTime) CallQty
FROM @.TimeBlock a
LEFTJOIN @.CallData b
ON a.DTBlock BETWEENDATEADD(mi,DATEDIFF(mi,0,b.StartTime),0)
AND b.EndTime
GROUPBY a.DTBlock
ORDERBY 1
Will Probably end up tidying it up - poss with some indexing but it works fast enough at the Mo.

PS Mars Bar for anyone who can tell me how to LOSE THE WHILE LOOP !!

GW|||Will Probably end up tidying it up some indentation wouldn't hurt, man ;)|||I don't think you need the @.CallData table and
SELECTDATEADD(mi,1,MAX(DTBlock))FROM @.TimeBlockEvery time you select max you scan the table and the more rows you add the more there is to scan
Rather do this
select @.seq=0, @.MinReq =DATEDIFF(mi,@.FromDate,@.ToDate)
WHILE(@.seq<@.MinReq)
BEGIN
INSERT INTO @.TimeBlock SELECT DATEADD(mi,@.seq,@.FromDate)
SET @.seq = @.seq + 1
END
You could avoid the while loop if you had a numbers table e.g.
select DTBlock=dateadd(mi,numbers.seqno-1,@.FromDate)
from numbers
where seqno<=DATEDIFF(mi,@.FromDate,@.ToDate)+1

You can generate a numbers table by selecting from a large table
e.g.
select top 50000 seq=identity(int,1,1) into numbers from some_big_table

and you can handle all in a single query if you join to the numbers table|||r937 - 4 Sure - looks fine in my Query Editor but everything (including spaces) is stripped off when pasting between #Code# blocks for some reason ?.

Glad it keeps the Pretty Colours though.

Yup pdreyer - using the @.seq variable is gonna make a lot of sense - not sure about a numbers table though as I prefer not to create ancilliary/metadata tables unless I absolutely have to.

I'll have a think & post back optimized Code

Thanks Chaps

GW|||Had some time to play and test what I said before.
The left join can be a killer and it seems you need an interim table

I generated this test data

create table CallActionSummary (Call_Def int,NotiTime datetime,SSTotConn int
, CallCode char(10), ssOpConn int, ReasonRef int)
insert into CallActionSummary select
10942086 ,'2007-04-01 00:01:09.000',622 ,'ok',2,1 union all select
10038553 ,'2007-04-01 00:01:24.000',0 ,'ok',2,1 union all select
10942071 ,'2007-04-01 00:01:25.000',78 ,'ok',2,1 union all select
10942271 ,'2007-04-01 00:02:57.000',124 ,'ok',2,1 union all select
10942283 ,'2007-04-01 00:05:54.000',56 ,'ok',2,1 union all select
10942079 ,'2007-04-01 00:07:15.000',31 ,'ok',2,1 union all select
10942287 ,'2007-04-01 00:07:30.000',42 ,'ok',2,1 union all select
10942289 ,'2007-04-01 00:07:49.000',44 ,'ok',2,1 union all select
10942090 ,'2007-04-01 00:09:52.000',228 ,'ok',2,1 union all select
10942094 ,'2007-04-01 00:15:18.000',27 ,'ok',2,1 union all select
10942306 ,'2007-04-01 00:15:37.000',49 ,'ok',2,1 union all select
10942104 ,'2007-04-01 00:17:51.000',38 ,'ok',2,1 union all select
1 ,'2007-04-01 02:17:51.000',38 ,'ok',2,1 --union all select

insert into CallActionSummary select Call_Def,dateadd(hh,1,NotiTime),SSTotConn ,'ok',2,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(hh,5,NotiTime),SSTotConn ,'ok',2,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(hh,12,NotiTime),SSTotConn ,'ok',3,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,1,NotiTime),SSTotConn ,'ok',3,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,2,NotiTime),SSTotConn ,'ok',3,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,4,NotiTime),SSTotConn ,'ok',4,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,8,NotiTime),SSTotConn ,'ok',4,10 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(dd,16,NotiTime),SSTotConn ,'ok',4,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(mi,30,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(mm,-2,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
insert into CallActionSummary select Call_Def,NotiTime,SSTotConn,'16',5,1 from CallActionSummary
union all select Call_Def,NotiTime,SSTotConn,'not ok',0,1 from CallActionSummary
union all select Call_Def,NotiTime,SSTotConn,'not ok',10,5 from CallActionSummary

insert into CallActionSummary select Call_Def,dateadd(mm,-4,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(yy,-1,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary
insert into CallActionSummary select Call_Def,dateadd(yy,-4,NotiTime),SSTotConn ,'ok',5,1 from CallActionSummary

create index ix1 on CallActionSummary (NotiTime)

and then created this numbers table (You might want to include the generation of a temporary #numbers table in your proc instead)

select top 43200 seq=identity(int,1,1) into numbers
from CallActionSummary

create unique clustered index ix1 on numbers(seq)

Table sizes during test:
name rows reserved data index_size unused
------ ---- ------ ------ ------ ---
CallActionSummary 425984 28408 KB 19040 KB 9328 KB 40 KB
numbers 43200 576 KB 560 KB 16 KB 0 KB

Tried this query (1 day):

declare @.FromDate DateTime, @.ToDate DateTime
select @.FromDate='2007-04-01 00:00:00', @.ToDate='2007-04-01 23:59:59'
select dateadd(mi,seq-1,@.FromDate) DTBlock ,count(NotiTime) CallQty
into #t3 -- used to check runtime without passing data to client
from numbers t1
left join CallActionSummary t2
on t1.seq between datediff(mi,@.FromDate,NotiTime)+1
and (datediff(ss,@.FromDate,NotiTime)+SSTotConn)/60+1
and NotiTime between @.FromDate and @.ToDate
AND RTRIM(LTRIM -- rtrim,ltrim really needed?
(CallCode )) NOT IN ('','B','F','4','Q','H','O','P','16')-- Background
AND ssOpConn > 0
AND ReasonRef <> 5
where t1.seq between 1 and datediff(mi,@.FromDate,@.ToDate)+1
group by t1.seq
order by 1
drop table #t3 -- used to check runtime without passing data to client

(1440 row(s) affected)
Table 'Worktable'. Scan count 621, logical reads 831, physical reads 0, read-ahead reads 0.
Table 'CallActionSummary'. Scan count 2, logical reads 4760, physical reads 0, read-ahead reads 0.
Table 'numbers'. Scan count 2, logical reads 5, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 817, logical reads 1027, physical reads 0, read-ahead reads 0.
Table '#t3__<snip>'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
SQL Server Execution Times: CPU time = 765 ms, elapsed time = 557 ms.

Then 10 days and expected everything times 10
but the time was much worse
and logical reads on 'Worktable' was way up (what's with the worktable?)

...
select @.FromDate='2007-04-01 00:00:00', @.ToDate='2007-04-10 23:59:59'
...
(14400 row(s) affected)
Table 'Worktable'. Scan count 6935, logical reads 43697, physical reads 0, read-ahead reads 0.
Table 'CallActionSummary'. Scan count 2, logical reads 4760, physical reads 0, read-ahead reads 0.
Table 'numbers'. Scan count 2, logical reads 25, physical reads 0, read-ahead reads 0.
Table 'Worktable'. Scan count 7463, logical reads 46865, physical reads 0, read-ahead reads 0.
Table '#t3__<snip>'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
SQL Server Execution Times: CPU time = 37672 ms, elapsed time = 19758 ms.

And I killed the test on 30 days after waiting for more than 2 minutes
It seems you need an interim table to solve this

Changed query to:
declare @.FromDate DateTime, @.ToDate DateTime
select @.FromDate='2007-04-01 00:00:00', @.ToDate='2007-04-01 23:59:59'
select dateadd(mi,a.seq-1,@.FromDate) DTBlock, case when b.CallQty is null then 0 else b.CallQty end CallQty
into #t3 -- used to check runtime without passing data to client
from numbers a left join (
select seq, count(*) CallQty --count(NotiTime) CallQty
from numbers t1
join CallActionSummary t2 --with (index (ix1))
on t1.seq between datediff(mi,@.FromDate,NotiTime)+1
and (datediff(ss,@.FromDate,NotiTime)+SSTotConn)/60+1
and NotiTime between @.FromDate and @.ToDate
AND RTRIM(LTRIM -- rtrim,ltrim really needed?
(CallCode )) NOT IN ('','B','F','4','Q','H','O','P','16')-- Background
AND ssOpConn > 0
AND ReasonRef <> 5
where t1.seq between 1 and datediff(mi,@.FromDate,@.ToDate)+1
group by t1.seq
) b on a.seq=b.seq
where a.seq between 1 and datediff(mi,@.FromDate,@.ToDate)+1
order by 1
drop table #t3 -- used to check runtime without passing data to client

(1440 row(s) affected)
Table 'numbers'. Scan count 210, logical reads 451, physical reads 0, read-ahead reads 0.
Table 'CallActionSummary'. Scan count 2, logical reads 2380, physical reads 0, read-ahead reads 0.
Table 't3__<snip>'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.
SQL Server Execution Times: CPU time = 187 ms, elapsed time = 250 ms.

No more 'Worktable', and the run for 30 days is no longer a problem

...
select @.FromDate='2007-04-01 00:00:00', @.ToDate='2007-04-30 23:59:59'
...

(43200 row(s) affected)
Table 'numbers'. Scan count 6242, logical reads 13015, physical reads 0, read-ahead reads 0.
Table 'CallActionSummary'. Scan count 2, logical reads 2380, physical reads 0, read-ahead reads 0.
Table '#t3__<snip>'. Scan count 0, logical reads 1, physical reads 0, read-ahead reads 0.

SQL Server Execution Times: CPU time = 719 ms, elapsed time = 752 ms.

Hope this help you to find a suitable solution for your problem|||You don't need a table with a row for every minute in your date range. As you said, for 30 days, you would need 43200 minutes (rows). You actually only need as many rows as the length of the longest call. If the longest call is 26 minutes, your derived table only needs 26 rows. And since the datatype is integer, the performance and resource usage will be no problem.
Thanks to r937, I learned how handy an "integers" table can be (as pdreyer suggested with the numbers table).
Or you could generate the table variable as your example showed. The changes I made were to declare the datetimes as smalldatetime, since you only need precision to the minute. The field in the @.TimeBlock variable is of type int. (Should have changed the variable name to match). Changed @.MinReq to @.MaxReq since it's getting its value from the Max() function. I also wasn't sure about the structure of your "CallActionSummary" table, or the WHERE NOT IN clause (background).
You'll notice that instead of showing all minutes, this method will only show the minutes during which at least one call was taking place.

ALTER PROCEDURE LineUtilisation @.FromDate smalldatetime, @.ToDate smalldatetime
AS
SET NOCOUNT ON
DECLARE @.TimeBlock TABLE (DTBlock int)
DECLARE @.CallData TABLE(Call_Def Int,StartTime smalldatetime,EndTime smalldatetime)
DECLARE @.MaxReq Int
SET @.MaxReq = (SELECT MAX(DATEDIFF(minute, StartTime, EndTime)) FROM CallData)
INSERT INTO @.TimeBlock
SELECT @.MaxReq
WHILE (@.MaxReq > 0)
BEGIN
SET @.MaxReq = @.MaxReq - 1
INSERT INTO @.TimeBlock
SELECT @.MaxReq
END
/*
Wasn't too sure about the structure of your "CallActionSummary" table
so instead referenced the "CallData" table you used earlier in your post
*/
INSERT INTO @.CallData
SELECT
CallId,StartTime,EndTime
FROM CallData
WHERE StartTime Between @.FromDate AND @.ToDate /* if you want to filter on a date range */

SELECT DATEADD(minute, a.DTBlock, b.StartTime) AS Minutes, COUNT(DATEADD(minute, a.DTBlock, b.StartTime)) AS CallQty
FROM @.TimeBlock a INNER JOIN @.CallData b
ON a.DTBlock BETWEEN 0 AND DATEDIFF(minute, b.StartTime, b.EndTime)
GROUP BY DATEADD(minute, a.DTBlock, b.StartTime)
ORDER BY Minutes|||Thanks to r937, I learned how handy an "integers" table can be (as pdreyer suggested with the numbers table).thank you :cool:|||Overall I'm trying analyze how many telephone lines we need

If this is your overall goal, then the solution is very, very simple.

This query will tell you the maximum number of concurrent phonecalls per month

select year(starttime),month(starttime),max(cnt) from
(select dv.starttime, (select count(*) from calllog l where l.starttime<=dv.starttime and l.endtime>dv.starttime) as cnt
from (select distinct starttime from calllog) dv) t
group by year(starttime),month(starttime)

You can group the starttime column to fit your need. Group by day if you want to.

This query is based on samples from the book "Inside Microsoft SQL Server 2005 T-SQL PROGRAMMING" by guru Itzik Ben-Gan|||Thanks kaffenils

But the Query as suggested
setdateformat ymd
selectDATEPART(hour,NotiTime),max(cnt)
from
(select dv.NotiTime,(selectcount(*)
from CallActionSummary l
where l.NotiTime<=dv.NotiTime andDATEADD(ss,l.SSTotConn,l.NotiTime)>dv.NotiTime
)as cnt
from(selectdistinct NotiTime from CallActionSummary
) dv
) t
where NotiTime BETWEEN'2007-05-13'AND'2007-05-14'
groupbyDATEPART(hour,NotiTime)
Took just over one Hour to run a single day sooo..... I'm gonna get my head round the other suggestions and then post back.

Thanks Guys for all your hard work - your truly going above and beyond the call on this one.

GW|||Create an index on NotiTime. That would hopefully improve performance.

Complex PROC question

Complex to me, anyways. I posted this quite a bit ago, although the question was different, and you guys pointed out what was wrong, which saved me lots of headaches and coffee, so hopefully you guys can point out what is wrong this time.

This PROC is used in a search engine type script I wrote that searches through a database of magazine articles. You can search with just a name or description, or a range of dates. You can also search for all articles posted after a date, or before a date. On the perl side, I pass it 4 parameters, $querry_string, the description or name, $datefrom and $dateto, which specifies a range of dates, and $slice, which is which slice of results to return, like 1-10.

The script *mostly* works, it works correctly with two dates or no dates are specified, however it will not work with only one date filled in. ( See querry2 in the proc ) Any ideas what is wrong? When I run the line in Querry Analyzer it works, but in the script, it finds 0 results no matter what.

CREATE PROC [dbo].[search_querry_results](
@.datefrom datetime = NULL,
@.dateto datetime = NULL,
@.querry_string varchar(60),
@.slice int
)
AS

DECLARE @.querry nvarchar(2000)
DECLARE @.querry2 nvarchar(2000)
DECLARE @.querry3 nvarchar(2000)

-- Care of blind dude

SET @.querry = 'SELECT TOP ' + CAST(@.slice AS varchar(100))
+ ' * FROM FREETEXTTABLE( Exponent, *, ''' + @.querry_string
+ ''' ) as ct JOIN Exponent AS e ON ct.[KEY] = e.[Key] WHERE date > '''
+ CAST( @.datefrom AS varchar(30)) + ''' AND date < '''
+ CAST(@.dateto AS varchar(30)) + ''' ORDER BY Rank DESC'

SET @.querry2 = 'SELECT TOP ' + CAST(@.slice AS varchar(100))
+ ' * FROM FREETEXTTABLE( Exponent, *, ''' + @.querry_string
+ ''' ) as ct JOIN Exponent AS e ON ct.[KEY] = e.[Key] WHERE date < '''
+ CAST(@.dateto AS varchar(30)) + ''' ORDER BY Rank DESC'

SET @.querry3 = 'SELECT TOP ' + CAST(@.slice AS varchar(100))
+ ' * FROM FREETEXTTABLE( Exponent, *, ''' + @.querry_string
+ ''' ) as ct JOIN Exponent AS e ON ct.[KEY] = e.[Key] ORDER BY Rank DESC'

BEGIN
IF ( @.datefrom IS NOT NULL ) AND ( @.dateto IS NOT NULL )
EXEC sp_executesql @.querry

ELSE

IF ( @.dateto IS NOT NULL ) AND ( @.datefrom IS NULL)
EXEC sp_executesql @.querry2

ELSE

IF ( @.datefrom IS NULL ) AND (@.dateto IS NULL )
EXEC sp_executesql @.querry3
END
GO

There is the code. Let me know if you can come up with something, im all out of ideas. Thanks

-ruhkWhat is the error you're getting?

If you CAST a NULL to varchar, you get NULL. So I'm not sure if that's where you're problem is occurring or not...|||Hi!, the "date" field in the table is (VarChar or NVarchar) or DateTime?
if it's Datetime, i think the better way is comparing it as datetime. You don't have to convert anything because @.DateTo and @.DateFrom are already DateTime, so compare it directly.

Sorry for my bad english, i'm a programmer, not a biligue, jejeje. I hope this will be usefull, i spend much time figting with date comparisons and that's the way i found it works.
Suerte!!!!!!|||Try the following.

I changed:
- Compare actual dates rather than string version of dates
- Cleaned up the conditions of datefrom/dateto being NULL. The code below is much easier to understand/maintain.
- Avoided escaping issues by leaving @.querry_string as a variable reference in the dynamic SQL rather than dynamically adding the actual value to the dynamic SQL.

<code>
CREATE PROC [dbo].[search_querry_results](
@.datefrom datetime = NULL,
@.dateto datetime = NULL,
@.querry_string varchar(60),
@.slice int
)
AS

DECLARE @.query varchar(2000)

SET @.query = 'SELECT TOP ' + CAST(@.slice AS varchar(100))
+ ' * FROM FREETEXTTABLE( Exponent, *, @.querry_string ) AS ct'
+ ' JOIN Exponent AS e ON ct.[KEY] = e.[Key]'

IF ( @.datefrom IS NOT NULL ) AND ( @.dateto IS NOT NULL )
SET @.query = @.query + ' WHERE Date BETWEEN @.datefrom AND @.dateto'
ELSE IF ( @.datefrom IS NOT NULL )
SET @.query = @.query + ' WHERE Date > @.datefrom'
ELSE IF ( @.dateto IS NOT NULL )
SET @.query = @.query + ' WHERE Date < @.dateto'

SET @.query = @.query + ' ORDER BY Rank DESC'

EXEC sp_executesql @.query
</code>|||Roger - Tried using your code and it gives me a Procedure expects parameter '@.statement' of type 'ntext/nchar/nvarchar'. (SQL-37000)(DBD: st_execute/SQLExecute err=-1) error. Going to try to figure out what that means.|||Ah, I changed the declare line to DECLARE @.query nvarchar(2000), and that got rid of one problem. However now it says I must declare @.querry_string, and if I add in ''' + @.querry_string + ''' it gives me errors that I must declare my other variables. Anyone know how to fix this?|||Comment out your EXEC statements temporarily. Then add code to display your SQL strings:

select @.querry1
select @.querry2
select @.querry3

Then try running each string through Query Analyzer to help debug your dynamic code.|||They all works perfectly in querry analzyer but when I run my origonal code I posted in the script, it returns 0 hits.

I think its a problem with the datetime. For some reason WHERE Date < @.Dateto does not work.|||I fixed it! Thank you RogerWilco and others, your code worked great. The problem was actually in another stored procedure that counted just the hits.

Friday, February 17, 2012

compilation and execution plan

Hi guys,
I'm confused on following situation, - I'm definitely missing something. I
have SP which plan is already in cache. The SP references table MyTable. I
setup trace on recompile and all cache related events and it runs during
test nonstop.
Here is what I did (before dash) and
what I saw in Profiler (after dash) on each action:
DBCC FREEPROCCACHE - sp:cacheremove
exec MySP - sp:cachemiss, sp:cacheinsert
drop procedure MySP - sp:cacheremove
CREATE PROCEDURE MySP - none
exec MySP - sp:cachemiss, sp:cacheinsert
exec sp_recompile MyTable - none
exec MySP - sp:ExecContextHit, sp:cacheremove,
sp:recompile,
sp:cachemiss, sp:cacheinsert
My question is Why I can't see sp:recompile events
when I execute SP after DBCC and then after DROP/CREATE statements?
My understanding is that if plan is removed from cache
it doesn't exist anymore and has to be compiled to enable SP to execute next
time (what we see when SP is executed after
exec sp_recompile MyTable)?
But instead we see sp:cacheinsert event right after sp:cachemiss event. It's
inserted into cache but from where? On which step did it get compiled?
I'd be highly grateful for any information.
AlexAlex,
The difference is in the difference between the words "compile" and "recompile". If you drop and
create the proc, the proc plan doesn't exist and fir the first execution you get a compilation. Same
if you empty the proc cache.
However, if you sp_recompile a table that the proc is using, the plan is still in cache and for the
next execution SQL Server will need to REcompile that plan.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex" <alex_remove_this_@.telus.net> wrote in message news:pSepd.2111$cE3.1783@.clgrps12...
> Hi guys,
> I'm confused on following situation, - I'm definitely missing something. I
> have SP which plan is already in cache. The SP references table MyTable. I
> setup trace on recompile and all cache related events and it runs during
> test nonstop.
> Here is what I did (before dash) and
> what I saw in Profiler (after dash) on each action:
> DBCC FREEPROCCACHE - sp:cacheremove
> exec MySP - sp:cachemiss, sp:cacheinsert
> drop procedure MySP - sp:cacheremove
> CREATE PROCEDURE MySP - none
> exec MySP - sp:cachemiss, sp:cacheinsert
> exec sp_recompile MyTable - none
> exec MySP - sp:ExecContextHit, sp:cacheremove,
> sp:recompile,
> sp:cachemiss, sp:cacheinsert
> My question is Why I can't see sp:recompile events
> when I execute SP after DBCC and then after DROP/CREATE statements?
> My understanding is that if plan is removed from cache
> it doesn't exist anymore and has to be compiled to enable SP to execute next
> time (what we see when SP is executed after
> exec sp_recompile MyTable)?
> But instead we see sp:cacheinsert event right after sp:cachemiss event. It's
> inserted into cache but from where? On which step did it get compiled?
> I'd be highly grateful for any information.
> Alex
>|||Thank you Tibor,
So Profiler has event related to recompilation (sp:recompile) and doesn't
have event related to compilation, that's why we see only sp:cachemiss,
sp:cacheinsert and nothing in between?
Thank you,
Alex
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> Alex,
> The difference is in the difference between the words "compile" and
"recompile". If you drop and
> create the proc, the proc plan doesn't exist and fir the first execution
you get a compilation. Same
> if you empty the proc cache.
> However, if you sp_recompile a table that the proc is using, the plan is
still in cache and for the
> next execution SQL Server will need to REcompile that plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alex" <alex_remove_this_@.telus.net> wrote in message
news:pSepd.2111$cE3.1783@.clgrps12...
> > Hi guys,
> >
> > I'm confused on following situation, - I'm definitely missing something.
I
> > have SP which plan is already in cache. The SP references table MyTable.
I
> > setup trace on recompile and all cache related events and it runs during
> > test nonstop.
> >
> > Here is what I did (before dash) and
> > what I saw in Profiler (after dash) on each action:
> >
> > DBCC FREEPROCCACHE - sp:cacheremove
> > exec MySP - sp:cachemiss, sp:cacheinsert
> > drop procedure MySP - sp:cacheremove
> > CREATE PROCEDURE MySP - none
> > exec MySP - sp:cachemiss, sp:cacheinsert
> > exec sp_recompile MyTable - none
> > exec MySP - sp:ExecContextHit, sp:cacheremove,
> > sp:recompile,
> > sp:cachemiss, sp:cacheinsert
> >
> > My question is Why I can't see sp:recompile events
> > when I execute SP after DBCC and then after DROP/CREATE statements?
> > My understanding is that if plan is removed from cache
> > it doesn't exist anymore and has to be compiled to enable SP to execute
next
> > time (what we see when SP is executed after
> > exec sp_recompile MyTable)?
> > But instead we see sp:cacheinsert event right after sp:cachemiss event.
It's
> > inserted into cache but from where? On which step did it get compiled?
> >
> > I'd be highly grateful for any information.
> >
> > Alex
> >
> >
>|||Yep. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Alex" <alex_removethis_@.healthmetrx.com> wrote in message
news:10qc0uncauftpd9@.corp.supernews.com...
> Thank you Tibor,
> So Profiler has event related to recompilation (sp:recompile) and doesn't
> have event related to compilation, that's why we see only sp:cachemiss,
> sp:cacheinsert and nothing in between?
>
> Thank you,
> Alex
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> > Alex,
> >
> > The difference is in the difference between the words "compile" and
> "recompile". If you drop and
> > create the proc, the proc plan doesn't exist and fir the first execution
> you get a compilation. Same
> > if you empty the proc cache.
> >
> > However, if you sp_recompile a table that the proc is using, the plan is
> still in cache and for the
> > next execution SQL Server will need to REcompile that plan.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> >
> >
> > "Alex" <alex_remove_this_@.telus.net> wrote in message
> news:pSepd.2111$cE3.1783@.clgrps12...
> > > Hi guys,
> > >
> > > I'm confused on following situation, - I'm definitely missing something.
> I
> > > have SP which plan is already in cache. The SP references table MyTable.
> I
> > > setup trace on recompile and all cache related events and it runs during
> > > test nonstop.
> > >
> > > Here is what I did (before dash) and
> > > what I saw in Profiler (after dash) on each action:
> > >
> > > DBCC FREEPROCCACHE - sp:cacheremove
> > > exec MySP - sp:cachemiss, sp:cacheinsert
> > > drop procedure MySP - sp:cacheremove
> > > CREATE PROCEDURE MySP - none
> > > exec MySP - sp:cachemiss, sp:cacheinsert
> > > exec sp_recompile MyTable - none
> > > exec MySP - sp:ExecContextHit, sp:cacheremove,
> > > sp:recompile,
> > > sp:cachemiss, sp:cacheinsert
> > >
> > > My question is Why I can't see sp:recompile events
> > > when I execute SP after DBCC and then after DROP/CREATE statements?
> > > My understanding is that if plan is removed from cache
> > > it doesn't exist anymore and has to be compiled to enable SP to execute
> next
> > > time (what we see when SP is executed after
> > > exec sp_recompile MyTable)?
> > > But instead we see sp:cacheinsert event right after sp:cachemiss event.
> It's
> > > inserted into cache but from where? On which step did it get compiled?
> > >
> > > I'd be highly grateful for any information.
> > >
> > > Alex
> > >
> > >
> >
> >
>|||On Thu, 25 Nov 2004 06:22:13 GMT, "Alex" <alex_remove_this_@.telus.net>
wrote:
>But instead we see sp:cacheinsert event right after sp:cachemiss event. It's
>inserted into cache but from where? On which step did it get compiled?
>I'd be highly grateful for any information.
sp_recompile only marks the SP, it doesn't actually *do* the
compilation at that time, SQLServer "lazily" waits for the next call
for execution, and then compiles it.
J.|||JXStern wrote:
> On Thu, 25 Nov 2004 06:22:13 GMT, "Alex" <alex_remove_this_@.telus.net>
> wrote:
> >But instead we see sp:cacheinsert event right after sp:cachemiss event. It's
> >inserted into cache but from where? On which step did it get compiled?
> >
> >I'd be highly grateful for any information.
> sp_recompile only marks the SP, it doesn't actually *do* the
> compilation at that time, SQLServer "lazily" waits for the next call
> for execution, and then compiles it.
> J.
This "laziness" avoids unnecessary compilations.
However, there is also a technical reason that SP's are not recompiled
immediate after a schema change or sp_recompile. This is because the
compilation phase uses the stored procedure parameters so the most
representative statistics can be used, and thus will not 'guess'
parameter values. This basically means that SP's cannot be compiled
without calling them (with the appropriate parameters).
Gert-Jan|||Not quite.
The explanation of the terms is fine, but this is not how Profiler records
them.
If you actually look at what happens when you issue the sp_recompile, you'll
see a SP:CacheRemove event. Then the next time you call the proc, you'll see
SP:CacheInsert just as for the first time.
AFAIK, the SP:Recompile event is ONLY generated in the situation where a
stored procedure is recompiled while it is executing. This is caused by
activities in the sproc like DDL, updating statistics or changing a SET
option.
See KB 308737 for more details.
--
HTH
--
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23Wuoqeu0EHA.1392@.TK2MSFTNGP14.phx.gbl...
> Alex,
> The difference is in the difference between the words "compile" and
> "recompile". If you drop and
> create the proc, the proc plan doesn't exist and fir the first execution
> you get a compilation. Same
> if you empty the proc cache.
> However, if you sp_recompile a table that the proc is using, the plan is
> still in cache and for the
> next execution SQL Server will need to REcompile that plan.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Alex" <alex_remove_this_@.telus.net> wrote in message
> news:pSepd.2111$cE3.1783@.clgrps12...
>> Hi guys,
>> I'm confused on following situation, - I'm definitely missing something.
>> I
>> have SP which plan is already in cache. The SP references table MyTable.
>> I
>> setup trace on recompile and all cache related events and it runs during
>> test nonstop.
>> Here is what I did (before dash) and
>> what I saw in Profiler (after dash) on each action:
>> DBCC FREEPROCCACHE - sp:cacheremove
>> exec MySP - sp:cachemiss, sp:cacheinsert
>> drop procedure MySP - sp:cacheremove
>> CREATE PROCEDURE MySP - none
>> exec MySP - sp:cachemiss, sp:cacheinsert
>> exec sp_recompile MyTable - none
>> exec MySP - sp:ExecContextHit, sp:cacheremove,
>> sp:recompile,
>> sp:cachemiss, sp:cacheinsert
>> My question is Why I can't see sp:recompile events
>> when I execute SP after DBCC and then after DROP/CREATE statements?
>> My understanding is that if plan is removed from cache
>> it doesn't exist anymore and has to be compiled to enable SP to execute
>> next
>> time (what we see when SP is executed after
>> exec sp_recompile MyTable)?
>> But instead we see sp:cacheinsert event right after sp:cachemiss event.
>> It's
>> inserted into cache but from where? On which step did it get compiled?
>> I'd be highly grateful for any information.
>> Alex
>>
>|||Thank you guys for all that information
Alex
"Gert-Jan Strik" <sorry@.toomuchspamalready.nl> wrote in message
news:41A77B97.DA602FD7@.toomuchspamalready.nl...
> JXStern wrote:
> >
> > On Thu, 25 Nov 2004 06:22:13 GMT, "Alex" <alex_remove_this_@.telus.net>
> > wrote:
> > >But instead we see sp:cacheinsert event right after sp:cachemiss event.
It's
> > >inserted into cache but from where? On which step did it get compiled?
> > >
> > >I'd be highly grateful for any information.
> >
> > sp_recompile only marks the SP, it doesn't actually *do* the
> > compilation at that time, SQLServer "lazily" waits for the next call
> > for execution, and then compiles it.
> >
> > J.
> This "laziness" avoids unnecessary compilations.
> However, there is also a technical reason that SP's are not recompiled
> immediate after a schema change or sp_recompile. This is because the
> compilation phase uses the stored procedure parameters so the most
> representative statistics can be used, and thus will not 'guess'
> parameter values. This basically means that SP's cannot be compiled
> without calling them (with the appropriate parameters).
> Gert-Jan

compilation and execution plan

Tuesday, February 14, 2012

Compatibility_52_409_30003

Hi
Sorry to those caught in this cross post, but the setup group is a bit
dead...you guys are usually spot on.
I am trying to set up a hot spare server. The production database is
set up with server collation Compatibility_52_409_30003 (and for teh
database too).
I am having a problem with trying to create a hot spare server with
this collation, or even to get a master rebuild to use it. Its not in
the options given.
Anyone have any idea how I can get this working?To let anyone who has this problem know: record an unattended install
file. Modify this to use the collation you need, and then run it.
Check BOL for syntax.

Sunday, February 12, 2012

Comparison to NULL and '' fields

Guys, can anybody remind the settings responsible for the results of the
search by the NULL fields? I saw this database setting several months ago
but forgot this switch. The problem is the following. We're trying to run a
search in a few tables using some SQL query with a multilevel combination of
AND/OR with
WHERE SOMEFIELD LIKE '%SOMETEXT%'
If the field is NULL or '' (zero length string) then two possible results
are available depending on this database setting, true or false. The default
setting is so that if the field is NULL these records are excluded from my
result list but I need all these records to be included. So I need to find
this switch to get TRUE always when I compare to NULL strings to include the
records with NULL fields into the result list.
Thanks,
Dmitri.Do you mean
SET ANSI_NULLS
Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to (<> )
comparison operators when used with null values.
Jens SUessmeyer.
"Just D." <no@.spam.please> schrieb im Newsbeitrag
news:Ojtbe.70654$lz2.58655@.fed1read07...
> Guys, can anybody remind the settings responsible for the results of the
> search by the NULL fields? I saw this database setting several months ago
> but forgot this switch. The problem is the following. We're trying to run
> a search in a few tables using some SQL query with a multilevel
> combination of AND/OR with
> WHERE SOMEFIELD LIKE '%SOMETEXT%'
> If the field is NULL or '' (zero length string) then two possible results
> are available depending on this database setting, true or false. The
> default setting is so that if the field is NULL these records are excluded
> from my result list but I need all these records to be included. So I need
> to find this switch to get TRUE always when I compare to NULL strings to
> include the records with NULL fields into the result list.
> Thanks,
> Dmitri.
>|||I strongly recommend not to do this. Take a look at this interesting puzzle
and see the consequnces of setting ANSI_NULLS OFF.
http://groups-beta.google.com/group...aca91a7912bdc76
To accomplish what you want, use:
...
WHERE SOMEFIELD LIKE '%SOMETEXT%' or soemfield is null
AMB
"Just D." wrote:

> Guys, can anybody remind the settings responsible for the results of the
> search by the NULL fields? I saw this database setting several months ago
> but forgot this switch. The problem is the following. We're trying to run
a
> search in a few tables using some SQL query with a multilevel combination
of
> AND/OR with
> WHERE SOMEFIELD LIKE '%SOMETEXT%'
> If the field is NULL or '' (zero length string) then two possible results
> are available depending on this database setting, true or false. The defau
lt
> setting is so that if the field is NULL these records are excluded from my
> result list but I need all these records to be included. So I need to find
> this switch to get TRUE always when I compare to NULL strings to include t
he
> records with NULL fields into the result list.
> Thanks,
> Dmitri.
>
>|||Hi Jens,
Probably you're right. But if it works with LIKE ? Or only with <> = ?
Thanks,
Dmitri.
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OIOIIenSFHA.2136@.TK2MSFTNGP14.phx.gbl...
> Do you mean
> SET ANSI_NULLS
> Specifies SQL-92 compliant behavior of the Equals (=) and Not Equal to
> (<> ) comparison operators when used with null values.
> Jens SUessmeyer.
> "Just D." <no@.spam.please> schrieb im Newsbeitrag
> news:Ojtbe.70654$lz2.58655@.fed1read07...
>|||I think it only changes the behavior of comparisons that include
at least one literal or variable that is NULL. Column-to-column
comparisons between NULLs are never true, regardless of the
setting. I think <column> LIKE NULL is never true also.
set ansi_nulls off
go
declare @.t table (
a int,
c char
)
insert into @.t values (null, null)
insert into @.t values (0, '0')
select * from @.t
where a = null
select * from @.t
where a = c
select * from @.t
where c like null
go
set ansi_nulls on
Steve Kass
Drew University
Just D. wrote:

>Hi Jens,
>Probably you're right. But if it works with LIKE ? Or only with <> = ?
>Thanks,
>Dmitri.
>"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
>message news:OIOIIenSFHA.2136@.TK2MSFTNGP14.phx.gbl...
>
>
>|||Hi Steve,
My parameters that I provide to SQL are never NULL, the string should look
like this:
SELECT * FROM blablabla WHERE Param1 LIKE '%Col1%' OR Param2 LIKE '%Col2%'
OR Param3 LIKE '%Col3%'
This is a very simplified string because actually it's a very long
combination of OR/ANDs and braces. The Param# are never NULL, but if the
value in the column is NULL then I lose this record from the result and this
is a problem. If I had some global setting it would be nice but if I don't
have this way then I need to evaluate all these cells manually and it will
be nightmare because i need to search in 20-30 fields in a few tables, the
tables are long enough and not necessarily all these value are set to
something instead of NULL.
Dmitri.
"Steve Kass" <skass@.drew.edu> wrote in message
news:uABhz7rSFHA.3312@.TK2MSFTNGP12.phx.gbl...
>I think it only changes the behavior of comparisons that include
> at least one literal or variable that is NULL. Column-to-column
> comparisons between NULLs are never true, regardless of the
> setting. I think <column> LIKE NULL is never true also.
> set ansi_nulls off
> go
> declare @.t table (
> a int,
> c char
> )
> insert into @.t values (null, null)
> insert into @.t values (0, '0')
> select * from @.t
> where a = null
> select * from @.t
> where a = c
> select * from @.t
> where c like null
> go
> set ansi_nulls on
> Steve Kass
> Drew University
> Just D. wrote:
>|||> My parameters that I provide to SQL are never NULL, the string should look
> like this:
>
> SELECT * FROM blablabla WHERE Param1 LIKE '%Col1%' OR Param2 LIKE '%Col2%'
OR
> Param3 LIKE '%Col3%'
> This is a very simplified string because actually it's a very long combina
tion
> of OR/ANDs and braces. The Param# are never NULL, but if the value in the
> column is NULL then I lose this record from the result and this is a problem.[/col
or]
As I see it, there are a couple of solutions. The first, most obvious soluti
on
is to populate those nulls with something (e.g. an empty string (ugh) ) and
set
the column to be non-nullable. Then you won't have to worry about null colum
n
values.
The other solution is to change your where clause to account for nulls like
so
(depending on the actual logic desired):
Select F1...Fn
From Table
Where Param1 Is Null
Or Param1 Like '%Col1%'
Or Param2 Is Null
Or Param2 Like '%Col2%'
Or Param3 Is Null
Or Param3 Like '%Col3%'
> If I had some global setting it would be nice but if I don't have this way
> then I need to evaluate all these cells manually and it will be nightmare
> because i need to search in 20-30 fields in a few tables, the tables are l
ong
> enough and not necessarily all these value are set to something instead of
> NULL.
I don't see that you have a whole lot of choices. It sounds like populating
those fields with something would be your easiest route. I would definitely
not
recommend playing with the ANSI_NULLS option. It will confuse the hell out o
f
any other developer that looks at your code. It is one of those unobvious
"gotchas" that developers hate.
Thomas|||Dmitri,
What you want isn't going to be found in a setting,
if you want NULL LIKE '%Col1%' to be true.
If you want rows where any column is null, along with
the once returned by the query below, try
WHERE COALESCE(Param1,'Col1') LIKE '%Col1%'
OR COALESCE(Param2,'Col2') LIKE '%Col2%'
OR COALESCE(Param3,'Col3') LIKE '%Col3%'
It seems very strange to have columns named ParamX where you
search for values called Col1, so if this is not what you want, it
will be best if you provide sample table data that includes rows
you want (matching LIKE), rows you want (because of NULLs)
and some rows you don't want, so you can explain and show us
what you want to retrieve.
SK
Just D. wrote:

>Hi Steve,
>My parameters that I provide to SQL are never NULL, the string should look
>like this:
>
>SELECT * FROM blablabla WHERE Param1 LIKE '%Col1%' OR Param2 LIKE '%Col2%'
>OR Param3 LIKE '%Col3%'
>This is a very simplified string because actually it's a very long
>combination of OR/ANDs and braces. The Param# are never NULL, but if the
>value in the column is NULL then I lose this record from the result and thi
s
>is a problem. If I had some global setting it would be nice but if I don't
>have this way then I need to evaluate all these cells manually and it will
>be nightmare because i need to search in 20-30 fields in a few tables, the
>tables are long enough and not necessarily all these value are set to
>something instead of NULL.
>Dmitri.
>"Steve Kass" <skass@.drew.edu> wrote in message
>news:uABhz7rSFHA.3312@.TK2MSFTNGP12.phx.gbl...
>
>
>

comparison Query

Hi guys,

I'm trying to do a comparison of 2 data sets. Basically what I want is: 'where event date from event number -24 is earlier than the event date for event number -13'

To get the eventdate for the eventno's, I have the following 2 queries:

select eventdate
from caseevent, cases
where eventno = -24

select eventdate
from caseevent, cases
where eventno = -13

So what i'm trying to say is: I want it so that the value of the first query is compared to be LESS than the value of the second query...

Any help please?

Thank you!

Do you want this for a specific event ? I did not get your point in your query, could you describe the functionality in non SQL words ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

basically eventdate and eventno are 2 different fields. The first query works out the eventdate for the event number -24, and the second query works out the eventdate for the event number -13. Then, the eventdate values for the first query have to be compared with the eventdate parts for the 2nd query.

Overall this is meant to be one query, so once I've worked out the comparison part, I will need to figure out how to make it one query. Can I somehow compare the retrieved results?

Many thanks

|||

Hi,

Can you provide data for those two tables for better understanding and also in your query you have mentioned two tables: caseevent and cases. Is there any specific relationship between those two tables.

Regards,

Kiran.Y

|||

the relationship is that once we've sorted out this comparison issue, i need to show certain values from each table, although for this situation now, they are not been joined or cross referenced...

If I run each query invidualy i do get results, but like after 3 minutes!! the closest i've got so far is:

select distinct eventdate
from caseevent, cases
where eventno = -24
and eventdate < all

(select distinct eventdate
from cases
where eventno = -13)

This gives results straight away. Just wondering if a subquery is the best method for doing this

Thanks,

|||

anyone? anything? Help please!!

EEK!!

|||

As you did not post any DDL yet, this is based on assumptions.

Maybe an exists will speed up your data retrieval:

select distinct eventdate
from caseevent A
where eventno = -24
and EXISTS
(select distinct eventdate
from cases B
where B.eventno = -13

AND A.EventDate < B.eventdate)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Cool, I didn't think about the EXIST clause. I'll try this 2mo as am off work 2day, but then logic seems right in it from the looks of it. When I do run the previous query I showed u, it was a bit slow but if the EXIST clause speeds things up then it should be better Smile

Thank you!!

|||

Hello!!

I tried the query you sent me and I think it gives the right search facility, so that should be fine. But another part of the query is that I need to show other columns within the result.

so the query I have so far is:

select eventdate
from caseevent A
where A.eventno = -24
and EXISTS
(select distinct eventdate
from caseevent B
where B.eventno = -13

AND A.EventDate < B.eventdate)

What I'm trying to do is the following:

Report of cases where eventdate for eventno = -24 is earlier than the eventdate for eventno = -13. Need report to include IRN, eventdate for eventno = -24, eventdate for eventno = -13, and initials.

The columns available from the tables are:

Caseevent: eventdate, eventno, caseid

cases: irn, caseid

I don't know if this should be a query or a view! I'm still fresh with stuff like Views so wouldn't know how to do this. So far the logic of comparing the results has been understood, but how do I show the other values in my final results table?

Many Thanks

|||

Hi Guys,

Any comments or ideas on my previous email please?

The main thing I'm not aware of is how to combine to columns into one final column in my final results table...

Still trying to do this as a query. I'm looking into views to see if it can be a more efficient approach.

Thanks!|||

TSQL SELECT clauses support expressions or concatenations as

SELECT FirstName + ' ' + LastName AS FullName

Does this do what you want?

comparison Query

Hi guys,

I'm trying to do a comparison of 2 data sets. Basically what I want is: 'where event date from event number -24 is earlier than the event date for event number -13'

To get the eventdate for the eventno's, I have the following 2 queries:

select eventdate
from caseevent, cases
where eventno = -24

select eventdate
from caseevent, cases
where eventno = -13

So what i'm trying to say is: I want it so that the value of the first query is compared to be LESS than the value of the second query...

Any help please?

Thank you!

Do you want this for a specific event ? I did not get your point in your query, could you describe the functionality in non SQL words ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Overall this is meant to be one query, so once I've worked out the comparison part, I will need to figure out how to make it one query. Can I somehow compare the retrieved results?

Many thanks

|||

Hi,

Regards,

Kiran.Y

|||

the relationship is that once we've sorted out this comparison issue, i need to show certain values from each table, although for this situation now, they are not been joined or cross referenced...

If I run each query invidualy i do get results, but like after 3 minutes!! the closest i've got so far is:

select distinct eventdate
from caseevent, cases
where eventno = -24
and eventdate < all

(select distinct eventdate
from cases
where eventno = -13)

This gives results straight away. Just wondering if a subquery is the best method for doing this

Thanks,

|||

anyone? anything? Help please!!

EEK!!

|||

As you did not post any DDL yet, this is based on assumptions.

Maybe an exists will speed up your data retrieval:

select distinct eventdate
from caseevent A
where eventno = -24
and EXISTS
(select distinct eventdate
from cases B
where B.eventno = -13

AND A.EventDate < B.eventdate)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thank you!!

|||

Hello!!

I tried the query you sent me and I think it gives the right search facility, so that should be fine. But another part of the query is that I need to show other columns within the result.

so the query I have so far is:

select eventdate
from caseevent A
where A.eventno = -24
and EXISTS
(select distinct eventdate
from caseevent B
where B.eventno = -13

AND A.EventDate < B.eventdate)

What I'm trying to do is the following:

Report of cases where eventdate for eventno = -24 is earlier than the eventdate for eventno = -13. Need report to include IRN, eventdate for eventno = -24, eventdate for eventno = -13, and initials.

The columns available from the tables are:

Caseevent: eventdate, eventno, caseid

cases: irn, caseid

Many Thanks

|||

Hi Guys,

Any comments or ideas on my previous email please?

The main thing I'm not aware of is how to combine to columns into one final column in my final results table...

Still trying to do this as a query. I'm looking into views to see if it can be a more efficient approach.

Thanks!|||

TSQL SELECT clauses support expressions or concatenations as

SELECT FirstName + ' ' + LastName AS FullName

Does this do what you want?

comparison Query

Hi guys,

I'm trying to do a comparison of 2 data sets. Basically what I want is: 'where event date from event number -24 is earlier than the event date for event number -13'

To get the eventdate for the eventno's, I have the following 2 queries:

select eventdate
from caseevent, cases
where eventno = -24

select eventdate
from caseevent, cases
where eventno = -13

So what i'm trying to say is: I want it so that the value of the first query is compared to be LESS than the value of the second query...

Any help please?

Thank you!

Do you want this for a specific event ? I did not get your point in your query, could you describe the functionality in non SQL words ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Overall this is meant to be one query, so once I've worked out the comparison part, I will need to figure out how to make it one query. Can I somehow compare the retrieved results?

Many thanks

|||

Hi,

Regards,

Kiran.Y

|||

the relationship is that once we've sorted out this comparison issue, i need to show certain values from each table, although for this situation now, they are not been joined or cross referenced...

If I run each query invidualy i do get results, but like after 3 minutes!! the closest i've got so far is:

select distinct eventdate
from caseevent, cases
where eventno = -24
and eventdate < all

(select distinct eventdate
from cases
where eventno = -13)

This gives results straight away. Just wondering if a subquery is the best method for doing this

Thanks,

|||

anyone? anything? Help please!!

EEK!!

|||

As you did not post any DDL yet, this is based on assumptions.

Maybe an exists will speed up your data retrieval:

select distinct eventdate
from caseevent A
where eventno = -24
and EXISTS
(select distinct eventdate
from cases B
where B.eventno = -13

AND A.EventDate < B.eventdate)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thank you!!

|||

Hello!!

I tried the query you sent me and I think it gives the right search facility, so that should be fine. But another part of the query is that I need to show other columns within the result.

so the query I have so far is:

select eventdate
from caseevent A
where A.eventno = -24
and EXISTS
(select distinct eventdate
from caseevent B
where B.eventno = -13

AND A.EventDate < B.eventdate)

What I'm trying to do is the following:

Report of cases where eventdate for eventno = -24 is earlier than the eventdate for eventno = -13. Need report to include IRN, eventdate for eventno = -24, eventdate for eventno = -13, and initials.

The columns available from the tables are:

Caseevent: eventdate, eventno, caseid

cases: irn, caseid

Many Thanks

|||

Hi Guys,

Any comments or ideas on my previous email please?

The main thing I'm not aware of is how to combine to columns into one final column in my final results table...

Still trying to do this as a query. I'm looking into views to see if it can be a more efficient approach.

Thanks!|||

TSQL SELECT clauses support expressions or concatenations as

SELECT FirstName + ' ' + LastName AS FullName

Does this do what you want?

comparison Query

Hi guys,

I'm trying to do a comparison of 2 data sets. Basically what I want is: 'where event date from event number -24 is earlier than the event date for event number -13'

To get the eventdate for the eventno's, I have the following 2 queries:

select eventdate
from caseevent, cases
where eventno = -24

select eventdate
from caseevent, cases
where eventno = -13

So what i'm trying to say is: I want it so that the value of the first query is compared to be LESS than the value of the second query...

Any help please?

Thank you!

Do you want this for a specific event ? I did not get your point in your query, could you describe the functionality in non SQL words ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Overall this is meant to be one query, so once I've worked out the comparison part, I will need to figure out how to make it one query. Can I somehow compare the retrieved results?

Many thanks

|||

Hi,

Regards,

Kiran.Y

|||

the relationship is that once we've sorted out this comparison issue, i need to show certain values from each table, although for this situation now, they are not been joined or cross referenced...

If I run each query invidualy i do get results, but like after 3 minutes!! the closest i've got so far is:

select distinct eventdate
from caseevent, cases
where eventno = -24
and eventdate < all

(select distinct eventdate
from cases
where eventno = -13)

This gives results straight away. Just wondering if a subquery is the best method for doing this

Thanks,

|||

anyone? anything? Help please!!

EEK!!

|||

As you did not post any DDL yet, this is based on assumptions.

Maybe an exists will speed up your data retrieval:

select distinct eventdate
from caseevent A
where eventno = -24
and EXISTS
(select distinct eventdate
from cases B
where B.eventno = -13

AND A.EventDate < B.eventdate)

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Thank you!!

|||

Hello!!

I tried the query you sent me and I think it gives the right search facility, so that should be fine. But another part of the query is that I need to show other columns within the result.

so the query I have so far is:

select eventdate
from caseevent A
where A.eventno = -24
and EXISTS
(select distinct eventdate
from caseevent B
where B.eventno = -13

AND A.EventDate < B.eventdate)

What I'm trying to do is the following:

Report of cases where eventdate for eventno = -24 is earlier than the eventdate for eventno = -13. Need report to include IRN, eventdate for eventno = -24, eventdate for eventno = -13, and initials.

The columns available from the tables are:

Caseevent: eventdate, eventno, caseid

cases: irn, caseid

Many Thanks

|||

Hi Guys,

Any comments or ideas on my previous email please?

The main thing I'm not aware of is how to combine to columns into one final column in my final results table...

Still trying to do this as a query. I'm looking into views to see if it can be a more efficient approach.

Thanks!|||

TSQL SELECT clauses support expressions or concatenations as

SELECT FirstName + ' ' + LastName AS FullName

Does this do what you want?

Friday, February 10, 2012

Comparison

Hey guys what would be the easiest way to create a report of value changes for particular records from one day to the next.... ?
Any suggestions would be greatly appreciated...
thanks,
JonathanDo you record record changes? (audit)
Does the audit contain a time stamp?|||That database has a modifieddate field in epoch...
I convert it into human readable then will have compare values from
(i.e 9/13/2007 <> 9/14/2007)
If there are any changes amongst the field values create a report of those changes

Saturday, February 25, 2012

Complex Query

Complex PROC question

Friday, February 17, 2012

compilation and execution plan

compilation and execution plan

compilation and execution plan

Tuesday, February 14, 2012

Compatibility_52_409_30003

Sunday, February 12, 2012

Comparison to NULL and '' fields

comparison Query

comparison Query

comparison Query

comparison Query

Friday, February 10, 2012

Comparison

Sql Comparing two tables

Blog Archive

About Me