I have a stored procedure with a couple of input parameters Input parameters: Result set: AuxTable is not the final intended table, it’s just being displayed for debugging/troubleshooting. MsgLinkId MyRowId WO_ID SourceUnitName MaterialName BatchID UnitofMeasure Segment1 Segment2 Segment3 Segment4 Segme…
Tag: sql-server
Displaying Unique Temperature Values for the US
I am looking to do something similar to what I did in R (below) in SQL: The output of this code looks like this: So far in SQL I have managed to output a single state using the following code: The output looks like this: However, I am unable to get the unique avgTemp for each state. When I take
Dates returned as columns in SQL Select
My user will submit a FromDate and a ToDate. What I want to happen is to select the dates that fall in between these dates, which I have accomplished with the script below. The dates will by dynamic. This returns that dates as rows. What I am looking for is a way to make these dates return as the columns
Filter an XML column in SQL Server
I have an xml type column productsXML in product table I want to find all the rows that have <products></products>. I tried this: This is returning all the rows that have products tag and understandably so. Is there a way to filter only those rows that have <products></products>? Answe…
SSMS SQL Identify purchases who only buy one specific item
I want to find customers who have only bought fruit in their purchase. My data looks like this: I want this: Thinking I need a where clause, grouped by ID and Purchase_date? Answer You can try to use Having with condition aggregate function to write the logic. Buy fruit (COUNT with fruit count will be greater…
Dynamic SQL – Use declared VARCHAR in SET SQL string
How to use the declared variable @CodeID inside the SQL string? When I run following statement I get the “Invalid object name (..)” error. Answer It looks to me that you need two levels of dynamic SQL, with the first level inserting the database name (from #folders), and the second level inserting…
SQL Query group by for a beginner
I’m quite new to SQL Server and was wondering if it was possible to run a query like this. I have a table with the following fields: USER, WORKSTATION, DURATION (seconds) Each USER can use multiple workstations and each workstation can be used by multiple USERs. In the table I have records like: I would…
How to use constraints to force two child items be from the same parent?
I have a Jobs table that holds jobs. I have a Tasks table that holds tasks that belong to a job (1:many). I have a Task_Relationships table that holds the data about which tasks depend on other tasks within a job. I have 2 jobs, each job has 3 tasks and within the jobs the tasks are related as in
Find Multiple Substring from a String Using SQL
I have a column log. I need to extract values from that log column and make 4 new columns My table: Output I need: Answer If the string really always follows exactly that format, you can use something funky like this: Which gives: This abuses the PARSENAME function, which is meant to parse 4 part named object…
SQL Query to merge several views into one
I was trying to create a new view out of 3 or 4 other views/tables. TableA: title_id homeTeam 1234 WSV 5678 SSV 7890 NULL 4321 SCC TableB: title_id awayTeam 1234 SSV 5678 SFV 7890 NULL 4321 KFC TableC: title_id homeTeam 1234 SSV 5678 NULL 7890 AAB 4711 BFG I would like to generate a new view out of those thre…