Return data from tables hourly

cloud97 · September 12, 2017, 5:26am

Hi experts,

I'm newbie in SQL. I have 3 tables, MST_PACK, MST_WPREC_TEST & MST_SESSION.

MST_PACK - records of data a packing machine can pack a product hourly
MST_WPREC_TEST - records of data total wrapper needed to wrap a product
MST_SESSION - records of data the machine operation session (from 07xx to 15xx as 1st session, 15xx to 23xx as 2nd session every day)

These 3 tables are required to calculate the wrapper wastage. Refer to below sample data.

IF OBJECT_ID('MST_WPREC_TEST', 'U') IS NOT NULL DROP TABLE MST_WPREC_TEST;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO

CREATE TABLE MST_WPREC_TEST(
	[WPREC_ID] [int] IDENTITY(1,1) NOT NULL,
	[WPREC_NOTE] [varchar](50) NULL,
	[WPREC_CODE] [varchar](15) NULL,
	[WPREC_REMARK] [varchar](50) NULL,
	[WPREC_TYPE] [varchar](15) NULL,
	[WPREC_WEIGHT] [float] NULL,
	[WPREC_COUNTER] [float] NULL,
	[WPREC_DATETIME] [datetime] NULL,
	[WPREC_USER] [varchar](15) NULL,
	[WPREC_VERIFY] [varchar](1) NULL,
	[WPREC_VERIFYTIME] [datetime] NULL,
	[WPREC_SUPERVISOR] [varchar](15) NULL,
	[SES_NOTE] [varchar](10) NULL
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT MST_WPREC_TEST ON 

INSERT MST_WPREC_TEST ([WPREC_ID], [WPREC_NOTE], [WPREC_CODE], [WPREC_REMARK], [WPREC_TYPE], [WPREC_WEIGHT], [WPREC_COUNTER], [WPREC_DATETIME], [WPREC_USER], [WPREC_VERIFY], [WPREC_VERIFYTIME], [WPREC_SUPERVISOR], [SES_NOTE]) 
VALUES (1, N'A000000001', N'APOPAK', N'A1', N'RECEIVE', 1.8, 25600, CAST(N'2017-04-21 07:11:41.000' AS DateTime), N'', N'', NULL, N'', N'A000000001')
,(2, N'A000000002', N'APOPAK', N'A1', N'RECEIVE', 1.8, 25600, CAST(N'2017-04-21 07:20:58.000' AS DateTime), N'', N'', NULL, N'', N'A000000001')
,(3, N'A000000003', N'APOPAK', N'A1', N'RECEIVE', 1.8, 25600, CAST(N'2017-04-21 08:15:30.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(4, N'A000000004', N'APOPAK', N'A1', N'RECEIVE', 1.8, 25600, CAST(N'2017-04-21 08:24:39.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(5, N'A000000005', N'APOPAK', N'A1', N'RECEIVE', 1.8, 25600, CAST(N'2017-04-21 09:29:22.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(6, N'A000000006', N'APOPAK', N'A1', N'RECEIVE', 1.8, 25600, CAST(N'2017-04-21 09:31:24.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(7, N'A000000007', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 10:13:30.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(8, N'A000000008', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 10:30:31.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(9, N'A000000009', N'APOPAK', N'A1', N'Return', 1.8, 25600, CAST(N'2017-04-21 11:12:16.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(10, N'A000000010', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 11:18:24.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(11, N'A000000011', N'APOPAK', N'A1', N'Receive', 1.8, 26505, CAST(N'2017-04-21 12:15:11.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(12, N'A000000012', N'APOPAK', N'A1', N'Receive', 1.8, 26505, CAST(N'2017-04-21 12:20:43.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000001')
,(13, N'A000000013', N'APOPAK', N'A1', N'Receive', 1.8, 26505, CAST(N'2017-04-21 13:16:02.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000001')
,(14, N'A000000014', N'APOPAK', N'A1', N'Receive', 1.8, 26505, CAST(N'2017-04-21 13:18:18.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000001')
,(15, N'A000000015', N'APOPAK', N'A1', N'Receive', 1.8, 26565, CAST(N'2017-04-21 14:18:32.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000001')
,(16, N'A000000016', N'APOPAK', N'A1', N'Receive', 1.8, 26565, CAST(N'2017-04-21 14:23:27.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000001')
,(17, N'A000000017', N'APOPAK', N'A1', N'Receive', 1.8, 29310, CAST(N'2017-04-21 15:09:26.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000001')
--,(18, N'A000000018', N'APOPAK', N'A2', N'Receive', 1.8, 25600, CAST(N'2017-04-21 15:29:57.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000078')
,(18, N'A000000018', N'APOPAK', N'A1', N'Return', 1.7, 25600, CAST(N'2017-04-21 16:25:49.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000078')
,(19, N'A000000019', N'APOPAK', N'A2', N'Return', 1.8, 25600, CAST(N'2017-04-21 16:31:22.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000078')
,(20, N'A000000020', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 17:11:43.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000078')
,(21, N'A000000021', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 17:23:35.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000078')
,(22, N'A000000022', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 18:19:42.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000078')
,(23, N'A000000023', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 18:31:28.000' AS DateTime), N'suppda1', N'Y', CAST(N'2017-04-21 18:40:10.000' AS DateTime), N'suppda1', N'A000000078')
,(24, N'A000000024', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 19:13:26.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000078')
,(25, N'A000000025', N'APOPAK', N'A1', N'Receive', 1.8, 25600, CAST(N'2017-04-21 19:36:34.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000078')
,(26, N'A000000026', N'APOPAK', N'A1', N'RECEIVE', 1.8, 26505, CAST(N'2017-04-21 20:12:01.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000078')
,(27, N'A000000027', N'APOPAK', N'A1', N'RECEIVE', 1.8, 26505, CAST(N'2017-04-21 20:36:52.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000078')
,(28, N'A000000028', N'APOPAK', N'A1', N'Receive', 1.8, 26505, CAST(N'2017-04-21 21:13:22.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000078')
,(29, N'A000000029', N'APOPAK', N'A1', N'Receive', 1.8, 26505, CAST(N'2017-04-21 21:20:40.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000078')
,(30, N'A000000030', N'APOPAK', N'A1', N'Receive', 2.4, 26565, CAST(N'2017-04-21 22:16:02.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000078')
,(31, N'A000000031', N'APOPAK', N'A1', N'Receive', 2.5, 26565, CAST(N'2017-04-21 22:29:28.000' AS DateTime), N'pda1', N'', NULL, N'', N'A000000078')
,(32, N'A000000032', N'APOPAK', N'A1', N'Receive', 1.8, 29310, CAST(N'2017-04-21 23:14:40.000' AS DateTime), N'suppda1', N'', NULL, N'', N'A000000078')
,(33, N'A000000033', N'APOPAK', N'A1', N'Receive', 1.8, 55699, CAST(N'2017-04-22 07:15:41.000' AS DateTime), N'suppda1', N'', NULL, N'', N'')
,(34, N'A000000034', N'APOPAK', N'A1', N'Receive', 1.8, 56669, CAST(N'2017-04-22 07:20:36.000' AS DateTime), N'suppda1', N'', NULL, N'', N'')
,(35, N'A000000035', N'APOPAK', N'A1', N'Receive', 1.8, 55699, CAST(N'2017-04-22 08:13:28.000' AS DateTime), N'suppda1', N'', NULL, N'', N'')
,(36, N'A000000036', N'APOPAK', N'A1', N'Receive', 1.8, 55699, CAST(N'2017-04-22 08:20:03.000' AS DateTime), N'suppda1', N'Y', NULL, N'suppda1', N'')
,(37, N'A000000037', N'APOPAK', N'A1', N'Receive', 1.8, 55699, CAST(N'2017-04-22 09:17:14.000' AS DateTime), N'suppda1', N'Y', NULL, N'suppda1', N'')
,(38, N'A000000038', N'APOPAK', N'A1', N'Receive', 1.8, 55633, CAST(N'2017-04-22 09:25:03.000' AS DateTime), N'suppda1', N'Y', NULL, N'suppda1', N'')
,(39, N'A000000039', N'APOPAK', N'A1', N'Receive', 1.8, 55699, CAST(N'2017-04-22 10:16:49.000' AS DateTime), N'suppda1', N'Y', NULL, N'suppda1', N'')
,(40, N'A000000040', N'APOPAK', N'B1', N'Receive', 1.88, 55699, CAST(N'2017-04-22 10:30:04.000' AS DateTime), N'suppda1', N'', NULL, N'', N'')

GO
SET IDENTITY_INSERT MST_WPREC_TEST OFF


IF OBJECT_ID('MST_PACK', 'U') IS NOT NULL DROP TABLE MST_PACK;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO


CREATE TABLE MST_PACK(
	[RP_PRODUCT] [varchar](50) NULL,
	[RP_START] [datetime] NULL,
	[RP_END] [datetime] NULL,
	[RP_TOTAL] [int] NULL, --Actual count of product packed by packing machine
	[RP_SPEC] [int] NULL,  --Amount of packing machine can pack a product theoretically
	[RP_PER] [float] NULL  --The packing machine efficiency, [RP_TOTAL] * 100 / [RP_SPEC]
) ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT MST_PACK ON 
GO

INSERT MST_PACK ([RP_PRODUCT], [RP_START], [RP_END], [RP_TOTAL], [RP_SPEC], [RP_PER]) 
VALUES (N'JK CURRY', CAST(N'2017-04-21 07:10:20.000' AS DateTime), CAST(N'2017-04-21 08:10:20.000' AS DateTime), 845, 880, 96.02)
,(N'JK CURRY', CAST(N'2017-04-21 08:10:20.000' AS DateTime), CAST(N'2017-04-21 09:10:20.000' AS DateTime), 845, 880, 96.02)
,(N'JK CURRY', CAST(N'2017-04-21 09:10:20.000' AS DateTime), CAST(N'2017-04-21 10:10:20.000' AS DateTime), 845, 880, 96.02)
,(N'JK CURRY', CAST(N'2017-04-21 10:10:20.000' AS DateTime), CAST(N'2017-04-21 11:10:20.000' AS DateTime), 845, 880, 96.02)
,(N'MK CURRY', CAST(N'2017-04-21 11:10:20.000' AS DateTime), CAST(N'2017-04-21 12:10:20.000' AS DateTime), 845, 880, 96.02)
,(N'JK CURRY', CAST(N'2017-04-21 12:10:20.000' AS DateTime), CAST(N'2017-04-21 13:10:20.000' AS DateTime), 845, 880, 96.02)
,(N'JK CURRY', CAST(N'2017-04-21 13:10:20.000' AS DateTime), CAST(N'2017-04-21 14:10:20.000' AS DateTime), 870, 880, 98.86)
,(N'JK CURRY', CAST(N'2017-04-21 14:10:20.000' AS DateTime), CAST(N'2017-04-21 15:10:20.000' AS DateTime), 870, 880, 98.86)
,(N'JK CURRY', CAST(N'2017-04-21 15:20:06.000' AS DateTime), CAST(N'2017-04-21 16:20:06.000' AS DateTime), 870, 880, 98.86)
,(N'MNS 30C CHIC 10X40X25G', CAST(N'2017-04-21 16:20:06.000' AS DateTime), CAST(N'2017-04-21 17:20:06.000' AS DateTime), 845, 880, 96.02)
,(N'MNS 30C CHIC 10X40X25G', CAST(N'2017-04-21 17:20:06.000' AS DateTime), CAST(N'2017-04-21 18:20:06.000' AS DateTime), 845, 880, 96.02)
,(N'MNS 30C CHIC 10X40X25G', CAST(N'2017-04-21 18:20:06.000' AS DateTime), CAST(N'2017-04-21 19:20:06.000' AS DateTime), 845, 880, 96.02)
,(N'MNS 30C CHIC 10X40X25G', CAST(N'2017-04-21 19:20:06.000' AS DateTime), CAST(N'2017-04-21 20:20:06.000' AS DateTime), 870, 880, 98.86)
,(N'MNS 30C CHIC 10X40X25G', CAST(N'2017-04-21 20:20:06.000' AS DateTime), CAST(N'2017-04-21 21:20:06.000' AS DateTime), 870, 880, 98.86)
,(N'MNS 30C CHIC 10X40X25G', CAST(N'2017-04-21 21:20:06.000' AS DateTime), CAST(N'2017-04-21 22:20:06.000' AS DateTime), 870, 880, 98.86)
,(N'MNS 30C CHIC 10X40X25G', CAST(N'2017-04-21 22:20:06.000' AS DateTime), CAST(N'2017-04-21 23:20:06.000' AS DateTime), 870, 880, 98.86)


GO
SET IDENTITY_INSERT MST_PACK OFF
GO


IF OBJECT_ID('MST_SESSION_TEST', 'U') IS NOT NULL DROP TABLE MST_SESSION_TEST; 
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO


CREATE TABLE MST_SESSION_TEST(
[SES_ID] [int] IDENTITY(1,1) NOT NULL,
[SES_NOTE] [varchar](10) PRIMARY KEY CLUSTERED NOT NULL,
[SES_START] [datetime] NULL,
[SES_END] [datetime] NULL
) ON [PRIMARY]
GO
SET IDENTITY_INSERT MST_SESSION_TEST ON 
GO
INSERT MST_SESSION_TEST ([SES_ID], [SES_NOTE], [SES_START], [SES_END]) 
VALUES (1, N'A000000001', CAST(N'2017-04-21 07:10:20.000' AS DateTime), CAST(N'2017-04-21 15:10:33.000' AS DateTime))
, (2, N'A000000078', CAST(N'2017-04-21 15:20:06.000' AS DateTime), CAST(N'2017-04-21 23:20:36.000' AS DateTime))
GO
SET IDENTITY_INSERT MST_SESSION_TEST OFF
GO

Desire result as below:

WPREC_CODE   WPR_START_TIME  COUNTER/HOUR  WRAP_USED/HOUR  WASTAGE(%)
  APOPAK    2017-04-21 0700     25600          25350         0.98
  APOPAK    2017-04-21 0800     25600          25350         0.98
  APOPAK    2017-04-21 0900     25600          25350         0.98
  APOPAK    2017-04-21 1000     25600          25350         0.98  
  APOPAK    2017-04-21 1100     25600          25350         0.98  
  APOPAK    2017-04-21 1200     26505          25350         4.36
  APOPAK    2017-04-21 1300     26505          25350         4.36
  APOPAK    2017-04-21 1400     26505          26100         1.53
  APOPAK    2017-04-21 1500     29310          26100        10.95
  APOPAK    2017-04-21 1600     26565          26100         1.75
  APOPAK    2017-04-21 1700     25600          25350         0.98
  APOPAK    2017-04-21 1800     25600          25350         0.98
  APOPAK    2017-04-21 1900     25600          25350         0.98
  APOPAK    2017-04-21 2000     26505          26100         1.53
  APOPAK    2017-04-21 2100     26505          26100         1.53
  APOPAK    2017-04-21 2200     26565          26100         1.75
  APOPAK    2017-04-21 2300     29310          26100        10.95

where

WPREC_CODE direct from table MST_WPREC_TEST
Data of COUNTER/HOUR come from WPREC_COUNTER in table MST_WPREC_TEST (* just retrieve one unique WPREC_COUNTER value within hourly time)
Value of WRAP_USED/HOUR come from RP_TOTAL in table MST_PACK with each entry value need to times variable (assume as 30 pack). Eg, 845 * 30 = 25,350.
WASTAGE = [(COUNTER/HOUR - WRAP_USED/HOUR) * 100] / COUNTER/HOUR

No idea where to start. Appreciate someone can help.

bitsmed · September 12, 2017, 4:50pm

I'm not able to get your desired result, but this may get you started:

with ses
  as (select min(ses_start) as ses_start
            ,max(ses_end) as ses_end
        from mst_session_test
       group by datediff(day,ses_start,current_timestamp)
     )
    ,wprec
  as (select b.wprec_code
            ,dateadd(hour,datediff(hour,0,b.wprec_datetime),0) as dt
            ,b.wprec_counter
        from ses as a
             inner join mst_wprec_test as b
                     on b.wprec_datetime>=a.ses_start
                    and b.wprec_datetime<=a.ses_end
       group by b.wprec_code
               ,b.wprec_counter
               ,datediff(hour,0,b.wprec_datetime)
     )
    ,pack
  as (select sum(b.rp_total) as rp_total
            ,dateadd(hour,datediff(hour,0,b.rp_start),0) as dt
        from ses as a
             inner join mst_pack as b
                     on b.rp_start>=a.ses_start
                    and b.rp_end<=a.ses_end
       group by datediff(hour,0,b.rp_start)
     )
select a.wprec_code
      ,a.dt
      ,a.wprec_counter
      ,b.rp_total*30
      ,round(100-b.rp_total*3000/a.wprec_counter,2)
  from wprec as a
       left outer join pack as b
                    on b.dt=a.dt
 order by a.dt
;

cloud97 · September 13, 2017, 6:59am

Works great although result got some wrong data captured. Million thanks @bitsmed. Below is the result.

wprec_code			dt	        wprec_counter (No column name)	(No column name)
APOPAK	2017-04-21 07:00:00.000		25600		25350				0.98
APOPAK	2017-04-21 08:00:00.000		25600		25350				0.98
APOPAK	2017-04-21 09:00:00.000		25600		25350				0.98
APOPAK	2017-04-21 10:00:00.000		25600		25350				0.98
APOPAK	2017-04-21 11:00:00.000		25600		25350				0.98
APOPAK	2017-04-21 12:00:00.000		26505		25350				4.36
APOPAK	2017-04-21 13:00:00.000		26505		26100				1.53
APOPAK	2017-04-21 14:00:00.000		26565		26100				1.75
APOPAK	2017-04-21 15:00:00.000		29310		26100				10.95
APOPAK	2017-04-21 16:00:00.000		25600		25350				0.98
APOPAK	2017-04-21 17:00:00.000		25600		25350				0.98
APOPAK	2017-04-21 18:00:00.000		25600		25350				0.98
APOPAK	2017-04-21 19:00:00.000		25600		26100				-1.95
APOPAK	2017-04-21 20:00:00.000		26505		26100				1.53
APOPAK	2017-04-21 21:00:00.000		26505		26100				1.53
APOPAK	2017-04-21 22:00:00.000		26565		26100				1.75
APOPAK	2017-04-21 23:00:00.000		29310		NULL				NULL

cloud97 · September 15, 2017, 1:03am

Still cannot get the correct data starting from 2017-04-21 13:00:00 onward...
Any advice and suggestions will be greatly appreciated.

bitsmed · September 15, 2017, 5:39pm

On this line of your sample data in table mst_pack:

RP_PRODUCT RP_START                RP_END                  RP_TOTAL RP_SPEC RP_PER
JK CURRY   2017-04-21 07:10:20.000 2017-04-21 08:10:20.000 845      880     96.02

does this "belong" to time 7:00 or 8:00?

Now, if you answer 7:00, then this:

RP_PRODUCT RP_START                RP_END                  RP_TOTAL RP_SPEC RP_PER
JK CURRY   2017-04-21 13:10:20.000 2017-04-21 14:10:20.000 870      880     98.86

should "belong" to time 13:00, but multiplying RP_TOTAL by 30 gives 26100. Not 25300 as you showed in your expected result.

If you answer 8:00 to the first row, then where you you get row for time 7:00 from?

cloud97 · March 1, 2018, 8:51am

Very sorry I've been absent for so long. I being relocated to other projects.

Agreed with your explanation.

Thanks

cloud97 · March 15, 2018, 3:41am

bitsmed, need your help & guideline again.

I created a stored procedure with add-on parameters/variables @ pck (packet size, as I assume 30) & @SESSION_ID. I'm trying to insert results of stored procedure into temp table but getting errors:

Msg 11529, Level 16, State 1, Procedure sp_describe_first_result_set, Line 1
The metadata could not be determined because every code path results in an error; see previous errors for some of these.
Msg 2812, Level 16, State 62, Procedure sp_describe_first_result_set, Line 1
Could not find stored procedure 'dbo.WRP_RESULT'.

from stored procedure:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[WRP_RESULT](@PCK INT, @SESSION_ID VARCHAR(11))--PCK - packet size, 
AS
BEGIN

/*******************************************************************************************************************/

/*IF OBJECT_ID ('TMP_REPORT_WRP', 'U') IS NOT NULL DROP TABLE TMP_REPORT_WRP
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
SET ANSI_PADDING ON

CREATE TABLE [dbo].[TMP_REPORT_WRP](
	[RP_WPREC_CODE] [varchar](50) NULL,
	[RP_DATETIME] [datetime] NULL,
	[RP_MACH_COUNTER] [int] NULL,
	[RP_ACC_WRP_USED] [int] NULL,
	[RP_WASTAGE] [FLOAT] NULL
) ON [PRIMARY]


SET ANSI_PADDING OFF

*/

/********************************************************************************************************************/

declare @SES_ID varchar (11);
declare @pck_size int;

set @SES_ID = @SESSION_ID;
set @pck_size = @PCK;

BEGIN

with ses  --Determine session start & end datetime base on inputed session note
  as (select min(ses_start) as ses_start
            ,max(ses_end) as ses_end
        from mst_session
		where SES_NOTE=@SES_ID 
       group by datediff(day,ses_start,current_timestamp)
     )
    ,wprec --wprec_counter is "counter/hour" from machine reading
  as (select b.wprec_code
            ,dateadd(hour,datediff(hour,0,b.wprec_datetime),0) as dt
            ,b.wprec_counter
			--,b.rp_start
	        --,b.rp_end
        from ses as a
             inner join mst_wprec as b
                     on b.wprec_datetime>=a.ses_start
                    and b.wprec_datetime<=a.ses_end
       group by b.wprec_code
               ,b.wprec_counter
               ,datediff(hour,0,b.wprec_datetime)
     )
    ,pack
  as (select sum(b.rp_total) as rp_total
            ,dateadd(hour,datediff(hour,0,b.rp_start),0) as dt
			
        from ses as a
             inner join TMP_REPORT as b
                     on b.rp_start>=a.ses_start
                    and b.rp_end<=a.ses_end
       group by datediff(hour,0,b.rp_start)
     )
select a.wprec_code as 'WPREC_CODE'
      ,a.dt AS 'DATETIME'
      ,a.wprec_counter AS 'MACH_COUNTER'
	  --,b.rp_start
	  --,b.rp_end
      ,b.rp_total*@pck_size as 'ACTUAL WRP USED'
      ,round(100-b.rp_total*@pck_size*100/a.wprec_counter,2) as 'WASTAGE (ACC)'
  from wprec as a
       left outer join pack as b
                    on b.dt=a.dt
 order by a.dt


 END
/***********************************************************************************************************************/

SELECT *
INTO #temp
FROM OPENQUERY(LOCALSERVER, 'set fmtonly off exec dbo.WRP_RESULT')

SELECT *FROM #temp

END

bitsmed · March 15, 2018, 11:14am

Tried to emulate your "project" and it works fine here.

Do you get an error if you run this in SSMS?
exec dbo.WRP_RESULT

cloud97 · March 16, 2018, 12:25am

Run fine without insert to temp table script.