none
Complex logic to be implemented in SQL - Please help!

    Question

  • Hi,
       I have a student table which has columns as StudentID, Enroll_date and class. If he is enrolled on Jan 2013 for Dance and then enrolled on May 2013 for Swimming means, from Jan to April, he was into Dance class.
    Similarly we want the output till the current month (august).

    I have given the i/p and the o/p. Can you please suggest me how to derive this using SQL?


    • Edited by vskindia Wednesday, August 28, 2013 7:56 PM edit
    Wednesday, August 28, 2013 7:56 PM

Answers

  • 1. Have a calendar table

    2. I assume you have actual Date column in your table instead of the formatted date as you showed

    ;with StudentSchedule as (select SE1.StudentID, SE1.Enroll_Date as Start_Date, SE1.Class, Coalesce(SE2.Enroll_date, CURRENT_TIMESTAMP) as End_Date FROM dbo.StudentEnrollments SE1 OUTER APPLY (select top (1) * from dbo.StudentEnrollments SE2 where SE1.StudentID = SE2.StudentID and SE2.Enroll_Date > SE1.Enroll_Date

    order by Enroll_Date) SE2) select DISTINCT S.StudentID, LEFT(datename(month, Cal.The_Date), 3) +

    RIGHT(datepart(year, Cal.The_Date),2) as Enroll_Date,

    S.Class from StudentSchedule S

    INNER JOIN dbo.Calendar Cal on Cal.The_Date between S.Start_Date and S.End_Date


    For the Calendar table creation script check this blog post

    Why should I consider a Calendar table?

    Also, the script above is from the top of my head (not tested), but should give you an idea.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi NModerator Wednesday, August 28, 2013 8:13 PM
    • Marked as answer by vskindia Wednesday, August 28, 2013 10:58 PM
    Wednesday, August 28, 2013 8:12 PM
    Moderator

All replies

  • 1. Have a calendar table

    2. I assume you have actual Date column in your table instead of the formatted date as you showed

    ;with StudentSchedule as (select SE1.StudentID, SE1.Enroll_Date as Start_Date, SE1.Class, Coalesce(SE2.Enroll_date, CURRENT_TIMESTAMP) as End_Date FROM dbo.StudentEnrollments SE1 OUTER APPLY (select top (1) * from dbo.StudentEnrollments SE2 where SE1.StudentID = SE2.StudentID and SE2.Enroll_Date > SE1.Enroll_Date

    order by Enroll_Date) SE2) select DISTINCT S.StudentID, LEFT(datename(month, Cal.The_Date), 3) +

    RIGHT(datepart(year, Cal.The_Date),2) as Enroll_Date,

    S.Class from StudentSchedule S

    INNER JOIN dbo.Calendar Cal on Cal.The_Date between S.Start_Date and S.End_Date


    For the Calendar table creation script check this blog post

    Why should I consider a Calendar table?

    Also, the script above is from the top of my head (not tested), but should give you an idea.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    • Edited by Naomi NModerator Wednesday, August 28, 2013 8:13 PM
    • Marked as answer by vskindia Wednesday, August 28, 2013 10:58 PM
    Wednesday, August 28, 2013 8:12 PM
    Moderator
  • Thanks..

    I got the error "Invalid column name 'The_Date'", while executing the error.Then I changed the The_Date column to Dt and it ran without error.

    Everhthing is good. But the only issue is for the changing month , its getting both the values. ie)  for the Month of Apr, I am getting both the records - Dance and Swimming. But I should get only one. Same is the case for June month - its getting both the values, Swimming and Karate. It should be only Karate.

    If this would be resolved, it would be really helpful. PFB the scrrenshot.



    • Edited by vskindia Wednesday, August 28, 2013 9:01 PM e
    Wednesday, August 28, 2013 9:00 PM
  • Oops, sorry, I had an idea in mind when I was writing this code, but forgot in the process of writing.

    Change this Coalesce(SE2.Enroll_date, CURRENT_TIMESTAMP) as End_Date

    into

    Coalesce(dateadd(month, -1,SE2.Enroll_date), CURRENT_TIMESTAMP) as End_Date,

    This should resolve the problem. Funny that this is what I meant and forgot :)


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, August 28, 2013 9:56 PM
    Moderator
  • GREAT! thanks a ton!

    I want the Elig_Date in YYYY-MM-DD format. Eg. if it is Jan-01-2013, I want to get as 2013-01-01.

    I am trying the below expression and its giving as 2013-1-1. Can you please tell me how to get 2013-01-01 instead of 2013-1-1, then I would  close the thread by marking this as Answer.

    RIGHT(datepart(year, Cal.Dt),4) +'-'+ LEFT(datePART(month, Cal.Dt),2)+'-'+ LEFT(datePART(DD, Cal.Dt),2) as ELIG_DATE

    thanks in advance!

    Wednesday, August 28, 2013 10:24 PM
  • declare @d date = current_timestamp
    
    select convert(varchar(10), @d, 120)
    Check Cast & Convert article.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    Wednesday, August 28, 2013 10:31 PM
    Moderator
  • sorry for bothering you again. I tried this expression below, then the result  went to unexpected counts.

    RIGHT(datepart(year, Cal.Dt),4) +'-'+ CONVERT(CHAR(2),CAL.dt,1)+'-' + CONVERT(CHAR(2),cal.dt,3) as ELIG_DATE   ---> gave 738 records

    but

    LEFT(datename(month, Cal.Dt), 3) + RIGHT(datepart(year, Cal.Dt),4) as ELIG_DATE ---> gave 23 records which is correct.

    can you tell me where is the issue pls?

    Wednesday, August 28, 2013 10:43 PM
  • If you're using the actual date, then the result will be different for all the dates.

    Post your latest code for revision. Also, I need to leave for ~ 1 h. now so don't expect immediate response from me.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Wednesday, August 28, 2013 10:46 PM
    Moderator
  • Instead of + CONVERT(char(2), cal.dt) try 

    + '01' (if you need to use first of the month).

    Otherwise your result will be every date, not just month.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    • Marked as answer by vskindia Wednesday, August 28, 2013 10:57 PM
    • Unmarked as answer by vskindia Wednesday, August 28, 2013 10:59 PM
    Wednesday, August 28, 2013 10:48 PM
    Moderator
  • Thanks a lot! thank much for your valuable inputs!
    Wednesday, August 28, 2013 10:58 PM
  • I created a new TechNet WiKi article for this problem and also shared another simpler SQL 2012 solution.

    Please take a look and feel free to improve this article or comment:

    http://social.technet.microsoft.com/wiki/contents/articles/19443.t-sql-applying-apply-operator.aspx


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Friday, August 30, 2013 2:41 AM
    Moderator