Solved

How to return the value of the integer variables used in the stored procedure

Posted on 2016-11-22
3
44 Views
Last Modified: 2016-11-25
I had this question after viewing Finding records between to times and spanning over midnight.

When designing the Crystal Report that used the stored procedure I found that using the integer variables that defined the start and end times of the shift would extremely useful to display dynamic labels.  So how would I be able to incorporate this into the stored procedure and return values such as:

SET @8hrfirstshiftstart = 7      (Return 0700 - 1500)
SET @8hrfirstshiftend = 15

                                                    (Calculate and return 1500 - 2300)

SET @8hrthirdshiftstart = 23  (Return 2300 - 0700)
SET @8hrthirdshiftend = 7

SET @12hrfirstshiftstart = 7   (Return 0700 - 1900)
SET @12hrfirstshiftend = 19
                                                    (Calculate and return 1900 -0700)


Here is the stored procedure:

USE [CT_PSIMS]
GO
/****** Object:  StoredProcedure [dbo].[CR_911_Calls_ByShiftAndCount]    Script Date: 11/22/2016 09:46:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Name
-- Create date: 
-- Description:	Find all 911 calls in E911Data per shift
-- =============================================
ALTER PROCEDURE [dbo].[CR_911_Calls_ByShiftAndCount] 
	-- Add the parameters for the stored procedure here
	@Agency int = 0, 
	@Year int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @8hrfirstshiftstart as int  -- Don't need second shift values
	DECLARE @8hrfirstshiftend as int
	DECLARE @8hrthirdshiftstart as int
	DECLARE @8hrthirdshiftend as int

	DECLARE @12hrfirstshiftstart as int  -- Don't need second shift values
	DECLARE @12hrfirstshiftend as int

	SET @8hrfirstshiftstart = 7
	SET @8hrfirstshiftend = 15
	SET @8hrthirdshiftstart = 23
	SET @8hrthirdshiftend = 7
	SET @12hrfirstshiftstart = 7
	SET @12hrfirstshiftend = 19



    -- Insert statements for procedure here
SELECT
      CurYRshift_1_8hr
    , CurYRshift_2_8hr
    , CurYRshift_3_8hr
    , PreYRshift_1_8hr
    , PreYRshift_2_8hr
    , PreYRshift_3_8hr
    , CurYRshift_1_8hr - PreYRshift_1_8hr AS DiffShift_1_8hr
    , CurYRshift_2_8hr - PreYRshift_2_8hr AS DiffShift_2_8hr
    , CurYRshift_3_8hr - PreYRshift_3_8hr AS DiffShift_3_8hr
    , CurYRshift_1_12Hr
    , CurYRshift_2_12Hr
    , PreYRshift_1_12Hr
    , PreYRshift_2_12Hr
    , CurYRshift_1_12Hr - PreYRshift_1_12Hr AS DiffShift_1_12hr
    , CurYRshift_2_12Hr - PreYRshift_2_12Hr AS DiffShift_2_12hr
FROM (
    SELECT
          COUNT(CASE WHEN EntryYear = 1 AND hr8_shift_no = 1 THEN 1 END) AS CurYRshift_1_8hr
        , COUNT(CASE WHEN EntryYear = 1 AND hr8_shift_no = 2 THEN 1 END) AS CurYRshift_2_8hr
        , COUNT(CASE WHEN EntryYear = 1 AND hr8_shift_no = 3 THEN 1 END) AS CurYRshift_3_8hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr8_shift_no = 1 THEN 1 END) AS PreYRshift_1_8hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr8_shift_no = 2 THEN 1 END) AS PreYRshift_2_8hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr8_shift_no = 3 THEN 1 END) AS PreYRshift_3_8hr
        , COUNT(CASE WHEN EntryYear = 1 AND hr12_shift_no = 1 THEN 1 END) AS CurYRshift_1_12Hr
        , COUNT(CASE WHEN EntryYear = 1 AND hr12_shift_no = 2 THEN 1 END) AS CurYRshift_2_12Hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr12_shift_no = 1 THEN 1 END) AS PreYRshift_1_12Hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr12_shift_no = 2 THEN 1 END) AS PreYRshift_2_12Hr
		
    FROM (
            select
                  EntryYear
                , case
                    when EntryHour  < @8hrthirdshiftend  or EntryHour > @8hrthirdshiftstart then 3
                    when EntryHour >= @8hrfirstshiftstart and EntryHour < @8hrfirstshiftend then 1
                    else 2
                  end hr8_shift_no
                , case
                    when EntryHour >= @12hrfirstshiftstart and EntryHour < @12hrfirstshiftend then 1
                    else 2
                  end hr12_shift_no
            from e911data
            cross apply (select
                             datepart(hour,[EntryDateTime]) as EntryHour 
                           , datepart(year,[EntryDateTime]) - (@year-1) as EntryYear
                        ) ca
            where EntryDateTime >= dateadd(year,(@year-1901),'19000101') /* 1 Jan year before @ year e.g. 2015 */
              and EntryDateTime <  dateadd(year,(@year-1899),'19000101')  /* 1 Jan year after @ year  e.g. 2017 */
			  and Agency = @Agency
          ) AS derived
    ) AS d	
