January 13, 2009 at 9:23 pm
Hello,
I am new here and I am not very experienced with more complex sql queries, so I hope someone will be able to help me.
I have two tables tblTicketHeader and tblTicketDetails. Here is the query I am working with.
SELECT [tblTicketHeader].[iKey],
[tblTicketHeader].[iInvoice_Number],
[tblTicketHeader].[iTicket_Date],
[tblTicketHeader].[iDestination_1],
[tblTicketHeader].[iRate],
[tblTicketDetail].[tDriver_FullName],
SUM([tblTicketDetail].[tBill_QTY]) AS TotalUnits,
SUM([tblTicketDetail].[tBill_Total_Charge]) AS Total
FROM [tblTicketHeader]
LEFT JOIN [tblTicketDetail]
ON [tblTicketHeader].[iKey] = [tblTicketDetail].[tIKey]
WHERE [tblTicketHeader].[iTicket_Date] BETWEEN @Invoice_StartDate AND @Invoice_EndDate
AND [tblTicketHeader].[iPosted] = '1'
AND [tblTicketDetail].[tDriver_FullName] = @Driver_Name
GROUP BY [tblTicketHeader].[iInvoice_Number],
[tblTicketHeader].[iTicket_Date],
[tblTicketHeader].[iDestination_1],
[tblTicketHeader].[iRate],
[tblTicketDetail].[tDriver_FullName],
[tblTicketHeader].[iKey];
This query produces no results, and I think it should produce two. If I remove AND [tblTicketDetail].[tDriver_FullName] = @Driver_Name I get some results, but it doesn't restrict them to the driver I want.
Is there a way to make this work? What am I doing wrong?
Thank you.
Ben
January 13, 2009 at 9:50 pm
--between is a little tricky and you need to know are you including the dates
--Test it with the driver_Name you want by replacing the parameter
--Simplfy your queries for clarity as shown below
SELECT h.iKey
,h.iInvoice_Number
,h.iTicket_Date
,h.iDestination_1
,t.iRate
,t.tDriver_FullName
,SUM(d.tBill_QTY) AS TotalUnits
,SUM(d.tBill_Total_Charge) AS Total
FROM tblTicketHeader h
LEFT JOIN tblTicketDetail d
ON h.iKey = d.tIKey
WHERE h.iTicket_Date >=@Invoice_StartDate AND h.iTicket_Date <=@Invoice_EndDate --To make sure you include the dates
AND h.iPosted= '1'
AND d.tDriver_FullName = @Driver_Name
GROUP BY h.iInvoice_Number
,h.iTicket_Date
,h.iDestination_1
,h.iRate
,d.tDriver_FullName
,h.iKey
January 13, 2009 at 9:55 pm
--Corrections
SELECT h.iKey
,h.iInvoice_Number
,h.iTicket_Date
,h.iDestination_1
,h.iRate
,d.tDriver_FullName
,SUM(d.tBill_QTY) AS TotalUnits
,SUM(d.tBill_Total_Charge) AS Total
FROM tblTicketHeader h
LEFT JOIN tblTicketDetail d
ON h.iKey = d.tIKey
WHERE h.iTicket_Date >=@Invoice_StartDate AND h.iTicket_Date<=@Invoice_EndDate
AND h.iPosted= '1'
AND d.tDriver_FullName = @Driver_Name
GROUP BY h.iInvoice_Number
,h.iTicket_Date
,h.iDestination_1
,h.iRate
,d.tDriver_FullName
,h.iKey
January 13, 2009 at 9:56 pm
Thank you for your reply.
Sorry, I should have posted all of the code. This is going to be a stored procedure, but I created a test sql until the bugs get worked out. Here is the full code. I do hard code the values through the parameters.
DECLARE @Driver_Name varchar(10)
DECLARE @Invoice_StartDate datetime
DECLARE @Invoice_EndDate datetime
SET @Driver_Name = 'David Howard'
SET @Invoice_StartDate = '1/1/2000'
SET @Invoice_EndDate = '12/31/2100'
SELECT [tblTicketHeader].[iKey],
[tblTicketHeader].[iInvoice_Number],
[tblTicketHeader].[iTicket_Date],
[tblTicketHeader].[iDestination_1],
[tblTicketHeader].[iRate],
[tblTicketDetail].[tDriver_FullName],
SUM([tblTicketDetail].[tBill_QTY]) AS TotalUnits,
SUM([tblTicketDetail].[tBill_Total_Charge]) AS Total
FROM [tblTicketHeader]
LEFT JOIN [tblTicketDetail]
ON [tblTicketHeader].[iKey] = [tblTicketDetail].[tIKey]
WHERE [tblTicketHeader].[iTicket_Date] BETWEEN @Invoice_StartDate AND @Invoice_EndDate
AND [tblTicketHeader].[iPosted] = '1'
AND [tblTicketDetail].[tDriver_FullName] = @Driver_Name
GROUP BY [tblTicketHeader].[iInvoice_Number],
[tblTicketHeader].[iTicket_Date],
[tblTicketHeader].[iDestination_1],
[tblTicketHeader].[iRate],
[tblTicketDetail].[tDriver_FullName],
[tblTicketHeader].[iKey];
I will check out what you wrote, now. I just wanted to get the whole thing up here.
January 13, 2009 at 10:01 pm
I changed the between statement to what you have and it still produces no results.
Is it possible to have a WHERE clause across two tables? Am I trying to do something goofy? I don't understanding why it works when I remove the "AND [tblTicketDetail].[tDriver_FullName] = @Driver_Name."
January 13, 2009 at 11:34 pm
Some times there could be spaces in between and the name for the driver_Name in your source may not be matching. To avoid you can use Ltrim and Rtrim functions to trim spaces on the left and right.
Ltrim(Rtrim[tblTicketDetail].[tDriver_FullName])) = @Driver_Name
January 13, 2009 at 11:59 pm
IMO your variable is declared to small !! char(10) seems rather small.
Be sure to match you declared size to the size of the column in de table ddl.
DECLARE @Driver_Name varchar(10) --????
DECLARE @Invoice_StartDate datetime
DECLARE @Invoice_EndDate datetime
SELECT @Driver_Name = 'David Howard'
, @Invoice_StartDate = convert(datetime,'1/1/2000',101)
, @Invoice_EndDate = convert(datetime,'12/31/2100',101)
print @Driver_Name
go -- just to reset all defined variables.
DECLARE @Driver_Name varchar(100) --???? (changed 10 to 100)
DECLARE @Invoice_StartDate datetime
DECLARE @Invoice_EndDate datetime
SELECT @Driver_Name = 'David Howard'
, @Invoice_StartDate = convert(datetime,'1/1/2000',101)
, @Invoice_EndDate = convert(datetime,'12/31/2100',101)
SELECT TH.[iKey]
, TH.[iInvoice_Number]
, TH.[iTicket_Date]
, TH.[iDestination_1]
, TH.[iRate]
, TD.[tDriver_FullName]
, SUM(TD.[tBill_QTY]) AS TotalUnits
, SUM(TD.[tBill_Total_Charge]) AS Total
FROM [tblTicketHeader] TH
LEFT JOIN [tblTicketDetail] TD
ON TH.[iKey] = TD.[tIKey]
WHERE TH.[iTicket_Date] >= @Invoice_StartDate
AND TH.[iTicket_Date] <= @Invoice_EndDate
AND TH.[iPosted] = '1'
AND TD.[tDriver_FullName] = @Driver_Name
GROUP BY TH.[iInvoice_Number]
, TH.[iTicket_Date]
, TH.[iDestination_1]
, TH.[iRate]
, TD.[tDriver_FullName]
, TH.[iKey] ;
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
January 14, 2009 at 5:56 am
ALZDBA hit the nail on the head. You're setting the variable as a CHAR(10) and the very first parameter, 'David Howard', has 12 characters. So it's going to truncate the last two characters and try to match 'David Howar' to whatever is in your database.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2009 at 12:44 pm
Aaahhh, that is so stupid of me!!
Thank you very much. I couldn't see the forest from the trees. That was supposed to be 510, not 10.
Thank you very much! That solved the problem!
EDIT: Is there a way to mark this thread resolved or answered?
January 14, 2009 at 12:49 pm
Something that strikes me as possibly erroneous in this query is that it has a Left Join, but there's a Where clause on the second table of the join. That effectively makes it an Inner Join. You might want to look into the mechanics on that a bit more.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
January 14, 2009 at 1:03 pm
bdgenterprises (1/14/2009)
Aaahhh, that is so stupid of me!!Thank you very much. I couldn't see the forest from the trees. That was supposed to be 510, not 10.
Thank you very much! That solved the problem!
EDIT: Is there a way to mark this thread resolved or answered?
You just did. Thanks.
Be sure to follow up on what GSquared said.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
January 14, 2009 at 1:50 pm
Thank you for pointing that out to me. I am new to these more complex queries so I am still learning. I will fix that.
January 14, 2009 at 2:10 pm
GSquared (1/14/2009)
Something that strikes me as possibly erroneous in this query is that it has a Left Join, but there's a Where clause on the second table of the join.That effectively makes it an Inner Join.
Meaning the results will be the same as when using an inner join, the query will perform a left join !
Alter it to an inner join (at least for its documentation value because at some point in time, some one will ask this same question over and again)
You might want to look into the mechanics on that a bit more.
This is indeed a caveat !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply