Idea Transcript
Sign me up!
Follow @VictoriaYudin
Email
Print
Share 0
Share
Tweet
Like 2 bloggers like this.
select a.ACTINDX, f.YEAR1, f.PERIODID from GL00100 a cross join (select distinct PERIODID, YEAR1 from SY40100) f
select a.*, s.DSCRIPTN from GL00100 a left outer join GL40200 s on a.ACTNUMBR_1 = s.SGMNTID and s.SGMTNUMB = 1
UNION ALL SELECT CAST(LTRIM(RTRIM(l.ACTNUMBR_1)) AS NVARCHAR(7)) AS ACTNUMBR_1 ,CAST(LTRIM(RTRIM(l.ACTNUMBR_2)) AS NVARCHAR(7)) AS ACTNUMBR_2 ,CAST(LTRIM(RTRIM(l.ACTNUMBR_3)) AS NVARCHAR(7)) AS ACTNUMBR_3 ,g.trxdate ,CAST(g.ORTRXSRC AS NVARCHAR(25)) AS ORTRXSRC ,SUM(g.DEBITAMT - g.CRDTAMNT) AS AmountCur ,g.[DATAAREAID] ,CAST(g.CURNCYID AS NVARCHAR(5)) AS CURNCYID ,CAST(LTRIM(RTRIM(l.ACTNUMBR_1)) AS NVARCHAR(10)) AS BUSINESSUNIT ,LTRIM(RTRIM(l.ACTNUMBR_3)) AS Department ,CAST(LTRIM(RTRIM(l.ACTNUMBR_2)) AS NVARCHAR(10)) AS ACCOUNTNUM FROM GL30000 g INNER JOIN GL00100 l ON g.ACTINDX = l.ACTINDX WHERE g.TRXDATE >= '2011-01-01 00:00:00.000'
GROUP BY l.ACTNUMBR_1 ,l.ACTNUMBR_2 ,l.ACTNUMBR_3 ,g.trxdate ,g.ORTRXSRC ,g.CURNCYID
select * from GL00100 where ACTINDX not in (select ACTINDX from GL00105) select * from GL00105 where ACTINDX not in (select ACTINDX from GL00100)
select g.*, p.PORDNMBR from GL20000 g left outer join (select DOCTYPE, VCHRNMBR, PORDNMBR from PM20000 union select DOCTYPE, VCHRNMBR, PORDNMBR from PM30200) p on g.ORTRXTYP = p.DOCTYPE and g.ORCTRNUM = p.VCHRNMBR
select c.ACCATDSC, g.* from GL20000 g -- GL inner join GL00100 a --accounts on g.ACTINDX = a.ACTINDX inner join GL00102 c --categories on a.ACCATNUM = c.ACCATNUM
select g.YEAR1 [Year], n.ACTNUMST Account_Number, g.MNACSGMT Main_Account_Segment, case g.ACTIVE when 1 then '' else 'X' end Inactive, g.ACTDESCR [Description], sum(case g.PERIODID when 0 then g.PERDBLNC else 0 end) Beginning_Balance, sum(case g.PERIODID when 0 then 0 else g.DEBITAMT end) Debit, sum(case g.PERIODID when 0 then 0 else g.CRDTAMNT end) Credit, sum(case g.PERIODID when 0 then 0 else g.PERDBLNC end) Net_Change, sum(PERDBLNC) Ending_Balance from GL11110 g inner join GL00105 n on g.ACTINDX = n.ACTINDX where g.ACCTTYPE = 1 group by g.YEAR1, g.MNACSGMT, g.ACTDESCR, g.ACTIVE, n.ACTNUMST
select * from GL00100 where ACTINDX not in (select distinct ACTINDX from GL10001 union select distinct ACTINDX from GL20000 union select distinct ACTINDX from GL30000)
delete from DYNAMICS..ACTIVITY delete from DYNAMICS..SY00800 delete from DYNAMICS..SY00801 delete from tempdb..DEX_LOCK delete from tempdb..DEX_SESSION
GL00100.ACTNUMBR_1 = GL40200.SGMNTID and GL40200.SGMTNUMB = 1
GL00100.ACTNUMBR_2 = GL40200.SGMNTID and GL40200.SGMTNUMB = 2
¢
SELECT * FROM RM30301 WHERE DSTINDX *** and DISTTYPE = 3
SELECT * FROM RM30301 WHERE DSTINDX = *** and DISTTYPE 3
select * from GL00100 where ACTINDX not in (select ACTINDX from GL20000 where TRXDATE between DATEADD(yy,-1,GETDATE()) and GETDATE() union select ACTINDX from GL30000 where TRXDATE between DATEADD(yy,-1,GETDATE()) and GETDATE())
select n.actnumst [Account], a.actdescr [Name], gl.periodid Period, coalesce(gl.perdblnc,0) Actual, coalesce(b.budgetamt,0) Budget from gl00105 n inner join gl00100 a on n.actindx = a.actindx left outer join gl11110 gl on gl.actindx = n.actindx and gl.year1 = 2009 left outer join gl01201 b on b.actindx = n.actindx and b.budgetid = '2009' --change to yours and b.periodid = gl.periodid
SELECT n.ACTNUMST Account, d.ACTDESCR Name, a.PERIODID Period, SUM(CASE a.AmtType WHEN 'Actual' THEN Amt ELSE 0 END) Actual_Amt, SUM(CASE a.AmtType WHEN 'Budget' THEN Amt ELSE 0 END) Budget_Amt FROM (SELECT ACTINDX, PERIODID, BUDGETAMT Amt, AmtType = 'Budget' FROM GL01201 WHERE BUDGETID = '2009' -change UNION ALL SELECT ACTINDX, PERIODID, PERDBLNC Amt, AmtType = 'Actual' FROM GL11110 WHERE YEAR1 = 2009 -- change ) a LEFT OUTER JOIN GL00100 d ON a.ACTINDX = d.ACTINDX LEFT OUTER JOIN GL00105 n ON a.ACTINDX = n.ACTINDX GROUP BY n.ACTNUMST, d.ACTDESCR, a.PERIODID