Hi All,
I have what seems to me to be a difficult query request for a database
I've inherited.
I have a table that has a varchar(2000) column that is used to store
system and user messages from an on-line ordering system.
For some reason (I have no idea why), when the original database was
being designed no thought was given to putting these messages in
another table, one row per message, and I've now been asked to provide
some stats on the contents of this field across the recordset.
A pseudo example of the table would be:
custrep, orderid, orderdate, comments
1, 10001, 2004-04-12, :Comment 1:Comment 2:Comment 3:Customer asked
for a brown model
2, 10002, 2004-04-12, :Comment 3:Comment 4:
1, 10003, 2004-04-12, :Comment 2:Comment 8:
2, 10004, 2004-04-12, :Comment 4:Comment 6:Comment 7:
2, 10005, 2004-04-12, :Comment 1:Comment 6:Customer cancelled order
So, what I've been asked to provide is something like this:
orderdate, custrep, syscomment, countofsyscomme nts
2004-04-12, 1, Comment 1, 1
2004-04-12, 1, Comment 2, 2
2004-04-12, 1, Comment 3, 1
2004-04-12, 1, Comment 8, 1
2004-04-12, 2, Comment 1, 1
2004-04-12, 2, Comment 3, 1
2004-04-12, 2, Comment 4, 2
2004-04-12, 2, Comment 6, 2
2004-04-12, 2, Comment 7, 1
I have a table in which each of the system comments are defined.
Anything else appearing in the column is treated as a user comment.
Does anyone have any thoughts on how this could be achieved? The end
result will end up in an SQL Server 2000 stored procedure which will
be called from an ASP page to provide order taking stats.
Any help will be humbly and immensely appreciated!
Much warmth,
Murray 7 2828
Assuming your tables look something like this:
CREATE TABLE Orders (custrep INTEGER NOT NULL, orderid INTEGER, orderdate
DATETIME NOT NULL, comment1 VARCHAR(2000) NULL, comment2 VARCHAR(2000) NULL,
comment3 VARCHAR(2000) NULL, comment4 VARCHAR(2000) NULL /*, PRIMARY KEY ???
*/)
CREATE TABLE SystemComments (comment VARCHAR(2000) PRIMARY KEY)
Try this:
SELECT O.orderdate, O.custrep, O.comment,
COUNT(S.comment ) AS count_of_syscom ments
FROM
(SELECT orderdate, custrep, comment1
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment2
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment3
FROM Orders
UNION ALL
SELECT orderdate, custrep, comment4
FROM Orders)
AS O (orderdate, custrep, comment)
LEFT JOIN SystemComments AS S
ON O.comment = S.comment
GROUP BY O.orderdate, O.custrep, O.comment
--
David Portas
SQL Server MVP
--
On Fri, 14 May 2004 15:51:25 +0100, "David Portas"
<RE************ *************** *@acm.org> wrote: Assuming your tables look something like this:
CREATE TABLE Orders (custrep INTEGER NOT NULL, orderid INTEGER, orderdate DATETIME NOT NULL, comment1 VARCHAR(2000) NULL, comment2 VARCHAR(2000) NULL, comment3 VARCHAR(2000) NULL, comment4 VARCHAR(2000) NULL /*, PRIMARY KEY ??? */)
CREATE TABLE SystemComments (comment VARCHAR(2000) PRIMARY KEY)
Try this:
SELECT O.orderdate, O.custrep, O.comment, COUNT(S.comment ) AS count_of_syscom ments FROM (SELECT orderdate, custrep, comment1 FROM Orders UNION ALL SELECT orderdate, custrep, comment2 FROM Orders UNION ALL SELECT orderdate, custrep, comment3 FROM Orders UNION ALL SELECT orderdate, custrep, comment4 FROM Orders) AS O (orderdate, custrep, comment) LEFT JOIN SystemComments AS S ON O.comment = S.comment GROUP BY O.orderdate, O.custrep, O.comment
Hi David,
Thanks for the suggestion, unfortunately that's not how the table is
defined.
Sorry, I should have posted a pseudo create table statement as well.
It looks something like:
CREATE TABLE OrderComments (custrep INTEGER NOT NULL, orderid INTEGER,
orderdate DATETIME NOT NULL, comments VARCHAR(2000))
The create table statement you have for SystemComments is fine.
So, in the OrderComments table, the comments column might contain:
':Comment 1:Comment 2: Comment 8:Comment whatever'
So, each of the system and user generated comments for a particular
order are concatenated into a string and are put into a single column
(comments column) for that order.
Sorry for the confusion...
Much warmth,
Murray
>So, in the OrderComments table, the comments column might contain: ':Comment 1:Comment 2: Comment 8:Comment whatever'
So, each of the system and user generated comments for a particular order are concatenated into a string and are put into a single column (comments column) for that order.
Sorry for the confusion...
Much warmth,
Murray
Can I assume that these are free form and free for all type of
comments and not standardized ?
Is there some kind of unique seperator between comments ?
Been there, done this real recently and it wasn't pretty at all.
Randy http://members.aol.com/rsmeiner
You can try this:
SELECT O.orderdate, O.custrep, O.comments,
COALESCE(SUM((L EN(O.comments)-LEN(REPLACE(O.c omments,S.comme nt,'')))
/LEN(S.comment)) ,0)
FROM OrderComments AS O
LEFT JOIN SystemComments AS S
ON O.comments LIKE '%'+S.comment+' %'
GROUP BY O.orderdate, O.custrep, O.comments
Don't expect great performance though!
--
David Portas
SQL Server MVP
--
On 14 May 2004 15:20:02 GMT, rs******@aol.co mcrap (RSMEINER) wrote:
[snip]
Can I assume that these are free form and free for all type of comments and not standardized ?
Is there some kind of unique seperator between comments ?
Been there, done this real recently and it wasn't pretty at all.
Hi Randy,
Pretty much, except that I have a reference table of the exact wording
of each of the system comments that might be found in the concatenated
value in the comments column.
The comments are delimited by a colon character, but I can't assume
that user comments, which get concatenated in the same field, will
always be lacking colon characters.
The only thing I can think to do is create a temp table in a stored
procedure and do multiple update...select statements to populate the
temp table, using the values in the predfined comments table.
I hear you that it isn't pretty.
Much warth,
Murray
>Hi Randy, Pretty much, except that I have a reference table of the exact wording of each of the system comments that might be found in the concatenated value in the comments column.
The comments are delimited by a colon character, but I can't assume that user comments, which get concatenated in the same field, will always be lacking colon characters.
The only thing I can think to do is create a temp table in a stored procedure and do multiple update...select statements to populate the temp table, using the values in the predfined comments table.
I hear you that it isn't pretty.
Much warth,
Murray
Since you have a table of the system comments, it makes it
much easier. I'm thinking on this.
How big are these tables ?
Randy http://members.aol.com/rsmeiner
Did you try my second solution?
--
David Portas
SQL Server MVP
-- This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Adrian Parker |
last post by:
Hi. I would like to query a database, given several where clauses to refine
my search, and return the value of one single field in the database.
eg: I have a table that lists teachers. Their first name, last name, age,
unique teacher number, etc is in the file.
I want to return the unique teacher number, for example, of the teacher
whose first name is Jane and last name is Doe.
How does one do this?
|
by: |
last post by:
If I need to check if a certain value does exist in a field, and return
either "yes" or "not" which query would be the most effestive?
|
by: Robert Brown |
last post by:
suppose I have the following table:
CREATE TABLE (int level, color varchar, length int, width int, height
int)
It has the following rows
1, "RED", 8, 10, 12
2, NULL, NULL, NULL, 20
3, NULL, 9, 82, 25
|
by: wnstnsmith |
last post by:
Dear all,
Suppose there is a table whose records contain a textfield TXT, and a query
that returns a subset of that table. Is it possible to have that query
return all the TXT-fields gathered together in one string? I really have no
idea as to how to tackle this. For clarity, here's an example of what I
mean:
there are three tables
|
by: Justin Koivisto |
last post by:
I am attempting to execute a *long* query string via a
ADODB.Recordset.Open (queryStr) call. Most of the time, the query string
will be less than 100 characters, but in some cases, it may be up to
2000 chracters. Right now the code looks like this:
Private Sub Command3_MouseUp(Button As Integer, Shift As Integer, X As
Single, Y As Single)
Dim rslt As ADODB.Recordset, rslt2 As ADODB.Recordset
Dim sqlstr As String, sqlstr2 As String
Dim...
| |
by: Brian |
last post by:
Hello,
Basically, I'm running a query on a form's activation, and I'd like to
have the results of the query be placed into other fields on the same
form automatically.
Does anybody know how this can be done?
I've tried setting the default value of the text fields on the form to
be equal to ! using Access' expression
|
by: Sreedhar Vankayala |
last post by:
Hi,
I have a table IV00108wc which contains several thousand records in whcih
ITEMNMBR is the primary key.
I have a com+ object returning recordset which has ITEMNMBR and another
details related.
I want to query another table based on Com+ object recordset.
Select distinct WebClas1 from IV00108wc where ItemNmbr in (...).
|
by: Aris |
last post by:
Hi all!
this is the code:
#include <iostream.h>
#include <stdlib.h>
#include <stdio.h>
#include <string.h>
void MyFunction(char *B)
{
cout<<sizeof(B)<<endl; //(1)
|
by: Merennulli |
last post by:
To start with, I'll give a simplified overview of my data.
BaseRecord (4mil rows, 25k in each Region)
ID | Name | Region | etc
OtherData (7.5mil rows, 1 or 2 per ID)
ID | Type(1/2) | Data
ProblemTable (4mil rows)
ID | ConcatenatedHistory
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |