quote:

---

Originally posted by sakets_2000

Can you post the full query ?

---

quote:

---

Originally posted by bklr

select convert(varchar(20),convert(varchar(20),datediff(minute,depdate,confirmdate))/(24*60))+':'+convert(varchar(20),convert(varchar(20),datediff(minute,depdate,confirmdate))/)%(24*60)/60) )as Ageing

---

SELECT	CAST(DATEDIFF(SECOND, DepDate, ConfirmDate) / 86400 AS VARCHAR(12)) + ':' + CAST(DATEDIFF(SECOND, DepDate, ConfirmDate) / 3600 AS VARCHAR(12))
FROM	#tempSemi

quote:

---

Originally posted by Peso

Simpler...

SELECT	CAST(DATEDIFF(SECOND, DepDate, ConfirmDate) / 86400 AS VARCHAR(12)) + ':' + CAST(DATEDIFF(SECOND, DepDate, ConfirmDate) / 3600 AS VARCHAR(12))
FROM	#tempSemi

---

E 12°55'05.63"
N 56°04'39.26"

---

quote:

---

Originally posted by ganny

quote:

---

Originally posted by Peso

Simpler...

SELECT	CAST(DATEDIFF(SECOND, DepDate, ConfirmDate) / 86400 AS VARCHAR(12)) + ':' + CAST(DATEDIFF(SECOND, DepDate, ConfirmDate) / 3600 AS VARCHAR(12))
FROM	#tempSemi

---

E 12°55'05.63"
N 56°04'39.26"

---

No,, its not working Peso and bklr.

if i execute the same query seperately as you given, its working. where the same is not working in my stored procedure.

please help someone..

---

CREATE PROCEDURE Lsp_DrBook
(
	@FromDate DATETIME,
	@ToDate DATETIME,
	@Port VARCHAR(MAX)
)
AS

SET NOCOUNT ON

DECLARE @FinalBooking TABLE
	(
		Agency VARCHAR(20),
		VesselCode VARCHAR(20),
		VoyageCode VARCHAR(20),
		Bound CHAR(1),
		Port VARCHAR(100),
		VesselArrive DATETIME,
		VesselDepature DATETIME,
		BokkingCrtd DATETIME,
		ConfirmDate DATETIME,
		DraftBkg_AtDepature VARCHAR(30),
		DraftBkg_Depature_24hr VARCHAR(30),
		BkgCrtd_AfterDepature VARCHAR(30), 
		BkgStatus VARCHAR(20),
		BkgCreated VARCHAR(20)
	) 

SELECT	*
INTO	#tmpPort
FROM	OperationactivityinsertTemp(@Port)

INSERT		@FinalBooking
		(
			Agency,
			VesselCode,
			VoyageCode,
			Bound,Port,
			VesselArrive,
			VesselDepature,
			BokkingCrtd,
			ConfirmDate,
			DraftBkg_AtDepature,
			DraftBkg_Depature_24hr,
			BkgCrtd_AfterDepature,
			BkgStatus
		) 
SELECT		b.agencyid,
		als.Vessel,
		als.Voyage,
		als.Bound,
		als.port, 
		als.arrdate,
		als.depdate,
		b.datecr,
		b.confirmdate,
		b.book_no,
		CASE
			WHEN DATEADD(hh, 24, als.depdate) <= GETDATE() THEN b.book_no
			ELSE 'N'
		END AS bkg24hrsDraftbkg, 
		CASE
			WHEN b.datecr > als.depdate THEN b.book_no
			ELSE 'N'
		END AS aftervsldep,
		b.status 
FROM		allschedules AS als
INNER JOIN	booking AS b ON b.loadportid = als.port
			and als.terminal = b.polterminal 
			and als.vessel = b.vesselcode
			and als.voyage = b.voyagecode
			and als.Bound = b.bound 
INNER JOIN	#tmpPort AS p ON p.string = als.port
WHERE		als.depdate >= @FromDate
		and als.depdate < @ToDate + 1
		and b.status = 'D'

SELECT	Agency,
	VesselCode,
	VoyageCode,
	Bound,Port,
	VesselArrive,
	VesselDepature,
	BokkingCrtd,
	CASE
		WHEN DraftBkg_Depature_24hr = 'N' AND BkgCrtd_AfterDepature = 'N' THEN DraftBkg_AtDepature
		ELSE 'N'
	END AS Draft_ATDep,
	CASE
		WHEN BkgCrtd_AfterDepature = 'N' THEN DraftBkg_Depature_24hr
		ELSE 'N'
	END AS Draft_24hrsDep,
	BkgCrtd_AfterDepature,
	ConfirmDate,
	BkgStatus,
	CAST(DATEDIFF(HOUR, VesselDepature, ConfirmDate) AS VARCHAR(12)) AS Ageing
INTO	#tempFinal
FROM	@FinalBooking

SELECT		b.agencyid,
		als.Vessel,
		als.Voyage,
		als.Bound,
		als.port, 
		als.arrdate,
		als.depdate,
		b.datecr,
		b.confirmdate,
		CASE
			WHEN DATEDIFF(HOUR, als.depdate, b.confirmdate) <= 24 THEN b.book_no
			ELSE 'N'
		END AS Draft_At_Dep, 
		CASE
			WHEN DATEDIFF(HOUR, als.depdate, b.confirmdate) > 24 THEN b.book_no
			ELSE 'N'
		END AS bkg24hrsDraftbkg,
		CASE
			WHEN b.datecr > als.depdate THEN b.book_no
			ELSE 'N'
		END AS aftervsldep,
		b.status
INTO		#tempSemi
FROM		allschedules AS als
INNER JOIN	booking AS b ON als.port = b.loadportid 
			AND als.terminal = b.polterminal 
			AND als.vessel = b.vesselcode
			AND als.voyage = b.voyagecode
			AND als.Bound = b.bound 
INNER JOIN	#tmpPort AS p ON p.string = als.port
WHERE		als.depdate >= @FromDate
		AND als.depdate < @ToDate + 1
		AND b.status = 'C'

SELECT	agencyid,
	vessel,
	voyage,
	Bound,
	Port,
	arrdate,
	depdate,
	datecr,
	Draft_At_dep as Draft_ATDep,
	bkg24hrsDraftbkg as Draft_24hrsDep, 
	aftervsldep,
	confirmdate,
	status,
	CAST(DATEDIFF(SECOND, DepDate, ConfirmDate) / 86400 AS VARCHAR(12)) + ':' + CAST(DATEDIFF(SECOND, DepDate, ConfirmDate) / 3600 AS VARCHAR(12)) AS Ageing
INTO	#tempFinal1
FROM	#tempSemi
WHERE	confirmDate >= depDate 

SELECT		agency,
		vesselcode,
		voyagecode,
		bound,
		port,
		vesselarrive,
		vesseldepature,
		bokkingcrtd,
		draft_atdep,
		draft_24hrsdep,
		bkgcrtd_afterdepature,
		confirmdate,
		bkgstatus,
		ageing
FROM		#tempFinal

UNION

SELECT		agencyid,
		vessel,
		voyage,
		bound,
		port,
		arrdate,
		depdate,
		datecr,
		draft_atdep,
		draft_24hrsdep,
		aftervsldep,
		confirmdate,
		status,
		ageing
FROM		#tempFinal1

ORDER BY	VesselArrive,
		VesselDepature 

DROP TABLE	#tempFinal,
		#tempSemi,
		#tempFinal1