|
<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.
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
|
|
|
|
|
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
|
|
|
|
|
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]
- 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
|
|
|
|
|
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 />
|
|
|
|
|
Luc has given you more than enough help, if you can't help yourself from here then maybe you're in the wrong profession?!
|
|
|
|
|
i am just trying to learn
|
|
|
|
|
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.
|
|
|
|
|
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)
|
|
|
|
|
Hi
How I can make my Table in Oracle 10G ?
in graphically - like in Sql server
thank's in advance
|
|
|
|
|
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.
|
|
|
|
|
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 ???......
|
|
|
|
|
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
Life goes very fast. Tomorrow, today is already yesterday.
|
|
|
|
|
Hope this will help you
string refnum = "091222REF";
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.
|
|
|
|
|
Thanks for the reply, got me thinking in the right direction 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.
|
|
|
|
|
1. You can make unique column of reference number and then in exception you insert new reference number.
example:
try<br />
{<br />
}<br />
catch<br />
{<br />
}<br />
finally<br />
{<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";
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.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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
|
|
|
|
|
hello everybody,
i have two tables Student and Supervisor
StudentId StudentName
1 john
2 paul
3 britney
4 Mike
.
.
.
.
13 beckham
SupervisorId SupervisorName
1 Martin
2 Tony
3 Janet
4 Brad
5 johnson
6 David
7 Raj
how can i allocate 7 supervisor to 13 students equally.
any help would be appricaited.
regards learner
|
|
|
|
|
I guess your table Student should contain column which have any value from Supervisor.
Your Student table have to looks like this :
StudentId StudentName SupervisorId <br />
1 john 1<br />
2 paul 1<br />
3 britney 3<br />
4 Mike 5<br />
.<br />
.<br />
.<br />
.<br />
13 beckham 7
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.
|
|
|
|
|
I've got an issue that I'd like to find a better solution to,
I'm inserting a new row into a table, which fires of a trigger that does a set of inserts to replace something that used to be done manually. Previously I had been using @@Identity to retrieve the ID of the new row I inserted, however since I added the trigger I was getting the ID of the last item the trigger inserted.
I've found SELECT IDENT_CURRENT('tbl'), which works, but leaves me concerned with potential concurrency issues.
Any better methods out there or should I consider killing off the trigger to ensure the correct ID will always be returned?
|
|
|
|
|
Read up on Scope_Identity() in BOL. This is a classic error when using triggers.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello,
I have 2 admin users - say Admin1 and Admin2 - on a XP home edition. I installed my application on a machine using Admin1 login. The application runs smoothly.
Now, i logged off and logged in to the same machine using Admin2 credentials and try to launch the application. But the application could not be launched as the database connection could not be established. The application uses SQL express as the client side database. I have verified that the SQL express service is running on the client.
I tried to reproduce the same case on XP professional (SP2 and SP3 both) by creating 2 admin users. But could not reproduce the issue. The application works wonderfully with both the logins.
Is there anything with SQL Express that is clashing with XP Home???
Do I need to do some special settings on XP Home for SQL Express to work for all admin users ???
Any help would be highly appreciated.
TIA
~Shrikant
|
|
|
|
|
Hi,
Just a little advice needed here. During university I studied databases extensively which included the use of composite primary keys. Now, since leaving university a year or so ago and working in the industry, I haven't come across the use of a composite primary key anywhere. And what's more, I have come across databases where I believe that a composite primary key, using existing data, is the right choice for the table, a single unique field has been added to be used as the primary key.
My question is this, are composite primary keys a good or bad idea? I always thought that they were a good idea but noticing the lack of them in real world applications I beginning to think twice!
|
|
|
|
|
Well, it's debated. The down side of a composite key is when it comes to joins and the like, there more fiddly and take longer. By joining on just a single field your queries are quicker. Some people always have a single field primary key, and the the composite key which you're thinking of as a unique index constraint on the table.
Personally, I don't see anything wrong with a composite primary key. It makes a lot of sense logically and saves a column worths of data.
Perhaps just a question of choice or style.
Regards,
Rob Philpott.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
