Re: select upcoming birthday? - Database Discussion Boards

Re: Replicate

Brendan Vogt15-Mar-09 5:54

Brendan Vogt

15-Mar-09 5:54

Thanks guys!!

So how will my membership tables look like, will I have one in each database?

select upcoming birthday?

Meax11-Mar-09 7:13

Meax

11-Mar-09 7:13

i have users Date Of Birth as DOB in sql server table called UsersProfile.

Person DOB
a 3/13/2009
b 3/13/2009
c 3/25/2009
d 4/2/2009
e 4/15/2009

How to select next(upcoming) birthday from this table?

if two persons bday falls on a same date, like Person a and Person b, how to select those two person?

Re: select upcoming birthday?

Syed Mehroz Alam11-Mar-09 7:39

Syed Mehroz Alam

11-Mar-09 7:39

On top of my head, I can think of something like:

Select min(DOB) from UsersProfile where DOB > GetDate()

for getting persons, we can have:

Select Person from UsersProfile where DOB = 
     ( Select min(DOB) from UsersProfile where DOB > GetDate() )

Hope that helps.

Regards,

My Blog
My Articles

Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

Re: select upcoming birthday?

Meax11-Mar-09 8:06

Meax

11-Mar-09 8:06

thnaks for replying, however i am not getting any data with those quaries

Re: select upcoming birthday?

Syed Mehroz Alam11-Mar-09 19:39

Syed Mehroz Alam

11-Mar-09 19:39

Strange.. It works for me. Here are my test statements:

declare @UsersProfile table
(
  Person varchar(20),
  DOB datetime
)

insert into @UsersProfile
  select 'Person1', '2009-03-11'
  union select 'Person2', '2009-03-13'
  union select 'Person3', '2009-03-13'  
  union select 'Person4', '2009-03-16'
  union select 'Person5', '2009-03-25'

select * from @UsersProfile 
  where DOB = ( select min(DOB) from @UsersProfile where DOB>GetDate() )

Are you sure your DOB column is of DateTime type?

Regards,

My Blog
My Articles

Computers are incredibly fast, accurate, and stupid; humans are incredibly slow, inaccurate and brilliant; together they are powerful beyond imagination. - Albert Einstein

Re: select upcoming birthday?

Luc Pattyn11-Mar-09 8:35

Luc Pattyn

11-Mar-09 8:35

Meax wrote:
i have users Date Of Birth

what is it these unborn people are using? D'Oh! | :doh:

Luc Pattyn [Forum Guidelines] [My Articles]

- before you ask a question here, search CodeProject, then Google
- the quality and detail of your question reflects on the effectiveness of the help you are likely to get
- use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets

Re: select upcoming birthday?

Meax11-Mar-09 8:38

Meax

11-Mar-09 8:38

sorry, meant to write 1980

Re: select upcoming birthday?

Luc Pattyn11-Mar-09 8:54

Luc Pattyn

11-Mar-09 8:54

<br />
SELECT TOP 1 * FROM UsersProfile WHERE datepart(dy,DOB)>=datepart(dy,now()) ORDER BY datepart(dy,DOB)

is what I would try to get the next one.

Smile | :)

Luc Pattyn [Forum Guidelines] [My Articles]

Re: select upcoming birthday?

Meax11-Mar-09 9:41

Meax

11-Mar-09 9:41

what if 2 people has same birthday?

like,

Person DOB
a 3/13/1980
b 3/13/1980

how to do it without writing TOP 2

Re: select upcoming birthday?

Luc Pattyn11-Mar-09 10:10

Luc Pattyn

11-Mar-09 10:10

Hi,

Since the first reply suggested something like

Select Person from UsersProfile where DOB = <br />
     ( Select min(DOB) from UsersProfile where DOB > GetDate() )

it must be rather easy to get it like so:

Select Person from UsersProfile where DOB =<br />
     ( whatever it takes to obtain the next birthday )

So I would try:

Select Person from UsersProfile where DOB =<br />
( SELECT min(datepart(dy,DOB)) FROM UsersProfile WHERE datepart(dy,DOB)>=datepart(dy, now()))

BTW: This is PHP/MySQL code that works for me:

$Query="SELECT * FROM $table WHERE DAYOFYEAR(birthday)= ".
    "(SELECT min(DAYOFYEAR(birthday)) FROM $table ".
    "WHERE DAYOFYEAR(birthday)>=DAYOFYEAR(now()))";

Luc Pattyn [Forum Guidelines] [My Articles]

Re: select upcoming birthday?

Meax11-Mar-09 21:10

Meax

11-Mar-09 21:10

How to make this work in sql server

<br />
"SELECT * FROM $table WHERE DAYOFYEAR(birthday)= ".<br />
		"(SELECT min(DAYOFYEAR(birthday)) FROM $table ".<br />
		"WHERE DAYOFYEAR(birthday)>=DAYOFYEAR(now()))";<br />

Re: select upcoming birthday?

Jay Royall11-Mar-09 23:00

Jay Royall

11-Mar-09 23:00

Luc has given you more than enough help, if you can't help yourself from here then maybe you're in the wrong profession?!

Re: select upcoming birthday?

Meax11-Mar-09 23:27

Meax

11-Mar-09 23:27

i am just trying to learn

Re: select upcoming birthday?

Jay Royall11-Mar-09 23:41

Jay Royall

11-Mar-09 23:41

Sure I realise that, I am too, but the best way to learn isn't to get other people to do it for you. Like I said, Luc has offered a great deal of help here, more than enough for you to work out the rest.

If you still can't do it then post your code, I'm sure someone will be able to point you in the right direction.

Re: select upcoming birthday?

je_gonzalez15-Mar-09 16:07

je_gonzalez

15-Mar-09 16:07

SELECT *
FROM UsersProfile
WHERE ((403 + (31 * month(DOB) + day(DOB)) - (31 * month(getdate()) + day(getdate()))) % 403) =
(SELECT MIN(((403 + (31 * month(DOB) + day(DOB)) - (31 * month(getdate()) + day(getdate()))) % 403)) FROM UsersProfile)

Create Table in Oracle 10G

E_Gold11-Mar-09 2:47

E_Gold

11-Mar-09 2:47

Hi How I can make my Table in Oracle 10G ?
in graphically - like in Sql server

thank's in advance

Re: Create Table in Oracle 10G

Blue_Boy11-Mar-09 2:57

Blue_Boy

11-Mar-09 2:57

Did you try to search on google?[^]!

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Re: Create Table in Oracle 10G

E_Gold11-Mar-09 5:36

E_Gold

11-Mar-09 5:36

Thanks for the help
but I need graphics explanation (better with picture) for How to
make Tables and Database.
I know to work with sql server, but today i install the Oracle...and...so much complicated... why Oracle ?? why ???......

Generate Reference Number

musefan11-Mar-09 0:06

musefan

11-Mar-09 0:06

Hi All,

Im creating an application in C# that needs to generate a partially-sequential value for a reference number which is stored in an SQL server database

the reference number will consist of three parts - a year value, a sequential number, and a system definded value

for example, 090001REF

then the next one generated would need to be 090002REF... 090003REF and so on

Ideally the sequence number would be reset at the start of a new year. 093245REF... 100001REF etc.

Can anyone offer a posible solution to how i would need to incorperate this functionality?

Can this be done within the database? or is this something i will need to handle throu my application? also i will need to keep in mind users creating a reference number at the same time, even if it is highly unlikely

Thanks for any suggestions Smile | :)

Life goes very fast. Tomorrow, today is already yesterday.

Re: Generate Reference Number

Blue_Boy11-Mar-09 2:18

Blue_Boy

11-Mar-09 2:18

Hope this will help you

string refnum = "091222REF";//here you get last reference number from database<br />
            string newrefNum = "";<br />
            if (DateTime.Now.ToString("yy") != refnum.Substring(0, 2))<br />
            {<br />
                newrefNum = DateTime.Now.ToString("yy") + "0001REF";<br />
            }<br />
            else<br />
            {<br />
                int incNum = int.Parse(refnum.Substring(2, 4)) + 1;<br />
<br />
                newrefNum = DateTime.Now.ToString("yy") +  incNum.ToString("0000") + "REF";<br />
            }

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

Re: Generate Reference Number

musefan11-Mar-09 2:29

musefan

11-Mar-09 2:29

Thanks for the reply, got me thinking in the right direction Smile | :)

i suppose some sort of TOP specification of 1 while ordering by created date and time would get the row i want.

Do you have any suggesion on how to avoid multiple users trying to add an entry at the same time thou?

What about just having the column as 'unique' then cathing the error and then trying the process again with an incremented reference?
Im not asking if it would be possible, as im sure it is, i just want to know if you can see any holes in the logic that i may have missed.

thanks again

Life goes very fast. Tomorrow, today is already yesterday.

Re: Generate Reference Number

Blue_Boy11-Mar-09 2:55

Blue_Boy

11-Mar-09 2:55

1. You can make unique column of reference number and then in exception you insert new reference number.
example:

try<br />
{<br />
//insret refnumber<br />
}<br />
catch<br />
{<br />
//get last inserted number in table and then genereate new refnumber<br />
}<br />
finally<br />
{<br />
//close connection or do some else<br />
}

2. You can select last inserted refnumber and then generate new refnumber.
Example:

select top 1 refnumber from mytable order by id desc //ID column must be inc autonumber

string refnum = "091222REF";//here you have last inserted reference number in database<br />
string newrefNum = "";<br />
if (DateTime.Now.ToString("yy") != refnum.Substring(0, 2))<br />
{<br />
newrefNum = DateTime.Now.ToString("yy") + "0001REF";<br />
}<br />
else<br />
{<br />
int incNum = int.Parse(refnum.Substring(2, 4)) + 1;<br />
<br />
newrefNum = DateTime.Now.ToString("yy") + incNum.ToString("0000") + "REF";<br />
}

I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.

T-SQL and XML question.

Jamie Nordmeyer10-Mar-09 17:11

Jamie Nordmeyer

10-Mar-09 17:11

A website I'm writing uses jQuery and a plugin called Flexigrid, that expects an XML document in this format:

<rows>
   <page>1</page>
   <total>7</total>
   <row id="1">
      <cell>1</cell>
      <cell>SomeName</cell>
      <cell>SomeDescription</cell>
   </row>
   <row id="2">
      <cell>2</cell>
      <cell>SomeName</cell>
      <cell>SomeDescription</cell>
   </row>
</rows>

I managed to make it work with the following SQL:

Select
	1 As 'page'
	,7 As 'total'
	,(
		Select
			ID As '@id'
			,(Select ID As 'cell' From Table Where ID = dc.ID For Xml Path(''), Type)
			,(Select [Name] As 'cell' From Table Where ID = dc.ID For Xml Path(''), Type)
			,(Select [Description] As 'cell' From Table Where ID = dc.ID For Xml Path(''), Type, Elements XsiNil)
		From
			Table dc
		For Xml Path('row'), Type
	)
For Xml Path('rows'), Type

Is there a better way to do this? I've scoured Google, and wasn't able to find anything. This works, but it seems like there must be a better way that I'm just not finding.

Thanks.

Jamie Nordmeyer
Portland, Oregon, USA
http://www.feralcodemonkies.com

Random or sequential allocation!

Learner52010-Mar-09 14:06

Learner520

10-Mar-09 14:06

hi everybody,
i have two tables student and supervisor

1-Student
studentId studentname
1 john
2 paul
3 Martin
4 tony
.
.
.
14 Mike

2-Supervisor
SupervisorId supervisorName
1 Mark
2 Rodney
3 Wilson
4 Sarah
5 Raj
6 Neil
7 David

i hav 14 students and 7 supervisor i have to allocate each supervisor to 2 students. please could anyone tell me how i can allocate 1 supervisor to 2 sutdents or how can i display output as Allocation Table

like this
Allocation Table
AllocationId Student Supervisor
1 1 1
2 2 1
3 3 2
4 4 2
5 5 3
6 6 3
7 7 4
. . .
. . .
. . .
13 13 7
14 14 7

thanks for any kind help in adv.
regards
learner

Re: Random or sequential allocation!

Ashfield10-Mar-09 22:04

Ashfield

10-Mar-09 22:04

So didn't you understand the answer the first time you asked? If not, try responding to the original post, not starting a new one.

Bob
Ashfield Consultants Ltd
Proud to be a 2009 Code Project MVP

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.