Can someone please help with a query I have? Basically I want to return all rows in a table that have multiple date entries that are different. For example:
This security 1636 has two entries in the DB with different dates. They are lots of securities with multiple entries with the same date but I need a list of the ones with different dates. Any ideas please?
I am using sql server 2005. I stuck out in a strange problem. I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
I am getting multiple rows returned because the form that am working with has 4 possible different signtures on it I need to be able to get it so that I can return on row with the a column for the signature and date for each signature
SELECT a.op__docid, a.order_no, a.deptname, a.order_date, a.person_completing, a.date_req, a.dept_head, a.dept_head_ext, a.deliver_to, a.vendor_info, a.purchase_reason, a.qty1, a.qty2, a.qty3, a.qty4, a.qty5, a.item1, a.item2, a.item3, a.item4, a.item5, a.unit_cost1, a.unit_cost2, a.unit_cost3, a.unit_cost4, a.unit_cost5, a.ext_cost1, a.ext_cost2, a.ext_cost3, a.ext_cost4, a.ext_cost5, a.shipping, a.total, b.op__tiername, b.op__sectionid, b.op__usersigname, b.op__when, CASEIF a.op__docid IN (SELECT op__parentid FROM t4w_signatures WHERE a.op__docid = op__parentid AND op__sectionid = 386) AS Requestor, CASEIF a.op__docid IN (SELECT op__parentid FROM t4w_signatures WHERE a.op__docid = op__parentid AND op__sectionid = 385) AS DeptHead, CASEIF a.op__docid IN (SELECT op__parentid FROM t4w_signatures WHERE a.op__docid = op__parentid AND op__sectionid = 384) AS Administration, CASEIF a.op__docid IN (SELECT op__parentid FROM t4w_signatures WHERE a.op__docid = op__parentid AND op__sectionid = 444) AS Receiver
FROMfd__purchase_order a JOINt4w_signatures b ON a.op__docid = b.op__parentid
Hello, Any help would be greatly appreciated. I have a single row that looks like this. Cust, Add, Item, Value 1 ST 258 6 I want to return six rows based on the value and the value could be any number. All of the row information will stay the same except the Value that will count off of the original value. Cust, Add, Item, Value 1 ST 258 1 1 ST 258 2 1 ST 258 3 1 ST 258 4 1 ST 258 5 1 ST 258 6
hi can anyone please help me i have a stored procedure ,can i put the value of this into a variable......???? I have one more query ---how can i pass the multiple values returned by this stored procedure into a single variable ... example if my stored procedure is
[dbo].[GetPortfolioName](@NT_Account varchar(100) ) and ans of this are NL UK IN GN JK then how can i put this into a variable like
Declare @PortfolioName as varchar(250) set @PortfolioName =(exec ].[GetPortfolioName](@NT_Account) ) ....... and my ans is @PortfolioName = 'NL','UK','IN','GN','JK'
now can i make a function which returns 'NL','UK','IN','GN','JK'
I have a subscriptions table that has many line items for each record. Each line item has a different type, dues, vol, Chapt.
101 dues Mem 100 101 Vol charity 200 101 chapt CHi 300
I want my end result to have one line item per record id, but I keep coming up with an error. I am pretty sure I am close, but need assistance before I can proceed.
101 mem 100 charity 200 chi 300
Error: Server: Msg 207, Level 16, State 3, Line 2 Invalid column name 'PRODUCT_CODE'. Server: Msg 207, Level 16, State 1, Line 2 Invalid column name 'product_code'. Server: Msg 207, Level 16, State 1, Line 2 Invalid column name 'product_code'.
SELECTp.ID, p.PRODUCT_CODE as Chapt, p.product_code as Dues, p.product_code as Vol from ( SELECT ID, product_code as Chapt, Null as dues, Null as Vol from subscriptions where prod_type = 'chapt' AND BALANCE > 0
union all
SELECT ID, Null as chapt, product_code as Dues, Null as vol from subscriptions where prod_type = 'dues' AND BALANCE > 0
union all
SELECT ID, Null as chapt, Null as dues, product_code as Vol from subscriptions where prod_type = 'vol' AND BALANCE > 0
I have written a reporting application which has a SQL2005 backend. An import routine into SQL, written by a 3rd party, frequently fails. The main problems are missing rows in certain tables.
I am going to write an SP that will accepts a from and to date. I then want to search for rows of type X between those dates that do not exist so we then know between a date range, we have no data for these XYZ days.
I have this working by returning all rows between the dates into a dataset, sorted by date, and then running through the rows and testing if the next rows date is the next expected date. This works but I think is a very poor solution. This is all done on the client in C#.
I want to learn and implement the most efficent way of doing this. My only solution in a SP was to make a temporary table of all dates between the date range for row type X and then do a right outer join against the data table, returning all rows which are missing.
Something like this:
SELECT twmd.date FROM #temp_table_with_all_dates ttwad RIGHT OUTER JOIN table_with_missing_date twmd ON ttwad.date = twmd.date WHERE twmd.date IS NULL
Would this be a good, efficent solution, or should I just stick to my processing of a dataset in C#?
I am using VS2005 to construct a website. I have a sql database with 3 datasets. One of the table adapters is called Proudcts and has the following fields. ProductItem, Description, Price, ProductID, PackSizeI want to be able to see a summary of the products (there are thousands of them) using one or more words (or partial words) in one text box to search in only 3 fields (ProductItem, Description, ProductID, ). This needs to show only records which contain the search criteria in a gridview?This is such a basic requirement for a website, and can be found on many sites, but I haven't found how to do it.Thanks, Bri
Does sql server have a mechanism (aside from count()) that for any given SELECT query will tell you only how many rows it will return without actually returning the data?
The reason for this is that we have a generic lookup form in an application that is used on almost every screen (we have a lot of screens, so it gets a lot of different, sometimes complicted, queries passed to it to use for the lookup, and having to manually edit the query to use count over all the select clauses doesn't seem like the best way to handle this. If we could do a kind of 'trial run' against the server just to get the number of rows and use that to help set up the form, that would be ideal.
When querying any of our database tables (returning all rows from within the enterprise manager) we are getting the following error message: "The query cannot be executed because some files are either missing or not registered" Can anyone help me with this....
When running a query with a table from another SQL7 Server that is linked, if only 1 table from each server is specified, the query runs fast and returns only the data rows needed from the linked server. When another table is added on to query (on the server running the query) the query processor tries to run a remote query and return over 400,000 rows over the network. Any suggestions?
I have three tables X,Y,Z. Table 'Y' is having foreign key constraints on tables 'X' and 'Z' (which happen to be primary key tables). I would like to run a query in which I can retrieve rows from Table 'X' only if the matching rows in Table 'Y' have "ALL" their matching rows available in a simple query being run on Table "Z". The "All" part is very important.
For more clarification, let me give you an example. Table "X" is equivalent to a mathematical "Equation" table which consists of an equation made up of several "Fields". These fields are stored in Table "Z". Table "Y" contains the primary keys from Tables "X" and "Z". i.e. Table "Y" determines what fields are required for an equation to be complete.
I am having a query "Q" on Table "Z" (Fields table) which returns me a bunch of Fields. Now, on the basis of these fields, I want to retrieve only those Equations (Table "X") which have "ALL" their required Fields present in the bunch retrieved by the Query "Q".
I hope I am clear enough. Does anyone have any solutions???
Hello SQL gurus!I am trying to write a query that will return a set of continguous rowsfrom a table, and limit the number of rows returned when a maximumtotal has been reached by adding a value in one of the columns.For example, the two columns below represent 2 columns in a table.a 2b 2c 2d 3e 4f 5g 5I want to start at, say "c", and return all the rows after it as longas the sum of the numbers in column 2 (starting at "c") don't exceed10. The result I'm after would be thusc 2d 3e 4....because 2 + 3 + 4 = 9 < 10Any ideas? Many thanks.
Given the following data how do I make a SQL query that returns only 1 row per product?
The returned rows need consist of only currently active products (that is WHERE (DateEffective <= { fn NOW() }). The twist: Sometimes a product will have duplicate DateEffective records. In that case, only return the record created latest because that's the most current data that exists for a product. RowTimeStap is when the record was created.
Example Data: HistoryID ProductID Name Color DateEffective RowTimeStamp (auto-number PK) 1 1 Wheel Red 2/1/2008 2/1/2008 2 1 Wheel Blue 3/5/2008 3/1/2008 3 1 Wheel Orange 3/5/2008 3/2/2008 4 1 Wheel Black 1/1/2010 3/3/2008 5 2 Knob Blue 3/2/2008 3/2/2008 6 2 Knob Green 3/3/2008 3/3/2008
Query should return: 3 1 Wheel Orange 3/5/2008 3/2/2008 5 2 Knob Green 3/3/2008 3/3/2008
The query I've created fails on the twist part. I have to allow duplicate DateEffective to keep a history of changes. Can anyone help?
Hi, I have 1 table with 5 rows. One of the rows has dateTime values. I want to know how many rows there are with a value in that column < today AND how many rows there are with a value in that column > today. I'm not sure how to do this.
SELECT Count(*) WHERE dateColumn <= today AND dateColumn > today gives me everything. I like to end up with a column containing the count of rows <= today and a column with rows where date > today.
Is this possible in SQL or do I have to retrieve all rows and then loop over the resultset and check each row?
Question: How to determine if a date value was between one of the date periods that appear in multiple rows?
Background: We have a table of "license valid" periods, wherein each license can have one or more rows. (As you know, a driver's license can be started, expired, renewed, suspended, reinstated, revoked, etc.) Instead of of having a license activity table--from which valid license periods could be extrapolated--we store just the periods for which a license was valid.
My task is to take a list of licenses and specific dates and determine if each license was valid as of that date, returning either true or false. What is the best way to accomplish this?
DECLARE @ValidityInQuestion TABLE ( LicenseID int , DateValidityInQuestion date);
DECLARE @LicenseValidPeriods TABLE ( LicenseID int , BeginDate date , EndDate date);
[Code] ...
How then do I query both tables in order to get the same result that results from the following query?
SELECT 12345 AS LicenseID , '2015-01-15' AS DateValidityInQuestion , 1 AS LicenseActive UNION SELECT 67890 , '2015-02-04' , 0;
I assume I need to join on the LicenseID columns, but my understanding stops there.
Hi, I have been struggling trying to design a query that will alow be to select the most recent date in a table and I'm obviously not having much luck
This is basically the table layout, note each employee can have multiple rows with different dates
Employee_ID
Last_Name
First_Name
Evaluation_Date
Evaluation_Score
1
Jones
Tom
01/04/07
40
1
Jones
Tom
01/.12/07
50
1
Jones
Tom
04/01/08
60
2
Smith
Ed
02/14/05
70
2
Smith
Ed
03/18/06
80
3
Brown
John
06/23/04
80
3
Brown
John
12/23/04
79
3
Brown
John
01/07/06
50
3
Brown
John
10/22/08
69
What I'd like to do would be to write some thing that would return the following, just the last date of the evaluation & whatever relevant data is in the table
Employee_ID
Last_Name
First_Name
Evaluation_Date
Evaluation_Score
1
Jones
Tom
04/01/08
60
2
Smith
Ed
03/18/06
80
3
Brown
John
10/22/08
69
I've looked at select distinct and the date operatives with out any success.
I hope someone can help, I usually just need to do simple queries, and am fine with that, but this is a bit over my head without a few pointers in the right direction.
I have a table that stores log results from pieces of hardware that contact an internet server periodically.
For simplification, this contains:
LogID SystemID DeviceID ContactDateTime Notes
Each device contacts the server about once every 5 minutes, and there are about 100 devices in 6 systems.
What I need to do is find out, for a certain date time range, things like:
1. the average time between comunication for any one device 2. the longest time between communication for any one device 3. average time between communications for all devices in a system.
We currently do this by downloading all the logs and working through them programmatically on the server, but as you can imagine, it is a very large amount of information, and it would be much more efficient if this could be done on the SQL server.
I have a table in which each record has a initial date and a final date. I would like to create I query that gives me one row for each month between the initial date and the final date. It would be something like this:
I would like to do that, because these dates correspond to the time in which the products are in possession of sellers, so I would to use this resulting query to generate a pivot chart in Excel to illustrate the value of the goods that are with our sellers in each month.
Is it possible to do it? I think I could do that direct in VBA, but I think that maybe it would be faster if I could do it directly in SQL.
By the way, I am using MS SQL Server and SQL Server Manegement Studio 2012.
How to design at database level such a way so that when I implement a SQL query that returns one hundred thousand rows only display 25 rows at the client (Web page at a time). How can I accomplish this?
Once I display first 25 rows then how do I bring next 25 rows and so on. Can it be done via paging or there are other techniques. However I asked to design this in the database level. I am using MS SQL Server 2008. Front end Visual Studio 2010. I use C# for coding.
I have two tables .. in one (containing user data, lets call it u).The important fields are:u.userName, u.userID (uniqueidentifier) and u.workgroupID (uniqueidentifier)The second table (w) has fieldsw.delegateID (uniqueidentifier), w.workgroupID (uniqueidentifier) The SP takes the delegateID and I want to gather all the people from table u where any of the workgroupID's for that delegate match in w. one delegateID may be tied to multiple workgroupID's. I know I can create a temporary table (@wgs) and do a: INSERT INTO @wgs SELECT workgroupID from w WHERE delegateID = @delegateIDthat creates a result set with all the workgroupID's .. this may be one, none or multipleI then want to get all u.userName, u.userID FROM u WHERE u.workgroupIDThis query works on an individual workgroupID (using another temp table, @users to aggregate the results was my thought, so that's included) INSERT INTO @users SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = @workGroupIDI'm trying to avoid looping or using a CURSOR for the performance hit (had to kick the development server after one of the cursor attempts yesterday)Essentially what I'm after is: SELECT u.userName,u.userID FROM tableU u LEFT JOIN tableW w ON w.workgroupID = u.workgroupID WHERE u.workgroupID = (SELECT workgroupID from w WHERE delegateID = @delegateID) ... but that syntax does not work and I haven't found another work around yet.TIA!
I have received some data out of a relational database that is incomplete and I need to find where the holes are. Essentially, I have three tables. One table has a primary key of PID. The other two tables have PID as a foreign key. Each table should have at least one instance of every available PID.
I need to find out which ones are in the second and third table that do not show up in the first one, which ones are in the first and third but not in the second, and which ones are in the first and second but not in the third.
I've come up with quite a few ways of working it but they all involve multiple union statements (or dumping to temp tables) that are joining back to the original tables and then unioning and sorting the results. It just seems like there should be a clean elegant way to do this.
Here is an example:
create table TBL1(PID int, info1 varchar(10) )
Create table TBL2(TID int,PID int)
Create table TBL3(XID int,PID int)
insert into TBL1
select '1','Someone' union all
select '2','Will ' union all
select '4','Have' union all
select '7','An' union all
select '8','Answer' union all
select '9','ForMe'
insert into TBL2
select '1','1' union all
select '2','1' union all
select '3','8' union all
select '4','2' union all
select '5','3' union all
select '6','3' union all
select '7','5' union all
select '8','9'
insert into TBL3
select '1','10' union all
select '2','10' union all
select '3','8' union all
select '4','6' union all
select '5','7' union all
select '6','3' union all
select '7','5' union all
select '8','9'
I need to find the PID and the table it is missing from. So the results should look like:
I'm trying to run a sql query using the core dotnetnuke database. I am tying to pull from two separate tables in the same database and return the results below: ------------------------------------
LastName | FirstName | City | PhoneNumber
Smith Joe New York (555)555-5555
------------------------------------
Last Name and FirstName are two separate columns in the DB_Users table, while City and PhoneNumber are both values of the same column DB_UserProfile.PropertyValue located in separate rows defined by an ID in the DB_UserProfile.PropertyDefinitionID column.
The UserProfile table looks something like this...
ID | PropertyDefinitionID | PropertyValue
1 27 New York 2 31 (555)555-5555
------------------------------------
This is what I have so far but it is not working out to well...
SELECT DB_Users.FirstName, DB_Users.LastName, DB_UserProfile.PropertyValue AS City, DB_UserProfile.PropertyValue AS PhoneNumber FROM DB_Users, DB_UserProfile WHERE DB_Users.UserID = DB_UserProfile.UserID AND DB_UserProfile.PropertyDefinitionID = 27 AND DB_UserProfile.PropertyDefinitionID = 31 ORDER BY DB_Users.LastName
I hate to ask such silly helps..but I'm missing something here..need help. I have a table having columns for createddate and deleteddate. The data gets created and deleted periodically and I need to find out the number of created,deleted and remaining number of records on each day. This query works, but takes a lot of time...not sure if there is a more better way to do this.. Please help SELECT CAST(createddate AS DATETIME) AS createdDate, Created, Deleted, Remaining FROM( SELECT CONVERT(VARCHAR,createdon,102) AS CreatedDate, COUNT(1) created, (SELECT COUNT(1) FROM table ta2 WHERE CONVERT(VARCHAR,ta2.deletedon,102) = CONVERT(VARCHAR,ta.createdon,102)) Deleted, ((SELECT COUNT(1) FROM table ta1 WHERE CONVERT(VARCHAR,ta1.createdon,102) <= CONVERT(VARCHAR,ta.createdon,102)) - (SELECT COUNT(1) FROM table ta1 WHERE CONVERT(VARCHAR,ta1.deletedon,102) <= CONVERT(VARCHAR,ta.createdon,102))) Remaining FROM table ta WHERE CONVERT(VARCHAR,createdon,102) >= (GETDATE() - 90) GROUP BY CONVERT(VARCHAR,createdon,102) ORDER BY CONVERT(VARCHAR,createdon,102) DESC) AS tmp
