<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META content="text/html; charset=windows-1257" http-equiv=Content-Type> <META name=GENERATOR content="MSHTML 9.00.8112.16430"> <STYLE></STYLE> </HEAD> <BODY> <DIV><FONT size=2 face=Arial>> if OBJECT_ID(N'INVENTTRANS_AV',N'U') IS NOT NULL<BR>> DROP TABLE DBO.INVENTTRANS_AV<BR>> PRINT 'DROP PABAIGA'<BR>> </FONT></DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial><STRONG>1.) SELECT INTO galima pakeist i INSERT INTO (blabla) SELECT FROM </STRONG></FONT></DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial>Tada nereiks dropint lentos pastoviai, o tik vis ja pravalyti pries operacija su DELETE FROM LENTA; </FONT></DIV> <DIV><FONT size=2 face=Arial> </FONT></DIV> <DIV><FONT size=2 face=Arial>P.s. toks stilius labai primena man vieno veikejo stiliu, kuris pastoviai pridirbdavo bedarydamas ir paskui ji po to taisyt reikedavo viska...</FONT></DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial><STRONG>2.) Uzklausa sunkiai skaitosi be aliasu... </STRONG></FONT></DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial><STRONG>3.) Matau cia darai kruvos lauku atranka pagal join, kad imti jei reiksme NULL is kitos lentos ta reiksme, cia tau gerai tiktu kad viskas tvarkingiau atrodytu sukurti view</STRONG></FONT></DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial>CREATE VIEW PRE_DATA AS</FONT></DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial>ir cia kishi selecta su atrinkimu, o WHERE palieki tik kad neimtu visai nereikalingu duomenu, pvz istrintu irasu jei sistemoje yra istrynimo pozymis.</FONT></DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial>CREATE VIEW PRE_DATA AS</FONT></DIV> <DIV><FONT face=Arial><FONT size=1><EM><FONT face="Times New Roman">SELECT<BR>INVD.INVENTLOCATIONID,<BR>CASE WHEN INV1.ITEMID is Null then LIK1.ITEMID else INV1.ITEMID END AS <BR>ITEMID,<BR>CASE WHEN INVT1.ITEMNAME is null then INVT2.ITEMNAME else <BR>INVT1.ITEMNAME END AS ITEMNAME,<BR>CASE WHEN INVT1.ALNINVENTGROUP1 is null then INVT2.ALNINVENTGROUP1 <BR>else INVT1.ALNINVENTGROUP1 END AS ALNGROUP,<BR><BR>SUM ( CASE WHEN LIK1.QTY is Null then 0 else LIK1.QTY END ) AS LQTY,<BR>SUM ( CASE WHEN LIK1.COSTAMOUNTPOSTED is Null then 0 else <BR>LIK1.COSTAMOUNTPOSTED END ) AS LSUM,<BR><BR>SUM ( CASE WHEN INV1.DIRECTION = 1 then INV1.QTY else 0 END ) AS PQTY,<BR>SUM ( CASE WHEN INV1.DIRECTION = 1 then INV1.COSTAMOUNTPOSTED else 0 <BR>END ) AS PSUM,<BR>SUM ( CASE WHEN INV1.DIRECTION = 2 then INV1.QTY else 0 END ) AS IQTY,<BR>SUM ( CASE WHEN INV1.DIRECTION = 2 then INV1.COSTAMOUNTPOSTED else 0 <BR>END ) AS ISUM,<BR><BR>INVD.INVENTDIMID,<BR>INVD.CONFIGID,<BR>INVD.INVENTBATCHID,<BR>INVD.INVENTSERIALId,<BR>CASE WHEN INV1.DATEPHYSICAL is null THEN LIK1.DATEPHYSICAL else <BR>INV1.DATEPHYSICAL end AS DATEPHYSICAL<BR><BR>INTO DBO.INVENTTRANS_AV<BR>FROM DBO.INVENTDIM INVD<BR><BR>FULL OUTER JOIN DBO.INVENTTrans INV1 ON INVD.INVENTDIMID = <BR>INV1.INVENTDIMID<BR>FULL OUTER JOIN DBO.UTINVENTREMAINMONTH LIK1 ON INVD.INVENTDIMID = <BR>LIK1.INVENTDIMID<BR><BR>JOIN DBO.INVENTTABLE INVT1 ON INV1.ITEMID = INVT1.ITEMID<BR>JOIN DBO.INVENTTABLE INVT2 ON LIK1.ITEMID = INVT2.ITEMID<BR><BR><FONT color=#ff0000>WHERE<BR>INVD.INVENTLOCATIONID = 'its' and<BR>INV1.datephysical >='2011.07.01' and<BR>INV1.datephysical <='2011.07.31' and<BR>LIK1.DATEPHYSICAL >= '2011.07.01' and<BR>LIK1.DATEPHYSICAL <= '2011.07.31'<BR></FONT><BR>GROUP BY<BR>INVD.INVENTLOCATIONID,<BR>CASE WHEN INV1.ITEMID is Null then LIK1.ITEMID else INV1.ITEMID END,<BR>CASE WHEN INVT1.ITEMNAME is null then INVT2.ITEMNAME else <BR>INVT1.ITEMNAME END,<BR>CASE WHEN INVT1.ALNINVENTGROUP1 is null then INVT2.ALNINVENTGROUP1 <BR>else INVT1.ALNINVENTGROUP1 END,<BR>CASE WHEN INV1.DATEPHYSICAL is null THEN LIK1.DATEPHYSICAL else <BR>INV1.DATEPHYSICAL END,<BR>INVD.CONFIGID,<BR>INVD.INVENTBATCHID,<BR>INVD.INVENTSERIALID,<BR>INVD.INVENTDIMID</FONT><BR></EM></FONT></FONT></DIV> <DIV><FONT size=2 face=Arial>ISMETI WHERE LAUK!</FONT></DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial>Tada selectini view kuriame jau viskas atrinkta ar imt is vienur ar kitur ir su juo darai kazka toliau, nes matau stygius patirties ir del to per daug viskas susipainioja kai kazkas sudetingiau daroma. Del to geriau sukapoti i atskiras logines dalis. View leciau dirbs bet bent susigaudysi kas vyksta..</FONT></DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial><STRONG>4. Del datu filtro </STRONG></FONT></DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial><FONT size=3 face="Times New Roman">>> (INV1.datephysical >='2011.07.01' or INV1.datephysical IS NULL) and<BR>>> (INV1.datephysical <='2011.07.31' or INV1.datephysical IS NULL) and<BR>>> (LIK1.DATEPHYSICAL >= '2011.07.01' or LIK1.datephysical IS NULL) and<BR>>> (LIK1.DATEPHYSICAL <= '2011.07.31' or LIK1.datephysical IS NULL)</FONT><BR></FONT></DIV> <DIV><FONT size=2 face=Arial>Darant view kuriame graziai isrinktum kurios lentos laukus naudot, tipo IsNull (lentaX.laukasA, lentaY.LaukasA) tai poto nereiketu sito... bet jei cia taisyt sia uzklausa tai cia butu kazkas tokio:</FONT></DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial>((<FONT size=3 face="Times New Roman">INV1.datephysical Is Not Null And LIK1.DATEPHYSICAL IS NULL AND ( INV1.datephysical BETWEEN '2011.07.01' AND '2011.07.31') </FONT></FONT></DIV> <DIV><FONT size=2 face=Arial><FONT size=3 face="Times New Roman">Or</FONT></FONT></DIV> <DIV><FONT size=2 face=Arial>(<FONT size=3 face="Times New Roman">LIK1</FONT></FONT>.datephysical Is Not Null And INV1.DATEPHYSICAL IS NULL AND ( LIK1.datephysical BETWEEN '2011.07.01' AND '2011.07.31'))</DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial>Tada rinks priklausomai kuris laukas bus ne NULL, taciau jei abu bus NULL bus blogai, tam reik dadet dar sita:</FONT></DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial>((<FONT size=3 face="Times New Roman">INV1.datephysical Is Not Null And LIK1.DATEPHYSICAL IS NULL AND ( INV1.datephysical BETWEEN '2011.07.01' AND '2011.07.31') </FONT></FONT></DIV> <DIV><FONT size=2 face=Arial><FONT size=3 face="Times New Roman">Or</FONT></FONT></DIV> <DIV><FONT size=2 face=Arial>(<FONT size=3 face="Times New Roman">LIK1</FONT></FONT>.datephysical Is Not Null And INV1.DATEPHYSICAL IS NULL AND ( LIK1.datephysical BETWEEN '2011.07.01' AND '2011.07.31'))</DIV> <DIV><FONT size=2 face=Arial>AND</FONT></DIV> <DIV> INV1.datephysical Is Not Null OR LIK1.datephysical Is Not Null</DIV> <DIV> </DIV> <DIV><FONT size=2 face=Arial>CIa siaip elementari logika su buline algebra...</FONT></DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial>P.S. </FONT></DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial>Gal freelancerio reikia? padeciau su tokiais query :) </FONT></DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial></FONT> </DIV> <DIV><FONT size=2 face=Arial>"Rimokas" <</FONT><A href="mailto:rimasu@ut.lt"><FONT size=2 face=Arial>rimasu@ut.lt</FONT></A><FONT size=2 face=Arial>> wrote in message </FONT><A href="news:j22u4k$4bl$1@trimpas.omnitel.net"><FONT size=2 face=Arial>news:j22u4k$4bl$1@trimpas.omnitel.net</FONT></A><FONT size=2 face=Arial>...</FONT></DIV><FONT size=2 face=Arial>>> Greiciausiai taip butu buve geriausiai daryti nuo pradziu :) butum jau <BR>>> senai padares - apie tokia lenta mes ne nezinojom su Bunny :). Tada <BR>>> tiesiog left/right outer joinais tik dajungi kitas lentas ir viskas ;)<BR>>><BR>>> P.S. Vakar kai perskaiciau sitai:<BR>>><BR>>>> Beda ta , kad paprastai L lentelej nera tokiu irasu , kuriu yra LK . Ir <BR>>>> butent ju nematau .<BR>>><BR>>> Tai biski isspaude sypsena ;) Na bet visko buna, gyveni ir mokaisi.<BR>> <BR>> Prispazinsiu , kad ant sudetingu SQl uzklausu esu zalias . Kol kas teko <BR>> daryt tik elementarius selectus . Ant MS SQl norim padaryt Data WareHouse .<BR>> Bandziau kaip ir rasiau , vistiek negaunu ko noriu . Busiu dekingas , <BR>> jeigu nurodysit , ka blogai darau ...<BR>> <BR>> GO<BR>> SET ANSI_NULLS ON<BR>> GO<BR>> SET QUOTED_IDENTIFIER ON<BR>> GO<BR>> ALTER PROCEDURE [dbo].[Inventtrans_AV_CREATING] AS<BR>> begin transaction<BR>> if OBJECT_ID(N'INVENTTRANS_AV',N'U') IS NOT NULL<BR>> DROP TABLE DBO.INVENTTRANS_AV<BR>> PRINT 'DROP PABAIGA'<BR>> <BR>> SELECT<BR>> INVD.INVENTLOCATIONID,<BR>> CASE WHEN INV1.ITEMID is Null then LIK1.ITEMID else INV1.ITEMID END AS <BR>> ITEMID,<BR>> CASE WHEN INVT1.ITEMNAME is null then INVT2.ITEMNAME else <BR>> INVT1.ITEMNAME END AS ITEMNAME,<BR>> CASE WHEN INVT1.ALNINVENTGROUP1 is null then INVT2.ALNINVENTGROUP1 <BR>> else INVT1.ALNINVENTGROUP1 END AS ALNGROUP,<BR>> <BR>> SUM ( CASE WHEN LIK1.QTY is Null then 0 else LIK1.QTY END ) AS LQTY,<BR>> SUM ( CASE WHEN LIK1.COSTAMOUNTPOSTED is Null then 0 else <BR>> LIK1.COSTAMOUNTPOSTED END ) AS LSUM,<BR>> <BR>> SUM ( CASE WHEN INV1.DIRECTION = 1 then INV1.QTY else 0 END ) AS PQTY,<BR>> SUM ( CASE WHEN INV1.DIRECTION = 1 then INV1.COSTAMOUNTPOSTED else 0 <BR>> END ) AS PSUM,<BR>> SUM ( CASE WHEN INV1.DIRECTION = 2 then INV1.QTY else 0 END ) AS IQTY,<BR>> SUM ( CASE WHEN INV1.DIRECTION = 2 then INV1.COSTAMOUNTPOSTED else 0 <BR>> END ) AS ISUM,<BR>> <BR>> INVD.INVENTDIMID,<BR>> INVD.CONFIGID,<BR>> INVD.INVENTBATCHID,<BR>> INVD.INVENTSERIALId,<BR>> CASE WHEN INV1.DATEPHYSICAL is null THEN LIK1.DATEPHYSICAL else <BR>> INV1.DATEPHYSICAL end AS DATEPHYSICAL<BR>> <BR>> INTO DBO.INVENTTRANS_AV<BR>> FROM DBO.INVENTDIM INVD<BR>> <BR>> FULL OUTER JOIN DBO.INVENTTrans INV1 ON INVD.INVENTDIMID = <BR>> INV1.INVENTDIMID<BR>> FULL OUTER JOIN DBO.UTINVENTREMAINMONTH LIK1 ON INVD.INVENTDIMID = <BR>> LIK1.INVENTDIMID<BR>> <BR>> JOIN DBO.INVENTTABLE INVT1 ON INV1.ITEMID = INVT1.ITEMID<BR>> JOIN DBO.INVENTTABLE INVT2 ON LIK1.ITEMID = INVT2.ITEMID<BR>> <BR>> WHERE<BR>> INVD.INVENTLOCATIONID = 'its' and<BR>> INV1.datephysical >='2011.07.01' and<BR>> INV1.datephysical <='2011.07.31' and<BR>> LIK1.DATEPHYSICAL >= '2011.07.01' and<BR>> LIK1.DATEPHYSICAL <= '2011.07.31'<BR>> <BR>> GROUP BY<BR>> INVD.INVENTLOCATIONID,<BR>> CASE WHEN INV1.ITEMID is Null then LIK1.ITEMID else INV1.ITEMID END,<BR>> CASE WHEN INVT1.ITEMNAME is null then INVT2.ITEMNAME else <BR>> INVT1.ITEMNAME END,<BR>> CASE WHEN INVT1.ALNINVENTGROUP1 is null then INVT2.ALNINVENTGROUP1 <BR>> else INVT1.ALNINVENTGROUP1 END,<BR>> CASE WHEN INV1.DATEPHYSICAL is null THEN LIK1.DATEPHYSICAL else <BR>> INV1.DATEPHYSICAL END,<BR>> INVD.CONFIGID,<BR>> INVD.INVENTBATCHID,<BR>> INVD.INVENTSERIALID,<BR>> INVD.INVENTDIMID<BR>> <BR>> COMMIT TRANSACTION<BR>> <BR>> CREATE INDEX INVENTLOCATIONIDIDX<BR>> ON DBO.INVENTTRANS_AV <BR>> (INVENTLOCATIONID,ALNGROUP,ITEMNAME,ITEMID,DATEPHYSICAL)<BR>> <BR>> "18-" ir "19-" grupes item'u nera suvestinej , sios grupes nejudejo , tik <BR>> likuciuose . Butent ju ir nematau . Bandziau visaip : ir prie INVD jungti <BR>> INV1 , o tada prie INV1 jungt LIK1 ir visaip kitaip . Visu tipu join'us - <BR>> FULL , LEFT , RIGHT ... Niekaip nepasirodo 18- ir 19- grupes prekes . Kaip <BR>> matot InventDimId yra vienas ir tas pats identifikatorius "00002576_D" . ITS <BR>> skyrius , be jokiu papildomu pozymiu .<BR>> <BR>> Dekui , is anksto , uz pagalba ! ....<BR>> <BR>> Likuciu duomenys :<BR>> <BR>> ItemId InventDimId InventLocationId Qty DatePhysical CostAmountPosted<BR>> 18-1-1-1 00002576_D ITS 2 2011.07.01 241,8<BR>> 18-2-1-1 00002576_D ITS 4 2011.07.01 0,04<BR>> 18-2-1-2 00002576_D ITS 1 2011.07.01 0,02<BR>> 18-2-1-3 00002576_D ITS 19 2011.07.01 0,57<BR>> 19-2-2-1 00002576_D ITS 1 2011.07.01 55<BR>> 19-2-2-6 00002576_D ITS 2 2011.07.01 9<BR>> 19-2-5-1 00002576_D ITS 2 2011.07.01 231,57<BR>> 20-1-1-10 00002576_D ITS 1 2011.07.01 46<BR>> 20-1-1-100 00002576_D ITS 4 2011.07.01 122<BR>> 20-1-1-102 00002576_D ITS 1 2011.07.01 41,42<BR>> 20-1-1-103 00002576_D ITS 1 2011.07.01 6,61<BR>> 20-1-1-11 00002576_D ITS 1 2011.07.01 30<BR>> 20-1-1-12 00002576_D ITS 1 2011.07.01 53<BR>> 20-1-1-14 00002576_D ITS 1 2011.07.01 24,79<BR>> 20-1-1-15 00002576_D ITS 2 2011.07.01<BR>> 20-1-1-19 00002576_D ITS 1 2011.07.01 78,23<BR>> 20-1-1-2 00002576_D ITS 4 2011.07.01 36,03<BR>> 20-1-1-21 00002576_D ITS 3 2011.07.01 231,14<BR>> 20-1-1-22 00002576_D ITS 1 2011.07.01 94,21<BR>> 20-1-1-23 00002576_D ITS 1 2011.07.01 99,17<BR>> 20-1-1-24 00002576_D ITS 2 2011.07.01 175,2<BR>> 20-1-1-3 00002576_D ITS 37 2011.07.01 44,06<BR>> 20-1-1-35 00002576_D ITS 1 2011.07.01 30<BR>> 20-1-1-4 00002576_D ITS 1 2011.07.01<BR>> 20-1-1-5 00002576_D ITS 8 2011.07.01 74,38<BR>> 20-1-1-58 00002576_D ITS 2 2011.07.01 26,8<BR>> 20-1-1-59 00002576_D ITS 5 2011.07.01 12,7<BR>> 20-1-1-6 00002576_D ITS 10 2011.07.01 66,95<BR>> 20-1-1-60 00002576_D ITS 1 2011.07.01 14,05<BR>> 20-1-1-65 00002576_D ITS 4 2011.07.01 52,02<BR>> 20-1-1-68 00002576_D ITS 7 2011.07.01 9,18<BR>> 20-1-1-71 00002576_D ITS 4 2011.07.01 74,08<BR>> 20-1-1-83 00002576_D ITS 1 2011.07.01 60<BR>> 20-1-1-9 00002576_D ITS 2 2011.07.01 13,56<BR>> <BR>> Judejimo duomenys :<BR>> ITEMID DATEPHYSICAL QTY COSTAMOUNTPOSTED DIRECTION INVENTDIMID<BR>> 20-1-1-24 2011.07.13 1 88,43 1 00002576_D<BR>> 20-1-1-21 2011.07.13 1 95,87 1 00002576_D<BR>> 20-1-1-3 2011.07.13 10 7 1 00002576_D<BR>> 20-1-1-44 2011.07.26 4 0 1 00002576_D<BR>> 20-1-1-43 2011.07.26 -21 0 2 00002576_D<BR>> 20-1-1-44 2011.07.26 -4 0 2 00002576_D<BR>> 20-1-1-83 2011.07.31 -1 -60 2 00002576_D<BR>> 20-1-1-36 2011.07.31 -1 -30 2 00002576_D<BR>> 20-1-1-36 2011.07.31 -1 -30 2 00002576_D<BR>> 20-1-1-100 2011.07.31 -1 -30,29 2 00002576_D<BR>> 20-1-1-51 2011.07.31 -1 -99,17 2 00002576_D<BR>> 20-1-1-36 2011.07.31 -1 -30 2 00002576_D<BR>> 20-1-1-100 2011.07.11 3 90 1 00002576_D<BR>> 20-1-1-36 2011.07.11 6 180 1 00002576_D<BR>> 20-1-1-11 2011.07.11 1 30 1 00002576_D<BR>> 11-10-1-1 2011.07.19 100 0 1 00002576_D<BR>> 11-10-1-1 2011.07.19 -100 0 2 00002576_D<BR>> 11-10-1-2 2011.07.19 -80 0 2 00002576_D<BR>> 11-10-1-2 2011.07.19 80 0 1 00002576_D<BR>> 20-1-1-43 2011.07.26 11 0 1 00002576_D<BR>> 20-1-1-43 2011.07.26 10 0 1 00002576_D<BR>> 20-1-1-51 2011.07.13 1 99,17 1 00002576_D<BR>> 20-1-1-22 2011.07.13 1 94,21 1 00002576_D<BR>> 20-1-1-124 2011.07.14 1 180 1 00002576_D<BR>> 20-1-1-100 2011.07.31 -1 -30,29 2 00002576_D<BR>> 20-1-1-36 2011.07.31 -1 -30 2 00002576_D<BR>> 20-1-1-100 2011.07.31 -1 -30,28 2 00002576_D<BR>> 20-1-1-100 2011.07.31 -1 -30,29 2 00002576_D<BR>> 20-1-1-35 2011.07.31 -1 -30 2 00002576_D<BR>> 20-1-1-24 2011.07.31 -1 -87,88 2 00002576_D<BR>> 20-1-1-100 2011.07.31 -1 -30,29 2 00002576_D<BR>> 20-1-1-100 2011.07.31 -1 -30,28 2 00002576_D<BR>> <BR>> <BR>> <BR>></FONT></BODY></HTML>