Julian Date

A table that I am querying from has the date field in julian date format "04194". I am only wanting to pull the information from this table that is => than todays date. Maybe some of you have done this before and can give a little help in finding the best way to do this?You could take today's date and subtract it from the first day of the year (DATEDIFF ( datepart , startdate , enddate ) ) to give you an int value for the day of the year (actually day of year -1 since 20040101 - 20040101 = 0). Then convert to a 3 character value and concatinate the two digit year to the front and ... tadaaa ... the julian date.|||? I am not following you?|||You can get the Julian date using:SELECT 1000 * (DatePart(yy, GetDate()) % 100) + DatePart(dy, GetDate())If you need it in character format, you can use:SELECT Replace(Str(
1000 * (DatePart(yy, GetDate()) % 100) + DatePart(dy, GetDate()), 5), ' ', '0')After you've got that, the rest should just be a simple compare.

-PatP|||Or you could go the other way so you can utilize all of SQL Server's functions

DECLARE @.julian char(5), @.gregorian datetime
SELECT @.julian = '04194'
SELECT @.gregorian = DATEADD(dd,CONVERT(int,SUBSTRING(@.julian,3,3)),CON VERT(datetime,'20'+SUBSTRING(@.julian,1,2)+'/01/01'))
SELECT DATEDIFF(dd,GetDate(),@.gregorian)|||Perfect!...Thanks for your help. I was going about it the wrong way. I was trying to convert the julian date and then compare.|||Thanks also Brett That was a road I was eventually going to have to cross.|||udf's...Makes a perfect house warming gift

CREATE FUNCTION udf_JulianToGregorian(@.julian char(5))
RETURNS datetime
AS
BEGIN
DECLARE @.gregorian datetime
SELECT @.gregorian =
DATEADD(dd,CONVERT(int,SUBSTRING(@.julian,3,3))
,CONVERT(datetime
,CASE WHEN SUBSTRING(@.julian,1,2) BETWEEN '00' AND '50'
THEN '20'
ELSE '19'
END
+SUBSTRING(@.julian,1,2)+'/01/01'))
RETURN @.gregorian
END
GO

DECLARE @.julian char(5)
SELECT @.julian = '04194'
SELECT dbo.udf_JulianToGregorian(@.julian)
GO

Joining tables

I have two tables one table T1 only have one result per date table 2 T2 can
range from 0 to 8 results per date,
I want to pull back all of the results in T2 and sum the totals, in my
example it will be summing "Stafftime" and "items", in T1 I need to sum the
"Contrctaed_Hours" and the OverTime. when I do sum the contracted Time I am
getting 61200, as there ate 2 rows in T1.
How can I sum my four colums but only get results for one Date in the T1
table'
Cheers
Mark
Select *
From T1
T1
Row_Date Contracted_Hours OverTime Ext_No
20050209 30600 0 4227
Select *
From T2
T2
RowDate Area Ext_No StaffTime Items
20050209 1 4227 24656 50
20050209 2 4227 6164 10
Select T1.RowDate, T1.Ext_No, sum(Contracted_Hours) as Con_Hrs,
Sum(OverTime) as OT, Sum(Items) as Completed_Work
From T1
Inner Join T2
On T1.Ext_No = T2.Ext_No
Where RowDate = '20050209' and Ext = 4227Try,
Select
T1.RowDate,
T1.Ext_No,
sum(distinct T1.Contracted_Hours) as Con_Hrs,
Sum(distinct T1.OverTime) as OT,
(select Sum(T2.Items) from T2 where T2.RowDate = T1.RowDate and T1.Ext_No =
T2.Ext_No) as Completed_Work
From
T1
Where
T1.RowDate = '20050209' and T1.Ext_No = 4227
go
AMB
"sh0t2bts" wrote:

> I have two tables one table T1 only have one result per date table 2 T2 ca
n
> range from 0 to 8 results per date,
> I want to pull back all of the results in T2 and sum the totals, in my
> example it will be summing "Stafftime" and "items", in T1 I need to sum th
e
> "Contrctaed_Hours" and the OverTime. when I do sum the contracted Time I a
m
> getting 61200, as there ate 2 rows in T1.
>
> How can I sum my four colums but only get results for one Date in the T1
> table'
>
> Cheers
> Mark
> Select *
> From T1
> T1
> Row_Date Contracted_Hours OverTime Ext_No
> 20050209 30600 0 4227
> Select *
> From T2
> T2
> RowDate Area Ext_No StaffTime Items
> 20050209 1 4227 24656 50
> 20050209 2 4227 6164 10
> Select T1.RowDate, T1.Ext_No, sum(Contracted_Hours) as Con_Hrs,
> Sum(OverTime) as OT, Sum(Items) as Completed_Work
> From T1
> Inner Join T2
> On T1.Ext_No = T2.Ext_No
> Where RowDate = '20050209' and Ext = 4227
>
>|||I think you have to use LEFT OUTER JOIN.
"sh0t2bts" wrote:

> I have two tables one table T1 only have one result per date table 2 T2 ca
n
> range from 0 to 8 results per date,
> I want to pull back all of the results in T2 and sum the totals, in my
> example it will be summing "Stafftime" and "items", in T1 I need to sum th
e
> "Contrctaed_Hours" and the OverTime. when I do sum the contracted Time I a
m
> getting 61200, as there ate 2 rows in T1.
>
> How can I sum my four colums but only get results for one Date in the T1
> table'
>
> Cheers
> Mark
> Select *
> From T1
> T1
> Row_Date Contracted_Hours OverTime Ext_No
> 20050209 30600 0 4227
> Select *
> From T2
> T2
> RowDate Area Ext_No StaffTime Items
> 20050209 1 4227 24656 50
> 20050209 2 4227 6164 10
> Select T1.RowDate, T1.Ext_No, sum(Contracted_Hours) as Con_Hrs,
> Sum(OverTime) as OT, Sum(Items) as Completed_Work
> From T1
> Inner Join T2
> On T1.Ext_No = T2.Ext_No
> Where RowDate = '20050209' and Ext = 4227
>
>|||SELECT
T1.RowDate,
T1.Ext_No,
T1.Contracted_Hours,
T1.OverTime,
T2.StaffTime,
T2.Items
FROM
T1
INNER JOIN
(
SELECT
T2.RowDate,
T2.Ext_No,
SUM(StaffTime),
SUM(Items)
FROM
T2
GROUP BY
T2.RowDate,
T2.Ext_No
) T2group
ON T2group.Ext_No = T1.Ext_No
AND T2group.RowDate = T1.Row_Date
"sh0t2bts" wrote:

> I have two tables one table T1 only have one result per date table 2 T2 ca
n
> range from 0 to 8 results per date,
> I want to pull back all of the results in T2 and sum the totals, in my
> example it will be summing "Stafftime" and "items", in T1 I need to sum th
e
> "Contrctaed_Hours" and the OverTime. when I do sum the contracted Time I a
m
> getting 61200, as there ate 2 rows in T1.
>
> How can I sum my four colums but only get results for one Date in the T1
> table'
>
> Cheers
> Mark
> Select *
> From T1
> T1
> Row_Date Contracted_Hours OverTime Ext_No
> 20050209 30600 0 4227
> Select *
> From T2
> T2
> RowDate Area Ext_No StaffTime Items
> 20050209 1 4227 24656 50
> 20050209 2 4227 6164 10
> Select T1.RowDate, T1.Ext_No, sum(Contracted_Hours) as Con_Hrs,
> Sum(OverTime) as OT, Sum(Items) as Completed_Work
> From T1
> Inner Join T2
> On T1.Ext_No = T2.Ext_No
> Where RowDate = '20050209' and Ext = 4227
>
>

Joining multiple pks

I have 2 tables that I need to pull data from.

Table 1
PK ID
PK Name
PK Address
PK State
Postion
Status

Table2
PK ID (FK)
PK Name (FK)
PK Address (FK)
PK State (FK)
PK Actions
PK History

Now i dont believe this db was designed in the most efficient way to begin with, but I'm trying to write a query to effectively pull data from it.

SELECT DISTINCT Table1.ID, Table1.Name, Table1.Position, Table1.Status, Table2.Address, Table2.State, Table2.Actions, Table2.History
FROM Table1, Table2
WHERE Table1.ID=Table2.ID AND
Table1.Name=Table2.Name AND
Table1.Address=Table2.Address AND
Table1.State=Table2.State AND
Table1.ID = "123"

This is what i got so far. Not very pretty, and the SQL is still running. Basically I need to join these 2 tables, by 4 fields. The field names have been changed bc the real ones are kind of confusing.

I'd appreciate any help possible,

Thanks,
Charlieif your query is really slow, then most likely the FK in Table2 needs an index, but your SQL is fine -- i prefer JOIN syntax over table list syntax, but the query is fine

well, except for the DISTINCT, you probably don't need that (and it does involve a total sort of all columns in all rows, so removing it will definitely speed up the query)|||I'll second R937's suggestion for a non-unique index on Table 2 (ID, Name, Address, State). That should improve performance a great deal.

I would also recommend that you ensure that there is a unique index on Table1 (ID, Name, Address, State), and that you make sure that ID is the first (leftmost) column in that index so that the optimizer can quickly find the row by id.

Just curious, but what database engine are you using? There are some engine specific tips that could apply, especially if you have very large tables.

-PatP|||On some systems it might even help to add the condition
AND Table2.ID = '123'
(which logically speaking is of course redundant).
Certainly when that column has an index, this could speed up the query a lot!|||I would also recommend that you ensure that there is a unique index on Table1 (ID, Name, Address, State) ... i think the PK adequately covers this requirement, no?|||i think the PK adequately covers this requirement, no?No. Not all database engines generate an index to enforce the PK definition, although most of them do. As a side note, a PK index might include the ID column, but not as the first column in the definition (different engines that support DRI have different rules for how they manage the PK definition).

-PatP|||just so that i don't look like a complete idiot the next time someone asks me if PK uniqueness is enforced by means of a unique index, would you kindly give an example of a database engine which does not do this

also, please be careful not to proliferate the idea of declaring a separate unique index on the PK column(s), because in most databases this will be redundant, superfluous, inefficient, and redundant|||Many implementations of MySQL do nothing whatsoever with DRI. They allow you to declare it, then completely ignore that declaration because the data file they are using doesn't support it. Very few implementations that I've seen even can support FK definitions, and a significant number of commercial implementations choose not to implement PK declarations to improve insert performance and reduce their internal tech support load.

Some versions of DB2 use "interesting" ways to determine how the PK will be enforced, for example they'll create no index for very small tables (because a table scan is cheaper than an index lookup for small amounts of data in that specific implementation), and don't have a way for the engine to change that decision if the table grows. They also tend to force integers and dates toward the end of any index unless you get very specific about it.

That's exactly why I was asking what engine the poster was using in my first response. There can be all kinds of engine specific quirks that can cause performance problems like this, and they are decidedly NOT intuitively obvious. At least if we knew what engine they were having problems with, we might have a better chance at helping them.

Besides all of the things you pointed out about creating a separate unique index to "back up" an existing PK, that can be overkill too. ;)

-PatP|||Many implementations of MySQL do nothing whatsoever with DRI. so what? PK indexes <> support DRI

i'm pretty sure MySQL uses an index to enforce PK uniqueness

the DB2 example (no index created for the PK) is nice, though|||No, not at all! What I'm saying is that most of the MySQL installations that I have experience using do not have any support at all for DRI.

Out of the box, the current generation of MySQL uses MyISAM. The default install does provide an index to enforce the PK, but most of the "web farm" operators disable that to increase performance and to reduce the amount of tech support that they need to provide their users. We won't go into what I think of that practice, it would just infuriate me for no good reason! At least as far as I know, MyISAM does not provide any support for FKs no matter what you do with it.

I guess that my point was that there are a number of ways to set up databases that use SQL or SQL-like languages, with varying degrees of support for DRI. We can't assume that just because a poster thinks that they have DRI that they've even formally declared it, and without confirming the details such as database engine, etc we can't assume that they have the features that we take for granted.

-PatP|||stop with the DRI already

engine creates unique index for PK, yes or no? that's all, yes or no -- forget the DRI stuff

that story about web farms disabling indexes to enforce the PK, i'm going to look into that, because that's insane|||How can you have a PK without DRI? If I can't declare it using standard SQL constructs, then have the database engine enforce that declaration, it is simply a pleasant notion to me. There are ways to coerce many of the database engines into doing what we expect a Relational Algebra Primary Key to do, but those fall into the category of what I consider to be "engine specific tricks", not what I consider to be a PK.

-PatP|||aw come on pat, give it up

how can you have a PK without DRI? like this --

create table patp
( id integer not null primary key
, foo varchar(9)
, bar varchar(37)
)

voila, i have declared a primary key

you have admitted that yes, this does create a unique index, unless one happens to be using a nefarious web farm

is that more or less what you're saying?|||how can you have a PK without DRI? like this --

create table patp
( id integer not null primary key
, foo varchar(9)
, bar varchar(37)
)You used DRI.

-PatP|||Oh yeah, if you are using Microsoft SQL, Oracle, or Sybase, then you'll create a unique index to enforce the primary key that you declared using Declared Referential Integrity.

-PatP|||<voice type="mr. burns">ehhhhhhhhhhhhxcellent</voice>

can we now revisit some earlier posts, like, say, starting around post #5?

pat: you (original poster) should declare a unique index
me: wouldn't the PK do?
pat: no, not all engines create an index for the PK
me: oh? which ones don't?
pat: mysql doesn't
me: what?! surely it does...
pat: not if it has been turned off
me: what? you can do that?|||create table patp
( id integer not null primary key
, foo varchar(9)
, bar varchar(37)
)
...this does create a unique index, unless one happens to be using a nefarious web farm
DB2 for z/OS does not automatically create an index in this case.

The unique index has to be created (manually) before inserting into this table.

Only when using schema's (which were only introduced in DB2 for z/OS v8) one gets the "automatic index creation".
But even in v8, schema's need not be used.|||After some offline discussion with R937, I want to reiterate what I said earlier, with added emphasis.

I would also recommend that you ensure that there is a unique index on Table1 (ID, Name, Address, State), and that you make sure that ID is the first (leftmost) column in that index so that the optimizer can quickly find the row by id.Using most of the major database products, these indicies will be created automagically for you. What I wanted the poster to do was verify that the expected indicies actually do exist for them, for their engine in their database. We (both R937 and I) expect those indicies to exist, but I'm a "belt and suspenders" type that is willing to take an extra minute or two to ensure that what I expect really exists where the rubber meets the road.

-PatP|||thanks pat

and the fact that there are engines where you can create a primary key but it's not going to be unique, well, that just frosts my petunias...

:)|||This is an example of my favourite type of thread. In ever so many ways :)|||Very interesting material in this thread.

I'm using DB2 v8.2 on AIX by the way.

JOIN Question

Good Morning,

I'm attempting to pull information regarding the orders placed over the last week for all of our customers. I want my result set to show a listing for each customer and any orders they have placed, or if they have not placed any orders, just a line with a NULL value or something similar. Ultimately, I may use a COUNT on this information, but for right now, I am just trying to work around the JOIN issue I'm having. Here is an example of what I am trying to do. I rewrote the query with generic names.

Code Snippet

SELECT Customers.CustID, Orders.OrderID, Order.OrderDate
FROM Customers FULL OUTER JOIN Orders
ON Customers.CustID = Orders.CustID
WHERE Orders.OrderDate >= DATEADD(week, DATEDIFF(week, 0, GETDATE())-1, 0) AND
Orders.OrderDate < DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)
ORDER BY Orders.OrderDate, Customers.CustID

I've tried this query using LEFT, RIGHT and FULL OUTER JOINS, but none seem to give me the results I desire. Below is an example of what I would like to see.

CustID OrderID OrderDate

00001 02345 08/05/2007
00001 02356 08/05/2007
00002 02347 08/05/2007
00003 NULL 08/05/2007
00004 02349 08/05/2007
00001 NULL 08/06/2007
00002 02358 08/06/2007
00003 02360 08/06/2007
00004 NULL 08/06/2007
.
.
.
.
.

Many thanks in advance for any help you can provide.
--
Anthony

The following query might help you..

Code Snippet

Create Table #orderdata (

[CustID] Varchar(100) ,

[OrderID] Varchar(100) ,

[OrderDate] datetime

);

Insert Into #orderdata Values('00001','02345','08/05/2007');

Insert Into #orderdata Values('00001','02356','08/05/2007');

Insert Into #orderdata Values('00002','02347','08/05/2007');

Insert Into #orderdata Values('00004','02349','08/05/2007');

Insert Into #orderdata Values('00002','02358','08/06/2007');

Insert Into #orderdata Values('00003','02360','08/06/2007');

Create Table #customer (

[CustID] Varchar(100)

);

Insert Into #customer Values('00001');

Insert Into #customer Values('00002');

Insert Into #customer Values('00003');

Insert Into #customer Values('00004');

Create function which will list all the dates between given from & to dates.

Code Snippet

Create Function DaysBetween(@.Startdate datetime,@.Enddate datetime)

returns @.dates Table (Date datetime)

as

Begin

While @.Startdate <= @.Enddate

Begin

Insert Into @.dates values(@.Startdate);

Set @.Startdate = Dateadd(dd,1,@.Startdate);

End

return;

End

First Cross Join the dates & customer id, then use outer join to link with your order table

Code Snippet

Select CustAndDate.CustID, OrderID, CustAndDate.Date from

(Select date,[CustID] from DaysBetween( DATEADD(week, DATEDIFF(week, 0, GETDATE())-2, 0), DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0)) days

cross join #customer) as CustAndDate

Left Outer Join #orderdata orders on orders.[CustID] = CustAndDate.[CustID] and orders.[OrderDate] = CustAndDate.date

|||

You were unable to get the results you desired simple because if a Customer did NOT place an order on a particular day, there would not be a OrderDate available.

As Mani has demonstrated very well, the solution to problems like this is to use some form of a Calendar table, and then to include the Calendar table in the JOIN to have a way to include 'missing dates'. You may find this article about the benefits of having a Calendar table to be useful. (Most databases 'should' have a permanent Calendar table. So many different operations involving dates are simplified by using a Calendar table.)

Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519

Mani -Excellent presentation of your suggested solution!!

|||First, thanks to both Mani and Arnie.

I'm still a bit confused on this. I'm reading about creating a calendar table now, but I guess I don't understand why if I request the names from one table, then join with orders in another table that it would not return at least one row for each name, regardless of whether it had an order associated and my thought was that in those situations, that single row would return NULL and I could work with that.

The reason I went the route of using a JOIN was that I saw if I just pulled from the orders table and a particular customer did not place an order, there would be no way of coming up with those names out of thin air.

I apologize for my continued confusion here.

Thanks Again
--
Anthony
|||

Anthony,

You can create a LEFT JOIN between Customers and Orders, and ALL Customers will be listed -BUT the OrderDate will be NULL.

From you presentation of desired results, there would be no way to indicate a OrderDate for a Customer that has not placed an Order -SINCE you don't know on what date they didn't place it.

So to have a 'placeholder' for the Customer on each date (as in your desired results), you have to JOIN with a table that has all possible dates -therefore the JOIN with a Calendar table.

|||Arnie,

After playing around with this a bit more, I understand now. It turns out, we had created a function previously that has the same effect as the Calendar table. I was able to select the customers and dates using the cross join on the function and customers table, then use the left join on that and the orders table and it worked just as Mani had stated.

I thank you both again for your assistance. I may still look into creating numbers and calendar tables as this seems like it might be a better approach than our existing function.
--
Anthony
|||Having static tables is most definitely better than having a function that is 'on demand' creating a temporary table. Every time you call the function, it creates a table -so every time you use it, you are wasting time and slowing performance.

|||Alright. I've got another question that is directly related to this. If I should post to a new thread, I apologize. What if I wanted to get the same result, but also factor in a particular product? So my example of how the output would look would be the same, but there would be an addition condition of something like ...

Code Snippet

AND ProductID = 23456

The Product ID is in the Orders table, so I tried just tacking that on to the WHERE clause in my outer select (the one with the LEFT OUTER JOIN), but it results in the ones that do not have that product not showing instead of showing zeros. In fact, if I use the query for all orders, it works, but when I add on that extra condition, with no other changes, it does not. I would venture this has something to do with the nested selects and the couple joins.

I feel like I understand what was done in your previous suggestion, but I'm not sure I am familiar enough to modify that in a way to suit this need.

Thanks again for the help.
--
Anthony

|||

Add

AND ProductID = 23456

to the JOIN condition for the #OrderData table (using Mani's sample code).

|||Arnie,

Sorry I didn't get your question until this morning. I believe I am already doing what you are suggesting, but it is not working. Currently, I have my query pulling the orders for specific date ranges dependant upon the day of the week. In my existing query, I'm using the function that we already had setup (I will likely setup the numbers and calendar tables) and so long as I am looking at all orders, it works fine. Here is my query so far. By the way, I've had to modify my query so not to give out sensitive information. I apologize if I've messed something up in the query below, but I assure you, this does give me the desired result.

Code Snippet

DECLARE @.StartTime datetime
DECLARE @.EndTime datetime

