TSql query for Time

Question

Hi All,

I have a table time column(EMP_ADJ_SIGN_ON_TIME), I want to insert these time value PROD_UNTS into another table with time buckets like 7 to 8 and 8 to 9 columns.

CREATE TABLE [dbo].[EMPTIME](
[CDATE] [datetime] NOT NULL,
[PRDSTLE] char NULL,
[EMP_ADJ_SIGN_ON_TIME] [decimal](4, 2) NULL,
[PROD_UNTS] [int] NULL
)
GO

INSERT [dbo].[EMPTIME] ([CDATE], [PRDSTLE], [EMP_ADJ_SIGN_ON_TIME], [PROD_UNTS]) VALUES (CAST(N'2021-07-11 07:18:10.090' AS DateTime), N'ABC', CAST(7.18 AS Decimal(4, 2)), 14)
GO
INSERT [dbo].[EMPTIME] ([CDATE], [PRDSTLE], [EMP_ADJ_SIGN_ON_TIME], [PROD_UNTS]) VALUES (CAST(N'2021-07-11 07:56:10.090' AS DateTime), N'ABC', CAST(7.63 AS Decimal(4, 2)), 10)
GO
INSERT [dbo].[EMPTIME] ([CDATE], [PRDSTLE], [EMP_ADJ_SIGN_ON_TIME], [PROD_UNTS]) VALUES (CAST(N'2021-07-11 09:30:10.090' AS DateTime), N'SANTOOR', CAST(9.64 AS Decimal(4, 2)), 331)
GO
INSERT [dbo].[EMPTIME] ([CDATE], [PRDSTLE], [EMP_ADJ_SIGN_ON_TIME], [PROD_UNTS]) VALUES (CAST(N'2021-07-11 09:30:10.090' AS DateTime), N'SANTOOR', CAST(9.00 AS Decimal(4, 2)), 0)
GO
INSERT [dbo].[EMPTIME] ([CDATE], [PRDSTLE], [EMP_ADJ_SIGN_ON_TIME], [PROD_UNTS]) VALUES (CAST(N'2021-07-11 12:43:10.090' AS DateTime), N'CINTHOL ', CAST(12.43 AS Decimal(4, 2)), 0)
GO
INSERT [dbo].[EMPTIME] ([CDATE], [PRDSTLE], [EMP_ADJ_SIGN_ON_TIME], [PROD_UNTS]) VALUES (CAST(N'2021-07-11 11:22:10.090' AS DateTime), N'ABC ', CAST(11.22 AS Decimal(4, 2)), 215)
GO

Thanks in Advance.

Answer 1

Hi @BHVS ,

Welcome to Microsoft Q&A!

After checking, your insert statement was not the same as the source data and target data provided in the snapshots. The value of [PROD_UNTS] was 0 in two rows.

I updated them as below:

INSERT [dbo].[EMPTIME] ([CDATE], [PRDSTLE], [EMP_ADJ_SIGN_ON_TIME], [PROD_UNTS]) VALUES (CAST(N'2021-07-11 09:30:10.090' AS DateTime), N'SANTOOR', CAST(9.00 AS Decimal(4, 2)), 1)  
GO  
INSERT [dbo].[EMPTIME] ([CDATE], [PRDSTLE], [EMP_ADJ_SIGN_ON_TIME], [PROD_UNTS]) VALUES (CAST(N'2021-07-11 12:43:10.090' AS DateTime), N'CINTHOL ', CAST(12.43 AS Decimal(4, 2)), 22)  
GO  

Please also refer below query using sum(case when...) which is alternative to pivot.

select PRDSTLE,   
isnull(sum(case when h = 7 then PROD_UNTS end),0) as [7 A:M to 8 A:M],  
isnull(sum(case when h = 8 then PROD_UNTS end),0) as [8 A:M to 9 A:M],  
isnull(sum(case when h = 9 then PROD_UNTS end),0) as [9 A:M to 10 A:M],  
isnull(sum(case when h = 10 then PROD_UNTS end),0) as [10 A:M to 11 A:M],  
isnull(sum(case when h = 11 then PROD_UNTS end),0) as [11 A:M to 12 P:M],  
isnull(sum(case when h = 12 then PROD_UNTS end),0) as [Remaining time]  
from   
(select PRDSTLE, PROD_UNTS, IIF(EMP_ADJ_SIGN_ON_TIME>=12,12,cast(EMP_ADJ_SIGN_ON_TIME as int)) h from EMPTIME) a  
group by PRDSTLE  

Output:

Best regards,
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Try this possibility:

select PRDSTLE, 
    isnull([7], 0) as [7 AM to 8 AM],
    isnull([8], 0) as [8 AM to 9 AM],
    isnull([9], 0) as [9 AM to 10 AM],
    isnull([10], 0) as [10 AM to 11 AM],
    isnull([11], 0) as [11 AM to 12 PM],
    isnull([12], 0) as [Remaining time]
from
(
    select PRDSTLE, PROD_UNTS,
        case when EMP_ADJ_SIGN_ON_TIME >= 12 then 12 else floor(EMP_ADJ_SIGN_ON_TIME) end as h
    from EMPTIME
) t
pivot
(
    sum(PROD_UNTS) for h in ( [7], [8], [9], [10], [11], [12] )
) p

The rows are not ordered.