DO NOT USE this for questions targeting Microsoft SQL Server, use the tag sql-server instead. "SQL" is an acronym for "Structured Query Language", it is used in relational database management systems (RDBMS) to query, update, delete, and insert data as well as modify the structure of the database. ...

learn more… | top users | synonyms (1)

0
votes
3answers
56 views

Query for total downtime of site using datestamps and status columns

what want to find the total downtime for a site. I'm thinking I can subtract the 'down' DateStamp from the following 'up' DateStamp. then add the totals together for all downtime. I want to get a ...
-1
votes
1answer
66 views

CTE Expression not working

I am trying to get the employee hierarchy as output with empLevel indicating the reporting layer. However, my query returns only one row i.e. The CEO of the company while it's supposed to return all ...
0
votes
1answer
38 views

trouble creating a stored procedure to print out values for each day of week

I am having difficulty in understanding how to get this working correctly. I am trying to have create a stored procedure that is run automatically on a sunday and will get the data for the previous ...
1
vote
1answer
37 views

SQL Server Query For Getting data in Specific Format

We have two tables. One is PriceGroupTable and other is PricesTable. There is a one-to-many relationship between them. Please refer to the attached pic for more details. I am looking for a SQL ...
0
votes
0answers
50 views

PostgreSQL query taking a long time

I have a query that is taking hours to run. There is a large table to parse, is there any way to optimise this? with VehData as ( SELECT * from dblink('host=databasesrv port=1234 ...
1
vote
1answer
40 views

Count number of duplicate rows, sort from most to least (statistic) [closed]

I'm trying to make statistic of top-10 most visited URL. I have one simple table. For each URL visit, one row added with that URL as content. Most visited URL has the most number of duplicate. I've ...
1
vote
1answer
14 views

Join tables up together to get different counts of account type per postcode are

I am trying to deterine the amount of account types per postcode range. I have the following code that works but I would like help in refactoring it as only 1 line of code changing between each ...
3
votes
4answers
88 views

Pairwise intersections

I have a table with two columns, let's say FirstName and LastName. I need to get another table, which for every pair of FirstName's from the first one contains a count of the common LastName's. Is ...
1
vote
1answer
74 views

Dealing with single quotes in a dynamic query [closed]

I'm building a query as a string in my application layer to be executed over jdbc. The string is constructed as follows: "SELECT COUNT(DISTINCT ROOT_ID) as rows1 FROM DANIEL.UNIQUE_PHYSICIAN " ...
1
vote
1answer
20 views

Union of data from all tables with name containing a substring

I have 500+ tables all containing a column called COLUMNDATA. The table names are like: TABLE_FIRST, TABLE_SECOND, ... ,TABLE_FIVE_HUNDREDTH. What I am trying to get is a column containing all the ...
0
votes
1answer
22 views

Error with my trigger. What should I do?

This trigger fails when it's called: create or replace TRIGGER UNIQUE_PHYSICIAN_TRG BEFORE INSERT ON UNIQUE_PHYSICIAN FOR EACH ROW BEGIN <<COLUMN_SEQUENCES>> ...
-1
votes
3answers
54 views

Query to return zero entries with COUNT

I have a problem with one task. I need to display various information through two diferent queries.(or one if it could be possible) The table has these fields. Location is set to x ...
1
vote
2answers
22 views

How to populate another column with a sequence value generated for an identity column?

I have a table that has a column that is determined by a sequence using the identity column thing, so after a record is inserted (or during, I'm not sure how it works behind the scenes, only that it ...
4
votes
1answer
141 views

Moving Transaction Data To Another Database For Reporting Purpose

We got a requirement from our developer group, to do the followings: Frequently (every 30mins) move the transaction data out of live database to another database Secondary database will be utilize ...
1
vote
0answers
23 views

Publishing Database Changes With SSDT that include Adding a New Column [closed]

Similar to Publishing database changes with SSDT that include altering data types of columns But I have the issue when I'm adding a new column to my database. I tried publishing incrementally by ...
0
votes
1answer
45 views

Query trans log or tempdb?

New DBA, all of our databases are SQL 2005. All of our servers run in Simple recovery mode. I work for a school district, where we report student attendance to the state. I run a query to fix ...
4
votes
1answer
32 views

Doing a select based on a count(), a date range, and string match

I'm going to do my best here. I Have a list of citations that were written and a and some information about those citations. I need to select the citations that have been voided in a given date range ...
1
vote
0answers
32 views

Update all foreign keys to a different primary key

In a MySQL database, I have a table full of business information. Lots of businesses have been inserted twice due to human error, so the same information exists with two different primary keys. ...
1
vote
1answer
38 views

generate new column from another table column, treating nulls are unique

I have a table that looks like the following: |device_id | login_id | ----------------------- | 1 | NULL | | 2 | A | | 3 | A | | 4 ...
1
vote
4answers
93 views

How to select first row from a join that returns multple rows on the primary key

This is related to this question: Joining multiple tables results in duplicate rows I have two tables that I am joining. They share a key. The person table has one name per primary key but the ...
2
votes
1answer
62 views

Wrong results from joining two CTEs

I've had some help to create a query to solve a problem I had and the solution seemed to be to create two CTE's and then join and query them. If I run the two CTE's as single queries each table ...
2
votes
1answer
48 views

Filtering by minimum value per group problem

I have a select statement, which when simplified produces the following: +------------+----------+-----+ | Group | Name | ID | | vegetables | potatoes | 1 | | vegetables | carrots | 6 ...
0
votes
2answers
51 views

Can I Show Multiple “WHERE” Filtered Results In A Single Query/Output Table?

I am using Postgres 9.2.4 on Red Hat 6. Tables --------------------------------------- | Table 1 | --------------------------------------- | WorkflowID | DataSet | ...
1
vote
1answer
45 views

Combining data from 3 tables depending values stored in 1 table

I'm trying to make a SQL query to return values potentially from 3 tables. This will combine all booking information for a booking which can span over multiple days with multiple guests and a single ...
0
votes
1answer
70 views

Cross Join and Left Join within a query

I have query which has a cross join and also a left join which produces an error Unknown column 'a.hour' in 'on clause' simplified version of query is like this SELECT b.*,a.hour,c.datetime FROM ...
1
vote
1answer
22 views

UPDATE with Nested Query

This is what i've tried UPDATE (SELECT * FROM music GROUP BY songalbum) SET albumID = (SELECT albumID FROM albumList GROUP BY album ) WHERE songalbum = albumList.album this give the error #1248 ...
2
votes
1answer
62 views

How to optimize this MySQL query?

Hi can someone help me to optimize this MySQL query, it's taking very long to complete, I have set indexes correctly, but it seems that string operations are slowing this query SELECT ...
0
votes
0answers
41 views

Problem forming a query with subqueries in design view? (MS Access 2010)?

I just started a new job as a dbase admin intern, and due to the database being very large, it is better to do queries in design view, rather than hardcoding the sql. That being said, I cant seem to ...
1
vote
0answers
35 views

How does 'exists' affect output

I previously asked a question about how to organize a long search query. I have taken it apart. I am presently attempting to understand the function of some of its parts. Specifically, I am ...
5
votes
1answer
102 views

Query Plans Change Performance Worse in SQL Server 2014

We recently upgraded our server from SQL Server 2008R2 to SQL Server 2014. We have a query that ran fine in 2008R2 but now in 2014 runs incredibly much slower and has a bad execution plan. I've done ...
0
votes
1answer
63 views

Problem with UNION ALL

I have to process 200 points (lat, long) to calculate the minor distance of same object (street in this case). So, I made a simple query to obtain 1 result, and replicate the same to the other 199. ...
0
votes
1answer
102 views

SQL Developer - trying to import an excel file into a table

I'm trying to import an excel file into a table. The excel file has dates in the following format: 20/08/2008 12:00:00 AM and I am telling SQLDEV the following: DD/MM/YYYY H24:MI:SS AM To import the ...
0
votes
1answer
66 views

Error using view to limit data access between databases in SQL Server [duplicate]

I am trying to limit user access to data through a view. I have a database full of replicated data that I do not want the users to be able to access, I also have a reporting database in which I will ...
-5
votes
1answer
117 views

How to select only few columns from a table in database? [closed]

I have a table which consists of around 200 columns where name of each one is col_i (i=1 ,2,..200).Suppose I have to select only first 50 columns, how should I proceed? I found about concat() function ...
1
vote
1answer
32 views

Data type and Operator type Errors

i am following learn sql the hard way tutorial, in tutorial tutor suggest to use sqllite 3 but im using MSSQL 2008 and i have a problem, tutor ask from me to find something using AND or OR operators, ...
1
vote
1answer
29 views

Problem passing rows results to columns names

I have a table with following data: ID IDALUMNO ALUMNO IDTIPOEVALUACION TIPOEVALUACION NOTA 1 682 ALVITEZ MIRANDA, KAREN LIZET 1 PR1 ...
-1
votes
2answers
55 views

Getting a total value for items obtained in the current quarter

I've been working with MS SQL Server Management Studio and have a problem I can't fix. I am currently able to get the total for a set of values (Volume and Activity) from my SQL Server database. ...
0
votes
2answers
57 views

Count of distinct values of a table using sql

name number ============= lin | 8 lin | 8 lin | 8 rob | 8 rob | 8 rob | 8 lin | 8 lin | 41 lin | 41 rob | 41 and I want the result like name | Count of 8 | count of 41 ...
0
votes
0answers
32 views

No data transferred when trying to transfer columns from one database to another [closed]

I'm trying to transfer columns from one database to another using the following: INSERT INTO ykz2d_purchas_integration (name, amount, summary) SELECT user_email, orderpayment_amount, ...
0
votes
1answer
76 views

How to organize long Query

I have a massive query string that I am trying to pick apart for a project I am working on. I am looking for some guidance on how to take it apart(format it into human readable). Also, what are the ...
2
votes
1answer
94 views

How to select from 3 tables while using count on 1, and sum on another?

I'm stuck on this one query. Here is my setup: table 1 (shows) show_user show_count table 2 (views) view_user view_views table 3 (users) user user_name What I need to is produce results ...
0
votes
0answers
12 views

Truncate log file mirrored database [duplicate]

In SQL Server 2008, I have a mirrored database with a log file that has increased to 80GB, which is not normal and I have serious problem. I would like to decrease the log file size by truncating it. ...
2
votes
1answer
77 views

Exclusively access a table and prevent other queries, even read queries

I'm stuck at setting transaction isolation level. Here's my scenario that happens in the application: Get unprocessed messages (using the IsProcessing flag) Set their IsProcessing to true (in RAM) ...
0
votes
0answers
41 views

Reduce SORT overhead / Improve order by performance

My business works like this: The client downloads the data from this table which contains roughly 33 millions rows holding one week worth of data (which is then moved to a historic table). The client ...
3
votes
3answers
148 views

Optimizing a query which runs through all hours in a day

I need help optimizing a query. I'm using PostgreSQL 9.3.4 currently but can upgrade to 9.4 if needed. I have a table with 60+ millions of records which looks like this: ...
1
vote
1answer
47 views

select members with the longest running absence streak

I have a database containing a table that stores attendance data, and its columns are member_id and datetime_attended. And another table storing members info, which is made up by the columns id and ...
2
votes
2answers
72 views

Ensure order when select two rows using two identifiers on the same column

I'm trying to writing a query to retrieve exactly two rows from a table using the same column for indentification. SELECT * FROM teams WHERE name = $1 OR name = $2; Is there any way to ensure that ...
0
votes
1answer
26 views

Naming a second additional column referencing another table column?

I'm working with a table in database that references to a column in another table. Such as: Product Table ------------ Id Description Currencies_Code Currencies ------------ Id Code Description ...
0
votes
2answers
134 views

Why does my SQL Server Express (2014) connection break after restarting my computer?

For some reason when I install SQL Server Express (2014), it works fine for a while (usually a day or two), and then quits allowing me to sign in. I'm not just talking about remotely here, either. ...
0
votes
1answer
24 views

Possible to use a field within a query in PostgreSQL?

I want to query all events that are going on right now. Each events record has at and minutes fields for the time range of the event. I might query the events table like this: SELECT * FROM events ...