Skip to content
Fix Code Error

Tag: sql-server

Join Table 1 with Table 2 and the Table 2 has two foreign key column in each raw

July 17, 2021 by Code Error

Posted By: Anonymous I have two Tables as given below – I would like to write a SQL query (SQL server Database) to get the data in Result table format. Please help me to write the SQL query which is follows the best practice rule. Solution Join table2 simply twice SELECT Pincode,t2.Lcationname,t3.Lcationname FROM Table1 t1 …

Continue Reading

capture reoccurring seventh day in new column

July 16, 2021 by Code Error

Posted By: Anonymous I have the below table… run_dt check_type curr_cnt 6/1/21 ALL 50 5/31/21 ALL 25 5/26/21 ALL 43 5/25/21 ALL 70 6/1/21 SUB 23 5/25/21 SUB 49 I would like to capture the value of what the check_type was seven days from the run_dt. What was the previous weekday value. Something like… run_dt …

Continue Reading

SQL Help – Find all distinct IDs that are not equal to a value – There are multiple IDs that are the same

July 16, 2021 by Code Error

Posted By: Anonymous I am trying to return the IDs in a table where the IDs do not contain a value in another column. There are multiple IDs in the table that are the same, however the second column contains a different value. I want to return all of the distinct IDs where the second …

Continue Reading

Azure Sql : Unable to Replace HTML String

July 15, 2021 by Code Error

Posted By: Anonymous I have below html in the database <table> <tbody> <tr> <td> <table height="36" align="center" valign="middle" border="0" cellpadding="0" cellspacing="0" class="tablet-button"> <tbody> <tr> <td width="auto" align="center" valign="middle" height="36" style=" background-color: __CustomButtonBackgroundColor__; border-top-left-radius: 4px; border-bottom-left-radius: 4px; border-top-right-radius: 4px; border-bottom-right-radius: 4px; background-clip: padding-box; font-size: 15px; font-family: Helvetica, arial, sans-serif; text-align: center; color: __CustomButtonForegroundColor__; font-weight: 10; padding-left: …

Continue Reading

Using Datagrip with SqlServer: linked servers and autocomplete

July 15, 2021 by Code Error

Posted By: Anonymous I connect DataGrip (db IDE from JetBrains) to SqlServer instance which has links to another SqlServers (server objects -> linked servers), but DataGrip not see linked servers and their tables. Is it possible to enable autocomplete in DataGrip to work with a linked server in SqlServer? Solution It’s not possible in the …

Continue Reading

Power BI Bar char with time text values

July 15, 2021 by Code Error

Posted By: Anonymous I have a table with the following data create table #tbl (Product varchar(20),dates Datetime) insert into #tbl values (‘Fan’,’2014-09-15 07:24:37.460′), (‘Byke’,’2015-11-15 01:46:07.460′), (‘Book’,’2017-05-25 12:52:37.670′), (‘Pencil’,’2015-12-30 17:48:07.530′), (‘Shoes’,’2016-09-05 12:13:19.600′) select Product, format(dates,’MM:ss’) AS Times from #tbl drop table #tbl The time have been converted to text value in SQL query before I import …

Continue Reading

How to insert values into two SQL Server tables updating primary key and foreign key simultaneously, using a procedure?

July 13, 2021 by Code Error

Posted By: Anonymous I have something like this: CREATE TABLE [dbo].[table1] ( [id1] [int] IDENTITY(1,1) NOT NULL, [data] [varchar](255) NOT NULL, CONSTRAINT [PK_table1] PRIMARY KEY(id1) ) CREATE TABLE [dbo].[table2] ( [id2] [int] IDENTITY(1,1) NOT NULL, [id1] [int] , CONSTRAINT [PK_table2] PRIMARY KEY (id2) CONSTRAINT [FK_table2] FOREIGN KEY(id1) REFERENCES Table1 ) I want to add values …

Continue Reading

SQL Group by Year based on after and Before,

July 13, 2021 by Code Error

Posted By: Anonymous I have set of data Name, Id and Joined Date ,want to Group by Year 2019 before joined members and after joined members, need those counts. I tried below, Select Year(JoinedDate),Count(1) from UserDetails Group by Year(JoinedDate); It’s listed all the year, Please help me to get the solution. Actual Data: Expected Output: …

Continue Reading

Count maximum rows with FK in any 8-week period

July 13, 2021 by Code Error

Posted By: Anonymous A similar post does not quite meet my needs. I’m using SSMS (SQL server). I want to add a column for the count of appointments by patient within an 8-week period (ApptDate to ApptDate + 56). What’s the best way to go about this? (Any example code would be appreciated.) The data …

Continue Reading

Paging in sql server for top level query

July 12, 2021 by Code Error

Posted By: Anonymous In sql server I have an Employee table and an Address table. An Employee can have many Addresses. I want to get the first 10 Employees with their Addresses. SELECT * FROM Employee e LEFT JOIN Address a ON a.EmployeeID = e.Id WHERE a.Street LIKE ‘%a%’ AND e.Name LIKE ‘%bob%’ ORDER BY …