END

Open in new window

0
Comment
Question by:MikeM670
3 Comments
 
LVL 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 250 total points
ID: 41898054
Simply incorporate those variables into your SELECT statement.

USE [CT_PSIMS]
GO
/****** Object:  StoredProcedure [dbo].[CR_911_Calls_ByShiftAndCount]    Script Date: 11/22/2016 09:46:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Name
-- Create date: 
-- Description:	Find all 911 calls in E911Data per shift
-- =============================================
ALTER PROCEDURE [dbo].[CR_911_Calls_ByShiftAndCount] 
	-- Add the parameters for the stored procedure here
	@Agency int = 0, 
	@Year int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @8hrfirstshiftstart as int  -- Don't need second shift values
	DECLARE @8hrfirstshiftend as int
	DECLARE @8hrthirdshiftstart as int
	DECLARE @8hrthirdshiftend as int

	DECLARE @12hrfirstshiftstart as int  -- Don't need second shift values
	DECLARE @12hrfirstshiftend as int

	SET @8hrfirstshiftstart = 7
	SET @8hrfirstshiftend = 15
	SET @8hrthirdshiftstart = 23
	SET @8hrthirdshiftend = 7
	SET @12hrfirstshiftstart = 7
	SET @12hrfirstshiftend = 19



    -- Insert statements for procedure here
SELECT
      CurYRshift_1_8hr
    , CurYRshift_2_8hr
    , CurYRshift_3_8hr
    , PreYRshift_1_8hr
    , PreYRshift_2_8hr
    , PreYRshift_3_8hr
    , CurYRshift_1_8hr - PreYRshift_1_8hr AS DiffShift_1_8hr
    , CurYRshift_2_8hr - PreYRshift_2_8hr AS DiffShift_2_8hr
    , CurYRshift_3_8hr - PreYRshift_3_8hr AS DiffShift_3_8hr
    , CurYRshift_1_12Hr
    , CurYRshift_2_12Hr
    , PreYRshift_1_12Hr
    , PreYRshift_2_12Hr
    , CurYRshift_1_12Hr - PreYRshift_1_12Hr AS DiffShift_1_12hr
    , CurYRshift_2_12Hr - PreYRshift_2_12Hr AS DiffShift_2_12hr
    , @8hrfirstshiftstart AS 8hrfirstshiftstart
    , @8hrfirstshiftend = AS 8hrfirstshiftend
    , @8hrthirdshiftstart = AS 8hrthirdshiftstart
    , @8hrthirdshiftend = AS 8hrthirdshiftend
    , @12hrfirstshiftstart = AS 12hrfirstshiftstart
    , @12hrfirstshiftend = AS 12hrfirstshiftend
FROM (
    SELECT
          COUNT(CASE WHEN EntryYear = 1 AND hr8_shift_no = 1 THEN 1 END) AS CurYRshift_1_8hr
        , COUNT(CASE WHEN EntryYear = 1 AND hr8_shift_no = 2 THEN 1 END) AS CurYRshift_2_8hr
        , COUNT(CASE WHEN EntryYear = 1 AND hr8_shift_no = 3 THEN 1 END) AS CurYRshift_3_8hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr8_shift_no = 1 THEN 1 END) AS PreYRshift_1_8hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr8_shift_no = 2 THEN 1 END) AS PreYRshift_2_8hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr8_shift_no = 3 THEN 1 END) AS PreYRshift_3_8hr
        , COUNT(CASE WHEN EntryYear = 1 AND hr12_shift_no = 1 THEN 1 END) AS CurYRshift_1_12Hr
        , COUNT(CASE WHEN EntryYear = 1 AND hr12_shift_no = 2 THEN 1 END) AS CurYRshift_2_12Hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr12_shift_no = 1 THEN 1 END) AS PreYRshift_1_12Hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr12_shift_no = 2 THEN 1 END) AS PreYRshift_2_12Hr
		
    FROM (
            select
                  EntryYear
                , case
                    when EntryHour  < @8hrthirdshiftend  or EntryHour > @8hrthirdshiftstart then 3
                    when EntryHour >= @8hrfirstshiftstart and EntryHour < @8hrfirstshiftend then 1
                    else 2
                  end hr8_shift_no
                , case
                    when EntryHour >= @12hrfirstshiftstart and EntryHour < @12hrfirstshiftend then 1
                    else 2
                  end hr12_shift_no
            from e911data
            cross apply (select
                             datepart(hour,[EntryDateTime]) as EntryHour 
                           , datepart(year,[EntryDateTime]) - (@year-1) as EntryYear
                        ) ca
            where EntryDateTime >= dateadd(year,(@year-1901),'19000101') /* 1 Jan year before @ year e.g. 2015 */
              and EntryDateTime <  dateadd(year,(@year-1899),'19000101')  /* 1 Jan year after @ year  e.g. 2017 */
			  and Agency = @Agency
          ) AS derived
    ) AS d	
END

Open in new window

0
 
LVL 25

Accepted Solution

