quote:

---

Originally posted by lionofdezert

Please provide table structure for Eventlog . Complete columns and their datatype is requried.

--------------------------
http://connectsql.blogspot.com/

---

quote:

---

Originally posted by lionofdezert

SELECT CAST(DAY(PanelDate) AS VARCHAR) + '/'
+ CAST(month(PanelDate) AS VARCHAR) + '-'
+ CAST(Year(PanelDate) AS VARCHAR) AS dato,
sum(DATEDIFF(HH, e.PanelDate, b.PanelDate)) AS Total,
CASE WHEN sum(DATEDIFF(HH, e.PanelDate, b.PanelDate)) > 8
THEN sum(DATEDIFF(HH, e.PanelDate, b.PanelDate)) - 8
ELSE NULL
END AS Overtid,
CASE WHEN DATEPART(dw,
CAST(CAST(DAY(PanelDate) AS VARCHAR) + '/'
+ CAST(month(PanelDate) AS VARCHAR) + '-'
+ CAST(Year(PanelDate) AS VARCHAR) AS DATETIME)) IN (
6, 7 ) THEN sum(DATEDIFF(HH, e.PanelDate, b.PanelDate))
ELSE NULL
END AS Weekend
FROM Eventlog AS b,
Eventlog AS e
WHERE ( ( e.Paneldate ) = ( SELECT min(Paneldate)
FROM Eventlog
WHERE ID1Data = 'Læser 2 (6) T-Reg Gået'
OR ID1Data = 'Læser 2 Tinghøj T-Reg Gået'
OR ID1Data = 'Læser 2 Haugaard T-Reg Gået'
AND PanelDate > b.PanelDate )
AND ( ( b.ID1Data ) = 'Læser 1 (5) T-Reg Kommet'
OR ( b.ID1Data ) = 'Læser 1 Tinghøj T-Reg Kommet'
OR ( b.ID1Data ) = 'Læser 1 Haugaard T-Reg Kommet'
)
)
GROUP BY CAST(DAY(PanelDate) AS VARCHAR) + '/'
+ CAST(month(PanelDate) AS VARCHAR) + '-'
+ CAST(Year(PanelDate) AS VARCHAR)

--------------------------
http://connectsql.blogspot.com/

---

quote:

---

Originally posted by xloafery

replace PanelDate with b.Paneldate to specify which table to get the column from. Ambiguous Column means there's more than one with that name.

---

WITH Totals
AS
(
	SELECT DATEADD(d, DATEDIFF(d, 0, E.PanelDate), 0) AS dato
		,SUM(DATEDIFF(s, E.PanelDate, D.PanelDate)) AS TotalSeconds
	FROM Eventlog E
		CROSS APPLY
		(
			SELECT MIN(E1.PanelDate) AS PanelDate
			FROM Eventlog E1
			WHERE E1.PanelDate > E.PanelDate
				AND E1.ID1Data IN
				(
					'Læser 2 (6) T-Reg Gået'
					,'Læser 2 Tinghøj T-Reg Gået'
					,'Læser 2 Haugaard T-Reg Gået'
				)
		) D
	WHERE E.ID1Data IN
	(
		'Læser 1 (5) T-Reg Kommet'
		,'Læser 1 Tinghøj T-Reg Kommet'
		,'Læser 1 Haugaard T-Reg Kommet'
	)
	GROUP BY DATEADD(d, DATEDIFF(d, 0, E.PanelDate), 0)
)
,Weekends(WE)
AS
(
	SELECT DATEPART(dw,'20120908')
	UNION ALL
	SELECT DATEPART(dw,'20120909')
)
,Unformated
AS
(
	SELECT dato, TotalSeconds
		,CASE
			WHEN TotalSeconds > (8 * 60 * 60)
			THEN TotalSeconds - (8 * 60 * 60)
		END AS OvertidSeconds
		,CASE
			WHEN DATEPART(dw, dato) IN (SELECT WE FROM Weekends)
			THEN TotalSeconds
		END AS WeekendSeconds
	FROM Totals
)
SELECT *
FROM Unformated
--SELECT STUFF(CONVERT(char(10), dato, 103), 6, 1, '-') AS dato
--	,RIGHT('0' + CAST(TotalSeconds / (60 * 60) AS varchar(2)), 2)
--		+ ':'
--		+ RIGHT('0' + CAST(TotalSeconds / 60 - (((TotalSeconds / (60 * 60)) * 60) AS varchar(2)), 2)
--		+ ':'
--		+ RIGHT('0' + CAST(TotalSeconds % 60 AS varchar(2)), 2) AS Total
--	,RIGHT('0' + CAST(OvertidSeconds / (60 * 60) AS varchar(2)), 2)
--		+ ':'
--		+ RIGHT('0' + CAST(OvertidSeconds / 60 - (((OvertidSeconds / (60 * 60)) * 60) AS varchar(2)), 2)
--		+ ':'
--		+ RIGHT('0' + CAST(OvertidSeconds % 60 AS varchar(2)), 2) AS Overtid
--	,RIGHT('0' + CAST(WeekendSeconds / (60 * 60) AS varchar(2)), 2)
--		+ ':'
--		+ RIGHT('0' + CAST(WeekendSeconds / 60 - (((WeekendSeconds / (60 * 60)) * 60) AS varchar(2)), 2)
--		+ ':'
--		+ RIGHT('0' + CAST(WeekendSeconds % 60 AS varchar(2)), 2) AS Weekend
--FROM Unformated

quote:

---

Originally posted by Ifor

You will get better answers if you provide example data and results.

You should really do the formating in your application.

Assuming 2008 or above:

---

WITH Totals AS (SELECT DATEADD(d, DATEDIFF(d, 0, E.PanelDate), 0) AS dato, SUM(DATEDIFF(s, E.PanelDate, D .PanelDate)) AS TotalSeconds
FROM Eventlog E CROSS APPLY
(SELECT MIN(E1.PanelDate) AS PanelDate FROM Eventlog E1 WHERE E1.PanelDate > E.PanelDate AND E1.ID1Data IN ('Læser 2 (6) T-Reg Gået', 'Læser 2 Tinghøj T-Reg Gået', 
'Læser 2 Haugaard T-Reg Gået')) D
WHERE E.ID1Data IN ('Læser 1 (5) T-Reg Kommet', 'Læser 1 Tinghøj T-Reg Kommet', 'Læser 1 Haugaard T-Reg Kommet') AND ID2Data=@IMP
GROUP BY DATEADD(d, DATEDIFF(d, 0, E.PanelDate), 0)), Weekends(WE) AS
(SELECT DATEPART(dw, '20120908')
UNION ALL
SELECT DATEPART(dw, '20120909')), Unformated AS
(SELECT dato,CASE WHEN DATEPART(dw, dato) IN (SELECT WE FROM Weekends) THEN NULL ELSE (CASE WHEN TotalSeconds>(8*60*60) THEN (8*60*60) ELSE TotalSeconds END) END AS  TotalSeconds,
CASE WHEN DATEPART(dw, dato) IN (SELECT WE FROM Weekends) THEN TotalSeconds END AS WeekendSeconds,
CASE WHEN DATEPART(dw, dato) IN (SELECT WE FROM Weekends) THEN NULL ELSE (CASE WHEN TotalSeconds > (8 * 60 * 60) THEN TotalSeconds - (8 * 60 * 60) END) END AS OvertidSeconds
FROM Totals)
SELECT (SELECT CONVERT(VARCHAR(10), Dato, 105) AS [DD/MM/YYYY]) AS Dato,
       (SELECT CONVERT(varchar(6), TotalSeconds / 3600) + ':' + RIGHT('0' + CONVERT(varchar(2), (TotalSeconds % 3600) / 60), 2) 
+ ':' + RIGHT('0' + CONVERT(varchar(2), TotalSeconds % 60), 2)) AS Total,
       (SELECT CONVERT(varchar(6), OvertidSeconds / 3600) + ':' + RIGHT('0' + CONVERT(varchar(2), (OvertidSeconds % 3600) / 60), 2) 
+ ':' + RIGHT('0' + CONVERT(varchar(2), OvertidSeconds % 60), 2)) AS Overtid,
       (SELECT CONVERT(varchar(6), WeekendSeconds / 3600) + ':' + RIGHT('0' + CONVERT(varchar(2), (WeekendSeconds % 3600) / 60), 2) 
+ ':' + RIGHT('0' + CONVERT(varchar(2), WeekendSeconds % 60), 2)) AS Weekend
FROM Unformated
WHERE Dato >= @Start AND Dato < @Slut;

quote:

---

Originally posted by Ifor

[code]
-- *** Test Data ***
CREATE TABLE #EventLog
(
PanelDate datetime NOT NULL
,PanelData nvarchar(50) NOT NULL
,ID2Data nvarchar(50) NOT NULL
,ID1Data nvarchar(50) NOT NULL

---