• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 847
  • Last Modified:

order birthday by current month forward

What I need to do is do and orderby on a bunch of birthdays but I need to order by the current month and then forward.  For example.

It would be the current month forward
OCT
NOV
DEC
JAN
FEB  ETC


I really have no idea how to accomplish this.

Select NAME, DOB, FILE, SSN from tblPersonnel
Order by DOB

Open in new window

0
kdeutsch
Asked:
kdeutsch
  • 5
  • 2
  • 2
  • +2
2 Solutions
 
kdeutschAuthor Commented:
ok,
got this to work which order it by the current month of 10, but after this, its not correct

ORDER by CASE WHEN DATEPART(month, dob) = DATEPART(month, getdate()) THEN 1 Else 2 END,
0
 
BrandonGalderisiCommented:
This example is driven off of a date table that I have.  The time_coordinate table is just a static list of dates.  Let's pretend they are birthdays.  So replace theCoordinate with your birthday field and time_coordinate with your table.  The below example assumes that DOB in the current month (even if past) should still appear at the top of the list.


SELECT *
FROM
     (
     SELECT DATEADD(m,DATEDIFF(m,0,tc.theCoordinate),0) AS theMonth,tc.theCoordinate
     FROM util_db.dbo.time_coordinate tc
     WHERE tc.theDimension='day'
       AND tc.theCoordinate >= '2011-01-01'
       AND tc.theCoordinate < '2012-01-01'
     ) a
ORDER BY DATEADD(yy,CASE WHEN a.theMonth < DATEADD(m, DATEDIFF(m,0,GETDATE()),0) THEN 1 ELSE 0 END,a.theMonth),a.theCoordinate
0
 
kdeutschAuthor Commented:
Ok I tried it but it order it by year, month, day.  This is my whole query statment and I pull form a openquery from a sidpers DB.  So I am just rying to do and order by after the fact.


Select      SSN_SM, name_ind, RANK, POSC, AUTH_PARA_DSG, AUTH_LINE_DSG, DOB from
            OPENQUERY(SIDPERS, 'Select      pt.SSN_SM, pt.name_ind, substr(st.GR_ABBR_CODE,1,3) RANK, dt.POSC, dt.AUTH_PARA_DSG, dt.AUTH_LINE_DSG, pt.DOB
                                          from PERS_PERSON_TBL pt
                                                 LEFT JOIN PERS_TBL st on st.mpc = pt.mpc and st.ASG_SEQ_NBR = pt.ASG_SEQ_NBR LEFT JOIN
                                                 PERS_DUTY_POSN_TBL dt on dt.mpc = st.mpc and dt.ASG_SEQ_NBR = st.ASG_SEQ_NBR LEFT JOIN
                                                 PERS_ATCHMT_TBL at on at.mpc = pt.mpc and at.ASG_SEQ_NBR = pt.ASG_SEQ_NBR
                                                 Where pt.REC_PREC = ''99999999'' and st.REC_STAT <> ''M'' AND (dt.UPC IN (''PJPT0'') or at.UPC IN (''PJPT0''))')
                                                ORDER BY DATEADD(month,CASE WHEN dob < DATEADD(m, DATEDIFF(m,0,GETDATE()),0) THEN 1 ELSE 0 END, dob), dob
0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
tim_csCommented:
I think what you had originally was pretty close to working for you.  Try this.


ORDER by CASE WHEN DATEPART(month, dob) >= DATEPART(month, getdate()) THEN 1 Else 2 END, dob
0
 
BrandonGalderisiCommented:
The problem I see is that DOB probably contains the year.  You need to put all of your DOBs in terms of the same year since I assume your intent is to show upcoming birthdays?
0
 
deightonCommented:
select * from 
(SELECT 
      [ForeName1]
      ,[ForeName2]
      ,[Surname]
	,dateadd(year, year(getdate()) - year([DOB]) ,dob) nextbirthday
	,dob
      
  FROM YourTable
  
UNION
SELECT 
      [ForeName1]
      ,[ForeName2]
      ,[Surname]
	,dateadd(year, year(getdate()) + 1 - year([DOB]) ,dob) nextbirthday
	,dob
      
  FROM YourTable) as qq

  where nextbirthday >= getdate()  and nextbirthday < dateadd(year,1,getdate())
  order by nextbirthday

Open in new window

0
 
kdeutschAuthor Commented:
BrandonGalderisi:,

Yes tha is kind of the intent, my goal is to show the current months birthdays first then then show the next months and so on down the line.  Always so that the current months birtdays are first in order.

Oct
Nov
Dec
Jan
Feb
march
0
 
tim_csCommented:
Does this give you what you're looking for?

ORDER by CASE WHEN DATEPART(month, dob) >= DATEPART(month, getdate()) THEN 1 Else 2 END, DATEPART(MONTH,dob),DATEPART(DAY,dob)
0
 
awking00Commented:
order by case when datepart(month,dob) - datepart(month,getdate()) < 0
              then datepart(month,dob) - datepart(month,getdate()) + 12
              else datepart(month,dob) - datepart(month,getdate())
         end
0
 
kdeutschAuthor Commented:
tim_cs:,
I looked at you previous example and it did not work but the last one works just like I want it.  I swear I tried that but it did not work for me, but I don't remember having the >= in there.
0
 
kdeutschAuthor Commented:
Thanks for the help
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 2
  • 2
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now