PageRenderTime 15ms CodeModel.GetById 2ms app.highlight 4ms RepoModel.GetById 1ms app.codeStats 1ms

/TempSQLTests.txt

http://github.com/khaneh/Orders
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