HOW TO GET FIRST DATE

• Deny_Christian

  SSC Eights!

  Points: 914

  More actions

  September 17, 2018 at 12:54 am

  #372788

  Dear all,

  I have data like:
  
     NAME                             SPONSOR_NAME     DATE_OF_CALL
       A                                    FIFA                            2018-08-27 4:45:09 PM
       A                                    FIFA                            2018-08-28 09:00:00 AM
       A                                    FIFA                            2018-08-28 01:00:00.PM
       B                                    VIVA                           2018-08-27 04:50:10 PM
       B                                    VIVA                           2018-08-28 10:00:10 AM  
       B                                    VIVA                           2018-08-28 10:50:55 AM
       C                                    VOIP                          2018-08-29 08:50:00 AM
       C                                    VOIP                          2018-08-30 09:59:00 AM    
       C                                    VOIP                          2018-08-30 02:45:00 PM
       D                                    PIVO                          2018-08-30 07:00:00 AM


  The Result that I want only take the first date of call like this:

  
     NAME                             SPONSOR_NAME     DATE_OF_CALL
       A                                    FIFA                            2018-08-27 4:45:09 PM
       B                                    VIVA                           2018-08-27 04:50:10 PM
       C                                    VOIP                          2018-08-29 08:50:00 AM
       D                                    PIVO                          2018-08-30 07:00:00 AM


  I have made the looping syntax:

  
SELECT DISTINCT(NAME)--,DATE_OF_CALL
--SELECT *
--INTO ##TEST1
FROM ##TEST
--GROUP BY NAME--,DATE_OF_CALL
ORDER BY NAME ASC

  declare @SQL    nvarchar (4000)
declare @name        nvarchar (1000)
DECLARE @TIME    NVARCHAR(MAX)

  DECLARE db_cursor CURSOR FOR
