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 w
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
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]It sounds like your database has problem with Normalization. This statemen
t
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.
Perayu
"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 colle
ct
> goods from us on say 2 days per w
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 Tuesda
ys
> & Thursday, and Depot B wants deliveries Mondays & Fridays.
> When despatching a product, the user will first select a collection day (T
ue
> 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 onl
y
> 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 DeliveryDay
s
> 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 fo
r
> 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 a
nd
> 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 don
e
> 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
>
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>|||"Perayu" <Perayu@.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.|||I added a table:
CREATE TABLE [dbo].[LocationDeliveryDay] (
[LocationID] [tinyint] NOT NULL ,
[DeliveryDays] [tinyint] NOT NULL
) ON [PRIMARY]
GO
removed DeliveryDays from Locations table.
The DeliveryDays will be entered as Sunday - Saturday values as 1 - 7
Then your query will be modified as :
Select CC.CollectionDay,
LD.DeliveryDays,
DeliveryDay =
case when LD.DeliveryDays < (select Datepart(dw, (Getdate()))) then
(select dateadd(dd, (select Datepart(dw, (Getdate())) + LD.DeliveryDays -
4 ), getdate()))
else
(select dateadd(dd, (LD.DeliveryDays - (select Datepart(dw,
(Getdate())))), getdate()))
end
from Locations L
inner join LocationDeliveryDay LD on LD.LocationID = L.LocationID
inner join Customers C on C.CustomerID = L.CustomerID
inner join CarrierCollections CC on CC.CarrierID = C.ManagedCarrierID
Where L.LocationID = @.LocationId
and CC.CollectionDay = @.CollectionDay
When I tried to run this query with @.LocationId = 1, @.CollectionDay = 8 ,
the result will look like this:
CollectionDay DeliveryDays DeliveryDay
-- --
---
8 2 2005-08-23 11:47:28.933
8 4 2005-08-25 11:47:28.933
You can reformat the date result as whatever you want.
I just gave an example of how you could modify your tables so that you can
get what you want. If you are going to modify, you may want to modify table
CarrierCollections also.
Just an one cent idea.
Perayu
"CJM" wrote:
> "Perayu" <Perayu@.discussions.microsoft.com> wrote in message
> news:3398A112-FC1D-4E5C-931E-4BE3237E3FAF@.microsoft.com...
> You may well be right, but suggesting that there is a problem isnt the sam
e
> 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 on
e
> 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 use
d
> 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.
>
>|||Thanks Perayu,
I'd already changed the structure a little; the CarrierCollections table now
has one record per Carrier, per collection day, per delivery day - which I'm
thinking is the kind of change you were hinting at. And I am currently
working on a function to calculate the date of the next delivery day(s).
However, I'll digest your suggestions and see if and how I can incorporate
them, before I go any further down the line. I'll post back with any useful
conclusions.
Thanks for your efforts
Chris|||why not build a calendar table somethgn like this?
CREATE TABLE Calendar
(cal_date DATETIME NOT NULL PRIMARY KEY,
mn_del DATETIME NOT NULL,
tu_del DATETIME NOT NULL,
.
fr_del DATETIME NOT NULL);
Now you can adjust for holidays and use temporal functions on the data.
I also hope you are nto acctually using IDENTITY for locations and
customers.|||Here's my version of this code:
Select L.LocationID, L.LocationName, DD.DeliveryDay,
Case when DD.DeliveryDay <= (Select DatePart(dw,GetDate())) then
(Select DateAdd(dd, 7 - (Select DatePart(dw,GetDate()) - DD.DeliveryDay),
GetDate()))
Else
(Select DateAdd(dd, DD.DeliveryDay - (Select DatePart(dw,GetDate())) ,
GetDate()))
End as NextDate
from Locations L
inner join DeliveryDays DD on DD.LocationID = L.LocationID
inner join Customers C on C.CustomerID = L.CustomerID
inner join CarrierCollections CC on CC.CarrierID = C.ManagedCarrierID
and CC.DeliveryDay = DD.DeliveryDay
where L.LocationID = @.LocationID
and CC.CollectionDay = @.CollectionDay
Order By CollectionDay
I notice it varies from yours within the case statement, but it seems to
work fine for me.
I'm not sure I quite understand your approach. It may be incorrect , but I
haven't tested it fully.
Anyway, thanks for your help...
Chris