by:
Pawan Kumar earned 250 total points
ID: 41898279
try...

USE [CT_PSIMS]
GO
/****** Object:  StoredProcedure [dbo].[CR_911_Calls_ByShiftAndCount]    Script Date: 11/22/2016 09:46:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		Name
-- Create date: 
-- Description:	Find all 911 calls in E911Data per shift
-- =============================================
ALTER PROCEDURE [dbo].[CR_911_Calls_ByShiftAndCount] 
	-- Add the parameters for the stored procedure here
	@Agency int = 0, 
	@Year int = 0
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

	DECLARE @8hrfirstshiftstart as int  -- Don't need second shift values
	DECLARE @8hrfirstshiftend as int
	DECLARE @8hrthirdshiftstart as int
	DECLARE @8hrthirdshiftend as int

	DECLARE @12hrfirstshiftstart as int  -- Don't need second shift values
	DECLARE @12hrfirstshiftend as int

	SET @8hrfirstshiftstart = 7
	SET @8hrfirstshiftend = 15
	SET @8hrthirdshiftstart = 23
	SET @8hrthirdshiftend = 7
	SET @12hrfirstshiftstart = 7
	SET @12hrfirstshiftend = 19



    -- Insert statements for procedure here
SELECT
      CurYRshift_1_8hr
    , CurYRshift_2_8hr
    , CurYRshift_3_8hr
    , PreYRshift_1_8hr
    , PreYRshift_2_8hr
    , PreYRshift_3_8hr
    , CurYRshift_1_8hr - PreYRshift_1_8hr AS DiffShift_1_8hr
    , CurYRshift_2_8hr - PreYRshift_2_8hr AS DiffShift_2_8hr
    , CurYRshift_3_8hr - PreYRshift_3_8hr AS DiffShift_3_8hr
    , CurYRshift_1_12Hr
    , CurYRshift_2_12Hr
    , PreYRshift_1_12Hr
    , PreYRshift_2_12Hr
    , CurYRshift_1_12Hr - PreYRshift_1_12Hr AS DiffShift_1_12hr
    , CurYRshift_2_12Hr - PreYRshift_2_12Hr AS DiffShift_2_12hr
	, @8hrfirstshiftstart [8hrfirstshiftstart]
    , @8hrfirstshiftend  [8hrfirstshiftend]
    , @8hrthirdshiftstart [8hrthirdshiftstart]
    , @8hrthirdshiftend [8hrthirdshiftend]
    , @12hrfirstshiftstart [12hrfirstshiftstart]
    , @12hrfirstshiftend [12hrfirstshiftend]
FROM (
    SELECT
          COUNT(CASE WHEN EntryYear = 1 AND hr8_shift_no = 1 THEN 1 END) AS CurYRshift_1_8hr
        , COUNT(CASE WHEN EntryYear = 1 AND hr8_shift_no = 2 THEN 1 END) AS CurYRshift_2_8hr
        , COUNT(CASE WHEN EntryYear = 1 AND hr8_shift_no = 3 THEN 1 END) AS CurYRshift_3_8hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr8_shift_no = 1 THEN 1 END) AS PreYRshift_1_8hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr8_shift_no = 2 THEN 1 END) AS PreYRshift_2_8hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr8_shift_no = 3 THEN 1 END) AS PreYRshift_3_8hr
        , COUNT(CASE WHEN EntryYear = 1 AND hr12_shift_no = 1 THEN 1 END) AS CurYRshift_1_12Hr
        , COUNT(CASE WHEN EntryYear = 1 AND hr12_shift_no = 2 THEN 1 END) AS CurYRshift_2_12Hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr12_shift_no = 1 THEN 1 END) AS PreYRshift_1_12Hr
        , COUNT(CASE WHEN EntryYear = 0 AND hr12_shift_no = 2 THEN 1 END) AS PreYRshift_2_12Hr
		
    FROM (
            select
                  EntryYear
                , case
                    when EntryHour  < @8hrthirdshiftend  or EntryHour > @8hrthirdshiftstart then 3
                    when EntryHour >= @8hrfirstshiftstart and EntryHour < @8hrfirstshiftend then 1
                    else 2
                  end hr8_shift_no
                , case
                    when EntryHour >= @12hrfirstshiftstart and EntryHour < @12hrfirstshiftend then 1
                    else 2
                  end hr12_shift_no
            from e911data
            cross apply (select
                             datepart(hour,[EntryDateTime]) as EntryHour 
                           , datepart(year,[EntryDateTime]) - (@year-1) as EntryYear
                        ) ca
            where EntryDateTime >= dateadd(year,(@year-1901),'19000101') /* 1 Jan year before @ year e.g. 2015 */
              and EntryDateTime <  dateadd(year,(@year-1899),'19000101')  /* 1 Jan year after @ year  e.g. 2017 */
			  and Agency = @Agency
          ) AS derived
    ) AS d	
END

Open in new window

0
 

Author Closing Comment

by:MikeM670
ID: 41901679
Thanks to both of you for the help.  I split the points between both of you.  One for the original solution the other for the example.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

25 Experts available now in Live!

Get 1:1 Help Now