Please Help Me on this 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)

Please Help Me on this QUERY....

Author

Topic

bny82
Starting Member

3 Posts

Posted - 2012-05-14 : 00:42:40

Guys,help me out,for this query...

I have this table

NoTrans-----ID------------Datetimelog-------------------Type
1-----------001-----------01/01/2012-08:00:00-----------IN
2-----------001-----------01/01/2012-09:00:00-----------IN
3-----------001-----------01/01/2012-17:00:00-----------OUT
4-----------001-----------02/01/2012-08:00:00-----------IN
5-----------001-----------02/01/2012-17:00:00-----------OUT
6-----------001-----------02/01/2012-19:00:00-----------OUT
7-----------001-----------02/01/2012-23:00:00-----------IN
8-----------001-----------03/01/2012-04:00:00-----------OUT
9-----------001-----------03/01/2012-20.00:00-----------IN
10----------001-----------04/01/2012-15:00:00-----------IN
11----------001-----------05/01/2012-20:00:00-----------OUT

i-want-to-become-like-this-on-result.

ID------------DATE-IN-----------------------DATE-OUT---------------------------------------VARIANT
001-----------01/01/2012-08:00:00-----------01/01/2012-17:00:00---------------------------------8
001-----------02/01/2012-08:00:00-----------02/01/2012-19:00:00-(The-Longest-Periode)-----------10
001-----------02/01/2012-23:00:00-----------03/01/2012-04.00.00---------------------------------4
001-----------03/01/2012-20:00:00-----------NULL------------------------------------------------0
001-----------04/01/2012-15:00:00-----------NULL------------------------------------------------0
001-----------NULL--------------------------05/01/2012-20:00:00---------------------------------0

Any kind of help would be appreciate...

Thanks & Regards,
bny17

bny82
Starting Member

3 Posts

Posted - 2012-05-14 : 01:22:10

Please...some one help me out...anyone....

Thanks & Regards,
bny17

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2012-05-14 : 22:09:58

Explain buz rule how to get the expected output, please.

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-15 : 02:56:26

I wasn't able to work out a business logic according to your requirement. If you can tell us, according to what Business Logic your data is transformed into your Expected Result Set, then perhaps we could try it.

But till then if it is a one-time requirement(may be for generating a one-time report) then perhaps you can use the following workaround:


--Creating Table

Create Table Ex
(NoTrans int,
 ID varchar(5),
 Datetimelog DateTime,
 Type varchar(5) )


--Inserting Sample Data
 
Insert Into Ex
Select 1, 001, '01/01/2012 08:00:00', 'IN'
Union ALL
Select 2, 001, '01/01/2012 09:00:00', 'IN'
Union ALL
Select 3, 001, '01/01/2012 17:00:00', 'OUT'
Union ALL
Select 4, 001, '02/01/2012 08:00:00', 'IN'
Union ALL
Select 5, 001, '02/01/2012 17:00:00', 'OUT'
Union ALL
Select 6, 001, '02/01/2012 19:00:00', 'OUT'
Union ALL
Select 7, 001, '02/01/2012 23:00:00', 'IN'
Union ALL
Select 8, 001, '03/01/2012 04:00:00', 'OUT'
Union ALL
Select 9, 001, '03/01/2012 20:00:00', 'IN'
Union ALL
Select 10, 001, '04/01/2012 15:00:00', 'IN'
Union ALL
Select 11, 001, '05/01/2012 20:00:00', 'OUT'


--Query For Your Requirement

;With CTE
As
(Select c.ID, c.DateIN ,d.DateOUT, ROW_NUMBER() Over (Order By (Select NULL)) As rownum From
	   (Select a.*
		From (Select ID,
			        (Case When TYPE = 'IN' Then Datetimelog Else '' End) As DateIN
		      From Ex) As a
	    ) As c
Full JOIN
	   (Select b.*
	    From (Select ID,
			        (Case When TYPE = 'OUT' Then Datetimelog Else '' End) As DateOUT
		      From Ex) As b
	    ) As d
ON c.ID = d.ID)
Select ID,
	  (Case When DateIN = '19000101 00:00:00.000' Then NULL Else DateIN End) As DateIN, 
	  (Case When DateOUT = '19000101 00:00:00.000' Then NULL Else DateOUT End) As DateOUT
From CTE
Where rownum IN (3,39,74,89,100,33)
Order By DateIN

OR maybe some of the Pros here might come up with something.

N 28° 33' 11.93148"
E 77° 14' 33.66384"

vinu.vijayan
Posting Yak Master

227 Posts

Posted - 2012-05-15 : 05:17:40

Sorry, I forgot about Variant in the last attempt. Here is the edited Version:


--Creating Table

Create Table Ex
(NoTrans int,
 ID varchar(5),
 Datetimelog DateTime,
 Type varchar(5) )


--Inserting Sample Data
 
Insert Into Ex
Select 1, 001, '01/01/2012 08:00:00', 'IN'
Union ALL
Select 2, 001, '01/01/2012 09:00:00', 'IN'
Union ALL
Select 3, 001, '01/01/2012 17:00:00', 'OUT'
Union ALL
Select 4, 001, '02/01/2012 08:00:00', 'IN'
Union ALL
Select 5, 001, '02/01/2012 17:00:00', 'OUT'
Union ALL
Select 6, 001, '02/01/2012 19:00:00', 'OUT'
Union ALL
Select 7, 001, '02/01/2012 23:00:00', 'IN'
Union ALL
Select 8, 001, '03/01/2012 04:00:00', 'OUT'
Union ALL
Select 9, 001, '03/01/2012 20:00:00', 'IN'
Union ALL
Select 10, 001, '04/01/2012 15:00:00', 'IN'
Union ALL
Select 11, 001, '05/01/2012 20:00:00', 'OUT'


--Query For Your Requirement

;With CTE
As
(Select c.ID, c.DateIN ,d.DateOUT, ROW_NUMBER() Over (Order By (Select NULL)) As rownum From
	   (Select a.*
		From (Select ID,
			        (Case When TYPE = 'IN' Then Datetimelog Else '' End) As DateIN
		      From Ex) As a
	    ) As c
Full JOIN
	   (Select b.*
	    From (Select ID,
			        (Case When TYPE = 'OUT' Then Datetimelog Else '' End) As DateOUT
		      From Ex) As b
	    ) As d
ON c.ID = d.ID)
Select ID,
	  (Case When DateIN = '19000101 00:00:00.000' Then NULL Else DateIN End) As DateIN, 
	  (Case When DateOUT = '19000101 00:00:00.000' Then NULL Else DateOUT End) As DateOUT,
	  (Case When DateDiff(MM, Cast(DateIN AS Date), Cast(DateOUT As Date)) = 0 AND DateIN <> '19000101 00:00:00.000'
			Then DateDiff(SS, Convert(Time, DateIN), Convert(Time, DateOUT))/3600
	        When DateDiff(MM, Cast(DateIN AS Date), Cast(DateOUT As Date)) > 0 AND DateIN <> '19000101 00:00:00.000'
			Then Convert(int, DateAdd(SS, DateDiff(SS,Convert(Time, DateIN),
			CONVERT(Time, '23:59:59')), DateDiff(SS, CONVERT(Time, '00:00:00'),Convert(Time, DateOUT)) ))/3600
	        Else DatePart(HH, CONVERT(Time, '00:00:00'))
	   End) As Variant
From CTE
Where rownum IN (3,39,74,89,100,33)
Order By DateIN

N 28° 33' 11.93148"
E 77° 14' 33.66384"

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-16 : 00:16:31

[code]

set dateformat dmy
CREATE TABLE #Temp
(
NoTrans int,
ID varchar(3),
Datetimelog datetime,
[Type] varchar(3)
)
GO
INSERT #Temp
VALUES
(1,'001','01/01/2012 08:00:00','IN'),
(2,'001','01/01/2012 09:00:00','IN'),
(3,'001','01/01/2012 17:00:00','OUT'),
(4,'001','02/01/2012 08:00:00','IN'),
(5,'001','02/01/2012 17:00:00','OUT'),
(6,'001','02/01/2012 19:00:00','OUT'),
(7,'001','02/01/2012 23:00:00','IN'),
(8,'001','03/01/2012 04:00:00','OUT'),
(9,'001','03/01/2012 20:00:00','IN'),
(10,'001','04/01/2012 15:00:00','IN'),
(11,'001','05/01/2012 20:00:00','OUT')

;With CTE
AS
(
SELECT t.*
FROM #Temp t
OUTER APPLY (SELECT TOP 1 [Type]
FROM #Temp
WHERE Datetimelog< t.Datetimelog AND ID = t.ID
AND DAY(DATEADD(hh,-8,Datetimelog)) = DAY(DATEADD(hh,-8,t.Datetimelog))
ORDER BY Datetimelog DESC
)t1

WHERE (t1.[Type] IS NULL OR t1.[Type] <> 'IN')
AND t.[Type] = 'IN'

UNION ALL

SELECT t.*
FROM #Temp t
OUTER APPLY (SELECT TOP 1 [Type]
FROM #Temp
WHERE Datetimelog> t.Datetimelog AND ID = t.ID
AND DAY(DATEADD(hh,-8,Datetimelog)) = DAY(DATEADD(hh,-8,t.Datetimelog))
ORDER BY Datetimelog ASC
)t1

WHERE (t1.[Type] IS NULL OR t1.[Type] <> 'OUT')
AND t.[Type] = 'OUT'

)

SELECT c1.ID,c1.Datetimelog,c2.Datetimelog,COALESCE(DATEDIFF(HH,c1.Datetimelog,c2.Datetimelog)-1,0) AS Variant
FROM CTE c1
OUTER APPLY (SELECT TOP 1 Datetimelog
FROM CTE
WHERE [TYPE]='OUT'
AND [ID] = c1.ID
AND Datetimelog > c1.Datetimelog
AND DAY(DATEADD(hh,-8,Datetimelog)) = DAY(DATEADD(hh,-8,c1.Datetimelog))
ORDER BY Datetimelog)c2
WHERE c1.[Type] = 'IN'
UNION ALL
SELECT c1.ID,null,c1.Datetimelog,0 AS Variant
FROM CTE c1
OUTER APPLY (SELECT TOP 1 Datetimelog
FROM CTE
WHERE [TYPE]='IN'
AND [ID] = c1.ID
AND Datetimelog < c1.Datetimelog
AND DAY(DATEADD(hh,-8,Datetimelog)) = DAY(DATEADD(hh,-8,c1.Datetimelog))
ORDER BY Datetimelog)c2
WHERE c1.[Type] = 'OUT'
AND c2.Datetimelog IS NULL

DROP TABLE #temp

output
---------------------------------
ID Datetimelog Datetimelog Variant
001 2012-01-01 08:00:00.000 2012-01-01 17:00:00.000 8
001 2012-01-02 08:00:00.000 2012-01-02 19:00:00.000 10
001 2012-01-02 23:00:00.000 2012-01-03 04:00:00.000 4
001 2012-01-03 20:00:00.000 NULL 0
001 2012-01-04 15:00:00.000 NULL 0
001 NULL 2012-01-05 20:00:00.000 0

[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

bny82
Starting Member

3 Posts

Posted - 2012-05-23 : 23:12:45

thanks in many visakh16, you help me on this solution, actually i have the solution also, now i will choose regard the execution time, anyway thanks for vinu.vijayan but unfortunately your solution looks have a hardcode, which is i cant select one by one...anyway thanks for all your solutions guys....

if you want to know what my solution...this as ur reff. also.

THIS IS MY STRUCTURE TABLE

CREATE TABLE [dbo].[checkinout](
[id] [int] NULL,
[userid] [bigint] NULL,
[checktime] [datetime] NULL,
[checktype] [int] NULL,
[verifycode] [int] NULL,
[sn] [bigint] NULL
) ON [PRIMARY]

AND THIS IS MY STORE PROCEDURE.

GO
/****** Object: StoredProcedure [dbo].[SP_SYNCLOGIX] Script Date: 05/24/2012 09:58:28 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROC [dbo].[SP_SYNCLOGIX](@STARTDATE AS DATETIME, @ENDDATE AS DATETIME)
AS
BEGIN
--SET @STARTDATE = '05/01/2012'
--SET @ENDDATE = '05/30/2012'
IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N'TempDB..#CHECKIN')
)
BEGIN
DROP TABLE #CHECKIN
END

select * into #CHECKIN
from checkinout
WHERE CHECKTYPE = '0'

IF EXISTS
(
SELECT *
FROM tempdb.dbo.sysobjects
WHERE ID = OBJECT_ID(N'TempDB..#CHECKOUT')
)
BEGIN
DROP TABLE #CHECKOUT
END

select * into #CHECKOUT
from checkinout
WHERE CHECKTYPE = '1'

SELECT DISTINCT * FROM (
SELECT A.ID,A.USERID,A.CHECKTIME AS CHECKIN , B.CHECKTIME AS CHECKOUT,A.VERIFYCODE,A.SN,DATEDIFF(HOUR, A.CHECKTIME, B.CHECKTIME)AS VARIANCE FROM #CHECKIN A
LEFT OUTER JOIN #CHECKOUT B
ON A.UserID = B.UserID AND
((DAY(A.CHECKTIME) = DAY(B.CHECKTIME)
AND MONTH(A.CHECKTIME) = MONTH(B.CHECKTIME)
AND YEAR(A.CHECKTIME) = YEAR(B.CHECKTIME))
OR
(DAY(A.CHECKTIME) = DAY(B.CHECKTIME) - 1
AND MONTH(A.CHECKTIME) = MONTH(B.CHECKTIME)
AND YEAR(A.CHECKTIME) = YEAR(B.CHECKTIME)))
AND A.ID < B.ID
)A
WHERE CHECKIN < CHECKOUT AND ((DATEDIFF(HOUR, CHECKIN, CHECKOUT))<12)
AND CHECKIN BETWEEN @STARTDATE AND @ENDDATE
END

Thanks & Regards,
bny17

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources