Hi guys, i hope that you can help me with this problem that i've got on SQL

The scenario is this..

I store in a table called orders, the week number and the weekday, of the date that the order was placed.. as this
datepart(week, getdate()) and datepart(weekday, getdate())

The problem is that i need to generate a report that brings me the orders for a specific date range..

The 2 date ranges are taken from a form that ask for two #mm/dd/yyyy# format textboxes, so my question is.

How can i create a query, that first translate the given dates, and the show the orders between? just if i was doing a

select * from order where DatePlaced between(date1, date2)

Thanks for your time.:)

It's impossible to resist asking, "Why on earth did you design the table this way? What were you thinking?"

If you had stored the order date you wouldn't be here asking this question. You could have generated the week number and week day anytime you needed them from the order date. Instead, storing them and not the order date has discarded information and this discarded information cannot be reconstructed. In particular, the year has been lost, so you can no longer unambiguously determine which year an order is in. It is relatively straightforward to extract the day and week from the two parameter dates, and compare the week numbers and days, (you could multiply the week number by 10 and add the day and compare the results, for example). But, you can't know which year an order was in. Dates in the first week in January, for example, will have identical day and week numbers even if they are in different years. Thus, you will pick up orders from other years whose dates also fall within the range of the week number and day.

You're in trouble, I'm afraid.



Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

In addition to what Jeff says, you must also be aware of the many different ways of calculating week numbers. I think that SQL Server always has january 1 in week nr 1, and december 31 is always in the last week of the year (52 or 53).

But some places, like here in Norway, we always have week number 1 in the first week of the year with at least 4 days from current year (so january 1 could actually be in week 52 of the previous year).

Just something to consider.

Gert

Thanks to all for the replys, i forgot to say that i found the table that way... no kidding, but i also found that there is also a yearNumber field too, as usually filled by a datepart(year, getdate()), so i dont know if that would help?

Thanks again!

Having the year helps a lot.

Off the top of my head, writing a user defined function which would reconstitute the date from the year, week number and day of week is certainly doable, but if all you need to do is select based on a date range, then simply extend my original idea and multiply the year by 100, the week by 10, then add those results and the day number. Do that for each row and the two date parameters and the WHERE clause is a simple BETWEEN test.

Then find the guy that designed that table, take him out back and shoot him. :D

Jeff Mason
Custom Apps, Inc.
www.custom-apps.com

Thanks Jeff, i'll try your suggestions and tell how it did.

:0)

It worked fine! thanks a lot Jeff and everybody, by the way i had to make a last fix of the query... i had to multiply the yearnumber by 10000 because, the famous guy, had last year's weeks, only God knows why and how, starting with a 1 in front. so weeknumber 1 was 101 and week number 52 was 152... i think it was beacuse that way he was separating orders by year.... JEEZZ!!!

LOL... thanks again guys

Saludos desde Costa Rica.