|
Try this
SELECT Sentby,SentOn
FROM tblSentBySentOn
UNION
SELECT AcceptBy,AcceptOn
FROM tblSentBySentOn
WHERE Sentby <> AcceptBy
For grouping the results use this
SELECT A.Sentby, A.SentOn
FROM(
SELECT Sentby,SentOn
FROM tblSentBySentOn
UNION
SELECT AcceptBy,AcceptOn
FROM tblSentBySentOn
WHERE Sentby <> AcceptBy
) A ORDER BY A.SentOn
Hope this helps
Niladri Biswas
modified on Wednesday, July 1, 2009 3:56 AM
|
|
|
|
|
Thank you very much....Thanks for your timely help...
Nothing is Impossible. Keep always Smiling...
modified on Wednesday, July 1, 2009 3:30 AM
|
|
|
|
|
• Atomic Studios just moved to a 15000 square foot facility with 3 times the shooting space of the former studio
• Atomic Studios never charges overtime
• The only studio that offers a 100% completion guarantee. If for what ever reason you do not finish your shoot or you forget a shot, you can come back and shoot for free
• Parking for up to 100 cars
• over 50 linear foot of shootable green screen
• over 80 linear foot of shootable White Cyc
• 48 linear foot of shootable black void
• sign up on our site and receive a complete filmmakers tool kit packed with all the releases forms and printable documents needed to make your next film or video.
• We now can provide over 20 ton of grip and lighting, steadicam, jibs, dollies.
www.atomicstudios.com
323-851-3825
|
|
|
|
|
If you wish to advertise on the site then go through the normal channels - don't spam the forums.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I heard that it doesn't take much in the way of brains to set yourself up as a filmmaker what with monkey point deals and all. Thanks for confirming that rumor with your post.
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
I'm developing a quiz application and i want to access the questions randomly from my dadtabase. Is it possible? please help.
ML Lingwati
|
|
|
|
|
Create the primary key to your questions as an integer (ID), then have the random number generator select the corresponding ID.
|
|
|
|
|
Hi,
The random numbers can be generated in multiple ways.
But in all the cases the questions should have a unique QuestionId( a Primary Key)
Step 1:
Create a table (say tblQuestions) with QuestionId(int Primary Key), Questions(Varchar(50))
Step 2:
Insert some values:
QuestionId Questions
---------------------
1 Q1
2 Q2
3 Q3
4 Q4
5 Q5
6 Q6
7 Q7
8 Q8
9 Q9
10 Q10
11 Q11
12 Q12
13 Q20
14 Q25
15 Q30
Solution 1:
Suppose you have a fixed set of questions (say 1 to 30).
Fire this query
SELECT QUESTIONS
FROM TBLQUESTIONS
WHERE QUESTIONID = ROUND(((30 - 1) * RAND() + 1), 0)
where 30 is the upper limit and 1 is lower limit
Solution 2:
Suppose your question may grow to any length dynamically
Fire this query
SELECT QUESTIONS
FROM TBLQUESTIONS
WHERE QUESTIONID = CAST(RAND() * N AS INT)
Where N is any number.
Means if you have records(i.e. Questions) from 1 to 10, N = 10
if you have records(i.e. Questions) from 1 to 50, N = 50
if you have records(i.e. Questions) from 1 to 100, N = 100 etc.
Solution 3:
For the same above scenario, the following query can also be use
SELECT QUESTIONS
FROM TBLQUESTIONS
WHERE QUESTIONID = ABS(CAST(NEWID() AS binary(6)) %N) + 1
Where N is any number.
Solution 4:
Send the random numbers from your front end application to the backend either in INLINE SQL or in STORED PROC, and retieve the questions based on the QuestionId.
Hope this helps
Niladri Biswas
modified on Tuesday, June 30, 2009 11:26 AM
|
|
|
|
|
How i connect a database in the main server to a client machine i am using SQL 2003
|
|
|
|
|
jamith wrote: How i connect a database in the main server to a client machine i am using SQL 2003
You are? How interesting.
"My interest is in the future because I'm going to spend the rest of my life there." - Charles F. Kettering
|
|
|
|
|
You have that reversed, the client conection to the server - see connectionstrings.com
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Dear All,
I have established transactional with updatable subscription replication among two sql 2005 instance.
Server A as Publisher and Server B as subscriber, both have same db
the questions is how can i keep existing data into server b Database.
i have changed the status for "Action if name is in use to Keep existing object unchanged" in my publication, but still got no success?
your help is really appreciated
Abdul Rahaman Hamidy
Database Developer
Kabul, Afghanistan
|
|
|
|
|
Hi Experts
i Have Data of 2 millions of record i am using C#.NET
as a front end then suggest me which database is better SQL Server or MS Access .we have perform sorting and searching also on this database
.
Dinesh
|
|
|
|
|
I suggest you to chose SQL Server.
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.
|
|
|
|
|
MS Access can support even 2 million of records.
But considering other factors like
Feature SQL Server 2000 Access 2000
database size 1,048,516 TB 2 GB plus
linked tables size
objects in a database 2,147,483,647 32,768
user name length 128 20
password length 128 14
table name length 128 64
column name length 128 64
index name length 128 64
Number of concurrent users limited 255
columns per table 1024 255
table size limited 1 GB
number of indexes in a table 250 32
number of columns in an index 16 10
bytes per row 8060 2000
number of tables in a query 256 32
columns per SELECT statement 4096 255
nested subqueries 32 50
number of enforced relationships 253 32
(obtained from http://www.allinterview.com/showanswers/63402.html[^])
it is better to go with SQL SERVER.
However, cost is also a factor!
But if your requirement is not so high, you can go with SQL SERVER EXPRESS EDITION. which is free
But the license is limited.(hope 10 users)
Hope this answers your question.
Niladri Biswas
|
|
|
|
|
|
Can v create permanent hidden tables in sql 2000 and how?
|
|
|
|
|
Hi,
Though the question is not very clear to me but if you mean that you want to give preference of View access over table access to the users,then the answer is
DENY SELECT ON TABLE TO USER
GRANT SELECT ON VIEW TO USER
If you want to hide the system tables in SQL SERVER 2000, follow this steps:
1) Choose a registered server
2) Right click on that and select Edit SQL Server Registration properties
3)Uncheck
Show system databases and system objects
Hope this helps
Niladri Biswas
|
|
|
|
|
Hi everybody!
I have my own kind of server that for each client keeps an oledb connection opened. It also uses a thread pool for serving the clients. Now let's say the connection between my server and SQL server breaks for whatever reason. While it's down if my server attempts to do anything with the database it obviously fails. Then network issue gets solved.
If my server attempts to continue manipulate the same oledb connection - it still fails. If I reconnect it fails anyway as long as there are those other olddb connections for those other clients of mine. Only when all oledb connections are closed I can start connecting successfully to the SQL server. It seems that in multithreaded/same exe session environment all oledb connections must be closed and reopened in order to recover. Is that true? Or is there a better way to force oledb to revive connections?
Please help!
Thanks a lot in advance.
|
|
|
|
|
Hi guys,
I am having a weird issue, I am trying the following and for some reason varchar(50) value is set to null when I encrypt or decrypt it. If I change it from varchar to nvarchar it works. Our table in the database is already set to varchar to store the encrypted information and can't change it due to licensing issues. Please help, what am I doing wrong?
OPEN SYMMETRIC KEY DSBEncryptionKey DECRYPTION BY CERTIFICATE EncryptDsb
DECLARE @encryptedNum varchar(50) -- varchar doesn't work here, nvarchar works
SELECT @encryptedNum=EncryptByKey(Key_GUID('DSBEncryptionKey '), '111000614');
select CONVERT(varchar(50),decryptbykeyautocert(cert_id('EncryptDsb'), NULL, @encryptedNum))
FYI, my symetric key uses AES_256 algorithm.
Please help!
Sameer
|
|
|
|
|
I have a query that is joining tables and calculating the SUM.
It seems to be working, however; the "SUM" is double what it should be.
Any ideas:
Select, spendaccount, spend.fromcode, spend.keyword,
spend.kwtype, SUM (Rev.trans)
Trans, SUM (rev.newaccounts)
newaccounts, SUM (rev.gross) Gross, SUM (rev.refunds)
Refunds, SUM (rev.net) Net, spend.clicks, spend.spend
From spend, Rev wherespend.fromcode=
rev.fromcode
group by spend.fromcode
Regards,
Hulicat
|
|
|
|
|
Hi,
Which SUM you are talking about?
SUM (Rev.trans)?
SUM (rev.newaccounts)?
SUM (rev.gross)?
SUM (rev.refunds)?
SUM (rev.net) Net?
Niladri Biswas
|
|
|
|
|
Hello and thanks for the reply; I am not sure on the approach but here is what I am trying to do:
Spend table-> spend (SUM'd), clicks, kytype account
Rev table-> transactions, newaccounts, gross, refunds, net (SUM'd),
From spend, Rev wherespend.fromcode=
rev.fromcode
----all in one output--------
Regards,
Hulicat
|
|
|
|
|
I am assuming certain things.
I have created both the tables and has inserted certain values. Please look into it whether you also have the same values or not!
tblSpend
COLUMN NAME DATATYPE
----------- -------
SPENDACCOUNT int
FROMCODE int
KEYWORD varchar(50)
KWTYPE int
CLICKS int
SPEND int
Values in tblSpend are
101 1 KeyWord1 1 1 10
102 2 KeyWord2 1 10 11
103 3 KeyWord3 2 21 15
104 4 KeyWord4 3 22 16
105 5 KeyWord5 5 11 11
106 6 KeyWord6 2 33 12
107 7 KeyWord7 1 111 11
108 8 KeyWord8 2 22 2
109 9 KeyWord9 1 1 1
110 10 KeyWord10 1 2 3
tblRev
COLUMN NAME DATATYPE
----------- -------
NEWACCOUNTS int
GROSS int
REFUNDS int
NET int
TRANS int
FROMCODE int
Values in tblRev are
1 10 20 10 10 1
2 20 30 10 20 2
3 30 40 15 25 3
4 40 50 45 5 4
5 50 100 50 50 5
6 60 400 200 200 6
7 70 100 100 0 7
8 70 100 50 50 7
6 111 300 120 180 6
The Query is
SELECT
S.SPENDACCOUNT, S.KEYWORD,
S.KWTYPE,S.CLICKS,S.SPEND,
D.FROMCODE,D.TRANS,D.NEWACCOUNTS,
D.GROSS,D.REFUNDS,D.NET
FROM (
SELECT
T.FROMCODE,
SUM (R.TRANS) TRANS,
SUM (R.NEWACCOUNTS) NEWACCOUNTS,
SUM (R.GROSS) GROSS,
SUM (R.REFUNDS) REFUNDS,
SUM (R.NET) NET
FROM TBLSPEND T, TBLREV R
WHERE
T.FROMCODE= R.FROMCODE
GROUP BY T.FROMCODE
) D, TBLSPEND S
WHERE S.FROMCODE = D.FROMCODE
OUTPUT:
101 KeyWord1 1 1 10 1 1 10 20 10 10
102 KeyWord2 1 10 11 2 2 20 30 10 20
103 KeyWord3 2 21 15 3 3 30 40 15 25
104 KeyWord4 3 22 16 4 4 40 50 45 5
105 KeyWord5 5 11 11 5 5 50 100 50 50
106 KeyWord6 2 33 12 6 12 171 700 320 380
107 KeyWord7 1 111 11 7 15 140 200 150 50
Hope this helps
Niladri Biswas
modified on Tuesday, June 30, 2009 4:26 AM
|
|
|
|
|
Hello Niladri_Biswas, thanks for your help.
Actually I know what the problem is I am just can't figure out the query.
Example of data a problem.
Fromcode Spend.spend rev.gross
ABC 10 70
ABC 20 50
ABC 10 20
Results look like the following:
rev.gross sum'd + spend.spend
Fromcode spend.spend rev.gross
abc 140 (expected 90)
abc 160 (expected 100)
abc etc
I want the total for each column based on the fromcode but not joined sums if that makes sense.
Thanks and Regards
Regards,
Hulicat
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