Continue Reading

Find rows with maximum count which have multiple joins in SQL

July 12, 2021 by Code Error

Posted By: Anonymous firstname lastname quantity object no datecol soman mitra 50 1 31-05-2021 nitya sharma 100 2 31-05-2021 tanisha agarwal 200 3 31-05-2021 tarun mittal 300 4 31-05-2021 Above is the output of multiple joined tables. Now, I want to find the rows which have the maximum quantity How can I do this since …

Continue Reading

File with .SCRIPT extension – Not running in SQL

July 12, 2021 by Code Error

Posted By: Anonymous I have a file named "CreateDatabase1.script". I want to create a database from this file but this file is not executable in SQL Server management studio. How can I run this file and create this database?? Solution You need to associate a file extension with SSMS Editor. Answered By: Anonymous

Continue Reading

How to union current table and history table order by recent edit

July 11, 2021 by Code Error

Posted By: Anonymous I am currently working on preparing audit table and the output structure I am planning to have is, latest record from current table and followed by associated(id) records from history table for all the records. So that user can see the latest changes and its history. At present I am using UNION …

Continue Reading

How to replace characters in JPQL @Query

July 11, 2021 by Code Error

Posted By: Anonymous My db has a table RESOURCE like: ID NAME 1 some_resource 2 another_resource And DEPARTMENTS ID NAME RESOURCE_NAME 1 dep1 some-resource 2 dep2 another-resource I need to be able to match them by resource name in a query, and in order to do so I was expecting to use the SQL Server …

Continue Reading

Does grouping keyword have different output in select and order by?

July 10, 2021 by Code Error

Posted By: Anonymous I am learning about grouping sets and the grouping function. When used in SELECT, grouping returns whether the column participates in aggregation on that row. Where as when used in order by, it can be used to sort the result by the column. Does grouping keyword have different output in select and …

Continue Reading

Can we concat as well as replace string values in SQL Server (SSMS)?

July 10, 2021 by Code Error

Posted By: Anonymous I executed below query, but I got result as null values. SELECT CAST(REPLACE(REPLACE(REPLACE(REPLACE(FIRST_NAME,’null’,”),’N/A’,”),’no’,”),’NA’,”) +’ ‘ + REPLACE(REPLACE(REPLACE(REPLACE(LAST_NAME,’null’,”),’N/A’,”),’no’,”),’NA’,”)) AS ‘FULL_NAME’ FROM NAMES Instead I want my output to be: FULL_NAME ___________ Ankit Kumar Mayank Sharma Puneet Kumar Rajesh Narender Robert Chahuhan Jaswinder Singh Rakesh Solution The more recent versions of SQL Server support …

Continue Reading

Select bottom n records and concatenate in same row

July 10, 2021 by Code Error

Posted By: Anonymous I have a select n bottom records in table and return a concatenate values in same row. This is code is ok but not return n bottom records: SELECT STUFF(( SELECT ‘; ‘ + ISNULL(Val1, ”) + ‘; ‘ + ISNULL(Val2, ”) + ‘; ‘ + ISNULL(Val3, ”) + ‘; ‘ + …

Continue Reading

How to get an output from stored procedure when condition is comparing string?

July 9, 2021 by Code Error

Posted By: Anonymous here is my stored procedure: ALTER PROCEDURE [dbo].[PR_City_SelectByPK] @CityName varchar AS SELECT [dbo].[City].[CityName], [dbo].[City].[State] FROM [dbo].[City] WHERE [dbo].[City].[CityName] = @CityName Primary key is CityName. I am trying to get CityName and State as output from the Stored procedure but I am not getting any output value in return. Solution Never use string …

Continue Reading

Get data and control records using AWS DMS with Kafka as the target

July 9, 2021 by Code Error

Posted By: Anonymous I am using SQL Server RDS as the source database and Apache-Kafka as the target in AWS DMS. I want to receive both the data and control records on every CDC changes made in the source database but I am only getting data records in case of CRUD commands and control record …

Continue Reading

How to get the only max row result from duplicate rows

July 7, 2021 by Code Error

Posted By: Anonymous I have results in table in this format. For this format my query is select distinct channel,location,user_loadtimestamp from [Email_Channels] I want the results in below format. Solution You want aggregation: select channel, location, max(user_loadtimestamp) from [Email_Channels] group by channel, location; Answered By: Anonymous

Continue Reading

How do insert records in a table that will require no values

July 7, 2021 by Code Error

Posted By: Anonymous How do insert records in a table that will require no values Create Table Orders ( OrderId int IDENTITY(1,1) PRIMARY KEY, PlacetAt time Default convert(varchar(10), GETDATE(), 108), Placeton date Default GETDATE(), Total money Default 0, ) Solution You can use default values: insert into Orders default values; Or, if you prefer, choose …

Continue Reading

SQL BCP Export XML To UTF-8 Format

July 7, 2021 by Code Error

Posted By: Anonymous I have a query that executes a stored procedure and exports the results to an XML file via BCP. The process works, except that the encoding of the XML file is showing as UCS-2 and I am told that the system I will be importing it into needs it to be UTF-8. …

Continue Reading

Filter Rank Based On Column Not being Ordered By

July 7, 2021 by Code Error

Posted By: Anonymous Here is some fake/sample data: DECLARE @tbl1 TABLE (PersonID VARCHAR(255), FirstNM VARCHAR(100), FileNBR VARCHAR(255)); INSERT INTO @tbl1 (PersonID, FirstNM, FileNBR) VALUES (‘456789’, ‘NONAME’, ‘FileNBR780’), (‘456789’, ‘John’, ‘FileNBR781’), (‘456789’, ‘NONAME’, ‘FileNBR783’), (‘476362’, ‘NONAME’, ‘FileNBR780’), (‘476362’, ‘Mary’, ‘FileNBR781’), (‘476362’, ‘Mary’, ‘FileNBR783’) PersonID FirstNM FileNBR 456789 NONAME FileNBR780 456789 John FileNBR781 456789 NONAME FileNBR783 476362 …

Continue Reading

SQL server: alter column from nvarchar to int

July 7, 2021 by Code Error

Posted By: Anonymous This table has filedate(nvarchar) and Filename. The format needs to be in MMDDYYYY for the filedate. I used replace(cast(filedate as varchar),’-‘,”) and changed the datatype to int, which worked, but not in the format that I need. Do I need to update the Fname and then try this or is there another …

Continue Reading

Maximum occurrence in sequence (advanced gaps and island problem)

July 7, 2021 by Code Error

Posted By: Anonymous I need to count maximum sequential occurrence of event with flag=1 for each hid in human. Sequence is generated by date. There is an example of my table: CREATE TABLE max_in_row ([hit_finish_dttm] varchar(13), [hid] varchar(13), [agent_login] varchar(57), [flg_no_talk] int) ; INSERT INTO max_in_row ([hit_finish_dttm], [hid], [agent_login], [flg_no_talk]) VALUES (‘2020-03-01’, ‘EQERR13’, ‘Dmitrii’, 0), …

Continue Reading

Storing the history of 1 or many columns in a row

July 7, 2021 by Code Error

Posted By: Anonymous I have a request to store the date on which a specific field was changed in a table. For example, In my dbo.User table, we need to know when the IsActive flag was changed. With history. I am proposing this: New schema – History. New table – [History].User_History CREATE TABLE [History].User_History ( …

Continue Reading

Change all bit columns to int with default value NULL

July 6, 2021 by Code Error

Posted By: Anonymous I have a database where we would store Yes/No questions as bits. However, as the project is going to a different path, we need to change all bit data types to int with default value NULL. Trying the following query, I get object is dependent of column error. ALTER TABLE dbo.Customers ALTER …

Continue Reading

NOT EXISTS with multiple criteria and indirect unique id in SQL Server

July 5, 2021 by Code Error

Posted By: Anonymous I am having a hard time understanding this query with NOT EXISTS in SQL Server. My colleague helped me with this query which works fine. There are 3 tables basically and I want to see if work_q_id exists in t_allocation table or not but t_allocation does not have a column (key) which …

Continue Reading

No mapping exists from object type System.__ComObject to a known managed provider native type. c#

July 5, 2021 by Code Error

Posted By: Anonymous I want to upload a pdf file from a scanner into a database (SQL Server), but I get the error message: System.ArgumentException: ‘No mapping exists from object type System.__ComObject to a known managed provider native type.’ I’m using Visual Studio 2019. This is my code: escanerDisponible = manejadorDisp.DeviceInfos[i]; Device dispositivo = escanerDisponible.Connect(); …

Continue Reading

Change multiple ids to table variable sql

July 5, 2021 by Code Error

Posted By: Anonymous I have sql query in which I am inserting multiple records based on how many Ids there are (few hundred): INSERT INTO dbo.ChangeBook (ID_Part, Price, ID_Client) SELECT d.ID_Parties, d.Price, a.TagId FROM dbo.Audit a CROSS JOIN (VALUES (1, 0.5), (3, 0.5), (5, 0.5), (6, 0.5), (8, 0.5), (9, 0.5) ) d(ID_Parties, Price) WHERE …

Continue Reading

Posts navigation

  • 1
  • 2
  • 3
  • 4
  • …
  • 47
  • Next

.net ajax android angular arrays aurelia backbone.js bash c++ css dataframe ember-data ember.js excel git html ios java javascript jquery json laravel linux list mysql next.js node.js pandas php polymer polymer-1.0 python python-3.x r reactjs regex sql sql-server string svelte typescript vue-component vue.js vuejs2 vuetify.js

  • you shouldn’t need to use z-index
  • No column in target database, but getting “The schema update is terminating because data loss might occur”
  • Angular – expected call-signature: ‘changePassword’ to have a typedeftslint(typedef)
  • trying to implement NativeAdFactory imports deprecated method by default in flutter java project
  • What should I use to get an attribute out of my foreign table in Laravel?
© 2022 Fix Code Error