/TempSQLTests.txt
Plain Text | 510 lines | 421 code | 89 blank | 0 comment | 0 complexity | d787521a12b52cdefed88175ee05970b MD5 | raw file
1CREATE PROCEDURE dbo.proc_GLAccountReport 2 @GLAccount int, 3 @FromDate nvarchar(10), 4 @ToDate nvarchar(10), 5 @FromTafsil int, 6 @ToTafsil int, 7 @GL int 8AS 9 10SELECT GLRows.Tafsil, SUM(GLRows.IsCredit * GLRows.Amount) AS SumCredit, SUM(- ((GLRows.IsCredit - 1) * GLRows.Amount)) AS SumDebit, COUNT(*) 11 AS CNT, Accounts.AccountTitle 12FROM (SELECT ID AS GLDoc, GLDocDate 13 FROM GLDocs 14 WHERE (GLDocs.IsTemporary = 1 OR 15 GLDocs.IsChecked = 1 OR 16 GLDocs.IsFinalized = 1) AND (GLDocDate >= @FromDate) AND (GLDocDate <= @ToDate) AND (GL = @GL ) AND (IsRemoved = 0) AND 17 (deleted = 0)) EffectiveGLDocs INNER JOIN 18 GLRows ON EffectiveGLDocs.GLDoc = GLRows.GLDoc INNER JOIN 19 Accounts ON GLRows.Tafsil = Accounts.ID 20WHERE (GLRows.GLAccount = @GLAccount) 21GROUP BY GLRows.Tafsil, Accounts.AccountTitle 22HAVING (ISNULL(GLRows.Tafsil, 0) >= @FromTafsil) AND (ISNULL(GLRows.Tafsil, 0) <= @ToTafsil) 23ORDER BY Accounts.AccountTitle 24GO 25=================================================== 26exec proc_GLAccountReport '1303', '1383/06/01', '1383/07/01', '0', '9999999', '83' 27=================================================== 28CREATE PROCEDURE dbo.proc_GLAccountReport 29 @GLAccount int, 30 @FromDate nvarchar(10), 31 @ToDate nvarchar(10), 32 @FromTafsil int, 33 @ToTafsil int, 34 @GL int 35AS 36 37SELECT 0 AS Tafsil, SUM(GLRows.IsCredit * GLRows.Amount) AS SumCredit, SUM(- ((GLRows.IsCredit - 1) * GLRows.Amount)) AS SumDebit, COUNT(*) 38 AS CNT, ' ����� ���� '+CONVERT(nvarchar(4),@GLAccount)+' ��� �� '+@FromDate AS AccountTitle 39FROM (SELECT ID AS GLDoc 40 FROM GLDocs 41 WHERE (GLDocs.IsTemporary = 1 OR 42 GLDocs.IsChecked = 1 OR 43 GLDocs.IsFinalized = 1) AND (GLDocDate >= N'1383/01/01') AND (GLDocDate <= @FromDate) AND (GL = @GL) AND (IsRemoved = 0) AND 44 (deleted = 0)) EffectiveGLDocs INNER JOIN 45 GLRows ON EffectiveGLDocs.GLDoc = GLRows.GLDoc 46WHERE (1 = 1) AND (GLRows.GLAccount = @GLAccount) AND (ISNULL(GLRows.Tafsil, 0) >= @FromTafsil) AND (ISNULL(GLRows.Tafsil, 0) <= @ToTafsil) 47GROUP BY GLRows.GLAccount 48UNION 49SELECT GLRows.Tafsil, SUM(GLRows.IsCredit * GLRows.Amount) AS SumCredit, SUM(- ((GLRows.IsCredit - 1) * GLRows.Amount)) AS SumDebit, COUNT(*) 50 AS CNT, Accounts.AccountTitle AS AccountTitle 51FROM (SELECT ID AS GLDoc, GLDocDate 52 FROM GLDocs 53 WHERE (GLDocs.IsTemporary = 1 OR 54 GLDocs.IsChecked = 1 OR 55 GLDocs.IsFinalized = 1) AND (GLDocDate >= @FromDate) AND (GLDocDate <= @ToDate) AND (GL = @GL ) AND (IsRemoved = 0) AND 56 (deleted = 0)) EffectiveGLDocs INNER JOIN 57 GLRows ON EffectiveGLDocs.GLDoc = GLRows.GLDoc INNER JOIN 58 Accounts ON GLRows.Tafsil = Accounts.ID 59WHERE (GLRows.GLAccount = @GLAccount) 60GROUP BY GLRows.Tafsil, Accounts.AccountTitle 61HAVING (ISNULL(GLRows.Tafsil, 0) >= @FromTafsil) AND (ISNULL(GLRows.Tafsil, 0) <= @ToTafsil) 62ORDER BY Accounts.AccountTitle 63GO 64 65 66=================================================== 67SELECT GLAccounts.Name, DERIVEDTBL.totalDebit AS totalDebit, DERIVEDTBL.totalCredit AS totalCredit, GLAccounts.ID 68FROM (SELECT SUM(GLRows.IsCredit * GLRows.Amount) AS totalCredit, SUM(- ((GLRows.IsCredit - 1) * GLRows.Amount)) AS totalDebit, 69 GLRows.GLAccount, COUNT(*) AS Expr1 70 FROM GLDocs INNER JOIN 71 GLRows ON GLDocs.ID = GLRows.GLDoc 72 WHERE (GLDocs.IsTemporary = 1 OR 73 GLDocs.IsChecked = 1 OR 74 GLDocs.IsFinalized = 1) AND (GLDocs.deleted = 0) AND (GLDocs.IsRemoved = 0) AND (GLRows.deleted = 0) 75 GROUP BY GLRows.GLAccount, GLDocs.GL 76 HAVING (GLDocs.GL = 83)) DERIVEDTBL RIGHT OUTER JOIN 77 GLAccounts ON DERIVEDTBL.GLAccount = GLAccounts.ID 78WHERE (GLAccounts.GL = 83) AND (GLAccounts.GLGroup = 1300) 79ORDER BY GLAccounts.ID 80=================================================== 81SELECT SUM(GLRows.IsCredit * GLRows.Amount) AS totalCredit, SUM(- ((GLRows.IsCredit - 1) * GLRows.Amount)) AS totalDebit, GLRows.GLAccount, 82 COUNT(*) AS Expr1 83FROM GLDocs INNER JOIN 84 GLRows ON GLDocs.ID = GLRows.GLDoc 85WHERE (GLDocs.IsTemporary = 1 OR GLDocs.IsChecked = 1 OR GLDocs.IsFinalized = 1) AND (GLDocs.deleted = 0) AND (GLDocs.IsRemoved = 0) AND (GLRows.deleted = 0) 86GROUP BY GLRows.GLAccount, GLDocs.GL 87HAVING (GLDocs.GL = 83) AND (GLRows.GLAccount = 1303) 88=================================================== 89(GLDocs.IsTemporary = 1 OR GLDocs.IsChecked = 1 OR GLDocs.IsFinalized = 1) AND (GLDocs.deleted = 0) AND (GLDocs.IsRemoved = 0) AND (GLRows.deleted = 0) 90=============== 91 92SELECT 0 AS Tafsil, SUM(GLRows.IsCredit * GLRows.Amount) AS SumCredit, SUM(- ((GLRows.IsCredit - 1) * GLRows.Amount)) AS SumDebit, COUNT(*) AS CNT, 93 GLRows.GLAccount 94FROM (SELECT ID AS GLDoc 95 FROM GLDocs 96 WHERE (GLDocs.IsTemporary = 1 OR 97 GLDocs.IsChecked = 1 OR 98 GLDocs.IsFinalized = 1) AND (GLDocDate >= N'1383/01/01') AND (GLDocDate <= N'1383/07/10') AND (GL = 83) AND (IsRemoved = 0) AND 99 (deleted = 0)) EffectiveGLDocs INNER JOIN 100 GLRows ON EffectiveGLDocs.GLDoc = GLRows.GLDoc 101WHERE (1 = 1) AND (GLRows.GLAccount = 1303) AND (ISNULL(GLRows.Tafsil, 0) >= 0) AND (ISNULL(GLRows.Tafsil, 0) <= 999999) 102GROUP BY GLRows.GLAccount 103UNION 104 105 106SELECT GLRows.Tafsil, SUM(GLRows.IsCredit * GLRows.Amount) AS SumCredit, SUM(- ((GLRows.IsCredit - 1) * GLRows.Amount)) AS SumDebit, COUNT(*) 107 AS CNT, Accounts.AccountTitle 108 109 110 111 112 113 114 115 116=================================================== 117SELECT SUM(GLRows.IsCredit * GLRows.Amount) AS SumCredit, SUM(- ((GLRows.IsCredit - 1) * GLRows.Amount)) AS SumDebit, COUNT(*) AS CNT, 118 GLRows.GLAccount 119FROM (SELECT ID AS GLDoc, GLDocDate 120 FROM GLDocs 121 WHERE (GLDocs.IsTemporary = 1 OR 122 GLDocs.IsChecked = 1 OR 123 GLDocs.IsFinalized = 1) AND (GLDocDate >= N'1383/01/01') AND (GLDocDate <= N'1383/07/10') AND (GL = 83) AND (IsRemoved = 0) AND 124 (deleted = 0)) EffectiveGLDocs INNER JOIN 125 GLRows ON EffectiveGLDocs.GLDoc = GLRows.GLDoc 126WHERE (GLRows.GLAccount = 1303) AND (ISNULL(GLRows.Tafsil, 0) >= 0) AND (ISNULL(GLRows.Tafsil, 0) <= 999999) 127GROUP BY GLRows.GLAccount 128 129=================================================== 130 131SELECT GLRows.Tafsil, SUM(GLRows.IsCredit * GLRows.Amount) AS SumCredit, SUM(- ((GLRows.IsCredit - 1) * GLRows.Amount)) AS SumDebit, COUNT(*) AS CNT 132 133FROM (SELECT ID AS GLDoc, GLDocDate 134 FROM GLDocs 135 WHERE (GLDocs.IsTemporary = 1 OR 136 GLDocs.IsChecked = 1 OR 137 GLDocs.IsFinalized = 1) AND (GLDocDate >= N'1383/06/01') AND (GLDocDate <= N'1383/07/01') AND (GL = 83) AND (IsRemoved = 0) AND 138 (deleted = 0)) EffectiveGLDocs INNER JOIN 139 GLRows ON EffectiveGLDocs.GLDoc = GLRows.GLDoc 140WHERE (GLRows.GLAccount = 1303) 141GROUP BY GLRows.Tafsil 142HAVING (ISNULL(GLRows.Tafsil, 0) >= 0) AND (ISNULL(GLRows.Tafsil, 0) <= 999999) 143ORDER BY GLRows.Tafsil 144 145GO 146 147 148=========================== 149 150SELECT * 151FROM (SELECT ID AS GLDoc, GLDocDate 152 FROM GLDocs 153 WHERE (GLDocs.IsTemporary = 1 OR 154 GLDocs.IsChecked = 1 OR 155 GLDocs.IsFinalized = 1) AND (GLDocDate <= N'1383/07/12') AND (GLDocDate >= N'1383/06/01') AND (GL = 83) AND (IsRemoved = 0) AND 156 (deleted = 0)) EffectiveGLDocs INNER JOIN 157 GLRows ON EffectiveGLDocs.GLDoc = GLRows.GLDoc 158WHERE (GLRows.GLAccount = 1303) 159ORDER BY EffectiveGLDocs.GLDocDate 160 161 162 163 164 165 166 167 168 169 170============ 171============ 172SELECT EffGLAccBal.* 173FROM GLAccounts INNER JOIN 174 (SELECT SUM(GLRows.IsCredit * GLRows.Amount) AS totalCredit, SUM(- ((GLRows.IsCredit - 1) * GLRows.Amount)) AS totalDebit, 175 GLRows.GLAccount, COUNT(*) AS CNT 176 FROM GLDocs INNER JOIN 177 GLRows ON GLDocs.ID = GLRows.GLDoc 178 WHERE (GLDocs.IsTemporary = 1 OR 179 GLDocs.IsChecked = 1 OR 180 GLDocs.IsFinalized = 1) AND (GLDocs.deleted = 0) AND (GLDocs.IsRemoved = 0) AND (GLRows.deleted = 0) 181 GROUP BY GLRows.GLAccount, GLDocs.GL 182 HAVING (GLDocs.GL = 83)) EffGLAccBal ON GLAccounts.ID = EffGLAccBal.GLAccount INNER JOIN 183 GLAccountGroups ON GLAccounts.GLGroup = GLAccountGroups.ID INNER JOIN 184 GLAccountSuperGroups ON GLAccountGroups.GLSuperGroup = GLAccountSuperGroups.ID 185WHERE (GLAccountSuperGroups.GL = 83) AND (GLAccountGroups.GL = 83) AND (GLAccounts.GL = 83) 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200============================ 201============================ 202SELECT ISNULL(Remains.GLAccount, EffGLRows.GLAccount) AS GLAccount, EffGLRows.GLDoc, EffGLRows.GLDocID, EffGLRows.GLDocDate, 203 EffGLRows.Amount, EffGLRows.IsCredit, EffGLRows.Description, ISNULL(Remains.remCred, 0) AS remainedCredit, ISNULL(Remains.remDeb, 0) 204 AS remainedDebit 205FROM (SELECT isnull(SUM(IsCredit * Amount), 0) AS remCred, isnull(SUM(- ((IsCredit - 1) * Amount)), 0) AS remDeb, GLAccount 206 FROM EffectiveGLRows 207 WHERE (GL = 83) AND (GLDocDate < N'1383/08/01') AND (Tafsil = 215256) 208 GROUP BY GLAccount) Remains FULL OUTER JOIN 209 (SELECT GLDoc, GLDocID, GLDocDate, Amount, IsCredit, Description, GLAccount 210 FROM EffectiveGLRows 211 WHERE (Tafsil = 215256) AND (GL = 83) AND (GLDocDate <= N'1383/08/09') AND (GLDocDate >= N'1383/08/09')) EffGLRows ON 212 Remains.GLAccount = EffGLRows.GLAccount 213 214 215 216 217=============================== 218=============================== 219SELECT DRV.GLAcc AS GLAccount, GLAccounts.Name, DRV.GLDoc, DRV.GLDocID, DRV.GLDocDate, DRV.Amount, DRV.IsCredit, DRV.Description, 220 DRV.remainedCredit, DRV.remainedDebit 221FROM (SELECT ISNULL(Remains.GLAccount, EffGLRows.GLAccount) AS GLAcc, ISNULL(Remains.remCred, 0) AS remainedCredit, ISNULL(Remains.remDeb, 222 0) AS remainedDebit, EffGLRows.* 223 FROM (SELECT ISNULL(SUM(IsCredit * Amount), 0) AS remCred, ISNULL(SUM(- ((IsCredit - 1) * Amount)), 0) AS remDeb, GLAccount 224 FROM EffectiveGLRows 225 WHERE (GL = 83) AND (GLDocDate < N'1383/08/01') AND (Tafsil = 215256) 226 GROUP BY GLAccount) Remains FULL OUTER JOIN 227 (SELECT GLDoc, GLDocID, GLDocDate, Amount, IsCredit, Description, GLAccount 228 FROM EffectiveGLRows 229 WHERE (Tafsil = 215256) AND (GL = 83) AND (GLDocDate <= N'1383/08/09') AND (GLDocDate >= N'1383/08/09')) EffGLRows ON 230 Remains.GLAccount = EffGLRows.GLAccount) DRV INNER JOIN 231 GLAccounts ON DRV.GLAcc = GLAccounts.ID 232WHERE (GLAccounts.GL = 83) 233 234=============================== 235=============================== 236=============================== 237=============================== 238 239 240SELECT GLRows.Amount, GLRows.Description, GLRows.Ref1, GLRows.Ref2, GLRows.IsCredit, GLDocs.GLDocDate 241FROM GLRows INNER JOIN GLDocs ON GLRows.GLDoc = GLDocs.ID 242WHERE (GLRows.ID IN ( 243 SELECT MAX(GLRows.ID) AS MaxID 244 FROM GLRows INNER JOIN GLDocs ON GLRows.GLDoc = GLDocs.ID 245 WHERE (GLRows.deleted = 0) AND (GLRows.Ref1 <> '') AND (GLRows.GLAccount = 17001) AND (GLRows.Tafsil IS NULL) AND (GLDocs.IsRemoved = 0) AND (GLDocs.deleted = 0) AND (GLDocs.GL = 83) 246 GROUP BY GLRows.Ref1, GLRows.Ref2, GLRows.Amount, GLRows.GLAccount, GLRows.Tafsil, GLDocs.GL HAVING (COUNT(GLRows.Ref1) % 2 = 1) 247 ) 248) 249ORDER BY GLRows.Ref2, GLRows.ID 250 251 252 253=============================== 254 255SELECT Amount, Description, Ref1, Ref2, IsCredit, GLDocDate 256FROM EffectiveGLRows 257WHERE (ID IN 258 (SELECT MAX(ID) AS MaxID 259 FROM EffectiveGLRows 260 GROUP BY GLAccount, Tafsil, Amount, Ref1, Ref2, GL 261 HAVING (GLAccount = 17001) AND (Tafsil IS NULL) AND (GL = 83) AND (Ref1 <> N'') AND (COUNT(Ref1) % 2 = 1))) 262ORDER BY Ref2, ID 263 264=============================== 265SUM REMAINED: 266 267SELECT ISNULL(SUM(IsCredit * Amount), 0) AS remCred, ISNULL(SUM(- ((IsCredit - 1) * Amount)), 0) AS remDeb 268FROM EffectiveGLRows 269WHERE (ID IN( 270 SELECT MAX(ID) AS MaxID 271 FROM EffectiveGLRows 272 GROUP BY GLAccount, Tafsil, Amount, Ref1, Ref2, GL, Ref1, Ref2 273 HAVING (GLAccount = 17001) AND (Tafsil IS NULL) AND (GL = 83) AND (Ref1 <> N'') AND (COUNT(Ref1) % 2 = 1) AND (Ref2 < N'1382/01/01') 274 )) 275=============================== 276 277SELECT ISNULL(SUM(IsCredit * Amount), 0) AS remCred, ISNULL(SUM(- ((IsCredit - 1) * Amount)), 0) AS remDeb 278FROM EffectiveGLRows 279WHERE (ID IN 280 (SELECT MAX(ID) AS MaxID 281 FROM EffectiveGLRows 282 GROUP BY GLAccount, Tafsil, Amount, Ref1, Ref2, GL, Ref1, Ref2 283 HAVING (GLAccount = 17001) AND (Tafsil IS NULL) AND (GL = 83) AND (Ref1 <> N'') AND (COUNT(Ref1) % 2 = 1) AND (Ref2 < N''))) 284 285 286=============================== 287=============================== 288 289CREATE PROCEDURE dbo.proc_CheqBook_Partial 290 @GLAccount int, 291 @Tafsil int, 292 @FromDate nvarchar(10), 293 @ToDate nvarchar(10), 294 @GL int, 295 @ShowRemained bit 296AS 297DECLARE 298 @remainedCredit int, 299 @remainedDebit int 300SELECT @remainedCredit=ISNULL(SUM(IsCredit * Amount), 0) , @remainedDebit=ISNULL(SUM(- ((IsCredit - 1) * Amount)), 0) 301FROM EffectiveGLRows 302WHERE (ID IN( 303 SELECT MAX(ID) AS MaxID 304 FROM EffectiveGLRows 305 GROUP BY GLAccount, Tafsil, Amount, Ref1, Ref2, GL, Ref1, Ref2 306 HAVING (GLAccount = 17001) AND (Tafsil IS NULL) AND (GL = 83) AND (Ref1 <> N'') AND (COUNT(Ref1) % 2 = 1) AND (Ref2 < N'1382/01/01') 307 )) 308 309IF @Tafsil='' 310 BEGIN 311 SELECT Amount, Description, Ref1, Ref2, IsCredit, GLDocDate 312 FROM EffectiveGLRows 313 WHERE (ID IN 314 (SELECT MAX(ID) AS MaxID 315 FROM EffectiveGLRows 316 GROUP BY GLAccount, Tafsil, Amount, Ref1, Ref2, GL 317 HAVING (GLAccount = @GLAccount) AND (Tafsil IS NULL) AND (GL = @GL) AND (Ref1 <> N'') AND (COUNT(Ref1) % 2 = 1)) AND (Ref2 >= @FromDate) AND (Ref2 <= @ToDate)) 318 ORDER BY Ref2, ID 319 END 320ELSE 321 BEGIN 322 SELECT Amount, Description, Ref1, Ref2, IsCredit, GLDocDate 323 FROM EffectiveGLRows 324 WHERE (ID IN 325 (SELECT MAX(ID) AS MaxID 326 FROM EffectiveGLRows 327 GROUP BY GLAccount, Tafsil, Amount, Ref1, Ref2, GL 328 HAVING (GLAccount = @GLAccount) AND (Tafsil = @Tafsil ) AND (GL = @GL) AND (Ref1 <> N'') AND (COUNT(Ref1) % 2 = 1)) AND (Ref2 >= @FromDate) AND (Ref2 <= @ToDate)) 329 ORDER BY Ref2, ID 330 END 331GO 332 333=============================== 334=============================== 335 336SELECT ISNULL(CONVERT(tinyint, Invoices.IsA), 2) AS IsA, Accounts.IsADefault, ARItems.Link 337FROM Accounts INNER JOIN 338 ARItems ON ARItems.Account = Accounts.ID LEFT OUTER JOIN 339 ARItemsRelations ON ARItemsRelations.CreditARItem = ARItems.ID LEFT OUTER JOIN 340 ARItems ARItems_2 ON ARItems_2.ID = ARItemsRelations.DebitARItem LEFT OUTER JOIN 341 Invoices ON ARItems_2.Link = Invoices.ID 342WHERE (ARItems.Type = 2) AND (ARItems.Link = 23681) 343 344=============================== 345=============================== 346SELECT * 347FROM EffectiveGLRows 348WHERE (Ref1 IN 349 (SELECT DISTINCT Ref1 350 FROM EffectiveGLRows 351 WHERE (Ref1 <> '') AND (GL = 83) AND (GLAccount LIKE '4200%'))) AND (Ref1 NOT IN 352 (SELECT DISTINCT Ref1 353 FROM EffectiveGLRows 354 WHERE (Ref1 <> '') AND (GL = 83) AND (GLAccount LIKE '4200%') AND SYS IS NOT NULL)) 355ORDER BY Ref1, ID DESC 356 357=============================== 358*** GLAccount wich has worked with 4200% 359SELECT DISTINCT GLAccount 360FROM EffectiveGLRows 361WHERE (Ref1 IN 362 (SELECT DISTINCT Ref1 363 FROM EffectiveGLRows 364 WHERE (Ref1 <> '') AND (GL = 83) AND (GLAccount LIKE '4200%'))) AND (GL = 83) 365ORDER BY GLAccount 366 367=============================== 368SELECT DISTINCT GLAccount 369FROM EffectiveGLRows 370WHERE (Ref1 IN 371 (SELECT DISTINCT Ref1 372 FROM EffectiveGLRows 373 WHERE (Ref1 <> '') AND (GL = 83))) AND (GL = 83) AND (Ref1 NOT IN 374 (SELECT DISTINCT Ref1 375 FROM EffectiveGLRows 376 WHERE (Ref1 <> '') AND (GL = 83) AND (SYS IS NOT NULL))) 377ORDER BY GLAccount 378=============================== 379=============================== 380SELECT Accounts.ID AS AccID, Accounts.AccountTitle, SUM(InvoiceLines.Price) AS Price, SUM(InvoiceLines.Discount) AS Discount, SUM(InvoiceLines.Reverse) 381 AS Reverse, InvoiceItemCategories.ID AS CatID, InvoiceItemCategories.Name AS CatName, COUNT(*) AS InvQtty 382FROM InvoiceItemCategoryRelations INNER JOIN 383 InvoiceItems ON InvoiceItemCategoryRelations.InvoiceItem = InvoiceItems.ID INNER JOIN 384 InvoiceItemCategories ON InvoiceItemCategoryRelations.InvoiceItemCategory = InvoiceItemCategories.ID INNER JOIN 385 InvoiceLines ON InvoiceItems.ID = InvoiceLines.Item INNER JOIN 386 Invoices ON InvoiceLines.Invoice = Invoices.ID INNER JOIN 387 Accounts ON Invoices.Customer = Accounts.ID 388WHERE (Invoices.Voided = 0) AND (Invoices.Issued = 1) AND (Invoices.IssuedDate >= N'1382/08/20') AND (Invoices.IssuedDate <= N'1383/08/20') 389GROUP BY Accounts.AccountTitle, InvoiceItemCategories.ID, InvoiceItemCategories.Name, Accounts.ID 390HAVING (InvoiceItemCategories.ID > 2) 391ORDER BY AccID, CatID 392=============================== 393SELECT Kid.AccID, Kid.AccountTitle, Kid.Price, Kid.Discount, Kid.Reverse, Kid.CatID, Kid.CatName, Kid.InvQtty, COUNT(*) AS TotalInvQtty, 394 SUM(Invoices.TotalReceivable) AS TotalReceivable, SUM(Invoices.TotalDiscount) AS TotalDiscount, SUM(Invoices.TotalReverse) 395 AS TotalReverse 396FROM (SELECT Accounts.ID AS AccID, Accounts.AccountTitle, SUM(InvoiceLines.Price) AS Price, SUM(InvoiceLines.Discount) AS Discount, 397 SUM(InvoiceLines.Reverse) AS Reverse, InvoiceItemCategories.ID AS CatID, InvoiceItemCategories.Name AS CatName, COUNT(*) 398 AS InvQtty 399 FROM InvoiceItemCategoryRelations INNER JOIN 400 InvoiceItems ON InvoiceItemCategoryRelations.InvoiceItem = InvoiceItems.ID INNER JOIN 401 InvoiceItemCategories ON InvoiceItemCategoryRelations.InvoiceItemCategory = InvoiceItemCategories.ID INNER JOIN 402 InvoiceLines ON InvoiceItems.ID = InvoiceLines.Item INNER JOIN 403 Invoices ON InvoiceLines.Invoice = Invoices.ID INNER JOIN 404 Accounts ON Invoices.Customer = Accounts.ID 405 WHERE (Invoices.Voided = 0) AND (Invoices.Issued = 1) AND (Invoices.IssuedDate >= N'1382/08/20') AND (Invoices.IssuedDate <= N'1383/08/20') 406 GROUP BY Accounts.AccountTitle, InvoiceItemCategories.ID, InvoiceItemCategories.Name, Accounts.ID 407 HAVING (InvoiceItemCategories.ID > 2)) Kid INNER JOIN 408 Invoices ON Kid.AccID = Invoices.Customer 409GROUP BY Kid.AccID, Kid.AccountTitle, Kid.Price, Kid.Discount, Kid.Reverse, Kid.CatID, Kid.CatName, Kid.InvQtty, Invoices.Issued, Invoices.Voided 410HAVING (Invoices.Issued = 1) AND (Invoices.Voided = 0) 411ORDER BY Kid.AccID, Kid.CatID 412=============================== 413SELECT InvoiceLines.Item 414FROM InvoiceLines INNER JOIN 415 Invoices ON InvoiceLines.Invoice = Invoices.ID 416WHERE (Invoices.Voided = 0) AND (Invoices.Issued = 1) AND (Invoices.IssuedDate >= N'1382/08/20') AND (Invoices.IssuedDate <= N'1383/08/20') 417=============================== 418SELECT ID 419FROM InvoiceItems 420WHERE (ID IN 421 (SELECT InvoiceLines.Item 422 FROM InvoiceLines INNER JOIN 423 Invoices ON InvoiceLines.Invoice = Invoices.ID 424 WHERE (Invoices.Voided = 0) AND (Invoices.Issued = 1) AND (Invoices.IssuedDate >= N'1382/08/20') AND 425 (Invoices.IssuedDate <= N'1383/08/20'))) AND (ID NOT IN 426 (SELECT Item_ID 427 FROM InventoryItemCategoryRelations 428 WHERE (Cat_ID > 2))) 429=============================== 430=============================== 431=============================== 432=============================== 433=============================== 434=============================== 435=============================== 436=============================== 437=============================== 438 439=============================== 440=============================== 441SELECT * 442FROM (SELECT TOP 10 Accounts.ID AS AccID, Accounts.AccountTitle, SUM(InvoiceLines.Price) AS Price, SUM(InvoiceLines.Discount) AS Discount, 443 SUM(InvoiceLines.Reverse) AS Reverse, InvoiceItemCategories.ID AS CatID, COUNT(*) AS InvQtty 444 FROM InvoiceItemCategoryRelations INNER JOIN 445 InvoiceItems ON InvoiceItemCategoryRelations.InvoiceItem = InvoiceItems.ID INNER JOIN 446 InvoiceItemCategories ON InvoiceItemCategoryRelations.InvoiceItemCategory = InvoiceItemCategories.ID INNER JOIN 447 InvoiceLines ON InvoiceItems.ID = InvoiceLines.Item INNER JOIN 448 Invoices ON InvoiceLines.Invoice = Invoices.ID INNER JOIN 449 Accounts ON Invoices.Customer = Accounts.ID 450 WHERE (Invoices.IssuedDate >= N'1382/08/20') AND (Invoices.IssuedDate <= N'1383/08/20') AND (Invoices.Voided = 0) AND (Invoices.Issued = 1) 451 GROUP BY Accounts.AccountTitle, InvoiceItemCategories.ID, Accounts.ID 452 HAVING (InvoiceItemCategories.ID = 3) 453 ORDER BY COUNT(*) DESC, SUM(InvoiceLines.Price) DESC) TOPSORTED 454=============================== 455SELECT Ref1, Ref2, Amount, IsCredit 456FROM GLRows 457WHERE (GLAccount = 15004) AND (IsCredit = 1) AND (Ref1 <> '') AND (SYS IS NULL) 458 459=============================== 460SELECT * 461FROM GLRows 462WHERE (ID IN 463 (SELECT GLRows.ID 464 FROM (SELECT Ref1, Ref2, Amount 465 FROM GLRows 466 WHERE (GLAccount IN (49001,49010,49011,49012,49013,15004)) AND (IsCredit = 1) AND (Ref1 <> '') AND (SYS IS NULL)) CHQ INNER JOIN 467 GLRows ON CHQ.Ref1 = GLRows.Ref1 AND CHQ.Ref2 = GLRows.Ref2 AND CHQ.Amount = GLRows.Amount)) 468 469=============================== 470UPDATE GLRows 471SET IsCredit = CONVERT(bit, 1 - IsCredit) 472WHERE (ID IN 473 (SELECT GLRows.ID 474 FROM (SELECT Ref1, Ref2, Amount 475 FROM GLRows 476 WHERE (GLAccount IN (49001,49010,49011,49012,49013,15004)) AND (IsCredit = 1) AND (Ref1 <> '') AND (SYS IS NULL)) CHQ INNER JOIN 477 GLRows ON CHQ.Ref1 = GLRows.Ref1 AND CHQ.Ref2 = GLRows.Ref2 AND CHQ.Amount = GLRows.Amount)) 478 479=============================== 480 481=============================== 482 483=============================== 484 485=============================== 486 487=============================== 488 489=============================== 490 491=============================== 492 493=============================== 494 495=============================== 496 497=============================== 498 499=============================== 500 501=============================== 502 503=============================== 504 505=============================== 506 507=============================== 508 509=============================== 510