IF DATEPART(weekday, GETDATE()) IN (1, 2)
BEGIN
SET @.StartTime = DATEADD(week, DATEDIFF(week, 0, GETDATE())-1, 0)
SET @.EndTime = DATEADD(day, -1, DATEADD(week, DATEDIFF(week, 0, GETDATE()), 0))
END
ELSE IF DATEPART(weekday, GETDATE()) = 3
BEGIN
SET @.StartTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-1, 0)
SET @.EndTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-1, 0)
END
ELSE IF DATEPART(weekday, GETDATE()) = 4
BEGIN
SET @.StartTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-2, 0)
SET @.EndTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-1, 0)
END
ELSE IF DATEPART(weekday, GETDATE()) = 5
BEGIN
SET @.StartTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-3, 0)
SET @.EndTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-1, 0)
END
ELSE IF DATEPART(weekday, GETDATE()) = 6
BEGIN
SET @.StartTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-4, 0)
SET @.EndTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-1, 0)
END
ELSE IF DATEPART(weekday, GETDATE()) = 7
BEGIN
SET @.StartTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-5, 0)
SET @.EndTime = DATEADD(day, DATEDIFF(day, 0, GETDATE())-1, 0)
END

SELECT CustAndDate.CustName, COUNT(Orders.[Order Num]) AS Total, LEFT(DATENAME(weekday, CustAndDate.TheDate), 3) AS DayName
FROM (
SELECT TheDate, CustName, CustID
FROM fn_TimeDimension(@.StartTime,@.EndTime) CROSS JOIN Customers
WHERE CustID IN (00020, 00025, 00027, 00029, 00030, 00032, 00034)
) AS CustAndDate LEFT OUTER JOIN Orders
ON CustAndDate.CustID = Orders.CustID AND CONVERT(char(8), CustAndDate.TheDate, 112) = CONVERT(char(8), Orders.[Order Date], 112)
WHERE DATEPART(weekday, CustAndDate.TheDate) IN (2, 3, 4, 5, 6)
GROUP BY DATEPART(weekday, CustAndDate.TheDate), CustAndDate.CustName, LEFT(DATENAME(weekday, CustAndDate.TheDate), 3)
ORDER BY DATEPART(weekday, CustAndDate.TheDate), CustAndDate.CustName

Now what I'm trying to do is restrict this to only show certain products, so my thought was that after the line that reads

Code Snippet

WHERE DATEPART(weekday, CustAndDate.TheDate) IN (2, 3, 4, 5, 6)

I would add another line that reads

Code Snippet

AND ProdID = 12345

Unfortunately, this does not work. So I don't know if I am putting that additional condition in the wrong place or what. The ProdID is in the Orders table.

Thanks Again for all your help.
--
Anthony

|||

Try something more like this:

LEFT OUTER JOIN Orders
ON ( CustAndDate.CustID = Orders.CustID

AND CONVERT(char(8), CustAndDate.TheDate, 112) = CONVERT(char(8), Orders.[Order Date], 112)

AND ProdID = 12345

)
WHERE ...

AS a side comment, using CONVERT() on both sides of the equality ensures that indexes CANNOT be used and the the entire table has to be scanned, adding significantly to execution time.

|||Arnie,

This seems to work. I will have to do some more testing. The reason I was using CONVERT on both sides of the equation was to get both dates in the same format as I am under the impression that when you are performing a JOIN, your columns that you join on must be the same, so if TheDate = '08/09/2007 00:00:00' and Order Date = '08/09/2007 11:22:05', then you would have an issue. I guess I could use ISODate instead of TheDate from our calendar function or the calendar table once that has been established and just perform the convert on the date from the orders table.

What would you suggest in a situation such as mine?
|||

You could do something like this:

LEFT OUTER JOIN Orders
ON ( CustAndDate.CustID = Orders.CustID

AND ( Orders.OrderDate >= CustAndDate.TheDate

AND Orders.OrderDate < ( dateadd( day, 1, CustAndDate.TheDate ))

)

AND ProdID = 12345

)
WHERE ...

The Orders.OrderDate is not converted, and it will properly use any indexing.

Substitute your Calendar table date for the CustAndDate.TheDate value, if a better 'match'.

|||So in your example, you are joining on the condition that the Order Date be greater than or equal to 'TheDate', which would be midnight of the day in question, but not more than a day difference between the two. I will certainly take your suggestion under consideration. At present, the query is not taking more than a couple seconds to run, however I any opportunity I have to improve performance is not something to be ignored.

Thanks again to you and Mani for your assistance. I've learned quite a lot since working with SQL, but there is always something new that you've not run into before and it is great to have a resource such as these forums and individuals who take the time to assist others.