pauledwardian
asked on
Need a sql query
Here is my Columns in the Dbo.Table1:
PostNumber PostDate AuditDate Class Type
I am looking for a query that would randomly seelct the 15 of PostNumbers based on the Audit Date NOT being within the last seven days and ONLY selects the ones that are within the Class "D" Group (Which is the 4th Column on my Table).
The report needs to be generated once a week. Basically, my purpose of needing this is to have a report for the end user so he can see a list of Posts that haven't been audited since the last 7 Days AND the ones that he audits for each week CANNOT show up until the next 3 month. So, he will see 1 post 4 times a year in his report.
For instance:
P115666 was audited today along with 14 other Posts Which are under the Class "D".
These 15 Posts SHOULD NOT show up until the next three month.
Please provide the query if it is possible.
FYI: " I will need this query to use in a C# Winform project in a Report Viewer."
Thank you,
PE
PostNumber PostDate AuditDate Class Type
I am looking for a query that would randomly seelct the 15 of PostNumbers based on the Audit Date NOT being within the last seven days and ONLY selects the ones that are within the Class "D" Group (Which is the 4th Column on my Table).
The report needs to be generated once a week. Basically, my purpose of needing this is to have a report for the end user so he can see a list of Posts that haven't been audited since the last 7 Days AND the ones that he audits for each week CANNOT show up until the next 3 month. So, he will see 1 post 4 times a year in his report.
For instance:
P115666 was audited today along with 14 other Posts Which are under the Class "D".
These 15 Posts SHOULD NOT show up until the next three month.
Please provide the query if it is possible.
FYI: " I will need this query to use in a C# Winform project in a Report Viewer."
Thank you,
PE
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
>>Can you please look at my example posted and verify if a Post that is audited today will Not show until the next 3 month. <<
No unfortunately that will not do it. In order to do that you will have to come up with another column or table to mark the ones you have already audited.
>>I'm not knowledgeable in SQL but I think <> means Not Equal To? <<
Correct.
No unfortunately that will not do it. In order to do that you will have to come up with another column or table to mark the ones you have already audited.
>>I'm not knowledgeable in SQL but I think <> means Not Equal To? <<
Correct.
I'm a little confused. You say the report cannot show posts that have been audited in the last 7 days, but also posts cannot be viewed in this report until 3 months after their last audit date. Surely the 2nd criteria will override the 1st?
Does each post have an audit date? Or does the audit date only get set when it is audited?
If the report is to show posts that have not been audited, then why have a time limit at all?
Do you mean to say posts that were CREATED within the last 7 days but have not yet been audited?
Does each post have an audit date? Or does the audit date only get set when it is audited?
If the report is to show posts that have not been audited, then why have a time limit at all?
Do you mean to say posts that were CREATED within the last 7 days but have not yet been audited?
It seems like some routine changes to Lowfat's query should do it.
Since you want "within Class D", you probably need eq rather than ne.
And 3 months instead of 7 days.
WHERE
AuditDate < DATEADD(MONTH, -3, DATEDIFF(DAY, 0, GETDATE())) AND
Class = 'D'
[Naturally pls no pts for me for something that routine.]
Since you want "within Class D", you probably need eq rather than ne.
And 3 months instead of 7 days.
WHERE
AuditDate < DATEADD(MONTH, -3, DATEDIFF(DAY, 0, GETDATE())) AND
Class = 'D'
[Naturally pls no pts for me for something that routine.]
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Seems like an excellent correction to me :-) .
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
You guys are AWESOME.
Thank you so much for everyone's time on this request.
Paul
Thank you so much for everyone's time on this request.
Paul
ASKER
I also need the report for ONLY the ones that have the CLASS D. I'm not knowledgeable in SQL but I think <> means Not Equal To?