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 …
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 …
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 …
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: …
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 …
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 …
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 …
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: …
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 …
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 …
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 …
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
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 …
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 …
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 …
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 …
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, ”) + ‘; ‘ + …
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 …
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 …
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
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 …
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. …
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 …
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 …
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), …
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 ( …
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 …
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 …
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(); …
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 …
