|
Ah, I now understand your issue.
Here is one last suggestion: Replicate your user/authentication tables.
The idea behind this is that it won't cause any change to your existing application logic and if the time comes where you want to sell one of your websites, you could just stop the replication. I'm not too afraid of performance problems because I doubt there is much write activity to these authentication tables and read access won't impact the replication process. The other upside to this is that you can bring a website down for maintenance without impacting the other website.
My 2 cents.
|
|
|
|
|
Thanks guys!!
So how will my membership tables look like, will I have one in each database?
|
|
|
|
|
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?
|
|
|
|
|
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
|
|
|
|
|
thnaks for replying, however i am not getting any data with those quaries
|
|
|
|
|
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
|
|
|
|
|
Meax wrote: i have users Date Of Birth
what is it these unborn people are using?
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
|
|
|
|
|
sorry, meant to write 1980
|
|
|
|
|
<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
|
|
|
|