Possible phone billing or clockin/clockout query.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Possible phone billing or clockin/clockout query.

Author

Topic

Sitka
Aged Yak Warrior

571 Posts

Posted - 2011-03-15 : 10:00:52

[code]
--I think this query is like what would be used in a phone call or other service billing situation.
--I've seen this type of record keeping in some clockin clockout applications as well.
--The main difficulty is that as opposed to the more usual record which would have a starttime and an endtime
--for each row, this data utilizes a sequence of stops and starts events which aren't necessarily paired.

--this table represents a compilaton of various logs from machines
--throughout the enterprise, for this example only one machine is included

CREATE TABLE machines
(
machine nchar(10) NOT NULL,
datexxx datetime NOT NULL,
maincommand nchar(10) NOT NULL,
secondarycommand nvarchar(50) NULL,
idxxx int IDENTITY(1,1) NOT NULL,
CONSTRAINT PK_machines PRIMARY KEY CLUSTERED
(
idxxx ASC
)
)

SET IDENTITY_INSERT machines ON

INSERT INTO machines (machine, datexxx, maincommand, secondarycommand,idxxx) VALUES ('dia01','2011-03-13 00:00:01.000','Start','sadsda',1)
INSERT INTO machines (machine, datexxx, maincommand, secondarycommand,idxxx) VALUES ('dia01','2011-03-13 03:12:15.000','Stop','wertty',2)
INSERT INTO machines (machine, datexxx, maincommand, secondarycommand,idxxx) VALUES ('dia01','2011-03-13 04:26:39.000','Start','mjuidfhelwst dsd 000 nhdy',3)
INSERT INTO machines (machine, datexxx, maincommand, secondarycommand,idxxx) VALUES ('dia01','2011-03-13 07:08:51.500','Stop','jo is cool',25)
INSERT INTO machines (machine, datexxx, maincommand, secondarycommand,idxxx) VALUES ('dia01','2011-03-13 12:21:20.000','Start','X$$$$HEX)*(',5)
INSERT INTO machines (machine, datexxx, maincommand, secondarycommand,idxxx) VALUES ('dia01','2011-03-13 13:40:11.000','Stop','st',6)
INSERT INTO machines (machine, datexxx, maincommand, secondarycommand,idxxx) VALUES ('dia01','2011-03-13 19:56:28.250','Start','123456',7)
INSERT INTO machines (machine, datexxx, maincommand, secondarycommand,idxxx) VALUES ('dia01','2011-03-13 23:17:39.000','Start','NULL',8)
INSERT INTO machines (machine, datexxx, maincommand, secondarycommand,idxxx) VALUES ('dia01','2011-03-13 23:33:11.000','Stop','jebesus',9)
INSERT INTO machines (machine, datexxx, maincommand, secondarycommand,idxxx) VALUES ('dia01','2011-03-14 01:26:39.000','Stop','NULL',10)
INSERT INTO machines (machine, datexxx, maincommand, secondarycommand,idxxx) VALUES ('dia01','2011-03-14 01:26:42.000','Stop','90',11)
INSERT INTO machines (machine, datexxx, maincommand, secondarycommand,idxxx) VALUES ('dia01','2011-03-14 12:39:00.000','Start','NULL',12)
INSERT INTO machines (machine, datexxx, maincommand, secondarycommand,idxxx) VALUES ('dia01','2011-03-14 14:02:00.000','Stop','NULL',111)
INSERT INTO machines (machine, datexxx, maincommand, secondarycommand,idxxx) VALUES ('dia01','2011-03-14 17:26:39.000','Start','a loop 900',14)
INSERT INTO machines (machine, datexxx, maincommand, secondarycommand,idxxx) VALUES ('dia01','2011-03-14 19:07:01.000','Stop','NULL',15)

SET IDENTITY_INSERT machines OFF

--TRUNCATE TABLE machines

SELECT machine
,datexxx
,maincommand
,secondarycommand
,idxxx
FROM machines
ORDER BY datexxx

--The above information would have an intermediate result shown below but at Time0 there is no
--result for the first row, That is no counting duration.
--The numeric value is seconds between starts and stops and stops and stops and starts and starts
--The change in a maincommand defines the duration, not the value of the maincommand and each row
--needs to reference the previous state of the machine
--Up is Uptime
--Dn is downtime
/*
11534 Up
4464 Dn
9732.5 Up
18748.5 Dn
4731 Up
22577.25 Dn
12070.75 Up
931.9999997 Up
6808 Dn
3.000000073 Dn
40338 Dn
4980 Up
12279 Dn
6022 Up
*/

--the final desired result is
--secondsofUP secondsofDN
--50002.25 105217.75

[/code]

The exact Start and Stop logic is very simple. Start followed by Stop would be a datediff in seconds between the two datexxx values of the two rows.
Start followed by one or more Starts means there is no change, the machine continues to run. Same with multiple Stops.

The sequence order is datexxx based, it is the change in maincommand that flips the state of the machine from Started and Stopped.

"it's definitely useless and maybe harmful".

Sitka
Aged Yak Warrior

571 Posts

Posted - 2011-03-15 : 11:47:30

I think this gets rid of the nonessential rows. What is a nonessential row? One where the state of the machine does not change because the maincommand is not different than the previous maincommand.

I think just repeat this conditional concept, WHERE D.rank = C.rank - 1 to widen the rows to contain a start AND a stop datexxx then it becomes a typical datediff aggregate query.


SELECT E.rankleft,E.machineleft,E.datexxxleft,E.maincommandleft
FROM
(
	SELECT
		C.rank as rankleft,C.machine as machineleft,C.datexxx as datexxxleft,C.maincommand as maincommandleft
		,D.rank as rankright,D.machine as machineright,D.datexxx as datexxxright,D.maincommand as maincommandright
	FROM
	(
		SELECT rank() OVER (ORDER BY A.datexxx) as rank, A.[machine]
		,A.datexxx
		,A.maincommand
		FROM machines A
	)
	C CROSS JOIN
	(
		SELECT rank() OVER (ORDER BY A.datexxx) as rank, A.machine
		,A.datexxx
		,A.maincommand
		FROM machines A
	)
	D
	WHERE  D.rank = C.rank - 1
	AND C.maincommand <> D.maincommand
)
E

UNION

SELECT TOP 1 rank() OVER (ORDER BY F.datexxx) as rank, F.machine
,F.datexxx
,F.maincommand	      
FROM machines F

"it's definitely useless and maybe harmful".

Sitka
Aged Yak Warrior

571 Posts

Posted - 2011-03-16 : 10:26:24

This at least proves I can get most of the requirements I've been given so far.
It returns the running time and notrunning time in seconds for a machine for one day.
I don't know yet how the first boundary conditions are exactly going to be met. The data will propabably end up being continuous day to day so I will probably have to look back in time to resolve the beginning state of the machine.





SELECT P.machine, SUM( DATEDIFF(ss,P.leftdate, P.rightdate) ) as duration, P.runstate
--SELECT P.machine, DATEDIFF(ss,P.leftdate, P.rightdate) as duration, P.runstate
FROM
(
	SELECT W.rowxxx, W.machineleft as machine, W.datexxxleft as leftdate,
	--COALESCE(Z.datexxxleft, CAST(convert(varchar, getdate(), 112)+' 23:59:59' as datetime)) as rightdate,
	COALESCE(Z.datexxxleft, '2011-03-14 19:07:00.000') as rightdate,

	CASE W.maincommandleft WHEN 'Start' THEN 'Up'
	ELSE 'Dn'
	END as runstate
	FROM
	(
		SELECT ROW_NUMBER() OVER (ORDER BY X.datexxxleft) as rowxxx, X.machineleft, X.datexxxleft, X.maincommandleft
		FROM
		(
			SELECT E.rankleft,E.machineleft,E.datexxxleft,E.maincommandleft
			FROM
			(
				SELECT
					C.rank as rankleft,C.machine as machineleft,C.datexxx as datexxxleft,C.maincommand as maincommandleft
					,D.rank as rankright,D.machine as machineright,D.datexxx as datexxxright,D.maincommand as maincommandright
				FROM
				(
					SELECT rank() OVER (ORDER BY A.datexxx) as rank, A.[machine]
					,A.datexxx
					,A.maincommand
					FROM machines A
				)
				C CROSS JOIN
				(
					SELECT rank() OVER (ORDER BY A.datexxx) as rank, A.machine
					,A.datexxx
					,A.maincommand
					FROM machines A
				)
				D
				WHERE  D.rank = C.rank - 1
				AND D.maincommand <> C.maincommand
			)
			E

			UNION

			SELECT TOP 1 rank() OVER (ORDER BY F.datexxx) as rank, F.machine
			,F.datexxx
			,F.maincommand	      
			FROM machines F
		)
		X
	)
	W
	LEFT JOIN
	(
		SELECT ROW_NUMBER() OVER (ORDER BY Y.datexxxleft) as rowxxx, Y.machineleft, Y.datexxxleft
		FROM	
		(
			SELECT E.rankleft,E.machineleft,E.datexxxleft,E.maincommandleft
			FROM
			(
				SELECT
					C.rank as rankleft,C.machine as machineleft,C.datexxx as datexxxleft,C.maincommand as maincommandleft
					,D.rank as rankright,D.machine as machineright,D.datexxx as datexxxright,D.maincommand as maincommandright
				FROM
				(
					SELECT rank() OVER (ORDER BY A.datexxx) as rank, A.[machine]
					,A.datexxx
					,A.maincommand
					FROM machines A
				)
				C CROSS JOIN
				(
					SELECT rank() OVER (ORDER BY A.datexxx) as rank, A.machine
					,A.datexxx
					,A.maincommand
					FROM machines A
				)
				D
				WHERE  D.rank = C.rank - 1
				AND D.maincommand <> C.maincommand
			)
			E

			UNION

			SELECT TOP 1 rank() OVER (ORDER BY F.datexxx) as rank, F.machine
			,F.datexxx
			,F.maincommand	      
			FROM machines F
		)
		Y
	)
	Z
	ON W.rowxxx = Z.rowxxx - 1
)
P
GROUP BY P.machine,P.runstate

"it's definitely useless and maybe harmful".

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources