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