Denny Cherry Jan 11, 2008   6:50 PM GMT

Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.

69,065 pointsBadges:

Sqldude Jan 29, 2008   10:29 PM GMT

Here's some pointers: You need only the summary counts, hence you have to filter the RECORDTYPE of table IV00102, list only the master records, thus where iv00102.rcrdtype = 1 Also, you can not derive your output from one single SQL command, you have to utilize a temp table. To accomplish your intended output, use this script: -- Declare Variables declare @itemnmbr varchar(20), @itemdesc varchar(50), @qtyonhnd int -- Create a temporary table to hold rows create table #temp ( itemnmbr varchar(20), itemdesc varchar(50), qtyonhnd int, sopnumbe varchar(50), invodate datetime ) -- Get all qty-on-hand where record type = Summary inventory count declare c cursor for select v.itemnmbr, v.itemdesc, q.qtyonhnd ..CUT.. open c fetch next from c into @itemnmbr, @itemdesc, @qtyonhnd while (@@fetch_status <> -1) begin -- Collect them into the temporary table insert into #temp (itemnmbr, itemdesc, qtyonhnd, sopnumbe, invodate) fetch next from c into @itemnmbr, @itemdesc, @qtyonhnd ..CUT.. end deallocate c -- Output the generated rows select * from #temp -- Clean up drop table #temp Script Source: http://www.gp-dynamics.com/dynamics-gp-command-details.asp?id=65 Title: Get inventory items with positive quantity-on-hand with latest corresponding invoice info

40 pointsBadges:

Sqldude Jan 29, 2008   10:34 PM GMT

Source Reference: Title: Get inventory items with positive quantity-on-hand with latest corresponding invoice info

40 pointsBadges:

Sqldude Jan 29, 2008   10:35 PM GMT

for some reason, the link won't work.. so here it is. http://www.gp-dynamics.com/dynamics-gp-command-details.asp?id=65 http://www.gp-dynamics.com/dynamics-gp-command-details.asp?id=65

40 pointsBadges:

Sqldude Jan 29, 2008   10:58 PM GMT

ok, last try. here's the link Dear admin: think you should have a way to edit a recent post?

40 pointsBadges: