Tag: tsql

SQL Server – Join across several tables

Below are the relevant tables info: tgenie Guid | genietypeguid | … and : tgenieType guid | genietype | … I made this query to select all possible entries: select tsearch.description, tcompany.CompanyName, tsearch.CompanyGUID, tgenie.GenieNotes, tGenieType.GenieType from tcompany, tsearch, tGenieType left outer join tGenie on tgenie.GenieTypeGUID = tGenieType.GUID As the result I have this output: description […]

Is there a way to retrieve inserted identity as well as some values from the query in an INSERT SELECT?

I have a situation in which I need to insert some values from a query into a table that has an identity PK. For some of the records, I need also to insert values in another table which has a 1-to-1 (partial) relationship: CREATE TABLE A ( Id int identity primary key clustered, Somevalue varchar(100), […]

Joining 3 tables and removing nulls from 2nd and 3rd table in SQL Server

I would like to join 3 tables and remove null in second and third tables where records doesn’t match due to join. I kind of got the result but not able to remove null and hence why when I take this query into SSRS I am losing a record. Here are my 3 tables and […]

String manipulation in SQL

I’m using SQL server 2014.I have a column that has text data in it. The data would look like this: create table #temp ( stringdata varchar(100) NULL ) insert into #temp values (‘CN=ABCD,PN=XYZ,AD=123,AN=rst’), (‘AN=ABC,PN=XYZ,CN=12,AN=rst’), (‘AN=ABC,CN=XYZ,PN=123,AN=rst’), (‘AN=ABC,AN=XYZ,CN=1234567,PN=rst’) And I need the result set value that is mapped for CN. I tried to extract the data with […]

A way to not repeat expressions in a SQL Server string manipulation query

I have the following sql: SUBSTRING(Location, CHARINDEX(‘ : ‘, Location) + 3, CHARINDEX(‘ ms – ‘, Location) – (CHARINDEX(‘ : ‘, Location) + 3)) (Location could be something like this: 1dasev : 232 ms – 323-asv3R and the substring would return 232) If I wrote this in C# I would not re-calculate CHARINDEX(‘ : ‘, […]

Select only if more than X occurences

My data is something like this: Client Number | Order Date | Order Amount | Sequence (created with Row_Number()) I have created a sequence with Row_Number(), so I can see how many orders a client has. If I use WHERE Sequence > 3, I lose the orders prior to 3. I can’t use HAVING because […]

SQL – Value difference between specific rows

My query is as follows SELECT LEFT(TimePeriod,6) Period, — string field with YYYYMMDD SUM(Value) Value FROM f_Trans_GL WHERE Account = 228 GROUP BY TimePeriod And it returns Period Value ————— 201412 80 201501 20 201502 30 201506 50 201509 100 201509 100 I’d like to know the Value difference between rows where the period is […]

Query to loop through one table and insert incrementally by group into another

I need some help with a sql query. I’ve got two simple tables, one that contains Channels, each with a unique identifier (ChannelID), and another one to many ID (OtherID). The second one will contain a sort order of each channel as it appears in another query. This sorting table is currently empty and my […]

Procedure or function has too many arguments specified SSRS

For some reason I am able to run my SELECT statement in SSMS, but when I use it in SSRS then it says Procedure or function has too many arguments specified. If I choose only one parameter – it gives me the result. But if I choose 2 or more – I got an error. […]

SQL query that finds repeated data in one column with different data in a second column

Let’s say I have data that looks like this: ID Date Data A D1 123 A D1 456 A D2 123 What I’m looking for is a select statement that will pull all rows where ID and Date are repeated as a pair, but the data doesn’t match. In this case, it would return the […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.