SQL Query Help please!!! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

SQL Query Help please!!!

Author

Topic

Shailen
Starting Member

17 Posts

Posted - 2005-11-23 : 03:30:52

Hi have this table of data below

No Employee Name Description Date Status
47 SK Original Bank statements filling 11/17/2005 10:57:01 AM Given to Reception to file
47 SK Original Bank statements filling 11/17/2005 12:36:41 PM Given to reception as well as the Provisional Bank Statements, Also Merchant Factors Weekly report statements
47 SK Original Bank statements filling 11/21/2005 8:06:11 AM Completed 18th
47 SK Original Bank statements filling 11/21/2005 8:06:28 AM Completed
48 SK Complete Debtors Reconciliation of Long 11/21/2005 8:14:49 AM Incorporated with Rentals/Electricity Run
49 SK Complete Creditors Reconciliation of Long 11/21/2005 8:14:31 AM Incorporated with creditors run 7th
50 SK Ditz Inc Invoices for payment 11/17/2005 10:59:13 AM "Ditz to amend invoice
compile seperate cheques per company"
50 SK Ditz Inc Invoices for payment 11/17/2005 3:32:13 PM seperates cheques awaiting signatures
50 SK Ditz Inc Invoices for payment 11/21/2005 8:06:51 AM Awaiting cheques, to be signed inhouse
50 SK Ditz Inc Invoices for payment 11/23/2005 10:02:02 AM Ditz has been deposited pending signature
51 SK Confirm Reciept of Rental and Electricity Invoices 11/23/2005 10:03:00 AM Completed

Ok, this is my table data. The NO field is the record number. As you can see there are multiple records with the same number. Reason being, there can be multiple information for a record. How do I write an sql query to extract all the records where the comment is equal to completed.
As you can see, record 47th got a few updates to the record and a completed comment. How do i get all the records similiar to 47th to be printed out ??

surendrakalekar
Posting Yak Master

120 Posts

Posted - 2005-11-23 : 03:35:38

select * from yourtable where status = 'Completed'
or
select * from yourtable where status like 'Completed%'

This is what you need?

Surendra

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-23 : 03:45:57

Select columns from yourTable where No in (Select No from yourTable where Status like 'Completed%')

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 03:45:59

hmm..

Select * From <TableName> Where Status Like '%Completed%'

Or

Select * From <TableName> Where RTrim(lTrim(Status)) Like 'Completed%'

Hope this works for u .. ????

Sucess Comes to those who Believe in Beauty of their Dream..

Shailen
Starting Member

17 Posts

Posted - 2005-11-23 : 03:54:30

I did that query and it only brings up 1 row of the record
47 SK Original Bank statements filling 11/21/2005 8:06:28 AM Completed

I need it to bring up
47 SK Original Bank statements filling 11/17/2005 10:57:01 AM Given to Reception to file
47 SK Original Bank statements filling 11/17/2005 12:36:41 PM Given to reception as well as the Provisional Bank Statements, Also Merchant Factors Weekly report statements
47 SK Original Bank statements filling 11/21/2005 8:06:11 AM Completed 18th
47 SK Original Bank statements filling 11/21/2005 8:06:28 AM Completed

I don't only want the completed row, i want all the information pertainin to that record

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-23 : 03:55:49

Did you try the query I suggested earlier?
Select columns from yourTable where No in (Select No from yourTable where Status like 'Completed%')

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 03:58:17

Do u mean that u want the record with the no 47 ????
and which are completed??

Select * From <TableName> Where No = 47 and Status Like '%Completed%' ???

or you want only the records with no = 47

Select * From <TableName> Where No = 47

Sucess Comes to those who Believe in Beauty of their Dream..

sqlmember
Starting Member

7 Posts

Posted - 2005-11-23 : 04:01:33

yes surendrakalekar has suggested the accurate query. You can test this with your data which I have converted as (I have removed the column employee because I did not find data for this column or may be due to concatenation i was unable to understand which data to come into)

declare @table table (No int, Name varchar(50), Description varchar(100),Date datetime, Status varchar(255))
insert into @table
select 47, 'SK', 'Original Bank statements filling', '11/17/2005 10:57:01 AM', 'Given to Reception to file' union all
select 47, 'SK', 'Original Bank statements filling', '11/17/2005 12:36:41 PM', 'Given to reception as well as the Provisional Bank Statements, Also Merchant Factors Weekly report statements' union all
select 47, 'SK', 'Original Bank statements filling', '11/21/2005 8:06:11 AM', 'Completed 18th' union all
select 47, 'SK', 'Original Bank statements filling', '11/21/2005 8:06:28 AM', 'Completed' union all
select 48, 'SK', 'Complete Debtors Reconciliation of Long', '11/21/2005 8:14:49 AM', 'Incorporated with Rentals/Electricity Run' union all
select 49, 'SK', 'Complete Creditors Reconciliation of Long', '11/21/2005 8:14:31 AM', 'Incorporated with creditors run 7th' union all
select 50, 'SK', 'Ditz Inc Invoices for payment', '11/17/2005 10:59:13 AM', 'Ditz to amend invoice compile seperate cheques per company' union all
select 50, 'SK', 'Ditz Inc Invoices for payment', '11/17/2005 3:32:13 PM', 'seperates cheques awaiting signatures' union all
select 50, 'SK', 'Ditz Inc Invoices for payment', '11/21/2005 8:06:51 AM','Awaiting cheques, to be signed inhouse' union all
select 50, 'SK', 'Ditz Inc Invoices for payment', '11/23/2005 10:02:02 AM', 'Ditz has been deposited pending signature' union all
select 51, 'SK', 'Confirm Reciept of Rental and Electricity Invoices', '11/23/2005 10:03:00 AM', 'Completed'
select * from @table where status = 'completed'
select * from @table where status like '%completed%'

-Khurram Iqbal

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-23 : 04:06:21

sqlmember, your query will give only two records of 47. But the questioner needs all records of 47 which is what I suggested

select * from @table where no in (select no from @table where status like 'completed%')

Madhivanan

Failing to plan is Planning to fail

Shailen
Starting Member

17 Posts

Posted - 2005-11-23 : 04:13:31

Hi

Madhivanan query is wat i am looking for. Select columns from yourTable where No in (Select No from yourTable where Status like 'Completed%')But there is a problem, In my database i got lots of record either completed or not. This query works when i edit the query to, it works fine

Select *
from FinAdminFullTaskHistory
where FinAdminFullTaskHistory.[No] in (Select [No] from FinAdminFullTaskHistory where Status = 'Completed')

When i change the query now to bring up all the record which are not completed, it still brings up the data that are completed as well

any idea's guys

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-23 : 04:17:15

>>When i change the query now to bring up all the record which are not completed,

You should use this query

Select *
from FinAdminFullTaskHistory where Status <>'Completed'

Madhivanan

Failing to plan is Planning to fail

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 04:18:47

Select *
from FinAdminFullTaskHistory
where FinAdminFullTaskHistory.[No] in (Select [No] from FinAdminFullTaskHistory where Status = '%Not Completed%')
????

Sucess Comes to those who Believe in Beauty of their Dream..

Shailen
Starting Member

17 Posts

Posted - 2005-11-23 : 04:19:34

Thanks fine Madhivanan but we cannot do that cos that will bring up parts of record 47 which we cannot have cos its been completed.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-23 : 04:22:59

Post some sample data and the result you want for each criteria

Madhivanan

Failing to plan is Planning to fail

Shailen
Starting Member

17 Posts

Posted - 2005-11-23 : 04:23:06

Hi chiragkhabaria. The query you have given me does not bring up any records at all. There are records in the database that are not completed.
I tried this query but is still gives me records that are completed
Select *
from FinAdminFullTaskHistory
where FinAdminFullTaskHistory.[No] in (Select [No] from FinAdminFullTaskHistory where Status <> 'Completed')

Shailen
Starting Member

17 Posts

Posted - 2005-11-23 : 04:29:46

No Description Date Status
1 Ordering of Rental Software 10/7/2005 10:36:06 AM Meeting held with Tony Pitwood. Further requirements to
1 Ordering of Rental Software 11/16/2005 11:25:56 AM Relationship terminated with Revival software. Costinged from
2 Encumbrances on call accounts 10/7/2005 10:39:02 AM Advised via Chin - Standard Bank. Meeting held and inf
2 Encumbrances on call accounts 11/14/2005 3:32:10 PM Completed
3 First Provisional returns 2006 10/7/2005 10:41:40 AM Deai Jadwats. Completed
3 First Provisional returns 2006 11/14/2005 3:31:53 PM Completed
4 Swiss Strasse payment plan - R30 000 x 6 11/10/2005 10:44:04 AM Paybale from 07 Sept to 07 Feb. Sept and Oct
4 Swiss Strasse payment plan - R30 000 x 6 11/14/2005 3:32:03 PM Completed
7 Standard Bank - Unlimited suretyship - credit card 11/10/2005 11:20:36 AM Alvin B referred matter to Credit Manager at Card Division Head office. Alvin to call back.
7 Standard Bank - Unlimited suretyship - credit card 11/18/2005 11:45:51 AM Suretyship has been sorted out and cards collected as per Alvin Bhagaloo.
8 Uniroute Filing 10/7/2005 11:29:09 AM Done
8 Uniroute Filing 11/14/2005 3:32:28 PM Completed
9 Electronic Banking 10/7/2005 11:30:41 AM Implemented and Training provided to Vani - Completed
9 Electronic Banking 11/14/2005 3:32:21 PM Completed
10 Current Management Accounts 11/15/2005 8:50:05 AM Completed
10 Current Management Accounts 9/28/2005 11:32:38 AM August submitted x 5 for review to DJI.
10 Current Management Accounts 10/7/2005 11:34:21 AM August completed and submitted
11 Ace International - Refund for RB Trip 11/18/2005 10:14:26 AM Completed
11 Ace International - Refund for RB Trip 10/7/2005 11:36:22 AM Allocated as done on my list - but cannot recall; query Janice
12 Cash Flow Variance Report for Factors 10/7/2005 11:38:06 AM July; August completed and submited, required monthly.
12 Cash Flow Variance Report for Factors 11/15/2005 8:50:23 AM Completed
13 Balancing of Factors Loan Account 10/7/2005 11:39:17 AM Force-balanced to July against a suspense account,
14 Security Invoices amend for amarco and site description 11/15/2005 8:50:31 AM Completed
14 Security Invoices amend for amarco and site description 10/7/2005 11:42:21 AM Actioned and completed
15 Overdraft facility 10/7/2005 11:43:12 AM Confirmed with Tim Maidment on 07-Oct-05 facility in place to end Dec;
15 Overdraft facility 11/18/2005 10:15:09 AM Completed
16 Brenell costs and operations 10/7/2005 11:48:06 AM Documents drafted and discussed
16 Brenell costs and operations 11/18/2005 10:15:42 AM Completed
16 Brenell costs and operations 10/21/2005 11:46:28 AM Muhammad Agjee designated as project co-ordinator
16 Brenell costs and operations 10/28/2005 11:47:04 AM Setup docs compiled and discussed with all participants
17 Ethekwini Bulk Terminal followup 10/21/2005 11:50:00 AM Recover rentals for office in Johnston Road - o
17 Ethekwini Bulk Terminal followup 11/18/2005 10:16:06 AM Completed
17 Ethekwini Bulk Terminal followup 11/16/2005 11:50:20 AM 4 pdcs presented by Chris Winter d
17 Ethekwini Bulk Terminal followup 10/7/2005 11:52:22 AM "three followups by Raj to Chris Winter
17 Ethekwini Bulk Terminal followup 10/29/2005 11:53:19 AM Brought to RBs attention who left a message for
18 Powerhouse credit 10/21/2005 11:55:16 AM Recover monies due for damaged aluminium
18 Powerhouse credit 11/18/2005 10:16:27 AM Completed
18 Powerhouse credit 11/16/2005 11:52:59 AM Received credit note in the amt R 522 for damage to 4
19 Short Payment for Laser Logistics 10/21/2005 11:56:38 AM Resolve underpayment of invoices to Laser Logistics.
19 Short Payment for Laser Logistics 11/16/2005 11:54:01 AM Sherwyn presented all relevant info to Ryan.
20 ITalk Cost/Benefit computation 10/21/2005 11:58:03 AM Compute whether or not it would be in
20 ITalk Cost/Benefit computation 11/16/2005 1:50:42 PM iTalk do not wish to concede any costs.

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 04:30:30

Select *
from FinAdminFullTaskHistory Where status Not like '%Completed%'

how to get the records which are not completed.. is there a flag or special text for the same.. ???

or in the status column if there is no "completed" text that means its not completed..????

Sucess Comes to those who Believe in Beauty of their Dream..

Shailen
Starting Member

17 Posts

Posted - 2005-11-23 : 04:35:31

The data above is some of my data from the db. If i run a query for all completed projects, it must bring up

No Description Date Status
2 Encumbrances on call accounts 10/7/2005 10:39:02 AM Advised via Chin - Standard Bank. Meeting held and info on file.
2 Encumbrances on call accounts 11/14/2005 3:32:10 PM Completed
3 First Provisional returns 2006 10/7/2005 10:41:40 AM Deai Jadwats. Completed
3 First Provisional returns 2006 11/14/2005 3:31:53 PM Completed
4 Swiss Strasse payment plan - R30 000 x 6 11/10/2005 10:44:04 AM Paybale from 07 Sept to 07 Feb. Sept and Oct Done
4 Swiss Strasse payment plan - R30 000 x 6 11/14/2005 3:32:03 PM Completed

8 Uniroute Filing 10/7/2005 11:29:09 AM Done
8 Uniroute Filing 11/14/2005 3:32:28 PM Completed
9 Electronic Banking 10/7/2005 11:30:41 AM Implemented and Training provided to Vani - Completed
9 Electronic Banking 11/14/2005 3:32:21 PM Completed
10 Current Management Accounts 11/15/2005 8:50:05 AM Completed
10 Current Management Accounts 9/28/2005 11:32:38 AM August submitted x 5 for review to DJI.
10 Current Management Accounts 10/7/2005 11:34:21 AM August completed and submitted
11 Ace International - Refund for RB Trip 11/18/2005 10:14:26 AM Completed
11 Ace International - Refund for RB Trip 10/7/2005 11:36:22 AM Allocated as done on my list - but cannot recall; query Janice
12 Cash Flow Variance Report for Factors 10/7/2005 11:38:06 AM July; August completed and submited, required monthly.
12 Cash Flow Variance Report for Factors 11/15/2005 8:50:23 AM Completed
14 Security Invoices amend for amarco and site description 11/15/2005 8:50:31 AM Completed
14 Security Invoices amend for amarco and site description 10/7/2005 11:42:21 AM Actioned and completed
15 Overdraft facility 10/7/2005 11:43:12 AM Confirmed with Tim Maidment on 07-Oct-05 facility in place to end Dec;
15 Overdraft facility 11/18/2005 10:15:09 AM Completed
16 Brenell costs and operations 10/7/2005 11:48:06 AM Documents drafted and discussed
16 Brenell costs and operations 11/18/2005 10:15:42 AM Completed
16 Brenell costs and operations 10/21/2005 11:46:28 AM Muhammad Agjee designated as project co-ordinator
16 Brenell costs and operations 10/28/2005 11:47:04 AM Setup docs compiled and discussed with all participants
17 Ethekwini Bulk Terminal followup 10/21/2005 11:50:00 AM Recover rentals for office in Johnston Road - o
17 Ethekwini Bulk Terminal followup 11/18/2005 10:16:06 AM Completed
17 Ethekwini Bulk Terminal followup 11/16/2005 11:50:20 AM 4 pdcs presented by Chris Winter d
17 Ethekwini Bulk Terminal followup 10/7/2005 11:52:22 AM "three followups by Raj to Chris Winter, payment still pending
"
17 Ethekwini Bulk Terminal followup 10/29/2005 11:53:19 AM Brought to RBs attention who left a message for
18 Powerhouse credit 10/21/2005 11:55:16 AM Recover monies due for damaged aluminium
18 Powerhouse credit 11/18/2005 10:16:27 AM Completed
18 Powerhouse credit 11/16/2005 11:52:59 AM Received credit note in the amt R 522 for damage to 4

Shailen
Starting Member

17 Posts

Posted - 2005-11-23 : 04:37:34

This is the data i require when comment <> completed

No Description Date Status
1 Ordering of Rental Software 10/7/2005 10:36:06 AM Meeting held with Tony Pitwood. Further requirements to incorporate a job
1 Ordering of Rental Software 11/16/2005 11:25:56 AM Relationship terminated with Revival software. Costing received from
7 Standard Bank - Unlimited suretyship - credit card 11/10/2005 11:20:36 AM Alvin B referred matter to Credit Manager at Card Division Head office. Alvin to call back.
7 Standard Bank - Unlimited suretyship - credit card 11/18/2005 11:45:51 AM Suretyship has been sorted out and cards collected as per Alvin Bhagaloo.
13 Balancing of Factors Loan Account 10/7/2005 11:39:17 AM Force-balanced to July against a suspense account,
19 Short Payment for Laser Logistics 10/21/2005 11:56:38 AM Resolve underpayment of invoices to Laser Logistics.
19 Short Payment for Laser Logistics 11/16/2005 11:54:01 AM Sherwyn presented all relevant info to Ryan.
20 ITalk Cost/Benefit computation 10/21/2005 11:58:03 AM Compute whether or not it would be in
20 ITalk Cost/Benefit computation 11/16/2005 1:50:42 PM iTalk do not wish to concede any costs.

Shailen
Starting Member

17 Posts

Posted - 2005-11-23 : 04:38:59

To get records that are not completed, they don't have a completed in the status column

chiragkhabaria
Master Smack Fu Yak Hacker

1907 Posts

Posted - 2005-11-23 : 05:36:47

Select *
from FinAdminFullTaskHistory Where status Not like '%Completed%'

you tried out this query .. ????

and also tried out this query .. if its work ..

Select *
from FinAdminFullTaskHistory Where No Not in (Select * from FinAdminFullTaskHistorystatus like '%Completed%')

Hope this work???

Sucess Comes to those who Believe in Beauty of their Dream..

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources