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.
| Author |
Topic |
|
mickleyd
Starting Member
5 Posts |
 Posted - 2010-11-15 : 16:26:36
|
| I have a table with records submited by a user weekly. I have the ProjectNumber, Date, and Status (in addition to other data) that is returned grouped by the ProjectNumber. The values for the status are Active and Closed.I have a query that will display the last 4 weeks worth of records grouped by ProjectNumber. I want to remove the entire group (all records for that ProjectNumber) from my report if the status of the just 1 of the previous 3 weeks is Closed.Basically I want to see that a project number is closed in the current week, but after that I don't want any records for that project number to show up on my reports at all.ThanksDwight |
|
|
jessiefun
Starting Member
35 Posts |
Posted - 2010-11-15 : 21:52:13
|
| Hi Mick, You'd better provide your table's schema and the resuts that you expect, so others can understand clearly. Regards, Jessie |
 |
|
|
mickleyd
Starting Member
5 Posts |
Posted - 2010-11-16 : 13:51:25
|
| Okay I have a table like this:ID, ProjectNumber, RecordDate, Status, Comment1, Z001234, 10/18/2010, Active, Project is on time and under budget2, Z001234, 10/25/2010, Active, Project is on time and under budget3, Z009876, 10/25/2010, Active, Project is on time and under budget4, Z001234, 11/1/2010, Active, Project is on time and under budget5, Z009876, 11/1/2010, Active, Project is on time and under budget6, Z001234, 11/8/2010, Closed, Completed under budget7, Z009876, 11/8/2010, Active, We are a little over Budget8, Z009876, 11/15/2010, Closed, Completed on time but over budget9, Z001010, 11/15/2010, Active, On time and budgetIf I run the report the week of the 15th want the report to look like this (not displaying any records from Z001234 because its status was closed last week):Z009876 10/25/2010, Active, Project is on time and under budget 11/01/2010, Active, Project is on time and under budget 11/08/2010, Active, We are a little over Budget 11/15/2010, Closed, Completed on time but over budgetZ001010 11/15/2010, Active, On time and budget |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
mickleyd
Starting Member
5 Posts |
Posted - 2010-11-16 : 14:12:59
|
| Brett,Sorry but that is not what I want. There are multiple records for Project Number Z001234, Where status is not equal to Closed will still return records for Z001234 when the status was active. I want to hide all records for Z001234 because the status was marked as closed last week, and still show all records for Z009876 because the status was marked as closed this week.I run the report each week. I do not want to see any records for projects closed last week, but do want to see records for active projects and projects that were closed this week.Sorry I must not be explaining this very well. |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
mickleyd
Starting Member
5 Posts |
Posted - 2010-11-17 : 16:41:51
|
| Thanks the query in the query is what I needed. I am not there just yet, but I think I can figure out the rest.Thanks again for your help!Dwight |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
mickleyd
Starting Member
5 Posts |
Posted - 2010-11-18 : 15:42:26
|
| Just in case someone else was looking to do something similar here is my query.SELECT * FROM TableNameWHERE ([Project Number] NOT IN (SELECT [Project Number] FROM TableName WHERE(([Project Status] = 'Closed') AND (TimePeriodEnding Between DateAdd("m",-4,DateSerial(Year(Now()),Month(Now()),1)) And DateAdd("m",-1,DateSerial(Year(Now()),Month(Now()),1)))))) AND (TimePeriodEnding Between DateAdd("m",-4,DateSerial(Year(Now()),Month(Now()),1)) And DateAdd("d",-1*Day(Date()),Date())) ORDER BY [Project Number], TimePeriodEnding; |
|
|
|
X002548
Not Just a Number
15586 Posts |
|
|
|
|
|
|
|
GroupBy question