|
select convert(varchar(10),status.Logintime ,101) as Date,empcode,empname,count(*) as [No of Calls]
from status join empmaster on empmaster.empid = status.empcode
where empmaster.Permission <> '1' and logintime between '06/02/2006' and '06/03/2006'
group by empcode,empname, convert(varchar(10),status.Logintime ,101) order by empcode asc
select convert(varchar(10),status.Logintime ,101) as Date,empcode,empname,count(*) as [No of Contacts]
from status join empmaster on empmaster.empid = status.employeecode
where empmaster.Permission <> '1' and logintime between '06/02/2008' and '06/03/2008' and
statuscode not in (11)group by empcode,empname, convert(varchar(10),status.Logintime ,101) order by emp asc
i have these two queries.the problem is how to join these queries?
can any one help me.if i write two queries as one,data of two columns ( [no of calls] and [no of contacts]) resulting same count.
|
|
|
|
|
use UNION
select convert(varchar(10),status.Logintime ,101) as Date,empcode,empname,count(*) as [No of Calls]<br />
from status join empmaster on empmaster.empid = status.empcode<br />
where empmaster.Permission <> '1' and logintime between '06/02/2006' and '06/03/2006' <br />
group by empcode,empname, convert(varchar(10),status.Logintime ,101) order by empcode asc<br />
<br />
UNOIN<br />
<br />
select convert(varchar(10),status.Logintime ,101) as Date,empcode,empname,count(*) as [No of Contacts]<br />
from status join empmaster on empmaster.empid = status.employeecode<br />
where empmaster.Permission <> '1' and logintime between '06/02/2008' and '06/03/2008' and<br />
statuscode not in (11)group by empcode,empname, convert(varchar(10),status.Logintime ,101) order by emp asc
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Use "Union All" instead of "Union" if your 2 queries doesn't return duplicates.
|
|
|
|
|
Yes you are right, I forgot to write UNION ALL in both two places on my first response.Thanks for correcting me dude.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
No problem. You beat me to the solution anyhow
|
|
|
|
|
if u wants to run both qries one after another
then the Solution is....
SqlQry 1
Go
Sqlqry 2
Go
.
.
.
.
Try Go Statement in SQL it will help you...
Enjoy your coding..
|
|
|
|
|
Hi guys,
I have the following query, example
Select a.id, b.description
from table1 a
left join (select top 1 c.id, c.description from tableb c where c.id = a.id) as b on b.id = a.id
The reason I do this is because tableb could have multiple duplicates of c.id and I want it to only do the select on one specific id from the table1.
But I get the error because a.id is not within the select statement of tableb.
Is there any way that I can do this, if I remove the where clause in the tableb select statement it returns duplicate values on the description.
Thanks in advance
No matter how long he who laughs last laughs, he who laughs first has a head start!
|
|
|
|
|
Try this
Select a.id<br />
(select top 1 c.id from tableb c where c.id = a.id) ,<br />
(select top 1 c.description from tableb c where c.id = a.id) <br />
from table1 a
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Works, Thanks
No matter how long he who laughs last laughs, he who laughs first has a head start!
|
|
|
|
|
No problem,I am glad that worked for you.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
I have to be able to select het highest number smaller than a certain number
for example I have in DB
1
2
5
6
7
and I want the highest number smaller than 5 (in this case 2) but can't seem to get this working (at least not in 1 sql statement)
I can get the numbers 1 and 2 (is easy) but want to get only the number 2
any help would be appriciated
|
|
|
|
|
TDDragon wrote: I have to be able to select het highest number smaller than a certain number
for example I have in DB
1
2
5
6
7
and I want the highest number smaller than 5 (in this case 2) but can't seem to get this working (at least not in 1 sql statement)
I can get the numbers 1 and 2 (is easy) but want to get only the number 2
any help would be appriciated
Select Max(ID) where ID<5
Does that work for you?
|
|
|
|
|
I tried this yesterday but it returned an empty row
tried it again today and now I do get an value (think my mind is playing tricks on me )
or the fact that I had a second column in the select statement was cousing trouble I don't know anyway it works now
Thank you for you time
|
|
|
|
|
ChandraRam is correct, but you can use this query too:
select top 1 id from tablename where id < 5 order by id desc
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Wonder why I didn't think of that...
|
|
|
|
|
Hi everyone,
I am developing a asp.net c# application. I am a beginner to MSMQ. I need to save the data(username, password) into msmq. In the particular period of time, I need to transfer that data(username, password) to MySQL database due to making the fast data access. How is it possible. Please help me regarding this. The best elaborated answer with some examples would be appreciated.
Thanks in Advance,
J. Mohan.
I see the God Face in Helping people
|
|
|
|
|
i do not how it is in mysql but in MS SQL you can use stored procesure..
if you want to knw abt that tellme...
|
|
|
|
|
I developing a software in which I am listing products.
On product name there is link which shows product details on the basis on product name passed to details page.
Some product contain name with % symbol shows error in details page. Other product details are coming fine.
How to resolve this problem. Please suggest.
|
|
|
|
|
Poonam Devi wrote: Some product contain name with % symbol shows error in details page
What is the error?
|
|
|
|
|
CAn you Explore it more with Code....
I can try on it.....
By
|
|
|
|
|
as far as the backward compatibility is concerned , how can i differentiate the ms access and sql server , on the basis of this feature?
If you have an apple & I have an apple and we exchange our apples, then each of us will still have only one apple but if you have an idea & I have an idea and we exchange our ideas, then each of us will have two ideas!
|
|
|
|
|
Sorry, I don't understand your question. What are you trying to find out?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Does any one know how I would get some data from a MsSQL database and upload it to MySQL hosted server
Situation
I’m running Symantec backup exec on a few severs and they email me notifications of what’s happened in the back up but there are too many to start filling away!!! The program uses MsSql to store the information I want to upload it to mysql hosted sever. I can tell backup exec to run a command after backup
any ideas guys
|
|
|
|
|
Have you tried SSIS?
“If we are all in agreement on the decision - then I propose we postpone further discussion of this matter until our next meeting to give ourselves time to develop disagreement and perhaps gain some understanding of what the decision is all about.”-Alfred P. Sloan
|
|
|
|
|
Please tell me how to add a database created in sql server database which is stored in default folder of Microsoft SQL Server to a my folder(created by me)?
|
|
|
|