"CJM" wrote:

> Preface: This is a bit of a long read, but if I cut too many corners it
> would all sound like nonsense, so apologies in advance.
>
>
> I'm working on application where customers may have a nominated carrier to
> deliver goods to them on one of several given days. The carrier will collect
> goods from us on say 2 days per week though this may change over time.
>
> Currently we only have one carrier, though there may be more in time, and
> they collect on Tuesdays & Thursdays.
>
> Say we have a Customer with 2 depots; Depot A wants deliveries on a Tuesdays
> & Thursday, and Depot B wants deliveries Mondays & Fridays.
>
> When despatching a product, the user will first select a collection day (Tue
> or Thu), which will then present them with a list of possible delivery
> dates; so in this example, if Thursday was selected, if it was Depot A only
> the following Tuesday would be offered, if it was Depot B both Friday &
> Monday need to be offered. It is this code that is proving the problem for
> me at the moment.
>
>
> Architecture (Snipped DDL at end):
>
> We have a Customers table that records the nominated Carrier for that
> customer.
>
> We have a Locations (i.e. Depots) table with a int field to store the
> preferred DeliveryDays for that depot - so for Depot A, DeliveryDays=10
> (where Tues = 2, Thurs=8, total = 10) which will be queried using bitwise
> operators.
>
> We have a CarrierCollections table which holds a record for each carrier,
> for each day they collect on, which indicates what the possible delivery
> dates for that collection date are:
> e.g..
>
> CarrierID - DeliveryDay - CollectionDays
> 6  -  2  -  12
> 6  -  8  -  23
>
> 12 = Weds/Thurs
> 23 = Mon/Tue/Fri
>
> I can query for a given depot what delivery dates are appropriate:
>
> Select  CC.CollectionDay,(CC.DeliveryDays & L.DeliveryDays) as DeliveryDays
> from Locations L
> inner join Customers C on C.CustomerID = L.CustomerID
> inner join CarrierCollections CC on CC.CarrierID = C.ManagedCarrierID
> Where L.LocationID = @LocationID
> and (CC.DeliveryDays & L.DeliveryDays) > 0
> and CC.CollectionDay = @CollectionDay
>
> which, for Depot A/Thursday collection returns:
>
> CollectionDay DeliveryDays
> ------------- ------------
> 8.00          2.00
>
> For Depot B/Thursday Collection:
>
> CollectionDay DeliveryDays
> ------------- ------------
> 8.00          17.00
>
> What I want to do now is to modify the query to return a row for each
> delivery day including what the date of that delivery date would be, so for
> Depot B example above, I want to return (if run today, 19th Aug):
>
> CollectionDay DeliveryDay NextDate
> ------------- ------------ ------------
> 8.00           Mon         22/08/05
> 8.00           Fri            26/08/05
>
> And this is where I am stuck, I'm still working on it, but so far I haven't
> found a (good) solution. I could handle this in my ASP application, but I'm
> assuming that an SQL-only solution would be better(?).
>
> I'm not sure if what I have done so far is a stroke of genius or a sign of
> madness. I deliberated about storing the DeliveryDays for both the Depot and
> the Carrier Collection tables in separate tables, but I was drawn to the
> bitwise comparison route. I'm not sure if this is foolhardy or not!
>
> So can this be done given my current architecture? If not, could it be done
> if I took a different approach in SQL? Or should I stick with what I have,
> and do the final steps in ASP?
>
> Thanks in advance - you deserve a medal for reading this far...
>
> Chris
>
> DDL:
>
> CREATE TABLE [dbo].[CarrierCollections] (
>  [CarrierID] [int] NOT NULL ,
>  [CollectionDay] [int] NOT NULL ,
>  [DeliveryDays] [int] NOT NULL
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Customers] (
>  [CustomerID] [int] IDENTITY (1, 1) NOT NULL ,
>  [CustomerName] [varchar] (30) COLLATE Latin1_General_CI_AS NOT NULL ,
>  [CustomerType] [tinyint] NULL ,
>  [ManagedCarrierID] [int] NOT NULL
>
> ) ON [PRIMARY]
> GO
>
> CREATE TABLE [dbo].[Locations] (
>  [LocationID] [int] IDENTITY (1, 1) NOT NULL ,
>  [LocationName] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ,
>  [CustomerID] [int] NOT NULL ,
>  [DeliveryDays] [int] NOT NULL
>
> ) ON [PRIMARY]
> GO
>
>
> --
> cjmnew***@REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>
>

"CJM" wrote:

> "Perayu" <Per***@discussions.microsoft.com> wrote in message
> news:3398A112-FC1D-4E5C-931E-4BE3237E3FAF@microsoft.com...
> > It sounds like your database  has problem with Normalization.  This
> > statement
> > tells me that it is not in 1st NormalForm: DeliveryDays=10  (where Tues =
> > 2,
> > Thurs=8, total = 10). It is not atomic field. I would suggest to redesign
> > your database first, then you will be easy to find out the solution.
> >
>
> You may well be right, but suggesting that there is a problem isnt the same
> as identifying the problem, nor solving it. Also, in some situations, you
> don't want to fully normalize the data for performance reasons; is this one
> of those cases?
>
> More importantly, what would you suggest the data structure should be?
>
> By normalizing the data such that there are two extra tables to store the
> DeliveryDays for the Depot's and the Carriers, you will change the SQl used
> in my query, but you will still be left with the same problem in how to
> determine the appropriate delivery & dates and sorted in the right order.
>
>
>