Database

SELECT main.EmpId, main.Date, 
(SELECT i.EventTime FROM Attendance i 
WHERE i.EmpID = main.EmpId AND 
i.Date = main.Date AND i.Login_Type = 'I') AS ShiftInTime, 
(SELECT o.EventTime FROM Attendance o
WHERE o.EmpID = main.EmpID AND
o.Date = main.Date AND o.Login_Type = 'O') AS ShiftOutTime
FROM Attendance main

SELECT main.UserName, <br />
(SELECT i.EventTime FROM History i  <br />
WHERE i.FuncCode="0") AS InTime, <br />
(SELECT o.EventTime FROM History o  <br />
WHERE o.FuncCode="10") AS  OutTime <br />
FROM  History main

SELECT     
  h.EmpID, 
  h.[Date], 
  h.EventTime AS ShiftInTime, 
  h2.EventTime AS ShiftOutTime
FROM 
  History h 
LEFT OUTER JOIN
  History h2 
ON 
  h.EmpID = h2.EmpID AND 
  h.[Date] = h2.[Date] AND 
  h2.EventCode = 'O'
WHERE
  h.EventCode = 'I'

SELECT h.UserName, h.EventDate, h.EventTime AS InTime, h2.EventTime AS OutTime, h.Dept<br />
FROM History AS h LEFT JOIN History AS h2 ON (h.UserName = h2.UserName) AND (h.EventDate = h2.EventDate)<br />
WHERE (((h.EventDate)=#2/24/2006#) AND ([h.FuncCode]="0") AND ([h2.FuncCode]="10") AND ((h.Dept)="Engg"));

tonyalex wrote:

please help me getting a portable data access layer code which independent of language

tonyalex wrote:

What i meant is the DAL Source Code that is given by the microsoft for all its .net language.........

tonyalex wrote:

yes, microsoft is providing code for the data access layer for the our custom application,think some thing like SQLHELPER

indian143 wrote:

Can anybody please tell me in SQL Server how should I get the records with their row numbers in a table.

<br />
CREATE TRIGGER dbo.trg_Count <br />
   ON  dbo._TEST<br />
   AFTER INSERT, DELETE<br />
AS <br />
BEGIN<br />
	<br />
	SET NOCOUNT ON;<br />
	DECLARE @PKEY UNIQUEIDENTIFIER<br />
	DECLARE @I BIGINT<br />
	<br />
	IF ((SELECT trigger_nestlevel()) = 1)<br />
	BEGIN<br />
		DECLARE TRG_TEST_CUR CURSOR FOR<br />
		SELECT [GUID] FROM _TEST<br />
<br />
		OPEN TRG_TEST_CUR<br />
		FETCH NEXT FROM TRG_TEST_CUR<br />
		INTO @PKEY<br />
		<br />
		SET @I = 1<br />
<br />
		WHILE @@FETCH_STATUS = 0<br />
		BEGIN<br />
			UPDATE _TEST SET ID = @I WHERE [GUID] = @PKEY<br />
			SET @I = @I + 1<br />
			FETCH NEXT FROM TRG_TEST_CUR<br />
			INTO @PKEY<br />
		END<br />
		CLOSE TRG_TEST_CUR<br />
		DEALLOCATE TRG_TEST_CUR<br />
	END<br />
END<br />
GO<br />