Structured Query Language (SQL). For questions about the language, not Microsoft SQL Server (tag sql-server).

learn more… | top users | synonyms (2)

0
votes
0answers
44 views

automate import and export of process EXCEL -> SQL SERVER - > EXCEL without using SSIS

I have an ETL process that is entirely written using T-SQL scripts. The script grabs an Excel spreadsheet and imports it using openrowset. Once I get the transformed data I copy with headers and paste ...
1
vote
0answers
27 views

PostgreSQL 9.3 Query Design Optimisation - Improving speed

I am looking to optimise a query that is currently taking ~2500ms to run in PostgreSQL 9.3. The reason I need to optimise it is that may have to be run frequently and it directly impacts user ...
2
votes
1answer
42 views

Prevent duplicate entries

I have log in form with Username and Password. $con=mysqli_connect(LOGINDETAILS); /*if (!$con) { die("Can not connect: " . mysql_error()); } */ $Username = $_POST['Username']; $pswd = ...
2
votes
1answer
47 views

REGEXP does not show the right result?

I have in the name column: 3 boxes of frogs 7 boxes of frogs 48 boxes of frogs When I execute: SELECT name FROM items WHERE name REGEXP "[^1-5] boxes" ...the result is: 7 boxes of frogs 48 ...
1
vote
2answers
88 views

Find percentage of NULL storage in Oracle table

I have a table in Oracle 11g Standard One Edition: table1 col1 col2 col3 col4 col5 col6 col7 col8 col9 col10 col11 1 NULL 2 3 4 5 NULL NULL 19 21 22 1 NULL 2 3 ...
0
votes
1answer
14 views

Combine addresses missing Unit/Suite #

I have a list of addresses which need to be updated in SQL Server. How can I go about adding the Suite # to the addresses which are missing it? Address 1 Burton Hills Blvd, Suite 250 1 Burton Hills ...
3
votes
3answers
191 views

9 digit zip codes are reporting LEN of 12

I am attempting to resolve some difficulties in getting zip codes to display properly. The original spreadsheet has zip codes of mixed 5 and 9 digit formats. After the import process, these 9 digit ...
0
votes
1answer
20 views

Access Web App query 3 Tables with aggregatios - Need a workaround

I'm designing an Access 2016 Web App for inventoring our computers. The problem I have is with a query where I want to get the CPUCore-amount and RAM-Amount for each PC. The computer-value in RAM ...
0
votes
1answer
21 views

not a single-group group function when trying to add two sum() function [closed]

I have two tables: create table a ( index_ numeric(1), value_ numeric(2) ); create table b ( index_ numeric(1), value_ numeric(2) ); Now I want to get the number of these two tables sum,I ...
0
votes
2answers
31 views

Compare possible null values in one query

Question is pretty short and simple: I have following query: SELECT * FROM CTE Now I want to get all rows with only maximum value of some field. SELECT * FROM CTE WHERE NodeId = (SELECT ...
6
votes
3answers
80 views

Good example of MDX vs SQL for analytical queries

Can anybody show me a good example of MDX's advantages over regular SQL when doing analytical queries? I would like to compare an MDX query with an SQL query that gives similar results. Wikipedia ...
0
votes
0answers
6 views

Datastax solr query

I have 1 dse search node in my little cluster. I can look for data using solr quey only from this node. Is it possible to execute solr query from not dse search node (with cassandra only) too? Thanks
3
votes
2answers
44 views

Poor SELECT performance on recheck cond with data and large (millions of rows) partial index

Query: EXPLAIN (ANALYZE, BUFFERS) SELECT COUNT (*) AS "count" FROM "Posts" AS "Post" WHERE "Post"."createdAt" > '2015-08-19 14:55:50.398' AND "Post"."new" = TRUE; Index: CREATE ...
0
votes
0answers
33 views

MySQL: Calculations and Triggers across multiple tables

I'm new to sql and I'm having trouble with a calculation involving two of my tables. I have a table, payroll, which looks something like this: CREATE TABLE payroll ( id INT(6) UNSIGNED AUTO_INCREMENT ...
0
votes
0answers
29 views

Error while accessing TempDB Properties from SSMS

While trying to access TempDB's properties from SSMS (2014 SP1 SSMS) the following error comes up. Any ideas how to fix this? TempDB is owned by 'sa' and I am logged in with the 'sa' account. ...
4
votes
2answers
60 views

Get SSRS Datasources from ReportServer

Housekeeping on an old SSRS, this particular server is 2008R2. I have the query below which gets me the name, location, creation and modification overview for all the reports. I would also like to ...
0
votes
0answers
11 views

SQL query for setting value

I found this example for configuration databse tables: http://stackoverflow.com/questions/10204902/database-design-for-user-settings Can you show me some example for SQL query for getting ...
0
votes
1answer
10 views

postgresql fulltext slow

I am trying PostgreSQL for the first time, but am finding the fulltext search very slow. My database has a table with 5 columns and 5 million rows CREATE TABLE table_test ( id integer NOT NULL, ...
2
votes
1answer
184 views

OFFSET … FETCH overlapping results on second page

When using OFFSET ... FETCH, I'm having I'm getting...interesting results. Here are two sqlfiddles to illustrate my problem. http://sqlfiddle.com/#!6/71ac1/4 and http://sqlfiddle.com/#!6/71ac1/8 ...
0
votes
1answer
31 views

Writing a query to COUNT all the services involving a particular

Learning PHP and SQL on an online MOOC, this is a homework that I've got to do. I need to write a query in SQL to count all the services which involve a particular person in my database. The ...
0
votes
1answer
44 views

Multiple databases in production

We have 3 level hierarchy in our business model. Entities at level 3 contains huge data (like dailyfacts tables). In our current DB architecture, we have one single database & all level 1 ...
4
votes
2answers
207 views

Optimize query to find top N users who commented on a post

Problem Trying to find the most efficient query to retrieve the top N (5 in the examples) users who have commented on a post, where a user is considered 'top' if they have the most followers. The ...
0
votes
0answers
23 views

Calculate total number of faults that occurred today?

I have created a database application in Access that can be used to view fault reports that have been logged against a computing department's inventory. So far I have created queries which enabled the ...
2
votes
1answer
56 views

Select where value in row is different and update

I have a large table that includes rows like the following: Query: select * from cartographic_text_vm where fid = 'osgb5000005104723440' order by ckpt; Results: -[ RECORD 1 ...
1
vote
2answers
64 views

How to get the DDL for Oracle object without extra characters

When using Oracle's dbms_metadata.get_ddl for getting DDL I see some extra characters, especially there are whitespace characters at very beginning of DDL script. For example, if I create this sample ...
10
votes
5answers
198 views

Convert a date range to an interval description

A requirement in a recent project was to report when a resource would be fully consumed. As well as the exhaustion calendar date I was asked to show the remaining time in English-like format, ...
4
votes
2answers
110 views

find the beginning and ending values of sequential runs

This article does the inverse of what I need: Find which numbers in [1, 161] are not in the result set? Given the sample data they used: mysql> CREATE TABLE wp_blogs -> ( -> ...
0
votes
1answer
22 views

Postgresql maintainenace queries fail with foreignkey violation

I maintain a Django app with a postgresql backend, where users post interesting URLs (links) and then either leave replies under these or upvote/downvote them. All this is done via 4 postgresql tables ...
1
vote
1answer
41 views

Display last date grouped by warehouse and product

I have a table called daily_stock with the following fields: +-----------+----------+------------+----------+ | warehouse | product | date | stock | ...
4
votes
1answer
90 views

Subtracting sum of previous rows' data from current row's data in SQL

I want to create a table or view in SSMS which should has a column called qoh. In that column should be calculate sum values of the column rel_qty. i.e. The first data of qoh is the same of the ...
5
votes
4answers
221 views

Total duration “server” was in “In” state per day

I have data of a server start & stop time on a daily basis for one month. I need the result containing the first start time of the day, last stop time of the day, total time the server was started ...
0
votes
2answers
28 views

Group By Month With Overlapping years

I want my query to show all months grouped together, regardless of the year. For example, I would want this to be the output: 01/13/2015 01/01/2016 02/07/2015 02/01/2016 This is sample DDL - what ...
2
votes
1answer
52 views

SQL Server MDW - Query for data collector retention period

I have data collectors active on many remote servers and I am trying to find a query that will capture the retention periods for all data collectors active on an instance. These are system data ...
1
vote
1answer
27 views

Show Dollar Amount By Name On A Month To Month Basis

I want to show dollar amount by name on a month to month basis, but my syntax gives me an entry for each different dollar amount. Below is what I get from my query (subset of data) Name Value ...
0
votes
1answer
77 views

SQL query to count number of payments made in a time period with data from two different table

Could someone please tell me how to write sql query for the following question? I am not able to get how to write the condition where it selects those user_id which is present on the said date and ...
0
votes
1answer
43 views

How to express functional dependency in relational calculus and SQL?

I want to express in relational calculus and SQL the following functional dependency associated with Vote. This is the first time I try to implement it in actual SQL and relational calculus. Vote: ...
1
vote
1answer
30 views

Subquery inside a select vs left join

Often I have to return the count of of some row in a query that fetch other non related rows. For example A table User a table Review and a Table picture User: id nickname Review: id to_user_id ...
0
votes
3answers
73 views

Syntax on Changing return String by Concatenation?

I successfully created the scalar function FN_TotalBilled in SQL Server, which gives me the total amount invoiced to a company using tables Invoices(InvoiceID, InvoiceTotal,..) and Vendors(VendorName, ...
4
votes
1answer
41 views

PostgreSQL join subquery can't restrict query

I'm working on a check-splitting project that splits out amount owed for an activity. Users can be a part of multiple 'groups', which each have their own running balances. I'm trying to run a query ...
0
votes
0answers
34 views

SQL Query with double distinct dates iteration, and start date < my date < final date

I have a kind of rental system database where user can rent an entire house, or just a room of the house. I have a table called offers which has columns id, room_id and a few more. If room_id = ...
0
votes
2answers
51 views

Check if the trigger was fired by INSERT or DELETE

I know this is the easiest question you can find on this website but I wasn't able to find its answer on the internet. How to check if the trigger was fired by an INSERT or DELETE? Example CREATE ...
0
votes
1answer
39 views

how to get multiple rows from multiple tables concatenated into a single string in a stored procedure in Oracle

I have a table A that has 7 different 1-N relationships. Given an A id, now I am running 7 simple queries to get the N side rows. I suspect this could be sped up. Is it possible to: create a stored ...
3
votes
1answer
66 views

Correlated subquery to find total larger than state average

I have tables Vendors (VendorName, VendorState,....) and Invoices (InvoiceID, InvoiceTotal,...). I want to get the Invoices (as InvoiceId) that are larger than the Avg state InvoiceTotal. I know I ...
3
votes
1answer
26 views

Oracle SQL 12c How to correctly use compound foreign keys in compound primary keys?

DISCLAIMER: I am a student. I do not work for an airline. I do not pretend to be a professional programmer. Greetings, I want understand what I need to correct in order for these tables to work ...
0
votes
1answer
48 views

How to enrich event based data per row based on future events

I'm trying to figure out a not too terribly hacky way to solve the following issue. This data is currently available both in MySQL and RedShift, so a solution for either is fine though RedShift is ...
3
votes
2answers
76 views

Max date record for items for given set of dates

I am looking for some help to create an efficient SQL query (SQL Server 2012) that will identify the latest records (date) for all the items in the table (table 2 below) on each of a list of given ...
-1
votes
1answer
40 views

How to read the deleted value using a variable

I'm trying to get @OldValue = (select @fieldname from #del) to give me the value. Instead I get the column name for the result. Is there a way to write this so it can output the value. If I write ...
1
vote
0answers
22 views

How to design for scalable set operations?

This is the requirement for an educational application to teach words to language learners: Learner can declare that he/she knows a given word Learner can memorize the given word (to be managed by ...
0
votes
1answer
25 views

Append Records to a fixed number of columns using dynamic data [duplicate]

I have a table with 22,000 records formatted like so: Input [ID],[Cost Center],[Function] 1,01A1001710,GF000001 2,01A1001710,GF000802 3,02R1012000,GF000001 4,02R1012000,GF000059 ...
8
votes
4answers
221 views

Separate letters from digits in alphanumeric string

I have an alphanumeric string as input and I want to get two results out of it: a string where all the numerals are removed and an integer that is the sum of all the digits in the input string. ...