I'd like to use sql to select top (#) random row into another table, but it inserts the same record a few times sometimes. I want to avoid duplicates to be inserted, what shall I do?
what I've got for now is
INSERT INTO CompWin (PrizeID, EntryID, UserID) SELECT TOP (50) PrizeID, EntryID, UserID FROM CompEntry ORDER BY NEWID()
How to get the date at which a record is created in SQL?..i.e, if i add a record to a database table db1 at 8/10/2010 11:30, I want to get the datetime, i.e 8/10/2010 11:30..;.Also let me know how to compare datetime values..
if(8/10/2010 11:30 >8/10/2010 10:30) will work or not?
What i need is i need to bind last 10 Order in Gridview/details view with paging, and the same order detail also fetch from database and shown in another gridview.. if user click any oderNo in first Gridview/DetailView then i need to show Respective order detail in anther gridview..
without postback coz we already fetch 10 order with orderdetails from the database..and if user click next 10 orderNo using paging then again we need to load 10 orderNo with respective orderdeatils from database like this going on....as well as let me know how to take print of particular oder and order detail....?
in DataBase Attendancedate is save in this format 4/5/2010 4:16:28 PM ..i want to query like this select * from EmployeeAttendance where Attendancedate='4/5/2010' but this giv error
i wants to display the related informations of each record of dataset. That mean, dataset having rows for A,B,C and etc..
Then i wants to display the information in same Gridview of each record(A,B,C). i wrote the below my coding, it dispaly info of the last row of dataset only. pls correct the coding.
so a given Customer can have many orders in this table however I only want to return the last order (using OrderDate) for each customer. how do you do this? I assume that I need to group by customerID and then use max on the OrderDate but not sure of the exact syntax.
How to create Jobs, I am using SQL 2005, i need a query which should get current date and compare current date with date in table and Send Email according to Job scheduled.
I have the following query which gets the top record SELECT TOP 1 username from users GROUP BY username ORDER BY COUNT(*) DESC. now i have a second field which gets the numbers of views of each user i need to return just ONE single field and that is the same username field but based on views field i can do this:
SELECT TOP 1 username,views from users GROUP BY username,views ORDER BY COUNT(*) DESC but i just need that username field based on views not both!
i would like to query out the next record and previous record guid from the current record guid.But i don't want to use the ID + 1 or ID - 1 because the ID is not secure for query like this. I am using MS SQL.
Id - Date - Type 1 - 2011-03-01 - 1 2 - 2011-03-02 - 0 3 - 2011-03-03 - 0 4 - 2011-03-04 - (-1) 5 - 2011-03-04 - 0
Can I somehow select the records between a record having type 1 and type -1 without using a cursor,for example the records with ids 1-4 in this example?
I am new to development and want to know the professional way to deal with dates in SQL Server. In my applications mainly we deal with the DATE datatype and no concern with time part. Also maintaining the format dd/mm/yyyy So for example if I have the table with the following structure.
EmployeeTable --------------- emp_id int emp_name varchar(50) join_date date
and if I want to query "join_date" in between start date and end date and pass the dd/mm/yyyy as stored procedure criteria and want to query.What is the professional way to handle dates? I always convert date in varchar and then do the comparison which I guess is the unprofessional way of doing it.
i have been having trouble trying to find out what is wrong with my sql select query... i wanted to select a sessionID by comparing the tourID and dates. the dates datatype at the database is datetime and the value of itemSessionDate is in string. i tried to convert itemSessionDate to date and dateAndTime but it gave me mm/dd/yyyy while the date in my database is dd/mm/yyyy hh:mm:ss. so i thought i can try using the itemSessionDate string as it has the same value as the one return from the database. but it keeps on goin to the if not reader.hasRows and quit the statement
* 100 AS PERCENTAGE FROM support.SRFILE INNER JOIN BSFLBWF AS BSFLBWF_1 ON BSFLBWF_1.Incidentx = SRFILE.SRONUMBER WHERE (BSFLBWF_1.Closex IS NULL) AND (BSFLBWF_1.DedTypex = 'School' OR BSFLBWF_1.DedTypex = 'LAN-MLE') UNION ALL
The rest of the query continues as the above albeit selecting the same data from different tables and works fine.
The issue I have is that I want to Select some more data (another SELECT query) but with a different Where clause e.g. WHERE (BSFLBWF_1.KPIx NOT LIKE 'KPI%' OR BSFLBWF_1.KPIx NOT LIKE 'Proactive%') AND (BSFLBWF_1.Availabilityx LIKE '')
The problem is that this WHERE clause returns data which the above in bold has null values for (and thus doesnt work). Im still selecting the same data its just the WHERE Clause has changed. Is it possible to run such a query? If so, can it be done via a sub query?
I have got the following exception when try to select data from SQL Server or inserting data in in with a C# windows application. I am passing the date in where clause of select query in single quotes like this '16/03/2011' The exception message is shown below:
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
Is there any perfect solution for inserting and selecting date from sqlserver database irrelevant to the operating system. i.e. that works on both Italian and English OS.
I have a table with logentries (tblLog) for items (tblItem) (one-to-many relationship). How can I select say the id and date for the latest logentry when I select a record from the tblItems table? In other words I want a result set with all columns from the tblItem table and also some additional columns from the tblLog table (LogId, Date, Event) for each row selected from the tblItem table. How can I do this?
tblLog LogId ItemId Date Event tblItem ItemId Field select tblItem.*, tblLog.LogId, tblLog.Date, tblLog.Event FROM tblItem tblItem JOIN tblLog tblLog ON?
I have an SQL 2005 db with a table named "industry_suppliers."
I've created a query to enable someone to search this table using 3 parameters.
The results need to be grouped by "product_description" (which is essentially a category). When displayed on an .aspx page, for example, the results need to look like this:
Apples
Acme Apple Company Bradbury Orchards Oranges A1 Citrus Company Belfast Growers, Inc Bradbury Orchards
Notice how "Bradbury Orchards" is repeated under both categories. This is the result I want, EXCEPT in cases where someone wants to filter the search results by category. Let's say someone wants to search just for apple suppliers. The problem I'm having is that any record that appears under apples will also get listed again under any other categories associated with that record. So, searching for apple suppliers, the results are this:
Apples Acme Apple Company Bradbury Orchards Oranges Bradbury Orchards
I'm having trouble figuring out how to revise my query to avoid this problem.
I have partitioned a table and it has been partitioned according to an account id. ie also rows that have accountid =1 will be in A partition and all rows that have accountid = 2 will be in B partition. so now if i execute a query with where condition accountid =1 sql server will search only in A partition. Suppose if i add another condition to it ie where accountid=1 and place="aaa" will this query search only A partition or the entire table?
I have stored procedure which selects records from an SQL table based on a bunch of user-input parameters.Just discovered that if a record in the table has null values in some of the columns (I haven't figured out which ones yet), then the SELECT is not returning the record (even if it satisfies all the parameters).The SELECT statement is supposed to allow for nulls, and I've been over it a bunch of times and am not sure what I'm doing wrong.Or do I need to get rid of all the null values in the SQL table, and prevent new ones from being introduced?