In SQL Server, can we set if it rounds or truncates by default?

I have a large number of inserts and updates coded in my program that involves money, and I cam across a serious rounding bug. The majority of the time, I want to truncate the values, instead of the SQL Server default of rounding.

Rather than changing all my existing code, is there a way I can set this at a column level? Table level? Server level?

Edit: Sorry, the column type is a decimal(10, 2). Example: I want to enter 100.555 and either make it save as 100.55 or 100.56, as it currently rounds.

SQL 2012 locating duplicate column entries in a table

Hello I am using SQL 2012 and trying to identify rows where the SourceDataID column has two unique entries in the PartyCode column and having difficulties

SELECT PartyCode, SourceDataID, count (*) as CNT
FROM CustomerOrderLocation (nolock) 
GROUP BY PartyCode, SourceDataID
HAVING (Count(PartyCode)>1)
ORDER BY PartyCode

Results are returning as such:

W3333 948_O 31 (party code/sourcedataid/CNT)

This is showing me the total entries where the Partycode and the SourceDataID are listed together in the table; However I need it to show a count of any instances where W333 lists 948_O as the SourceDataID more than once.

I'm not having luck structuring the query to pull the results I am looking to get.

Mysql function that query a table and reconstruct a string from the query-result

I'm a newbie in mysql programming, and i would create a mysql function myFunction with a string parametre; that query a table and reconstruct a string from the query-result like this example :

myTable
 ---------------
|id   |  value   |
 ---------------
| id1 |value1    |
| id2 |value2    |
| id3 |value3    |
| id4 |value4    |
 ---------------

Calling this function is like this

myFunction('value2#value1#value4')

and must return

 'id2#id1#id4'

Thank you very much

SQL permissions based on record value (if column X = Y for user1 allow Inserts/Updates etc.)

I need to run queries as a "user" which is a record in a table, with permissions based on a record value of that user.

I have a database with a tUsers Table, such as:

ID    Username       Email         Role
1      Pieman       mail.com       Admin
2     Cakedude     mail.co.uk   Receptionist
3      Muffin        gh.com        Other

I need to have it so only "users"/records with "Role" of "Admin" can view and edit the table, and "Receptionist" view it etc.

I know of and use GRANT for permissions, but don't know how to run a query as a user based on a table record and to have the permission only GRANTED if that users' role is "Admin"

So if I have:

USE DB1;
GRANT SELECT ON OBJECT::tUsers TO Admins;
GO 
SELECT * FROM tUsers

How do I make that run as say tUser with ID 1, and the GRANT if the users' role = "Admin"

I'm sure I've seen this done before.

I'm fairly new and still learning the correct terminology, so if this is a duplicate question, or is essentially just describing an sql Function sorry.

Unable to create a constant value of type .... Only primitive types or enumeration types are supported in this context

The following database query is returning the following error.

I have a feeling it is to do with the contains function within the where clause but im not too sure?

Any ideas??

     var roomToMatch = db.rooms.Where(r=>r.roomNumber==number && r.buildingCode==code).First();
        var yearID = Convert.ToInt32(year);
        var semesterID = Convert.ToInt32(semester);
        var rounds = db.rounds.Where(r => r.semester == semesterID && r.year == yearID);

        int[] days = new int[5] { 1, 2, 3, 4, 5 };
        int[] times = new int[9] { 1, 2, 3, 4, 5, 6, 7, 8, 9 };
        List<request> bookings = new List<request>();
        var requests = db.requests.Include(r => r.module.staffs).Where(r => r.deptCode == user).Where(r => r.booked == 1).Where(r => rounds.Contains(r.round)).Where(r => r.rooms.Contains(roomToMatch));
        bookings = requests.ToList();

the full error

    Unable to create a constant value of type 'Timetabling06.Models.room'. Only primitive types or enumeration types are supported in this context.

Subquery is faster using a function

I have a long query (~200 lines) that I have embedded in a function:

CREATE FUNCTION spot_rate(base_currency character(3),
                          contra_currency character(3),
                          pricing_date date) RETURNS numeric(20,8)

Whether I run the query directly or the function I get similar results and similar performance. So far so good.

Now I have another long query that looks like:

SELECT x, sum(y * spot_rates.spot)
FROM (SELECT a, b, sum(c) FROM t1 JOIN t2 etc. (6 joins here)) AS table_1,
     (SELECT
        currency,
        spot_rate(currency, 'USD', current_date) AS "spot"
      FROM (SELECT DISTINCT currency FROM table_2) AS "currencies"
     ) AS "spot_rates"
WHERE
     table_1.currency = spot_rates.currency
GROUP BY / ORDER BY

This query runs in 300 ms, which is slowish but fast enough at this stage (and probably makes sense given the number of rows and aggregation operations).

If however I replace spot_rate(currency, 'USD', current_date) by its equivalent query, it runs in 5+ seconds.

Running the subquery alone returns in ~200ms whether I use the function or the equivalent query.

Why would the query run more slowly than the function when used as a subquery?

ps: I hope there is a generic answer to this generic problem - if not I'll post more details but creating a contrived example is not straightforward.

---

EDIT: EXPLAIN ANALYZE run on the 2 subqueries and whole queries

• subquery with function: http://ift.tt/1cvVnZA
• subquery with direct query: http://ift.tt/1KRONb0
• whole query with function: http://ift.tt/1cvVpk2
• whole query with direct query: http://ift.tt/1KROMUh
• just the function body, using one set of arguments: http://ift.tt/1cvVofQ

How to get how many minutes the user is idle by using user lastActivity?

Im using PhpMyadmin Mysql

this is my sql record from database, How can I use timediff to subtract NOW(); from last activity which is user is user1 and get how many minutes user1 is idle.

id   lastActivity           user
1    2015-05-08 00:20:40    user1
2    2015-05-08 00:32:29    user2  

Having Trouble Running An SQL Update Statement

Forgive me but I'm relatively new to SQL.

I am trying to update a column of a table I created with a function I created but when I run the Update Statement, nothing happens, I just see the underscore flashing (I'm assuming its trying to run it). The Update Statement is updating around 60,000 fields so I assume it should take a little while but it's been 10 minutes and no good.

I would just like to know if anyone knows just some general reasons that the underscore may be flashing. I know this is super general but I've just never seen this before.

Here's an image of what I'm talking about: http://ift.tt/1P6hA1q

How to query a one-to-many table based on a condition on a date field?

I have Client table with ont-to-many with Comment table as the following:

Client table:

Id name status....

Comment table:

Id description created_date client_id

this is what i tried:

    @Entity
@Table(name = "client")
public class Client implements Serializable {
/** The comments. */
    private Set<Comment> comments = new HashSet<Comment>(0);
@OneToMany(fetch = FetchType.EAGER, mappedBy = "client", cascade = CascadeType.ALL)
    @OrderBy(clause = "createdDate")
    //@Fetch(FetchMode.SELECT)
    public Set<Comment> getComments() {
        return comments;
    }
}
@Entity
@Table(name = "comment")
public class Comment implements Serializable {

    /** The created date. */
    private Date createdDate;

@ManyToOne(fetch = FetchType.LAZY)
    @JoinColumn(name = "CLIENT_ID", nullable = false)
    public Client getClient() {
        return client;
    }

    /**
     * Sets the client.
     *
     * @param client
     *            the new client
     */
    public void setClient(Client client) {
        this.client = client;
    }

}

    public List<Client> getLastEditedDateFromComment() {
        try {
            Calendar cal = Calendar.getInstance();
            //cal.add(Calendar.HOUR_OF_DAY, 2);
            cal.add(Calendar.DATE, -7);
            //.add(Restrictions.le("comments.editedDate", cal.getTime()))
            //System.out.println("ClientDAO.getLastEditedDateFromComment()"+cal.getTime());
            startOperation();
            Criteria criteria = session
                    .createCriteria(Client.class)
                    .createAlias("comments", "comments")
                    .add(Restrictions.eq("status", "Pending"))
                    .add(Restrictions.le("comments.createdDate", cal.getTime()))
                    .setProjection(
                            Projections.distinct(Projections.projectionList()
                                    .add(Projections.groupProperty("clientName"))
                                    .add(Projections.groupProperty("id"))
                                    .add(Projections.groupProperty("status"))
                                    .add(Projections.property("clientName"), "clientName")
                                    .add(Projections.property("id"), "id")
                                    .add(Projections.property("status"), "status")
                                    .add(Projections.max("comments.createdDate"))));
            // .setProjection(Projections.max("comments.editedDate"));
            // Integer maxAge = (Integer)criteria.uniqueResult();
            List<Client> clist = criteria.setResultTransformer(Transformers.aliasToBean(Client.class))
                    .list();
            tx.commit();
            return clist;
    }
    }

the above code generates:

select distinct this_.clientName as y0_, this_.ID as y1_, this_.status as y2_, this_.clientName as y3_, this_.ID as y4_, this_.status as y5_, max(comments1_.createdDate) as y6_ from zyhnavkp.client this_ inner join zyhnavkp.comment comments1_ on this_.ID=comments1_.CLIENT_ID where this_.status=? and comments1_.createdDate<=? group by this_.clientName, this_.ID, this_.status

I need a query to get all clients with the created_date passed 1 week ONLY from comment table where status ='Pending', I'm trying to do this hibernate, any sql query is acceptable :)

Thanks in advance :)

SQL: Searching X table and returning rows from Y based on X data

1. Search goals for rows where userid = 1.
2. Using returned rows, search checkin where biometricid's match, along with the userid, and filter out rows that are older than the goal date.

Note: Both the userid and biometricid are foreign tables.

How may I do this with one query?

checkin
id  | userid    | date                  | biometricid   | value
1   | 1         | 2015-01-10 00:00:00   | 1             | 9
2   | 1         | 2000-05-11 00:00:00   | 1             | 7
3   | 2         | 2015-01-10 00:00:00   | 1             | 9
4   | 1         | 2015-01-10 00:00:00   | 2             | 1
5   | 1         | 2017-01-11 00:00:00   | 1             | 4

goals
id  | userid | date                 | biometricid   | value 
1   | 1      | 2000-01-05 00:00:00  | 1             | 3
2   | 1      | 2015-01-01 00:00:00  | 2             | 2
3   | 2      | 2015-01-01 00:00:00  | 1             | 2

desired result
id  | date                  | biometricid   | value 
1   | 2015-01-10 00:00:00   | 1             | 9
2   | 2017-01-11 00:00:00   | 1             | 4

SQL: Pulling info from a column and building new columns

I’m building a report where I am combining two different databases. As it is this query is functional.

select  t.[Test ID],
          [Business Group],
          [Department],
          [SubGroup],
          [Regulation Source],
          [Risk Level],
          [Regulation Title],
          [Data Points],
          [Control Test Question(s)],
          [Compliance Test Questions],
        r.MonthSubmitted,
          CompliancePassPercent,
          ControlPassPercent


from DefaultOperationalRisk..RCSATracker             T (nolock)
left join DefaultOperationalRisk..TB_FLODTestResults R (nolock)

on T.[Test ID]=R.[TestName]

where t.[Control Testing Status 3] not like 'test retired' 
or    t.[Control Testing Status 3] not like 'pending test development'
and r.MonthSubmitted > GETDATE()-90

The problem is that the columns from TB_FLODTestResults should look more like this:

AprilCompResults|AprilControlResults|MarchCompResults|MarchControlResults|

Instead these columns come out looking like this:

MonthSubmitted|CompliancePassPercent|ControlPassPerent|

This way means a lot of my information is duplicated. I need the results to be based on the Test ID column from the RCSATracker database.

The thread from this question game me a start (SQL: Move data in one column to different columns).

SELECT a.TestName, 
       b.ControlPassPercent AS 'April Control Results', 
       c.ControlPassPercent AS 'March Control Results', 
       d.ControlPassPercent AS 'February Control Results' 
FROM   (SELECT DISTINCT testname 
        FROM   DefaultOperationalRisk..TB_FLODTestResults) a 
       LEFT JOIN (SELECT testname, 
                         ControlPassPercent
                  FROM   DefaultOperationalRisk..TB_FLODTestResults
                  WHERE  MonthSubmitted = 'March 2015') b 
              ON b.TestName = a.TestName 
       LEFT JOIN (SELECT TestName, 
                         ControlPassPercent
                  FROM   DefaultOperationalRisk..TB_FLODTestResults 
                  WHERE  MonthSubmitted = 'April 2015') c 
              ON c.TestName = a.TestName 
       LEFT JOIN (SELECT TestName, 
                         ControlPassPercent 
                  FROM   DefaultOperationalRisk..TB_FLODTestResults 
                  WHERE  MonthSubmitted = 'February 2015') d 
              ON d.TestName = a.TestName 

I would like the monthly results/columns to have a three month look back not including the current month. So basically a rolling query. Also I have not been able to fit the code I wrote from the forum thread into my query at the top.

Counting total records vs status

I'm looking to do an update for a batch when all the jobs for that batch are completed. To do this I want to compare the total number of jobs vs the total completed. I'm doing that with the query below, however it's a pretty slow query. Any suggestions to improve this? Or alternate ways to approach the main update?

SELECT DISTINCT j.BatchId, 
    j.JobStatusId,
    COUNT(j.BatchId) OVER(PARTITION BY j.BatchID, j.JobStatusID),
    COUNT(j.BatchId) OVER(PARTITION BY j.BatchID) 
FROM [Job] j 
ORDER BY j.BatchID

Want specific years between date joined and now

I am trying to get the members of a company that qualify for 'EMERITUS' status.

To qualify, one must be a member for 35 years from the date joined 'JOIN_DATE' and must be >=65 years of age to qualify 'BIRTH_DATE'. I want to see >= 2015 under the 'EMERITUS' column. Does this query make sense?

SELECT 
  N.ID, N.FULL_NAME, N.MEMBER_TYPE,
  N.JOIN_DATE,DA.BIRTH_DATE,
  (SELECT CASE 
     WHEN DATEDIFF(YEAR,N.JOIN_DATE,GETDATE()) + 35 > DATEDIFF(YEAR,DA.BIRTH_DATE,GETDATE()) + 65 
       THEN CONVERT(VARCHAR(4),YEAR(N.JOIN_DATE) + 35)
     WHEN DATEDIFF(YEAR,N.JOIN_DATE,GETDATE()) + 35 < DATEDIFF(YEAR,DA.BIRTH_DATE,GETDATE()) + 65 
       THEN CONVERT(VARCHAR(4),YEAR(DA.BIRTH_DATE) + 65)
     ELSE NULL
   END) AS 'EMERITUS'

Get the last transaction where date is less than 1 year ms access

I have this code:

    cnn.Open()
        query = "SELECT LAST(AnnualPayments.DueDate), Members.FirstName, Members.LastName " & _
        "FROM AnnualPayments INNER JOIN Members ON AnnualPayments.MemID = Members.ID " & _
        "WHERE (((DateDiff('d', Now(), AnnualPayments.DueDate)) < 1)) " & _
        "GROUP BY  Members.FirstName, Members.LastName,  AnnualPayments.DueDate " & _
        "ORDER BY AnnualPayments.DueDate DESC"

        cmd = New OleDbCommand(query, cnn)

        Dim dt As New DataTable
        ds = New DataSet
        ds.Tables.Add(dt)
        da = New OleDbDataAdapter(cmd)
        da.Fill(dt)

        Dim newRow As DataRow

        For Each newRow In dt.Rows

            MainForm.lstView1.Items.Add(newRow.Item(0)) 'this is for ID
            MainForm.lstView1.Items(MainForm.lstView1.Items.Count - 1).SubItems.Add(newRow.Item(1)) 'this is for date
            MainForm.lstView1.Items(MainForm.lstView1.Items.Count - 1).SubItems.Add(newRow.Item(2)) 'this is for firstname
        Next

    Catch ex As Exception
        GetErrorMessage(ex)
    Finally
        CloseConnection()
    End Try

What it's supposed to do is show only the data if the due date is more than 1 day from the date today. Can you correct my mistake please. Thanks.

SQL: Not Like produces different results than what would be Like's opposite

So, I'm practicing for an exam (high school level), and although we have never been thought SQL it is necessarry know a little when handling MS Access.

The task is to select the IDs of areas which names does not correspond with the town's they belong to.

In the solution was the following example:

SELECT name 
FROM area 
WHERE id not in (SELECT areaid 
           FROM area, town, conn 
           WHERE town.id = conn.townid 
           AND area.id = conn.areaid AND 
               area.name like "*"+town.name+"*");

It would be the same with INNER JOINS, just stating that, because Access makes the connection between tables that way.

It works perfectly (well, it was in the solution), but what I don't get is that why do we need the "not in" part and why can't we use just "not like" instead of "like" and make the query in one step.

I rewrote it that way (without the "not in" part) and it gave a totally different result. If I changed "like" with "not like" it wasn't the opposite of that, but just a bunch of mixed data. Why? How does that work? Please, could someone explain?

Edit (after best answer): It was more like a theoretical question on how SQL queries work, and does not needed a concrete solution, but an explanation of the process. (Because of this I feel like the sql tag however belongs here)

How can I link PHP with a SQL services over bluemix?

I am deploying a web app using bluemix. I am using php and SQL DB service. But I have a question, How Can I link my php code to make a query in SQL service in bluemix ?

Running a count of something against something else to find duplicates in MS SQL

I've not really done much with count before outside of basic stuff. The basic thing I'm trying to do find any point in a table these two specific Vendor IDs (say xxx8 and xxx5) are sharing the same batch number.

I'm assuming I want to run a count vendor id against batch number, but I'm not sure now to structure that.

sql Loader read file with more or less column

I need to create a .ctl file that can read source file wit less or more row... Example

file a: a;b;c

file b: a;b

load data
APPEND 
into table table_of_parameter
fields terminated by ";" optionally enclosed by '"'
TRAILING NULLCOLS
(
parameters1,
parameters2,
parameters3
)

and I want this:

select * from table_of_parameter

parameters1 parameters2 parameters3

 a              b              c
 a              b           null (o something else)

there's a way to do this on ctl file?

Sort group of records that are resulted from ORDER BY FIELD

In a MySQL table, I want to sort the group of records that I get by ORDER BY FIELD()

More specifically, Suppose, I ran the following query:

SELECT name,status,date FROM memberTickets ORDER BY FIELD(status,7,10,3,4) ASC

and got the following result:

---------------------------------------------------------
|    name    |    status    |            date           |
---------------------------------------------------------
|    A       |      7       |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    B       |      7       |    2015-05-07 00:00:00    |
---------------------------------------------------------
|    C       |      7       |    2015-05-03 00:00:00    |
---------------------------------------------------------
|    D       |      10      |    2015-05-08 00:00:00    |
---------------------------------------------------------
|    E       |      10      |    2015-05-01 00:00:00    |
---------------------------------------------------------
|    F       |      10      |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    G       |      10      |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    H       |      3       |    2015-05-03 00:00:00    |
---------------------------------------------------------
|    I       |      3       |    2015-05-08 00:00:00    |
---------------------------------------------------------
|    J       |      3       |    2015-05-01 00:00:00    |
---------------------------------------------------------
|    K       |      4       |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    l       |      4       |    2015-05-07 00:00:00    |
---------------------------------------------------------

Now, I want to sort the records in such an way so that the position of the group of records remain same(as set by order by field), but the records in each group are sorted with the date attribute as descending order, like the following:

---------------------------------------------------------
|    name    |    status    |            date           |
---------------------------------------------------------
|    A       |      7       |    2015-05-07 00:00:00    |
---------------------------------------------------------
|    B       |      7       |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    C       |      7       |    2015-05-03 00:00:00    |
---------------------------------------------------------
|    D       |      10      |    2015-05-08 00:00:00    |
---------------------------------------------------------
|    E       |      10      |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    F       |      10      |    2015-05-05 00:00:00    |
---------------------------------------------------------
|    G       |      10      |    2015-05-01 00:00:00    |
---------------------------------------------------------
|    H       |      3       |    2015-05-08 00:00:00    |
---------------------------------------------------------
|    I       |      3       |    2015-05-03 00:00:00    |
---------------------------------------------------------
|    J       |      3       |    2015-05-01 00:00:00    |
---------------------------------------------------------
|    K       |      4       |    2015-05-07 00:00:00    |
---------------------------------------------------------
|    l       |      4       |    2015-05-05 00:00:00    |
---------------------------------------------------------

Any idea or suggesions how to do that?

Importing and Splitting Excel Sheets into Tables

I have 6 excel sheets that I'm importing into sql server and each sheet will be its own table. They all have a Compound Name and Compound Type column which I would like to make into a Compound table that they all would refer to by ID. Is there an easy way to do this with minimal sql?

The closest I can get is a wizard that will split up each table into the two tables I want, but doing this for each table gives me 6 Compound tables. I'm new to sql and I can't immediately think of a way to combine these tables and preserve the references while not creating duplicates.

Thank you for any help.