question

BHVS-3227 avatar image
0 Votes"
BHVS-3227 asked MelissaMa-msft commented

TSql query for Time

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](14) 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.

sql-server-transact-sql
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @BHVS-3227,

Could you please validate all the answers so far and provide any update?

If both are not working, please provide more sample data and expected output.

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread. 

Thank you for understanding!

Best regards,
Melissa

0 Votes 0 ·
MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

Hi @BHVS-3227,

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:

113578-output.png

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.



output.png (4.5 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.