0

i JUST WANT TO KNOW IF cte's are used in sybase too...i knw that cte's used for sql server..but have no idea whether they are used for sybase...

If Cte's are not used for sybase, is there a any other method in sybase that it performs as CTE in sql server...??

i HAVE a query like ::

select CONVERT(VARCHAR(7)
  , [startdatetime],111) AS [year-month]
  , nm.nameLine1 AS CompanyName
  , sum(datediff(hour, startdatetime, enddatetime)) as total 
  from  srl 
  inner join  sr on srl.ServiceRequestId = sr.ServiceRequestId
  inner join Name nm
          on (sr.clientCustomerId = nm.customerId 
         and nm.nameTypeId = 'OFICE')
  where (startdatetime >= '08-01-2011 00:00:00.000'
    and enddatetime <= '10-31-2011 00:00:00.000')
  group by  CompanyName, [year-month]
  order by  CompanyName, [year-month]

output of above query:

year-month CompanyName        total       
---------- -----------     ----------- 
2011/08    B                    4 
2011/09    B                    7 
2011/10    B                    0 
2011/08    E                   167 
2011/09    E                   212 
2011/10    E                   131 
2011/08    L                    14 
2011/09    L                    23 
2011/10    L                     3 
2011/08    O                    18 
2011/09    O                     8 
2011/10    O                     7 
2011/08    S                    43 
2011/09    S                    60 
2011/10    S                    60

i want the totals to be displayed in output as :: (I want the output to be as follows)

year-month CompanyName        total        companytotals   
---------- -----------     -----------    --------------- 
2011/08    B                    4 
2011/09    B                    7 
2011/10    B                    0            11
2011/08    E                   167 
2011/09    E                   212 
2011/10    E                   131           510
2011/08    L                    14 
2011/09    L                    23 
2011/10    L                     3          40
2011/08    O                    18 
2011/09    O                     8 
2011/10    O                     7           33
2011/08    S                    43 
2011/09    S                    60 
2011/10    S                    60          163

Is there any methods to do this...?

Thanks in advance..

Jens Mühlenhoff
  • 14,565
  • 6
  • 56
  • 113
sonu
  • 95
  • 2
  • 7
  • The above query is for SYBASE!! – sonu Dec 13 '11 at 00:24
  • I know; I only added some whitespace to make it readable. I don't know Sybase, so I wont comment on the content. – wildplasser Dec 13 '11 at 00:49
  • 1
    Note that you should really specify the exact product name and versions if you want accurate answers, Sybase is a company and not a product. – Lucero Dec 13 '11 at 01:03
  • 1
    Isn't it the same as: http://stackoverflow.com/questions/8469917/query-for-summation-up-to-certain-rows-in-a-table? – Michał Powaga Dec 13 '11 at 07:10
  • @MichałPowaga : : yes, u r right..its jus same as that u answered. . trying to get a respond for the answer so i have again posted this..sorry fr the trouble..!! thanks again..!! – sonu Dec 14 '11 at 01:19

1 Answers1

2

Sybase ASA starting with version 9 does support CTE's as far as I know, but I'm not using Sybase products. For your query you don't need a CTE though, I'd use normal ranking functions.

Add the following to the select:

, SUM(totals) OVER (PARTITION BY [year-month], [CompanyName]) AS companytotals
Lucero
  • 59,176
  • 9
  • 122
  • 152
  • :: thanks for ur valuable comments.. i tried adding the above query to select but it doesnt display companytotal rather it displayed same column as total.. – sonu Dec 13 '11 at 01:16