|
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[^]
|
|
|
|
|
|
qureshiaquib wrote: what is meysam post?
Not a what, but a "whom". I was referring to a good answer to your question that has been sent to you by someone with that name[^].
Did you succeed in adding a reference to the project yet?
Bastard Programmer from Hell
if you can't read my code, try converting it here[^]
|
|
|
|
|
As mentioned -- definitely never concatenate your SQL statements -- use parameters.
Here's a link to another excellent article here that bears on your situation:
Password Storage: How to do it.[^]
I'll also mention that you should use a layered approach:
User Interface Layer -- You have the console app, you can write a WinForms app that uses the same layers beneath here.
Business Logic Layer -- encapsulates the logic of validating the login details (and other things) (SQL goes here).
Data Access Layer -- encapsulates the details of connecting to and interacting with the database.
Try it, learn it, live it -- you'll be way ahead of your classmates, and you'll become a chick magnet  .
|
|
|
|
|
Excellent suggestion on the layers. Griff's article I second as well.
PIEBALDconsult wrote: be way ahead of your classmates, and you'll become a chick magnet
 That is signature material right there
"Any sort of work in VB6 is bound to provide several WTF moments." - Christian Graus
|
|
|
|
|
First do Right-Click on Project > References and add the Windows.Forms.dll to the project.
then you need to add the namespaces to your class.
After that right click on Project and select Properties; From "Application" tab set output type to "Windows Application".
Good Luck
Meysam
|
|
|
|
|
We have a customer running a new Windows 7/64 system.
Our 32 bit VB.NET 2008 application uses the MyODBC connector - DSNLESS.
However, for 1 particular Windows 7/64 customer only that we can see no matter which MyODBC connector we try we always get
-2147467259 Hex(80004005) [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified
We have tried 6 different versions. Both the 32 bit and 64 bits versions of 3.51, 5.1.11 and 5.1.8 (I believe). It always fails on her machine and never fails anywhere else. We have tried it on 3 different systems here and it always works.
Any ideas?
Thanks
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
