Re: Dar karta T-SQL select'as [Dream-Colored Bunny @ 2011-08-12 14:41:28]

Tema: Re: Dar karta T-SQL select'as
Autorius: Dream-Colored Bunny
Data: 2011-08-12 14:41:28
Pasitaisau shiek tiek, turetu buti:
>      (INV1.datephysical >='2011.07.01' or INV1.datephysical IS NULL)  and
>      (INV1.datephysical <='2011.07.31' or INV1.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)

-- 

=^_^=


"Dream-Colored Bunny" <cream@lemon.nospam> wrote in message news:j23364$b0h$1@trimpas.omnitel.net...
> Na...
> Visu pirma gali sutrumpinti:
>>      CASE WHEN INV1.ITEMID is Null then LIK1.ITEMID else INV1.ITEMID END,
> i
> ISNULL(INV1.ITEMID,LIK1.ITEMID)
> 
> O antra didele problema yra chia
>>      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'
> Taigi pridek atitinkamas salygas ish WHERE prie ON cia:
>>      FULL OUTER JOIN DBO.UTINVENTREMAINMONTH LIK1 ON INVD.INVENTDIMID = 
>> LIK1.INVENTDIMID
> arba perrashyk mazhdaug taip:
>      WHERE
>      INVD.INVENTLOCATIONID = 'its'     and
>      isnull(INV1.datephysical,LIK1.DATEPHYSICAL) >='2011.07.01'  and
>      isnull(INV1.datephysical,LIK1.DATEPHYSICAL) <='2011.07.31'
> arba taip:
>      WHERE
>      INVD.INVENTLOCATIONID = 'its'     and
>      (INV1.datephysical >='2011.07.01' or INV1.datephysical IS NULL)  and
>      (INV1.datephysical <='2011.07.31' or INV1.datephysical IS NULL)  and
>      (LIK1.DATEPHYSICAL >= '2011.07.01' or INV1.datephysical IS NULL) and
>      (LIK1.DATEPHYSICAL <= '2011.07.31' or INV1.datephysical IS NULL)
> ar panashiai, priklausomai nuo duomenu.
> Turek omenyje, kad jei darai full outer join ir vienoj lentoje duomenu nera, tai is tos lentos duomenis gauni kaip NULL'us.
> Bet koks lyginimas su NULL grazhins tau FALSE (pagal nutylejima).
> 
> -- 
> 
> =^_^=
> 
> 
> "Rimokas" <rimasu@ut.lt> wrote in message news:j22u4k$4bl$1@trimpas.omnitel.net...
>>> 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
>> 
>> 
>> 
>>