t-sql query

[ctlscteb]

OOPS I put the extra criteria in the wrong part

SELECT ID,AB,DTE,datediff(d,DTE,isnull(PrevDte,FirstOfMonth)) as NumDays
FROM
(   SELECT ID,AB,DTE,
           convert(char(4),year(Dte) 
               + '-' +convert(char(2),month(Dte)) 
               + '-01') 
           as FirstOfMonth,
          (   SELECT max(DTE) 
              FROM View1 a 
              WHERE a.ID=b.ID and a.AB=b.AB and a.Dte<b.Dte
          ) as PrevDte
    FROM View1 b 
) s

Hi Delerna,

Thanks for all the help in the previous post of mine and I need help again. Current output is showing results as given below from previous query and posts which we communicated but I need some modifications (i want to count 0 as "daysinbetw een" if "dte" repeat itself for "ab" and "id" combination) to put 0 for duplicate dates in "daysinbetw een" -

current results -

id ab dte daysinbetween

1/01/2007 --assumed first of the month
14 ab 1/11/2007 10
14 ab 1/11/2007 10
14 ab 1/11/2007 10
14 ab 1/25/2007 14
14 ab 2/1/2007 6
3/01/2007--assumed first of the month
15 cd 3/13/2007 12
15 cd 3/13/2007 12
15 cd 3/13/2007 12
15 cd 3/24/2007 11
15 cd 3/24/2007 11
15 cd 3/28/2007 4


Modified results would be -

id ab dte days in between

1/01/2007 --assumed first of the month
14 ab 1/11/2007 10
14 ab 1/11/2007 0
14 ab 1/11/2007 0
14 ab 1/25/2007 14
14 ab 1/25/2007 0
14 ab 2/1/2007 6
3/01/2007 --assumed first of the month
15 cd 3/13/2007 12
15 cd 3/13/2007 0
15 cd 3/13/2007 0
15 cd 3/24/2007 11
15 cd 3/24/2007 0
15 cd 3/28/2007 4

Thanks again!

[Delerna]

if you look at this data sequence

14 ab 1/11/2007 10
14 ab 1/11/2007 10
14 ab 1/11/2007 10
you can see that there is nothing to distinguish 1 row from the other.

Therefore, there is nothing to use in order to tell which row should keep its number and which should become 0.
So unless there are other fields that can be brought into the query that would make each row unique, you will need to resort to cursors.

PS
You are welcome to the help but its not a good idea to address a question to an individual expert. There are lots of other experts just as capable and lots more even more capable than I here.
You are actually limiting your chances of getting an answer. What if I decided never to come back to this site?
Just A word to the wise.