SQL Dynamic Query -DAM UEGRNT PLZZ HELP

Question

Hi all..
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 Valecha · Answer

Hi dear.... 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 Sharma · Answer

Okay try this query on your second request onwards: SELECT IDStatusCompany[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 Valecha · Answer

Hi...This is a simple select query... 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 Sharma · Answer

Okay show me the query that you're using right now to fetch this data: 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

Riddhi Valecha · Answer

Hi... 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 (V1V2...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 Sharma · Answer

Hi Riddhi 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 CompanyLocation from where NOT Status='Success'; Hope it helps!