Sql3/Sql3Str.ps1
|
@"
-- Rmk: -- is remark -- 3Lvl: alway 3 level -- Lvl1: is namespace -- Lvl2: is name. That means is always under a namespace -- Root Ns: fst non remark line is root ns -- Lvl3: is expression -- Lvl2Nm-?: can be have optional ? in front which means its value can be empty string -- Lvl2Nm-?-Fst-term-of- expression: It must belong with ? -- ?: namepace-? is for Sql Drp .Drp Tx TxMbr MbrDta Div Sto Crd Cnt Oup MbrWs T @ Tx TxMbr ?MbrDta Div Sto Crd O @ Cnt Oup ?MbrWs Sql.T Tx .Sel@ Crd Amt Qty Cnt ?Mbr ?Div ?Sto ?Dte .Into .Fm SalesHistory .Wh SHSDate between '@P.Fm' and '@P.To' .And@ ?Div ?Sto ?Dte .Gp@ Crd ?Mbr ?Div ?Sto ?Dte Tx .Upd .Set@ TxWD TxMbr .SelDis Mbr .Into .Fm #Tx ?MbrDta ?BrkMbr .Sel@ Mbr Age Sex Sts Dist Area .Into .Fm JCMember .Wh JCMCode (Select Mbr From #TxMbr) Div .Sel@ Div Nm Seq Sts .Fm Division Sto .Sel@ Sto Nm CNm .Fm LocTbl Crd .Sel@ Crd Nm .Fm JR_FrqMbrLis_#CrdTy() Sql.O Cnt .Sel@ ?MbrCnt RecCnt TxCnt Qty Amt .Into .Fm #Tx Oup .Sel@ Crd ?Mbr ?Sto ?Div ?Dte Amt Qty TxCnt .Into .Fm #Tx x .Jn@ Crd ?Div ?Sto ?Mbr ?MbrWs ?SelMbr .Sel@ Mbr ?Nm ?Adr ?Mail ?Phone .Into .Fm JCMember .Wh JCMCode in (Select Mbr From #TxMbr) Sql.T.Tx.Set TxWD ... Sql.T.Tx.Sel Crd @ CasewhenThen Else End Amt Sum(SHAmount) Qty Sum(SHQty) Cnt Count(SHInvoice+SHSDate+SHRef) ?Mbr ?BrkMbr JCMMCode ?Div ?BrkDiv @Expr ?Sto ?BrkSto @Expr ?Dte @Expr @Sql.T.Tx.Sel.Crd CasewhenThen ... Else ... :NEnd .Repeat :N END~ End | :NEnd @Sql.T.Tx.And ?Div ?SelDiv And .Fld@ in (.List@) ?Crd ?SelCrd And .Fld@ in (.List@) ?Sto ?SelSto And .Fld@ in (.List@) Sql.T.Tx.And.?Div Fld @Expr.Div Sql.T.Tx.And.?Crd Fld @Expr.Crd Sql.T.Tx.And.?Sto Fld @Expr.Sto Sql.T.Tx.And.?Div List @In.Div Sql.T.Tx.And.?Sto List @In.Sto Sql.T.Tx.And.?Crd List @In.Crd Sql.T.Tx.Gp Crd @Expr.Crd ?Mbr ?BrkMbr SHMCode ?Div ?BrkDiv @Expr.Div ?Sto ?BrkSto @Expr.Sto ?Dte ?BrkDte @Expr.Dte Expr Div Sto Dte ?SumY @Expr. TxY ?SumM @Expr. TxY TxM ?SumW @Expr. TxY TxM TxW ?SumD @Expr. TxY TxM TxW TxD TxWD TxDte TxY TxM TxW TxD TxWD TxDte Sql.T.?MbrDta.Sel Mbr JCMCode Age DATEDIFF(YEAR,CONVERT(DATETIME ,JCMDOB,112),GETDATE()) Sex JCMSex Sts JCMStatus Dist JCMDist Area JCMArea Sql.T.Div.Sel Div Dept + Division Nm LongDesc Seq Seq Sts Status Sql.T.Sto.Sel Sto '0'+Loc_Code Nm Loc_Name CNm Loc_CName Sql.T.Crd.Sel Crd CrdTyId Nm CrdTyNm ? SelDiv .Ne @P.DivLis *Blank SelCrd .Ne @P.CrdLis *Blank SelSto .Ne @P.StoLis *Blank BrkDiv .Eq @P.BrkSto 1 BrkSto .Eq @P.BrkSto 1 BrkSto .Eq @P.BrkSto 1 Y .Eq @P.SumLvl Y M .Eq @P.SumLvl M W .Eq @P.SumLvl W D .Eq @P.SumLvl D Dte .Or Y M W D AnyMbrInf @P !Or .InclAdr .InclPhone .InclMail Mbr .And BrkMbr AnyMbrInf Sql.O.Oup.Sel ?Mbr ?BrkMbr Mbr ?Sto ?BrkSto Sto ?Div ?BrkDiv Div Sql.O.Oup.Sel.Dte Y TxY M TxY TxM W TxY TxM TxW D TxY TxM TxW TxD TxWD TxDte Sql.O.Cnt.Sel MbrCnt RecCnt Count(*) TxCnt Sum(TxCnt) Qty Sum(Qty) Amt Sum(Amt) Sql.O.Oup.Jn Crd | Left Join #Crd a #Crd on a.Crd=x.Crd ?Mbr ?BrkMbr | Left Join #MbrDta b on a.Mbr = x.Mbr ?Div ?BrkDiv | Left Join #Div on c.Div = x.Div ?Sto ?BrkSto | Left Join #Sto on d.Sto = x.Sto ---aaa "@ |