I need help in - 1) SQL Query.
My requirement is -
I have to call a method every 5 minutes and display the data on gridview in Windows Application.
I need help in forming the query.
I have a table as below -
| ID | Status | Company | Key | Location |
| 1 | Error | A | V1 | F |
| 2 | Error | B | V1 | G |
| 3 | Success | C | V1 | H |
| 4 | Error | D | V2 | I |
| 5 | Error | E | V2 | J |
Now, once the Key hits success i.e. here V1 key gets success on ID-3. Hence, after the Key gets success, that key should not be picked again when the method is called on timer control.
V2 has not got success yet. Hence, it should be picked.
All the records, till the Key hits success should be picked. Once the Status is Success, then those keys should not be picked.
The output should be as follows -
| ID | Status | Key | Company | Location |
| 1 | Errior | V1 | A | F |
| 2 | Error | V1 | B | G |
| 4 | Error | V2 | D | I |
| 5 | Error | V2 | E | J |
After 5 minutes, when the method is called, only 2 records with ID-4 and 5 should be displayed.
This is because, V1 key have success on ID-3. Hence, that record should not be picked.
Please help me out...
This is urgent.
Riddhi ValechaPosted May 22, 2014, 8:17 AM
Please help me out in dynamic search query in sql stored procedure....
----
My procedure is as -
@CompanyCode varchar(max) = null,
@Document varchar(max) = null,
@Status varchar(max) = null,
@Location varchar(max) = null
declare @SQL as nvarchar(max)
set @SQL = 'select * from Table where 1=1'
if @CompanyCode is not null
set @SQL = @SQL + 'and Table.CompanyCode = '+@CompanyCode
if @Document is not null
set @SQL = @SQL + 'and Table.Document = '+@Document
Same way, for the rest of the parameters.
-----------
Now, my query is, how do we get the total records from this query ??
i.e.
select @Totalrecords = count(*) from Table + where clause.
There are 50000 records in the table, but only 10000 satisfy the conditions.
SO, I want 10000 count as a output variable.
How do I get this??
PLease help....
Sunny SharmaPosted Jan 30, 2014, 5:56 AM
try this query on your second request onwards:
SELECT
ID,Status,Company,[Key],Location
FROM
MyTable
WHERE [Key] NOT IN
(SELECT [Key] FROM MyTable WHERE STATUS='Success');
Change your table name accordingly and you're done.
Cheers!!
Riddhi ValechaPosted Jan 30, 2014, 3:55 AM
Select * from table;
This data is currently present in the table.
Now, I do not want to pick the data of Key V1 here because this key now have success against it.
I have to call this method on timer control ( every 5 minutes ).
First time when the method is being called, then I should get all the records when the Status is Error and corresponding key do not have success.
Again after 5 minutes, the key V1 have success. So, this time the method should not have the records where the key is V1.
I am trying out the following logic -
I have taken a variable @Keys and I am trying to pick all those keys that have success.
And then I will try to fire the the query - select * from table where key not in (@Keys) and status = Error.
Sunny SharmaPosted Jan 30, 2014, 1:07 AM
Riddhi ValechaPosted Jan 30, 2014, 12:58 AM
Thanks a ton yaar...
I did try out your solution.
But, If I put this condition "Status <> Success", then also I will keep getting the records of Key V1, although it has now achieved success once.
Full Logic -
I want the records of only those keys (V1,V2...and so on) that have not achieved success.
Once the status againse these keys gets success, then the records with those keys should not be displayed.
Eg-The key V1 have success in ID-3. Then, next time the method is called, no records with Key-V1 should be picked.
Please help.
Sunny SharmaPosted Jan 29, 2014, 5:17 AM
How about filtering records to select only those having "Error", or not "Success":
Well, since you haven't mentioned your method, I can't see the logic behind. The easiest solution seems to me is: modify your sql query to pick only those records which have Status not equal to 'Success'.
Select ID, Status, Key, Company,Location from
Hope it helps!