Hi All,
I have a table called Employee with EmpID,Salary,Name fields.
I want to get top two employees with maximum salary.
How can i write this query ?
Hello!
In my database, I have a "users", a "quests" and a "questings" table.
A user can solve a quest. Solving a quest will save the "user_id" and the "quest_id" in my "questings" table.
Now, I want to select all quests, a user has NOT solved (meaning there is no entry for this user and quest in "questings" table)!
Let's say the user has the id 14. How to write this query?
After solving this query, I want to filter the results, too.
A quest and a user has a city, too.
What to do for writing a query which returns all quests, a user has NOT solved yet, in the users city (user city == quest city)?
I have an import between 2 linked servers. I basically got to get the data from a multiple join into a table on my side.
The current query is something like this:
select a.*
from db1.dbo.tbl1 a
inner join db1.dbo.tbl2 on ...
inner join db1.dbo.tbl3 on ...
inner join db1.dbo.tbl4 on ...
inner join db2.dbo.myside on ...
db1 = linked server
db2 = my own database
After this one, I am using an insert into + select to add this data in my table which is located in db2. (usually few hundred records - this import running once a minute)
My question is related to performance. The tables on the linked server (tbl1, tbl2, tbl3, tbl4) are huge tables, with millions of records, and it is slowing down the import process.
I was told that, if I do the join on the "other" side (db1 - linked server) for example in a stored procedure, than, even if the query looks the same, it would run faster. Is that right? This is kinda hard to test. Note that the join contains a table from my database too.
Also. are there other "tricks" I could use in order to make this run faster? Thanks
I want to use the foreach container to iterate through a folder matching something like: "Filename_MMYYYY.xls". That's easy enough to do; but I can't seem to find a way to parse the MMYYYY from the filename and add it to a variable (or something) that i can use as a lookup field for my DimDate table. It seems possible with a flat file data source, but not an excel connection. I'm using Visual Studio 2005. Please help!
I am adding a new GUID/Uniqueidentifier column to my table.
ALTER TABLE table_name
ADD VersionNumber UNIQUEIDENTIFIER UNIQUE NOT NULL DEFAULT NEWSEQUENTIALID()
GO
And when ever a record is updated in the table, I would want to update this column "VersionNumber". So I create a new trigger
CREATE TRIGGER [DBO].[TR_TABLE_NAMWE]
ON [DBO].[TABLE_NAME]
AFTER UPDATE
AS
BEGIN
UPDATE TABLE_NAME
SET VERSIONNUMBER=NEWSEQUENTIALID()
FROM TABLE_NAME D
JOIN INSERTED I ON D.ID=I.ID/* some ID which is used to join*/
END
GO
But just realized that NEWSEQUENTIALID() can only be used with CREATE TABLE or ALTER TABLE. I got this error
The newsequentialid() built-in function can only be used in a DEFAULT expression for a column of type 'uniqueidentifier' in a CREATE TABLE or ALTER TABLE statement. It cannot be combined with other operators to form a complex scalar expression.
Is there a workaround for this ?
Edit1: Changing NEWSEQUENTIALID() to NEWID() in the trigger solves this, but I am indexing this column and using NEWID() would be sub-optimal
I have a simple query that relies on two full-text indexed tables, but it runs extremely slow when I have the CONTAINS combined with any additional OR search. As seen in the execution plan, the two full text searches crush the performance. If I query with just 1 of the CONTAINS, or neither, the query is sub-second, but the moment you add OR into the mix the query becomes ill-fated.
The two tables are nothing special, they're not overly wide (42 cols in one, 21 in the other; maybe 10 cols are FT indexed in each) or even contain very many records (36k recs in the biggest of the two).
I was able to solve the performance by splitting the two CONTAINS searches into their own SELECT queries and then UNION the three together. Is this UNION workaround my only hope?
Thanks.
SELECT a.CollectionID
FROM collections a
INNER JOIN determinations b ON a.CollectionID = b.CollectionID
WHERE a.CollrTeam_Text LIKE '%fa%'
OR CONTAINS(a.*, '"*fa*"')
OR CONTAINS(b.*, '"*fa*"')
Execution Plan (guess I need more reputation before I can post the image):
i have a table with 6 fields. the columns are ID, new_id price,title,Img,Active. I have datawhich is duplicated for the price column.
When I do a select i want to show only distinct rows where new_id is not the same.
e.g.-
ID New_ID Price Title Img Active
1 1 20.00 PA-1 0X4... 1
2 1 10.00 PA-10 0X4... 1
3 3 20.00 PA-11 0X4... 1
4 4 30.00 PA-5 0X4... 1
5 9 20.00 PA-99A 0X4... 1
6 3 50.00 PA-55 0X4... 1
When the select statement runs, only rows with ID (1,4,9,6) should show. Reason being the new_ID with the higher price should show up.
How can i do this?
Found this solution to get substring after slash () character
DECLARE @st1 varchar(10)
SET @st1 = 'MYTEST\aftercompare'
SELECT @st1
,SUBSTRING(@st1, CHARINDEX('\', @st1) + 1, LEN(@st1))
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/5c3a5e2c-54fc-43dd-b12c-1a1f6784d7d8/tsql-get-substring-after-slash-character
But is there a way to get substring after second slash or even more?
DECLARE @st1 varchar(50)
--Added more slashes
SET @st1 = 'MYTEST\aftercompare\slash2\slash3\slash4'
SELECT @st1
--This part would need some work
--,SUBSTRING(@st1, CHARINDEX('\', @st1) + 1, LEN(@st1))
And getting only the substring between the slashes.
Values: [1] "aftercompare" - [2] "slash2" - [3] "slash3" - [4] "slash4"
If i have a table with two fields.customer id and order.
let's say i have in total order ID 1,2,3,4
all the customer can have all the four orders.like below
1234 1
1234 2
1234 3
1234 4
3245 3
3245 4
5436 2
5436 4
you can see above that 3245 customer doesnt have order id 1 and 2.
how could i print in the query output like
3245 1
3245 2
5436 1
5436 3
EDIT: i dont have order table but i have list of order's like we can hard code it in the query(1,2,3,4) i dont have an orders table.
HI,
Currently i have one big datacontex with 35 tables (i dragged all my DB tables to the designer). I must admit it is very comfortable cause i have ORM to my full DB and query with linq is easy and simple.
My questions are:
1. Would you consider it bad design to have one datacontext with 35 tables or should i split it to logic units?
2. Is there any performance penalties for using such a big datacontext?
Thanks, Pini.
If you have a table with a clustered index on the Primary Key (int), is it redundant and bad to have one (ore more) non-clustered indexes that include that primary key column as one of the columns in the non-clustered index?
So, MongoDB defaults to "AND" when finding records. For example:
db.users.find({age: {'$gte': 30}, {'$lte': 40}});
The above query finds users = 30 AND <= 40 years old.
How would I find users <= 30 OR = 40 years old?
I have a bunch of records I want to move to another database and I just want to create a bunch of inserts that I can copy and paste. I've seen someone do this before but I can't figure it out. I'm not getting the escapes right.
It's something like this where 'Code', 'Description' and 'Absent' are the columns I want from the table.
SELECT 'INSERT INTO AttendanceCodes
(Code, Description, Absent)
VALUES
(' + Code + ',' + Description + ',' + Absent')'
FROM AttendanceCodes
The end result should be a slew of INSERTS with the correct values like this:
INSERT INTO AttendanceCodes
(Code, Description, Absent)
VALUES
('A','Unverified Absence','UA')
can any one explain hiow this query works..It's for getting the Nth largest elemet from a table.here it's 4 th largest
SELECT a.ID
FROM tblitem a
WHERE (4) = (select count(*)
from tblItem b
where b.id < a.id)
Thanks in advance
Hi All,
Am having columns as category and songs in my table for each category there are almost 10 songs and in total there are 7 category such that which was tabled as
category1 songCategory1a
category1 songCategory1b
category1 songCategory1c
---
category2 songCategory2a
category2 songCategory2b
category2 songCategory2c
---
category3 songCategory3a
category3 songCategory3b
category3 songCategory3c
---
like that there is table in that i want to get the result as
category1
category2
category3
category4
kindly any one help me ,
i tried
(from s in _context.db_songs
select new { s.Song_Name, s.Song_Category }).Distinct().ToList(); but it didnt work its resulting as such.
Hi. I need to set datetime variable to two days from now but it's time part must be 18:00.
For example if i call getdate() now i'll get 2010-05-17 13:18:07.260. I need to set it to 2010-05-19 18:00:00.000.
Does anybody have a good snippet for that or any ideas how to do it right?
I have a report that we need to link (which we've checked to be working) to in a JSF project, the link looks like the following:
http://www.example.com/report/summary&rs:Command=Render
However when we try to load the page that links to it we get the following error:
The reference to entity "rs:Command" must end with the ';'
How can I link to the report within my pages and prevent it from trying to parse the rs:Command?
If I want to get a user that has the email address of '[email protected]', how do I pass that as a parameter in linq?
ie.:
var a = from u in Users
where u.Email = @email
Select u;
So this would be used in my method:
public static GetuserByEmail(string email)
Do I just pass in the variable or?
I currently have the following code to generate a sales report over the last 30 days. I'd like to know if it would be possible to use linq to generate this report in one step instead of the rather basic loop I have here.
For my requirement, every day needs to return a value to me so if there are no sales for any day then a 0 is returned.
Any of the Sum linq examples out there don't explain how it would be possible to include a where filter so I am confused on how to get the total amount per day, or a 0 if no sales, for the last days I pass through.
Thanks for your help,
Rich
//setup date ranges to use
DateTime startDate = DateTime.Now.AddDays(-29);
DateTime endDate = DateTime.Now.AddDays(1);
TimeSpan startTS = new TimeSpan(0, 0, 0);
TimeSpan endTS = new TimeSpan(23, 59, 59);
using (var dc = new DataContext())
{
//get database sales from 29 days ago at midnight to the end of today
var salesForDay = dc.Orders.Where(b => b.OrderDateTime > Convert.ToDateTime(startDate.Date + startTS) && b.OrderDateTime <= Convert.ToDateTime(endDate.Date + endTS));
//loop through each day and sum up the total orders, if none then set to 0
while (startDate != endDate)
{
decimal totalSales = 0m;
DateTime startDay = startDate.Date + startTS;
DateTime endDay = startDate.Date + endTS;
foreach (var sale in salesForDay.Where(b => b.OrderDateTime > startDay && b.OrderDateTime <= endDay))
{
totalSales += (decimal)sale.OrderPrice;
}
Response.Write("From Date: " + startDay + " - To Date: " + endDay + ". Sales: " + String.Format("{0:0.00}", totalSales) + "<br>");
//move to next day
startDate = startDate.AddDays(1);
}
}
eg:table
pkey --guid
annualpay
datefrom
dateto--if null means current record
percentannualincrease
percent annual increase will be calculated only if there is a difference in newly inserted and previously existing last differing value.
percentannualincrease =
([newannualpay-just previous pay(if different from current)]/newannualpay)*100
eg
newid(),5000,today,null,0--very first row
newid(),5000,today+1,null(*),0
newid,5500,today+2,null(*),?????????????--> need to be calculated before insert
*--insert will close the previous record by updating dateto=null to todays date
How can I do this stuff in a trigger???
I have a temporary table that I'm using and I have a unique column, example:myID INT UNIQUE
Would this create an index on that column? Or do I have to explicitly create the index?
I have a table that has millions of records and we are looking at implementing table partitioning. Looking at it we have a foreign key "GroupID" that we would like to partition on. Is this possible?
The Group will have more entries added to it, so as new GroupID's are added can the partition's be made dynamically?