Solved

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

Posted on 2016-11-22
3
48 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 28

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL date incremented 11 31
What's wrong with this T-SQL Foreign Key? 7 46
Show Results for Latest DateTime in a View 27 25
access query to sql server 3 21
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
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

810 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