October 26, 2007 at 8:34 am
Sorry, I reformatted this so that I could follow it, but what does the following code return in the col field?
Sample results please?
SELECT
[row] = ItemNo
,[col] = YearWeekNo
,[Data] = SUM(tblItem_Trx.QtyOnHand + tblItem_Trx.QtyOrd - tblItem_Trx.QtyAlloc)
FROM
tblItem_BaseInfo
INNER JOIN tblItem_Trx
ON tblItem_BaseInfo.Item_No = tblItem_Trx.ItemNo
INNER JOIN tblSys_Calendar
ON tblItem_Trx.InvDate = tblSys_Calendar.DateMMDDYY
INNER JOIN tblItem_AvailType
ON tblItem_Trx.Loc = tblItem_AvailType.Loc
INNER JOIN tblItem_AvailType_XRef
ON tblItem_AvailType.AvailTypeCd = tblItem_AvailType_XRef.AvailTypeCd
AND tblItem_BaseInfo.ItemClassCd = tblItem_AvailType_XRef.ItemClassCd
WHERE
YearWeekNo BETWEEN '200743' AND '200744'
AND tblItem_AvailType.AvailTypeCd = 'AVS'
GROUP BY ItemNo, YearWeekNo
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgOctober 26, 2007 at 9:12 am
Because I'm guessing your issue is here.... [ & 'Week1' & ]
SELECT
[Item] = CASE WHEN [row] IS NULL THEN 'Totals' ELSE CONVERT(VARCHAR(80), [row]) END
,[Week1] = SUM(CASE col WHEN 'Week1' THEN data ELSE 0 END)
,[Week2] = SUM(CASE col WHEN 'Week2' THEN data ELSE 0 END)
,[Week3] = SUM(CASE col WHEN 'Week3' THEN data ELSE 0 END)
,[Total] = SUM(data)
FROM
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgViewing 2 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply