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…
Tag: sql
How to check against a DB Table in Mapforce?
The question is about mapping in Mapforce. I have an Excel input file. I also have a table from Oracle DB. What I want to do is the following: Take tournr from Excel and compare it to tournr in Table from DB. When there is a match, take NR column from DB table. How can I do that? Answer I
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
How do I clear specific columns in DataGrid?
i am using WPF, connected to SQL DataBase via LinqToSql. I have filled DataGrid using ItemsSource. I would like to clear everything in my DataGrid, except first two rows(ID,first and second name), and Headers ofcourse, by pressing a button. All items i would like to(either set to null or 0) remove, are floats…
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…
Merging Respective Start and End Dates, Setting Flag Depending on Start/End Date – SQL
Problem: I have a table of transactions (see below) with either (open/start) or (close/end) transaction in the format of date. The task is to merge these transactions with their corresponding dates, however, there can be also cases when the transaction is opened/started, but not closed/ended, in which case on…
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…
SQL query for counting the number of same values in a table and fetching the first value if the count is greater than 1
There is a table such as I want the number of open-status records and if the number of records are greater than 1, I want to fetch the 1st open status record ID. for that, I have been trying doing like this which seems not working (please forgive me if it is a blunder). I finally want the ID of