Using 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
Struggling with Rich Text Field
Hello Community, I have a SQL table which has a column containing rich text field tags. I am trying for 2 days now to get rid of the tags but nothing is working for me. <div class="ExternalClass00D082CB77D043AC975C783B3BC9B418">PR approval process complete, currently with procurement. EU to provide the Scope and SSJ. Else Procurement with reject the PR.</div> Below is how the text appears while in the actual website, the data appears like this: The removal process cannot be hard-coded because every record tags are different from another. For example see the below example: <div class="ExternalClass156752DF6D8A4BBF84D75235021E4A45">Demand status updated to Under Tech. Procurement - Pending PR Creation <div class="ExternalClass1A849FEC18A44CCE90E425ED0F54A1A2"><html> <p>One time Support Request.<br></p></html></div> </div> *I have attached the actual output of this from website If anyone can help me on how to clean the characters with a SQL function or maybe a C# code that will be very helpful. I have tried the below links and they do not solve my problem. https://www.codeproject.com/Tips/821281/Convert-RTF-to-Plain-Text-Revised-Again https://social.msdn.microsoft.com/Forums/en-US/143abeb5-f98a-4b47-a746-dd141bdd6df7/sql-query-to-retrieve-a-rich-text-memo-field?forum=sqlgetstarted Any help on this issue will be very much appreciated.Access syntax migrating to T-SQL
I am migrating an insert statement and came upon what looks like Access syntax. While I scratch my head, "Why does that look like a column update inside an insert statement?" What would be the appropriate translation T-SQL Statement? my guess is get rid of what looks like the SET operation round(GreenSampleWeight....) This is what it looks like in the GUI: This is what it looks like in T-SQL
