|
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)
|
|
|
|
|
hi friends i have small doubt in ssis plz tell me how to solve this task.
suppose i have a table that table contains 50 records .that records like
deptno 10 contains 10 records and deptno 20 contains 11 records and deptno 30 contains 9 records and deptno 40 contain 5 records and deptno 50 contains 15 recods so totaly a table contain 50 reords.
Based on this table we want load this reords in one execel sheet in execel sheets we want load records seperately each sheet like sheet1 contains....>deptno10 related records and shhet2 ....> deptno 20 records and sheet3 .....> deptno30 and sheet4 ....> deptno40 records and sheet5 ....>deptno 50 related records.
and iam trying this task like in control flow level i taken task foreach loop container and dft task and dft level i taken oledb source and condition split and finaly i load execel shhet but it not load each shheet.
plz tell me how to solve this one
|
|
|
|
|
hey members i need some help. i have made this console base app which connects to a database and retrieves the data from two column. same it is like a login check form. but can i convert this into a windows form application which consist of two text boxes and a login button
need some help
thanks in advance
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.Sql;
using System.Data.SqlClient;
namespace c_database
{
class program
{
static void main(string[] aqu)
{
string connectionstring =@"Data Source=.\SQLEXPRESS;AttachDbFilename=c:\documents and settings\aquib\my documents\visual studio 2010\Projects\WindowsFormsApplication2\WindowsFormsApplication2\Database1.mdf;Integrated Security=True;User Instance=True";
SqlConnection connection= new SqlConnection(connectionstring);
try
{
connection.Open();
Console.WriteLine("connection open");
Console.WriteLine("");
}
catch (Exception e)
{
Console.WriteLine(e);
Console.Read();
}
Console.Write("username>");
string username =Console.ReadLine();
Console.Write("Password>");
string password =Console.Read();
SqlCommand command = new SqlCommand("SELECT * FROM [USERS] WHERE Username='" + username + "' AND Password ='" + password + "'" , connection);
SqlDataReader reader = null;
reader = command.ExecuteReader();
while (reader.Read())
{
Console.WriteLine("welcome" + reader["username"].ToString());
Console.Read();
}
Console.WriteLine("user" +username + "does Not exist" );
goto
}
}
}
|
|
|
|
|
qureshiaquib wrote: need some help
With what, exactly?
Add a reference to System.Windows.Forms, add a Form, run it in the main-method. And please, do use parameterized queries[^]. Without it, the entire idea of "security" becomes a bit useless. Google for "Little Bobby Tables" if you want to know why.
Bastard Programmer from Hell 
if you can't read my code, try converting it here[^]
|
|
|
|
|
|
As Eddy has mentioned, build a form with the text boxes, and reuse the appropriate code you have in the console app in the windows form app.
I understand you are learning right now, and the following is dangerous as it allows for SQL injection attacks:
SqlCommand command = new SqlCommand("SELECT * FROM [USERS] WHERE Username='" + username + "' AND Password ='" + password + "'" , connection);
You do not want to use string concatenation for building a query, but use parameterized queries instead. Take a look at SQL Injection Attacks and Some Tips on How to Prevent Them[^] to learn how to prevent them.
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
