Finding duplicate based on records in same table

I need a help with a sql...
Is there a way to find a duplicate value based on different records
inside the same table?
I got a scenario, where I'm getting same ID with different date...
I need to get rid of one with earlier date...
Thanks in advance...
paulcasso@.gmail.com,
- Can you be more explicit and post some DDl, sample data and expected result?
- Which version are you using 2000 or 2005?
;with cte
as
(
select
[date], [id],
row_number () over(partition by [id] order by [date] DESC) as rn
from
dbo.t1
)
delete cte
where rn > 1;
AMB
"paulcasso@.gmail.com" wrote:

> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>
|||Take a look at this example:
--SQL Begins--
use tempdb
go
create table #tablename(
id_field int not null,
dt_field datetime not null
)
go
insert #tablename values(1, '1/1/2007')
insert #tablename values(2, '1/2/2007')
insert #tablename values(2, '1/3/2007')
insert #tablename values(3, '1/4/2007')
insert #tablename values(2, '1/5/2007')
insert #tablename values(3, '1/6/2007')
insert #tablename values(1, '1/7/2007')
go
-- deletes all but the most recent record for each id
delete a
from #tablename a
left join (select id_field, max(dt_field) dt
from #tablename
group by id_field) b
on a.id_field = b.id_field and a.dt_field = b.dt
where b.id_field is null
go
select * from #tablename
go
drop table #tablename
go
--SQL Ends--
On Apr 11, 2:20 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
|||Thank you for your help.,
but where does value dt coming from?
...on a.id_field = b.id_field and a.dt_field = b.dt ...
|||On Apr 11, 4:21 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> Thank you for your help.,
> but where does value dt coming from?
> ...on a.id_field = b.id_field and a.dt_field = b.dt ...

>From a previous post:
left join ( select id_field, max(dt_field) dt
dt is an alias for max(dt_field)
Cheers,
Jason Lepack
|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html
<paulcasso@.gmail.com> wrote in message
news:1176315620.878773.240790@.y5g2000hsa.googlegro ups.com...
>I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>

Finding duplicate based on records in same table

I need a help with a sql...
Is there a way to find a duplicate value based on different records
inside the same table?
I got a scenario, where I'm getting same ID with different date...
I need to get rid of one with earlier date...
Thanks in advance...paulcasso@.gmail.com,
- Can you be more explicit and post some DDl, sample data and expected resul
t?
- Which version are you using 2000 or 2005?
;with cte
as
(
select
[date], [id],
row_number () over(partition by [id] order by [date] DESC) as rn
from
dbo.t1
)
delete cte
where rn > 1;
AMB
"paulcasso@.gmail.com" wrote:

> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>|||Take a look at this example:
--SQL Begins--
use tempdb
go
create table #tablename(
id_field int not null,
dt_field datetime not null
)
go
insert #tablename values(1, '1/1/2007')
insert #tablename values(2, '1/2/2007')
insert #tablename values(2, '1/3/2007')
insert #tablename values(3, '1/4/2007')
insert #tablename values(2, '1/5/2007')
insert #tablename values(3, '1/6/2007')
insert #tablename values(1, '1/7/2007')
go
-- deletes all but the most recent record for each id
delete a
from #tablename a
left join ( select id_field, max(dt_field) dt
from #tablename
group by id_field) b
on a.id_field = b.id_field and a.dt_field = b.dt
where b.id_field is null
go
select * from #tablename
go
drop table #tablename
go
--SQL Ends--
On Apr 11, 2:20 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...|||Thank you for your help.,
but where does value dt coming from?
...on a.id_field = b.id_field and a.dt_field = b.dt ...|||On Apr 11, 4:21 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> Thank you for your help.,
> but where does value dt coming from?
> ...on a.id_field = b.id_field and a.dt_field = b.dt ...

>From a previous post:
left join ( select id_field, max(dt_field) dt
dt is an alias for max(dt_field)
Cheers,
Jason Lepack|||Hi
[url]http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html[/
url]
<paulcasso@.gmail.com> wrote in message
news:1176315620.878773.240790@.y5g2000hsa.googlegroups.com...
>I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>

Finding duplicate based on records in same table

I need a help with a sql...
Is there a way to find a duplicate value based on different records
inside the same table?
I got a scenario, where I'm getting same ID with different date...
I need to get rid of one with earlier date...
Thanks in advance...paulcasso@.gmail.com,
- Can you be more explicit and post some DDl, sample data and expected result?
- Which version are you using 2000 or 2005?
;with cte
as
(
select
[date], [id],
row_number () over(partition by [id] order by [date] DESC) as rn
from
dbo.t1
)
delete cte
where rn > 1;
AMB
"paulcasso@.gmail.com" wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>|||Take a look at this example:
--SQL Begins--
use tempdb
go
create table #tablename(
id_field int not null,
dt_field datetime not null
)
go
insert #tablename values(1, '1/1/2007')
insert #tablename values(2, '1/2/2007')
insert #tablename values(2, '1/3/2007')
insert #tablename values(3, '1/4/2007')
insert #tablename values(2, '1/5/2007')
insert #tablename values(3, '1/6/2007')
insert #tablename values(1, '1/7/2007')
go
-- deletes all but the most recent record for each id
delete a
from #tablename a
left join ( select id_field, max(dt_field) dt
from #tablename
group by id_field) b
on a.id_field = b.id_field and a.dt_field = b.dt
where b.id_field is null
go
select * from #tablename
go
drop table #tablename
go
--SQL Ends--
On Apr 11, 2:20 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...|||Thank you for your help.,
but where does value dt coming from?
...on a.id_field = b.id_field and a.dt_field = b.dt ...|||On Apr 11, 4:21 pm, "paulca...@.gmail.com" <paulca...@.gmail.com> wrote:
> Thank you for your help.,
> but where does value dt coming from?
> ...on a.id_field = b.id_field and a.dt_field = b.dt ...
>From a previous post:
left join ( select id_field, max(dt_field) dt
dt is an alias for max(dt_field)
Cheers,
Jason Lepack|||Hi
http://dimantdatabasesolutions.blogspot.com/2007/02/dealing-with-duplicates.html
<paulcasso@.gmail.com> wrote in message
news:1176315620.878773.240790@.y5g2000hsa.googlegroups.com...
>I need a help with a sql...
> Is there a way to find a duplicate value based on different records
> inside the same table?
> I got a scenario, where I'm getting same ID with different date...
> I need to get rid of one with earlier date...
> Thanks in advance...
>

Finding Consecutive Records (Based Upon A Integer Column)

Hi all,
Hopefully one of you brainboxes can help out with a tricky problem
that I just can't get my head around!
I have a table with a single integer column with the example data
(column name = ID):
1,2,3,4,5,7,8,9,10,12,14,16,17
And I need essentially the start value and size of each consecutive
group, ie:
ID Size
-- --
1 5
7 4
12 1
14 1
16 2
In that order (essentially so I can do a TOP 1, WHERE Size >=
@.minsizerequired) to find the first group that has enough consecutive
rows in it.
I've been working like this so far:
SELECT l.id AS firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) AS lastid
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
But I can't get the size of each group without performing yet another
subquery which I'd like to avoid (performance is a big issue here).
Has anyone got any bright ideas or am I barking up the wrong tree
entirely? I'd also like to avoid using ranking and window functions if
possible as this has to be 2000 compatible.
Thanks very much,
Kieran
Kidogg,
I think your query gives you the first and last id's in each run,
so you can instead ask for
select
l.id as firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) - firstid + 1 AS runlength
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
You might also try this approach:
SELECT
MIN(T.id) AS id,
MAX(T.id) - MIN(T.id) + 1 AS runlength
FROM (
SELECT b.id, count(a.id) as rk
FROM W AS a
JOIN W AS b
ON a.id <= b.id
GROUP BY b.id
) T
GROUP BY T.id - T.rk
-- Steve Kass
-- Drew University
Kidogg wrote:

>Hi all,
>Hopefully one of you brainboxes can help out with a tricky problem
>that I just can't get my head around!
>I have a table with a single integer column with the example data
>(column name = ID):
>1,2,3,4,5,7,8,9,10,12,14,16,17
>And I need essentially the start value and size of each consecutive
>group, ie:
>ID Size
>-- --
>1 5
>7 4
>12 1
>14 1
>16 2
>In that order (essentially so I can do a TOP 1, WHERE Size >=
>@.minsizerequired) to find the first group that has enough consecutive
>rows in it.
>I've been working like this so far:
>SELECT l.id AS firstid,
>(
> SELECT MIN(a.id) FROM @.t a
> LEFT OUTER JOIN @.t b ON a.id = b.id - 1
> WHERE b.id IS NULL AND a.id >= l.id
>) AS lastid
>FROM @.t l
>LEFT OUTER JOIN @.t r ON r.id = l.id - 1
>WHERE r.id IS NULL
>But I can't get the size of each group without performing yet another
>subquery which I'd like to avoid (performance is a big issue here).
>Has anyone got any bright ideas or am I barking up the wrong tree
>entirely? I'd also like to avoid using ranking and window functions if
>possible as this has to be 2000 compatible.
>Thanks very much,
>Kieran
>
>

Finding Consecutive Records (Based Upon A Integer Column)

Hi all,
Hopefully one of you brainboxes can help out with a tricky problem
that I just can't get my head around!
I have a table with a single integer column with the example data
(column name = ID):
1,2,3,4,5,7,8,9,10,12,14,16,17
And I need essentially the start value and size of each consecutive
group, ie:
ID Size
-- --
1 5
7 4
12 1
14 1
16 2
In that order (essentially so I can do a TOP 1, WHERE Size >=
@.minsizerequired) to find the first group that has enough consecutive
rows in it.
I've been working like this so far:
SELECT l.id AS firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) AS lastid
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
But I can't get the size of each group without performing yet another
subquery which I'd like to avoid (performance is a big issue here).
Has anyone got any bright ideas or am I barking up the wrong tree
entirely? I'd also like to avoid using ranking and window functions if
possible as this has to be 2000 compatible.
Thanks very much,
KieranKidogg,
I think your query gives you the first and last id's in each run,
so you can instead ask for
select
l.id as firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) - firstid + 1 AS runlength
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
You might also try this approach:
SELECT
MIN(T.id) AS id,
MAX(T.id) - MIN(T.id) + 1 AS runlength
FROM (
SELECT b.id, count(a.id) as rk
FROM W AS a
JOIN W AS b
ON a.id <= b.id
GROUP BY b.id
) T
GROUP BY T.id - T.rk
-- Steve Kass
-- Drew University
Kidogg wrote:

>Hi all,
>Hopefully one of you brainboxes can help out with a tricky problem
>that I just can't get my head around!
>I have a table with a single integer column with the example data
>(column name = ID):
>1,2,3,4,5,7,8,9,10,12,14,16,17
>And I need essentially the start value and size of each consecutive
>group, ie:
>ID Size
>-- --
>1 5
>7 4
>12 1
>14 1
>16 2
>In that order (essentially so I can do a TOP 1, WHERE Size >=
>@.minsizerequired) to find the first group that has enough consecutive
>rows in it.
>I've been working like this so far:
>SELECT l.id AS firstid,
>(
> SELECT MIN(a.id) FROM @.t a
> LEFT OUTER JOIN @.t b ON a.id = b.id - 1
> WHERE b.id IS NULL AND a.id >= l.id
> ) AS lastid
>FROM @.t l
>LEFT OUTER JOIN @.t r ON r.id = l.id - 1
>WHERE r.id IS NULL
>But I can't get the size of each group without performing yet another
>subquery which I'd like to avoid (performance is a big issue here).
>Has anyone got any bright ideas or am I barking up the wrong tree
>entirely? I'd also like to avoid using ranking and window functions if
>possible as this has to be 2000 compatible.
>Thanks very much,
>Kieran
>
>

Finding Consecutive Records (Based Upon A Integer Column)

Hi all,
Hopefully one of you brainboxes can help out with a tricky problem
that I just can't get my head around!
I have a table with a single integer column with the example data
(column name = ID):
1,2,3,4,5,7,8,9,10,12,14,16,17
And I need essentially the start value and size of each consecutive
group, ie:
ID Size
-- --
1 5
7 4
12 1
14 1
16 2
In that order (essentially so I can do a TOP 1, WHERE Size >= @.minsizerequired) to find the first group that has enough consecutive
rows in it.
I've been working like this so far:
SELECT l.id AS firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) AS lastid
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
But I can't get the size of each group without performing yet another
subquery which I'd like to avoid (performance is a big issue here).
Has anyone got any bright ideas or am I barking up the wrong tree
entirely? I'd also like to avoid using ranking and window functions if
possible as this has to be 2000 compatible.
Thanks very much,
KieranKidogg,
I think your query gives you the first and last id's in each run,
so you can instead ask for
select
l.id as firstid,
(
SELECT MIN(a.id) FROM @.t a
LEFT OUTER JOIN @.t b ON a.id = b.id - 1
WHERE b.id IS NULL AND a.id >= l.id
) - firstid + 1 AS runlength
FROM @.t l
LEFT OUTER JOIN @.t r ON r.id = l.id - 1
WHERE r.id IS NULL
You might also try this approach:
SELECT
MIN(T.id) AS id,
MAX(T.id) - MIN(T.id) + 1 AS runlength
FROM (
SELECT b.id, count(a.id) as rk
FROM W AS a
JOIN W AS b
ON a.id <= b.id
GROUP BY b.id
) T
GROUP BY T.id - T.rk
-- Steve Kass
-- Drew University
Kidogg wrote:
>Hi all,
>Hopefully one of you brainboxes can help out with a tricky problem
>that I just can't get my head around!
>I have a table with a single integer column with the example data
>(column name = ID):
>1,2,3,4,5,7,8,9,10,12,14,16,17
>And I need essentially the start value and size of each consecutive
>group, ie:
>ID Size
>-- --
>1 5
>7 4
>12 1
>14 1
>16 2
>In that order (essentially so I can do a TOP 1, WHERE Size >=>@.minsizerequired) to find the first group that has enough consecutive
>rows in it.
>I've been working like this so far:
>SELECT l.id AS firstid,
>(
> SELECT MIN(a.id) FROM @.t a
> LEFT OUTER JOIN @.t b ON a.id = b.id - 1
> WHERE b.id IS NULL AND a.id >= l.id
>) AS lastid
>FROM @.t l
>LEFT OUTER JOIN @.t r ON r.id = l.id - 1
>WHERE r.id IS NULL
>But I can't get the size of each group without performing yet another
>subquery which I'd like to avoid (performance is a big issue here).
>Has anyone got any bright ideas or am I barking up the wrong tree
>entirely? I'd also like to avoid using ranking and window functions if
>possible as this has to be 2000 compatible.
>Thanks very much,
>Kieran
>
>sql

finding and removing duplicates rows

Say I had a table called 'RAS', with 4 columns:
Date, Time, UserName, GroupName
How can I check for any duplicate rows based on all those columns
combined.
And once they are found, can the duplicates be deleted?
It would be nice to see 2 solutions for learning purposes... one
statement just to show the duplicates, and another separate statement
to find and delete the duplicates.
Thanks in advance!
StephenCREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"Stephen" <facetoface72@.hotmail.com> wrote in message
news:d6e21d67.0403160535.5ac3afa7@.posting.google.com...
> Say I had a table called 'RAS', with 4 columns:
> Date, Time, UserName, GroupName
> How can I check for any duplicate rows based on all those columns
> combined.
> And once they are found, can the duplicates be deleted?
> It would be nice to see 2 solutions for learning purposes... one
> statement just to show the duplicates, and another separate statement
> to find and delete the duplicates.
> Thanks in advance!
> Stephen|||Thanks for the message, it definately helped solve a lot of issues.
Is there a way to do everything mentioned, but without having a unique
key for each record?
"Uri Dimant" <urid@.iscar.co.il> wrote in message news:<#6uUEw1CEHA.1452@.TK2MSFTNGP09.phx.gb
l>...
> CREATE TABLE #Demo (
> idNo int identity(1,1),
> colA int,
> colB int
> )
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (1,6)
> INSERT INTO #Demo(colA,colB) VALUES (2,4)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (4,2)
> INSERT INTO #Demo(colA,colB) VALUES (3,3)
> INSERT INTO #Demo(colA,colB) VALUES (5,1)
> INSERT INTO #Demo(colA,colB) VALUES (8,1)
> PRINT 'Table'
> SELECT * FROM #Demo
> PRINT 'Duplicates in Table'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo <> B.idNo
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Duplicates to Delete'
> SELECT * FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> DELETE FROM #Demo
> WHERE idNo IN
> (SELECT B.idNo
> FROM #Demo A JOIN #Demo B
> ON A.idNo < B.idNo -- < this time, not <>
> AND A.colA = B.colA
> AND A.colB = B.colB)
> PRINT 'Cleaned-up Table'
> SELECT * FROM #Demo
> DROP TABLE #Demo
> "Stephen" <facetoface72@.hotmail.com> wrote in message
> news:d6e21d67.0403160535.5ac3afa7@.posting.google.com...|||On 17 Mar 2004 05:24:49 -0800, Stephen wrote:

>Thanks for the message, it definately helped solve a lot of issues.
>Is there a way to do everything mentioned, but without having a unique
>key for each record?
No. If all columns in two rows are the same, there's no way to tell
them apart. Any where clause will either select both rows or none of
them. If you want to eliminate duplicates, you have to add an IDENTITY
or GUID column, or else you will delete both rows instead of one of
the duplicates.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||DELETE *
FROM table3
WHERE sno not in (SELECT min(sno)
FROM table3
GROUP BY fd1, fd2, fd3);

Finding affected tables

I am attempting to document a sql server 2000 based accounting system.
Is there any way to see what tables a stored procedure affects
without diving into the code?

Regards,

Ty"Tyler Hudson" <tylerh@.allpax.com> wrote in message
news:804fa945.0403020849.fcf8f0b@.posting.google.co m...
> I am attempting to document a sql server 2000 based accounting system.
> Is there any way to see what tables a stored procedure affects
> without diving into the code?
>
> Regards,
> Ty

You can try sp_depends, although it may not be completely accurate,
depending on the order in which objects were created.

Simon

find value based on max(date)

I know I have done this before, but cannot for the life of me remember how.

I am trying to determine return the current (last added) deduction amount for each deduction type for each employee

Sample Table:
employee|Deduction_type|Date_entered|Amount
1|MED|1/1/2007|50
1|DEPC|1/1/2007|100
1|MED|1/8/2007|50
1|DEPC|1/8/2007|100
1|MED|1/15/2007|150
2|MED|1/1/2007|35
2|DEPC|1/1/2007|100
2|MED|1/8/2007|35
2|DEPC|1/8/2007|75
2|MED|1/15/2007|35

Any suggestions?select t.employee
, t.Deduction_type
, t.Date_entered
, t.Amount
from Sample as t
inner
join (
select employee
, Deduction_type
, max(Date_entered) as max_date
from Sample
group
by employee
, Deduction_type
) as m
on m.employee = t.employee
and m.Deduction_type = t.Deduction_type
and m.max_date = t.Date_enteredsql

find phonecall records based on user at the time of call

i have a table of phonecall records detailing amongst other things the time and duration of the call. there is a relationship linking this table to a table of handsets and from there to a table of users. this users table lists the start date the user had the phone. as ever sample data might make this easier to show:

PhoneUsers table

PhoneUserID Name StartDate PhoneID

1 john 1/08/2006 1

2 bob 20/08/2006 1

3 fred 2/08/2006 2

etc

Phones table:

PhoneID

1

2

etc

PhoneCalls table:

PhoneCallID PhoneID CallDate Duration

1 1 10/08/2006 25

2 1 23/082006 20

3 2 23/08/2006 20

i want the following result set:

user calldate duration

john 10/08/2006 25

bob 23/08/2006 20

fred 23/08/2006 20

essentially i am trying to link the phonecalls to the user of the phone at the time of the call. i make the following assumptions regarding the data:

the user is responsible for all calls for a given handset from the 'startdate' until the next 'startdate' for a different user, if such a record exists, if not then for all calls after that date

thanks

This should be close:

select

U1.Name as [user],

C.CallDate as calldate,

C.Duration as duration

from PhoneCalls as C

left outer join

PhoneUsers as U1

on U1.PhoneID = C.PhoneID

and U1.StartDate <= C.CallDate

and not exists (

select * from PhoneUsers as U2

where U2.PhoneID = U1.PhoneID

and U2.StartDate > U1.StartDate

and U2.StartDate <= C.CallDate

)

Steve Kass

Drew University

http://www.stevekass.com

milo68@.discussions.microsoft.com wrote:

> i have a table of phonecall records detailing amongst other things the

> time and duration of the call. there is a relationship linking this

> table to a table of handsets and from there to a table of users. this

> users table lists the start date the user had the phone. as ever sample

> data might make this easier to show:

>

> PhoneUsers table

>

> PhoneUserID Name StartDate PhoneID

>

> 1 john 1/08/2006 1

>

> 2 bob 20/08/2006 1

>

> 3 fred 2/08/2006 2

>

> etc

>

> Phones table:

>

> PhoneID

>

> 1

>

> 2

>

> etc

>

> PhoneCalls table:

>

> PhoneCallID PhoneID CallDate Duration

>

> 1 1 10/08/2006 25

>

> 2 1 23/082006 20

>

> 3 2 23/08/2006 20

>

> i want the following result set:

>

> user calldate duration

>

> john 10/08/2006 25

>

> bob 23/08/2006 20

>

> fred 23/08/2006 20

>

> essentially i am trying to link the phonecalls to the user of the phone

> at the time of the call. i make the following assumptions regarding the

> data:

>

> the user is responsible for all calls for a given handset from the

> 'startdate' until the next 'startdate' for a different user, if such a

> record exists, if not then for all calls after that date

>

>

>

> thanks

>

>

>

>

>

>

Find out the percentage complete of Stored Procedure

hi,

In my code i have to increment progress bar based on percentage
completion of Stored Procedure.I am not to get any solution on this.
Please Help me on this issue.
I am using win forms Visual studio 2005 and Sql server 2005.

Thanx in advance

NitinThere is no way to measure the progress of a single SQL command, so
what you an do depends on what is going on inside the stored
procedure. If the procedure has multiple steps you could probably
return something between each that would let the front end indicate
progress. If all the time is in one big SELECT or UPDATE or whatever,
then you can not show true progress.

Of course you could cheat and just pretend to show progress. I have
long been convinced that many progress bars are fakes intended to keep
users from getting too anxious.

Roy Harvey
Beacon Falls, CT

On 12 Sep 2006 02:21:12 -0700, nitinsharma717@.gmail.com wrote:

Quote:

Originally Posted by

>hi,
>
>In my code i have to increment progress bar based on percentage
>completion of Stored Procedure.I am not to get any solution on this.
>Please Help me on this issue.
>I am using win forms Visual studio 2005 and Sql server 2005.
>
>Thanx in advance
>
>Nitin

|||Roy Harvey wrote:

Quote:

Originally Posted by

Of course you could cheat and just pretend to show progress. I have
long been convinced that many progress bars are fakes intended to keep
users from getting too anxious.

In SQL Server you may or may not be correct. But other RDBMS products
do contain the ability to monitor progress, adjust for workload, and
accurately report back progress.

One example is Oracle's DBMS_APPLICATION_INFO built-in package
(http://www.psoug.org/reference/dbms..._info.html#aplo)
--
Daniel Morgan
University of Washington|||On Tue, 12 Sep 2006 06:24:41 -0700, DA Morgan <damorgan@.psoug.org>
wrote:

Quote:

Originally Posted by

>Roy Harvey wrote:
>

Quote:

Originally Posted by

>Of course you could cheat and just pretend to show progress. I have
>long been convinced that many progress bars are fakes intended to keep
>users from getting too anxious.

>
>In SQL Server you may or may not be correct. But other RDBMS products
>do contain the ability to monitor progress, adjust for workload, and
>accurately report back progress.
>
>One example is Oracle's DBMS_APPLICATION_INFO built-in package
>(http://www.psoug.org/reference/dbms..._info.html#aplo)

Interesting. Thanks for the enlightenment.

Roy

Find duplicates

I have a query that I used that was based on the following query that Hugo
gave me yesterday (I added CheckNumber in for the problem).
I had a problem with finding duplicate check numbers in the table
(voids,re-issued checks etc). It does show in my results, but out of
4300+checks it is hard to find all the duplicates for auditing purposes
while setting up my script.
How do I go about changing my query to show ONLY duplicates (or vice versa)?
Here is the data:
drop table EmployeePay
drop table PayDetail
CREATE TABLE [dbo].[EmployeePay] (
[EmployeePayID] [int] IDENTITY (1, 1),
[ClientID] [int] Not NULL ,
[CheckNumber] [int] Not Null,
[PayDate] [smalldatetime] Not NULL,
PRIMARY KEY (EmployeePayID)
) ON [PRIMARY]
CREATE TABLE [dbo].[PayDetail] (
[PayDetailID] [int] IDENTITY (1, 1),
[ClientID] [int] Not NULL ,
[PayDate] [smalldatetime] Not NULL ,
[Code] [varchar] (5) Not NULL ,
[Hours] [int] Not NULL ,
[Amount] [money] Not NULL,
PRIMARY KEY (PayDetailID)
) ON [PRIMARY]
insert EmployeePay values (1,1234,'01-01-05')
insert EmployeePay values (1,2255,'02-03-05')
insert EmployeePay values (2,1331,'01-01-05')
insert EmployeePay values (2,3011,'03-06-05')
insert EmployeePay values (2,1331,'05-06-05')
insert PayDetail values (1,'01-01-05','5',20,200.50)
insert PayDetail values (1,'01-01-05','6',25,10.50)
insert PayDetail values (1,'01-01-05','13',50,120.25)
insert PayDetail values (1,'02-03-05','5',5,110)
insert PayDetail values (1,'02-03-05','9',18,250.50)
insert PayDetail values (2,'01-01-05','5',50,120.25)
insert PayDetail values (2,'01-01-05','44',10,320.32)
insert PayDetail values (2,'01-01-05','32',50,120.25)
insert PayDetail values (2,'03-06-05','46',18,235.75)
insert PayDetail values (2,'05-06-05','5',-50,-120.25)
insert PayDetail values (2,'05-06-05','44',-10,-320.32)
insert PayDetail values (2,'05-06-05','32',-50,-120.25)
and the script I am using:
CREATE VIEW PayDetailWithRank
AS
SELECT pd1.ClientID, pd1.PayDate, pd1.Code, pd1.Hours, pd1.Amount,
(SELECT COUNT(*)
FROM PayDetail AS pd2
WHERE pd2.ClientID = pd1.ClientID
AND pd2.PayDate = pd1.PayDate
AND pd2.Code < pd1.Code) AS Rank
FROM PayDetail AS pd1
go
SELECT e.ClientID, e.PayDate, e.CheckNumber,
d1.Code, d1.Hours, d1.Amount,
d2.Code, d2.Hours, d2.Amount,
d3.Code, d3.Hours, d3.Amount
FROM EmployeePay AS e
INNER JOIN PayDetailWithRank AS d1
ON d1.ClientID = e.ClientID
AND d1.PayDate = e.PayDate
AND d1.Rank % 3 = 0
LEFT JOIN PayDetailWithRank AS d2
ON d2.ClientID = e.ClientID
AND d2.PayDate = e.PayDate
AND d2.Rank / 3 = d1.Rank / 3
AND d2.Rank % 3 = 1
LEFT JOIN PayDetailWithRank AS d3
ON d3.ClientID = e.ClientID
AND d3.PayDate = e.PayDate
AND d3.Rank / 3 = d1.Rank / 3
AND d3.Rank % 3 = 2
ORDER BY e.ClientID, e.PayDate, d1.Rank
go
DROP VIEW PayDetailWithRank
go
This will give you 1 duplicate check. Can I change this to show only
duplicate, somehow?
Thanks,
TomNever mind.
I figure out how. I have to modify the statement slightly
I need to change the Order by to Group By and add a "Having Count(*) > 1"
clause.
Tom
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23tVBdqhuFHA.1472@.TK2MSFTNGP15.phx.gbl...
>I have a query that I used that was based on the following query that Hugo
>gave me yesterday (I added CheckNumber in for the problem).
> I had a problem with finding duplicate check numbers in the table
> (voids,re-issued checks etc). It does show in my results, but out of
> 4300+checks it is hard to find all the duplicates for auditing purposes
> while setting up my script.
> How do I go about changing my query to show ONLY duplicates (or vice
> versa)?
> Here is the data:
> drop table EmployeePay
> drop table PayDetail
> CREATE TABLE [dbo].[EmployeePay] (
> [EmployeePayID] [int] IDENTITY (1, 1),
> [ClientID] [int] Not NULL ,
> [CheckNumber] [int] Not Null,
> [PayDate] [smalldatetime] Not NULL,
> PRIMARY KEY (EmployeePayID)
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[PayDetail] (
> [PayDetailID] [int] IDENTITY (1, 1),
> [ClientID] [int] Not NULL ,
> [PayDate] [smalldatetime] Not NULL ,
> [Code] [varchar] (5) Not NULL ,
> [Hours] [int] Not NULL ,
> [Amount] [money] Not NULL,
> PRIMARY KEY (PayDetailID)
> ) ON [PRIMARY]
> insert EmployeePay values (1,1234,'01-01-05')
> insert EmployeePay values (1,2255,'02-03-05')
> insert EmployeePay values (2,1331,'01-01-05')
> insert EmployeePay values (2,3011,'03-06-05')
> insert EmployeePay values (2,1331,'05-06-05')
> insert PayDetail values (1,'01-01-05','5',20,200.50)
> insert PayDetail values (1,'01-01-05','6',25,10.50)
> insert PayDetail values (1,'01-01-05','13',50,120.25)
> insert PayDetail values (1,'02-03-05','5',5,110)
> insert PayDetail values (1,'02-03-05','9',18,250.50)
> insert PayDetail values (2,'01-01-05','5',50,120.25)
> insert PayDetail values (2,'01-01-05','44',10,320.32)
> insert PayDetail values (2,'01-01-05','32',50,120.25)
> insert PayDetail values (2,'03-06-05','46',18,235.75)
> insert PayDetail values (2,'05-06-05','5',-50,-120.25)
> insert PayDetail values (2,'05-06-05','44',-10,-320.32)
> insert PayDetail values (2,'05-06-05','32',-50,-120.25)
>
> and the script I am using:
> CREATE VIEW PayDetailWithRank
> AS
> SELECT pd1.ClientID, pd1.PayDate, pd1.Code, pd1.Hours, pd1.Amount,
> (SELECT COUNT(*)
> FROM PayDetail AS pd2
> WHERE pd2.ClientID = pd1.ClientID
> AND pd2.PayDate = pd1.PayDate
> AND pd2.Code < pd1.Code) AS Rank
> FROM PayDetail AS pd1
> go
> SELECT e.ClientID, e.PayDate, e.CheckNumber,
> d1.Code, d1.Hours, d1.Amount,
> d2.Code, d2.Hours, d2.Amount,
> d3.Code, d3.Hours, d3.Amount
> FROM EmployeePay AS e
> INNER JOIN PayDetailWithRank AS d1
> ON d1.ClientID = e.ClientID
> AND d1.PayDate = e.PayDate
> AND d1.Rank % 3 = 0
> LEFT JOIN PayDetailWithRank AS d2
> ON d2.ClientID = e.ClientID
> AND d2.PayDate = e.PayDate
> AND d2.Rank / 3 = d1.Rank / 3
> AND d2.Rank % 3 = 1
> LEFT JOIN PayDetailWithRank AS d3
> ON d3.ClientID = e.ClientID
> AND d3.PayDate = e.PayDate
> AND d3.Rank / 3 = d1.Rank / 3
> AND d3.Rank % 3 = 2
> ORDER BY e.ClientID, e.PayDate, d1.Rank
> go
> DROP VIEW PayDetailWithRank
> go
>
> This will give you 1 duplicate check. Can I change this to show only
> duplicate, somehow?
> Thanks,
> Tom
>|||Hi
Changing the first left outer join to be an inner join will ensure at least
one duplicate.
SELECT e.ClientID, e.PayDate, e.CheckNumber,
d1.Code, d1.Hours, d1.Amount, d1.rank,
d2.Code, d2.Hours, d2.Amount, d2.rank,
d3.Code, d3.Hours, d3.Amount, d3.rank
FROM EmployeePay AS e
JOIN PayDetailWithRank AS d1
ON d1.ClientID = e.ClientID
AND d1.PayDate = e.PayDate
AND d1.Rank % 3 = 0
JOIN PayDetailWithRank AS d2
ON d2.ClientID = e.ClientID
AND d2.PayDate = e.PayDate
AND d2.Rank / 3 = d1.Rank / 3
AND d2.Rank % 3 = 1
LEFT JOIN PayDetailWithRank AS d3
ON d3.ClientID = e.ClientID
AND d3.PayDate = e.PayDate
AND d3.Rank / 3 = d1.Rank / 3
AND d3.Rank % 3 = 2
ORDER BY e.ClientID, e.PayDate, d1.Rank
What are you expecting if there are 4 duplicates?
John
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23tVBdqhuFHA.1472@.TK2MSFTNGP15.phx.gbl...
>I have a query that I used that was based on the following query that Hugo
>gave me yesterday (I added CheckNumber in for the problem).
> I had a problem with finding duplicate check numbers in the table
> (voids,re-issued checks etc). It does show in my results, but out of
> 4300+checks it is hard to find all the duplicates for auditing purposes
> while setting up my script.
> How do I go about changing my query to show ONLY duplicates (or vice
> versa)?
> Here is the data:
> drop table EmployeePay
> drop table PayDetail
> CREATE TABLE [dbo].[EmployeePay] (
> [EmployeePayID] [int] IDENTITY (1, 1),
> [ClientID] [int] Not NULL ,
> [CheckNumber] [int] Not Null,
> [PayDate] [smalldatetime] Not NULL,
> PRIMARY KEY (EmployeePayID)
> ) ON [PRIMARY]
> CREATE TABLE [dbo].[PayDetail] (
> [PayDetailID] [int] IDENTITY (1, 1),
> [ClientID] [int] Not NULL ,
> [PayDate] [smalldatetime] Not NULL ,
> [Code] [varchar] (5) Not NULL ,
> [Hours] [int] Not NULL ,
> [Amount] [money] Not NULL,
> PRIMARY KEY (PayDetailID)
> ) ON [PRIMARY]
> insert EmployeePay values (1,1234,'01-01-05')
> insert EmployeePay values (1,2255,'02-03-05')
> insert EmployeePay values (2,1331,'01-01-05')
> insert EmployeePay values (2,3011,'03-06-05')
> insert EmployeePay values (2,1331,'05-06-05')
> insert PayDetail values (1,'01-01-05','5',20,200.50)
> insert PayDetail values (1,'01-01-05','6',25,10.50)
> insert PayDetail values (1,'01-01-05','13',50,120.25)
> insert PayDetail values (1,'02-03-05','5',5,110)
> insert PayDetail values (1,'02-03-05','9',18,250.50)
> insert PayDetail values (2,'01-01-05','5',50,120.25)
> insert PayDetail values (2,'01-01-05','44',10,320.32)
> insert PayDetail values (2,'01-01-05','32',50,120.25)
> insert PayDetail values (2,'03-06-05','46',18,235.75)
> insert PayDetail values (2,'05-06-05','5',-50,-120.25)
> insert PayDetail values (2,'05-06-05','44',-10,-320.32)
> insert PayDetail values (2,'05-06-05','32',-50,-120.25)
>
> and the script I am using:
> CREATE VIEW PayDetailWithRank
> AS
> SELECT pd1.ClientID, pd1.PayDate, pd1.Code, pd1.Hours, pd1.Amount,
> (SELECT COUNT(*)
> FROM PayDetail AS pd2
> WHERE pd2.ClientID = pd1.ClientID
> AND pd2.PayDate = pd1.PayDate
> AND pd2.Code < pd1.Code) AS Rank
> FROM PayDetail AS pd1
> go
> SELECT e.ClientID, e.PayDate, e.CheckNumber,
> d1.Code, d1.Hours, d1.Amount,
> d2.Code, d2.Hours, d2.Amount,
> d3.Code, d3.Hours, d3.Amount
> FROM EmployeePay AS e
> INNER JOIN PayDetailWithRank AS d1
> ON d1.ClientID = e.ClientID
> AND d1.PayDate = e.PayDate
> AND d1.Rank % 3 = 0
> LEFT JOIN PayDetailWithRank AS d2
> ON d2.ClientID = e.ClientID
> AND d2.PayDate = e.PayDate
> AND d2.Rank / 3 = d1.Rank / 3
> AND d2.Rank % 3 = 1
> LEFT JOIN PayDetailWithRank AS d3
> ON d3.ClientID = e.ClientID
> AND d3.PayDate = e.PayDate
> AND d3.Rank / 3 = d1.Rank / 3
> AND d3.Rank % 3 = 2
> ORDER BY e.ClientID, e.PayDate, d1.Rank
> go
> DROP VIEW PayDetailWithRank
> go
>
> This will give you 1 duplicate check. Can I change this to show only
> duplicate, somehow?
> Thanks,
> Tom
>|||>> had a problem with finding duplicate check numbers in the table (voids,r
e-issued checks etc). <<
I have a better question for you: why are you creating duplicate check
numbers? Do you think that perhaps not having a proper relational key
could be part of the problem?
If you will learn to write proper DDL, you will save a lot of complex
DML.
Also, look up the ISO-8601 format for dates and times.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1126814005.385526.122020@.z14g2000cwz.googlegroups.com...
> I have a better question for you: why are you creating duplicate check
> numbers? Do you think that perhaps not having a proper relational key
> could be part of the problem?
As I had mentioned, this is not my database. I built a table to mimic that
tables I am extracting information from to create a CSV file that I can use
to import into our system. I am not creating Duplicate Check numbers - that
is what is in their system and I have to deal with it. I have over 5000
checks and as it turned out 5 duplicate check numbers. I used my group
by/having solutions to find them. As I mentioned, they were voids (hence
the duplicate check number) as well as a check that was re-issued (we don't
know why yet).
Are you suggesting I should spend my time creating a PROPER database schema,
even if it doesn't mirror what I am trying to accomplish?
And BTW, you have pounded incessantly about the evils of the IDENTITY.
I for one am grateful they used it, as you would have seen in my other post.
Hugos solution with the Views worked great, but hadn't taken into account
multiple codes on one check (which there would have been no way for him to
know this). I found that their tables used identities and that was the only
field I could replace the Code field with to make it work. It had to be
sequential and unique - Gaps would not have been an issue.
Tom
> If you will learn to write proper DDL, you will save a lot of complex
> DML.
> Also, look up the ISO-8601 format for dates and times.
>|||On Thu, 15 Sep 2005 13:40:38 -0700, tshad wrote:
(snip)
>Hugos solution with the Views worked great, but hadn't taken into account
>multiple codes on one check (which there would have been no way for him to
>know this). I found that their tables used identities and that was the onl
y
>field I could replace the Code field with to make it work. It had to be
>sequential and unique - Gaps would not have been an issue.
Hi Tom,
It would also have been solveable without the identity column. If there
was no single column to make each row unique within Client/Paydate, I'd
have used a combination of two columns. Or three. Or ... well, you get
the point. The code would have been more messy, but it would have
worked.
The only thing I would not have been able to handle were complete
duplicates (i.e. two or more rows with the same value in each of their
columns). But if you had those, you'd have much bigger problems to solve
than generating a kind-of-crosstab report. <g>
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:5oqji11o59rufd33auh0psimi867s65rj7@.
4ax.com...
> On Thu, 15 Sep 2005 13:40:38 -0700, tshad wrote:
> (snip)
> Hi Tom,
> It would also have been solveable without the identity column. If there
> was no single column to make each row unique within Client/Paydate, I'd
> have used a combination of two columns. Or three. Or ... well, you get
> the point. The code would have been more messy, but it would have
> worked.
I agree.
But in this case, there was no other set of columns (that I could make out)
that would have solved the problem. I am not sure you could have made
multiple columns work with a surrogate key, in this case. All the tables
(earnings, taxes and deductions) all could have multiple records with the
same code, date, employee number (SSN) etc on the same check. That was what
I was looking for, I just happen to notice that they have an identity field
there and it solved the problem.
Thanks,
Tom
> The only thing I would not have been able to handle were complete
> duplicates (i.e. two or more rows with the same value in each of their
> columns). But if you had those, you'd have much bigger problems to solve
> than generating a kind-of-crosstab report. <g>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)

find an article

There was an article that Ive come across and not even aware of what issue
it pertains to..
All i remember is that it talks about perfmon and based upon the ecid value
or something like that in sysprocesses, it then states to go to the thread
counter. It was pretty interesting and it was to debug something.. Does
anyone recollect this ? Not too sure if i read it at the
sql-server-performance.com site or someplace else...
maybe from the Online Seminar from PSS below
http://support.microsoft.com/default...b;en-us;838622
http://support.microsoft.com/default...Fwct041504.asp
Andy
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e%23rJovGYEHA.2944@.TK2MSFTNGP11.phx.gbl...
> There was an article that Ive come across and not even aware of what issue
> it pertains to..
> All i remember is that it talks about perfmon and based upon the ecid
value
> or something like that in sysprocesses, it then states to go to the thread
> counter. It was pretty interesting and it was to debug something.. Does
> anyone recollect this ? Not too sure if i read it at the
> sql-server-performance.com site or someplace else...
>
>

find an article

There was an article that Ive come across and not even aware of what issue
it pertains to..
All i remember is that it talks about perfmon and based upon the ecid value
or something like that in sysprocesses, it then states to go to the thread
counter. It was pretty interesting and it was to debug something.. Does
anyone recollect this ? Not too sure if i read it at the
sql-server-performance.com site or someplace else...maybe from the Online Seminar from PSS below
http://support.microsoft.com/default.aspx?scid=kb;en-us;838622
http://support.microsoft.com/default.aspx?scid=%2Fservicedesks%2Fwebcasts%2Fen%2Ftranscripts%2Fwct041504.asp
Andy
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e%23rJovGYEHA.2944@.TK2MSFTNGP11.phx.gbl...
> There was an article that Ive come across and not even aware of what issue
> it pertains to..
> All i remember is that it talks about perfmon and based upon the ecid
value
> or something like that in sysprocesses, it then states to go to the thread
> counter. It was pretty interesting and it was to debug something.. Does
> anyone recollect this ? Not too sure if i read it at the
> sql-server-performance.com site or someplace else...
>
>

find an article

There was an article that Ive come across and not even aware of what issue
it pertains to..
All i remember is that it talks about perfmon and based upon the ecid value
or something like that in sysprocesses, it then states to go to the thread
counter. It was pretty interesting and it was to debug something.. Does
anyone recollect this ? Not too sure if i read it at the
sql-server-performance.com site or someplace else...maybe from the Online Seminar from PSS below
http://support.microsoft.com/defaul...kb;en-us;838622
ranscripts%2Fwct041504.asp" target="_blank">http://support.microsoft.com/defaul...2Fwct041504.asp
Andy
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:e%23rJovGYEHA.2944@.TK2MSFTNGP11.phx.gbl...
> There was an article that Ive come across and not even aware of what issue
> it pertains to..
> All i remember is that it talks about perfmon and based upon the ecid
value
> or something like that in sysprocesses, it then states to go to the thread
> counter. It was pretty interesting and it was to debug something.. Does
> anyone recollect this ? Not too sure if i read it at the
> sql-server-performance.com site or someplace else...
>
>

Financial Periods YTD & Date Ranges

I am developing a set of reports for a site based on AS2000.

The reports will have columns for each finance period in the current year from period 01 to the current reporting period from the Primary Time dimension.

i.e. If period 4 is the default period, The report should show columns for periods 1,2,3,4 with a total that sums the four columns.

When the default period 5 the columns should be 1,2,3,4,5.

I cannot use the current year and explode that to show all periods in the year, as there are postings in future periods beyond the current accounting period that should not be displayed on the report.

I am setting the default member of the time dimension to the current reporting period. (2005/08 for example)
I am trying to use a named set based on an MDX expression to set the column range for the report.

The first test I tried was to set the MDX for the period range to some fixed values
{[Primary Time].[ACCSCAL].&[200501]:[Primary Time].[ACCSCAL].&[200508]}
This gave the expected result showing columns 01/2005 to 08/2005

I then tried changing the last period to use the default member of the dimension
{[Primary Time].[ACCSCAL].&[200501]:[Primary Time].[ACCSCAL].DefaultMember}
This again gave the expected result range and I could change the default member on the dimension and see the columns on the report update correctly.

I then tried testing that the first period can be found correctly
{[Primary Time].[ACCSCAL].&[200508].FirstSibling:[Primary Time].[ACCSCAL].&[200508]}
This again gave the expected result range from 01/2005 to 08/2005

I then tried combining the default member and first sibling calculations
{[Primary Time].[ACCSCAL].DefaultMember.FirstSibling:[Primary Time].[ACCSCAL].DefaultMember}
This gave a single column "All Primary Time", this was not what I was expecting.

So I tried changing the mdx to test the value of the first sibling of the default member, expecting 01/2005
{[Primary Time].[ACCSCAL].DefaultMember.FirstSibling}
This again gave a single column "All Primary Time".

So I then tried changing the mdx to test that a single period would work in the named set
{[Primary Time].[ACCSCAL].DefaultMember}
This again gave a single column "All Primary Time".

So I tried changing the mdx to test the value of the default member, expecting 08/2005
{[Primary Time].[ACCSCAL].&[200508]}
This again gave a single column for 08/2005 as expected

By now this has me totally confused!

Do you have any idea why the default member works when used as the second part of the range but does not work when used on its own or as the first element of the range?

Is this the correct way of achieving the report I am aiming for or is there a better method to use?

Any suggestions you have would be welcome.

Apologies is this is a FAQ.

What is the actual MDX expression for the Default Member of [Primary Time].[ACCSCAL]?|||The Default Member is [Primary Time].[ACCSCAL].&[200508]

Filters using an 'OR' condition

I am dealing with a set of 5 parameters. I want to filter the data based on
these parameters using the 'or' condition instead of an 'And'. In the filter
tab the condition defaults to an 'And' and is greyed out. How do I change it
to use an 'Or' condition.You can rewrite filter to use singe expression that combines all parameters
instead of 5 separate expressions.
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"Sumi" <Sumi@.discussions.microsoft.com> wrote in message
news:9BD59230-0A07-4AA3-9E58-4F11640D3D4A@.microsoft.com...
>I am dealing with a set of 5 parameters. I want to filter the data based on
> these parameters using the 'or' condition instead of an 'And'. In the
> filter
> tab the condition defaults to an 'And' and is greyed out. How do I change
> it
> to use an 'Or' condition.|||Combine the in one expression
fe
filter-expresion:
Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
filter-value:
=1
"Sumi" wrote:
> I am dealing with a set of 5 parameters. I want to filter the data based on
> these parameters using the 'or' condition instead of an 'And'. In the filter
> tab the condition defaults to an 'And' and is greyed out. How do I change it
> to use an 'Or' condition.|||Thank you very much! This made things very clear.
"Antoon" wrote:
> Combine the in one expression
> fe
> filter-expresion:
> Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
> filter-value:
> =1
> "Sumi" wrote:
> > I am dealing with a set of 5 parameters. I want to filter the data based on
> > these parameters using the 'or' condition instead of an 'And'. In the filter
> > tab the condition defaults to an 'And' and is greyed out. How do I change it
> > to use an 'Or' condition.|||Antoon,
Thanks a bundle.I tried this and it works wonderfully. But this has
triggered off another question.
I have a stored proc that takes in Startdate and Enddate as two parameters.
Then I have a set of 5 parameters which I am using to further filter the
data, this is to give the user flexibility to choose whichever parameter they
want. These parameters have a drodown box which has all the values they can
select along with a blank row, incase the user does not want to use that
parameter to filter.
Using the expression described below, I can filter by one or more
parameters, but if I select blank for all the 5 parameter, I was expecting
the expression to be ignored and the result set to have all data for the
entered Startdate and EndDate. Which are not a part of this filter
expression. But the report pulls up blank.
Any ideas as to how to fix this?
filter-expresion:
Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
filter-value:
=1
"Antoon" wrote:
> Combine the in one expression
> fe
> filter-expresion:
> Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
> filter-value:
> =1
> "Sumi" wrote:
> > I am dealing with a set of 5 parameters. I want to filter the data based on
> > these parameters using the 'or' condition instead of an 'And'. In the filter
> > tab the condition defaults to an 'And' and is greyed out. How do I change it
> > to use an 'Or' condition.|||I use a specific value for the all parameter
Select parname, parvalue from parlist
union
select '<All>', -1 from dual
You can then filter in the query
Select ...
where
... (table.value = :parameter or :paremeter = -1)
or you can change the filter, and add
Parameters!par1.Value = -1, 1
to the switch statement
(I work in oracle)
"Sumi" wrote:
> Antoon,
> Thanks a bundle.I tried this and it works wonderfully. But this has
> triggered off another question.
> I have a stored proc that takes in Startdate and Enddate as two parameters.
> Then I have a set of 5 parameters which I am using to further filter the
> data, this is to give the user flexibility to choose whichever parameter they
> want. These parameters have a drodown box which has all the values they can
> select along with a blank row, incase the user does not want to use that
> parameter to filter.
> Using the expression described below, I can filter by one or more
> parameters, but if I select blank for all the 5 parameter, I was expecting
> the expression to be ignored and the result set to have all data for the
> entered Startdate and EndDate. Which are not a part of this filter
> expression. But the report pulls up blank.
> Any ideas as to how to fix this?
> filter-expresion:
> Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
> filter-value:
> =1
>
> "Antoon" wrote:
> > Combine the in one expression
> > fe
> > filter-expresion:
> > Switch( Parameters!par1.Value = x or Parameters!par2.Value = y, 1, true, 0)
> > filter-value:
> > =1
> > "Sumi" wrote:
> >
> > > I am dealing with a set of 5 parameters. I want to filter the data based on
> > > these parameters using the 'or' condition instead of an 'And'. In the filter
> > > tab the condition defaults to an 'And' and is greyed out. How do I change it
> > > to use an 'Or' condition.|||The only way I've achieved any 'clever' from parameters regardin
optional parameters ands and ors is by building up a where string o
the fly
DECLARE @.select as varchar(100
DECLARE @.where as varchar(100
DECLARE @.sql as varchar(100
SET @.select = 'SELECT * FROM poext WHERE '
SET @.where = ''
IF @.vendor <> 'not set' SET @.where = @.where + 'vendor = '''
@.vendor + ''''
IF len(@.where) > 0 SET @.where = @.where + ' and '
IF @.buyer <> - 1 SET @.where = @.where + 'buyer = '
cast(@.buyer as varchar(5))
if len(@.where)>0 and right(@.where,5) <> ' an
' SET @.where = @.where + ' and '
set @.where = @.where + 'orddate between '''+@.orderdatefrom + ''' an
''' + @.orderdateto + ''''
SET @.sql = @.select + @.where
EXEC sp_executesql @.sq
And use that as my dataset query. This works fine, although you hav
to refresh it a few times or the preview doesn't work, it tends t
get a little confused as it has no idea about the data you're pullin
until it's got it.[/code

Filters Date

Hi:
How do I filter the output based on a date. I tried with CDate, without
CDate, with quotes, without quotes, putting time in the date. Nothing seems
to work. keep getting this error
Expression: =CDate(Fields!ISSUEDDATE.Value)
Value: CDate('1/1/2004')
An error has occurred during report processing. (rsProcessingAborted) Get
Online Help
The processing of filter expression for the table 'table1' cannot be
performed. The comparison failed. Please check the data type returned by
filter expression. (rsProcessingError) Get Online Help
Please suggest the write combination.
ThanksWhen doing expressions you need to use VB classes/methods. In particular
what you are looking for is the convert class.
The following will work:
expression Operator Value
=Fields!datetime.Value >
=convert.ToDatetime("9/17/2004 00:00:00")
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"NI" <NI@.discussions.microsoft.com> wrote in message
news:29C16728-4FD7-47E3-9B83-D4D5A71AA990@.microsoft.com...
> Hi:
> How do I filter the output based on a date. I tried with CDate, without
> CDate, with quotes, without quotes, putting time in the date. Nothing
seems
> to work. keep getting this error
> Expression: =CDate(Fields!ISSUEDDATE.Value)
> Value: CDate('1/1/2004')
>
> An error has occurred during report processing. (rsProcessingAborted) Get
> Online Help
> The processing of filter expression for the table 'table1' cannot be
> performed. The comparison failed. Please check the data type returned by
> filter expression. (rsProcessingError) Get Online Help
> Please suggest the write combination.
> Thanks
>
>
>|||Thanks Bruce. That worked :)
"Bruce L-C [MVP]" wrote:
> When doing expressions you need to use VB classes/methods. In particular
> what you are looking for is the convert class.
> The following will work:
> expression Operator Value
> =Fields!datetime.Value >
> =convert.ToDatetime("9/17/2004 00:00:00")
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "NI" <NI@.discussions.microsoft.com> wrote in message
> news:29C16728-4FD7-47E3-9B83-D4D5A71AA990@.microsoft.com...
> > Hi:
> >
> > How do I filter the output based on a date. I tried with CDate, without
> > CDate, with quotes, without quotes, putting time in the date. Nothing
> seems
> > to work. keep getting this error
> >
> > Expression: =CDate(Fields!ISSUEDDATE.Value)
> > Value: CDate('1/1/2004')
> >
> >
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > The processing of filter expression for the table 'table1' cannot be
> > performed. The comparison failed. Please check the data type returned by
> > filter expression. (rsProcessingError) Get Online Help
> >
> > Please suggest the write combination.
> >
> > Thanks
> >
> >
> >
> >
> >
> >
>
>|||Bruce, that worked for me too.
However, I am trying to pass in parameter @.Start_Date.
I have tried the following and I cannot get it to work.
convert.ToDatetime("@.Start_Date 00:00:00")
convert.ToDatetime(@.Start_Date + " 00:00:00")
convert.ToDatetime(Start_Date + " 00:00:00")
Any ideas?
Thanks.
"Bruce L-C [MVP]" wrote:
> When doing expressions you need to use VB classes/methods. In particular
> what you are looking for is the convert class.
> The following will work:
> expression Operator Value
> =Fields!datetime.Value >
> =convert.ToDatetime("9/17/2004 00:00:00")
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "NI" <NI@.discussions.microsoft.com> wrote in message
> news:29C16728-4FD7-47E3-9B83-D4D5A71AA990@.microsoft.com...
> > Hi:
> >
> > How do I filter the output based on a date. I tried with CDate, without
> > CDate, with quotes, without quotes, putting time in the date. Nothing
> seems
> > to work. keep getting this error
> >
> > Expression: =CDate(Fields!ISSUEDDATE.Value)
> > Value: CDate('1/1/2004')
> >
> >
> > An error has occurred during report processing. (rsProcessingAborted) Get
> > Online Help
> > The processing of filter expression for the table 'table1' cannot be
> > performed. The comparison failed. Please check the data type returned by
> > filter expression. (rsProcessingError) Get Online Help
> >
> > Please suggest the write combination.
> >
> > Thanks
> >
> >
> >
> >
> >
> >
>
>

Filtering Table name with Parameter

<br><br>I obtain table names from a database and pass them to a dropdownlist. Based on user selection, I want to pass each table name to a query.Here is an extract from my code: <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="select * from @.dDTable"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" Name="dDTable" PropertyName="SelectedValue" DefaultValue="product" Direction="InputOutput" Size="15" Type="String" /> </SelectParameters> </asp:SqlDataSource>I keep getting this error: Must declare the table variable "@.dDTable".Please does anyone knows the best way to go about this?

Hi,

thats not working. Dynamic table name are allowed in here. You could use a stored procedure instead which then uses dynamic sql to execute the query that was first concatenated (with the table name).

But you should be aware of the curse of dynamic SQL :-)

http://www.sommarskog.se/dynamic_sql.html

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||Thanks.

Filtering records between 2 dates for the report(Using CR 9.2)

HI,
I am developing this application which stores purchases requisitions information from the user in a d/b.I have to generate reports based on the user inputted dates.The reports should list all the records between the 2 dates which the user inputs at run time.
This is the code I have provided to do the filtering of records for the CR.
********************************************************
MsgBox "Please enter 2 dates in between which to generate the Cost allocation Report", vbOKOnly + vbInformation, "Printing Reports.."
date1 = InputBox("Please enter the starting date for the Report!", "Cost Allocation Report")
date2 = InputBox("please enter the ending date for the Report!", "Cost Allocation Report")
date1 = CDate(MakeDateFormated(date1, "/"))
date2 = CDate(MakeDateFormated(date2, "/"))
Dim str3
str3 = "{purchase.invno}<> '" & invnonil & "' AND {purchase.podate} >= '" & date1 & "' AND {purchase.podate} <= '" & date2 & "'"
CrystalReport6.RecordSelectionFormula = str3
CrystalReport6.PrintOut True, 1
CrystalReport6.DiscardSavedData
********************************************************
Date1 and date 2 are of type Date.
When this code is run,I get an error message which says that "a Date-Time is required here" for the line giving the Record selection formula.
When I checked the CR in the designer,the datatype of the field "podate" is shown as Date-Time instead of just Date,even though,it is of type date in the Database.
I have tried deleting the field and adding it again to the report.It still shows datatype to be Date-Time.
How can I change this?
Please help me as I am very very new to this area.
Thank you for your time.Your help is highly appreciated.
Lakshmi VinayTry this

str3 = "{purchase.invno}<> '" & invnonil & "' AND {purchase.podate} >= #" & date1 & "# AND {purchase.podate} <= #" & date2 & "#"