How do I unpivot so that the value column comes at the end?
hello all, I have learning the unpivot function, according to https://learn.microsoft.com/en-us/sql/t-sql/queries/from-using-pivot-and-unpivot?view=sql-server-ver16, the clause before the FOR keyword comes at the end. But when I do it, the column before the FOR keyboard comes before the column after the FOR keyboard. please see this example I did How can I make the value column to be the final column like the documentation? Thanks you.
Help in processing calculation with hhmmss
I need help to provide the following Expected finished time in hhmmss (Expected_finished_in_hhmmss) and Expected finished datetime (Expected_date). Request: If 8% records have been processed in 4:8:40 hh:mm:ss on 2025-01-028 15:55:17 then when 100% records will be processed. I needs result in hhmmss and finishing time after calculation. We'll have to add hhmmss in datetime etc. Expected result: Processed_% processed_on processed_in_hhmmss Expected_finished_in_hhmmss Expected_Date 8 2025-01-28 15:15:17 4:8:40Using a Sequence
Consider this table: CREATE TABLE [Events]( [EventID] [int] NULL, << Other columns >> and this Sequence: CREATE SEQUENCE [NewEventID] AS [int] START WITH 1 INCREMENT BY 1 MINVALUE 1 MAXVALUE 2147483647 NO CACHE and this Stored Procedure; CREATE PROCEDURE [Insert_Event] << Parameters >> AS BEGIN INSERT INTO [Events] ( EventID, << Other fields >> ) VALUES ( NEXT VALUE FOR NewEventID, << Other fields >> ) END GO When I run this procedure, I get this error message: NEXT VALUE FOR function cannot be used if ROWCOUNT option has been set, or the query contains TOP or OFFSET. None of those conditions are true so why am I getting this error message?
How to Create a Case Statement to Classify / Segment Data
Can someone show me how to create a case statement that will classify the sample data below to appear as follows: At the moment the table in SQL looks like the following: Sample data is as follows: CREATE TABLE #tmpTable ( Channel nvarchar(50), Months nvarchar(50), Total Sales int) INSERT #tmpTable VALUES (N'Online',N'Jan',1427), (N'Online',N'Feb',1486), (N'Online',N'Mar',1325), (N'Online',N'Apr',1065), (N'Online',N'May',803), (N'Online',N'Jun',661), (N'Online',N'Jul',665), (N'Online',N'Aug',912), (N'Online',N'Sep',1444), (N'Online',N'Oct',1689), (N'Online',N'Nov',1763), (N'Online',N'Dec',1440), (N'Social Media',N'Jan',1025), (N'Social Media',N'Feb',925), (N'Social Media',N'Mar',769), (N'Social Media',N'Apr',555), (N'Social Media',N'May',412), (N'Social Media',N'Jun',325), (N'Social Media',N'Jul',341), (N'Social Media',N'Aug',495), (N'Social Media',N'Sep',949), (N'Social Media',N'Oct',1126), (N'Social Media',N'Nov',1273), (N'Social Media',N'Dec',1067), (N'Outlet',N'Jan',125), (N'Outlet',N'Feb',96), (N'Outlet',N'Mar',73), (N'Outlet',N'Apr',88), (N'Outlet',N'May',65), (N'Outlet',N'Jun',53), (N'Outlet',N'Jul',45), (N'Outlet',N'Aug',66), (N'Outlet',N'Sep',145), (N'Outlet',N'Oct',199), (N'Outlet',N'Nov',202), (N'Outlet',N'Dec',181), (N'Stores',N'Jan',639), (N'Stores',N'Feb',641), (N'Stores',N'Mar',418), (N'Stores',N'Apr',398), (N'Stores',N'May',329), (N'Stores',N'Jun',306), (N'Stores',N'Jul',258), (N'Stores',N'Aug',398), (N'Stores',N'Sep',701), (N'Stores',N'Oct',879), (N'Stores',N'Nov',961), (N'Stores',N'Dec',818) SELECT * FROM #tmpTable Thank you
Stored Procedure Input Parameter Needed Power Automate
Hi, I currently have a Stored Procedure that does not have any Input Parameters, it basically updates a Calendar Table, setting columns like CurrentDay, PreviousDay, CurrentMonth etc... to 1 or 0 if the conditions matches. I wanted to use Power Automate to run this stored procedure at 12:00 everyday but it failed because Power Automate only executes Stored Procedures with an Input Parameter https://docs.microsoft.com/en-us/connectors/sql/#execute-stored-procedure-(v2) What is the most efficient way to add an Input Parameter to my current stored procedure but basically does nothing but still does the update is meant to do. Thanks
Azure Database creation using T-SQL or SSMS currently allows Unsupported characters in it's name.
If a Database created using SSMS or T-SQL has unsupported characters in it's name, then any Powershell/Azure CLI/Portal operation on it can fail. You may experience a few other kinds of unexpected behavior as well, with this specific database.How to collapse consecutive periods of dates that have the same value [Number] in one in T-SQL?
Hello. I have this data: USE [TEST] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[MyData]( [RES_UID] [uniqueidentifier] NULL, [Number] [numeric](5, 2) NULL, [StartDate] [date] NULL, [EndDate] [date] NULL, [Num] [bigint] NULL ) ON [PRIMARY] GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000000', CAST(1.00 AS Numeric(5, 2)), CAST(N'2014-09-12' AS Date), CAST(N'2017-02-28' AS Date), 1) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000000', CAST(1.00 AS Numeric(5, 2)), CAST(N'2017-03-01' AS Date), CAST(N'2017-10-31' AS Date), 2) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000000', CAST(1.00 AS Numeric(5, 2)), CAST(N'2017-11-01' AS Date), CAST(N'2018-10-31' AS Date), 3) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000000', CAST(1.00 AS Numeric(5, 2)), CAST(N'2018-11-01' AS Date), CAST(N'2020-08-31' AS Date), 4) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000000', CAST(1.00 AS Numeric(5, 2)), CAST(N'2020-09-01' AS Date), CAST(N'2021-09-30' AS Date), 5) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000000', CAST(1.00 AS Numeric(5, 2)), CAST(N'2021-10-01' AS Date), CAST(N'2049-12-31' AS Date), 6) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000001', CAST(1.00 AS Numeric(5, 2)), CAST(N'2019-10-14' AS Date), CAST(N'2021-09-30' AS Date), 1) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000001', CAST(1.00 AS Numeric(5, 2)), CAST(N'2021-10-01' AS Date), CAST(N'2049-12-31' AS Date), 2) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000002', CAST(1.00 AS Numeric(5, 2)), CAST(N'2016-03-15' AS Date), CAST(N'2018-10-31' AS Date), 1) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000002', CAST(1.00 AS Numeric(5, 2)), CAST(N'2018-11-01' AS Date), CAST(N'2020-01-19' AS Date), 2) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000002', CAST(1.00 AS Numeric(5, 2)), CAST(N'2020-01-20' AS Date), CAST(N'2020-03-01' AS Date), 3) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000002', CAST(1.00 AS Numeric(5, 2)), CAST(N'2020-03-02' AS Date), CAST(N'2020-08-02' AS Date), 4) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000002', CAST(1.00 AS Numeric(5, 2)), CAST(N'2020-08-03' AS Date), CAST(N'2049-12-31' AS Date), 5) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000003', CAST(1.00 AS Numeric(5, 2)), CAST(N'2017-09-20' AS Date), CAST(N'2019-07-31' AS Date), 1) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000003', CAST(1.00 AS Numeric(5, 2)), CAST(N'2019-08-01' AS Date), CAST(N'2020-08-02' AS Date), 2) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000003', CAST(1.00 AS Numeric(5, 2)), CAST(N'2020-08-03' AS Date), CAST(N'2021-06-30' AS Date), 3) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000003', CAST(1.00 AS Numeric(5, 2)), CAST(N'2021-07-01' AS Date), CAST(N'2021-08-20' AS Date), 4) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000003', CAST(0.00 AS Numeric(5, 2)), CAST(N'2021-08-21' AS Date), CAST(N'2049-12-31' AS Date), 5) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000004', CAST(1.00 AS Numeric(5, 2)), CAST(N'2019-11-11' AS Date), CAST(N'2020-07-01' AS Date), 1) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000004', CAST(1.00 AS Numeric(5, 2)), CAST(N'2020-07-02' AS Date), CAST(N'2020-08-02' AS Date), 2) GO INSERT [dbo].[MyData] ([RES_UID], [Number], [StartDate], [EndDate], [Num]) VALUES (N'00000000-0000-0000-0000-000000000004', CAST(1.00 AS Numeric(5, 2)), CAST(N'2020-08-03' AS Date), CAST(N'2049-12-31' AS Date), 3) GO How to collapse consecutive periods of dates that have the same value [Number] in one in T-SQL? The result table is: USE [TEST] GO SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TABLE [dbo].[MyData2]( [RES_UID] [uniqueidentifier] NULL, [Number] [numeric](5, 2) NULL, [StartDate] [date] NULL, [EndDate] [date] NULL ) ON [PRIMARY] GO INSERT [dbo].[MyData2] ([RES_UID], [Number], [StartDate], [EndDate]) VALUES (N'00000000-0000-0000-0000-000000000000', CAST(1.00 AS Numeric(5, 2)), CAST(N'2014-09-12' AS Date), CAST(N'2049-12-31' AS Date)) GO INSERT [dbo].[MyData2] ([RES_UID], [Number], [StartDate], [EndDate]) VALUES (N'00000000-0000-0000-0000-000000000001', CAST(1.00 AS Numeric(5, 2)), CAST(N'2019-10-14' AS Date), CAST(N'2049-12-31' AS Date)) GO INSERT [dbo].[MyData2] ([RES_UID], [Number], [StartDate], [EndDate]) VALUES (N'00000000-0000-0000-0000-000000000002', CAST(1.00 AS Numeric(5, 2)), CAST(N'2016-03-15' AS Date), CAST(N'2049-12-31' AS Date)) GO INSERT [dbo].[MyData2] ([RES_UID], [Number], [StartDate], [EndDate]) VALUES (N'00000000-0000-0000-0000-000000000003', CAST(1.00 AS Numeric(5, 2)), CAST(N'2017-09-20' AS Date), CAST(N'2021-08-20' AS Date)) GO INSERT [dbo].[MyData2] ([RES_UID], [Number], [StartDate], [EndDate]) VALUES (N'00000000-0000-0000-0000-000000000003', CAST(0.00 AS Numeric(5, 2)), CAST(N'2021-08-21' AS Date), CAST(N'2049-12-31' AS Date)) GO INSERT [dbo].[MyData2] ([RES_UID], [Number], [StartDate], [EndDate]) VALUES (N'00000000-0000-0000-0000-000000000004', CAST(1.00 AS Numeric(5, 2)), CAST(N'2019-11-11' AS Date), CAST(N'2049-12-31' AS Date)) GO With best regards, VasilyComo hacer un UNPIVOT de más de una columna
Tengo que crear una vista en SQL Server haciendo un UNPIVOT de una tabla y no consigo hacerlo correctamente. A partir de una tabla origen como la primera, tengo que conseguir la segunda: ¿Cómo tendría que ser la consulta SQL para conseguir esta vista a partir de la tabla original? Los colores es simplemente para saber como se tienen que distribuir los valores en la consulta resultado. Cualquier ayuda será bien recibida. Muchas gracias!
Convert ASCII to TEXT
Hi New to SQL here I have a table with a column that have ASCII codes in the following format 76, 71, 32, 72, 68, 32, 80, 76, 85, 83, 0, 0, 0 How do i convert this to standard TEXT. Every number represents a character, in the end i need the text (without the commas) that this represents. Please help with the T-SQL Regards
