April 5, 2013 at 12:10 pm
I know you guys are tired of hearing this and seeing this same question asked over and over (figured this out from reading your forums for days trying to find the answer). I'll apologize up front because I am new to SQL, having been thrown in during the middle of implementation of new software. But I AM trying to learn, just having to do so a lot faster than I'd ever have planned for myself !:w00t:
I have written the following query and would like for the results to give me ALL the rows that match my MAX Batch ID, but I am getting all the rows for all of the batches that belong to that customer.
Declare @actid VARCHAR(10)
SET @actid ='642584789T'
SELECT d.cust.id, rtrim(isnull(d.cust_ln,''))+', '+rtrim(isnull(d.cust_fn,'')) AS Name,
convert(char(10),d.ord_dt,101) as OrderDate, d.item_name, d.item_qty, d.rowno, d.bat_num,
(SELECT MAX(d.bat_num)
FROM ORDERS.dbo.tb_batch AS d
WHERE d.bat_num=e.bat_num) AS Batch
FROM ORDERS.dbo.tb_batch AS d left join ORDERS.dbo.tb._cust AS e ON d.bat_num=e.bat_num and d.rowno=e.rowno
WHERE d.acct_id = @actid
THE RESULTS I GET ARE:
Cust_id. . .Name. . . . .OrderDate. . . .item_name. . . .item_Qty. .rowno. . Batch
1. . . .Smith, Ed. . . .03/01/2013. . . Broom #3. . . . . . . . 4 . . . . .647. . . 2582
2. . . .Smith, Ed. . . .02/01/2013. . . Shovel #4. . . . . . . .6 . . . . .647. . . 2582
3. . . .Smith, Ed. . . .03/01/2013. . . Rake #2.. . . . .. . . 4 . . . . .256. . . 1645
4. . . .Smith, Ed. . . .02/01/2013. . . Shovel #4. . . . . . . .6 . . . . .256. . . 1645
I would like to see only the two rows for the MAX batch ID of 2582.
April 5, 2013 at 12:22 pm
The way you've got it written, you're just returning the max(bat_num) for every record you're getting back from your main query.
Try this instead:
Declare @actid VARCHAR(10)
SET @actid ='642584789T'
SELECTd.cust.id,
rtrim(isnull(d.cust_ln,''))+', '+rtrim(isnull(d.cust_fn,'')) AS Name,
convert(char(10),d.ord_dt,101) as OrderDate,
d.item_name,
d.item_qty,
d.rowno,
d.bat_num
FROM ORDERS.dbo.tb_batch AS d
left join ORDERS.dbo.tb._cust AS e
ON d.bat_num=e.bat_num
and d.rowno=e.rowno
WHERE d.acct_id = @actid
and d.bat_num = (SELECT MAX(d.bat_num)
FROM ORDERS.dbo.tb_batch)
April 5, 2013 at 12:24 pm
MtnMan92 (4/5/2013)
I know you guys are tired of hearing this and seeing this same question asked over and over (figured this out from reading your forums for days trying to find the answer). I'll apologize up front because I am new to SQL, having been thrown in during the middle of implementation of new software. But I AM trying to learn, just having to do so a lot faster than I'd ever have planned for myself !:w00t:I have written the following query and would like for the results to give me ALL the rows that match my MAX Batch ID, but I am getting all the rows for all of the batches that belong to that customer.
Declare @actid VARCHAR(10)
SET @actid ='642584789T'
SELECT d.cust.id, rtrim(isnull(d.cust_ln,''))+', '+rtrim(isnull(d.cust_fn,'')) AS Name,
convert(char(10),d.ord_dt,101) as OrderDate, d.item_name, d.item_qty, d.rowno, d.bat_num,
(SELECT MAX(d.bat_num)
FROM ORDERS.dbo.tb_batch AS d
WHERE d.bat_num=e.bat_num) AS Batch
FROM ORDERS.dbo.tb_batch AS d left join ORDERS.dbo.tb._cust AS e ON d.bat_num=e.bat_num and d.rowno=e.rowno
WHERE d.acct_id = @actid
THE RESULTS I GET ARE:
Cust_id. . .Name. . . . .OrderDate. . . .item_name. . . .item_Qty. .rowno. . Batch
1. . . .Smith, Ed. . . .03/01/2013. . . Broom #3. . . . . . . . 4 . . . . .647. . . 2582
2. . . .Smith, Ed. . . .02/01/2013. . . Shovel #4. . . . . . . .6 . . . . .647. . . 2582
3. . . .Smith, Ed. . . .03/01/2013. . . Rake #2.. . . . .. . . 4 . . . . .256. . . 1645
4. . . .Smith, Ed. . . .02/01/2013. . . Shovel #4. . . . . . . .6 . . . . .256. . . 1645
I would like to see only the two rows for the MAX batch ID of 2582.
It would help if you would provide us with the DDL (CREATE TABLE statement) for the table(s) involved, some sample data (as a series of INSERT INTO statements) for the table(s) involed, and your expected results based on the sample data.
For help with this please read the first article I reference below in my signature block regarding asking for help.
April 5, 2013 at 12:58 pm
Grasshopper, I'm only getting my columns headers now - no data. :crying:
April 5, 2013 at 1:02 pm
Lynn,
I apologize but I'm not creating any tables yet or inserting any data. Just running the query to be sure I pull in the data that I need.
This is probably not the best way to do it, but it's the rules I have to follow.
Once the query pulls correctly, then I can create the stored procedure and develop the search form for end users to do the lookup.
April 5, 2013 at 1:07 pm
MtnMan92 (4/5/2013)
Lynn,I apologize but I'm not creating any tables yet or inserting any data. Just running the query to be sure I pull in the data that I need.
This is probably not the best way to do it, but it's the rules I have to follow.
Once the query pulls correctly, then I can create the stored procedure and develop the search form for end users to do the lookup.
Again, read the first article I reference in my signature block.
You aren't going to get the help you need until you provide us with the DDL (the CREATE TABLE statements) for the tables involved, some sample data (as a series of INSERT INTO statements) for each of the tables, and the expected results you are looking for from your query based on the sample data.
You can get the DDL for the tables by right clicking on the table in the object explorer and the selecting script to clipboard then pasting that in a reply post on this thread.
The article will walk you through all of this.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply