How to display date if there are no values? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

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

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

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources