October 26, 2007 at 8:24 am
I'm trying to get this code to populate a Gridview in ASP.net. It will be used to populate a up-to-date availability listing. If I can get the syntax right, i can then try to start passing variables to it.
Help??
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 (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)
GROUP BY [row]
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 3 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply