Re: Dar karta T-SQL select'as [Rimokas @ 2011-08-12 13:09:24]

Tema: Re: Dar karta T-SQL select'as
Autorius: Rimokas
Data: 2011-08-12 13:09:24
> Greiciausiai taip butu buve geriausiai daryti nuo pradziu :) butum jau 
> senai padares - apie tokia lenta mes ne nezinojom su Bunny :). Tada 
> tiesiog left/right outer joinais tik dajungi kitas lentas ir viskas ;)
>
> P.S. Vakar kai perskaiciau sitai:
>
>> Beda ta , kad paprastai L lentelej nera tokiu irasu , kuriu yra LK . Ir 
>> butent ju nematau .
>
> Tai biski isspaude sypsena ;) Na bet visko buna, gyveni ir mokaisi.

   Prispazinsiu , kad ant sudetingu SQl uzklausu esu zalias . Kol kas teko 
daryt tik elementarius selectus . Ant MS SQl norim padaryt Data WareHouse .
   Bandziau kaip ir rasiau , vistiek negaunu ko noriu . Busiu dekingas , 
jeigu nurodysit , ka blogai darau ...

GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
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,
      CASE WHEN INV1.ITEMID is Null then LIK1.ITEMID else INV1.ITEMID END AS 
ITEMID,
      CASE WHEN INVT1.ITEMNAME is null then INVT2.ITEMNAME else 
INVT1.ITEMNAME END AS ITEMNAME,
      CASE WHEN INVT1.ALNINVENTGROUP1 is null then INVT2.ALNINVENTGROUP1 
else INVT1.ALNINVENTGROUP1 END AS ALNGROUP,

      SUM ( CASE WHEN LIK1.QTY is Null then 0 else LIK1.QTY END ) AS LQTY,
      SUM ( CASE WHEN LIK1.COSTAMOUNTPOSTED is Null then 0 else 
LIK1.COSTAMOUNTPOSTED END ) AS LSUM,

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

      INVD.INVENTDIMID,
      INVD.CONFIGID,
      INVD.INVENTBATCHID,
      INVD.INVENTSERIALId,
      CASE WHEN INV1.DATEPHYSICAL is null THEN LIK1.DATEPHYSICAL else 
INV1.DATEPHYSICAL end AS DATEPHYSICAL

      INTO DBO.INVENTTRANS_AV
      FROM DBO.INVENTDIM INVD

      FULL OUTER JOIN DBO.INVENTTrans INV1 ON INVD.INVENTDIMID = 
INV1.INVENTDIMID
      FULL OUTER JOIN DBO.UTINVENTREMAINMONTH LIK1 ON INVD.INVENTDIMID = 
LIK1.INVENTDIMID

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

      WHERE
      INVD.INVENTLOCATIONID = 'its'     and
      INV1.datephysical >='2011.07.01'  and
      INV1.datephysical <='2011.07.31'  and
      LIK1.DATEPHYSICAL >= '2011.07.01' and
      LIK1.DATEPHYSICAL <= '2011.07.31'

      GROUP BY
      INVD.INVENTLOCATIONID,
      CASE WHEN INV1.ITEMID is Null then LIK1.ITEMID else INV1.ITEMID END,
      CASE WHEN INVT1.ITEMNAME is null then INVT2.ITEMNAME else 
INVT1.ITEMNAME END,
      CASE WHEN INVT1.ALNINVENTGROUP1 is null then INVT2.ALNINVENTGROUP1 
else INVT1.ALNINVENTGROUP1 END,
      CASE WHEN INV1.DATEPHYSICAL is null THEN LIK1.DATEPHYSICAL else 
INV1.DATEPHYSICAL END,
      INVD.CONFIGID,
      INVD.INVENTBATCHID,
      INVD.INVENTSERIALID,
      INVD.INVENTDIMID

COMMIT TRANSACTION

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

   "18-" ir "19-" grupes item'u nera suvestinej , sios grupes nejudejo , tik 
likuciuose . Butent ju ir nematau .  Bandziau visaip : ir prie INVD jungti 
INV1 , o tada prie INV1 jungt LIK1 ir visaip kitaip . Visu tipu join'us - 
FULL , LEFT , RIGHT ... Niekaip nepasirodo 18- ir 19- grupes prekes . Kaip 
matot InventDimId yra vienas ir tas pats identifikatorius "00002576_D" . ITS 
skyrius , be jokiu papildomu pozymiu .

   Dekui , is anksto , uz pagalba ! ....

Likuciu duomenys :

      ItemId InventDimId InventLocationId Qty DatePhysical CostAmountPosted
      18-1-1-1 00002576_D ITS 2 2011.07.01 241,8
      18-2-1-1 00002576_D ITS 4 2011.07.01 0,04
      18-2-1-2 00002576_D ITS 1 2011.07.01 0,02
      18-2-1-3 00002576_D ITS 19 2011.07.01 0,57
      19-2-2-1 00002576_D ITS 1 2011.07.01 55
      19-2-2-6 00002576_D ITS 2 2011.07.01 9
      19-2-5-1 00002576_D ITS 2 2011.07.01 231,57
      20-1-1-10 00002576_D ITS 1 2011.07.01 46
      20-1-1-100 00002576_D ITS 4 2011.07.01 122
      20-1-1-102 00002576_D ITS 1 2011.07.01 41,42
      20-1-1-103 00002576_D ITS 1 2011.07.01 6,61
      20-1-1-11 00002576_D ITS 1 2011.07.01 30
      20-1-1-12 00002576_D ITS 1 2011.07.01 53
      20-1-1-14 00002576_D ITS 1 2011.07.01 24,79
      20-1-1-15 00002576_D ITS 2 2011.07.01
      20-1-1-19 00002576_D ITS 1 2011.07.01 78,23
      20-1-1-2 00002576_D ITS 4 2011.07.01 36,03
      20-1-1-21 00002576_D ITS 3 2011.07.01 231,14
      20-1-1-22 00002576_D ITS 1 2011.07.01 94,21
      20-1-1-23 00002576_D ITS 1 2011.07.01 99,17
      20-1-1-24 00002576_D ITS 2 2011.07.01 175,2
      20-1-1-3 00002576_D ITS 37 2011.07.01 44,06
      20-1-1-35 00002576_D ITS 1 2011.07.01 30
      20-1-1-4 00002576_D ITS 1 2011.07.01
      20-1-1-5 00002576_D ITS 8 2011.07.01 74,38
      20-1-1-58 00002576_D ITS 2 2011.07.01 26,8
      20-1-1-59 00002576_D ITS 5 2011.07.01 12,7
      20-1-1-6 00002576_D ITS 10 2011.07.01 66,95
      20-1-1-60 00002576_D ITS 1 2011.07.01 14,05
      20-1-1-65 00002576_D ITS 4 2011.07.01 52,02
      20-1-1-68 00002576_D ITS 7 2011.07.01 9,18
      20-1-1-71 00002576_D ITS 4 2011.07.01 74,08
      20-1-1-83 00002576_D ITS 1 2011.07.01 60
      20-1-1-9 00002576_D ITS 2 2011.07.01 13,56

Judejimo duomenys :
      ITEMID DATEPHYSICAL QTY COSTAMOUNTPOSTED DIRECTION INVENTDIMID
      20-1-1-24 2011.07.13 1 88,43 1                     00002576_D
      20-1-1-21 2011.07.13 1 95,87 1                     00002576_D
      20-1-1-3 2011.07.13 10 7 1                     00002576_D
      20-1-1-44 2011.07.26 4 0 1                     00002576_D
      20-1-1-43 2011.07.26 -21 0 2                     00002576_D
      20-1-1-44 2011.07.26 -4 0 2                     00002576_D
      20-1-1-83 2011.07.31 -1 -60 2                     00002576_D
      20-1-1-36 2011.07.31 -1 -30 2                     00002576_D
      20-1-1-36 2011.07.31 -1 -30 2                     00002576_D
      20-1-1-100 2011.07.31 -1 -30,29 2                     00002576_D
      20-1-1-51 2011.07.31 -1 -99,17 2                     00002576_D
      20-1-1-36 2011.07.31 -1 -30 2                     00002576_D
      20-1-1-100 2011.07.11 3 90 1                     00002576_D
      20-1-1-36 2011.07.11 6 180 1                     00002576_D
      20-1-1-11 2011.07.11 1 30 1                     00002576_D
      11-10-1-1 2011.07.19 100 0 1                     00002576_D
      11-10-1-1 2011.07.19 -100 0 2                     00002576_D
      11-10-1-2 2011.07.19 -80 0 2                     00002576_D
      11-10-1-2 2011.07.19 80 0 1                     00002576_D
      20-1-1-43 2011.07.26 11 0 1                     00002576_D
      20-1-1-43 2011.07.26 10 0 1                     00002576_D
      20-1-1-51 2011.07.13 1 99,17 1                     00002576_D
      20-1-1-22 2011.07.13 1 94,21 1                     00002576_D
      20-1-1-124 2011.07.14 1 180 1                     00002576_D
      20-1-1-100 2011.07.31 -1 -30,29 2                     00002576_D
      20-1-1-36 2011.07.31 -1 -30 2                     00002576_D
      20-1-1-100 2011.07.31 -1 -30,28 2                     00002576_D
      20-1-1-100 2011.07.31 -1 -30,29 2                     00002576_D
      20-1-1-35 2011.07.31 -1 -30 2                     00002576_D
      20-1-1-24 2011.07.31 -1 -87,88 2                     00002576_D
      20-1-1-100 2011.07.31 -1 -30,29 2                     00002576_D
      20-1-1-100 2011.07.31 -1 -30,28 2                     00002576_D