August 24, 2009 at 8:41 am
I hope I word this correctly so it is not confusing. I have a stored procedure that is returning many rows group by a product SKU ID. In these returned rows, I also need a value that shows the min date and the max date being returned for each product. So if the query is returning ten rows for product XYZ and the first date on these ten rows is 8/12 and the max date is 8/19, I need just those dates returned in a variable, or along with the dataset (if this makes sense). And I need that for each group of SKU (and many SKUs can be returned in the dataset).
Thanks for the information for this newbie (newbie for more complicated stored procs, certainly not new to databases) 😉
August 24, 2009 at 3:33 pm
I think you should take the time to provide some SQL code to explain what you're looking for (including expected result). Please see the link in my signature on how to do it.
Based on your verbal description it's hard to come up with a non-verbal solution...
Maybe adding MIN(date_in_question) and MAX(date_in_question) to your select list together with a GROUP BY SKU will already solve the issue...
If not, please provide some sample data to work on.
August 25, 2009 at 8:42 am
Here is the SQL query:
declare @thisDate as datetime
declare @endDate as datetime
set @thisDate = dateadd(d, -8, getdate())
set @endDate = dateadd(d, 28, @thisdate)
SELECT DISTINCT pps.Date, shift.actual_shift, pps.Cases_Scheduled * product.quartile as Scheduled, cc.Case_Code, ci.CubeSize, DailyCC.Case_Count, ci.CartonsPerCase, ci.GluedPerSkid,
CartonInv.carton, CartonInv.glued, CartonInv.flat
FROM tblPackProdSchedules AS pps
INNER JOIN tblCaseCodes AS cc ON pps.uCaseCodeID = cc.uID
INNER JOIN tblCartonIndex AS ci ON cc.Case_Code = ci.CaseCode
INNER JOIN Product on cc.uProductID = Product.uid
INNER JOIN shift on pps.uShiftID = shift.uid
FULL OUTER JOIN
(SELECT [Date], uShiftID, Case_Code, SUM(Case_Count) AS Case_Count, SUM(Case_Sched) AS Case_Sched, Product
FROM dbo.vw_Daily_CaseCounts AS dcc
WHERE dcc.[date]>=@thisDate and dcc.[date]=@thisDate
) AS CartonInv
ON cc.Case_Code = CartonInv.Case_Code
WHERE pps.[date]>=@thisDate and pps.[date]<=@endDate
ORDER BY pps.[date], shift.actual_shift
Within the result set (which happens to be 200+ rows currently), I need to pull out the min([Date]) and max([Date]) for cc.Case_Code within that result set, but still returning the full result set.
By the way, I did not originally create this database. I would have NEVER named a field in the table, Date.
August 25, 2009 at 2:36 pm
Well, we're getting closer...
Next thing I'd like to see is a DDL statement (table definition) as well as sample data in a ready to use format as described in the link in my signature for the tables you're using in your join. You should also include your expected result that matches your sample data.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply