March 9, 2010 at 10:19 am
That ADDITIONAL table we just added maybe the problem because now, it has gone back to getting data that begin from March 16th.
March 9, 2010 at 10:24 am
simflex-897410 (3/9/2010)
That ADDITIONAL table we just added maybe the problem because now, it has gone back to getting data that begin from March 16th.
Change the INNER JOIN to a RIGHT OUTER JOIN (Added table is listed first, correct?).
March 9, 2010 at 10:24 am
So, please follow Lynns advice and change the join fom INNER to LEFT OUTER.
March 9, 2010 at 10:26 am
lmu92 (3/9/2010)
So, please follow Lynns advice and change the join fom INNER to LEFT OUTER.
I think you want a RIGHT OUTER JOIN the way the query is currently written.
March 9, 2010 at 10:45 am
Lynn Pettis (3/9/2010)
lmu92 (3/9/2010)
So, please follow Lynns advice and change the join fom INNER to LEFT OUTER.I think you want a RIGHT OUTER JOIN the way the query is currently written.
:blush:
I should stop posting for a moment until my head is clear again.
I had a rather long meeting today at work that made me completely questioning my programming skills as well as basic logic skills as well as my understanding of "common sense"...
I obviously took the "spirit" of that meeting into this thread. Sorry about that and thank you Lynn for cleaning up the mess I created.
:blush:
March 9, 2010 at 10:58 am
Man, you are SQL God; just adding right outer join showed the correct data.
I must have done something wrong because just before I posted it, I tried right outer join but to no avail.
So, I am going to add next table and post back.
I can't thank you enough - you guys but especially you Lutz for staying with me on this.
THANK YOU!
March 9, 2010 at 11:05 am
lmu92 (3/9/2010)
Lynn Pettis (3/9/2010)
lmu92 (3/9/2010)
So, please follow Lynns advice and change the join fom INNER to LEFT OUTER.I think you want a RIGHT OUTER JOIN the way the query is currently written.
:blush:
I should stop posting for a moment until my head is clear again.
I had a rather long meeting today at work that made me completely questioning my programming skills as well as basic logic skills as well as my understanding of "common sense"...
I obviously took the "spirit" of that meeting into this thread. Sorry about that and thank you Lynn for cleaning up the mess I created.
:blush:
Hey, Lutz, your the one who has been doing the heavy work on this one. Nothing to be sorry about here, we all have those senior moments at one time or another.
Let's keep on working.
March 9, 2010 at 11:08 am
lmu92 (3/9/2010)
Lynn Pettis (3/9/2010)
lmu92 (3/9/2010)
So, please follow Lynns advice and change the join fom INNER to LEFT OUTER.I think you want a RIGHT OUTER JOIN the way the query is currently written.
:blush:
I should stop posting for a moment until my head is clear again.
I had a rather long meeting today at work that made me completely questioning my programming skills as well as basic logic skills as well as my understanding of "common sense"...
I obviously took the "spirit" of that meeting into this thread. Sorry about that and thank you Lynn for cleaning up the mess I created.
:blush:
Glad you found the root cause 🙂
I haven't been that much of a help though (I think). But Lynn, AKA "Saint Lynn" did an excellent job. As usal.
March 9, 2010 at 11:12 am
Jeff Moden (3/5/2010)
sturner (3/4/2010)
try this:Select* from mytable
WHERE functionDate > CONVERT(char(10),getdate(),101)
that assumes all times are 00:00:00
It also causes a costly implicit conversion. Use the method that Lutz posted instead.
Not if you do this:
declare @today datetime
set @today = convert(char(10),getdate(),101)
select * from myTable
where functionDate < @today
My example was deliberately simplified to attempt to answer his question. Only a dementia patient would put my example into direct use against a large table (or think that it should be interpreted that way)
The probability of survival is inversely proportional to the angle of arrival.
March 9, 2010 at 11:17 am
sturner (3/9/2010)
Jeff Moden (3/5/2010)
sturner (3/4/2010)
try this:Select* from mytable
WHERE functionDate > CONVERT(char(10),getdate(),101)
that assumes all times are 00:00:00
It also causes a costly implicit conversion. Use the method that Lutz posted instead.
Not if you do this:
declare @today datetime
set @today = convert(char(10),getdate(),101)
select * from myTable
where functionDate < @today
My example was deliberately simplified to attempt to answer his question. Only a dementia patient would put my example into direct use against a large table (or think that it should be interpreted that way)
Some people will take what is posted as gospel. Best to be as clear as possible whenever possible rather than leave things to chance. Been there, done that, got the t-shirt, don't want another one if I can help it.
March 9, 2010 at 11:19 am
Yes, I read Lynn's posts a lot, just as much as I read yours and Sturner's.
As far as this thread is concerned, you are the star and 5 stars go out to you for your kindness and patience.
Right now, it appears to be working but just one last clarification.
I only need to change the first 2 joins, from INNER JOIN to RIGHT OUTER JOIN.
Ones with LEFT OUTER JOIN are ok.
Again, many, many thanks to you all of those other wonderful people who contributed their time and expertise.
Thank you.
I just need to drink some water and my headache will finally go away.
March 9, 2010 at 11:24 am
I'd need to review the code again to be sure, but if the query is returning the appropriate data, then you should have it right.
March 9, 2010 at 11:56 am
Hi Lynn,
I have just sent them a copy of data generated.
Please give me a few minutes to get a feedback from the users.
Again, I am truly grateful for you guys' generosity.
This is the best forum I have visited so far and I have visited a lot.
March 9, 2010 at 1:34 pm
They said they are getting too many blank(null values) for the following fieldnames:
ID number, Last and First Name, Defend Name, Hearing Type, Case Number, DA Number Event
Below is the latest code I just ran.
SELECT GJ1_APD.IDNumber, GJ1_APD.OffLName, GJ1_APD.OffFName, GJ1_APD.DefLName, CONVERT(VARCHAR(10), DEP.functionDate, 101) AS CourtDate,
DEP.EventTime AS CourtTime, JD.Description AS JudgeName, CD.Description AS CourtNum, GJ1_APD.Charge, GJ1_APD.CaseNum,
DEP.EventComment AS Memo, GJ1_APD.DANumber, ED.Description AS Hearing, [PAD].Description AS DAName, CONVERT(VARCHAR(10),
GJ1_APD.functionDate, 101) AS SubpDate, GJ1_APD.Description AS Event
FROM dbo.vwGJ1_APD GJ1_APD
RIGHT OUTER JOIN dbo.DefendantEventPros DEP ON GJ1_APD.DANumber = DEP.VBKey
RIGHT OUTER JOIN dbo.EventDescription ED ON DEP.EventID = ED.EventID
LEFT OUTER JOIN dbo.CourtDescription CD ON DEP.EventCourtID = CD.CourtID
LEFT OUTER JOIN dbo.JudgeDescription JD ON DEP.Judge = JD.JudgeID
LEFT OUTER JOIN dbo.ProsAttyDescription [PAD] ON DEP.ProsAtty = [PAD].ProsAttyID
WHERE (DEP.functionDate >= CONVERT(varchar(8), GETDATE(), 112)) AND (DEP.UserDelete = 0) AND (DEP.EventID = 654)
March 9, 2010 at 1:40 pm
simflex-897410 (3/9/2010)
They said they are getting too many blank(null values) for the following fieldnames:ID number, Last and First Name, Defend Name, Hearing Type, Case Number, DA Number Event
Of course they do. You changed it to an outer join to display all data starting from today.
What do they expect to see if there are ZERO corresponding data in dbo.vwGJ1_APD for today and tomorrom and the following days until March 16?
Viewing 15 posts - 31 through 45 (of 47 total)
You must be logged in to reply to this topic. Login to reply