Am o problema ciudata, pe o baza de date care se afla in spatele unei aplicatii accesata de multi utilizatori, rulez un select care trebuie sa intoarca niste date, scriptul rulat ieri omite citeva inregistari peste care am dat complet intimplator. Inregistrarile nu lipsesc din datele intoarse in celelalte zile ale saptaminii.

------------------------------------------------------------------------------------------------------

declare

@dataini datetime,

@datafin datetime,

@SiteId int,

@filtruarticol int,

@clasificare int,

@lunacurenta int

set @dataini = '20080901'

set @datafin = '20080930'

set @SiteId = 0

set @filtruarticol = 0

set @clasificare = 471

set @lunacurenta =2

DECLARE @filtre int

select @filtre=0

declare @Hierarchy TABLE

(CategoryId int,

ParentId int,CategName varchar (255))

delete from ___vinzari_costuri

insert into ___vinzari_costuri (

SiteCode, Divizie, Echipa, Agent, DataFact, Scadenta, Partener, Factura, ValFactura, StareDoc,

CodProdus, Produs, UM, Cantit, Greutate, ConversionRate, Paleti, PretIntrare, pretlista, ValidFrom,

PretListaStandart, PretFTVA, TVA, Valoare, DiscLinie, CantitateCompusa, CodUMCompusa, tip_produs,

level1, level2, level3, SyntheticName, ValLstStndrt, ValDiscStd, DiscountDat, MovementSign)

select a.SiteCode,

E_Mail as Divizie ,

a.WorkPlace as Echipa,

a.Agent,

a.DocumentDate as DataFact,

a.Scadenta,

a.PartnerName as Partener,

a.Factura,

a.ValFactura,

a.StareDoc,

a.ItemCode as CodProdus,

a.ItemName as Produs,

a.MeasuringUnitCode as UM,

a.Qtty as Cantit,

dbo.GetKG2(a.ItemId, a.Qtty) as Greutate,

d.ConversionRate,

(a.Qtty/d.ConversionRate) as Paleti,

cast(a.pretintrare as decimal(10,2)) as PretIntrare,

a.pretlista,

pdl.ValidFrom,

pdl.Price as PretListaStandart ,

a.UnitPrice as PretFTVA,

a.VAT as TVA,

a.Amount as Valoare,

a.DiscountPercentInvDet as DiscLinie,

a.CantitateCompusa,

a.CodUMCompusa,

a.ItemTypeId as tip_produs ,

b.level1,

b.level2,

b.level3,

c.SyntheticName,

isnull(cast((pdl.Price * a.Qtty) as decimal(10,2)),0) as ValLstStndrt,

isnull(cast(((pdl.Price * a.Qtty) - a.Amount) as decimal(10,2)),0)as ValDiscStd,

isnull(cast ((((pdl.Price * a.Qtty) - a.Amount)/(pdl.Price * a.Qtty)) as decimal(10,4)),0) as DiscountDat,

'Vinzari' as MovementSign

from __total_vanzari a left outer join __ierarhii_articole b on a.ItemId=b.ItemId and b.ItemClassificationId=@clasificare

left join PartnerAddress c on a.DeliveryAddressId = c.PartnerAddressId and a.SiteId = c.SiteId

left join (select * from PriceListDetail where PriceListId = 179) pdl on a.ItemId = pdl.ItemId and a.MeasuringUnitId = pdl.MeasuringUnitId and (ValidFrom <= a.DocumentDate) AND ( (ValidTo IS NULL) OR (ValidTo > a.DocumentDate))

left join __produse_paleti d on a.ItemId = d.ItemId and a.MeasuringUnitId = d.BaseMeasuringUnitId

where a.DocumentDate>=@dataini and a.DocumentDate<=@datafin and a.StareDoc<>'A'

and (isnull(@SiteId,-1)=-1 or a.SiteId = @SiteId)

and (@filtre=0 or a.ItemId in (select ItemId from @Hierarchy H left join repItemXCategory IC on H.CategoryId = IC.ItemCategoryId and IC.ItemId = a.ItemId))

insert into ___vinzari_costuri (

SiteCode, Divizie, Echipa, Agent, DataFact, Scadenta, Partener, Factura, ValFactura, StareDoc,

CodProdus, Produs, UM, Cantit, Greutate, ConversionRate, Paleti, PretIntrare, pretlista, ValidFrom,

PretListaStandart, PretFTVA, TVA, Valoare, DiscLinie, CantitateCompusa, CodUMCompusa, tip_produs,

level1, level2, level3, SyntheticName, ValLstStndrt, ValDiscStd, DiscountDat, MovementSign)

select a.SiteCode,

'Fara Divizie' as Divizie,

'Fara Echipa' as Echipa,

a.Agent,

a.DocumentDate as DataFact,

a.Scadenta,

a.PartnerName as Partener,

a.Factura,

a.ValFactura,

a.StareDoc,

a.ItemCode as CodProdus,

a.ItemName as Produs,

a.MeasuringUnitCode as UM,

a.Qtty as Cantit,

dbo.GetKG2(a.ItemId, a.Qtty) as Greutate,

d.ConversionRate,

(a.Qtty/d.ConversionRate) as Paleti,

a.pretintrare as PretIntrare,

pld.Price as PretListaStandart ,

pld.ValidFrom,

pld.Price as PretListaStandart ,

a.UnitPrice as PretFTVA,

a.VAT as TVA,

a.Amount as Valoare,

a.DiscountPercentInvDet as DiscLinie,

a.CantitateCompusa,

a.CodUMCompusa,

a.ItemTypeId as TipProdus,

b.level1,

b.level2,

b.level3,

StockAdminName,

isnull(cast((pld.Price * a.Qtty) as decimal(10,2)),0) as ValLstStndrt,

0 as ValDiscStd,

0 as DiscountDat,

'Intrari' as MovementSign

from __total_intrari a

left outer join __ierarhii_articole b on a.ItemId=b.ItemId and b.ItemClassificationId=@clasificare

left join (select ItemId, Price, MeasuringUnitId, ValidFrom from PriceListDetail where ValidTo is null and PriceListId = 179) pld on a.ItemId = pld.ItemId and a.MeasuringUnitId = pld.MeasuringUnitId

left join __produse_paleti d on a.ItemId = d.ItemId and a.MeasuringUnitId = d.BaseMeasuringUnitId

where a.DocumentDate>=@dataini and a.DocumentDate<=@datafin and a.StareDoc<>'A'

and (isnull(@SiteId,-1)=-1 or a.SiteId = @SiteId)

and (@filtruarticol=0 or a.ItemId in (select ItemId from @Hierarchy H left join repItemXCategory IC on H.CategoryId = IC.ItemCategoryId and IC.ItemId = a.ItemId) )

)