|
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
|
|
|
|
|
|
qureshiaquib wrote: Can you provide me an xample where i can understand the whole scenario
Nope. I am a college professor and it would be against my principles to just hand over a full-blown example, you would not learn much from it.
1. Design your form with your appropriate labels and text boxes (extra credit if you make the password text box **** out the password as it is typed in ), and a log in button.
2. Recycle/reuse your database connection code in which you can run the query after clicking the log in button.
3. If things don't work out well the first time, experiment around with it and use some creativity. You can do it 
Also, you do not want to store passwords for the user in plain text inside the database. You might want to study up on cryptographic hash functions as well. I am leaving this for you to research and enrich your learning with.
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
qureshiaquib wrote: i am a student.learning programming in university
..as I'm reading it, you are doing the learning "here". Welcome to CodeProject University I guess ![Badger | [badger,badger,badger,badger...]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/badger.gif)
When is the assignment due? Any chance we could sneak in a short piece (using the CPVanity class) to deny users with a negative reputation access for the first two tries?
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
|
Read Meysams post, has given you a description of what needs to be done.
..and you should be able to do this within a week, it's not much code. Panicking won't help either, it's more a matter of sitting down, reading the post and Googling a lot 
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
