Hi everyone..
I have a table with about 40,000 records in it.
I want to find records where all of the values in all of the fields, except for the unique field are equal.
The records in this table are answers to a online survey.
So, I want to find all of the records where people answered the questions exactly the same as someone else.
Any help would be appreciated.
NickieWhat about this:
select list of fields
from yourtable
group by list of fields having count(*)>1
Showing posts with label values. Show all posts
Showing posts with label values. Show all posts
Wednesday, March 28, 2012
finding duplicate values
Here's my situation: I work at a healthcare testing labratory, I have a table with all our tests, Dim_Test. The two columns i want to look at are: [Mnemonic] and [Test Name]. Mnemonic is the short test name and Test Name is a long name. The condition of the data is that there are a few duplicate mnemonics, but the the duplicates have different long test names. I know this is really messed up but thats our lab system for you, i have no control over that. I want to make a table that has all mnemonics that are duplicated, and the Long test name for each of the duplications. I have come up with a couple possible solutions but i dont know how to implement them or which would be the best course of action.
The First: Sort on the Mnemonic and delete duplicates, store that to a temp table. Then somehow compare Dim_test and the temp table to find the rows missing from the temp table and then store that to, say, temp2. So temp2 would contain all the data that was deleted during the deletion of duplicates on Dim_test. Then i could use the input Dim_test and do a lookup on table temp2 which would give me all mnemonics and test names with duplicate mnemonics.
The Second and third: Somehow get a dataset that has the distinct mnemonic and the count of each distinct mnemonic. Then i could use a conditional split to find out which mnemonics have a count of greater than one. then i can store that to a temp table and lookup on that. That temp table would be the same as temp2 in the first solution. The difference between the second and third solutions is that one uses an aggregate transformation and the other uses a pivot transformation.
I think the pivot transformation would work best if i pivoted on mnemonic but i dont know how to set it up. Please help. Thank you.
You don't need SSIS for this. This T-SQL will do the trick:
Code Snippet
use tempdb
go
CREATE Table [Dim_Test]
(
[Mnemonic] NVARCHAR (20),
[Test Name] NVARCHAR (200)
)
INSERT INTO [Dim_Test] VALUES ('AAA', 'This is a long test name 1')
INSERT INTO [Dim_Test] VALUES ('BBB', 'This is a long test name 2')
INSERT INTO [Dim_Test] VALUES ('CCC', 'This is a long test name 3')
INSERT INTO [Dim_Test] VALUES ('DDD', 'This is a long test name 4')
INSERT INTO [Dim_Test] VALUES ('EEE', 'This is a long test name 5')
INSERT INTO [Dim_Test] VALUES ('AAA', 'This is a long test name 6')
INSERT INTO [Dim_Test] VALUES ('FFF', 'This is a long test name 7')
INSERT INTO [Dim_Test] VALUES ('BBB', 'This is a long test name 8')
INSERT INTO [Dim_Test] VALUES ('AAA', 'This is a long test name 9')
GO
WITH DuplicatesCTE
AS
(
SELECT [Mnemonic]
FROM [Dim_Test]
GROUP BY [Mnemonic]
HAVING COUNT (*) > 1
)
SELECT [Dim_Test].*
FROM [Dim_Test]
INNER JOIN [DuplicatesCTE]
ON [Dim_Test].[Mnemonic] = [DuplicatesCTE].[Mnemonic]
If your source system is using SQL 2000 you will have to replace the CTE with a derived table, but the technique is the same.
Does this give you what you need?
|||select a.mnemonic, a.[test name] from dim_test a inner join(select mnemonic, count(*) from dim_test group by mnemonic having count(*) > 1) b
on a.mnemonic = b.mnemonic
This should give you a list of all of the duplicate mnemonics with their [test name]s.
If you want to create a NEW table with this information add an "into tablename" clause like so:
select a.mnemonic, a.[test name] into tablename
from dim_test a inner join
(select mnemonic, count(*) from dim_test group by mnemonic having count(*) > 1) b
on a.mnemonic = b.mnemonic|||
Both of these seemed to work, i ended up using Matthew's solution. I should really learn more about the SQL language, i pretty much do everything in SSIS. Thank you both very much.
Matthew could you please help me compact this. Please excuse my ignorance of the basic SQL language. So all the data is in Dim_test and the part i left out before was that there is also data with duplicate Mnemonics and Test Names example:
VALUES ('AAA', 'This is a long test name 1')
VALUES ('AAA', 'This is a long test name 1')
So this is what i want to do in a Stored procedure, and I need to do it without changing any data in Dim_test:
1. Disregard all duplicates of Mnemonic AND [Test Name] in Dim_test
2. DELETE FROM DupMnemonics
3. Store the results of your query in DupMnemonics
Thank you so much.
This is the query i ended up using
WITH DuplicatesCTE AS (SELECT Mnemonic
FROM DupMnemonics
GROUP BY Mnemonic
HAVING (COUNT(*) > 1))
SELECT DupMnemonics_1.Mnemonic, DupMnemonics_1.[Test Name]
FROM DupMnemonics AS DupMnemonics_1 INNER JOIN
DuplicatesCTE AS DuplicatesCTE_1 ON DupMnemonics_1.Mnemonic = DuplicatesCTE_1.Mnemonic
You can use a CTE to do this as well, when used in combination with the ROW_NUMBER ranking funcctiokn. Here's an example:
http://www.eggheadcafe.com/software/aspnet/29974931/removing-duplicate-rows-.aspx
|||
MatthewRoche wrote:
You can use a CTE to do this as well, when used in combination with the ROW_NUMBER ranking funcctiokn. Here's an example:
http://www.eggheadcafe.com/software/aspnet/29974931/removing-duplicate-rows-.aspx
Yeah, to the OP, there are so many ways to achieve this, you may want to move your discussions to the Transact-SQL forum. This is, after all, off topic in the SSIS forum... 
|||
Is there a way to just move this thread over to T-SQL
Thank you all.
|||
Is there a way to just move this thread over to T-SQL
Thank you all.
There is. I'll do that now for you.
Finding Duplicate Records
CREATE TABLE KeyLetter(
KeyNum int,
Letter char(1))
INSERT INTO KeyLetter VALUES(111111, 'a')
INSERT INTO KeyLetter VALUES(111111, 'b')
INSERT INTO KeyLetter VALUES(111111, 'c')
INSERT INTO KeyLetter VALUES(222222, 'a')
INSERT INTO KeyLetter VALUES(222222, 'b')
INSERT INTO KeyLetter VALUES(222222, 'c')
INSERT INTO KeyLetter VALUES(111111, 'a')
INSERT INTO KeyLetter VALUES(222222, 'a')
Given the above records, I have written the following to identify duplicate
records, but in my real life situation, I have a table with far more columns
to evaluate for duplication, and I was wondering if there is a better way to
identify duplicates such as this, without performing concatenation?
SELECT Cast(KeyNum as varchar(6)) + Letter),
COUNT(Cast(KeyNum as varchar(6)) + Letter))
FROM KeyLetter
GROUP BY Cast(KeyNum as varchar(6)) + Letter)
HAVING COUNT(Cast(KeyNum as varchar(6)) + Letter)) > 1
--
Message posted via http://www.sqlmonster.comUmm, anything wrong with this:
select KeyNum, Letter, count(*)
from KeyLetter
group by KeyNum, Letter
having count(*)>1
MC
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7989a4847c279@.uwe...
> CREATE TABLE KeyLetter(
> KeyNum int,
> Letter char(1))
> INSERT INTO KeyLetter VALUES(111111, 'a')
> INSERT INTO KeyLetter VALUES(111111, 'b')
> INSERT INTO KeyLetter VALUES(111111, 'c')
> INSERT INTO KeyLetter VALUES(222222, 'a')
> INSERT INTO KeyLetter VALUES(222222, 'b')
> INSERT INTO KeyLetter VALUES(222222, 'c')
> INSERT INTO KeyLetter VALUES(111111, 'a')
> INSERT INTO KeyLetter VALUES(222222, 'a')
> Given the above records, I have written the following to identify
> duplicate
> records, but in my real life situation, I have a table with far more
> columns
> to evaluate for duplication, and I was wondering if there is a better way
> to
> identify duplicates such as this, without performing concatenation?
> SELECT Cast(KeyNum as varchar(6)) + Letter),
> COUNT(Cast(KeyNum as varchar(6)) + Letter))
> FROM KeyLetter
> GROUP BY Cast(KeyNum as varchar(6)) + Letter)
> HAVING COUNT(Cast(KeyNum as varchar(6)) + Letter)) > 1
> --
> Message posted via http://www.sqlmonster.com
>
KeyNum int,
Letter char(1))
INSERT INTO KeyLetter VALUES(111111, 'a')
INSERT INTO KeyLetter VALUES(111111, 'b')
INSERT INTO KeyLetter VALUES(111111, 'c')
INSERT INTO KeyLetter VALUES(222222, 'a')
INSERT INTO KeyLetter VALUES(222222, 'b')
INSERT INTO KeyLetter VALUES(222222, 'c')
INSERT INTO KeyLetter VALUES(111111, 'a')
INSERT INTO KeyLetter VALUES(222222, 'a')
Given the above records, I have written the following to identify duplicate
records, but in my real life situation, I have a table with far more columns
to evaluate for duplication, and I was wondering if there is a better way to
identify duplicates such as this, without performing concatenation?
SELECT Cast(KeyNum as varchar(6)) + Letter),
COUNT(Cast(KeyNum as varchar(6)) + Letter))
FROM KeyLetter
GROUP BY Cast(KeyNum as varchar(6)) + Letter)
HAVING COUNT(Cast(KeyNum as varchar(6)) + Letter)) > 1
--
Message posted via http://www.sqlmonster.comUmm, anything wrong with this:
select KeyNum, Letter, count(*)
from KeyLetter
group by KeyNum, Letter
having count(*)>1
MC
"cbrichards via SQLMonster.com" <u3288@.uwe> wrote in message
news:7989a4847c279@.uwe...
> CREATE TABLE KeyLetter(
> KeyNum int,
> Letter char(1))
> INSERT INTO KeyLetter VALUES(111111, 'a')
> INSERT INTO KeyLetter VALUES(111111, 'b')
> INSERT INTO KeyLetter VALUES(111111, 'c')
> INSERT INTO KeyLetter VALUES(222222, 'a')
> INSERT INTO KeyLetter VALUES(222222, 'b')
> INSERT INTO KeyLetter VALUES(222222, 'c')
> INSERT INTO KeyLetter VALUES(111111, 'a')
> INSERT INTO KeyLetter VALUES(222222, 'a')
> Given the above records, I have written the following to identify
> duplicate
> records, but in my real life situation, I have a table with far more
> columns
> to evaluate for duplication, and I was wondering if there is a better way
> to
> identify duplicates such as this, without performing concatenation?
> SELECT Cast(KeyNum as varchar(6)) + Letter),
> COUNT(Cast(KeyNum as varchar(6)) + Letter))
> FROM KeyLetter
> GROUP BY Cast(KeyNum as varchar(6)) + Letter)
> HAVING COUNT(Cast(KeyNum as varchar(6)) + Letter)) > 1
> --
> Message posted via http://www.sqlmonster.com
>
Finding Consecutive Values
I'm stuck in trying to find out how to solve this.
Within a record set, I need to know when a id has been a certain value
for x consecutive years. For example,
Create table #Test (
tid int NOT NULL,
yr int NOT NULL,
value int NOT NULL)
insert into #Test values (100, 1998, 0)
insert into #Test values (100, 1999, 0)
insert into #Test values (100, 2000, 0)
insert into #Test values (100, 2001, 0)
insert into #Test values (100, 2002, 1)
insert into #Test values (100, 2003, 0)
insert into #Test values (100, 2004, 0)
insert into #Test values (100, 2005, 0)
insert into #Test values (100, 2006, 0)
insert into #Test values (100, 2007, 0)
insert into #Test values (100, 2008, 1)
insert into #Test values (200, 1999, 0)
insert into #Test values (200, 2001, 0)
insert into #Test values (200, 2002, 0)
insert into #Test values (300, 2001, 0)
insert into #Test values (300, 2002, 0)
insert into #Test values (300, 2003, 0)
insert into #Test values (300, 2004, 0)
insert into #Test values (300, 2005, 0)
FIND: tid, startdate, enddate when value is 0 for 4 or more consecutive
months.
RESULTS would yield
tid startdate enddate years
100 1998 2001 4
100 2003 2007 5
300 2001 2005 5
TIAselect t_from.tid, t_from.yr start_year, t_to.yr end_year, t_from.value
from
(select tid, yr, value from Test t1 where not exists(
select 1 from Test t2 where t1.tid=t2.tid and t1.value=t2.value and
t1.yr=(t2.yr+1))
)t_from
join
(select tid, yr, value from Test t1 where not exists(
select 1 from Test t2 where t1.tid=t2.tid and t1.value=t2.value and
t1.yr=(t2.yr-1))
)t_to
on t_from.tid=t_to.tid and t_from.value=t_to.value and
(t_from.yr+3)<=t_to.yr
and t_from.yr + (select count(*) from Test t where t_from.tid=t.tid
and t_from.yr<t.yr and t.yr<t_to.yr and t_from.value=t.value) + 1 =
t_to.yr
order by t_from.tid, t_from.yr
tid start_year end_year value
-- -- -- --
100 1998 2001 0
100 2003 2007 0
300 2001 2005 0
(3 row(s) affected)|||select tid, min(yr) as start, maxyr as [end], count(*) as yrs
from (
select *,
isnull(
(select max(yr)
from #test t2
where t2.tid=t1.tid
and t2.yr>=t1.yr
and t2.value=0
and t2.yr<=(select min(yr) from #test where tid=t2.tid and
yr>=t1.yr and value=1)
),
(select max(yr)
from #test t3
where t3.tid=t1.tid
)
) as maxyr
from #test t1
where value=0
) t4
group by tid, maxyr
having count(*)>=4
order by tid, start
carmaboy@.gmail.com wrote:
> I'm stuck in trying to find out how to solve this.
> Within a record set, I need to know when a id has been a certain value
> for x consecutive years. For example,
> Create table #Test (
> tid int NOT NULL,
> yr int NOT NULL,
> value int NOT NULL)
> insert into #Test values (100, 1998, 0)
> insert into #Test values (100, 1999, 0)
> insert into #Test values (100, 2000, 0)
> insert into #Test values (100, 2001, 0)
> insert into #Test values (100, 2002, 1)
> insert into #Test values (100, 2003, 0)
> insert into #Test values (100, 2004, 0)
> insert into #Test values (100, 2005, 0)
> insert into #Test values (100, 2006, 0)
> insert into #Test values (100, 2007, 0)
> insert into #Test values (100, 2008, 1)
> insert into #Test values (200, 1999, 0)
> insert into #Test values (200, 2001, 0)
> insert into #Test values (200, 2002, 0)
> insert into #Test values (300, 2001, 0)
> insert into #Test values (300, 2002, 0)
> insert into #Test values (300, 2003, 0)
> insert into #Test values (300, 2004, 0)
> insert into #Test values (300, 2005, 0)
>
> FIND: tid, startdate, enddate when value is 0 for 4 or more consecutive
> months.
> RESULTS would yield
> tid startdate enddate years
> 100 1998 2001 4
> 100 2003 2007 5
> 300 2001 2005 5
> TIA
>|||A different, shorter version:
DROP TABLE Foobar;
CREATE TABLE Foobar
(tid INTEGER NOT NULL,
yr INTEGER NOT NULL,
value INTEGER NOT NULL);
INSERT INTO Foobar VALUES (100, 1998, 0);
INSERT INTO Foobar VALUES (100, 1999, 0);
INSERT INTO Foobar VALUES (100, 2000, 0);
INSERT INTO Foobar VALUES (100, 2001, 0);
INSERT INTO Foobar VALUES (100, 2002, 1);
INSERT INTO Foobar VALUES (100, 2003, 0);
INSERT INTO Foobar VALUES (100, 2004, 0);
INSERT INTO Foobar VALUES (100, 2005, 0);
INSERT INTO Foobar VALUES (100, 2006, 0);
INSERT INTO Foobar VALUES (100, 2007, 0);
INSERT INTO Foobar VALUES (100, 2008, 1);
INSERT INTO Foobar VALUES (200, 1999, 0);
INSERT INTO Foobar VALUES (200, 2001, 0);
INSERT INTO Foobar VALUES (200, 2002, 0);
INSERT INTO Foobar VALUES (300, 2001, 0);
INSERT INTO Foobar VALUES (300, 2002, 0);
INSERT INTO Foobar VALUES (300, 2003, 0);
INSERT INTO Foobar VALUES (300, 2004, 0);
INSERT INTO Foobar VALUES (300, 2005, 0);
SELECT X.tid, MIN(X.yr), X.end_yr, (X.end_yr - MIN(X.yr)) AS duration
FROM
(SELECT F1.tid, F1.yr, MAX(F2.yr) AS end_yr
FROM Foobar AS F1, Foobar AS F2
WHERE F1.yr < F2.yr
AND F1.tid = F2.tid
AND 0
= ALL (SELECT value
FROM Foobar AS F3
WHERE F1.tid = F3.tid
AND F3.yr BETWEEN F1.yr AND F2.yr)
AND (F2.yr - F1.yr +1)
= (SELECT COUNT(*)
FROM Foobar AS F4
WHERE F1.tid = F4.tid
AND F4.yr BETWEEN F1.yr AND F2.yr)
GROUP BY F1.tid, F1.yr) AS X(tid, yr, end_yr)
GROUP BY X.tid, X.end_yr
HAVING (X.end_yr - MIN(X.yr)) > 1;|||A thoughful solution that leaves one to ponder the rationality
of the methodology and of the environment that spawned it :)
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:e5k25kf9FHA.2844@.TK2MSFTNGP10.phx.gbl...
> select tid, min(yr) as start, maxyr as [end], count(*) as yrs
> from (
> select *,
> isnull(
> (select max(yr)
> from #test t2
> where t2.tid=t1.tid
> and t2.yr>=t1.yr
> and t2.value=0
> and t2.yr<=(select min(yr) from #test where tid=t2.tid and
> yr>=t1.yr and value=1)
> ),
> (select max(yr)
> from #test t3
> where t3.tid=t1.tid
> )
> ) as maxyr
> from #test t1
> where value=0
> ) t4
> group by tid, maxyr
> having count(*)>=4
> order by tid, start
>
> carmaboy@.gmail.com wrote:|||this problem would be solved like that? ;)
CREATE TABLE Puzzles..YearVals(
tid INTEGER NOT NULL,
yr INTEGER NOT NULL,
value INTEGER NOT NULL,
PRIMARY KEY (tid, yr));
INSERT INTO Puzzles..YearVals VALUES (100, 1998, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 1999, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2000, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2001, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2002, 1);
INSERT INTO Puzzles..YearVals VALUES (100, 2003, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2004, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2005, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2006, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2007, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2008, 1);
INSERT INTO Puzzles..YearVals VALUES (200, 1999, 0);
--INSERT INTO Puzzles..YearVals VALUES (200, 2000, 1);
INSERT INTO Puzzles..YearVals VALUES (200, 2001, 0);
INSERT INTO Puzzles..YearVals VALUES (200, 2002, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2001, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2002, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2003, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2004, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2005, 0);
SELECT tid, MIN(yr) AS beg_yr, MAX(yr) AS fin_yr, COUNT(*) AS tally
FROM
(SELECT B.tid,B.yr,B.yr-COUNT(*)
FROM Puzzles..YearVals AS B
JOIN Puzzles..YearVals AS L
ON L.tid = B.tid AND L.yr <= B.yr AND L.value=B.value
WHERE B.value = 0
GROUP BY B.tid,B.yr) AS G(tid,yr,grp)
GROUP BY grp,tid
HAVING COUNT(*) >= 4|||rrr... i did not remove a reference on my local db
sorry|||Thanks to everyone for taking the time to help. I should have all I
need to get my results.
Within a record set, I need to know when a id has been a certain value
for x consecutive years. For example,
Create table #Test (
tid int NOT NULL,
yr int NOT NULL,
value int NOT NULL)
insert into #Test values (100, 1998, 0)
insert into #Test values (100, 1999, 0)
insert into #Test values (100, 2000, 0)
insert into #Test values (100, 2001, 0)
insert into #Test values (100, 2002, 1)
insert into #Test values (100, 2003, 0)
insert into #Test values (100, 2004, 0)
insert into #Test values (100, 2005, 0)
insert into #Test values (100, 2006, 0)
insert into #Test values (100, 2007, 0)
insert into #Test values (100, 2008, 1)
insert into #Test values (200, 1999, 0)
insert into #Test values (200, 2001, 0)
insert into #Test values (200, 2002, 0)
insert into #Test values (300, 2001, 0)
insert into #Test values (300, 2002, 0)
insert into #Test values (300, 2003, 0)
insert into #Test values (300, 2004, 0)
insert into #Test values (300, 2005, 0)
FIND: tid, startdate, enddate when value is 0 for 4 or more consecutive
months.
RESULTS would yield
tid startdate enddate years
100 1998 2001 4
100 2003 2007 5
300 2001 2005 5
TIAselect t_from.tid, t_from.yr start_year, t_to.yr end_year, t_from.value
from
(select tid, yr, value from Test t1 where not exists(
select 1 from Test t2 where t1.tid=t2.tid and t1.value=t2.value and
t1.yr=(t2.yr+1))
)t_from
join
(select tid, yr, value from Test t1 where not exists(
select 1 from Test t2 where t1.tid=t2.tid and t1.value=t2.value and
t1.yr=(t2.yr-1))
)t_to
on t_from.tid=t_to.tid and t_from.value=t_to.value and
(t_from.yr+3)<=t_to.yr
and t_from.yr + (select count(*) from Test t where t_from.tid=t.tid
and t_from.yr<t.yr and t.yr<t_to.yr and t_from.value=t.value) + 1 =
t_to.yr
order by t_from.tid, t_from.yr
tid start_year end_year value
-- -- -- --
100 1998 2001 0
100 2003 2007 0
300 2001 2005 0
(3 row(s) affected)|||select tid, min(yr) as start, maxyr as [end], count(*) as yrs
from (
select *,
isnull(
(select max(yr)
from #test t2
where t2.tid=t1.tid
and t2.yr>=t1.yr
and t2.value=0
and t2.yr<=(select min(yr) from #test where tid=t2.tid and
yr>=t1.yr and value=1)
),
(select max(yr)
from #test t3
where t3.tid=t1.tid
)
) as maxyr
from #test t1
where value=0
) t4
group by tid, maxyr
having count(*)>=4
order by tid, start
carmaboy@.gmail.com wrote:
> I'm stuck in trying to find out how to solve this.
> Within a record set, I need to know when a id has been a certain value
> for x consecutive years. For example,
> Create table #Test (
> tid int NOT NULL,
> yr int NOT NULL,
> value int NOT NULL)
> insert into #Test values (100, 1998, 0)
> insert into #Test values (100, 1999, 0)
> insert into #Test values (100, 2000, 0)
> insert into #Test values (100, 2001, 0)
> insert into #Test values (100, 2002, 1)
> insert into #Test values (100, 2003, 0)
> insert into #Test values (100, 2004, 0)
> insert into #Test values (100, 2005, 0)
> insert into #Test values (100, 2006, 0)
> insert into #Test values (100, 2007, 0)
> insert into #Test values (100, 2008, 1)
> insert into #Test values (200, 1999, 0)
> insert into #Test values (200, 2001, 0)
> insert into #Test values (200, 2002, 0)
> insert into #Test values (300, 2001, 0)
> insert into #Test values (300, 2002, 0)
> insert into #Test values (300, 2003, 0)
> insert into #Test values (300, 2004, 0)
> insert into #Test values (300, 2005, 0)
>
> FIND: tid, startdate, enddate when value is 0 for 4 or more consecutive
> months.
> RESULTS would yield
> tid startdate enddate years
> 100 1998 2001 4
> 100 2003 2007 5
> 300 2001 2005 5
> TIA
>|||A different, shorter version:
DROP TABLE Foobar;
CREATE TABLE Foobar
(tid INTEGER NOT NULL,
yr INTEGER NOT NULL,
value INTEGER NOT NULL);
INSERT INTO Foobar VALUES (100, 1998, 0);
INSERT INTO Foobar VALUES (100, 1999, 0);
INSERT INTO Foobar VALUES (100, 2000, 0);
INSERT INTO Foobar VALUES (100, 2001, 0);
INSERT INTO Foobar VALUES (100, 2002, 1);
INSERT INTO Foobar VALUES (100, 2003, 0);
INSERT INTO Foobar VALUES (100, 2004, 0);
INSERT INTO Foobar VALUES (100, 2005, 0);
INSERT INTO Foobar VALUES (100, 2006, 0);
INSERT INTO Foobar VALUES (100, 2007, 0);
INSERT INTO Foobar VALUES (100, 2008, 1);
INSERT INTO Foobar VALUES (200, 1999, 0);
INSERT INTO Foobar VALUES (200, 2001, 0);
INSERT INTO Foobar VALUES (200, 2002, 0);
INSERT INTO Foobar VALUES (300, 2001, 0);
INSERT INTO Foobar VALUES (300, 2002, 0);
INSERT INTO Foobar VALUES (300, 2003, 0);
INSERT INTO Foobar VALUES (300, 2004, 0);
INSERT INTO Foobar VALUES (300, 2005, 0);
SELECT X.tid, MIN(X.yr), X.end_yr, (X.end_yr - MIN(X.yr)) AS duration
FROM
(SELECT F1.tid, F1.yr, MAX(F2.yr) AS end_yr
FROM Foobar AS F1, Foobar AS F2
WHERE F1.yr < F2.yr
AND F1.tid = F2.tid
AND 0
= ALL (SELECT value
FROM Foobar AS F3
WHERE F1.tid = F3.tid
AND F3.yr BETWEEN F1.yr AND F2.yr)
AND (F2.yr - F1.yr +1)
= (SELECT COUNT(*)
FROM Foobar AS F4
WHERE F1.tid = F4.tid
AND F4.yr BETWEEN F1.yr AND F2.yr)
GROUP BY F1.tid, F1.yr) AS X(tid, yr, end_yr)
GROUP BY X.tid, X.end_yr
HAVING (X.end_yr - MIN(X.yr)) > 1;|||A thoughful solution that leaves one to ponder the rationality
of the methodology and of the environment that spawned it :)
"Trey Walpole" <treypole@.newsgroups.nospam> wrote in message
news:e5k25kf9FHA.2844@.TK2MSFTNGP10.phx.gbl...
> select tid, min(yr) as start, maxyr as [end], count(*) as yrs
> from (
> select *,
> isnull(
> (select max(yr)
> from #test t2
> where t2.tid=t1.tid
> and t2.yr>=t1.yr
> and t2.value=0
> and t2.yr<=(select min(yr) from #test where tid=t2.tid and
> yr>=t1.yr and value=1)
> ),
> (select max(yr)
> from #test t3
> where t3.tid=t1.tid
> )
> ) as maxyr
> from #test t1
> where value=0
> ) t4
> group by tid, maxyr
> having count(*)>=4
> order by tid, start
>
> carmaboy@.gmail.com wrote:|||this problem would be solved like that? ;)
CREATE TABLE Puzzles..YearVals(
tid INTEGER NOT NULL,
yr INTEGER NOT NULL,
value INTEGER NOT NULL,
PRIMARY KEY (tid, yr));
INSERT INTO Puzzles..YearVals VALUES (100, 1998, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 1999, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2000, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2001, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2002, 1);
INSERT INTO Puzzles..YearVals VALUES (100, 2003, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2004, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2005, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2006, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2007, 0);
INSERT INTO Puzzles..YearVals VALUES (100, 2008, 1);
INSERT INTO Puzzles..YearVals VALUES (200, 1999, 0);
--INSERT INTO Puzzles..YearVals VALUES (200, 2000, 1);
INSERT INTO Puzzles..YearVals VALUES (200, 2001, 0);
INSERT INTO Puzzles..YearVals VALUES (200, 2002, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2001, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2002, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2003, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2004, 0);
INSERT INTO Puzzles..YearVals VALUES (300, 2005, 0);
SELECT tid, MIN(yr) AS beg_yr, MAX(yr) AS fin_yr, COUNT(*) AS tally
FROM
(SELECT B.tid,B.yr,B.yr-COUNT(*)
FROM Puzzles..YearVals AS B
JOIN Puzzles..YearVals AS L
ON L.tid = B.tid AND L.yr <= B.yr AND L.value=B.value
WHERE B.value = 0
GROUP BY B.tid,B.yr) AS G(tid,yr,grp)
GROUP BY grp,tid
HAVING COUNT(*) >= 4|||rrr... i did not remove a reference on my local db
sorry|||Thanks to everyone for taking the time to help. I should have all I
need to get my results.
Friday, March 23, 2012
Finding "Id" with "Username" selection and then Inserting
I have a simple insert statement that takes values from textboxes and inserts them into a sql server database. I would like to add a value that is selected from a dropdown list and find its corresponding unique "Id" value and insert into the database as well.
My sql statement looks like this:
string strSQL = "INSERT INTO aspnet_Expenses (ExpenseDate,RentalCar,ect..) VALUES (@.ExpenseUserId,@.ExpenseDate,@.RentalCar,ect..)";
I would like to add this to it: SELECT @.ExpenseUserId = UserId FROM aspnet_users WHERE Username = ExpenseUserName
1) How do I assign the value from the dropdown list to save as the "ExpenseUserName"
and
2) Am I on the right path with this at all?
To do this in the least number of trips to the DB, pass all the parameters you have to a stored proc. In your proc:
(1) Use the SELECT from aspnet_users to get the userid into a variable.
(2) Do your regular insert.
|||
How do I pass the values to a stored procedure? Will I need to create a blll and data access layer? I've tried learning those but I was hoping to do something a little more straight forward. I'm pretty new to all of this.
|||OK, you can start from here:Using Stored Procedures with a Command
Try it and if you have any trouble please feel free to post it.
sql
Find values from record that had a max value in a group by
I am looking for the MS SQL variant of the oracle 'KEEP DENSE_RANK' construction.
If you have a table like this:
The query for retrieving the item from the last date should pick the Cherry, but what would be the most efficient way to do this (without using multiple queries, in oracle I don't need a sub query!).
You would like to do something like:
Select Key, max(Date) as LastDate, PickLastValue(Cherry) on Date desc
from Table
group by Key.
any suggestions?
hans
You can do the same funciton in SQL Server 2005..
Code Snippet
Create Table #data (
[key] Varchar(100) ,
[date] Varchar(100) ,
[item] Varchar(100)
);
Insert Into #data Values('1','20070101','Apple');
Insert Into #data Values('1','20070202','Banana');
Insert Into #data Values('1','20070303','Cherry');
Insert Into #data Values('2','20070101','Apple');
Insert Into #data Values('2','20070202','Banana');
Code Snippet
--Over all
;With CTE
as
(
Select [key],[date],[item],DENSE_RANK() Over (Order By [date] Desc) rank From #Data
)
Select [key],[date],[item] from CTE Where rank=1
Code Snippet
--For each Key
;With CTE
as
(
Select [key],[date],[item],DENSE_RANK() Over (Partition By [Key] Order By [date] Desc) rank From #Data
)
Select [key],[date],[item],rank from CTE Where rank=1
|||If you use sql server 2000 then you have to use the subquery...
Code Snippet
--For Overall
Select * from #data Where [date] in (Select max(date) From #data)
--For Each Key
Select * from #data Data
Join (Select [Key], max(date) date From #data Group By [Key]) Sub On
Sub.[Key]=Data.[Key] and Sub.date=Data.date
|||This will do!
thanx
hans
Monday, March 12, 2012
Find records with same values in a field
I have a table of postcodes and I want to find out which towns have the same
postcodes
e.g.
Table has these values
Bournemouth B34
Brighton B24
Chelmsford B34
I want to write the query which returns me Bournemouth and Chelmsford (based
on the postcode being the same and the town being different).
Any ideas please.
ThxHi,
select pc.City, pc.PostalCode
from PostalCode pc
inner join PostalCode pc1
on pc1.PostalCode=pc.PostalCode
where pc1.city<>pc.city
"Billy" wrote:
> I have a table of postcodes and I want to find out which towns have the sa
me
> postcodes
> e.g.
> Table has these values
> Bournemouth B34
> Brighton B24
> Chelmsford B34
> I want to write the query which returns me Bournemouth and Chelmsford (bas
ed
> on the postcode being the same and the town being different).
> Any ideas please.
> Thx|||Thank you
"Billy" wrote:
> I have a table of postcodes and I want to find out which towns have the sa
me
> postcodes
> e.g.
> Table has these values
> Bournemouth B34
> Brighton B24
> Chelmsford B34
> I want to write the query which returns me Bournemouth and Chelmsford (bas
ed
> on the postcode being the same and the town being different).
> Any ideas please.
> Thx|||On Mon, 14 Feb 2005 01:41:11 -0800, Billy wrote:
>I have a table of postcodes and I want to find out which towns have the sam
e
>postcodes
(snip)
Hi Billy,
SELECT a.Postcode, a.Town, b.Town
FROM MyTable AS a
INNER JOIN MyTable AS b
ON b.Postcode = a.Postcode
AND b.Town > a.Town
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
postcodes
e.g.
Table has these values
Bournemouth B34
Brighton B24
Chelmsford B34
I want to write the query which returns me Bournemouth and Chelmsford (based
on the postcode being the same and the town being different).
Any ideas please.
ThxHi,
select pc.City, pc.PostalCode
from PostalCode pc
inner join PostalCode pc1
on pc1.PostalCode=pc.PostalCode
where pc1.city<>pc.city
"Billy" wrote:
> I have a table of postcodes and I want to find out which towns have the sa
me
> postcodes
> e.g.
> Table has these values
> Bournemouth B34
> Brighton B24
> Chelmsford B34
> I want to write the query which returns me Bournemouth and Chelmsford (bas
ed
> on the postcode being the same and the town being different).
> Any ideas please.
> Thx|||Thank you
"Billy" wrote:
> I have a table of postcodes and I want to find out which towns have the sa
me
> postcodes
> e.g.
> Table has these values
> Bournemouth B34
> Brighton B24
> Chelmsford B34
> I want to write the query which returns me Bournemouth and Chelmsford (bas
ed
> on the postcode being the same and the town being different).
> Any ideas please.
> Thx|||On Mon, 14 Feb 2005 01:41:11 -0800, Billy wrote:
>I have a table of postcodes and I want to find out which towns have the sam
e
>postcodes
(snip)
Hi Billy,
SELECT a.Postcode, a.Town, b.Town
FROM MyTable AS a
INNER JOIN MyTable AS b
ON b.Postcode = a.Postcode
AND b.Town > a.Town
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Find Records with duplicate values in 1 row keyed to 2 other rows
My table looks like this:
rowid, tenantid, chargecode, date, amount, ..........
I need to get all rows that have multiple entries on the same date(mm/yyyy)
for a given combination of tenantID and chargecode.
for example:
1 tenant1 5 1/1999 $500
2 tenant1 6 1/1999 $25
3 tenant1 5 1/1999 $35
4 tenant2 5 1/1999 $30
5 tenant2 5 2/1999 $30
6 tenant2 6 2/1999 $40
7 tenant2 6 2/1999 $50
I would need to get that tenant1 has 2 records with a chargecode of 5 for
1/1999
and that tenant2 has 2 records with chargecode 6 for 2/1999
SELECT tenantID, chargecode, date, count(*) as Duplciates
FROM TheTable
GROUP BY tenantID, chargecode, date
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 22 Feb 2007 16:19:00 -0800, Joe S. <joecrew@.news.postalias>
wrote:
>My table looks like this:
>rowid, tenantid, chargecode, date, amount, ..........
>
>I need to get all rows that have multiple entries on the same date(mm/yyyy)
>for a given combination of tenantID and chargecode.
>for example:
>1 tenant1 5 1/1999 $500
>2 tenant1 6 1/1999 $25
>3 tenant1 5 1/1999 $35
>4 tenant2 5 1/1999 $30
>5 tenant2 5 2/1999 $30
>6 tenant2 6 2/1999 $40
>7 tenant2 6 2/1999 $50
>I would need to get that tenant1 has 2 records with a chargecode of 5 for
>1/1999
>and that tenant2 has 2 records with chargecode 6 for 2/1999
rowid, tenantid, chargecode, date, amount, ..........
I need to get all rows that have multiple entries on the same date(mm/yyyy)
for a given combination of tenantID and chargecode.
for example:
1 tenant1 5 1/1999 $500
2 tenant1 6 1/1999 $25
3 tenant1 5 1/1999 $35
4 tenant2 5 1/1999 $30
5 tenant2 5 2/1999 $30
6 tenant2 6 2/1999 $40
7 tenant2 6 2/1999 $50
I would need to get that tenant1 has 2 records with a chargecode of 5 for
1/1999
and that tenant2 has 2 records with chargecode 6 for 2/1999
SELECT tenantID, chargecode, date, count(*) as Duplciates
FROM TheTable
GROUP BY tenantID, chargecode, date
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 22 Feb 2007 16:19:00 -0800, Joe S. <joecrew@.news.postalias>
wrote:
>My table looks like this:
>rowid, tenantid, chargecode, date, amount, ..........
>
>I need to get all rows that have multiple entries on the same date(mm/yyyy)
>for a given combination of tenantID and chargecode.
>for example:
>1 tenant1 5 1/1999 $500
>2 tenant1 6 1/1999 $25
>3 tenant1 5 1/1999 $35
>4 tenant2 5 1/1999 $30
>5 tenant2 5 2/1999 $30
>6 tenant2 6 2/1999 $40
>7 tenant2 6 2/1999 $50
>I would need to get that tenant1 has 2 records with a chargecode of 5 for
>1/1999
>and that tenant2 has 2 records with chargecode 6 for 2/1999
Find Records with duplicate values in 1 row keyed to 2 other rows
My table looks like this:
rowid, tenantid, chargecode, date, amount, ..........
I need to get all rows that have multiple entries on the same date(mm/yyyy)
for a given combination of tenantID and chargecode.
for example:
1 tenant1 5 1/1999 $500
2 tenant1 6 1/1999 $25
3 tenant1 5 1/1999 $35
4 tenant2 5 1/1999 $30
5 tenant2 5 2/1999 $30
6 tenant2 6 2/1999 $40
7 tenant2 6 2/1999 $50
I would need to get that tenant1 has 2 records with a chargecode of 5 for
1/1999
and that tenant2 has 2 records with chargecode 6 for 2/1999SELECT tenantID, chargecode, date, count(*) as Duplciates
FROM TheTable
GROUP BY tenantID, chargecode, date
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 22 Feb 2007 16:19:00 -0800, Joe S. <joecrew@.news.postalias>
wrote:
>My table looks like this:
>rowid, tenantid, chargecode, date, amount, ..........
>
>I need to get all rows that have multiple entries on the same date(mm/yyyy)
>for a given combination of tenantID and chargecode.
>for example:
>1 tenant1 5 1/1999 $500
>2 tenant1 6 1/1999 $25
>3 tenant1 5 1/1999 $35
>4 tenant2 5 1/1999 $30
>5 tenant2 5 2/1999 $30
>6 tenant2 6 2/1999 $40
>7 tenant2 6 2/1999 $50
>I would need to get that tenant1 has 2 records with a chargecode of 5 for
>1/1999
>and that tenant2 has 2 records with chargecode 6 for 2/1999
rowid, tenantid, chargecode, date, amount, ..........
I need to get all rows that have multiple entries on the same date(mm/yyyy)
for a given combination of tenantID and chargecode.
for example:
1 tenant1 5 1/1999 $500
2 tenant1 6 1/1999 $25
3 tenant1 5 1/1999 $35
4 tenant2 5 1/1999 $30
5 tenant2 5 2/1999 $30
6 tenant2 6 2/1999 $40
7 tenant2 6 2/1999 $50
I would need to get that tenant1 has 2 records with a chargecode of 5 for
1/1999
and that tenant2 has 2 records with chargecode 6 for 2/1999SELECT tenantID, chargecode, date, count(*) as Duplciates
FROM TheTable
GROUP BY tenantID, chargecode, date
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 22 Feb 2007 16:19:00 -0800, Joe S. <joecrew@.news.postalias>
wrote:
>My table looks like this:
>rowid, tenantid, chargecode, date, amount, ..........
>
>I need to get all rows that have multiple entries on the same date(mm/yyyy)
>for a given combination of tenantID and chargecode.
>for example:
>1 tenant1 5 1/1999 $500
>2 tenant1 6 1/1999 $25
>3 tenant1 5 1/1999 $35
>4 tenant2 5 1/1999 $30
>5 tenant2 5 2/1999 $30
>6 tenant2 6 2/1999 $40
>7 tenant2 6 2/1999 $50
>I would need to get that tenant1 has 2 records with a chargecode of 5 for
>1/1999
>and that tenant2 has 2 records with chargecode 6 for 2/1999
Find Records with duplicate values in 1 row keyed to 2 other rows
My table looks like this:
rowid, tenantid, chargecode, date, amount, ..........
I need to get all rows that have multiple entries on the same date(mm/yyyy)
for a given combination of tenantID and chargecode.
for example:
1 tenant1 5 1/1999 $500
2 tenant1 6 1/1999 $25
3 tenant1 5 1/1999 $35
4 tenant2 5 1/1999 $30
5 tenant2 5 2/1999 $30
6 tenant2 6 2/1999 $40
7 tenant2 6 2/1999 $50
I would need to get that tenant1 has 2 records with a chargecode of 5 for
1/1999
and that tenant2 has 2 records with chargecode 6 for 2/1999SELECT tenantID, chargecode, date, count(*) as Duplciates
FROM TheTable
GROUP BY tenantID, chargecode, date
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 22 Feb 2007 16:19:00 -0800, Joe S. <joecrew@.news.postalias>
wrote:
>My table looks like this:
>rowid, tenantid, chargecode, date, amount, ..........
>
>I need to get all rows that have multiple entries on the same date(mm/yyyy)
>for a given combination of tenantID and chargecode.
>for example:
>1 tenant1 5 1/1999 $500
>2 tenant1 6 1/1999 $25
>3 tenant1 5 1/1999 $35
>4 tenant2 5 1/1999 $30
>5 tenant2 5 2/1999 $30
>6 tenant2 6 2/1999 $40
>7 tenant2 6 2/1999 $50
>I would need to get that tenant1 has 2 records with a chargecode of 5 for
>1/1999
>and that tenant2 has 2 records with chargecode 6 for 2/1999
rowid, tenantid, chargecode, date, amount, ..........
I need to get all rows that have multiple entries on the same date(mm/yyyy)
for a given combination of tenantID and chargecode.
for example:
1 tenant1 5 1/1999 $500
2 tenant1 6 1/1999 $25
3 tenant1 5 1/1999 $35
4 tenant2 5 1/1999 $30
5 tenant2 5 2/1999 $30
6 tenant2 6 2/1999 $40
7 tenant2 6 2/1999 $50
I would need to get that tenant1 has 2 records with a chargecode of 5 for
1/1999
and that tenant2 has 2 records with chargecode 6 for 2/1999SELECT tenantID, chargecode, date, count(*) as Duplciates
FROM TheTable
GROUP BY tenantID, chargecode, date
HAVING count(*) > 1
Roy Harvey
Beacon Falls, CT
On Thu, 22 Feb 2007 16:19:00 -0800, Joe S. <joecrew@.news.postalias>
wrote:
>My table looks like this:
>rowid, tenantid, chargecode, date, amount, ..........
>
>I need to get all rows that have multiple entries on the same date(mm/yyyy)
>for a given combination of tenantID and chargecode.
>for example:
>1 tenant1 5 1/1999 $500
>2 tenant1 6 1/1999 $25
>3 tenant1 5 1/1999 $35
>4 tenant2 5 1/1999 $30
>5 tenant2 5 2/1999 $30
>6 tenant2 6 2/1999 $40
>7 tenant2 6 2/1999 $50
>I would need to get that tenant1 has 2 records with a chargecode of 5 for
>1/1999
>and that tenant2 has 2 records with chargecode 6 for 2/1999
Friday, March 9, 2012
find Minimum in range thats not in Table?
Hi Folks,
any help appreciated on this problem:
I've got a Table with a comparable Datatype (inet on postgres).
The values in the table have a minimum and a maximum value.
Now, I've got to find the smallest value betweeen min. and max. that is NOT in the table.
Example:
Min=10, Max=20
Entries: 10, 11, 12, 14, 18
needed value: 13 (larger than Min., smallest value not in Table)
I dont have pure Numbers to deal with, so
I dont want to create an auxiliary table with all the possible values and do a SELECT ... WHERE NOT IN ... statement.
thanks in advance!could you give some examples of what "inet" values are?
kinda curious why you chose the oracle forum to post
http://dbforums.com/f81/ is the postresql forum
and what's wrong with an auxiliary table?
rudy|||Originally posted by r937
could you give some examples of what "inet" values are?
kinda curious why you chose the oracle forum to post
http://dbforums.com/f81/ is the postresql forum
and what's wrong with an auxiliary table?
rudy
Sorry, but my browser says 'SQL and PL/SQL' Forum; I'd say this is a SQL problem - not a postgres.
'inet' is an ip(v4)-address - this datatype is comparable but not incrementable.
I don't think it's necessary to create a table with some hundred continuous values that can be expressed by two borders; perhaps there is no other solution than an auxiliary table but that would be a pity.
thanks anyway, Z|||yes, you're right, there's a separate oracle forum, although this one should definitely have the "PL/SQL" taken off its name
if you want an sql solution and not a postgresql solution, the only ones i'm familiar with are:
-- NOT EXISTS
-- NOT IN
-- EXCEPT
-- OUTER JOIN with test for no match
each of these requires some way of specifying the set of things that aren't there
you don't actually have to have an auxiliary table, though
try joining the table to itself with a left outer join on a.inet = (b.inet - 1)
or something :rolleyes:
rudy|||Originally posted by r937
...
try joining the table to itself with a left outer join on a.inet = (b.inet - 1)
or something :rolleyes:
rudy
darn, you were right from the beginning: As the 'inet'-Type cant be incremented or decremented a pure SQL solution seems even more unapplicable now. I think I have to do some postgres-specific hack... :(
thanks,
zaphod|||yeah, but i didn't know i was right at the time!
meanwhile, i had a look at the postgresql docs, and found this (http://developer.postgresql.org/docs/pgsql/src/test/regress/expected/inet.out) page, which (a) freaks me out, but (b) offers encouragement that there might be custom functions available in postgresql for working with that datatype
rudy
any help appreciated on this problem:
I've got a Table with a comparable Datatype (inet on postgres).
The values in the table have a minimum and a maximum value.
Now, I've got to find the smallest value betweeen min. and max. that is NOT in the table.
Example:
Min=10, Max=20
Entries: 10, 11, 12, 14, 18
needed value: 13 (larger than Min., smallest value not in Table)
I dont have pure Numbers to deal with, so
I dont want to create an auxiliary table with all the possible values and do a SELECT ... WHERE NOT IN ... statement.
thanks in advance!could you give some examples of what "inet" values are?
kinda curious why you chose the oracle forum to post
http://dbforums.com/f81/ is the postresql forum
and what's wrong with an auxiliary table?
rudy|||Originally posted by r937
could you give some examples of what "inet" values are?
kinda curious why you chose the oracle forum to post
http://dbforums.com/f81/ is the postresql forum
and what's wrong with an auxiliary table?
rudy
Sorry, but my browser says 'SQL and PL/SQL' Forum; I'd say this is a SQL problem - not a postgres.
'inet' is an ip(v4)-address - this datatype is comparable but not incrementable.
I don't think it's necessary to create a table with some hundred continuous values that can be expressed by two borders; perhaps there is no other solution than an auxiliary table but that would be a pity.
thanks anyway, Z|||yes, you're right, there's a separate oracle forum, although this one should definitely have the "PL/SQL" taken off its name
if you want an sql solution and not a postgresql solution, the only ones i'm familiar with are:
-- NOT EXISTS
-- NOT IN
-- EXCEPT
-- OUTER JOIN with test for no match
each of these requires some way of specifying the set of things that aren't there
you don't actually have to have an auxiliary table, though
try joining the table to itself with a left outer join on a.inet = (b.inet - 1)
or something :rolleyes:
rudy|||Originally posted by r937
...
try joining the table to itself with a left outer join on a.inet = (b.inet - 1)
or something :rolleyes:
rudy
darn, you were right from the beginning: As the 'inet'-Type cant be incremented or decremented a pure SQL solution seems even more unapplicable now. I think I have to do some postgres-specific hack... :(
thanks,
zaphod|||yeah, but i didn't know i was right at the time!
meanwhile, i had a look at the postgresql docs, and found this (http://developer.postgresql.org/docs/pgsql/src/test/regress/expected/inet.out) page, which (a) freaks me out, but (b) offers encouragement that there might be custom functions available in postgresql for working with that datatype
rudy
Wednesday, March 7, 2012
Find last char index
Hi
i have column with this values :
aa-bb-cc-dd
ss-aa
dd-aa-ee
How can i find last '-' char index
i need retrieve this result :
dd
aa
ee
Thank youSELECT RIGHT(col,CHARINDEX('-',REVERSE(col))-1)
FROM YourTable
David Portas
SQL Server MVP
--|||The las '-' is the first of the reverse. Try:
select
reverse(left(reverse(colA), charindex('-', reverse(colA)) - 1))
from
(
select 'aa-bb-cc-dd'
union all
select 'ss-aa'
union all
select 'dd-aa-ee'
) as t(colA)
go
-- if max number of dashes allowed is 4, then
select
parsename(replace(colA, '-', '.'), 1)
from
(
select 'aa-bb-cc-dd'
union all
select 'ss-aa'
union all
select 'dd-aa-ee'
) as t(colA)
go
AMB
"Itzik" wrote:
> Hi
> i have column with this values :
> aa-bb-cc-dd
> ss-aa
> dd-aa-ee
> How can i find last '-' char index
> i need retrieve this result :
> dd
> aa
> ee
> Thank you
>
>
i have column with this values :
aa-bb-cc-dd
ss-aa
dd-aa-ee
How can i find last '-' char index
i need retrieve this result :
dd
aa
ee
Thank youSELECT RIGHT(col,CHARINDEX('-',REVERSE(col))-1)
FROM YourTable
David Portas
SQL Server MVP
--|||The las '-' is the first of the reverse. Try:
select
reverse(left(reverse(colA), charindex('-', reverse(colA)) - 1))
from
(
select 'aa-bb-cc-dd'
union all
select 'ss-aa'
union all
select 'dd-aa-ee'
) as t(colA)
go
-- if max number of dashes allowed is 4, then
select
parsename(replace(colA, '-', '.'), 1)
from
(
select 'aa-bb-cc-dd'
union all
select 'ss-aa'
union all
select 'dd-aa-ee'
) as t(colA)
go
AMB
"Itzik" wrote:
> Hi
> i have column with this values :
> aa-bb-cc-dd
> ss-aa
> dd-aa-ee
> How can i find last '-' char index
> i need retrieve this result :
> dd
> aa
> ee
> Thank you
>
>
Find gaps in sequential numbering
I would like to find the missing numbers in a sequential increase in a
column
So for instance
Create table #test
(numcol int)
insert #test values(1)
insert #test values(2)
insert #test values(3)
insert #test values(4)
insert #test values(7)
insert #test values(8)
insert #test values(9)
insert #test values(11)
insert #test values(100)
Would be nice to get the range of missing values such as
Minvalue Maxvalue
5 6
10 10
12 99
ThanksHere is one solution:
SELECT minval + 1 AS MinValue,
maxval - 1 AS MaxValue
FROM
(SELECT numcol,
(SELECT MIN(numcol)
FROM #test AS T2
WHERE T2.numcol > T1.numcol)
FROM #test AS T1) AS T3(minval, maxval)
WHERE maxval - minval > 1
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Hassan
select
min(i) as low,
max(i) as high
from (
select
N1.num,
count(N2.num) - N1.num
from Numbers as N1, Numbers as N2
where N2.num <= N1.num
group by N1.num
) as N(i,gp)
group by gp
"Hassan" <hassan@.hotmail.com> wrote in message
news:u5PlkdgXIHA.6140@.TK2MSFTNGP02.phx.gbl...
>I would like to find the missing numbers in a sequential increase in a
>column
> So for instance
> Create table #test
> (numcol int)
> insert #test values(1)
> insert #test values(2)
> insert #test values(3)
> insert #test values(4)
> insert #test values(7)
> insert #test values(8)
> insert #test values(9)
> insert #test values(11)
> insert #test values(100)
>
> Would be nice to get the range of missing values such as
> Minvalue Maxvalue
> 5 6
> 10 10
> 12 99
> Thanks
>
column
So for instance
Create table #test
(numcol int)
insert #test values(1)
insert #test values(2)
insert #test values(3)
insert #test values(4)
insert #test values(7)
insert #test values(8)
insert #test values(9)
insert #test values(11)
insert #test values(100)
Would be nice to get the range of missing values such as
Minvalue Maxvalue
5 6
10 10
12 99
ThanksHere is one solution:
SELECT minval + 1 AS MinValue,
maxval - 1 AS MaxValue
FROM
(SELECT numcol,
(SELECT MIN(numcol)
FROM #test AS T2
WHERE T2.numcol > T1.numcol)
FROM #test AS T1) AS T3(minval, maxval)
WHERE maxval - minval > 1
HTH,
Plamen Ratchev
http://www.SQLStudio.com|||Hassan
select
min(i) as low,
max(i) as high
from (
select
N1.num,
count(N2.num) - N1.num
from Numbers as N1, Numbers as N2
where N2.num <= N1.num
group by N1.num
) as N(i,gp)
group by gp
"Hassan" <hassan@.hotmail.com> wrote in message
news:u5PlkdgXIHA.6140@.TK2MSFTNGP02.phx.gbl...
>I would like to find the missing numbers in a sequential increase in a
>column
> So for instance
> Create table #test
> (numcol int)
> insert #test values(1)
> insert #test values(2)
> insert #test values(3)
> insert #test values(4)
> insert #test values(7)
> insert #test values(8)
> insert #test values(9)
> insert #test values(11)
> insert #test values(100)
>
> Would be nice to get the range of missing values such as
> Minvalue Maxvalue
> 5 6
> 10 10
> 12 99
> Thanks
>
Find empty values in a column
Hello,
I am trying to replicate somthing I did in my access reports.
I need to count how many 0 dollar values or null values show up in a
combination of two colums.
I dont know where to begin in Reporting Services. In Acces this was VBA.
Here is the VBA CODE.
End If
If Nz(Me.sQuotedPrice, 0) = "0" Or Len(Me!sQuotedPrice) = 0 And
Nz(Me.sGuessPrice, 0) = "0" Or Len(Me!sGuessPrice) = 0 Then
sJobsMissingPrices = sJobsMissingPrices + 1
End If
I try this in Reporting Services and it says it is not with in the correct
scope?
Public Shared Function GetEmptyValues()
Dim JobsMissingPrices
If ReportItem!JobPrice.Value= 0 Or Len(ReportItems!JobPrice.value) = 0 And
NzReportItems!Guess_Price = "0" Or Len(ReportItems!Guess_Price) = 0 Then
JobsMissingPrices = JobsMissingPrices + 1
End If
End Function
Any help would be appreciated.
--
Thank You, LeoHi Leo,
It seems there is something wrong here, an additional "Nz" was not removed
NzReportItems!Guess_Price = "0"
If removing Nz doesn't resolve this issue, please provide more detailed
information about this two values and I would like to make a reproduce on
my side.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Leo,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
If you have any questions or concerns, don't hesitate to let me know. We
are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
I am trying to replicate somthing I did in my access reports.
I need to count how many 0 dollar values or null values show up in a
combination of two colums.
I dont know where to begin in Reporting Services. In Acces this was VBA.
Here is the VBA CODE.
End If
If Nz(Me.sQuotedPrice, 0) = "0" Or Len(Me!sQuotedPrice) = 0 And
Nz(Me.sGuessPrice, 0) = "0" Or Len(Me!sGuessPrice) = 0 Then
sJobsMissingPrices = sJobsMissingPrices + 1
End If
I try this in Reporting Services and it says it is not with in the correct
scope?
Public Shared Function GetEmptyValues()
Dim JobsMissingPrices
If ReportItem!JobPrice.Value= 0 Or Len(ReportItems!JobPrice.value) = 0 And
NzReportItems!Guess_Price = "0" Or Len(ReportItems!Guess_Price) = 0 Then
JobsMissingPrices = JobsMissingPrices + 1
End If
End Function
Any help would be appreciated.
--
Thank You, LeoHi Leo,
It seems there is something wrong here, an additional "Nz" was not removed
NzReportItems!Guess_Price = "0"
If removing Nz doesn't resolve this issue, please provide more detailed
information about this two values and I would like to make a reproduce on
my side.
Thank you for your patience and corperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!|||Hi Leo,
I wanted to post a quick note to see if you would like additional
assistance or information regarding this particular issue. We appreciate
your patience and look forward to hearing from you!
If you have any questions or concerns, don't hesitate to let me know. We
are here to be of assistance!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
Find differences in Two tables
I have a parent and a child table. When I was loading the
child table I ran into foreign key error. I need to find
the different values in the text file I am loading into
the child table (Parent table is already loaded). There
are 4 columns in the foreign key definition. I have loaded
the text data into another table(did not include the
foreign key but the indexes).
I have the following query that is taking forever in a
table with 88000 rows. Is there a quicker query (Both
tables have the necessary indexes)'.
Select * from another an
Join parent pr on
an.col1 <> pr.col1 AND an.col2 <> pr.col2
an.col3 <> pr.col3 AND an.col4 <> pr.col4
Thanks for any helpTry:
Select * from another an
left outer Join parent pr on
an.col1 = pr.col1 AND an.col2 = pr.col2
an.col3 = pr.col3 AND an.col4 = pr.col4
WHERE pr.col1 IS NULL
Unequality (<> ) is not sargeable, i.e. the Query optimizer can not use
indexes when trying to solve an unequality, but it can use indexes when
trying to solve equality operations.
Jacco Schalkwijk
SQL Server MVP
"Kavin" <anonymous@.discussions.microsoft.com> wrote in message
news:93e401c40522$eb548bb0$a101280a@.phx.gbl...
> I have a parent and a child table. When I was loading the
> child table I ran into foreign key error. I need to find
> the different values in the text file I am loading into
> the child table (Parent table is already loaded). There
> are 4 columns in the foreign key definition. I have loaded
> the text data into another table(did not include the
> foreign key but the indexes).
> I have the following query that is taking forever in a
> table with 88000 rows. Is there a quicker query (Both
> tables have the necessary indexes)'.
> Select * from another an
> Join parent pr on
> an.col1 <> pr.col1 AND an.col2 <> pr.col2
> an.col3 <> pr.col3 AND an.col4 <> pr.col4
> Thanks for any help
>|||That did it.......
Thanks.
>--Original Message--
>Try:
>Select * from another an
>left outer Join parent pr on
>an.col1 = pr.col1 AND an.col2 = pr.col2
>an.col3 = pr.col3 AND an.col4 = pr.col4
>WHERE pr.col1 IS NULL
>Unequality (<> ) is not sargeable, i.e. the Query
optimizer can not use
>indexes when trying to solve an unequality, but it can
use indexes when
>trying to solve equality operations.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Kavin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:93e401c40522$eb548bb0$a101280a@.phx.gbl...
the
loaded
>
>.
>
child table I ran into foreign key error. I need to find
the different values in the text file I am loading into
the child table (Parent table is already loaded). There
are 4 columns in the foreign key definition. I have loaded
the text data into another table(did not include the
foreign key but the indexes).
I have the following query that is taking forever in a
table with 88000 rows. Is there a quicker query (Both
tables have the necessary indexes)'.
Select * from another an
Join parent pr on
an.col1 <> pr.col1 AND an.col2 <> pr.col2
an.col3 <> pr.col3 AND an.col4 <> pr.col4
Thanks for any helpTry:
Select * from another an
left outer Join parent pr on
an.col1 = pr.col1 AND an.col2 = pr.col2
an.col3 = pr.col3 AND an.col4 = pr.col4
WHERE pr.col1 IS NULL
Unequality (<> ) is not sargeable, i.e. the Query optimizer can not use
indexes when trying to solve an unequality, but it can use indexes when
trying to solve equality operations.
Jacco Schalkwijk
SQL Server MVP
"Kavin" <anonymous@.discussions.microsoft.com> wrote in message
news:93e401c40522$eb548bb0$a101280a@.phx.gbl...
> I have a parent and a child table. When I was loading the
> child table I ran into foreign key error. I need to find
> the different values in the text file I am loading into
> the child table (Parent table is already loaded). There
> are 4 columns in the foreign key definition. I have loaded
> the text data into another table(did not include the
> foreign key but the indexes).
> I have the following query that is taking forever in a
> table with 88000 rows. Is there a quicker query (Both
> tables have the necessary indexes)'.
> Select * from another an
> Join parent pr on
> an.col1 <> pr.col1 AND an.col2 <> pr.col2
> an.col3 <> pr.col3 AND an.col4 <> pr.col4
> Thanks for any help
>|||That did it.......
Thanks.
>--Original Message--
>Try:
>Select * from another an
>left outer Join parent pr on
>an.col1 = pr.col1 AND an.col2 = pr.col2
>an.col3 = pr.col3 AND an.col4 = pr.col4
>WHERE pr.col1 IS NULL
>Unequality (<> ) is not sargeable, i.e. the Query
optimizer can not use
>indexes when trying to solve an unequality, but it can
use indexes when
>trying to solve equality operations.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Kavin" <anonymous@.discussions.microsoft.com> wrote in
message
>news:93e401c40522$eb548bb0$a101280a@.phx.gbl...
the
loaded
>
>.
>
Sunday, February 26, 2012
find and replace
To ensure I don't leave orphans floating around in tables when records get deleted (values from one record might link to values in another) how do I find and possibly replace values in tables?
For example, if I have a unit of measure table and want to delete the value "inches", how do I look in other tables to find this value and give the user the option to cancel or clear it out. If I don't it will cause controls bound to that value like the dropdownlist to throw an error.
So you want to search all user tables for the value to be deleted? It's hard to control which tables to be search and it will be a huge workload when there are many tables. If you just want to maintenance the Reference Integrity, why not use constraints? You can create PK/FK between the tables, and cascading changes on the referencing columns. For more information about this, you can refer to:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_92ib.asp
Friday, February 24, 2012
Find a date that occurs every 2 days from a fixed date
Hi
I am trying to write a script that only sums the values in a column if the
date for the record occurs every 2 days after another date. The trouble is,
it is a recurring 2 day cycle. i.e Only sum the values if the record date is
every 2nd day from the first date.
eg. First Date=01/03/06. Only sum the values if the record date is 03/03,
05/03, 07/04 etc. If the record does not occur every second day, it should
return 0.
I can use datediff to get the difference and I can cast it into a decimal
and divide by 2 but can't get it to validate in the statement.
Is there an easier way?Mark,
Post your DDL & T-SQL ?
Robert
"Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
news:43502397-8D5F-46F6-B15D-4B3B384CCBF0@.microsoft.com...
> Hi
> I am trying to write a script that only sums the values in a column if the
> date for the record occurs every 2 days after another date. The trouble
> is,
> it is a recurring 2 day cycle. i.e Only sum the values if the record date
> is
> every 2nd day from the first date.
> eg. First Date=01/03/06. Only sum the values if the record date is 03/03,
> 05/03, 07/04 etc. If the record does not occur every second day, it should
> return 0.
> I can use datediff to get the difference and I can cast it into a decimal
> and divide by 2 but can't get it to validate in the statement.
> Is there an easier way?
>|||Here is the short version of a complex query:
select guest.arrival, multirate.room_type, multirate.rate_date,
count (multirate.room_type)
from guest, multirate
where guest.property=multirate.property and guest.account=multirate.account
and
multirate.rate_date='xxxxx'
The problem is this:
Assume the guest.arrival='2006-03-01'
If the multirate.rate_date in the where statment is '2006-03-02', it must
ignore the record from the count because the datediff between 02/03 and 01/0
3
is not a multiple of 2.
If the multitrate.rate_date='2006-03-03', it must count the record into the
result because it occurs 2 days after the arrival.
The multirate.rate_date will be a variable passed to the statement, so the
query must be able to validate that the rate_date has a multiple of 2
difference between the rate_date and arrival.
Does this make sense?
"Robert Ellis" wrote:
> Mark,
> Post your DDL & T-SQL ?
> Robert
> "Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
> news:43502397-8D5F-46F6-B15D-4B3B384CCBF0@.microsoft.com...
>
>|||Mark:
Does the following example help? There may be other ways to do it.
IF OBJECT_ID('blah') IS NOT NULL DROP TABLE blah
CREATE TABLE blah
(
BlahGroup CHAR(2) NOT NULL,
FirstDate DATETIME NOT NULL,
SecondDate DATETIME NOT NULL
)
GO
SET DATEFORMAT YMD
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-02'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-03'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-04'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-05'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-06'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-07'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-03'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-04'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-05'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-06'
GO
SELECT b.BlahGroup, CountOfBlahGroup = COUNT(b.BlahGroup)
FROM blah b
WHERE ((CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2) =
(FLOOR(CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)))
GROUP BY b.BlahGroup
GO
"Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
news:5C913A70-67B7-49C4-B377-2C43115CA907@.microsoft.com...
> Here is the short version of a complex query:
> select guest.arrival, multirate.room_type, multirate.rate_date,
> count (multirate.room_type)
> from guest, multirate
> where guest.property=multirate.property and
> guest.account=multirate.account
> and
> multirate.rate_date='xxxxx'
> The problem is this:
> Assume the guest.arrival='2006-03-01'
> If the multirate.rate_date in the where statment is '2006-03-02', it must
> ignore the record from the count because the datediff between 02/03 and
> 01/03
> is not a multiple of 2.
> If the multitrate.rate_date='2006-03-03', it must count the record into
> the
> result because it occurs 2 days after the arrival.
> The multirate.rate_date will be a variable passed to the statement, so the
> query must be able to validate that the rate_date has a multiple of 2
> difference between the rate_date and arrival.
> Does this make sense?
> "Robert Ellis" wrote:
>|||Additionally:
obviously you would add something like:
AND (FirstDate = @.ParamDate)
to the 'WHERE' clause for your implementation...
cheers,
Robert
"Robert Ellis" <robe_2k5.n0sp8m@.hotmail.co.uk> wrote in message
news:%23LZjNahQGHA.4344@.TK2MSFTNGP12.phx.gbl...
> Mark:
> Does the following example help? There may be other ways to do it.
> IF OBJECT_ID('blah') IS NOT NULL DROP TABLE blah
> CREATE TABLE blah
> (
> BlahGroup CHAR(2) NOT NULL,
> FirstDate DATETIME NOT NULL,
> SecondDate DATETIME NOT NULL
> )
> GO
> SET DATEFORMAT YMD
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-02'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-06'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-07'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-06'
> GO
> SELECT b.BlahGroup, CountOfBlahGroup = COUNT(b.BlahGroup)
> FROM blah b
> WHERE ((CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)
> =
> (FLOOR(CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)))
> GROUP BY b.BlahGroup
> GO
>
> "Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
> news:5C913A70-67B7-49C4-B377-2C43115CA907@.microsoft.com...
>|||% modulus
argh
"Robert Ellis" <robe_2k5.n0sp8m@.hotmail.co.uk> wrote in message
news:%23LZjNahQGHA.4344@.TK2MSFTNGP12.phx.gbl...
> Mark:
> Does the following example help? There may be other ways to do it.
> IF OBJECT_ID('blah') IS NOT NULL DROP TABLE blah
> CREATE TABLE blah
> (
> BlahGroup CHAR(2) NOT NULL,
> FirstDate DATETIME NOT NULL,
> SecondDate DATETIME NOT NULL
> )
> GO
> SET DATEFORMAT YMD
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-02'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-06'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-07'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-06'
> GO
> SELECT b.BlahGroup, CountOfBlahGroup = COUNT(b.BlahGroup)
> FROM blah b
> WHERE ((CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)
> =
> (FLOOR(CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)))
> GROUP BY b.BlahGroup
> GO
>
> "Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
> news:5C913A70-67B7-49C4-B377-2C43115CA907@.microsoft.com...
>
I am trying to write a script that only sums the values in a column if the
date for the record occurs every 2 days after another date. The trouble is,
it is a recurring 2 day cycle. i.e Only sum the values if the record date is
every 2nd day from the first date.
eg. First Date=01/03/06. Only sum the values if the record date is 03/03,
05/03, 07/04 etc. If the record does not occur every second day, it should
return 0.
I can use datediff to get the difference and I can cast it into a decimal
and divide by 2 but can't get it to validate in the statement.
Is there an easier way?Mark,
Post your DDL & T-SQL ?
Robert
"Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
news:43502397-8D5F-46F6-B15D-4B3B384CCBF0@.microsoft.com...
> Hi
> I am trying to write a script that only sums the values in a column if the
> date for the record occurs every 2 days after another date. The trouble
> is,
> it is a recurring 2 day cycle. i.e Only sum the values if the record date
> is
> every 2nd day from the first date.
> eg. First Date=01/03/06. Only sum the values if the record date is 03/03,
> 05/03, 07/04 etc. If the record does not occur every second day, it should
> return 0.
> I can use datediff to get the difference and I can cast it into a decimal
> and divide by 2 but can't get it to validate in the statement.
> Is there an easier way?
>|||Here is the short version of a complex query:
select guest.arrival, multirate.room_type, multirate.rate_date,
count (multirate.room_type)
from guest, multirate
where guest.property=multirate.property and guest.account=multirate.account
and
multirate.rate_date='xxxxx'
The problem is this:
Assume the guest.arrival='2006-03-01'
If the multirate.rate_date in the where statment is '2006-03-02', it must
ignore the record from the count because the datediff between 02/03 and 01/0
3
is not a multiple of 2.
If the multitrate.rate_date='2006-03-03', it must count the record into the
result because it occurs 2 days after the arrival.
The multirate.rate_date will be a variable passed to the statement, so the
query must be able to validate that the rate_date has a multiple of 2
difference between the rate_date and arrival.
Does this make sense?
"Robert Ellis" wrote:
> Mark,
> Post your DDL & T-SQL ?
> Robert
> "Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
> news:43502397-8D5F-46F6-B15D-4B3B384CCBF0@.microsoft.com...
>
>|||Mark:
Does the following example help? There may be other ways to do it.
IF OBJECT_ID('blah') IS NOT NULL DROP TABLE blah
CREATE TABLE blah
(
BlahGroup CHAR(2) NOT NULL,
FirstDate DATETIME NOT NULL,
SecondDate DATETIME NOT NULL
)
GO
SET DATEFORMAT YMD
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-02'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-03'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-04'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-05'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
'2006-01-06'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-07'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-03'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-04'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-05'
INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
'2006-01-06'
GO
SELECT b.BlahGroup, CountOfBlahGroup = COUNT(b.BlahGroup)
FROM blah b
WHERE ((CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2) =
(FLOOR(CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)))
GROUP BY b.BlahGroup
GO
"Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
news:5C913A70-67B7-49C4-B377-2C43115CA907@.microsoft.com...
> Here is the short version of a complex query:
> select guest.arrival, multirate.room_type, multirate.rate_date,
> count (multirate.room_type)
> from guest, multirate
> where guest.property=multirate.property and
> guest.account=multirate.account
> and
> multirate.rate_date='xxxxx'
> The problem is this:
> Assume the guest.arrival='2006-03-01'
> If the multirate.rate_date in the where statment is '2006-03-02', it must
> ignore the record from the count because the datediff between 02/03 and
> 01/03
> is not a multiple of 2.
> If the multitrate.rate_date='2006-03-03', it must count the record into
> the
> result because it occurs 2 days after the arrival.
> The multirate.rate_date will be a variable passed to the statement, so the
> query must be able to validate that the rate_date has a multiple of 2
> difference between the rate_date and arrival.
> Does this make sense?
> "Robert Ellis" wrote:
>|||Additionally:
obviously you would add something like:
AND (FirstDate = @.ParamDate)
to the 'WHERE' clause for your implementation...
cheers,
Robert
"Robert Ellis" <robe_2k5.n0sp8m@.hotmail.co.uk> wrote in message
news:%23LZjNahQGHA.4344@.TK2MSFTNGP12.phx.gbl...
> Mark:
> Does the following example help? There may be other ways to do it.
> IF OBJECT_ID('blah') IS NOT NULL DROP TABLE blah
> CREATE TABLE blah
> (
> BlahGroup CHAR(2) NOT NULL,
> FirstDate DATETIME NOT NULL,
> SecondDate DATETIME NOT NULL
> )
> GO
> SET DATEFORMAT YMD
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-02'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-06'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-07'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-06'
> GO
> SELECT b.BlahGroup, CountOfBlahGroup = COUNT(b.BlahGroup)
> FROM blah b
> WHERE ((CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)
> =
> (FLOOR(CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)))
> GROUP BY b.BlahGroup
> GO
>
> "Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
> news:5C913A70-67B7-49C4-B377-2C43115CA907@.microsoft.com...
>|||% modulus
argh
"Robert Ellis" <robe_2k5.n0sp8m@.hotmail.co.uk> wrote in message
news:%23LZjNahQGHA.4344@.TK2MSFTNGP12.phx.gbl...
> Mark:
> Does the following example help? There may be other ways to do it.
> IF OBJECT_ID('blah') IS NOT NULL DROP TABLE blah
> CREATE TABLE blah
> (
> BlahGroup CHAR(2) NOT NULL,
> FirstDate DATETIME NOT NULL,
> SecondDate DATETIME NOT NULL
> )
> GO
> SET DATEFORMAT YMD
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-02'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'aa', '2006-01-01',
> '2006-01-06'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-07'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-03'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-04'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-05'
> INSERT blah (BlahGroup, FirstDate, SecondDate) SELECT 'bb', '2006-01-02',
> '2006-01-06'
> GO
> SELECT b.BlahGroup, CountOfBlahGroup = COUNT(b.BlahGroup)
> FROM blah b
> WHERE ((CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)
> =
> (FLOOR(CAST(DATEDIFF(dd, b.FirstDate, b.SecondDate) AS DECIMAL(10,1))/2)))
> GROUP BY b.BlahGroup
> GO
>
> "Mark - HIS" <MarkHIS@.discussions.microsoft.com> wrote in message
> news:5C913A70-67B7-49C4-B377-2C43115CA907@.microsoft.com...
>
Sunday, February 19, 2012
Final Attemp -Sql Stored Procedure Tough Question
I have a procedure I need to get the values out of..I am using outputs...I have no idea why it wont work.....I need all values listed in the select part of procedure...
CREATE procedure dbo.Appt_Login_NET
(
@.LoginName nvarchar(15),
@.Password NvarChar(15),
@.UserName nvarchar(15)Output,
@.UserPassword nvarchar(15)Output,
@.UserClinic nvarchar(3)Output,
@.UserTester bit Output
)
as
select
UserName,
UserPassword,
UserClinic,
UserTester
from
Clinic_users
where
UserName = @.LoginName
and
UserPassword = @.Password
GO
my vb.net code to retrive this info is
Private Sub Button1_ServerClick(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.ServerClick
Dim con As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmd As New SqlCommand
Dim parmuser As SqlParameter
Dim parmus As SqlParameter
Dim parmpass As SqlParameter
Dim parmtest As SqlParameter
Dim struser As String
Dim strpass As String
Dim strclinic As String
Dim strnames As String
Dim tester As String
strpass = txtPass.Value
struser = txtUser.Value
cmd = New SqlCommand("Appt_Login_NET", con)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@.LoginName", struser)
cmd.Parameters.Add("@.Password", strpass)
parmus = cmd.Parameters.Add("@.UserName", SqlDbType.NVarChar)
parmus.Size = 15
parmus.Direction = ParameterDirection.Output
parmuser = cmd.Parameters.Add("@.UserClinic", SqlDbType.NVarChar)
parmuser.Size = 3
parmuser.Direction = ParameterDirection.Output
parmpass = cmd.Parameters.Add("@.UserPassword", SqlDbType.NVarChar)
parmpass.Size = 15
parmpass.Direction = ParameterDirection.Output
parmtest = cmd.Parameters.Add("@.UserTester", SqlDbType.Bit)
parmtest.Size = 1
parmtest.Direction = ParameterDirection.Output
con.Open()
cmd.ExecuteNonQuery()
If Not IsDBNull(cmd.Parameters("@.UserName").Value) Then
Label1.Text = cmd.Parameters("@.UserName").Value()
Else
Label1.Text = "No Results Found"
End If
con.Close()
End Sub
Why does this always show as "DBNUll" I get nothing when I debug any of my parm variables.I searched the SQl Server and in Query analyzer instead of the output variables in the procedure being just outputs they are input/outputs...............What does it take to get this working? Do I need a conversion datatype I would prefer I gain the values and store them in variables.....CREATE procedure dbo.Appt_Login_NET
(
@.LoginName nvarchar(15),
@.Password NvarChar(15)
)
as
select
UserName,
UserPassword,
UserClinic,
UserTester
from
Clinic_users
where
UserName = @.LoginName
and
UserPassword = @.Password
------------
You are trying to get all the information based on user/pass information passed into the SP. Then you won't need to have the other parameters in your SP since they are fields in your table.. Data contained in the table will get returned based on the user/pass credentials that were provided.
I am sure you were getting an error on the other parameters since you didn't provide data to those...
Then using a DataReader just use the field name to get the values out of them
Dim reader as SqlDataReader = command.ExecuteReader(...)
IF reader.Read THEN
string UserName = CType(reader["UserName"], string)
END IF|||So I should leave that procedure the way I had it like you have it and then how exactlly do I extract for example the User Clinic if I used this data reader thing.How might I set that up.....Thanks|||UserClinic is the clinic at which this user works then you can do the same thing I did in that IF statement...
dim user as string
dim uClinic as string
dim uTester as boolean
IF reader.Read THEN
user = CType(reader("username"), string)
uClinic = CType(reader("UserClinic"), string)
uTester = CType(reader("UserTester"), boolean)
END IF
sorry in previous post I used [] in the reader ordinal call...[] is a C# thing|||No, no no. You're are correct to use OUTPUT params and not to use a result set. You are trying to get a few columns of data for a single row. OUTPUT params is exactly the correct thing to use. Your problem is that you've not written your proc correctly. Open up Query Analyser, write the proc and test the thing. Once its working then you can then worry about the .net code.|||I have tried this code but I get an error on "Inncorrect Syntax" I really cant figure this out..help please...
here is the source error
Source Error:
Line 86: cmd.Parameters.Add("@.LoginName", txtUser.Value)
Line 87: cmd.Parameters.Add("@.Password", txtPass.Value)
Line 88: reader = cmd.ExecuteReader <--is highlighted in red
Line 89: If reader.Read Then
Line 90: user = CType(reader("UserName"), String)
my VB.net Code.......
Private Sub Button2_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim con As New SqlConnection("Server=myserver;database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim user As String
Dim uclinic As String
Dim uTester As Boolean
Dim reader As SqlDataReader
Dim cmd As SqlCommand
con.Open()
cmd = New SqlCommand("Appt_Login", con)
cmd.Parameters.Add("@.LoginName", txtUser.Value)
cmd.Parameters.Add("@.Password", txtPass.Value)
reader = cmd.ExecuteReader
If reader.Read Then
user = CType(reader("UserName"), String)
uclinic = CType(reader("UserClinic"), String)
End If
End Sub|||view post 510022|||Thanks guys for all the help....I got it working
Filters with large number of values
I have web-based reports using SQL server 2005 with filters called location
and suppliers and the number of locations and suppliers are more than 200
each. When the report is rendered with this filters, selection of locations
and suppliers is taking long time on the browsers and it is not easy to
search for the required options in the drop down with checkboxes,
1. Can we modify the parameters display area to show a file-open image
clicking on which opens a popup window for users to select the filter
values?
2. Can we improve the performance while user selects the filter values?
3. Is there any other way to handle this type of large filters (more than
200 filter options) for the reports?
Thanks and Regards,
SridharOn Jun 6, 12:13 am, "Sridhar" <dsrid...@.translogicsys.com> wrote:
> I have web-based reports using SQL server 2005 with filters called location
> and suppliers and the number of locations and suppliers are more than 200
> each. When the report is rendered with this filters, selection of locations
> and suppliers is taking long time on the browsers and it is not easy to
> search for the required options in the drop down with checkboxes,
> 1. Can we modify the parameters display area to show a file-open image
> clicking on which opens a popup window for users to select the filter
> values?
> 2. Can we improve the performance while user selects the filter values?
> 3. Is there any other way to handle this type of large filters (more than
> 200 filter options) for the reports?
> Thanks and Regards,
> Sridhar
In response to number 1, I'm afraid not. In response to number 2, if
I'm understanding you correctly, the more filters you have the more
the performance will be affected. To improve performance, I would
suggest running the report query/stored procedure through the Database
Engine Tuning Advisor to see if there are any indexes that can be
implemented/removed that would improve the performance of the query.
Then, make sure that you are doing as much of the processing in the
query/stored procedure as possible and not in the report. After that,
I would say to limit the number of filters you have on the report.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
and suppliers and the number of locations and suppliers are more than 200
each. When the report is rendered with this filters, selection of locations
and suppliers is taking long time on the browsers and it is not easy to
search for the required options in the drop down with checkboxes,
1. Can we modify the parameters display area to show a file-open image
clicking on which opens a popup window for users to select the filter
values?
2. Can we improve the performance while user selects the filter values?
3. Is there any other way to handle this type of large filters (more than
200 filter options) for the reports?
Thanks and Regards,
SridharOn Jun 6, 12:13 am, "Sridhar" <dsrid...@.translogicsys.com> wrote:
> I have web-based reports using SQL server 2005 with filters called location
> and suppliers and the number of locations and suppliers are more than 200
> each. When the report is rendered with this filters, selection of locations
> and suppliers is taking long time on the browsers and it is not easy to
> search for the required options in the drop down with checkboxes,
> 1. Can we modify the parameters display area to show a file-open image
> clicking on which opens a popup window for users to select the filter
> values?
> 2. Can we improve the performance while user selects the filter values?
> 3. Is there any other way to handle this type of large filters (more than
> 200 filter options) for the reports?
> Thanks and Regards,
> Sridhar
In response to number 1, I'm afraid not. In response to number 2, if
I'm understanding you correctly, the more filters you have the more
the performance will be affected. To improve performance, I would
suggest running the report query/stored procedure through the Database
Engine Tuning Advisor to see if there are any indexes that can be
implemented/removed that would improve the performance of the query.
Then, make sure that you are doing as much of the processing in the
query/stored procedure as possible and not in the report. After that,
I would say to limit the number of filters you have on the report.
Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant
Filters
A common question is on reports with parameters - is there some way to also
display what the parameter values were for this given report?I did find some ways to display the paramters - but man are they ugly:
Expression:
="Type: "&Join(Parameters!AssessmentTypeStatusATSStatus.Value)
The value selected is "Current" - but what is display on report with above
expression is:
Type: [Assessment Type Status].[ATS Status].&[Current]
"Joe" <hortoristic@.gmail dot com> wrote in message
news:eRTuvaQrGHA.3564@.TK2MSFTNGP03.phx.gbl...
>A common question is on reports with parameters - is there some way to also
>display what the parameter values were for this given report?
>
>
display what the parameter values were for this given report?I did find some ways to display the paramters - but man are they ugly:
Expression:
="Type: "&Join(Parameters!AssessmentTypeStatusATSStatus.Value)
The value selected is "Current" - but what is display on report with above
expression is:
Type: [Assessment Type Status].[ATS Status].&[Current]
"Joe" <hortoristic@.gmail dot com> wrote in message
news:eRTuvaQrGHA.3564@.TK2MSFTNGP03.phx.gbl...
>A common question is on reports with parameters - is there some way to also
>display what the parameter values were for this given report?
>
>
Subscribe to:
Posts (Atom)