t-sql query

[ctlscteb]

Hi Everyone,

This is my first time here and I want to tackle the given below problem. any insight would be great

I have 2 views and want to calculate days in between 2 dates(under same column) for approx. 500 rows.

Data is like this

view 1

14 ab 01/03/2007
14 ab 11/03/2007
14 ab 23/04/2007
14 ab 30/04/2008


view 2

14 ab xyz
13 ac avc
12 ad see
1 az qwa


Result i want

14 ab 01/03/2007 2 (minus from the first day of month)
14 ab 11/03/2007 10
14 ab 23/04/2007 .
14 ab 30/04/2008 .

[Delerna]

The datediff function returns the difference between two dates in whatever multiple you want. Days, months, years, hours, minutes, seconds

[ctlscteb]

The datediff function returns the difference between two dates in whatever multiple you want. Days, months, years, hours, minutes, seconds

This thing I know but I want to know that how I can use that with 2 views where multiple rows exist and how to add some functionality to catch the first day of month if first record day is not that.

[Delerna]

I dont know the field names so I will make some up and you can adjust to suit. From your desired result a cant see vow view 2 comes in so I will ignore for this post.
View1
ID AL DTE
14 ab 01/03/2007
14 ab 11/03/2007
14 ab 23/04/2007
14 ab 30/04/2008

SELECT ID,AB,DTE,isnull((SELECT max(DTE) from View1 a where a.Dte<b.Dte),b.Dte)) as PrevDte
FROM View1 b
Order by DTE
 

that will return the rows with the date from the previous record as PrevDte

now wrap that up as a subquery and do the datediff bit

SELECT ID,AB,DTE,datediff(d,DTE,PrevDte) as NumDays
FROM
(SELECT TOP 100 PERCENT ID,AB,DTE,isnull((SELECT max(DTE) from View1 a where a.Dte<b.Dte),b.Dte) as PrevDte
FROM View1 b
ORDER BY Dte) s
 

I haven't tested that so you may have some debugging to do. Hope that helps you

[ctlscteb]

Thanks for posting, your suggestions are on right track but I think that I confused you

This is what I am trying to do, if id, ab changes in view then the whole process run again and counts the days in between from start of month if start date is not 1st of month and if it is 1st then skip that part (in this case days in between would be 0) and count the days in between with next date under same id, ab. The query given by you is showing results in minus.

Here is what final output should look like

id ab dte days in between
14 ab 1/11/2007 10
14 ab 1/25/2007 14
14 ab 2/1/2007 6
15 cd 3/13/2007 12
15 cd 3/24/2007 11
15 cd 3/28/2007 4
.
.
.
.

[ck9663]

Thanks for posting, your suggestions are on right track but I think that I confused you

This is what I am trying to do, if id, ab changes in view then the whole process run again and counts the days in between from start of month if start date is not 1st of month and if it is 1st then skip that part (in this case days in between would be 0) and count the days in between with next date under same id, ab. The query given by you is showing results in minus.

Here is what final output should look like

id ab dte days in between
14 ab 1/11/2007 10
14 ab 1/25/2007 14
14 ab 2/1/2007 6
15 cd 3/13/2007 12
15 cd 3/24/2007 11
15 cd 3/28/2007 4
.
.
.
.

try this:

select id, ab, dte, NextDate, datediff(dd, dte,NextDate) as DaysInBetween 
from 
(select a.id, a.ab, a.dte, (select top 1 dte from yourtable b where a.id = b.id and a.ab = b.ab and a.dte < b.dte) as NextDate
from yourtable a) z
 

Good luck

-- CK

[ctlscteb]

try this:

select id, ab, dte, NextDate, datediff(dd, dte,NextDate) as DaysInBetween 
from 
(select a.id, a.ab, a.dte, (select top 1 dte from yourtable b where a.id = b.id and a.ab = b.ab and a.dte < b.dte) as NextDate
from yourtable a) z
 

Good luck

-- CK

Hi CK,

everything looks working except one thing that how should I catch first day of month in this if NextDate is not first of the month when ab is changing to cd and same at the start of data.

[ck9663]

On your example you have this:

id ab dte days in between
14 ab 1/11/2007 10
14 ab 1/25/2007 14
14 ab 2/1/2007 6
15 cd 3/13/2007 12
15 cd 3/24/2007 11
15 cd 3/28/2007 4

Would you mind inserting the first of the month and the days in between that you are trying to do?

Thanks

-- CK

[Delerna]

try this. And no, I don't know why I put oder by in my first example.
Brain snap

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.Dte<b.Dte) as PrevDte
    FROM View1 b where a.ID=b.ID and a.AB=b.AB and a.Dte>b.Dte
) s
 
 

[ctlscteb]

On your example you have this:



Would you mind inserting the first of the month and the days in between that you are trying to do?

Thanks

-- CK

Hi CK,

I want results like this

If this is the data in table (while days in between is output) -

id ab dte days in between
14 ab 1/11/2007 10
14 ab 1/25/2007 14
14 ab 2/1/2007 6
15 cd 3/13/2007 12
15 cd 3/24/2007 11
15 cd 3/28/2007 4

I want output like this -

id ab dte days in between

1/01/2007
14 ab 1/11/2007 10
14 ab 1/25/2007 14
14 ab 2/1/2007 6
3/01/2007
15 cd 3/13/2007 12
15 cd 3/24/2007 11
15 cd 3/28/2007 4


while 1/01/2007 and 3/01/2007 should calculate automatically the daysin between whenever 14 and ab changed to 15 and cd respectively and calculate days forward instead of null on last day entry under ab or cd.

currently, your query is doing same thing but not taking this first day of month crietia and that is why null value is coming in last of each batch of ab or cd.

Thanks of all the help till now!

[Delerna]

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
 
 

[ck9663]

Hi CK,

I want results like this

If this is the data in table (while days in between is output) -

id ab dte days in between
14 ab 1/11/2007 10
14 ab 1/25/2007 14
14 ab 2/1/2007 6
15 cd 3/13/2007 12
15 cd 3/24/2007 11
15 cd 3/28/2007 4

I want output like this -

id ab dte days in between

1/01/2007
14 ab 1/11/2007 10
14 ab 1/25/2007 14
14 ab 2/1/2007 6
3/01/2007
15 cd 3/13/2007 12
15 cd 3/24/2007 11
15 cd 3/28/2007 4


while 1/01/2007 and 3/01/2007 should calculate automatically the daysin between whenever 14 and ab changed to 15 and cd respectively and calculate days forward instead of null on last day entry under ab or cd.

currently, your query is doing same thing but not taking this first day of month crietia and that is why null value is coming in last of each batch of ab or cd.

Thanks of all the help till now!

Option 1:
1. Create a new view with this query:

      select a.id, a.ab, DATEADD(mm, DATEDIFF(mm,0,a.dte), 0) as dte
      from yourtable a left join (select b.id, b.ab, min(b.dte) as FirstTransactionDate from yourtable b group by b.id, b.ab) c
         on a.id = c.id and a.ab = c.ab and datediff(dd,DATEADD(mm, DATEDIFF(mm,0,a.dte), 0),c. FirstTransactionDate) = 0
      where c.id is null and c.ab is null and c.FirstTransactionDate is null
      union 
      select c.id, c.ab, c.dte from yourtable
 

This query is getting the first day of the month based on the month of the first dte. It will then check if the first day of the month is already existing on your table to avoid duplicates. Then append the rest of your table

2. Use the same query that I posted earlier. Instead of yourtable, replace it with the name of the view you created on #1.

Option 2:
1. Replace all yourtable with the query on Option 1-#1.

-- CK

[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,

Thanks for the help! After small tweaks this works as I wanted.

[TimHop12]

I don't see a reason, you should use View2 in your query when getting the # of days.

The query can go as:

[PHP]select ColumnX, datediff(dd,dateadd(mm, datediff(mm,0,getdate()),0),getdate())[/PHP]

[ck9663]

That's why he has the option.

-- CK

[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 "daysinbetween" if "dte" repeat itself for "ab" and "id" combination) to put 0 for duplicate dates in "daysinbetween" -

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.