SELECT NAME,DATE_OF_CALL FROM ##TEST --TEMPTABLE

  OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @NAME,@TIME

  WHILE @@FETCH_STATUS = 0
   BEGIN
     set @sql = 'INSERT INTO ##test1 select TOP 1 '+ @NAME + ','''+@TIME+'''
                 FROM ##TEST A INNER JOIN ##TEST1 B ON A.NAME = B. NAME
                 ORDER BY A.NAME ASC'
     print @sql
     --exec SP_EXECUTESQL @sql
    end

        FETCH NEXT FROM db_cursor INTO @NAME,@TIME
END

  CLOSE db_cursor
DEALLOCATE db_cursor


  but it failed.. 🙁

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  September 17, 2018 at 1:49 am

  #2005918

  And your question is?

  Allow me to preempt.  You don't need a cursor - instead, use the MIN function with GROUP BY.  Give it a go, and post back if you have any specific questions.  Please provide DDL and sample data in the form of CREATE TABLE and INSERT statements if you do.

  John

• Deny_Christian

  SSC Eights!

  Points: 914

  More actions

  September 17, 2018 at 4:08 am

  #2005927

  John Mitchell-245523 - Monday, September 17, 2018 1:49 AM

  And your question is?

  Allow me to preempt.  You don't need a cursor - instead, use the MIN function with GROUP BY.  Give it a go, and post back if you have any specific questions.  Please provide DDL and sample data in the form of CREATE TABLE and INSERT statements if you do.

  John

  Hai John,
  Thank you for asking me..my question is how do I can get only the first date_of_call?
  and I've found the answer, just like you said, I don't need a cursor, so I've tried and this is the update result:

  
SElect f.Name,f.Sponsor,f.Date_of_call
from (
 select Name, min(Date_of_call) as CALL_TIME
 from ##test group by Name
) as x
inner join ##test as f
    on f.Name= x.Nameand f.Date_of_call= x.CALL_TIME;


  Thank you

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  September 17, 2018 at 4:23 am

  #2005930

  Did you try running that?  It won't work, because you don't have a GROUP BY.  You shouldn't need the self-join, either.

  SELECT name, sponsor, MIN(Date_of_call)
  FROM MyTable
  GROUP BY name, sponsor

  John

• Deny_Christian

  SSC Eights!

  Points: 914

  More actions

  September 18, 2018 at 12:37 am

  #2006079

  John Mitchell-245523 - Monday, September 17, 2018 4:23 AM

  Did you try running that?  It won't work, because you don't have a GROUP BY.  You shouldn't need the self-join, either.

  SELECT name, sponsor, MIN(Date_of_call)
  FROM MyTable
  GROUP BY name, sponsor

  John

  Hai John,
  I've Tried it, and success

  
create table dbo.Testing_Name
( NAME            VARCHAR(50),
SPONSOR_NAME VARCHAR(15),
DATE_OF_CALL    DATETIME
)ON [PRIMARY]

  insert into DBO.TESTING_NAME
VALUES
('A','FIFA','2018-08-27 04:45:09 PM')
GO
insert into DBO.TESTING_NAME
VALUES
('A','FIFA','2018-08-28 09:00:00 AM')
GO
insert into DBO.TESTING_NAME
VALUES
('A','FIFA','2018-08-28 01:00:00 PM')
GO
insert into DBO.TESTING_NAME
VALUES
('B','VIVA','2018-08-27 04:50:10 PM')
GO
insert into DBO.TESTING_NAME
VALUES
('B','VIVA','2018-08-28 10:00:10 AM')
GO
insert into DBO.TESTING_NAME
VALUES
('B','VIVA','2018-08-28 10:50:55 AM')
GO
insert into DBO.TESTING_NAME
VALUES
('C','VOIP','2018-08-29 08:50:00 AM')
GO
insert into DBO.TESTING_NAME
VALUES
('C','VOIP','2018-08-30 02:45:00 PM')
GO
insert into DBO.TESTING_NAME
VALUES
('D','PIVO','2018-08-30 07:00:00 AM')
GO

  SELECT * fROM test..testing_name

  SElect f.Name,f.Sponsor_NAME,f.Date_of_call
from (
select Name, min(Date_of_call) as CALL_TIME
from test..testing_name
group by Name
) as x
inner join test..testing_name as f
  on f.Name= x.Name and f.Date_of_call= x.CALL_TIME;


• Chris Wooding

  SSCarpal Tunnel

  Points: 4530

  More actions

  September 19, 2018 at 6:22 am

  #2006251

  Am I the only person disappointed by the topic contents after reading the title?

• jschmidt 17654

  SSCommitted

  Points: 1610

  More actions

  September 19, 2018 at 10:11 am

  #2006297

  Deny_Christian - Tuesday, September 18, 2018 12:37 AM

  John Mitchell-245523 - Monday, September 17, 2018 4:23 AM

  Did you try running that?  It won't work, because you don't have a GROUP BY.  You shouldn't need the self-join, either.

  SELECT name, sponsor, MIN(Date_of_call)
  FROM MyTable
  GROUP BY name, sponsor

  John

  Hai John,
  I've Tried it, and success

  
create table dbo.Testing_Name
( NAME            VARCHAR(50),
SPONSOR_NAME VARCHAR(15),
DATE_OF_CALL    DATETIME
)ON [PRIMARY]

  insert into DBO.TESTING_NAME
VALUES
('A','FIFA','2018-08-27 04:45:09 PM')
GO
insert into DBO.TESTING_NAME
VALUES
('A','FIFA','2018-08-28 09:00:00 AM')
GO
insert into DBO.TESTING_NAME
VALUES
('A','FIFA','2018-08-28 01:00:00 PM')
GO
insert into DBO.TESTING_NAME
VALUES
('B','VIVA','2018-08-27 04:50:10 PM')
GO
insert into DBO.TESTING_NAME
VALUES
('B','VIVA','2018-08-28 10:00:10 AM')
GO
insert into DBO.TESTING_NAME
VALUES
('B','VIVA','2018-08-28 10:50:55 AM')
GO
insert into DBO.TESTING_NAME
VALUES
('C','VOIP','2018-08-29 08:50:00 AM')
GO
insert into DBO.TESTING_NAME
VALUES
('C','VOIP','2018-08-30 02:45:00 PM')
GO
insert into DBO.TESTING_NAME
VALUES
('D','PIVO','2018-08-30 07:00:00 AM')
GO

  SELECT * fROM test..testing_name

  SElect f.Name,f.Sponsor_NAME,f.Date_of_call
from (
select Name, min(Date_of_call) as CALL_TIME
from test..testing_name
group by Name
) as x
inner join test..testing_name as f
  on f.Name= x.Name and f.Date_of_call= x.CALL_TIME;


  This will probably work as long as NAME and SPONSOR_NAME are always the same pair, as your query implicitly assumes that case. If you encounter something where a NAME could have two different SPONSOR_NAME elements, then it will fail. Try adding another record:
  insert into #Testing_Name
VALUES ('B','FIFA','2018-08-06 09:50:10 PM');
  Then your query returns:
  Name    Sponsor_NAME    Date_of_call
  A    FIFA    2018-08-27 16:45:09.000
  B    FIFA    2018-08-06 21:50:10.000
  C    VOIP    2018-08-29 08:50:00.000
  D    PIVO    2018-08-30 07:00:00.000

  Notice you lose all the B-VIVA records. There are probably other data possibilities that will cause issues. I went through a similar transition from old batch or looping code to SQL set-based that takes advantage of relational database power. John's group by approach will be cleaner reading and more efficient for finding all pairs of NAME and SPONSOR_NAME.

  SELECT *
FROM #Testing_Name
order by NAME, SPONSOR_NAME, DATE_OF_CALL;

  NAME    SPONSOR_NAME    DATE_OF_CALL
A    FIFA    2018-08-27 16:45:09.000
A    FIFA    2018-08-28 09:00:00.000
A    FIFA    2018-08-28 13:00:00.000
B    FIFA    2018-08-06 21:50:10.000
B    VIVA    2018-08-27 16:50:10.000
B    VIVA    2018-08-28 10:00:10.000
B    VIVA    2018-08-28 10:50:55.000
C    VOIP    2018-08-29 08:50:00.000
C    VOIP    2018-08-30 14:45:00.000
D    PIVO    2018-08-30 07:00:00.000

  select NAME, SPONSOR_NAME, MIN(DATE_OF_CALL) as FIRST_DATE
from #Testing_Name
group by NAME, SPONSOR_NAME
order by NAME, SPONSOR_NAME;

  NAME    SPONSOR_NAME    FIRST_DATE
A    FIFA    2018-08-27 16:45:09.000
B    FIFA    2018-08-06 21:50:10.000
B    VIVA    2018-08-27 16:50:10.000
C    VOIP    2018-08-29 08:50:00.000
D    PIVO    2018-08-30 07:00:00.000

• JackCarrington

  Mr or Mrs. 500

  Points: 519

  More actions

  October 10, 2018 at 7:03 am

  #2008809

  Chris Wooding - Wednesday, September 19, 2018 6:22 AM

  Am I the only person disappointed by the topic contents after reading the title?

  Was just thinking that, and would have been a very pertinent question given our profession LOL

• pietlinden

  SSC Guru

  Points: 63435

  More actions

  October 10, 2018 at 9:55 am

  #2008874

  Bart vs. Australia, anyone?

• SQLStud 74876

  SSC Veteran

  Points: 247

  More actions

  October 12, 2018 at 5:27 pm

  #2009449

  Ask her out.

• Jeff Moden

  SSC Guru

  Points: 1003894

  More actions

  October 14, 2018 at 6:53 am

  #2009506

  Deny_Christian - Monday, September 17, 2018 12:54 AM

  Dear all,

  I have data like:
  
     NAME                             SPONSOR_NAME     DATE_OF_CALL
       A                                    FIFA                            2018-08-27 4:45:09 PM
       A                                    FIFA                            2018-08-28 09:00:00 AM
       A                                    FIFA                            2018-08-28 01:00:00.PM
       B                                    VIVA                           2018-08-27 04:50:10 PM
       B                                    VIVA                           2018-08-28 10:00:10 AM  
       B                                    VIVA                           2018-08-28 10:50:55 AM
       C                                    VOIP                          2018-08-29 08:50:00 AM
       C                                    VOIP                          2018-08-30 09:59:00 AM    
       C                                    VOIP                          2018-08-30 02:45:00 PM
       D                                    PIVO                          2018-08-30 07:00:00 AM


  The Result that I want only take the first date of call like this:

  
     NAME                             SPONSOR_NAME     DATE_OF_CALL
       A                                    FIFA                            2018-08-27 4:45:09 PM
       B                                    VIVA                           2018-08-27 04:50:10 PM
       C                                    VOIP                          2018-08-29 08:50:00 AM
       D                                    PIVO                          2018-08-30 07:00:00 AM


  I have made the looping syntax:

  
SELECT DISTINCT(NAME)--,DATE_OF_CALL
--SELECT *
--INTO ##TEST1
FROM ##TEST
--GROUP BY NAME--,DATE_OF_CALL
ORDER BY NAME ASC

  declare @SQL    nvarchar (4000)
declare @name        nvarchar (1000)
DECLARE @TIME    NVARCHAR(MAX)

  DECLARE db_cursor CURSOR FOR
SELECT NAME,DATE_OF_CALL FROM ##TEST --TEMPTABLE

  OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @NAME,@TIME

  WHILE @@FETCH_STATUS = 0
   BEGIN
     set @sql = 'INSERT INTO ##test1 select TOP 1 '+ @NAME + ','''+@TIME+'''
                 FROM ##TEST A INNER JOIN ##TEST1 B ON A.NAME = B. NAME
                 ORDER BY A.NAME ASC'
     print @sql
     --exec SP_EXECUTESQL @sql
    end

        FETCH NEXT FROM db_cursor INTO @NAME,@TIME
END

  CLOSE db_cursor
DEALLOCATE db_cursor


  but it failed.. 🙁

  Hi Deny,

  You've been around long enough to know that if you post readily consumable data, you'll get answers not only faster, but the answers will be better because people will test their stuff using your data.  Please see the article at the first link in my signature line below for one way to do that (there are a couple of other methods, as well).  Thanks for helping us help you.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Deny_Christian

  SSC Eights!

  Points: 914

  More actions

  October 15, 2018 at 4:25 am

  #2009551

  Jeff Moden - Sunday, October 14, 2018 6:53 AM

  Deny_Christian - Monday, September 17, 2018 12:54 AM

  Dear all,

  I have data like:
  
     NAME                             SPONSOR_NAME     DATE_OF_CALL
       A                                    FIFA                            2018-08-27 4:45:09 PM
       A                                    FIFA                            2018-08-28 09:00:00 AM
       A                                    FIFA                            2018-08-28 01:00:00.PM
       B                                    VIVA                           2018-08-27 04:50:10 PM
       B                                    VIVA                           2018-08-28 10:00:10 AM  
       B                                    VIVA                           2018-08-28 10:50:55 AM
       C                                    VOIP                          2018-08-29 08:50:00 AM
       C                                    VOIP                          2018-08-30 09:59:00 AM    
       C                                    VOIP                          2018-08-30 02:45:00 PM
       D                                    PIVO                          2018-08-30 07:00:00 AM


  The Result that I want only take the first date of call like this:

  
     NAME                             SPONSOR_NAME     DATE_OF_CALL
       A                                    FIFA                            2018-08-27 4:45:09 PM
       B                                    VIVA                           2018-08-27 04:50:10 PM
       C                                    VOIP                          2018-08-29 08:50:00 AM
       D                                    PIVO                          2018-08-30 07:00:00 AM


  I have made the looping syntax:

  
SELECT DISTINCT(NAME)--,DATE_OF_CALL
--SELECT *
--INTO ##TEST1
FROM ##TEST
--GROUP BY NAME--,DATE_OF_CALL
ORDER BY NAME ASC

  declare @SQL    nvarchar (4000)
declare @name        nvarchar (1000)
DECLARE @TIME    NVARCHAR(MAX)

  DECLARE db_cursor CURSOR FOR
SELECT NAME,DATE_OF_CALL FROM ##TEST --TEMPTABLE

  OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @NAME,@TIME

  WHILE @@FETCH_STATUS = 0
   BEGIN
     set @sql = 'INSERT INTO ##test1 select TOP 1 '+ @NAME + ','''+@TIME+'''
                 FROM ##TEST A INNER JOIN ##TEST1 B ON A.NAME = B. NAME
                 ORDER BY A.NAME ASC'
     print @sql
     --exec SP_EXECUTESQL @sql
    end

        FETCH NEXT FROM db_cursor INTO @NAME,@TIME
END

  CLOSE db_cursor
DEALLOCATE db_cursor


  but it failed.. 🙁

  Hi Deny,

  You've been around long enough to know that if you post readily consumable data, you'll get answers not only faster, but the answers will be better because people will test their stuff using your data.  Please see the article at the first link in my signature line below for one way to do that (there are a couple of other methods, as well).  Thanks for helping us help you.

  Hai Jeff,,sorry just reply,

  I have found the syntax..
  Here is the result:

  SELECT *
  FROM #Testing_Name
  order by NAME, SPONSOR_NAME, DATE_OF_CALL;

  NAME  SPONSOR_NAME  DATE_OF_CALL
  A  FIFA  2018-08-27 16:45:09.000
  A  FIFA  2018-08-28 09:00:00.000
  A  FIFA  2018-08-28 13:00:00.000
  B  FIFA  2018-08-06 21:50:10.000
  B  VIVA  2018-08-27 16:50:10.000
  B  VIVA  2018-08-28 10:00:10.000
  B  VIVA  2018-08-28 10:50:55.000
  C  VOIP  2018-08-29 08:50:00.000
  C  VOIP  2018-08-30 14:45:00.000
  D  PIVO  2018-08-30 07:00:00.000

  select NAME, min(SPONSOR_NAME), MIN(DATE_OF_CALL) as FIRST_DATE
  from #Testing_Name
  group by NAME
  order by NAME

  NAME  SPONSOR_NAME  FIRST_DAtE
  B  FIFA  2018-08-06 21:50:10.000
  B  VIVA  2018-08-27 16:50:10.000
  C  VOIP  2018-08-29 08:50:00.000
  D  PIVO  2018-08-30 07:00:00.000

• C DBA Lockhart

  SSC Veteran

  Points: 205

  More actions

  October 16, 2018 at 12:52 pm

  #2009941

  I would not want to make any assumptions about SPONSOR_NAME,
  So.. I'd do the following if wanting the first call by Name and Sponsor Name

  SELECT NAME, SPONSOR_NAME, MIN(DATE_OF_CALL) AS FIRST_DATE
FROM #Testing_Name
GROUP BY NAME, SPONSOR_NAME
ORDER BY NAME, SPONSOR_NAME;

  If you want to have the earliest DATE_OF_CALL for each NAME, with the SPONSOR_NAME for that DATE_OF_CALL, then
  WITH CallsByDate
AS (
    SELECT NAME
        ,SPONSOR_NAME
        ,DATE_OF_CALL
        ,ROWNUMBER() OVER (
            PARTITION BY NAME ORDER BY DATE_OF_CALL ASC
            ) AS DocRow
    FROM DBO.TESTING_NAME
    )
SELECT NAME
    ,SPONSOR_NAME
    ,DATE_OF_CALL AS FIRST_DATE
FROM CallsByDate
WHERE DocRow = 1;
  

• TangoVictor

  SSChampion

  Points: 10969

  More actions

  October 16, 2018 at 2:09 pm

  #2009955

  I would first tell her about SQL and show how an Over clause works. That's a sure bet.... 

  Here's an example
  SELECT
ss.NAME
,ss.SPONSOR_NAME
,ss.DATE_OF_CALL
FROM
(SELECT
  *
 ,MIN(DATE_OF_CALL) OVER (PARTITION BY SPONSOR_NAME) AS MinDoc
 FROM
  Testing_Name) ss
WHERE
ss.DATE_OF_CALL = ss.MinDoc;

• sgmunson

  SSC Guru

  Points: 110639

  More actions

  October 18, 2018 at 9:26 am

  #2010244

  Tom Van Harpen - Tuesday, October 16, 2018 2:09 PM

  I would first tell her about SQL and show how an Over clause works. That's a sure bet.... 

  Here's an example
  SELECT
ss.NAME
,ss.SPONSOR_NAME
,ss.DATE_OF_CALL
FROM
(SELECT
  *
 ,MIN(DATE_OF_CALL) OVER (PARTITION BY SPONSOR_NAME) AS MinDoc
 FROM
  Testing_Name) ss
WHERE
ss.DATE_OF_CALL = ss.MinDoc;

  Good luck with that....   The number of gals interested in such topics is rather unfortunately low....

  Steve (aka sgmunson) 🙂 🙂 🙂
  Rent Servers for Income (picks and shovels strategy)