none
TSQL - PARTITION BY / OVER functions

    Question

  • Hi,

    Are the functions

    PARTITION BY and OVER exist in SQL SERVER 2000?

    Can I have an example please?

    Thanks in advance,

    Aldo.

    Wednesday, September 05, 2007 12:47 PM

All replies

  •  

    Nope. OVER Clause introudced very first in SQL Server 2005.

     

    We have work-around on SQL Server 2000, it depends with the requirement (not direct or fixed one), tell me what exactly you want to do in SQL Server 2000.

    Wednesday, September 05, 2007 12:55 PM
  • Hi Mani,

    I am trying to solve the solution you gave me yesterday in the post below

    http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2093921&SiteID=1

     

    It looks that I can not run it because I retrieve the data from SQL SERVER 2000.

    I am trying to understand and reproduce Mani's script in such a way that SQL SERVER 2000 can run it...

     

    The code below is only a part of the code.. 

    Code Snippet

       SELECT

         *

    --     , ROW_NUMBER() OVER(PARTITION BY [Invoice No] ORDER BY [JurnalTransMoves_1.SUF]) AS ROWID,

    --     , SUM([JurnalTransMoves_1.SUF]) OVER (PARTITION BY [Invoice No]) AS SUM_SUF

       FROM

          (

          SELECT

              Accounts.FULLNAME AS 'ACCOUNTS.FULLNAME',

              Accounts.ACCOUNTKEY AS 'ACCOUNTS.ACCOUNTKEY',

              Accounts.FILTER AS 'ACCOUNTS.FILTER',

              Accounts.SORTGROUP AS 'ACCOUNTS.SORTGROUP',

              AccSortNames.SORTCODENAME AS 'AccSortNames.SORTCODENAME',

              Accounts.CreditTermsCode AS 'Accounts.CreditTermsCode',

              CreditTerms.DETAILS AS 'CreditTerms.DETAILS',  

              CreditTerms.CURRENF AS 'CreditTerms.CURRENF',

              CreditTerms.MONTH AS 'CreditTerms.MONTH',      

              CreditTerms.DAYS AS 'CreditTerms.DAYS',        

              CreditTerms.SHAREPRC AS 'CreditTerms.SHAREPRC',

              CreditTerms.TEMF AS 'CreditTerms.TEMF',        

     

              CASE

              WHEN CAST(Accounts.VatExampt AS int) = 0 THEN 'x'

              WHEN CAST(Accounts.VatExampt AS int) = 1 THEN 'y'

              ELSE 'Undefined' END AS 'VAT',

     

              Stock.DOCUMENTID AS 'Stock.DOCUMENTID',

              DocumentsDef.DOCNAME As 'DocumentsDef.DOCNAME',

     

              CASE

              WHEN CAST(Stock.DOCUMENTID as int) = 1 THEN Stock.DOCNUMBER

              WHEN CAST(Stock.DOCUMENTID as int) = 3 THEN Stock.DOCNUMBER

              WHEN CAST(Stock.DOCUMENTID as int) = 35 THEN Stock.DOCNUMBER

              WHEN CAST(Stock.DOCUMENTID as int) = 120 THEN Stock.DOCNUMBER

              WHEN CAST(Stock.DOCUMENTID as int) = 31 THEN Stock.REFERENCE

              WHEN CAST(Stock.DOCUMENTID as int) = 44 THEN Stock.REFERENCE

              WHEN CAST(Stock.DOCUMENTID as int) = 34 THEN Stock.REFERENCE

              WHEN CAST(Stock.DOCUMENTID as int) = 43 THEN Stock.REFERENCE

              WHEN CAST(Stock.DOCUMENTID as int) = 40 THEN Stock.REFERENCE

              ELSE '' END AS 'Invoice No',

     

              Stock.VALUEDATE AS 'Stock.VALUEDATE',

              JurnalTrans.DESCRIPTION AS 'JurnalTrans.DESCRIPTION',

              JurnalTrans.REF2 AS 'JurnalTrans.REF2',              

              JurnalTransMoves.SUF AS 'JurnalTransMoves.SUF',

              JurnalTransMoves_1.SUF AS 'JurnalTransMoves_1.SUF', 

              JurnalTransMoves.TRANSID AS 'JURNALTRANSMOVES.TRANSID'

     

          FROM

              JURNALTRANSMOVES AS JurnalTransMoves_1

              INNER JOIN JURNALTRANSMOVES AS JurnalTransMoves

              INNER JOIN (SELECT DISTINCT JURNALTRANSID, RECEIPTSTOCKID, FULLMATCH, TABLFNUM, CKCODE, RSORT, RUSEFID FROM RECEIPTJURNALMATCH) AS ReceiptJurnalMatch_1 ON ReceiptJurnalMatch_1.JURNALTRANSID = JurnalTransMoves.ID

              INNER JOIN ACCOUNTS AS Accounts ON JurnalTransMoves.ACCOUNTKEY = Accounts.ACCOUNTKEY

              INNER JOIN JURNALTRANS AS JurnalTrans ON JurnalTransMoves.TRANSID = JurnalTrans.TRANSID

              INNER JOIN STOCK AS Stock ON JurnalTrans.STOCKID = Stock.ID ON JurnalTransMoves_1.TRANSID = JurnalTrans.TRANSID AND JurnalTransMoves_1.ACCOUNTKEY = Accounts.ACCOUNTKEY

              LEFT OUTER JOIN ITEMS AS Items

              INNER JOIN STOCKMOVES ON Items.ITEMKEY = STOCKMOVES.ITEMKEY

              INNER JOIN ITEMSORTNAMES AS ItemSortNames ON Items.SORTGROUP = ItemSortNames.ITEMSORTCODE ON Stock.ID = STOCKMOVES.STOCKID

              LEFT OUTER JOIN ACCSORTNAMES AS AccSortNames ON Accounts.SORTGROUP = AccSortNames.ACCSORTCODE

              LEFT OUTER JOIN CREDITTERMS AS CreditTerms ON Accounts.CREDITTERMSCODE = CreditTerms.CREDITTERMSCODE

              LEFT OUTER JOIN DOCUMENTSDEF AS DocumentsDef ON Stock.DOCUMENTID = DocumentsDef.DOCUMENTID

          WHERE

             Accounts.SORTGROUP BETWEEN '3001' AND '3020'

             AND Accounts.ACCOUNTKEY IN ('123456')

          ) AS ORDERED

     

     

     

     

    Wednesday, September 05, 2007 1:07 PM
  • Please check the same thread. I posted the answer now.

    Wednesday, September 05, 2007 1:22 PM