Thursday, March 29, 2012
Concatenate Multiple Rows?
Spec T_R Section
A008 23w 1
A008 23w 2
A008 23w 4
I need a query that returns a single record/row like this:
Spec T_R Section
A008 23w 1, 2, 4
Any help would be appreciated.I've had this problem more times than I can count. While I was writing my SQL Tutorial (http://www.bitesizeinc.net/index.php/sql.html), I ran across this function for MySQL :
group_concat(field)
Which concatenates the grouped results into a string. If you are using Oracle, you'll need a stored procedure...
-Chrissqlsql
Concatenate Multiple Records Into One Field
TABLE
ColumnA ColumnB
1......A
2......B
3......C
4......A
5......A
6......B
7......C
8......D
9......C
10.....E
EXPECTED OUTPUT
ColumnA ColumnB
1......4,5
2......6
3......7
4......1,5
5......1, 4
6......2
7......3
8......
9......3,7
10.....create function ConcatFld (@.RowId int, @.RowVal char(1))
returns varchar(100) AS
begin
declare @.Ret varchar(100)
set @.Ret=''
select @.Ret= @.Ret + cast(ColA as varchar)+',' from Tbl1 where ColB=@.RowVal and ColA<>@.RowId
if len(@.Ret) > 0
set @.Ret = left(@.Ret,len(@.Ret)-1)
return @.Ret
end
--------
select ColA, dbo.ConcatFld(ColA,ColB) from Tbl1|||Thanks Upalsen, this is exactly what I need!!!
Tuesday, March 27, 2012
Concatenate
records do not have a middle initial (middle initial is NULL) therefore
causing the concatenation to yield a Null result. Is there an easy trick /
technique to fix this? thanksYou can use COALESCE or ISNULL to replace the NULLs to spaces like:
SELECT first_name + COALESCE( middle_initial, '' ) +
COALESCE( last_name, '' ) AS "full name"
FROM ...
--
Anith|||Thank You!
"Anith Sen" wrote:
> You can use COALESCE or ISNULL to replace the NULLs to spaces like:
> SELECT first_name + COALESCE( middle_initial, '' ) +
> COALESCE( last_name, '' ) AS "full name"
> FROM ...
> --
> Anith
>
>
Concat instead of SUM when grouping results
I have a very simple problem which I will illustrate with an example:
I have the following records in my table:
A 1 C
A 2 C
A 3 C
B 8 K
B 9 K
I now want to group them and the result has to be:
A 1,2,3 C
B 8,9 K
So the results in the second row have to be concatenated. I guess
there is no function to do this... What is the simplest solution?
Kind regards,
Bart WarnezHi Bart,
I've seen this question answered very neatly before, so with a bit of
digging and some copy/paste I came up with:
CREATE TABLE test (test1 VARCHAR(5), test2 varchar(5), test3
varchar(5))
INSERT INTO test(test1, test2, test3)
SELECT 'A', '1', 'C'
UNION ALL
SELECT 'A', '2', 'C'
UNION ALL
SELECT 'A', '3', 'C'
UNION ALL
SELECT 'B', '8', 'C'
UNION ALL
SELECT 'B', '9', 'C'
SELECT test1, SUBSTRING((select ', ' + test2 as [text()]
from test t
where t.test1 = ot.test1
for xml path(''), elements), 3, 100) as test2, test3
FROM test ot
GROUP BY test1, test3
DROP TABLE test
which seems to work :)
Good luck!
J|||On 23 nov, 12:52, jhofm...@.googlemail.com wrote:
Quote:
Originally Posted by
Hi Bart,
>
I've seen this question answered very neatly before, so with a bit of
digging and some copy/paste I came up with:
>
CREATE TABLE test (test1 VARCHAR(5), test2 varchar(5), test3
varchar(5))
>
INSERT INTO test(test1, test2, test3)
SELECT 'A', '1', 'C'
UNION ALL
SELECT 'A', '2', 'C'
UNION ALL
SELECT 'A', '3', 'C'
UNION ALL
SELECT 'B', '8', 'C'
UNION ALL
SELECT 'B', '9', 'C'
>
SELECT test1, SUBSTRING((select ', ' + test2 as [text()]
from test t
where t.test1 = ot.test1
for xml path(''), elements), 3, 100) as test2, test3
FROM test ot
GROUP BY test1, test3
>
DROP TABLE test
>
which seems to work :)
>
Good luck!
J
Hey, thank you very much, it works :). The only problem is that it
lasts more than 10 s to execute it and that with only 5 records :(.
Kind Regards,
Bart|||I have also tried out the solution below (with the same test-table),
with a function. But again the response time is very slow...
create function dbo.fn_groupIt(@.test1 varchar(5),@.test3 varchar(5))
returns varchar(5000)
as
begin
declare @.out varchar(5000)
select@.out = coalesce(@.out + ',' + convert(varchar,test2),
convert(varchar,test2))
fromtest
wheretest1 = @.test1 and
test3 = @.test3
return @.out
end
selecttest1, dbo.fn_groupIt(test1,test3) test2,test3
from(
selecttest1,test3
fromtest
group by test1,test3
) a|||Hi Bart,
What spec server are you using? I can run either script in under a
second :-/
J|||On 23 nov, 15:46, jhofm...@.googlemail.com wrote:
Quote:
Originally Posted by
Hi Bart,
>
What spec server are you using? I can run either script in under a
second :-/
>
J
Ok, I asked for another testserver because the first one was
apparently overloaded (read: dead). I didn't notice that at first
because a simple table-select took no time at all and those other
scripts took 10-20 seconds. On the new server, it takes no time...
Yes, you are right and I am happy :). Thank you very much!
Bart|||>I guess there is no function to do this... What is the simplest solution? <<
Do it in the front end instead violating 1NF in the Database side.|||On 25 nov, 19:59, --CELKO-- <jcelko...@.earthlink.netwrote:
Quote:
Originally Posted by
Quote:
Originally Posted by
Quote:
Originally Posted by
I guess there is no function to do this... What is the simplest solution? <<
>
Do it in the front end instead violating 1NF in the Database side.
Hi,
I'm not an expert in that area, but I thought NF had to do with
database design and not with querying a database? Correct me if I'm
wrong.
I would like most of the logic on server side, (the report result is
retrieved by an excel report that mainly adds lay-out and adds the
possibility to further process the results) because when an update of
the report is needed, I only need to change the stored procedure and
not the 'front-end' excel reports with everybody that uses it.
Kind regards,
Bart|||"Bart op de grote markt" <warnezb@.googlemail.comwrote in message
news:3e7b897e-7ff7-436f-9291-adc2ab732c32@.s36g2000prg.googlegroups.com...
Quote:
Originally Posted by
On 25 nov, 19:59, --CELKO-- <jcelko...@.earthlink.netwrote:
Quote:
Originally Posted by
Quote:
Originally Posted by
>I guess there is no function to do this... What is the simplest
>solution? <<
>>
>Do it in the front end instead violating 1NF in the Database side.
>
Hi,
>
I'm not an expert in that area, but I thought NF had to do with
database design and not with querying a database? Correct me if I'm
wrong.
You're "wrong".
You can't really separate the two. That's like saying that wheels on a car
have to do with the design, not with the actual driving.
If you design your database properly, your queries follow from that.
Quote:
Originally Posted by
>
I would like most of the logic on server side, (the report result is
retrieved by an excel report that mainly adds lay-out and adds the
possibility to further process the results) because when an update of
the report is needed, I only need to change the stored procedure and
not the 'front-end' excel reports with everybody that uses it.
>
Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?
Quote:
Originally Posted by
>
Kind regards,
>
Bart
--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||If you design your database properly, your queries follow from that.
This is nice in theory, but in practice I have seen many occasions
where reporting requirements simply don't align with the database
(which you often have no control over and may have been designed for
an input system for example). Short of designing a new database and
ETL'ing your data across (which there certainly is a market for but in
a lot of cases would be overkill to meet a single requirement),
sometimes you have to write "non-standard" queries.
Quote:
Originally Posted by
Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?
Why would a stored procedure not qualify as a middle layer? It
provides a convenient interface between the front-end and the database
and still allows the use of this type of query which, in my opinion,
is neat and easy to implement in SQL. Does it matter if your entire
data structure underneath the stored proc changes as long as the proc
continues to serve up the same results?
J|||On 26 nov, 14:09, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@.greenms.comwrote:
Quote:
Originally Posted by
"Bart op de grote markt" <warn...@.googlemail.comwrote in messagenews:3e7b897e-7ff7-436f-9291-adc2ab732c32@.s36g2000prg.googlegroups.com...
>
Quote:
Originally Posted by
On 25 nov, 19:59, --CELKO-- <jcelko...@.earthlink.netwrote:
Quote:
Originally Posted by
I guess there is no function to do this... What is the simplest
solution? <<
>
Quote:
Originally Posted by
Quote:
Originally Posted by
Do it in the front end instead violating 1NF in the Database side.
>
Quote:
Originally Posted by
Hi,
>
Quote:
Originally Posted by
I'm not an expert in that area, but I thought NF had to do with
database design and not with querying a database? Correct me if I'm
wrong.
>
You're "wrong".
>
You can't really separate the two. That's like saying that wheels on a car
have to do with the design, not with the actual driving.
>
If you design your database properly, your queries follow from that.
I have not said that database design has nothing to do with querying a
database... But a query of a database is combining the available data
to hava a certain result. Putting the normal forms into your database
is a way to avoid data loss in your database when you update or delete
your data. If I query a database for a report, then the result won't
interfere with the database itself, it just gives a view on your data.
I don't want to be offensive or so, but I'm not convinced yet.
And ok, I did not design the database... it is a database from a new
application my company bought. (In fact it's about two databases from
two different applications that have to be linked in a report, but I
won't go too far to explain that :-) )
Quote:
Originally Posted by
Quote:
Originally Posted by
I would like most of the logic on server side, (the report result is
retrieved by an excel report that mainly adds lay-out and adds the
possibility to further process the results) because when an update of
the report is needed, I only need to change the stored procedure and
not the 'front-end' excel reports with everybody that uses it.
>
Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?
>
As has been said by J above, the Stored Procedure acts as middle layer
between the database and the reports. If there is an update of the
database (e.g. new product version), I will adapt the stored
procedure, so that the user doesn't even notice that anything has
changed.
Kind regards and thx for all your comments
Bart|||You're "wrong".
Actually Greg - You're "wrong".
SQL Server is a data engine and not just a relational data storage method.
There are lots and lots of extensions and features in SQL Server to help us
gain more performance, more simplicity instead of having to code stuff in
the middle tier all the time.
For instance, if I was writing a data export why on earth would I want to
use a second programming langauge that adds complexity when I can easily use
the functions and features in T-SQL.
There is a move more to putting business logic in the data engine rather
than just using the data engine as a put and get object - see research by
Jim Gray.
Quote:
Originally Posted by
Then do it in a middle layer. What happens when your DB changes for other
reasons but your reports aren't supposed to?
It would be a bigger change if you had done it in the middle tier - both the
data access queries would change AND the middle tier source code. That's a
lot more testing, development - it's higher risk, more complicated etc...
--
Tony Rogerson, SQL Server MVP
http://sqlblogcasts.com/blogs/tonyrogerson
[Ramblings from the field from a SQL consultant]
http://sqlserverfaq.com
[UK SQL User Community]
Thursday, March 22, 2012
Computed column
I am trying yo return a set or records by using a SELECT statement, the
table contains a column called CreationDate.
I want to add one more column to the resulting dataset (not to the table),
that value of the column will be 1 if the CreationDate is 1 day before the
current time, 0 otherwise.
Any idea how to do it?
thanks first,
Gnicuse a case statement.
SELECT 'return' = case
WHEN datediff > 0 THEN 1
ELSE 0
FROM table|||I tried the statement and it returns
"Incorrect syntax near the keyword 'FROM'."
So it doesn't work, it that a Yukon feature?
Because I am using SQL Server 2000
thanks
"Red2" <sdibello@.gmail.com> wrote in message
news:1142952098.221443.242870@.i39g2000cwa.googlegroups.com...
> use a case statement.
> SELECT 'return' = case
> WHEN datediff > 0 THEN 1
> ELSE 0
> FROM table
>|||Oh I found that I need to add an END keyword at the end the case statement.
It works now.
thanks for your help
Gnic
"Gnic" <gasnic@.gmail.com> wrote in message
news:e4rrd8QTGHA.1868@.TK2MSFTNGP09.phx.gbl...
>I tried the statement and it returns
> "Incorrect syntax near the keyword 'FROM'."
> So it doesn't work, it that a Yukon feature?
> Because I am using SQL Server 2000
> thanks
>
> "Red2" <sdibello@.gmail.com> wrote in message
> news:1142952098.221443.242870@.i39g2000cwa.googlegroups.com...
>sqlsql
Computed column
I am trying yo return a set or records by using a SELECT statement, the
table contains a column called CreationDate.
I want to add one more column to the resulting dataset (not to the table),
that value of the column will be 1 if the CreationDate is 1 day before the
current time, 0 otherwise.
Any idea how to do it?
thanks first,
Gnic
use a case statement.
SELECT 'return' = case
WHEN datediff > 0 THEN 1
ELSE 0
FROM table
|||I tried the statement and it returns
"Incorrect syntax near the keyword 'FROM'."
So it doesn't work, it that a Yukon feature?
Because I am using SQL Server 2000
thanks
"Red2" <sdibello@.gmail.com> wrote in message
news:1142952098.221443.242870@.i39g2000cwa.googlegr oups.com...
> use a case statement.
> SELECT 'return' = case
> WHEN datediff > 0 THEN 1
> ELSE 0
> FROM table
>
|||Oh I found that I need to add an END keyword at the end the case statement.
It works now.
thanks for your help
Gnic
"Gnic" <gasnic@.gmail.com> wrote in message
news:e4rrd8QTGHA.1868@.TK2MSFTNGP09.phx.gbl...
>I tried the statement and it returns
> "Incorrect syntax near the keyword 'FROM'."
> So it doesn't work, it that a Yukon feature?
> Because I am using SQL Server 2000
> thanks
>
> "Red2" <sdibello@.gmail.com> wrote in message
> news:1142952098.221443.242870@.i39g2000cwa.googlegr oups.com...
>
sqlsql
Monday, March 19, 2012
composite primary key
the appropriate primary keys and indexes for the table.
To make the records unique, i need to include two datetime columns (
start and end dates ) and two integer columns ( attributes of the
record ) to make up a composite primary key. Both of these four
columns are found in the WHERE clause of my queries.
Is it acceptable in the view of performance and how should i create
the indexes?Hi
I think this is probably a candidate for a surrogate key.
John
"onder" <okeen1@.hotmail.com> wrote in message
news:9c3d3756.0309022355.10656428@.posting.google.c om...
> i have a master table with around 15 columns and i am trying to find
> the appropriate primary keys and indexes for the table.
> To make the records unique, i need to include two datetime columns (
> start and end dates ) and two integer columns ( attributes of the
> record ) to make up a composite primary key. Both of these four
> columns are found in the WHERE clause of my queries.
> Is it acceptable in the view of performance and how should i create
> the indexes?|||Hi John,
sorry for this but can you tell a little more on that?|||Hi
There is usually quite a debate in the newsgroups when surrogate keys are
mentioned. Basically it is when you add a column that you populate with
unique values, so you can reference a row by that instead of the 4 columns
you mentioned. This is a way to reduce the width of any table that refers to
your table with a foreign key (you would need all 4 columns replicated in
that table if you did not use a surrogate key). If you searched google for
"Surrogate SQLServer" it will turn up a few debates.
Quite often a surrogate key is given the IDENTITY property, so SQL Server
will populate the values for you,
http://msdn.microsoft.com/library/d...asp?frame=true
but it is possible to maintain the values your self.
HTH
John
"onder" <okeen1@.hotmail.com> wrote in message
news:9c3d3756.0309030711.5f4f582c@.posting.google.c om...
> Hi John,
> sorry for this but can you tell a little more on that?|||Ok about the surrogate keys. But i have an issue to ask:
In my 'select' queries, i always have the three columns which defines
a unique combination. So, i think the optimization of the table design
should bear this in mind.
If i add a surrogate key to the table to be a primary key, then i will
need to get that column to my select queries and then do the updates
based on that column.
What would you recommend for the index of the table?|||Hi
You would need to have the surrogate key in the JOIN or WHERE clause. This
could be totally transparent to the user. As a Primary Key there will
already be an index on the surrogate key column,
It is possible a covering index on the surrogate and candidate key columns
will be useful, but like any index you should investigate if it is being
used and if the speed improvements are worth while.
John
"onder" <okeen1@.hotmail.com> wrote in message
news:9c3d3756.0309080039.65cccc67@.posting.google.c om...
> Ok about the surrogate keys. But i have an issue to ask:
> In my 'select' queries, i always have the three columns which defines
> a unique combination. So, i think the optimization of the table design
> should bear this in mind.
> If i add a surrogate key to the table to be a primary key, then i will
> need to get that column to my select queries and then do the updates
> based on that column.
> What would you recommend for the index of the table?
Sunday, March 11, 2012
Complicated query
I have two tables Trade table and Cons table. Records are inserted in
both the tables independent of each other. There are fields like
Exc_Ref, Qty, Date in both the tables.
I need to write a query which should give me records :
1. Where there is missing Exc_Ref value in either of the table. i.e.
If Trade table has a Exc_Ref value but missing in Cons table then that
record should be displayed. Similarly if Cons has a Exc_Ref value
which is not found in Trade table then that too should be displayed.
2. In case where both the tables have matching Exc_Ref data then it
should display the record only when the remaining column does not
match like Qty or Date.
Please help me to resolve this complicated query.
Thanks
Nick--these 2 table need to have the same number of columns and datatypes
if you want to use union
--take the union out if they don't have the same number of columns and
datatypes
--you can also do this with 2 left or right joins or even a full outer
join
select *,'missing in Trade' as Missing
from Trade t
where not exists (select * from Cons c where c.Exc_Ref=t.Exc_Ref)
union all
select *,'missing in Cons' as Missing
from Cons c
where not exists (select * from Trade t where c.Exc_Ref=t.Exc_Ref)
--column mismatch
select * from Trade t
join Cons c on c.Exc_Ref=t.Exc_Ref
where c.Qty <> t.Qty
or c.Date <> t.Date
...
...
...
<rest of columns>
Denis The SQL Menace
http://sqlservercode.blogspot.com
http://sqlblog.com/blogs/denis_gobo/default.aspx|||On Sep 6, 12:29 pm, Nick <nachiket.shirwal...@.gmail.com> wrote:
> Hi,
> I have two tables Trade table and Cons table. Records are inserted in
> both the tables independent of each other. There are fields like
> Exc_Ref, Qty, Date in both the tables.
> I need to write a query which should give me records :
> 1. Where there is missing Exc_Ref value in either of the table. i.e.
> If Trade table has a Exc_Ref value but missing in Cons table then that
> record should be displayed. Similarly if Cons has a Exc_Ref value
> which is not found in Trade table then that too should be displayed.
> 2. In case where both the tables have matching Exc_Ref data then it
> should display the record only when the remaining column does not
> match like Qty or Date.
> Please help me to resolve this complicated query.
> Thanks
> Nick
select * from Trade t
FOOL OUTER join Cons c on c.Exc_Ref=t.Exc_Ref
AND c.Qty = t.Qty
AND c.Date = t.Date|||"Alex Kuznetsov" <alkuzo@.gmail.com> wrote in message
news:1189110636.605076.236920@.r34g2000hsd.googlegroups.com...
>.
> select * from Trade t FOOL OUTER join Cons c ...
It is no easy task to personalize a query. Good job:)
www.beyondsql.blogspot.com
Complicated query
I have two tables Trade table and Cons table. Records are inserted in
both the tables independent of each other. There are fields like
Exc_Ref, Qty, Date in both the tables.
I need to write a query which should give me records :
1. Where there is missing Exc_Ref value in either of the table. i.e.
If Trade table has a Exc_Ref value but missing in Cons table then that
record should be displayed. Similarly if Cons has a Exc_Ref value
which is not found in Trade table then that too should be displayed.
2. In case where both the tables have matching Exc_Ref data then it
should display the record only when the remaining column does not
match like Qty or Date.
Please help me to resolve this complicated query.
Thanks
NickSomething along these lines should do it.
SELECT COALESCE(A.Exc_Ref, B.Exc_Ref) as Exc_Ref,
CASE WHEN B.Exc_Ref IS NULL
THEN 'Trade'
WHEN A.Exc_Ref IS NULL
THEN 'Cons'
ELSE ' <'
End as Compare,
A.OtherCol1, B.OtherCol1,
...
A.OtherCol9, B.OtherCol9
FROM Trade as A
FULL OUTER
JOIN Cons as B
ON A.Exc_Ref = B.Exc_Ref
WHERE A.Exc_Ref IS NULL
OR B.Exc_Ref IS NULL
OR A.OtherCol1 <B.OtherCol1
OR ...
OR A.OtherCol9 <B.OtherCo9
This assumes that Exc_Ref is the unique key to both tables.
Roy Harvey
Beacon Falls, CT
On Thu, 06 Sep 2007 10:29:30 -0700, Nick
<nachiket.shirwalkar@.gmail.comwrote:
Quote:
Originally Posted by
>Hi,
>
>I have two tables Trade table and Cons table. Records are inserted in
>both the tables independent of each other. There are fields like
>Exc_Ref, Qty, Date in both the tables.
>
>I need to write a query which should give me records :
>
>1. Where there is missing Exc_Ref value in either of the table. i.e.
>If Trade table has a Exc_Ref value but missing in Cons table then that
>record should be displayed. Similarly if Cons has a Exc_Ref value
>which is not found in Trade table then that too should be displayed.
>
>2. In case where both the tables have matching Exc_Ref data then it
>should display the record only when the remaining column does not
>match like Qty or Date.
>
>Please help me to resolve this complicated query.
>
>Thanks
>Nick
Quote:
Originally Posted by
Something along these lines should do it.
>
SELECT COALESCE(A.Exc_Ref, B.Exc_Ref) as Exc_Ref,
CASE WHEN B.Exc_Ref IS NULL
THEN 'Trade'
WHEN A.Exc_Ref IS NULL
THEN 'Cons'
ELSE ' <'
End as Compare,
A.OtherCol1, B.OtherCol1,
...
A.OtherCol9, B.OtherCol9
FROM Trade as A
FULL OUTER
JOIN Cons as B
ON A.Exc_Ref = B.Exc_Ref
WHERE A.Exc_Ref IS NULL
OR B.Exc_Ref IS NULL
OR A.OtherCol1 <B.OtherCol1
OR ...
OR A.OtherCol9 <B.OtherCo9
>
This assumes that Exc_Ref is the unique key to both tables.
>
Roy Harvey
Beacon Falls, CT
>
On Thu, 06 Sep 2007 10:29:30 -0700, Nick
>
>
>
<nachiket.shirwal...@.gmail.comwrote:
Quote:
Originally Posted by
Hi,
>
Quote:
Originally Posted by
I have two tables Trade table and Cons table. Records are inserted in
both the tables independent of each other. There are fields like
Exc_Ref, Qty, Date in both the tables.
>
Quote:
Originally Posted by
I need to write a query which should give me records :
>
Quote:
Originally Posted by
1. Where there is missing Exc_Ref value in either of the table. i.e.
If Trade table has a Exc_Ref value but missing in Cons table then that
record should be displayed. Similarly if Cons has a Exc_Ref value
which is not found in Trade table then that too should be displayed.
>
Quote:
Originally Posted by
2. In case where both the tables have matching Exc_Ref data then it
should display the record only when the remaining column does not
match like Qty or Date.
>
Quote:
Originally Posted by
Please help me to resolve this complicated query.
>
Quote:
Originally Posted by
Thanks
Nick- Hide quoted text -
>
- Show quoted text -
Thanks Roy ! Your solution is too perfect.
Thanks
Thursday, March 8, 2012
complex stored procedure on history table
Hi All,
I have a table that hold status history records for cases. In this table is a status field with values, opened, assigned, or complete. Each case can be assigned a number of times before it is complete, and can be reassigned. I have the need to run a query that will get each case that is still assigned, and not yet complete. I wrote a stored procedure that contains a cursor containing each case, and get the last status history record for each case and puts it into a temp table to return to the user, but is hurting performance as there are .5 million records here. Does anyone know of a better way of doing this?
Thanks in advance : )
Found an answer elsewhere using correlated sub queries. thankscomplex sql server 2005 query
A sql server table is populated with records every 2 minutes. See below sample table
In the table, the Import_Date is a datetime field.
create table tblData
(
ID int identity(1, 1),
SourceID int,
SourceCode varchar(255)
Security varchar(255),
Bprice decimal(12, 8),
Aprice decimal(12, 8),
ImportDate datetime
)
Here is a populated table.
I have left gaps for better visual checks for you.
ID SourceID SourceCode Security Bprice BpriceSize Aprice ApriceSize ImportDate
1 1 sourceA SecA 100.2 2 99.12 1 2007-11-07 16:24:31.297
2 2 sourceW SecH 95.7 89.43 2007-11-07 16:24:31.297
3 3 SourceX SecS 50.56 1 76.44 4 2007-11-07 16:24:31.297
4 4 SourceQ SecZ 87.98 2007-11-07 16:24:31.297
5 5 SourceJ SecH 100.2 99.12 2 2007-11-07 16:24:31.297
6 6 SourceK SecU 2007-11-07 16:24:31.297
7 7 SourceT SecA 50.56 3 87.11 2007-11-07 16:24:31.297
8 1 sourceA SecA 100.2 6 99.12 2 2007-11-07 16:26:15.123
9 2 sourceW SecH 99.54 4 89.43 2007-11-07 16:26:15.123
10 3 SourceX SecS 50.56 2 19.33 2007-11-07 16:26:15.123
11 4 SourceQ SecZ 16.98 87.98 2007-11-07 16:26:15.123
12 5 SourceJ SecH 100.2 1 99.12 2 2007-11-07 16:26:15.123
13 6 SourceK SecU 2007-11-07 16:26:15.123
14 7 SourceT SecA 50.56 2 87.11 1 2007-11-07 16:26:15.123
15 1 sourceA SecA 100.2 1 87.11 1 2007-11-07 16:26:15.123
16 2 sourceW SecH 99.66 89.43 2 2007-11-07 16:26:15.123
17 3 SourceX SecS 50.56 2 19.33 2007-11-07 16:26:15.123
18 4 SourceQ SecZ 16.98 3 87.98 3 2007-11-07 16:26:15.123
19 5 SourceJ SecH 100.2 3 99.12 3 2007-11-07 16:26:15.123
20 6 SourceK SecU 2007-11-07 16:26:15.123
21 7 SourceT SecA 101.32 5 87.11 3 2007-11-07 16:26:15.123
...
I am trying to build a sql query to show which source is offering the max(Bprice) and who is offering the min(Aprice).
In addition if more than one sources are offering the same prices then they should be shown as shown below in the first record i.e. (SourceA, SourceT) --> 3 + 1 = 4
This is what I would like to see:
Security Max_Bprice Bprice_Size Bprice_SourceCode Min_Aprice Aprice_Size Aprice_SourceCode
SecA 101.32 5 SourceT 87.11 4 SourceA, SourceT
SecH 100.2 3 SourceJ 89.43 2 SourceW
SecS 50.56 2 SourceX 19.33 SourceX
SecZ 16.98 3 SourceQ 87.98 3 SourceQ
What is the sql query to do this please?
This is what I have started with but it is not correct...
select
Security,
max(Bprice) as 'Max_Bprice',
SourceCode as 'Bprice_SourceCode',
min(Aprice) as 'Min_Aprice',
SourceCode as 'Aprice_SourceCode'
from
tblData
group by
Security,
SourceCodeHi
You are almost there but not quite. Could you provide your sample data as point 3 here please:
http://www.dbforums.com/showthread.php?t=1196943
Also, your DDL does not match the data you have supplied.
Not really related, but there looks to be a third normal form issue here.
Cheers|||http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=92298|||Here it is.
Thanks
DECLARE @.Sample TABLE (ID INT, SourceID INT, SourceCode VARCHAR(20), Security VARCHAR(20), Bprice MONEY, BpriceSize INT, Aprice MONEY, ApriceSize INT, ImportDate DATETIME)
INSERT @.Sample
SELECT 1, 1, 'sourceA', 'SecA', 100.2 , 2, 99.12, 1, '2007-11-07 16:24:31.297' UNION ALL
SELECT 2, 2, 'sourceW', 'SecH', 95.7 , NULL, 89.43, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 3, 3, 'SourceX', 'SecS', 50.56, 1, 76.44, 4, '2007-11-07 16:24:31.297' UNION ALL
SELECT 4, 4, 'SourceQ', 'SecZ', 87.98, NULL, NULL, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 5, 5, 'SourceJ', 'SecH', 100.2 , NULL, 99.12, 2, '2007-11-07 16:24:31.297' UNION ALL
SELECT 6, 6, 'SourceK', 'SecU', NULL, NULL, NULL, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 7, 7, 'SourceT', 'SecA', 50.56, 3, 87.11, NULL, '2007-11-07 16:24:31.297' UNION ALL
SELECT 8, 1, 'sourceA', 'SecA', 100.2 , 6, 99.12, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 9, 2, 'sourceW', 'SecH', 99.54, 4, 89.43, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 10, 3, 'SourceX', 'SecS', 50.56, 2, 19.33, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 11, 4, 'SourceQ', 'SecZ', 16.98, NULL, 87.98, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 12, 5, 'SourceJ', 'SecH', 100.2 , 1, 99.12, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 13, 6, 'SourceK', 'SecU', NULL, NULL, NULL, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 14, 7, 'SourceT', 'SecA', 50.56, 2, 87.11, 1, '2007-11-07 16:26:15.123' UNION ALL
SELECT 15, 1, 'sourceA', 'SecA', 100.2 , 1, 87.11, 1, '2007-11-07 16:26:15.123' UNION ALL
SELECT 16, 2, 'sourceW', 'SecH', 99.66, NULL, 89.43, 2, '2007-11-07 16:26:15.123' UNION ALL
SELECT 17, 3, 'SourceX', 'SecS', 50.56, 2, 19.33, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 18, 4, 'SourceQ', 'SecZ', 16.98, 3, 87.98, 3, '2007-11-07 16:26:15.123' UNION ALL
SELECT 19, 5, 'SourceJ', 'SecH', 100.2 , 3, 99.12, 3, '2007-11-07 16:26:15.123' UNION ALL
SELECT 20, 6, 'SourceK', 'SecU', NULL , NULL, NULL, NULL, '2007-11-07 16:26:15.123' UNION ALL
SELECT 21, 7, 'SourceT', 'SecA', 101.32, 5, 87.11, 3, '2007-11-07 16:26:15.123'|||I'll leave it to Peso. He'll get it soon enough I would imagine.|||Thanks anyway|||I suggest that you break it up
Do one, then the other, then combine them
I think you can do a union or a join of 2 derived table.
Since each derived table is going to be a single row, you wont have to worry about a cartesian product
Wednesday, March 7, 2012
Complex Searches
Currently, if you enter "nike putter" it only returns records that
have "nike putter" as a connected phrase. How do I get the db to
return records that have "nike" or "putter" in say the Title field and/
or "nike" or "putter" in the Description field?
So, if a record has a Title with "putter" and a description with the
word "Nike", what needs to be done either with the SQL code or the SQL
server configuration to return the record if a search enters "Nike
Putter"?
Thanks.
Brett
Brett
Perhaps you need to consider ful-text search to enable on the database.
"Brett_A" <brettatkin@.gmail.com> wrote in message
news:1181046816.006183.220600@.q66g2000hsg.googlegr oups.com...
>I have a search form that searches multiple fields in a db.
> Currently, if you enter "nike putter" it only returns records that
> have "nike putter" as a connected phrase. How do I get the db to
> return records that have "nike" or "putter" in say the Title field and/
> or "nike" or "putter" in the Description field?
> So, if a record has a Title with "putter" and a description with the
> word "Nike", what needs to be done either with the SQL code or the SQL
> server configuration to return the record if a search enters "Nike
> Putter"?
> Thanks.
> Brett
>
|||Thanks for the quick reply but I have no way of knowing how the
searcher will enter the phrase. They may enter "putter nike titleist"
as a search phrase.
So maybe my question should be how to search the db for each
individual word used in the search term.
Brett
On Jun 5, 8:46 am, "vt" <vinu.t.1...@.gmail.com> wrote:[vbcol=seagreen]
> Hi
> try some thing like this
> select * from table where Title like '%nike%putter' or Description like
> '%nike%putter'
> Regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"Brett_A" <brettat...@.gmail.com> wrote in message
> news:1181046816.006183.220600@.q66g2000hsg.googlegr oups.com...
>
>
Complex Searches
Currently, if you enter "nike putter" it only returns records that
have "nike putter" as a connected phrase. How do I get the db to
return records that have "nike" or "putter" in say the Title field and/
or "nike" or "putter" in the Description field?
So, if a record has a Title with "putter" and a description with the
word "Nike", what needs to be done either with the SQL code or the SQL
server configuration to return the record if a search enters "Nike
Putter"?
Thanks.
BrettHi
try some thing like this
select * from table where Title like '%nike%putter' or Description like
'%nike%putter'
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Brett_A" <brettatkin@.gmail.com> wrote in message
news:1181046816.006183.220600@.q66g2000hsg.googlegroups.com...
>I have a search form that searches multiple fields in a db.
> Currently, if you enter "nike putter" it only returns records that
> have "nike putter" as a connected phrase. How do I get the db to
> return records that have "nike" or "putter" in say the Title field and/
> or "nike" or "putter" in the Description field?
> So, if a record has a Title with "putter" and a description with the
> word "Nike", what needs to be done either with the SQL code or the SQL
> server configuration to return the record if a search enters "Nike
> Putter"?
> Thanks.
> Brett
>|||Brett
Perhaps you need to consider ful-text search to enable on the database.
"Brett_A" <brettatkin@.gmail.com> wrote in message
news:1181046816.006183.220600@.q66g2000hsg.googlegroups.com...
>I have a search form that searches multiple fields in a db.
> Currently, if you enter "nike putter" it only returns records that
> have "nike putter" as a connected phrase. How do I get the db to
> return records that have "nike" or "putter" in say the Title field and/
> or "nike" or "putter" in the Description field?
> So, if a record has a Title with "putter" and a description with the
> word "Nike", what needs to be done either with the SQL code or the SQL
> server configuration to return the record if a search enters "Nike
> Putter"?
> Thanks.
> Brett
>|||Thanks for the quick reply but I have no way of knowing how the
searcher will enter the phrase. They may enter "putter nike titleist"
as a search phrase.
So maybe my question should be how to search the db for each
individual word used in the search term.
Brett
On Jun 5, 8:46 am, "vt" <vinu.t.1...@.gmail.com> wrote:
> Hi
> try some thing like this
> select * from table where Title like '%nike%putter' or Description like
> '%nike%putter'
> Regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"Brett_A" <brettat...@.gmail.com> wrote in message
> news:1181046816.006183.220600@.q66g2000hsg.googlegroups.com...
> >I have a search form that searches multiple fields in a db.
> > Currently, if you enter "nike putter" it only returns records that
> > have "nike putter" as a connected phrase. How do I get the db to
> > return records that have "nike" or "putter" in say the Title field and/
> > or "nike" or "putter" in the Description field?
> > So, if a record has a Title with "putter" and a description with the
> > word "Nike", what needs to be done either with the SQL code or the SQL
> > server configuration to return the record if a search enters "Nike
> > Putter"?
> > Thanks.
> > Brett
Complex Searches
Currently, if you enter "nike putter" it only returns records that
have "nike putter" as a connected phrase. How do I get the db to
return records that have "nike" or "putter" in say the Title field and/
or "nike" or "putter" in the Description field?
So, if a record has a Title with "putter" and a description with the
word "Nike", what needs to be done either with the SQL code or the SQL
server configuration to return the record if a search enters "Nike
Putter"?
Thanks.
BrettHi
try some thing like this
select * from table where Title like '%nike%putter' or Description like
'%nike%putter'
Regards
VT
Knowledge is power, share it...
http://oneplace4sql.blogspot.com/
"Brett_A" <brettatkin@.gmail.com> wrote in message
news:1181046816.006183.220600@.q66g2000hsg.googlegroups.com...
>I have a search form that searches multiple fields in a db.
> Currently, if you enter "nike putter" it only returns records that
> have "nike putter" as a connected phrase. How do I get the db to
> return records that have "nike" or "putter" in say the Title field and/
> or "nike" or "putter" in the Description field?
> So, if a record has a Title with "putter" and a description with the
> word "Nike", what needs to be done either with the SQL code or the SQL
> server configuration to return the record if a search enters "Nike
> Putter"?
> Thanks.
> Brett
>|||Brett
Perhaps you need to consider ful-text search to enable on the database.
"Brett_A" <brettatkin@.gmail.com> wrote in message
news:1181046816.006183.220600@.q66g2000hsg.googlegroups.com...
>I have a search form that searches multiple fields in a db.
> Currently, if you enter "nike putter" it only returns records that
> have "nike putter" as a connected phrase. How do I get the db to
> return records that have "nike" or "putter" in say the Title field and/
> or "nike" or "putter" in the Description field?
> So, if a record has a Title with "putter" and a description with the
> word "Nike", what needs to be done either with the SQL code or the SQL
> server configuration to return the record if a search enters "Nike
> Putter"?
> Thanks.
> Brett
>|||Thanks for the quick reply but I have no way of knowing how the
searcher will enter the phrase. They may enter "putter nike titleist"
as a search phrase.
So maybe my question should be how to search the db for each
individual word used in the search term.
Brett
On Jun 5, 8:46 am, "vt" <vinu.t.1...@.gmail.com> wrote:[vbcol=seagreen]
> Hi
> try some thing like this
> select * from table where Title like '%nike%putter' or Description like
> '%nike%putter'
> Regards
> VT
> Knowledge is power, share it...http://oneplace4sql.blogspot.com/"Brett_A"
<brettat...@.gmail.com> wrote in message
> news:1181046816.006183.220600@.q66g2000hsg.googlegroups.com...
>
>
>
>
Sunday, February 12, 2012
Comparison with multiple tables
I am using SQL in Access and need to pull all of the records that don't
match in the key field. The key fields are the same name in both tables and
I
have built a relationship on a different field. Both tables have some
matching records and some non matching. I want all of the records from both
tables that do not match!SELECT a.<column list>, b.<column list>
FROM a
FULL OUTER JOIN b
ON a.<primary key> = b.<primary key>
WHERE a.<primary key> IS NULL OR b.<primary key> IS NULL
Jacco Schalkwijk
SQL Server MVP
"Tess9126" <Tess9126@.discussions.microsoft.com> wrote in message
news:40EB8FA3-B1C8-412A-BC20-4E3E273EDD72@.microsoft.com...
> HELP!!!!
> I am using SQL in Access and need to pull all of the records that don't
> match in the key field. The key fields are the same name in both tables
> and I
> have built a relationship on a different field. Both tables have some
> matching records and some non matching. I want all of the records from
> both
> tables that do not match!|||Try,
select *
from t1 full outer join t2
on t1.pk_col = t2.pk_col
where t1.pk_col is null or t2.pk_col is null
AMB
"Tess9126" wrote:
> HELP!!!!
> I am using SQL in Access and need to pull all of the records that don't
> match in the key field. The key fields are the same name in both tables an
d I
> have built a relationship on a different field. Both tables have some
> matching records and some non matching. I want all of the records from bot
h
> tables that do not match!|||Access did not like the word outer. I am at work with absolutely no referenc
e
material. I am familure with SQL but I am really stuck. I want to thank you
for trying to help me!
"Jacco Schalkwijk" wrote:
> SELECT a.<column list>, b.<column list>
> FROM a
> FULL OUTER JOIN b
> ON a.<primary key> = b.<primary key>
> WHERE a.<primary key> IS NULL OR b.<primary key> IS NULL
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "Tess9126" <Tess9126@.discussions.microsoft.com> wrote in message
> news:40EB8FA3-B1C8-412A-BC20-4E3E273EDD72@.microsoft.com...
>
>|||Ah, Access. My Access SQL is rather rusty, so maybe the following will work:
SELECT a.<column list>, b.<column list>
FROM a
LEFT OUTER JOIN b
ON a.<primary key> = b.<primary key>
WHERE b.<primary key> IS NULL
UNION
SELECT a.<column list>, b.<column list>
FROM b
LEFT OUTER JOIN a
ON b.<primary key> = a.<primary key>
WHERE a.<primary key> IS NULL
If it doesn't work, the best thing to do is post your question on an Access
newsgroup.
Jacco Schalkwijk
SQL Server MVP
"Tess9126" <Tess9126@.discussions.microsoft.com> wrote in message
news:43F5BF5D-9B8C-401C-B1F7-136381102C7C@.microsoft.com...
> Access did not like the word outer. I am at work with absolutely no
> reference
> material. I am familure with SQL but I am really stuck. I want to thank
> you
> for trying to help me!
> "Jacco Schalkwijk" wrote:
>|||Then you will need a union all of two outer joins.
select t1.c1, ..., t1.cn
from t1 left join t2 on t1.pk_col = t2.pk_col
where t2.pk_col is null
union all
select t2.c1, ..., t2.cn
from t1 right join t2 on t1.pk_col = t2.pk_col
where t1.pk_col is null
AMB
"Tess9126" wrote:
> Access did not like the word outer. I am at work with absolutely no refere
nce
> material. I am familure with SQL but I am really stuck. I want to thank yo
u
> for trying to help me!
> "Jacco Schalkwijk" wrote:
>
comparison on text field
but, the result includes all records that having blanked ModelRef field.
I want to know what setting controls this behavior.
Thanks,
LeonardHi
You may want to look at using NULL instead of empty strings.
John
"Leonard Poon" wrote:
> my query: select * from inventory where ModelRef=' '
> but, the result includes all records that having blanked ModelRef field.
> I want to know what setting controls this behavior.
> Thanks,
> Leonard
>
>
Friday, February 10, 2012
Comparison
Any suggestions would be greatly appreciated...
thanks,
JonathanDo you record record changes? (audit)
Does the audit contain a time stamp?|||That database has a modifieddate field in epoch...
I convert it into human readable then will have compare values from
(i.e 9/13/2007 <> 9/14/2007)
If there are any changes amongst the field values create a report of those changes
Comparing two tables in two different databases
Note that both databases are mirrors of each other but contain slightly different data and are on different servers.
There a couple of tools on the market like RedGate Data Compare.
The SQL Server 2005 program directory also contains a commandline utility called tablediff.exe which does just what you want.
WesleyB
Visit my SQL Server weblog @. http://dis4ea.blogspot.com