Re: Dar karta T-SQL select'as [Rimokas @ 2011-08-12 16:17:48]

Tema: Re: Dar karta T-SQL select'as
Autorius: Rimokas
Data: 2011-08-12 16:17:48
Pasitaisau : su isnull viskas gerai eina .

Selectas :

ALTER PROCEDURE [dbo].[Inventtrans_AV_CREATING] AS
begin transaction
      if OBJECT_ID(N'INVENTTRANS_AV',N'U') IS NOT NULL
         DROP TABLE DBO.INVENTTRANS_AV
         PRINT 'DROP PABAIGA'

      SELECT
      INVD.INVENTLOCATIONID,
      ISNULL( JUD1.ITEMID, LIK1.ITEMID ) AS ITEMID,
      ISNULL( INVT1.ITEMNAME, INVT2.ITEMNAME ) AS ITEMNAME,
      ISNULL( INVT1.ALNINVENTGROUP1, INVT2.ALNINVENTGROUP1 ) AS ALNGROUP,

      SUM ( isnull( LIK1.QTY, 0 ) ) AS LQTY,
      SUM ( isnull( LIK1.COSTAMOUNTPOSTED, 0 ) )AS LSUM,

      SUM ( CASE WHEN JUD1.DIRECTION = 1 then JUD1.QTY else 0 END ) AS PQTY,
      SUM ( CASE WHEN JUD1.DIRECTION = 1 then JUD1.COSTAMOUNTPOSTED else 0 
END ) AS PSUM,
      SUM ( CASE WHEN JUD1.DIRECTION = 2 then JUD1.QTY else 0 END ) AS IQTY,
      SUM ( CASE WHEN JUD1.DIRECTION = 2 then JUD1.COSTAMOUNTPOSTED else 0 
END ) AS ISUM,

      INVD.INVENTDIMID,
      INVD.CONFIGID,
      INVD.INVENTBATCHID,
      INVD.INVENTSERIALId,
      isnull( JUD1.DATEPHYSICAL, LIK1.DATEPHYSICAL ) AS DATEPHYSICAL

      INTO DBO.INVENTTRANS_AV
      FROM DBO.INVENTDIM INVD

      FULL JOIN DBO.INVENTTrans JUD1 ON INVD.INVENTDIMID = JUD1.INVENTDIMID
      FULL JOIN DBO.UTINVENTREMAINMONTH LIK1 ON INVD.INVENTDIMID = 
LIK1.INVENTDIMID

      JOIN DBO.INVENTTABLE INVT1 ON JUD1.ITEMID = INVT1.ITEMID
      JOIN DBO.INVENTTABLE INVT2 ON LIK1.ITEMID = INVT2.ITEMID

      WHERE
      INVD.INVENTLOCATIONID = 'its'     and
      isnull( JUD1.DATEPHYSICAL, LIK1.DATEPHYSICAL ) >= '2011.07.01' and
      isnull( JUD1.DATEPHYSICAL, LIK1.DATEPHYSICAL ) <= '2011.07.01'

      GROUP BY
      INVD.INVENTLOCATIONID,
      isnull( JUD1.ITEMID, LIK1.ITEMID ),
      isnull( INVT1.ITEMNAME, INVT2.ITEMNAME ),
      isnull( INVT1.ALNINVENTGROUP1, INVT2.ALNINVENTGROUP1 ),
      isnull( JUD1.DATEPHYSICAL, LIK1.DATEPHYSICAL ),
      INVD.CONFIGID,
      INVD.INVENTBATCHID,
      INVD.INVENTSERIALID,
      INVD.INVENTDIMID

COMMIT TRANSACTION

      CREATE INDEX INVENTLOCATIONIDIDX
ON DBO.INVENTTRANS_AV 
(INVENTLOCATIONID,ALNGROUP,ITEMNAME,ITEMID,DATEPHYSICAL)


 Bet .... :-((

Su siom where salygom :
      (JUD1.datephysical >='2011.07.01' or JUD1.datephysical IS NULL)  and
      (JUD1.datephysical <='2011.07.31' or JUD1.datephysical IS NULL)  and
      (LIK1.DATEPHYSICAL >= '2011.07.01' or LIK1.datephysical IS NULL) and
      (LIK1.DATEPHYSICAL <= '2011.07.31' or LIK1.datephysical IS NULL)
arba
      isnull( JUD1.DATEPHYSICAL, LIK1.DATEPHYSICAL ) >= '2011.07.01' and
      isnull( JUD1.DATEPHYSICAL, LIK1.DATEPHYSICAL ) <= '2011.07.01'

 0 eiluciu ...

su laikotarpiais prie join'u :

      FULL JOIN DBO.INVENTTrans JUD1 ON INVD.INVENTDIMID = JUD1.INVENTDIMID
      and JUD1.datephysical >='2011.07.01' and JUD1.datephysical 
<='2011.07.31'
      FULL JOIN DBO.UTINVENTREMAINMONTH LIK1 ON INVD.INVENTDIMID = 
LIK1.INVENTDIMID
      and LIK1.DATEPHYSICAL >= '2011.07.01' and LIK1.DATEPHYSICAL <= 
'2011.07.31'

  tas pats rezultatas kaip ir ankstesniuos selectuos ... blogas . 18- ir 19- 
grupiu prekiu nera ...