SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
 Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Please Help Me on this QUERY....		  New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

bny82
Starting Member

Indonesia
3 Posts
Posted - 05/14/2012 :  00:42:40  Show Profile  Reply with Quote
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
Edited by - bny82 on 05/14/2012 01:25:34

bny82
Starting Member

Indonesia
3 Posts
Posted - 05/14/2012 :  01:22:10  Show Profile  Reply with Quote
Please...some one help me out...anyone....

Thanks & Regards,
bny17
Go to Top of Page

namman
Constraint Violating Yak Guru

USA
258 Posts
Posted - 05/14/2012 :  22:09:58  Show Profile  Reply with Quote
Explain buz rule how to get the expected output, please.
Go to Top of Page

vinu.vijayan
Posting Yak Master

India
227 Posts
Posted - 05/15/2012 :  02:56:26  Show Profile  Reply with Quote
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"
Edited by - vinu.vijayan on 05/15/2012 02:58:46
Go to Top of Page

vinu.vijayan
Posting Yak Master

India
227 Posts
Posted - 05/15/2012 :  05:17:40  Show Profile  Reply with Quote
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"
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
47023 Posts
Posted - 05/16/2012 :  00:16:31  Show Profile  Reply with Quote 


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


------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
Go to Top of Page

bny82
Starting Member

Indonesia
3 Posts
Posted - 05/23/2012 :  23:12:45  Show Profile  Reply with Quote
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
Edited by - bny82 on 05/23/2012 23:15:11
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.11 seconds. Powered By: Snitz Forums 2000