Hi,

I have a view(A) and I am trying to do a join on another table (B) to
include only rows where date values in view A is greater than in table
B. I also want the view to pick up rows in viewA based on date values.
Here is what I have so far:

SELECT *
FROM viewA vw
left JOIN tableB tb ON
vw.id = tb.id and
(vw.date1 > tb.date1 or
vw.date2 > tb.date2 or
vw.date3 > tb.date3)
WHERE vw.date4 > getdate()-1

Not matter what kind of join I use I can get both the rows from the
view where dateA > getdate()-1 AND where date1-3 are greate than in
tableB. Dates 1 - 4 seperate date fields. Could someone please tell
me what I am doing wrong.

Thanks.

brendan_gallagher_2001@yahoo.co.uk (brendan_gallagher_2001@yahoo.co.uk)
writes:
> I have a view(A) and I am trying to do a join on another table (B) to
> include only rows where date values in view A is greater than in table
> B. I also want the view to pick up rows in viewA based on date values.
> Here is what I have so far:
>
> SELECT *
> FROM viewA vw
> left JOIN tableB tb ON
> vw.id = tb.id and
> (vw.date1 > tb.date1 or
> vw.date2 > tb.date2 or
> vw.date3 > tb.date3)
> WHERE vw.date4 > getdate()-1
>
> Not matter what kind of join I use I can get both the rows from the
> view where dateA > getdate()-1 AND where date1-3 are greate than in
> tableB. Dates 1 - 4 seperate date fields. Could someone please tell
> me what I am doing wrong.

I will have to confess that your description of what you want and
your laments of what you get appears contradictive in conjunction
with the query. The problem with verbal descriptions is that they
are not always unambiguous.

The standard recommendation for this kind of question is that you
include:

o CREATE TABLE statement for your tables (possibly simplified) (Just
pretend that your view is a table).
o INSERT statement with sample data.
o The desired result given the samepl.

This both makes it clear what you are looking for, and makes it simple
to copy and paste into Query Analyzer to develop a tested solution.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

Hi Erland,

Thanks for your reply. Here is the SQL you requested :

Note vw_orderstat is the table representing the view (viewA) -
Tbl_OrderStatusLog3 is the table (tableB).

Correction to the initial query:Not matter what kind of join I use I
cannot get BOTH the rows from the
view where dateA > getdate()-1 AND where dates 1 to 3 are greater than
in
tableB. Dates 1 to 4 are seperate date fields. Could someone please
tell
me what I am doing wrong.



CREATE TABLE [vw_orderstat] (
[fundlocation] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[cficashid] [float] NOT NULL ,
[orderid] [float] NOT NULL ,
[transactid] [float] NOT NULL ,
[applied] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CRED] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[cashdate] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[cficash_entry] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL ,
[f_entry] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[h_entry] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
) ON [PRIMARY]
GO


INSERT INTO [vw_orderstat]([fundlocation], [cficashid],
[orderid], [transactid], [applied], [CRED], [cashdate],
[cficash_entry],
[f_entry], [h_entry])
VALUES('DUB', 11968301.0,
7280901.0,0.0,'Y','CRED','2005-06-05','2005-05-31 15:22:00.000',
'2004-10-16 08:35:00.000','2004-10-16 08:35:00.000')

INSERT INTO [vw_orderstat]([fundlocation], [cficashid],
[orderid], [transactid], [applied], [CRED], [cashdate],
[cficash_entry],
[f_entry], [h_entry])
VALUES('DUB', 11968401.0,
7281001.0,0.0,'Y','CRED','2005-06-05','2005-05-31 15:22:00.000',
'2004-10-16 08:35:00.000','2004-10-18 08:35:00.000')

INSERT INTO [vw_orderstat]([fundlocation], [cficashid],
[orderid], [transactid], [applied], [CRED], [cashdate],
[cficash_entry],
[f_entry], [h_entry])
VALUES('DUB', 11968201.0,
7281101.0,0.0,'Y','CRED','2005-06-05','2005-05-31 15:22:00.000',
'2004-10-16 08:35:00.000','2005-05-31 15:17:00.000')

GO

CREATE TABLE [Tbl_OrderStatusLog3] (
[cficashid] [float] NULL ,
[OrderId] [float] NULL ,
[TransactId] [float] NULL ,
[cficash_entry] [datetime] NULL ,
[h_entry] [datetime] NULL ,
[f_entry] [datetime] NULL ,
[paymentVersion] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
[status] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO


INSERT INTO [Tbl_OrderStatusLog3]([cficashid], [OrderId], [TransactId],
[cficash_entry], [h_entry], [f_entry], [paymentVersion])
VALUES(11968301.0,7280901.0,0.0,'2005-05-31 15:22:00.000','2004-10-16
08:35:00.000','2004-10-16 08:35:00.000', 'A')
INSERT INTO [Tbl_OrderStatusLog3]([cficashid], [OrderId], [TransactId],
[cficash_entry], [h_entry], [f_entry], [paymentVersion])
VALUES(11968401.0,7281001.0,0.0,'2005-05-31 15:22:00.000','2004-10-16
08:35:00.000','2004-10-16 08:35:00.000', 'A')

GO

I am looking to create a join (between vw_orderstat and
Tbl_OrderStatusLog3) that will return rows where the 'cashdate' in
vw_orderstat is today (i.e. greater than getdate() -1) AND where the
cficash_entry, f_entry,and h_entry dates in vw_orderstat are greater
than the cficash_entry, f_entry,and h_entry dates that appear in
Tbl_OrderStatusLog3. The common id is the cficashid.

In this example, the only rows that should be returned would be cash
ids 11968401.0 - because the h_entry is greater in vw_orderstat than in
Tbl_OrderStatusLog3.
11968201.0 - because the cashdate is greater than getdate() -1.

In reality,the vw_orderstat is a view on a number of joined
tables/views.


Any help on this would be greatly apreciated.
Thanks
Brendan


Erland Sommarskog wrote:
> brendan_gallagher_2001@yahoo.co.uk (brendan_gallagher_2001@yahoo.co.uk)
> writes:
> > I have a view(A) and I am trying to do a join on another table (B) to
> > include only rows where date values in view A is greater than in table
> > B. I also want the view to pick up rows in viewA based on date values.
> > Here is what I have so far:
> >
> > SELECT *
> > FROM viewA vw
> > left JOIN tableB tb ON
> > vw.id = tb.id and
> > (vw.date1 > tb.date1 or
> > vw.date2 > tb.date2 or
> > vw.date3 > tb.date3)
> > WHERE vw.date4 > getdate()-1
> >
> > Not matter what kind of join I use I can get both the rows from the
> > view where dateA > getdate()-1 AND where date1-3 are greate than in
> > tableB. Dates 1 - 4 seperate date fields. Could someone please tell
> > me what I am doing wrong.
>
> I will have to confess that your description of what you want and
> your laments of what you get appears contradictive in conjunction
> with the query. The problem with verbal descriptions is that they
> are not always unambiguous.
>
> The standard recommendation for this kind of question is that you
> include:
>
> o CREATE TABLE statement for your tables (possibly simplified) (Just
> pretend that your view is a table).
> o INSERT statement with sample data.
> o The desired result given the samepl.
>
> This both makes it clear what you are looking for, and makes it simple
> to copy and paste into Query Analyzer to develop a tested solution.
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
>
> Books Online for SQL Server SP3 at
> http://www.microsoft.com/sql/techinf...2000/books.asp

brendan_gallagher_2001@yahoo.co.uk (brendan_gallagher_2001@yahoo.co.uk)
writes:
> Thanks for your reply. Here is the SQL you requested :
>
> Note vw_orderstat is the table representing the view (viewA) -
> Tbl_OrderStatusLog3 is the table (tableB).
>
> Correction to the initial query:Not matter what kind of join I use I
> cannot get BOTH the rows from the view where dateA > getdate()-1 AND
> where dates 1 to 3 are greater than in tableB. Dates 1 to 4 are
> seperate date fields. Could someone please tell me what I am doing
> wrong.

Thanks for your scripts! It really help to sort things out. Your
query looked sound to me, but I had an experience when I ran it...

Normally the confusion with LEFT JOIN is that people have conditions
in the WHERE clause should be in ON, but this an opposite case. With
the condition in ON, you will always get all rows in the view, as long
as they match the date criteria. So you need to add the date condition
to the WHERE clause, and add a condition which says "there is no
orderstat at all):

SELECT v.*
FROM vw_orderstat v
LEFT JOIN Tbl_OrderStatusLog3 t ON t.cficashid = v.cficashid
WHERE v.cashdate = '2005-06-05' -- convert(char(10), getdate(), 120)
AND (v.cficash_entry > t.cficash_entry OR
v.h_entry > t.h_entry OR
v.f_entry > t.f_entry OR
t.cficashid IS NULL)

Also, I hope that the underlying column do casedate is not a char(10)
in real life!


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp