Calculate dates - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Calculate dates

Author

Topic

lols
Posting Yak Master

174 Posts

Posted - 2007-12-17 : 22:38:37

Hi,
I have a system where an employee makes a call. The call can be of type 1,2,3. I have to calculate:
a. the last time the employee called up for that particular call type.
b. Yesterday
c. Call Duration between the last call date and yesterday
Please see the expected output to get an idea.

I will be passing in EmpID and CallType to the procedure.

First a record gets inserted into CallDetail and then into the Call for each CallType.

SAMPLE SCRIPT:

DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)
DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime)

INSERT @CallDetail
SELECT 12123, 1, '11/30/2007 10:41:34 AM' UNION ALL
SELECT 43555, 1, '12/1/2007 11:21:23 AM' UNION ALL
SELECT 65322, 1, '12/18/2007 04:12:34 AM' 
-- 65322 is the current calldetail id

INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 2, 12123 UNION ALL
SELECT 113, 2, 12123 UNION ALL
SELECT 123, 1, 43555 UNION ALL
SELECT 134, 1, 43555 UNION ALL
SELECT 143, 1, 65322 UNION ALL
SELECT 145, 1, 65322 UNION ALL
SELECT 154, 2, 65322 UNION ALL
SELECT 185, 3, 65322

Select * from @Call
Select * from @CallDetail

EXPECTED OUTPUT

-- For CallType 1 EmpID 1
LastCallDate   Yesterday   Duration
12/1/2007     12/17/2007   16 days

-- For CallType 2 EmpID 1
LastCallDate   Yesterday   Duration
11/30/2007     12/17/2007  17 days

-- For CallType 3 (There was no CallType 3 made earlier by Empid 1)
LastCallDate   Yesterday   Duration
     -         12/17/2007  0 days

How to achieve it
thanks.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-12-17 : 23:52:29

What do you mean by Yesterday??? Is it Yesterday or Tomorrow ??

Chirag

http://www.chirikworld.com

lols
Posting Yak Master

174 Posts

Posted - 2007-12-18 : 00:08:57

Hi,
Sorry about that..i changed the date..it is Yesterday..so like today is 12/18..yesterday is 12/17

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2007-12-18 : 00:20:25

i think this will provide you with desired o/p:-

CREATE PROC EmpCallDetails
@EmpID int,
@CallType int
AS

SELECT MAX(EntryDt) AS 'LastCallDate',
DATEADD(d,DATEDIFF(d,0,DATEADD(d,-1,GETDATE())),0) AS 'Yesterday',
DATEDIFF(d,MAX(EntryDt),DATEADD(d,DATEDIFF(d,0,DATEADD(d,-1,GETDATE())),0)) AS 'Duration'
FROM @Call c
INNER JOIN @CallDetail cd
ON cd.CallDetailID =c.CallDetailID
WHERE c.CallType=@CallType
AND cd.EmpID=@EmpID
GROUP BY cd.EmpID,c.CallType
GO

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-12-18 : 00:28:01

[code]
Select Case When EntryDt > Yesterday Then '' Else Convert(Varchar(20),EntryDt, 101) End As EntryDt
,Yesterday,Case When Datediff(dd,EntryDt,Yesterday) < 0 Then 0 Else Datediff(dd,EntryDt,Yesterday) End As NosofDays From
(
Select Max(CD.EntryDt) as EntryDt ,DateADd(dd,-1,GetDate()) As Yesterday
From
@Call C Left Outer Join @CallDetail Cd On C.CallDetailID = Cd.CAllDetailID
Group By C.CallDetailID
) As F
[/code]

Chirag

http://www.chirikworld.com

lols
Posting Yak Master

174 Posts

Posted - 2007-12-18 : 00:57:57

Hi,
Thanks chirag..But where do I add the WHERE clause

Select Case When EntryDt > Yesterday Then '' Else Convert(Varchar(20),EntryDt, 101) End As EntryDt
,Yesterday,Case When Datediff(dd,EntryDt,Yesterday) < 0 Then 0 Else Datediff(dd,EntryDt,Yesterday) End As NosofDays From
(
Select Max(CD.EntryDt) as EntryDt ,DateADd(dd,-1,GetDate()) As Yesterday
From
@Call C Left Outer Join @CallDetail Cd On C.CallDetailID = Cd.CAllDetailID
where C.CallType = 1 and Cd.EmpId = 1
Group By C.CallDetailID
) As F

I will be passing in EmpID and CallType to the procedure.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-12-18 : 01:03:23

[code]

DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)
DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime)
DECLARE @EmpID int, @CallType Int

Select @EmpID = 1 , @CallType =1

INSERT @CallDetail
SELECT 12123, 1, '11/30/2007 10:41:34 AM' UNION ALL
SELECT 43555, 1, '12/1/2007 11:21:23 AM' UNION ALL
SELECT 65322, 1, '12/20/2007 04:12:34 AM'
-- 65322 is the current calldetail id

INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 2, 12123 UNION ALL
SELECT 113, 2, 12123 UNION ALL
SELECT 123, 1, 43555 UNION ALL
SELECT 134, 1, 43555 UNION ALL
SELECT 143, 1, 65322 UNION ALL
SELECT 145, 1, 65322 UNION ALL
SELECT 154, 2, 65322 UNION ALL
SELECT 185, 3, 65322

--Select * from @Call
--Select * from @CallDetail

Select Case When EntryDt > Yesterday Then '' Else Convert(Varchar(20),EntryDt, 101) End As EntryDt
,Yesterday,Case When Datediff(dd,EntryDt,Yesterday) < 0 Then 0 Else Datediff(dd,EntryDt,Yesterday) End As NosofDays From
(
Select Max(CD.EntryDt) as EntryDt ,DateADd(dd,-1,GetDate()) As Yesterday
From
@Call C Left Outer Join @CallDetail Cd On C.CallDetailID = Cd.CAllDetailID
Where Cd.EmpID = @EmpID And C.CallType = @CallType
Group By C.CallDetailID
) As F

[/code]

Chirag

http://www.chirikworld.com

lols
Posting Yak Master

174 Posts

Posted - 2007-12-18 : 01:17:24

Hi Chirag,
:):)..i am a newbie but i know that for sure

I wanted to show that even after passing the empid and calltype, i get 3 rows instead of one..So where do I put the WHERE clause so as to get only the desired row as per the empid and calltype

EXPECTED OUTPUT

-- If CallType 1 EmpID 1
LastCallDate Yesterday Duration
12/1/2007 12/17/2007 16 days

-- If CallType 2 EmpID 1
LastCallDate Yesterday Duration
11/30/2007 12/17/2007 17 days

-- IF CallType 3 (There was no CallType 3 made earlier by Empid 1)
LastCallDate Yesterday Duration
- 12/17/2007 0 days

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2007-12-18 : 02:03:03

I guess i had missed out something .. please find the modfied.



DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)
DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime)
DECLARE @EmpID int, @CallType Int

Select @EmpID = 1 , @CallType =3  

INSERT @CallDetail
SELECT 12123, 1, '11/30/2007 10:41:34 AM' UNION ALL
SELECT 43555, 1, '12/1/2007 11:21:23 AM' UNION ALL
SELECT 65322, 1, '12/20/2007 04:12:34 AM' 
-- 65322 is the current calldetail id

INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 2, 12123 UNION ALL
SELECT 113, 2, 12123 UNION ALL
SELECT 123, 1, 43555 UNION ALL
SELECT 134, 1, 43555 UNION ALL
SELECT 143, 1, 65322 UNION ALL
SELECT 145, 1, 65322 UNION ALL
SELECT 154, 2, 65322 UNION ALL
SELECT 185, 3, 65322


--Select * from @Call order by 2 
--Select * from @CallDetail



Select  IsNull(Convert(varchar(20),Max(EntryDt),103),'-') As EntryDt,Max(Yesterday) As Yesterday, 
		DateDiff(dd,IsNull(Max(EntryDt),Max(Yesterday)),Max(Yesterday)) As Duration
From 
(
	Select CD.EntryDt as EntryDt ,DateADd(dd,-1,GetDate()) As Yesterday 
	From 
	@Call C Left Outer Join 
	(
		Select * From  @CallDetail Cd Where EntryDt < GetDate() And  Cd.EmpID = @EmpID 
	) As Cd 
	On C.CallDetailID = Cd.CAllDetailID
	Where  C.CallType = @CallType 
	Group By C.CallDetailID,CD.EntryDt  

) As F

Chirag

http://www.chirikworld.com

lols
Posting Yak Master

174 Posts

Posted - 2007-12-18 : 03:03:40

thanks buddy..this is exactly what i need..God bless..

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources