Please give me example DATA (from ALL tables) and desired result.
It is hard to me to read whole code and trying to understand your (or whoever wrote it) logic.
Also, I don't know what dbo.udf_TempoDecorridoSegundos() function does.
>Sure and thanks for your attention
>
>The result I want is something like the tblSales. First I calculate the DataBeginSales from the table historicoproduto.for each product.Next for each product I calculate the sum of sales from the table romaneio and update the cursor.
>
>I cannot figure out how to do that without using cursors, in a set-based way for better performance.
>
>
>
>declare @DateINi smalldatetime
>declare @TotSales Numeric(10,2)
>
>set @DateINi='20100218'
>
>select @dateini=dateadd(hh,(select datepart(hh,getdate())),cast(convert(CHAR(11),@dateini,113) as datetime))
>select @dateini=dateadd(mi,(select datepart(mi,getdate())),@dateini)
>select @dateini=dateadd(ss,(select datepart(ss,getdate())),@dateini)
>
>print @dateini
>
>select @TotSales=(select sum(valor) from romaneio r where tipo='VC' and caddate>=@dateini)
>
>declare SSales cursor for
> select e.id,max(caddate) as DataBeginSales from historicoproduto h right join estoquefisico e
>on h.prodid=e.id
> where ativo=1 and ( tipo IS NULL or tipo<>'Posição'
> or (tipo='Posição' and (h.historico like 'Colocação em Venda%' or h.historico like '%->Venda%'))) group by e.id
>
>DECLARE @tblSales TABLE (ProdID Char(10),Data smalldatetime,Sales Numeric(10,2))
>DECLARE @ProdID char(10)
>DECLARE @MaxSales smalldatetime
>DECLARE @Sales Numeric(10,2)
>
>OPEN SSales
>
>
>
>
>
>FETCH NEXT FROM SSales INTO @ProdID, @MaxSales
>
>
>
>WHILE @@FETCH_STATUS = 0
>
>BEGIN
> SET @Sales=@TotSales
> IF dbo.udf_TempoDecorridoSegundos(@MaxSales,getdate())<180000
> SET @Sales=(select sum(valor) from romaneio r where tipo='VC' and caddate>=@maxSales)
>
> INSERT INTO @tblSales values(@ProdID, @MaxSales,@Sales)
>
>
>
>
> FETCH NEXT FROM SSales INTO @ProdID, @MaxSales
>
>END
>
>
>
>
>
>CLOSE SSales
>
>DEALLOCATE SSales
>
>select * from @tblSales
>
>
>
>
Against Stupidity the Gods themselves Contend in Vain - Johann Christoph Friedrich von Schiller
The only thing normal about database guys is their tables.