|
|
Learn to Normalize. A simple set of rules that can be applied to any collection of facts, and which results in a relational and normalized dataset.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
Dear all,
I'm new comer in SQL, I have problem in web development, in my system there is searching for phonetic name like nielsen and nelson. Example:
If I enter test word 'nielsen' then output can be 'nilsen','nelson'. I've used sounds like but it given so many return and the return have far distance with test word. Also I have implemented it with traditional way, but the searching running slow, I compare some data (from excel, the number of data 200 record) with database record about 600.000 record.Does anyone have idea with my problem?Or have implement this case? Thank you for helping..
|
|
|
|
|
As you haven't said what the database is, I'm going to assume you are talking about SQL Server. Most "real" databases provide a SOUNDEX[^] function which can be used to do this form of matching.
|
|
|
|
|
Hi nagy,
Thank you for your answer, I used MY SQL, and I've used soundex, but it return too many result which not match to my keyword, how about you idea bro?
Thank you,
Regards.
|
|
|
|
|
Please try out this Code Project Article[^]. It has a total of 6 parts and the current one handles your situation.
Also you can try out with Metaphone approach which performs better that Soundex. Here[^] is an implementation for the same.
Hope that helps
Niladri Biswas
(Code Project MVP 2012)
|
|
|
|
|
Hi Niladri,
Thank you for your answer.. I want to, can that solution if we want to compare 100 data in table a with 100000 data in table b. so it looks like google search, when program compare 1 data to 1000, the system doesn't compare to all data, but only similar data, and the system give return hit, example: 1 data is suspected similar with 8 data in table b. Can the solution solve the problem niladri?
Thank you,
Regards
|
|
|
|
|
Morning all. I need to select certain data from a table:
PI JobID FormID ShiftID StartEvent SW SG End Event EG EW
3 9249 36208 6241 2010-08-14 10:00:15.610 0 0 2010-08-14 10:14:52.000 143 109
3 9249 36208 6241 2010-08-14 10:14:52.217 109 143 2010-08-14 10:15:04.000 150 109
3 9249 36208 6241 2010-08-14 10:15:04.763 109 150 2010-08-14 10:15:07.000 150 109
3 9249 36208 6241 2010-08-14 10:15:09.820 109 150 2010-08-14 10:29:15.000 150 221
3 9249 36208 6241 2010-08-14 10:29:15.570 221 150 2010-08-14 10:53:09.000 376 300
3 9249 36208 6241 2010-08-14 10:53:09.240 300 376 2010-08-14 11:01:18.000 4294 1824
3 9249 36208 6241 2010-08-14 11:01:18.553 1824 4294 2010-08-14 11:02:06.000 4294 1942
3 9249 36208 6241 2010-08-14 11:02:06.363 1942 4294 2010-08-14 11:02:14.000 4294 1920
Out of this I need to get the start values and the end values, ignoring everything in between, essentially reducing it to this:
PI JobID FormID ShiftID StartEvent SW SG End Event EG EW
3 9249 36208 6241 2010-08-14 10:00:15.610 0 0 2010-08-14 11:02:14.000 4294 1920
Any help will be much appreciated.
-edited to show that min and max values on SW, SG, EG, and EW may not necessarily work
modified 20-Aug-12 12:32pm.
|
|
|
|
|
Try this ...
select PI,JobID,FormID,ShiftID,Min(StartEvent),Max(End Event)
from someTable
group by PI,JobID,FormID,ShiftID
|
|
|
|
|
Thanks for the reply. That works for the times, but doesn't cover SW, SG, EG, and EW.
|
|
|
|
|
Here is a fix up on David's query:
select PI,JobID,FormID,ShiftID,min(SW) as SW,min(SG) as SG,max(EG) as EG,
max(EW) as EW,Min(StartEvent) as StartEvent,Max(EndEvent) as EndEvent
from someTable
group by PI,JobID,FormID,ShiftID
Hope this helps out.
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
Thanks for the reply. That's basically what I have now for my query. Unfortunately min and max only work on the timestamps. I need to select the specific values tied to those timestamps as there might be higher or lower values in the rest of the data. I updated the sample data to reflect that.
|
|
|
|
|
I am suspecting you probably need a select subquery to get the last row that you had updated the sample dataset with, because my query doesn't give you the 1920 in the EW column.
The subquery might be one in which you get the minimum timestamp and the maximum timestamp, then the actual main query pulls the other information out of the subquery. essentially, David's query would be the subquery. Just a thought.
You may want to do this as a stored procedure instead.
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
modified 20-Aug-12 13:10pm.
|
|
|
|
|
Roger. Thanks for all your help. I wanted an challenge, I got one.
|
|
|
|
|
milo-xml wrote: I wanted an challenge, I got one
Yes, you did. I like trying my best to help with a challenging task.
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
You're missing the SW, SG, EW, and EG fields he needed 
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
One of the ways:
select min(dt.[pi]),min(dt.jobid),min(dt.formid),min(dt.shiftid), min(dt.startevent),
(
select top 1 (dt.sw)
from datatable dt
order by dt.endevent
)as sw,
(
select top 1 (dt.sg)
from datatable dt
order by dt.endevent
)as sg,
(
select top 1 (dt.endevent)
from datatable dt
order by dt.endevent desc
) as endevent,
(
select top 1 (dt.eg)
from datatable dt
order by dt.endevent desc
) as eg,
(
select top 1 (dt.ew)
from datatable dt
order by dt.endevent desc
) as ew
from datatable dt
group by dt.[pi],dt.jobid,dt.shiftid
I Love T-SQL
"VB.NET is developed with C#.NET"
If my post helps you kindly save my time by voting my post.
|
|
|
|
|
Try this [not tested but it should work!]
SELECT t1.PI,
t1.JobID,
t1.FormID,
t1.ShiftID,
t2.StartEvent,
t2.SW,
t2.SG,
t3.EndEvent,
t3.EG,
t3.EW
FROM
(
SELECT DISTINCT PI, JobID, FormID, ShiftID
FROM [table]
) t1
CROSS APPLY
(
SELECT TOP 1 StartEvent, SW, SG
FROM [table]
WHERE PI = t1.PI
AND JobID = t1.JobID
AND FormID = t1.FormID
AND ShiftID = t1.ShiftID
ORDER BY StartEvent ASC
) t2
CROSS APPLY
(
SELECT TOP 1 EndEvent, EG, EW
FROM [table]
WHERE PI = t1.PI
AND JobID = t1.JobID
AND FormID = t1.FormID
AND ShiftID = t1.ShiftID
ORDER BY EndEvent DESC
) t3
|
|
|
|
|
This might have worked great if I wasn't working on a SQL2k server...... Thanks for your time.
|
|
|
|
|
|
Preaching to the choir my friend.
|
|
|
|
|
Declare @t table(PI int, JobID int, FormID int, ShiftID int, StartEvent datetime, SW int,SG int, EndEvent datetime,EG int,EW int)
Insert Into @t
Select 3,9249,36208,6241,'2010-08-14 10:00:15.610',0,0,'2010-08-14 10:14:52.000',143,109 Union All
Select 3,9249,36208,6241,'2010-08-14 10:14:52.217',109,143,'2010-08-14 10:15:04.000',150,109 Union All
Select 3,9249,36208,6241,'2010-08-14 10:15:04.763',109,150,'2010-08-14 10:15:07.000',150,109 Union All
Select 3,9249,36208,6241,'2010-08-14 10:15:09.820',109,150,'2010-08-14 10:29:15.000', 150,221 Union All
Select 3,9249,36208,6241,'2010-08-14 10:29:15.570', 221,150,'2010-08-14 10:53:09.000',376,300 Union All
Select 3,9249,36208,6241,'2010-08-14 10:53:09.240',300,376,'2010-08-14 11:01:18.000',4294,1824 Union All
Select 3,9249,36208,6241,'2010-08-14 11:01:18.553',1824,4294 ,'2010-08-14 11:02:06.000',4294,1942 Union All
Select 3,9249,6208,6241,'2010-08-14 11:02:06.363',1942,4294,'2010-08-14 11:02:14.000',4294,1920
Select X.*,Y.EndEvent,Y.EG,Y.EW
From( Select Top 1 PI,JobID,FormID,ShiftID,StartEvent,SW,SG
From @t
Order By StartEvent)X
Join ( Select Top 1 PI,EndEvent,EG,EW
From @t
Order By EndEvent DESC)Y
On X.PI = Y.PI
Result
PI JobID FormID ShiftID StartEvent SW SG EndEvent EG EW
3 9249 36208 6241 2010-08-14 10:00:15.610 0 0 2010-08-14 11:02:14.000 4294 1920
Niladri Biswas
(Code Project MVP 2012)
|
|
|
|
|
Here is one approach. Hope this helps.
CREATE TABLE #Temp
(
ID INT IDENTITY(1,1),
[PI] INT,
JobID INT,
FormID INT,
ShiftID INT,
StartEvent DATETIME,
SW INT,
SG INT,
EndEvent DATETIME,
EG INT,
EW INT
)
INSERT INTO #Temp
SELECT 3, 9249, 36208, 6241, '2010-08-14 10:00:15.610', 0, 0, '2010-08-14 10:14:52.000', 143, 109 UNION
SELECT 3, 9249, 36208, 6241, '2010-08-14 10:14:52.217', 109, 143, '2010-08-14 10:15:04.000', 150, 109 UNION
SELECT 3, 9249, 36208, 6241, '2010-08-14 10:15:04.763', 109, 150, '2010-08-14 10:15:07.000', 150, 109 UNION
SELECT 3, 9249, 36208, 6241, '2010-08-14 10:15:09.820', 109, 150, '2010-08-14 10:29:15.000', 150, 221 UNION
SELECT 3, 9249, 36208, 6241, '2010-08-14 10:29:15.570', 221, 150, '2010-08-14 10:53:09.000', 376, 300 UNION
SELECT 3, 9249, 36208, 6241, '2010-08-14 10:53:09.240', 300, 376, '2010-08-14 11:01:18.000', 4294, 1824 UNION
SELECT 3, 9249, 36208, 6241, '2010-08-14 11:01:18.553', 1824, 4294, '2010-08-14 11:02:06.000', 4294, 1942 UNION
SELECT 3, 9249, 36208, 6241, '2010-08-14 11:02:06.363', 1942, 4294, '2010-08-14 11:02:14.000', 4294, 1920
SELECT T1.*, T2.EndEvent, T2.EG, T2.EW FROM
(
SELECT T.[PI], T.JobID, T.FormID, T.ShiftID, T.StartEvent, T.SW, T.SG FROM #Temp T
INNER JOIN
(
SELECT MIN(ID) AS MinID, [PI], JobID, FormID, ShiftID FROM #Temp
GROUP BY [PI], JobID, FormID, ShiftID
) X ON T.ID = X.MinID
) T1
INNER JOIN
(
SELECT T.[PI], T.JobID, T.FormID, T.ShiftID, T.EndEvent, T.EG, T.EW FROM #Temp T
INNER JOIN
(
SELECT MAX(ID) AS MaxID, [PI], JobID, FormID, ShiftID FROM #Temp
GROUP BY [PI], JobID, FormID, ShiftID
) X ON T.ID = X.MaxID
) T2 ON T1.[PI] = T2.[PI] AND T1.JobID = T2.JobID AND T1.FormID = T2.FormID AND T1.ShiftID = T2.ShiftID
DROP TABLE #Temp
|
|
|
|
|
Thanks all for your help. Took a little from all the answers. I got the data in the format I want, but not sure it's the most efficient way to do it. First I created a view that had my job data in it(I wasn't sure how to it another way...)
SELECT dbo.tblFormEvent.PressIndex, dbo.tblFormEvent.JobID, dbo.tblFormEvent.FormID, dbo.tblFormEvent.ShiftID, dbo.tblShift.ClockNumber, MIN(dbo.tblFormEvent.StartEvent) AS StartEvent, MAX(dbo.tblFormEvent.EndEvent) AS EndEvent
FROM dbo.tblFormEvent
INNER JOIN dbo.tblShift ON dbo.tblFormEvent.PressIndex = dbo.tblShift.PressIndex AND dbo.tblFormEvent.ShiftID = dbo.tblShift.ShiftID
GROUP BY dbo.tblFormEvent.PressIndex, dbo.tblFormEvent.JobID, dbo.tblFormEvent.FormID, dbo.tblFormEvent.ShiftID, dbo.tblShift.ClockNumber
(Forgot I needed the other data from another table as well...)
Then I used that to query the count values from the main table using joins.
SELECT dbo.View_1.PressIndex, dbo.View_1.JobID, dbo.View_1.FormID, dbo.View_1.ShiftID, dbo.View_1.ClockNumber, dbo.View_1.StartEvent, tblFormEvent_1.StartWaste, tblFormEvent_1.StartGross, dbo.View_1.EndEvent, dbo.tblFormEvent.EndGross, dbo.tblFormEvent.EndWaste
FROM dbo.View_1 LEFT OUTER JOIN dbo.tblFormEvent ON dbo.View_1.PressIndex = dbo.tblFormEvent.PressIndex AND dbo.View_1.JobID = dbo.tblFormEvent.JobID AND dbo.View_1.FormID = dbo.tblFormEvent.FormID AND dbo.View_1.ShiftID = dbo.tblFormEvent.ShiftID AND dbo.View_1.EndEvent = dbo.tblFormEvent.EndEvent LEFT OUTER JOIN dbo.tblFormEvent AS tblFormEvent_1 ON dbo.View_1.StartEvent = tblFormEvent_1.StartEvent AND dbo.View_1.PressIndex = tblFormEvent_1.PressIndex AND dbo.View_1.JobID = tblFormEvent_1.JobID AND dbo.View_1.FormID = tblFormEvent_1.FormID AND dbo.View_1.ShiftID = tblFormEvent_1.ShiftID
ORDER BY dbo.View_1.PressIndex, dbo.View_1.StartEvent
Works, but I have serious doubts about the efficiency. It'll give me something to improve.
|
|
|
|
|
Not really knowing the structure of your keys - something like this should work.
SELECT
s.PI,
s.JobID,
s.FormID,
s.ShiftID,
s.StartEvent,
s.SW,
s.SG,
e.EndEvent,
e.EG,
e.EW
FROM
[BaseTable] s
INNER JOIN
[BaseTable] e
ON (s.PI = e.PI AND
s.JobId = e.JobId AND
s.FormId = e.FormId AND
s.ShiftId = e.ShiftId AND
e.EndEvent = (SELECT
MAX(EndEvent)
FROM
[BaseTable]
WHERE
s.PI = PI AND
s.JobId = JobId AND
s.FormId = FormId AND
s.ShiftId = ShiftId))
WHERE
s.StartEvent = (SELECT
MIN(StartEvent)
FROM
[BaseTable]
WHERE
s.PI = PI AND
s.JobId = JobId AND
s.FormId = FormId AND
s.ShiftId = ShiftId)
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
