Need some SQL Help - 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 2005 Forums

Transact-SQL (2005)

Need some SQL Help

Author

Topic

Harry C
Posting Yak Master

148 Posts

Posted - 2008-06-25 : 19:01:19

tblEnrollment
intTransactionID (PK)
intEnrollment (number of enrollments)
dtmReportDate (Date Reported)

I have some seriously strange requirements here. I know it will make no sense, but I am just asking IF it can be done in SQL, which will make it much simpler on me.

Given these examples, this is as succinct as I can get it (square brackets used to clarify text that should be read as one piece):

If a report was filed at any time during the month in question, then return the TOP enrollment report when sorting all reports with [filing date] >= [the first of that month] ascending.

If a report was not filed at any time during the month in question, then return the TOP enrollment report when sorting all reports with [filing date] < [the first of the next month] descending.

Here are some examples of what the client wants

Example 1:

Reports filed for: 11/1/07, 11/20/07, 1/8/08

Run the enrollment report for January 2008. Report will display January, December, and November for the buckets.

Under January, report the 1/8/08 data, because:
{Data exists for the month} and
{that’s the closest report to the first of the month (by virtue of being the only one)}
Under Dec, report the 11/20/07 data, because:
{Data doesn’t exist for the month} and
{that’s the next most recent report, looking back}
Under November, report the 11/1/07 data, because:
{Data exists for the month} and
{that’s the closest report to the first of the month}

Example 1b:

Suppose a 1/28/08 report is uploaded, but I run the report for January 2008 again.

Then under January, still report the 1/8/08 data, because:
{Data exists for the month} and
{that’s the closest report to the first of the month}

Example 2:

Reports filed for: 3/12/07, 4/10/07, and 5/15/07

Run the enrollment report for June 2007. Report will display June, May, and April for the buckets.

Under June, report the 5/15/07 data, because:
{Data doesn’t exist for the month} and
{that’s the next most recent report, looking back}
Under May, report the 5/15/07 data, because:
{Data exists for the month} and
{that’s the closest report to the first of the month (by virtue of being the only one)}
Under April, report the 4/10/87 data, because:
{Data exists for the month} and
{that’s the closest report to the first of the month (by virtue of being the only one)}

So, is this even possible, given the requirements? The only perinent fields are the ones listed. Please let me know if this could be done. I am leaving for about two hours until 9:30 EST, then I will be back to talk through this. Thanks for taking a look

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-06-26 : 00:34:34

This is very well possible. If i understand you correctly, you just need to grab data from table which is having a dtmReportDate value closest before or equal to interested date.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources