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)
 How to display date if there are no values?

Author  Topic 

swenri
Yak Posting Veteran

72 Posts

Posted - 2013-11-01 : 16:48:22
Hi,
I’ve a report that picks up orders between yesterday 6:00 AM to today 6:00 AM. The problem I‘m having is when I don’t have any orders between this time period in the crystal report it is not displaying the time ie ; the fromdate and todate. How do I make sure that though I have zero orders for time period,I display the fromdate and todate in the crystal report.Please see the code that I have highlighted. This is really urgent.Thank you
--------------------------------------------------------------------------


USE tempDB



IF OBJECT_ID(N'tempdb..#Result1', N'U')
IS NOT NULL
DROP TABLE #Result1;

GO




USE [xxxxxxxxxxxxxxxxxxx]
GO

IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[MMC_SP_FN_NutritionConsult]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[MMC_SP_FN_NutritionConsult]
GO

USE [xxxxxxxxxxxxxxx]
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



CREATE PROC [dbo].[MMC_SP_FN_NutritionConsult]

AS


BEGIN

SET NOCOUNT ON
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

PRINT 'FOOD AND NUTRITION NUTRITION CONSULT ORDERS'
DECLARE @Location varchar(255)

BEGIN


Declare @FromDate datetime
Declare @ToDate datetime
Declare @RptTimeFrameout VARCHAR(50)




SET @RptTimeFrameout='1'
if @RptTimeFrameout='1' -- Daily
Begin

select @FromDate = CONVERT(datetime,ltrim(CONVERT(char(30),DATEADD(d,-1,getdate()),101))+ '06:00:00 AM')
select @ToDate = CONVERT(DATETIME,ltrim(CONVERT(char(30),GETDATE(),101))+'06:01:00 AM')


End



SELECT DISTINCT

(select top 1 "BVActions"."IPR_Display"
from "dbo"."BVActions"
where "BLSession_Extended"."sessionid" = "BVActions"."sessionid"
AND "BVActions"."IPR_Display"='Order Food and Nutrition Consult'
AND "BVActions".OrderType = 'RegularOrder'
AND "BVActions"."cancelledSig" IS NULL
order by"BVActions"."orderedTime" Desc
) AS Nutrition_Consult,



(select top 1 "BVActions"."ForeveryText"
from "dbo"."BVActions"
where "BLSession_Extended"."sessionid" = "BVActions"."sessionid"
AND "BVActions"."IPR_Display"='Order Food and Nutrition Consult'
AND "BVActions".OrderType = 'RegularOrder'
AND "BVActions"."cancelledSig" IS NULL
order by"BVActions"."orderedTime" Desc
) AS Nut_Consult_Reason,



(select top 1 "BLUser_names_Extended"."FullName"
from "dbo"."BLUser_names_Extended"
where
"BVActions"."orderedSigner"="BLUser_names_Extended"."UserID"
AND "BVActions"."IPR_Display"='Order Food and Nutrition Consult'
AND "BVActions".OrderType = 'RegularOrder'
AND "BVActions"."cancelledSig" IS NULL
order by"BVActions"."orderedTime" Desc
) AS ConsultPlacedBy,


(select top 1 "BVActions"."orderedTime"
from "dbo"."BVActions"
where "BLSession_Extended"."sessionid" = "BVActions"."sessionid"
AND "BVActions"."IPR_Display"='Order Food and Nutrition Consult'
AND "BVActions".OrderType = 'RegularOrder'
AND "BVActions"."cancelledSig" IS NULL
order by"BVActions"."orderedTime" Desc
) AS ConsultTime,

("BLSession_Extended"."LastName" + "BLSession_Extended"."firstname") AS PtName,


(select top 1 "bllocation"."location_name"
from "dbo"."bllocation"
where "BLPatient_Location"."location_num" = "bllocation"."location_num"
AND "BLPatient_Location"."SessionID" = "BVActions"."SessionID"
AND "BVActions"."IPR_Display"='Order Food and Nutrition Consult'
AND "BVActions".OrderType = 'RegularOrder'
AND "BVActions"."cancelledSig" IS NULL
AND dbo.bllocation.FacilityID = 0
ORDER BY BLPatient_Location.enter_time DESC
) AS ROOM,


"BLSession_Extended"."MRN",

("MO_Times"."RegistrationAdmissionTime") AS AdmitDate,
"MO_Demographics"."Age",
"MO_Demographics"."DateOfBirth" AS DOB,



(select top 1 f.ValueStr from BVFindings f
where f.sessionid = "BLSession_Extended"."sessionid"
AND f.ObjectName = 'Height_feet'
order by f.entrytime desc) AS HtFeet,



(select top 1 f.ValueStr from BVFindings f
where f.sessionid = "BLSession_Extended"."sessionid"
AND f.ObjectName = 'Height_inch'
order by f.entrytime desc) AS HtInch,



(select top 1 f.ValueStr from BVFindings f
where f.sessionid = "BLSession_Extended"."sessionid"
AND f.IPR_Display = 'Current Weight'
order by f.entrytime desc) AS Wt,



(select top 1 "BLOrdersLog"."Text_str"
from "dbo"."BLOrdersLog"
where "BLSession_Extended"."sessionid" = "BLOrdersLog"."sessionid"
AND ("BLOrdersLog"."Text_str" LIKE '%diet%'
OR "BLOrdersLog"."Text_str" LIKE '%NPO%'
OR "BLOrdersLog"."Text_str" LIKE '%Order NPO + Ice Chips%')
AND "BLOrdersLog"."CancelledSig" IS NULL
order by "BLOrdersLog".OrderedTime Desc
) AS Diet,



(select top 1 ISNULL ("BLOrdersLog"."ForEveryText",' ')
from "dbo"."BLOrdersLog"
where "BLSession_Extended"."sessionid" = "BLOrdersLog"."sessionid"
AND ("BLOrdersLog"."Text_str" LIKE '%diet%'
OR "BLOrdersLog"."Text_str" LIKE '%NPO%'
OR "BLOrdersLog"."Text_str" LIKE '%Order NPO + Ice Chips%')
AND "BLOrdersLog"."CancelledSig" IS NULL
order by "BLOrdersLog".OrderedTime Desc
) AS DietText,



(select top 1 "BLOrdersLog"."orderedTime"
from "dbo"."BLOrdersLog"
where "BLSession_Extended"."sessionid" = "BLOrdersLog"."sessionid"
AND ("BLOrdersLog"."Text_str" LIKE '%diet%'
OR "BLOrdersLog"."Text_str" LIKE '%NPO%'
OR "BLOrdersLog"."Text_str" LIKE '%Order NPO + Ice Chips%')
AND "BLOrdersLog"."CancelledSig" IS NULL
order by "BLOrdersLog".OrderedTime Desc
) AS DietOrderedTime,


(select top 1 "BLOrdersLog"."Annotation"
from "dbo"."BLOrdersLog"
where "BLSession_Extended"."sessionid" = "BLOrdersLog"."sessionid"
AND ("BLOrdersLog"."Text_str" LIKE '%diet%'
OR "BLOrdersLog"."Text_str" LIKE '%NPO%'
OR "BLOrdersLog"."Text_str" LIKE '%Order NPO + Ice Chips%')
AND "BLOrdersLog"."CancelledSig" IS NULL

AND "BLOrdersLog"."Annotation" IS NOT NULL
order by "BLOrdersLog".OrderedTime Desc
) AS Annotation,

Allergy_Description,

@FromDate AS Fromdate,
@ToDate AS Todate INTO #Result1






FROM ((("DatamartDB2"."dbo"."BLSession_Extended" "BLSession_Extended"
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVProblems" "BVProblems"
ON "BLSession_Extended"."sessionID"="BVProblems"."sessionid"
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLOrdersLog" "BLOrdersLog"
ON "BLSession_Extended"."sessionID"="BLOrdersLog"."sessionID"


)
INNER JOIN "DatamartDB2"."dbo"."BLPatient_Location" "BLPatient_Location"
ON "BLSession_Extended"."sessionID"="BLPatient_Location"."sessionID")
LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Times" "MO_Times"
ON "BLSession_Extended"."sessionID"="MO_Times"."SessionID")
INNER JOIN "DatamartDB2"."dbo"."bllocation" "bllocation"
ON "BLPatient_Location"."location_num"="bllocation"."location_num"

LEFT OUTER JOIN "DatamartDB2"."dbo"."MO_Demographics" "MO_Demographics"
ON "BLSession_Extended"."sessionID"="MO_Demographics"."sessionID"
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLUserFreeText" "BLUserFreeText"
ON ("BVProblems"."sessionid"="BLUserFreeText"."sessionID"
AND "BVProblems"."AtomID"="BLUserFreeText"."AtomID"
AND "BVProblems"."RowID" = "BLUserFreeText"."RowID"
LEFT OUTER JOIN "DatamartDB2"."dbo"."BVActions" "BVActions"
ON "BLSession_Extended"."sessionID"="BVActions"."sessionid"
LEFT OUTER JOIN "DatamartDB2"."dbo"."BLUser_names_Extended" "BLUser_names_Extended"
ON "BVActions"."orderedSigner"="BLUser_names_Extended"."UserID"

WHERE "BLSession_Extended"."FacilityID"=0


AND "BVActions"."orderedTime" >= @FromDate
AND "BVActions"."orderedTime" <=@ToDate


AND "BVActions"."IPR_Display"='Order Food and Nutrition Consult'
AND "BVActions".OrderType = 'RegularOrder'
AND "BVActions"."CancelledSig" IS NULL



SELECT rlt1.*
FROM #Result1 rlt1
where ROOM in

(SELECT TOP 1 ROOM
from #Result1 rlt2
where rlt1.mrn=rlt2.mrn
order by ROOM desc
)

END

END

SET NOCOUNT OFF

SET ANSI_NULLS OFF




GO

GO


IF OBJECT_ID(N'tempdb..#Result1', N'U')
IS NOT NULL
DROP TABLE #Result1;

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-02 : 13:13:13
Add them as output parameters in procedure and return values. Then in report check if no data present and display these values

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

swenri
Yak Posting Veteran

72 Posts

Posted - 2013-11-05 : 09:48:10
Thank you for the reply but, can you please explain how to do it in detailed. Thank you once again and it's urgent
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-05 : 12:47:02
see

http://www.sqlteam.com/article/stored-procedures-returning-data

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

swenri
Yak Posting Veteran

72 Posts

Posted - 2013-11-05 : 13:53:56
Thank you for the reply. But, I had tried this to use a formula field in crystal reports where I used to retrieve currentdate using currentdatetime function and previousdate using currentdatetime -1 but, the problem is it is not showing as 11-05-2013 6:00 AM and 11-04-2013 6:00AM. Can anybody let me know how do I achieve this ? Thank you
Go to Top of Page
   

- Advertisement -