Hi I am trying to loop through a dataset from an SQL query giving me new column with # of previous occurances of an item in another column.
is this able to do?
most databases have count() function
I am just not sure how to set it up never looped through yet.
you should probably do this through the query, not through the dataset
Show us your SQL query. Please see Wiki - how to post code on this forum.
select CONCAT((CONVERT(INT,[Requested Ship Date])+2),[SOP Number], [Customer PO],[Item])as Column1,[Requested Ship Date],[SOP Number], [Customer PO], [Customer],[CUSTITEMNMBR], [Item], [Qty Remaining to Ship],[Qty On Hand]
from [MAP].[dbo].[cv_qtyremainingtoship_custitemnmbr]
order by [Requested Ship Date] ASC
I am running a view in SQL.
I want to count the number of times [Item] is above in the data giving # of previous occurances of that item number.
You should be able to do this in SQL. Try this. (It should be close to working!)
SELECT
CONCAT(
(CONVERT(INT,[Requested Ship Date])+2),
[SOP Number],
[Customer PO],
[Item]
) AS Column1,
[Requested Ship Date],
[SOP Number],
[Customer PO],
[Customer],
[CUSTITEMNMBR],
[Item],
[Qty Remaining to Ship],
[Qty On Hand],
(SELECT
COUNT(Item)
FROM [MAP].[dbo].[cv_qtyremainingtoship_custitemnmbr] t2
WHERE t1.Item = t2.Item
AND t1.Item > t2.Item -- <<<< Error here. See Post #13.
) AS PrevOcc
FROM [MAP].[dbo].[cv_qtyremainingtoship_custitemnmbr] t1
ORDER BY [Requested Ship Date] ASC
Tips:
- Make your SQL readable. Use line breaks. Always add a space after commas (same as writing in English).
- Be consistent with your SQL keyword case - either uppercase or lowercase but don't mix them.
2 Likes
I would use count() as a Window Aggregate, like so:
SELECT
CONCAT(
(CONVERT(INT,[Requested Ship Date])+2),
[SOP Number],
[Customer PO],
[Item]
) AS Column1,
[Requested Ship Date],
[SOP Number],
[Customer PO],
[Customer],
[CUSTITEMNMBR],
[Item],
[Qty Remaining to Ship],
[Qty On Hand],
COUNT(*) OVER (PARTITION BY [Item] ORDER BY [Requested Ship Date] UNBOUNDED PRECEDING) AS PrevOcc
FROM [MAP].[dbo].[cv_qtyremainingtoship_custitemnmbr] t1
ORDER BY [Requested Ship Date]
So as we look at the data in excel want to look at each row or rows above and know if that item has already been called out. So line 1 would be 0 on PrecOcc but line 2 would be 1 time it was above. and if happens again would be 2 or so on throughout the data.
well this can never be true
OK. There's a problem with my query (which Phil's probably avoided).
AND t1.Item > t2.Item won't work as the Item column does not contain unique values. You need to compare on a column that does and is in order. Usually this is the Id column or equivalent. So it should be,
(SELECT COUNT(Item)
FROM custitemnmbr t2
WHERE t1.Item = t2.Item
AND t1.Id > t2.Id
) AS PrevOcc
(or whatever you can use instead of Id).
Have a look at this example created for you:
Yes.
Must be the same order as the outer query. The [Requested Ship Date].
Window Aggregates usually blow the doors off of subqueries or joins, performance-wise.
The Italian Job, 1969.
2 Likes
@pturmel I tired your code but got this error
Sorry, should be ROWS UNBOUNDED PRECEDING
Insert - 1 before AS
Yes sir thats what I did I was a little slow.
SELECT
[Requested Ship Date],
[SOP Number],
[Customer PO],
[Customer],
[CUSTITEMNMBR],
[Item],
[Qty Remaining to Ship],
[Qty On Hand],
(COUNT(*) OVER (PARTITION BY [Item]
ORDER BY [Requested Ship Date] ROWS UNBOUNDED PRECEDING) - 1) AS PrevOcc
FROM [MAP].[dbo].[cv_qtyremainingtoship_custitemnmbr] t1
ORDER BY [Requested Ship Date]