Pivoting a date and value column to get their combination

I have a table with this structure:

+--------+-----------+------------+------+
| userid |   date    |    item    | rank |
+--------+-----------+------------+------+
|  34444 | 01-Jul-15 | pen        |    3 |
|  34444 | 04-Jul-15 | TV         |    2 |
|  34444 | 09-Jul-15 | controller |    1 |
|    531 | 03-Jul-15 | keyboard   |    3 |
|    531 | 06-Jul-15 | pen        |    2 |
|    531 | 10-Jul-15 | bowl       |    1 |
+--------+-----------+------------+------+

Each item has already been ranked based on their dates with a limit of 3 items per user. I have their last 3 items and the dates associated with them. The items can be anything.

I want to produce a view in a way that pivots the date and item combination. For example, the desired view for this table is:

+--------+------------+-----------+-------+-----------+----------+-----------+
| userid |   item1    |   date1   | item2 |   date2   |  item3   |   date3   |
+--------+------------+-----------+-------+-----------+----------+-----------+
|  34444 | controller | 09-Jul-15 | TV    | 04-Jul-15 | pen      | 01-Jul-15 |
|    531 | bowl       | 10-Jul-15 | pen   | 06-Jul-15 | keyboard | 03-Jul-15 |
+--------+------------+-----------+-------+-----------+----------+-----------+

Is this possible?

Thanks

Select rows where jsonb array contains any string in array

This is my SQL so far:

select * from table where table.json_info @> '{"search_tags":["hello", "world"]}'

And this works if the row has BOTH hello and world in the search_tags

I'm wondering if there is a way to include rows that have EITHER hello OR world and of course those that have both.

Thanks to all ahead of time!

Replicating Count() on two querysets from different models in Django

I'm trying to query two models in Django. Both have similar data. Once the queries are merged I want to be able to count the occurences of unique values and add a count column to show this. So far I have:

queryCount = table.objects.values(last_name, first_name)
queryCount = queryCount.annotate(count=Count(last_name))

This works but only for one table. If I add another table (repeat the query with another table) I have options of how to merge the two queries.

I've tried itertools chain but that means I can no longer use distinct() to get unique values.

I've tried the snippet here but again no distinct() function.

I've also tried removing the .annotate(Count) and just getting all the values (without counting) and adding the count() method after merging the two querysets. I'm having the same problem as the merged querysets don't have the Count() method available.

Is this possible in Django or should I just drop to Raw SQL?

Select all MySQL records in the last day that are between a time range of 7am and midnight

I have this query

SELECT * FROM `timeclock_timecard` 
WHERE `clock_in_datetime` > DATE_SUB(CURDATE(), INTERVAL 1 DAY)  

which can get record in the last day but I need to limit to records created after 7AM

Any help please?

Alternative to UDF in aws redshift

I am creating views on a redshift table, but would like to have some sort of argument that I can pass to limit the data I get back from the view. The table is for the whole month and joins take a lot of time. I looked into redshift documentation but it says that redshift doesnot support user-defined functions. Is there any alternative to choose besides views/UDF....

To be particular, I have a query like: “with lookup as(Select DISTINCT * from Table where property_value = 'myproperty' AND time_stamp > ‘2015-07-##’ AND time_stamp < ‘2015-07-##’ order by sortkey) Select * from lookup where ……..”

I wanted to be flexible in changing the time_stamp. Also, would like user to pass arguments to the created view and grab data just for specified timestamps.

Thanks

Doctrine Query Builder Select Count with Case

I have the following sql-statement that I want to transform into doctrine query builder. The goal is to count how many ratings exist with rating value 1 and with rating value 2.

SELECT 
COUNT(CASE WHEN rating.rating = 1 THEN rating.rating END) as rat1, 
COUNT(CASE WHEN rating.rating = 2 THEN rating.rating END) as rat2 
FROM rating 

This sql statement is working fine - but when I try to transform it into a Doctrine statement, it does not anymore. When nothing should get counted (because no ratings for this value exist), it returns me a "1" instead of a 0. How can I tell doctrine to simply return a zero when there is nothing to count? I tried it by removing the "ELSE 0" , but then I get an error that this part is required..

return $qb
        ->addSelect('COUNT(CASE WHEN r.rating = 1 THEN r.rating ELSE 0 END) as rat_1')
        ->addSelect('COUNT(CASE WHEN r.rating = 2 THEN r.rating ELSE 0 END) as rat_2')
        ->getQuery()
        ->getResult();

Regards,

"sum" is not required - example: votings 2,2,2,2,2 should return 5 , because the rating with value 2 got voted 5 times.

How can I group rows that aren't familiar?

My data looks like this: http://ift.tt/1IzPWF4

But I want it to look like:

+------------+------+---+
| 2015-01-01 | walk | 1 |
| 2015-01-01 | run  | 0 |
| 2015-01-01 | bike | 0 |
| 2015-01-02 | walk | 0 |
| 2015-01-02 | run  | 0 |
| 2015-01-02 | bike | 0 |
| 2015-01-03 | walk | 0 |
| 2015-01-03 | run  | 1 |
| 2015-01-03 | bike | 0 |
| 2015-01-04 | walk | 0 |
| 2015-01-04 | run  | 0 |
| 2015-01-04 | bike | 0 |
| 2015-01-05 | walk | 0 |
| 2015-01-05 | run  | 1 |
| 2015-01-05 | bike | 0 |
+------------+------+---+

Each day should have a collection of how many occurrences of each event response happened.

• The dates are collected from a calendar table and are thus purely static.
• The event names are numerous and likely to change.
• Event responses join events for name and other context rules.

Would be a big help to figure this out. At least offer tips for better title (search terms) so I can figure out how to resolve this issue.

This is for processing graphs at activezoo.com. Any advice for other approaches or methods for analyzing data is very welcome.

Thanks

SQL Date Queries

Please help me to understand this SQL Select statement:

Select DATEADD(DAY, DATEDIFF(DAY, 0, GETDATE()) - 1, 0)

The 0s are throwing me off.

What is the solution when i want to update list data from Application to exists large records in SQL server (more then 800million records)

Please tell me how to update list data from Application to exists large records in SQL server (more then 800million records).

This mean : i will foreach my List data --> then [select record by id from table in SQL] then if exist record i will update record from Application to SQL server. Because the database very large then 1 record want to be updated will find more than 800million records in SQL--> 1*800million--> so i want to update 100 record then --> 100*800million.

Please help me the best solution to solve this prolem. Thank you so much!

SQL Query taking long time with STUFF function

Example Dataset:

ID  seat    code
15098   1   AA21
15098   2   AA21
15105   1   AA21
15105   1   DD15
15105   1   NN60
15196   1   AA21
15196   2   DD50
15196   2   DD51
15209   1   AA21
15209   3   AA21
15209   2   CC50
15209   1   DD01
15209   3   DD01
15210   1   AA21
15210   2   AA21
15210   3   AA21
15210   1   DD21
15210   2   DD21
15210   3   DD21
15211   1   CC51
15211   1   DD20
15212   1   AA21
15212   1   DD03

and Desired Result is:

ID  seat    Codes
15098   1    AA21
15098   2    AA21
15105   1    AA21, DD15, NN60
15196   1    AA21
15196   2    DD50, DD51
15209   1    AA21, DD01
15209   2    CC50
15209   3    AA21, DD01
15210   1    AA21, DD21
15210   2    AA21, DD21
15210   3    AA21, DD21
15211   1    CC51, DD20
15212   1    AA21, DD03

---

Example Dataset table query:

CREATE TABLE #Temp(
ID varchar(50),
seat varchar(50), 
code varchar(150))

INSERT INTO #Temp VALUES ('15098', '1', 'AA21');
INSERT INTO #Temp VALUES ('15098', '2', 'AA21');
INSERT INTO #Temp VALUES ('15105', '1', 'AA21');
INSERT INTO #Temp VALUES ('15105', '1', 'DD15');
INSERT INTO #Temp VALUES ('15105', '1', 'NN60');
INSERT INTO #Temp VALUES ('15196', '1', 'AA21');
INSERT INTO #Temp VALUES ('15196', '2', 'DD50');
INSERT INTO #Temp VALUES ('15196', '2', 'DD51');
INSERT INTO #Temp VALUES ('15209', '1', 'AA21');
INSERT INTO #Temp VALUES ('15209', '3', 'AA21');
INSERT INTO #Temp VALUES ('15209', '2', 'CC50');
INSERT INTO #Temp VALUES ('15209', '1', 'DD01');
INSERT INTO #Temp VALUES ('15209', '3', 'DD01');
INSERT INTO #Temp VALUES ('15210', '1', 'AA21');
INSERT INTO #Temp VALUES ('15210', '2', 'AA21');
INSERT INTO #Temp VALUES ('15210', '3', 'AA21');
INSERT INTO #Temp VALUES ('15210', '1', 'DD21');
INSERT INTO #Temp VALUES ('15210', '2', 'DD21');
INSERT INTO #Temp VALUES ('15210', '3', 'DD21');
INSERT INTO #Temp VALUES ('15211', '1', 'CC51');
INSERT INTO #Temp VALUES ('15211', '1', 'DD20');
INSERT INTO #Temp VALUES ('15212', '1', 'AA21');
INSERT INTO #Temp VALUES ('15212', '1', 'DD03');

I am using below query as a part of my SSRS report stored procedure to get the result but it is taking too long to run as my original dataset is more than a 100000 rows. Is there any other efficient way to get the result.

SELECT 
 SS.ID
,SS.seat
,STUFF((SELECT ', ' + CAST(LTRIM(RTRIM(CR.Code)) AS VARCHAR(10)) [text()]
        FROM #Temp CR 
        WHERE CR.ID = SS.ID and CR.seat = SS.seat
        FOR XML PATH(''),TYPE).value('.','NVARCHAR(MAX)'),1,2,' ') Codes
FROM #Temp SS
GROUP BY SS.ID, SS.seat

Count group by in laravel

My current select statement:

return User::select('id', 'created_at')
                    ->orderBy('created_at', 'desc')
                    ->get()
                    ->groupBy(function($date) {
                        return Carbon::parse($date->created_at)->format('Y m d');
                    });

This currently returns

2015 07 28: [
{
id: 935,
created_at: "2015-07-28 23:00:20"
}
],

2015 07 27: [
{
id: 926,
created_at: "2015-07-27 17:13:58"
},
{
id: 925,
created_at: "2015-07-27 15:00:36"
},
{
id: 924,
created_at: "2015-07-27 13:37:00"
}
]

What I just want is:

Date | Users
-----+------
7/28 | 1
7/27 | 3

table alias for LATERAL VIEW query

Not sure if tableAlias has any practical values in Hive Lateral view query? I think people may be able to use columnAlias directly? It will be great if anyone could show an example when tableAlias has values? Thanks.

http://ift.tt/1pzhrVp

thanks in advance, Lin

retrieve value from sql statement for later use

How can I retrieve the value of TOTAL from the SQL statement below in c# and save it as a variable for use in a following query ?

SELECT  detail.branch, SUM(CONVERT(DECIMAL(8, 2), detail.total)) AS TOTAL, 
        SUM(CONVERT(DECIMAL(8, 2),detail.typeA / 1.2)) AS TA, 
        COUNT(DISTINCT detail.orderid) AS NOR, 
        SUM(DISTINCT header.coupon) AS coupon
FROM    detail INNER JOIN header ON detail.filename = header.filename
WHERE   detail.dates BETWEEN '04/01/2015' AND '04/30/2015' AND branch='SW1'
GROUP BY detail.branch

Primary Key assignment in PostGres

I have table tmp in my postgres database that contains roughly 139 million records. I am trying to move the the columns col1, col2, and col3 to col1, col2, and col3 of another tabled named r4a. I created the table r4a with this query:

CREATE TABLE r4a(
    gid serial NOT NULL,
    col1 double precision,
    col2 double precision,
    col3 double precision,
    the_geom geometry,
    CONSTRAINT r4a_pkey PRIMARY KEY (gid));

I created this insert into query to populate fields in r4a:

INSERT INTO r4a (col1,col2,col3)
SELECT col1, col2, col3
FROM tmp
limit 500;

It populates the gid [PK] serial column with numbers ranging from [14816024-14816523].

How does it determine which 500 records to limit the query too?
Is it choosing to import rows [14816024-14816523] or is it just arbitrarily assigning numbers?

Ideally I want the primary key to begin at 1 and count upwards. Being new to postgres and having such a large (in my opinion) table, I want to make sure I understand what is going on.

Thanks in advance,

dubbbdan

Hive query execution issue

When executing a hive query, here is the output, wondering for "Map 1" and "Reducer 2", what does the 1 and 2 mean?

Map 1: 21/27    Reducer 2: 0/1
Map 1: 22/27    Reducer 2: 0/1
Map 1: 23/27    Reducer 2: 0/1
Map 1: 24/27    Reducer 2: 0/1
Map 1: 26/27    Reducer 2: 0/1
Map 1: 27/27    Reducer 2: 0/1
Map 1: 27/27    Reducer 2: 1/1

thanks in advance, Lin

resultSet not open, works in one case another case not

Why if i query the database from the db class using queryFromDb() i get "ResultSet not open" when it try to do rs.next() for the second time and instead if i try to use queryFromMain everything works?

public class Db {

private String protocol = "jdbc:derby:";
private ResultSet resultSet = null;
private Connection connection = null;
private Statement statement;

public Db(){

    try{
        Properties props = new Properties(); 
        props.put("user", "user");
        props.put("password", "password");

        String dbName = "database";
        connection = DriverManager.getConnection(protocol + dbName  , props);

    }
    catch(SQLException sqle){
        printSQLException(sqle);
    }
}

public ResultSet returnValue(String query){

    try{
        statement = connection.createStatement();

        resultSet = statement.executeQuery(query);  
    }
    catch(SQLException sqle){
        printSQLException(sqle);
    }

    return resultSet;   
}


public void queryFromDb(){
         try {
            statement = connection.createStatement();
            ResultSet rs = statement.executeQuery("SELECT * FROM clsbck ORDER BY id");  
            while(rs.next()){
                System.out.println(rs.getString(2));
                String str = "INSERT INTO cls rck VALUES 2";
                [...]           
                statement.execute(str); 
            }
         } catch (SQLException e) {
            printSQLException(e);
         }  
    }

   }
}

public class Main {
    private static Db db;

    public static void main(String[] args){
        db = new Db();
    }

    public static void queryFromMain(){
        ResultSet rs = db.returnValue("SELECT * FROM clsbck ORDER BY id");
        try {   
           while(rs.next()){
               String str = "INSERT INTO cls rck VALUES 2";     
               [...]
               db.addValue(str);
           }
        } catch (SQLException e) {
            printSQLException(e);
        }   
    }
}

How to order queries of one table based on the sum of a column in a related table

Hello I have a users table with columns

id, username, password

I also have an orders table with columns

id, user_id, price, status

In the index method of the UsersController, I would like to order my users based on the amount of purchases they have made. The purchases of each user can be gotten as the sum of the price of all the orders in which the user_id of the order is equal to the id of a user and in which the order.status of such an order is equal to 4

The restriction in order status is made because users have the possibility of cancelling orders in my system so I'll like to query only the orders which have not been canceled.

Trials

I tried getting the users based on

$users = User::leftJoin('orders', 'users.id', '=', 'orders.user_id')
     ->where('orders.status', '!=', 2)->where('orders.status', '!=', 5)
     ->groupBy('orders.user_id');

But yet I still get errors.

Based on my Models, I could get all the purchases of a particular user by usinng a query such as

 User::find(1)->orders()->where('orders.status', '=', 4)->sum('orders.price') 

Dynamically Using Sheetname in Sql-VBA

rs.Open "SELECT [Sheet1$].ID FROM [Sheet1$] WHERE [Sheet1$].ID IS NULL ", cn, adOpenKeyset, adLockReadOnly

Sheet1, i.e. the name of the sheet in my case is variable in my case and I want to repeat this query for 100 sheets, so how do I dynamically specify the name?

Thanks in advance.

Dapper must declare the scalar variable

I am experiencing issues when trying to used a paramaterized query in Dapper. I have found a number of other users with similar issues but have not been able to resolve the issue.

The code

    public User GetUser(int employeeId)
    {
        var args = new
        {
            EmployeeId = employeeId
        };

        const string sql = @"
                    select 
                        first_name 'FirstName', 
                        last_name 'LastName'
                    from 
                        users 
                    where 
                        employee_id = @EmployeeId 
                ";

        using (var con = MakeConnection())
        {
            var r = con.Query<User>(sql, args);
            return r.FirstOrDefault();
        }
    }

The error

A first chance exception of type 'System.Data.Odbc.OdbcException' occurred in System.Data.dll

Additional information: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare the scalar variable "@EmployeeId".

I have also tried using DynamicParameters and passing that instead but that is not working either

var p = new DynamicParameters();
p.Add("@EmployeeId", employeeId); // I have also tried without the @
//...
var r = con.Query<User>(sql,p);

SQL Server 2014 ShowPlanXML hyperlink crashes ssms

On SQL Server 2014, my client crashes after running a DMV query that shows a query plan. An example of the code:

SELECT TOP 10 
DB_NAME(qt.dbid) as dbname, 
OBJECT_NAME(qt.objectid) 
spname, 
qs.execution_count,
qs.total_logical_reads, 
qs.last_logical_reads,
qs.total_logical_writes, 
qs.last_logical_writes,
qs.total_worker_time,
qs.last_worker_time,
qs.total_elapsed_time,
qs.last_elapsed_time,
qs.last_execution_time,
qp.query_plan
FROM sys.dm_exec_query_stats qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) qt
CROSS APPLY sys.dm_exec_query_plan(qs.plan_handle) qp
WHERE qt.dbid not in (1,2,3,4,18) --system & distribution databases
ORDER BY qs.last_logical_writes DESC, execution_count DESC

The result set for the query_plan column is a hyperlink that displays the actual query plan. Once I click on a row in the query_plan column, the ssms client crashes.

DB2 SQL SELECT top 1 group with join

I'm connecting to a DB2 database and want to use the Max(PO180M1.POORDT) or the TOP 1 grouping by the PO180M2.PHPPN (PARTNO). How would I do this? I think I keep getting close but not getting it to work.

SELECT PO180M2.PHPPN AS PartNo, (PO180M2.PHVNPD || ' ' || PO180M2.PHVNP2) AS PartDesc, 
       PO180M1.POORDT AS OrderDate, PO180M2.PHUNCT AS UnitCost
FROM PO180M1, PO180M2 
WHERE PO180M1.POORNO = PO180M2.PHORNO 
ORDER BY PartNo, OrderDate DESC

Query runs faster without an index. Why?

I have two tables. One of those tables has this schema:

CREATE TABLE `object_master_70974_` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
 `id_object` int(10) unsigned NOT NULL DEFAULT '0',
 `id_master` int(10) unsigned NOT NULL DEFAULT '0',
 `id_slave` int(10) unsigned NOT NULL DEFAULT '0',
 `id_field` bigint(20) unsigned NOT NULL DEFAULT '0',
 `id_slave_field` bigint(20) unsigned NOT NULL DEFAULT '0',
 PRIMARY KEY (`id`),
 UNIQUE KEY `id_object`    (`id_object`,`id_master`,`id_slave`,`id_field`,`id_slave_field`),
 KEY `id_object_2` (`id_object`,`id_master`,`id_field`,`id_slave_field`),
 KEY `id_object_3` (`id_object`,`id_slave`,`id_field`),
 KEY `id_object_4` (`id_object`,`id_slave_field`),
 KEY `id_object_5` (`id_object`,`id_master`,`id_slave`,`id_field`),
 KEY `id_object_6` (`id_object`,`id_master`,`id_slave`,`id_slave_field`),
 KEY `id_master` (`id_master`,`id_slave_field`),
 KEY `id_object_7` (`id_object`,`id_field`)
) ENGINE=InnoDB AUTO_INCREMENT=17827 DEFAULT CHARSET=utf8;

As you can see, there is an overlapping index KEY id_object_5 (id_object,id_master,id_slave,id_field) and there is no index that would cover these three fields: id_object, id_master, id_field. However, when I run these two queries:

SELECT f1.id 
FROM object_70974_ f1  
LEFT JOIN object_master_70974_ mss0 ON mss0.id_object IN (70974,71759)  
AND mss0.id_master = 71100 AND mss0.id_slave = 70912 AND mss0.id_field = f1.id

and

SELECT f1.id 
FROM object_70974_ f1  
LEFT JOIN object_master_70974_ mss0 ON mss0.id_object IN (70974,71759)  
AND mss0.id_master = 71100 AND mss0.id_field = f1.id

they both return the same number of rows (since in fact id_slave field does not really matter) - 3530, however, the first query is slower than the second query by one second - 8 and 7 seconds respectively. So, I guess I have to ask two questions - 1) why does the second query run faster, even though it does not use index and 2) why does the first query run so slowly and why does not it use an index (obviously). In short, what the heck is going on?

EDIT

This is the result of EXPLAIN command (identical for both queries):

"id"    "select_type"   "table" "type"  "possible_keys" "key"   "key_len"   "ref"   "rows"  "Extra"
"1" "SIMPLE"    "f1"    "index" \N  "attr_80420_"   "5" \N  "3340"  "Using index"
"1" "SIMPLE"    "mss0"  "ref"   "id_object,id_object_2,id_object_3,id_object_4,id_object_5,id_object_6,id_master,id_object_7"   "id_master" "4" "const" "3529"  "Using where"

EDIT

It's extremely interesting, because if I DROP id_master index (which for some strange reason is used by both queries), then it starts to use id_object_5 index.

EDIT

And, yes, with id_master index being dropped, both queries start to run super-fast. So, I guess there is some trouble with optimizer.

EDIT

I even have a guess what trouble faces the optimizer - it may be incorrectly treats id_slave_field field name in the key, as if it were two fields instead - id_slave and id_field. In this case it becomes reasonable, why it firstly used this key in both queries.

Manipulating a data structure in Pig/Hive

I'm not really sure how to phrase this question, so please redirect me if there is a better place for this question.

Right now I have a data structure, more or less organized like this:

I want my data to look like this:

Sorry for the images, apparently I can't use markdown to make these!

I realize my question is similar to this one, but ideally I would like to be able to do this in Pig, but knowing how to do it in Hive, R, Python, or Excel/LibreCalc would be useful/interesting too.

I'm not even sure what this kind of data manipulation is called, so directing me to some sort of general wiki page would be helpful.

Stored procedure with parameters in Access

I have this stored procedure that I run in Access.

Private Sub ReviewTrns_Click()
    Dim cdb As DAO.Database, qdf As DAO.QueryDef
        Set cdb = CurrentDb
        Set qdf = cdb.CreateQueryDef("")
        ' get .Connect property from existing ODBC linked table
        qdf.Connect = cdb.TableDefs("dbo_FreeShipping").Connect
        qdf.SQL = "EXEC dbo.UpdateTrns"
        qdf.ReturnsRecords = False
        qdf.Execute dbFailOnError
        Set qdf = Nothing
        Set cdb = Nothing
MsgBox "Records Updated!"

End Sub

It works perfectly. dbo_FreeShipping is a linked table, dbo.UpdateTrns is a stored procedure. I also want to add input value for parameter @variable1

Could it be done with message box input?

Entity Framework SQL query on DateTime

I want to get 5 popular stories using Entity Framework query in a time span for example in 7 days or 14 days. I am storing views in a separate table with DateTime parameters with StoryID.

Stories table:

StoryID   Story
--------------------
1         story one
2         story two 
3         story three
4         story four
5         story five
6         story six

Views table:

ViewID     ViewDate             StoryID
---------------------------------------
  1        2015 07 17 19:00:00        1
  2        2015 07 17 20:00:00        1
  3        2015 07 17 21:00:00        2
  4        2015 07 18 19:00:00        2
  5        2015 07 19 19:00:00        2
  6        2015 07 21 19:00:00        1
  7        2015 07 23 19:00:00        2

Can you guys please help me figuring this out. Thanks

Edit: (so far this is what how I think I should do it)

return _viewdb.ObjectSet.Where(p => DateTime.Now >= EntityFunctions.AddDays(p.ViewDate, -14)).Select(p => new ExampleViewModel
            {
                StoryTitle = p.Sotries.Story,
            }).Take(5);

I am looking for a sql query to exclude records in sql that are not a filename and is liget file's extension

sample data is like below(5 rows):

e:\deploy\applications\intel management engine interface and serial over lan driver (sol) driver 7.1.2.1041v2\me_sw\mewmiprov\me\cim_schema\system\cim_computersystemdma.mof
c:\$recycle.bin\s-1-5-21-3125639655-2069970247-2443061104-29869\$iqzvjd6.jpg
c:\users\asdf\music\the cranberries\no need to argue\02 i can't be with you.mp3
e:\oracle\epm\docs\epm~1.111\wa_user\about_navigating_data_objects.html
Marijuana; Suicide; ass; butt; don’t say anything; drug; kick; knife; knives; marijuana; murder; naked; opiate; party; prick; scam; sex; smoke; smoke a joint; smoking; sneak; speculation; stabbed; stoned; suck; tripping

Apparently the last one is NOT a legitimate file and the others are. Can anyone help? Thank you very much

Concatenation of max_times within 15 minute time intervals SQL

I am trying to write a sql query that pulls an ID and concatenates the max times within 15 minute time intervals starting from the first time.

i.e. for one log_id data might be

101 01:01 
101 01:08 
101 01:23 
101 02:01 
101 02:10 
101 02:16

we would want to display

101 01:01, 01:08, 01:23, 2:01, 2:16

any ideas?

PoestgreSQL - Import CSV with PSQL Function as Non-superuser

I am using PG Admin and would like to get around the cumbersome method of importing CSVs manually. I've come across the COPY command, similar to this:

COPY table_nameFROM '\\server\mypath\myfile.csv'(FORMAT 'csv', DELIMITER ',', HEADER)

I'm not a superuser, so I get the following error:

ERROR:  must be superuser to COPY to or from a file
HINT:  Anyone can COPY to stdout or from stdin. psql's \copy command also works for anyone.

Ideally, I would like to create a function that I can run when needed. It would be great if the function could CREATE TABLE on the fly and import all fields as text:

select csvimporter('table_name','\\server\mypath\myfile.csv',',','HEADER')

I am still fairly new to functions. Has anyone had experience using functions like this while not being a superuser? Any help/tips are much appreciated.

Thanks!

Unknown SQL Error in PHP [duplicate]

This question already has an answer here:

• Reference - What does this error mean in PHP? 28 answers

When you run this code

mysqli_query($dbc, "insert into toons values(NULL, $toonId, '$toonName', $admin, '$dna', $bank, $money, $hp, '$inventory', $lastPlace)")

I get the error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ' '', , '', , , , '', )' at line 1

Does anyone know whats going on? Thanks!

sybase data access connectivity libraries were not found on the machine

Want to install the sybase data access connectivity libraries. so I can do my migration from sybase to SQL server 2012. But I look everywhere and can't find the libraries that I need to install.

Transactions with OrientSQL statements

I am using the PhpOrient driver and am trying to setup transactions. I have everything working fine, until I try to attach a sql command to the transaction.

I am not sure if this is a limitation of the PhpDriver or of Orient itself, or the binary protocol the driver is built upon.

I have also come across SQL Batch, and am not sure the difference between batch and transactions.

Is it possible to attach sql commands to transactions?

My code:

//get the transaction and start it
$tx = $client->getTransactionStatement();

//BEGIN THE TRANSACTION
$tx = $tx->begin()

$create = $client->command("INSERT INTO V set test = 'a'");

$tx->attach($create);

$result = $tx->commit();

Cannot add foreign key constraint. Mysql

By executing the following SQL statement it gives me an error such as "Error 1215: Cannot add foreign key constraint"

this is my SQL code. the erro gives me to create the first table: "process"

CREATE TABLE process(
  idp VARCHAR(36) NOT NULL,
  idc VARCHAR(36) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  provider TEXT NULL,
  PRIMARY KEY(idp),
  FOREIGN KEY (idc) REFERENCES clients(idc)
  ON DELETE CASCADE
) ENGINE=INNODB;

CREATE TABLE tag_group (
  idtg VARCHAR(36) NOT NULL,
  idp VARCHAR(36) NOT NULL,
  name VARCHAR(255) NOT NULL,
  description TEXT NULL,
  ttl TEXT NULL,
  PRIMARY KEY(idtg),
  FOREIGN KEY (idp) REFERENCES process(idp)
  ON DELETE CASCADE
) ENGINE=INNODB;

CREATE TABLE clients (
  idc VARCHAR(36) NOT NULL,
  name VARCHAR(255) NOT NULL,
  company VARCHAR(255) NOT NULL,
  address VARCHAR(255) NULL,
  phone VARCHAR(45) NULL,
  fax VARCHAR(255) NULL,
  website VARCHAR(255) NULL,
  PRIMARY KEY(idc)
) ENGINE=INNODB;

CREATE TABLE users (
  idu VARCHAR(36) NOT NULL,
  idc VARCHAR(36) NOT NULL,
  title VARCHAR(36) NULL,
  firs_name VARCHAR(255) NOT NULL,
  last_name VARCHAR(255) NOT NULL,
  email VARCHAR(255) NULL,
  password VARCHAR(255) NOT NULL,
  country VARCHAR(255) NULL,
  city VARCHAR(255) NULL,
  telephone VARCHAR(255) NULL,
  is_agent BOOLEAN NOT NULL,
  last_login TIMESTAMP NULL,
  timeout TIMESTAMP NULL,
  timeout_enabled BOOLEAN NULL,
  preferred_language VARCHAR(36) NOT NULL,
  PRIMARY KEY(idu),
  FOREIGN KEY (idc) REFERENCES clients(idc)
  ON DELETE CASCADE
) ENGINE=INNODB;

Html table not populating from ViewModel

I am trying to populate an HTML table with data from a table in my database. The issue is simply that the HTML table is not getting populated with any data.

Here is the ViewModel:

    public class TestViewModel
{
    public string MatchedId { get; set; }
    public string UnmatchedId { get; set; }
    public string Auth { get; set; }
    public DateTime CreditDate { get; set; }
    public string CreditNumber { get; set; }
    public decimal CreditAmount { get; set; }
    public DateTime DeniedDate { get; set; }
    public int DeniedReasonId { get; set; }
    public string DeniedNotes { get; set; }
}

Controller Action:

   [HttpPost]
public ActionResult UploadValidationTable(HttpPostedFileBase csvFile)
{
    var inputFileDescription = new CsvFileDescription
    {
        SeparatorChar = ',',
        FirstLineHasColumnNames = true
    };
    var cc = new CsvContext();
    var filePath = uploadFile(csvFile.InputStream);
    var model = cc.Read<Credit>(filePath, inputFileDescription);

    try
    {
        var entity = new Entities();

//model here is the .csv, doesn't have anything to do with this issue
        foreach (var item in model)
        {
            var tc = new TemporaryCsvUpload
            {
                Id = item.Id,
                CreditAmount = item.CreditAmount,
                CreditDate = item.CreditDate,
                CreditNumber = item.CreditNumber,
                DeniedDate = item.DeniedDate,
                DeniedReasonId = item.DeniedReasonId,
                DeniedNotes = item.DeniedNotes
            };
            entity.TemporaryCsvUploads.Add(tc);
        }
        entity.SaveChanges();

        System.IO.File.Delete(filePath);

//This is where the database table is getting filled
 entity.Database.ExecuteSqlCommand("Insert into CsvReport Select  p.Id as MatchedId, case when p.Id is null then t.Id end as UnmatchedId, p.Auth,p.CreditDate, p.CreditNumber,p.CreditAmount, p.DeniedDate,p.DeniedReasonId, p.DeniedNotes from TemporaryCsvUpload t left join PermanentTable p on p.Id = t.Id;");


        TempData["Success"] = "Updated Successfully";

    }
    catch (LINQtoCSVException)
    {
        TempData["Error"] = "Upload Error: Ensure you have the correct header fields and that the file is of .csv format.";
    }

    return View("Upload");
}

View:

@model IEnumerable<TestProject.TestViewModel>

                        @if (Model != null)
                    {
                        foreach (var item in Model.Where(x => x.IdMatched != null))
                        {
                            <tr>
                                <td>
                                    @item.MatchedId
                                </td>
                                <td>
                                    @item.Auth
                                </td>
                                <td>
                                    @item.CreditDate
                                </td>
                                <td>
                                    @item.CreditNumber
                                </td>
                                <td>
                                    @item.CreditAmount
                                </td>
                                <td>
                                    @item.DeniedDate
                                </td>
                                <td>
                                    @item.DeniedReasonId
                                </td>
                                <td>
                                    @item.DeniedNotes
                                </td>
                            </tr>
                        }
                    }

It's a little weird because I am populating the database with an SQL command. What am I missing here? Do I need to try and pass it through the controller action? Let me know if you need more information. Thanks!

Comparing rows of two tables in HIVE

I want to compare two tables in HIVE. More specifically, I want to see if table2 has any rows that are not in table1, and vice versa. So far I have this:

select count(A.PERS_KEY) from
table1 A left outer join table2 B
on A.PERS_GEN_KEY = B.PERS_KEY
where B.PERS_KEY IS NULL;

But this will only check for the PERS_KEY. How would I check to see if an entire row is in one table but not the other?

How to write readable SQL in VB.NET

When writing Sql in VB.NET one often ends up with something quite unreadable due to VB's lack of multi-line strings.

For Example:

Dim sql As String = "SELECT t1.Name, t1.Description, t1.Address, t1.PhoneNumber, t2.RegistrationID, t2.Date, t2.Description, t2.RegistrationStatus FROM Users t1 JOIN Registrations t2 ON t1.UserID = t2.UserID WHERE t2.RegistrationID = @RegistrationID"

You could break the string up using line-continuation characters, but the extra quote marks and line-continuation characters make this harder to read. Also it makes transferring the query between code and SSMS difficult.

Is there a solution that makes SQL readable within VB and also allows easy transfer of queries (via copy/paste) between VB code and SSMS (or any other SQL editor/IDE)?

SQL Multiple rows to single row with multiple columns

I have these data in a table in SQL:

Product    Size     Colour    Number

-------    ----     ------    ------

Jacket       S       Red         3

Jacket       M       Red         2

Jacket       S       Green       5

Shirt        S       Blue        1

Shirt        L       Blue        9

and I want to convert it dynamically without knowing how many rows I might have by combining all the same product rows to something like this:

Product   SRed    MRed    SGreen     SBlue    LBlue

-------   ----    ----    ------     -----    -----

Jacket     3       2        5

Shirt                                  1        9

Is this possible and how?

Thanks

Double increment where 2nd increment reflects 1st in sql for encounter data

I am building healthcare 837 encounters and need to set increments on the HL segments.

• C1 based on what is set on Criteria1 and C2 based on Criteria2.
• C2 will never have the same number as C1 and vice versa.
• C1 I was able to pull using row_number() over(order by (select Criteria1))

It's the C2 I am having a problem with.

 C1 | C2 | Criteria1 | Criteria2
  1 | 2 | ID1 | NID1
  1 | 3 | ID1 | NID2
  1 | 4 | ID1 | NID3
  5 | 6 | ID2 | NID4
  5 | 7 | ID2 | NID5
  5 | 8 | ID2 | NID6
  9 |10 | ID3 | NID7

Simplified query:

 SELECT cm.Criteria1, cm.Criteria2, cj.C1 
 FROM [dbo].[TBL1] cm 
 JOIN (
         SELECT cm.Criteria1,
                row_number() over(order by (select Criteria1)) as C1 
         FROM [dbo].[TBL1] cm 
         GROUP BY cm.Criteria1) cj on cj.Criteria1 = cm.Criteria1 
 GROUP BY cm.Criteria1, cm.Criteria2, cj.C1 Order by cj.C1 

want to delete oldest record when a new one is added leaving no more then 5 records

I have the following table:

id      user_id     message     timestamp
----    -------     --------    ----------
1        abc        message1    2015-07-27 19:10:40
2        def        message1    2015-07-27 19:20:41
3        xyz        message1    2015-07-27 19:30:41
4        abc        message2    2015-07-28 19:11:40
5        abc        message3    2015-07-28 19:12:40
6        abc        message4    2015-07-28 19:13:40
7        abc        message5    2015-07-28 19:14:40

When user 'abc' adds the 6th message I want the 1st one (oldest) to be deleted.

I'm trying the following two SQL queries but it only leaves 5 messages total not taking into account the user_id

INSERT INTO sample(user_id, message, created)
VALUES('abc', 'message6', now());

DELETE FROM sample 
WHERE user_id = 'abc' 
  AND id NOT IN (SELECT id 
                 FROM 
                     (SELECT id 
                      FROM sample 
                      ORDER BY id DESC 
                      LIMIT 5) x 
                ); 

Thanks in advance

Recursive CTE - Employee with multiple Manager

I have a table like follow

+-----------+------------+
| ManagerID | EmployeeID |
+-----------+------------+
| MAN001    | BOY001     |
| MAN001    | BOY002     |
| MAN001    | BOY003     |
| MAN001    | BOY004     |
| MAN001    | BOY005     |
| BOY005    | KID001     |
| BOY005    | KID002     |
| BOY005    | KID003     |
| BOY005    | KID004     |
| MAN002    | BOY005     |
| MAN002    | BOY004     |
| BOY004    | KID001     |
| BOY004    | KID002     |
| BOY004    | KID003     |
| BOY004    | KID004     |
| KID002    | SOM001     |
| KID002    | SOM002     |
| KID002    | SOM003     |
+-----------+------------+

The main point is that MAN001 does not exists in EmployeeID, meaning MAN001 is top level. But BOY005 can work for MAN001 or MAN002 and more ... The same with KID00X CAN work with Any manager MAN00X or BOY00X.

It is also possible that KID001 KID002 KID003 KID004 may have an employee, eg KID002 has three Employee SOM001 SOM002 and SOM003 BUT BOY005 will never be an employee of KID because we know that BOY005 is the manager for KID.

The output that I would like to get is as follow, where the column E can grow as much as the data has.

+--------+--------+--------+--------+-------+
|   M1   |   E1   |   E2   |   E3   |  ...  |
+--------+--------+--------+--------+-------+
| MAN001 | BOY001 |        |        |       |
| MAN001 | BOY002 |        |        |       |
| MAN001 | BOY003 |        |        |       |
| MAN001 | BOY004 |        |        |       |
| MAN001 | BOY005 | KID001 |        |       |
| MAN001 | BOY005 | KID002 |        |       |
| MAN001 | BOY005 | KID003 |        |       |
| MAN001 | BOY005 | KID004 |        |       |
| MAN001 | BOY005 | KID002 | SOM001 |       |
| MAN001 | BOY005 | KID002 | SOM002 |       |
| MAN001 | BOY005 | KID002 | SOM003 |       |
+--------+--------+--------+--------+-------+

I tried to follow this basic simple recursive cte. But the example does not consider if an employee exists in multiple manager. http://ift.tt/QOJbrI

Also another different case with the blog post above is in my case Top level ManagerID simply don't exists in EmployeeID

WITH Emp_CTE AS (
SELECT EmployeeID, ManagerID
FROM Employee
WHERE ManagerID IS NULL
UNION ALL
SELECT e.EmployeeID, , e.ManagerID
FROM HumanResources.Employee e
INNER JOIN Emp_CTE ecte ON ecte.EmployeeID = e.ManagerID
)
SELECT *
FROM Emp_CTE

Thank you

SQL Select (Value A at max value B) and other computed values (avg/min/max)

I have some weather data stored in a SQL Server database. The relevant columns are

[SampleDate], [SampleDateTime], [WindSpeed_Avg_2MIN], [WindDir_AVG_2MIN]

and store data at 2 minute intervals. The code gives me a summary by day for a month

SELECT 
    [SampleDate], 
    Max([WindSpeed_Avg_2MIN]) as PeakWind, 
    Avg([WindSpeed_Avg_2MIN]) as AverageWind
FROM
   (SELECT 
        [SampleDateTime], 
        [WindSpeed_Avg_2MIN],
        [WindDir_AVG_2MIN] 
    FROM 
        WeatherData
    WHERE 
        ((DATEPART(mm,SampleDateTime) = @Month) 
        AND (DATEPART(yyyy,SampleDateTime) = @Year))) as tblA
GROUP BY 
    [SampleDate]
ORDER BY 
    [SampleDate]

Which yields

SampleDate  PeakWind    AverageWind
----------------------------------------
15/01/01    3.9         1.18587301587302
15/01/02    4.6         1.60222531293463
15/01/03    6.6         1.86013888888888

What I want is to add a column that would show the Wind Direction [WindDir_AVG_2MIN] for the row that had the PeakWind. So for 720 rows in a day, I've got the AVG and MAX of [WindSpeed_Avg_2MIN], and I want to show the discrete value for [WindDir_AVG_2MIN] for the row that has the MAX of [WindSpeed_Avg_2MIN]

I can't seem to find the proper JOIN or sub query to get the result.

Any ideas?

Loop and group by

I have a trip that has sequence of stops

Trip  Stop  Time 
1     A     1:10
1     B     1:15
1     B     1:20
1     B     1:25
1     C     1:30
2     A     2:10
2     B     2:15
2     C     2:20
2     B     2:25  

I want to transfer the table to:

Trip  Stop  Time  WaitTime
1     A     1:10   0
1     B     1:15   10min
1     C     1:30   0
2     A     2:10   0
2     B     2:15   0
2     C     2:20   0
2     B     2:25   0

I'm wondering if a oracle query can achieve it or cursor? pseudo code: SELECT case when previousstop = stop then time-lag(time) over (partition by trip order by trip,time) as waittime, but I don't know how to group by B.

Split Table Into Two Separate Tables and Set New Primary Key as a Foreign Key

I have seen posts similar to what I need, but they all fall slightly short. I'm also not sure if what I need can be done. If it cannot be done, then any suggestions would be greatly appreciated.

I am reconstructing my database, and need to merge parts of three different tables into one. Currently I have three different types of tasks, let's call them DailyTasks, Reviews and Requests. All three tables have similar fields such as DateCreated, DateCompleted, EstimatedHours etc. I have created a parent table, called Tasks, which contains these common fields, and the other tables (DailyTasks, Reviews and Requests) only have fields that are specific to themselves. The child tables now also have a Foreign Key, TaskID, that points to the parent table. I believe this is standard database practice, correct me if I'm wrong.

Currently I am working in a test database, so I can pull the original data from the live database for testing purposes. I need to be able to pull the common fields from each table and place them in the parent table, while at the same time placing the new primary key into the TaskID column of the child table.

I would have like to of have used a join, but I don't believe I can insert into two tables that way while retrieving the Primary Key. My next thought if a join won't work is to use a cursor. Any help would be appreciated and I hope I've explained everything properly.

EDIT Here are the table's fields

TASKS (Parent)

• TaskID (PK)
• DateCreated
• DateCompleted
• DateDue
• EstHrs
• BaselineHrs
• Notes
• Priority
• CreatedBy

DailyTasks (Child)

• EngTaskID (PK)
• TaskID (FK)
• ProjectID
• Description
• AssignedTo

Requests (Child)

• DrawingRequestID (PK)
• TaskID (FK)
• DrawingLink
• PartNumber

Reviews (Child)

• JobAuditID (PK)
• TaskID (FK)
• ItemNumber
• CycleTime
• ToolingRequirements

Why does SQL try to convert a string to int rather than int to string for comparisons?

These queries return unexpected results for me.

SELECT 'Test' WHERE '00001000' = 1000
SELECT 'Test' WHERE '00001000' = '1000'
SELECT 'Test' WHERE 0001000 = 1000

I would expect it to be no results, no results, results, but instead the first query returns results. It seems that SQL will try and convert the string to an int to do the comparison, instead of the other way around. Is there a reason for this?

How can I get a count of Tickets Closed on a given day?

I have a table that stores the status changes of a ticket. Some tickets were re-opened after being closed. And in certain cases (example below) re-opened several times.

Ticket  SEQUENCE    NEWVALUE    OLDVALUE        TIMESTAMP
5879    15870       REQUEST     NULL        2015-06-10 12:13:28.000
5879    16256       Pending     REQUEST     2015-06-11 14:26:38.000
5879    21642       Open        Pending     2015-07-02 13:32:47.000
5879    21943       Pending     Open        2015-07-06 09:55:37.000
5879    23195       Open        Pending     2015-07-09 15:00:47.000
5879    24917       Pending     Open        2015-07-17 15:09:28.000
5879    25903       Closed      Pending     2015-07-22 11:13:03.000
5879    25940       Open        Closed      2015-07-22 12:56:05.000
5879    25972       Pending     Open        2015-07-22 13:54:30.000
5879    25996       Closed      Pending     2015-07-22 14:31:05.000
5879    26484       Pending     Closed      2015-07-24 14:43:28.000
5879    26776       Closed      Pending     2015-07-27 11:34:00.000

I am trying to determine the last time a ticket was Closed. But I have no idea how to even begin this query. Could someone provide a clue? Thanks so much!

How to create a DbCommand that is compatible with SQL Server and SQLite without a large condition?

HI lets say I have a C# code

private const string INSERT = "INSERT INTO Person VALUES (@FirstName, @LastName)";

public static DbCommand INSERTCOMMAND(IPerson person)
{
    DbCommand command = null;
    var sqlParams = new List<DbParameter>();

    if(SQLManagerFactory.SQLManager is SQLServerManager)
    {
        command = new SqlCommand(INSERT);
        sqlParams.Add(new SqlParameter("@FirstName", person.FirstName);
        sqlParams.Add(new SqlParameter("@LastName", person.LastName);
    }
    else // SQLiteManager
    {
        command = new SQLiteCommand(INSERT);
        sqlParams.Add(new SQLiteParameter("@FirstName", person.FirstName);
        sqlParams.Add(new SQLiteParameter("@LastName", person.LastName);
    }

    command.Parameters.AddRange(sqlParams.ToArray());
    return command;
}

Which is working perfectly fine. Of course in my production code, it is quite bigger and has a lot more location that does the similar things for different commands.

My question is that is there way to make this shorter? I do not wish to copy and paste code which essentially does the same thing except for calling the different constructors.

Thank you very much in advance.

SQL getting averages with multiple joins

I'm trying to write a single query using 3 tables.
The tables and their columns that I will be using are:

Sec – ID, Symbol
Hss – Code, HDate, Holiday
Fddd – ID, Date, Price

Given a symbol 'AAA', I need to get the ID from the first table and match it with the ID from the third table. The second table's date must match the third table's dates with the condition of Code=1 and Holiday=1. The Dates in the second and third table are in ascending order with most recent dates at the bottom. I want to get the average 50 day and 200 day prices. The dates in the tables are in ascending order so I want to make it descending and select the top 50 and 200 to get the average prices.

So far I can only get one average. I cannot add a second SELECT TOP 50 or add a subquery within the second avg().

SELECT AVG(TwoHun)TwoHunAvg   --, AVG(Fifty) AS FiftyAvg 
FROM (SELECT TOP 200 Fddd.price AS TwoHun    --, TOP 50 Fddd.price AS Fifty
FROM Sec 
JOIN Fddd
ON Sec.ID = Fddd.ID AND Sec.symbol = 'AAA' 
JOIN Hss 
ON Fddd.date = Hss.Hdate AND Hss.Code = 1 AND Hss.Holiday = 1 
ORDER BY Fddd.Date DESC) AS tmp;

Thanks in advance!

Join tables while returning distinct values from the second table

I would like to join two tables joined by a date value. Below are the tables I would like to join.

      **Table 1**                               **Table2**
ID  Name    Date    AnalysisID              ID  Name    Date    FileID
1   Test   8/1/14       50                  1   Test   8/1/14      10
1   Test   8/1/14       55                  1   Test   8/1/14      12
1   Test   8/1/14       64
1   Test   8/1/14       67       

When attempting to join, I'll get repeating FileIDs for each AnalysisID using the following sql

select t1.id
, t1.name
, t1.date
, t1.analysisid
, t2.fileid
from Table_1 t1
, Table_2 t2
where t2.id = t1.id
and t2.name = t1.name
and t2.date = t2.date

**Joined Table**

ID  Name    Date    AnalysisID   FileID
1   Test   8/1/14       50         10
1   Test   8/1/14       50         12
1   Test   8/1/14       55         10
1   Test   8/1/14       55         12
1   Test   8/1/14       64         10
1   Test   8/1/14       64         12
1   Test   8/1/14       67         10
1   Test   8/1/14       67         12

My desired results would look like the Following

**Desired Table**
ID  Name    Date    AnalysisID   FileID 
1   Test   8/1/14       50         10 
1   Test   8/1/14       55         12 
1   Test   8/1/14       64
1   Test   8/1/14       67 

I'm not sure how to accomplish this. I've tried removing duplicated using row_number solutions but I just get a really choppy looking table. Is there a better way to use joins here? Any help is appreciated. Thanks

SQL Search Column by Some Words

I am trying to write a program where I need SQL to select a column that doesn't exactly match the user input, just by a word or so. For example if I have a table that looks like this,

Type         Price
Fruit        $4.00  
Vegetables   $6.00

And if the user types in "Fruit Salad", how can I tell SQL to choose the column that contains the keywords "Fruit" or "Salad"?

Thanks!

SQL Server database won't shrink

I have a 31 GB database I got from a customer for a project. I'm coding and keeping the database in a 80GB Win7 VM. I got the db file and imported the database from that, and found that the space was being eaten up mostly by one table, with 240,000 images. I dropped most of those rows and now have 29,000MB available. I can't perform lots of functions because the drive is down to it's last few GB, and there are still a lot of records to modify (I'm changing a data type in several tables, but keep getting a active_transaction error). It's not shrinking via DBCC_SHRINKDATABASE(0) or Tasks > Shrink > Database or Files. It only shrinks to the size of the database when I first got it, it seems. How do I get it to release that space? If I can', can I export what data is still there to a smaller database, and then restore from that?

Running SQL Server 2012 and Management Studio.

Thanks,

Tony

SQL query on M:N relation

I was told that there may return some dulipcate records when a select table join on M:N relationship tables, but I cannot find out what situation would be.

I would like to know if it is true, and in what situation ? Thanks a lot.