| Author |
Message |
carleen
Newbie
Joined: 02 Jul 2008
Online Status: Offline
Posts: 29
|
  
Topic: extracting data
Posted: 06 Sep 2009 at 7:58pm |
Hi
I have two tables, these being a supplier table and a transaction detail table. I am trying to find the suppliers that have had no transactions since a certain date. E.g where the are no transactions(maybe transactions are null?) I cant work out the syntax! Maybe its my join also thats the problem? I want to see where suppliers have no transactions from this date. can anyone help, its seems so simple but for the life of me I cant work it out?
thanks as always help much appreciated!
|
 IP Logged |
|
|
|
leoneking
Newbie
Joined: 03 Sep 2009
Location: Philippines
Online Status: Offline
Posts: 4
|
Posted: 07 Sep 2009 at 2:46am |
Try using a parameter.
Create a parameter will be the date/range where supplier did not have any transactions {?DateEntries}.
Then in the record selection, choose the "not equal to" to the {?DateEntries}
Hope this helps. :)
|
| IP Logged |
|
Jyothi Yepuri
Senior Member
Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
|
Posted: 07 Sep 2009 at 4:30pm |
|
Try something like this
select *
from supplier
where supplierid in (
select supplierid
FROM (
select max(TranDate) as TranDate,supplierId
from Transcationdetail
where "Add filtercondition if you have any"
group by transactionId
) AS Tran
WHERE Tran.TranDate = "Date here"
)
and where "Add filtercondition if you any"
Jyothi
|
| IP Logged |
|
carleen
Newbie
Joined: 02 Jul 2008
Online Status: Offline
Posts: 29
|
Posted: 07 Sep 2009 at 5:19pm |
Thanks, but I am still slightly confused. How do I get that SQL into the report? I copied and pasted, changed the relevant table and field names but it doesnt seem to like the syntax under the formula editor?
|
| IP Logged |
|
Jyothi Yepuri
Senior Member
Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
|
Posted: 07 Sep 2009 at 5:22pm |
|
are you adding Supplier and Transaction tables to the report or using SQL command?
It won't work in formula editor. Its a SQL Query.
Jyothi
|
| IP Logged |
|
carleen
Newbie
Joined: 02 Jul 2008
Online Status: Offline
Posts: 29
|
Posted: 07 Sep 2009 at 5:23pm |
|
I have added those tables to the report, are you suggesting I do it another way? If so can you explain how? ta
|
| IP Logged |
|
Jyothi Yepuri
Senior Member
Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
|
Posted: 07 Sep 2009 at 5:28pm |
|
There is an Add command option in database expert under the selected database, click on it and add your query there.
Jyothi
|
| IP Logged |
|
carleen
Newbie
Joined: 02 Jul 2008
Online Status: Offline
Posts: 29
|
Posted: 07 Sep 2009 at 5:43pm |
It gives me an error missing right parenthesis. code is below including correct table names. I think my trandate needs be < 01/07/208 etc
select *
from supplier
where supidx in
(
select supidx
FROM
(
select max(gltd) as TranDate,supidx
from apthdr
group by apthidx
)
AS Tran
WHERE Tran.TranDate = "01/07/2009"
)
|
| IP Logged |
|
Jyothi Yepuri
Senior Member
Joined: 11 May 2009
Location: Australia
Online Status: Offline
Posts: 127
|
Posted: 07 Sep 2009 at 5:53pm |
|
1) select max(gltd) as TranDate,supidx
from apthdr
group by apthidx
here supidx and apthidx should be same. You should group by supplier id here and supplier id in select list
2)if transDate is datetime type then
change where condition as
Convert(varchar(10),Tran.TranDate,120) = Convert(varchar(10),"01/07/2009",120)
Jyothi
|
| IP Logged |
|
carleen
Newbie
Joined: 02 Jul 2008
Online Status: Offline
Posts: 29
|
Posted: 07 Sep 2009 at 6:07pm |
Ok - i made changes as indicated - see below. however still same message. only thing I am not sure on is, trandate is the new field you have created in the statement (from gldt) is that correct and that select statement creates a new table called Tran - is that also correct? Hence why the last statement is looking at tran.trandate (as these are both not current tables or fields. Just checking as the only thing I cna think of.
select *
from supplier
where supidx in
(
select supidx
FROM
(
select max(gltd) as TranDate,supidx
from apthdr
group by supidx
)
AS Tran
Convert(varchar(10),Tran.TranDate,120) = Convert(varchar(10),"01/07/2009",120)
)
group by supidx
|
| IP Logged |
|
|
|
