|
hi i m ajay rathi..i m facing a problem in group by clause in sql query..plz any one of u help me
The problem is... following are 3 queries and i need to combine all three queries into one query..because this time these queries are giving me data in three table and i am showing this data in repeater in ASP.NET with SQL SERVER 2003..
select count(*)as active from tbl_Post_Job where Company_Id_Fk = 60
and Status = 'Active' and Post_Date between '01/01/2007' and '01/01/2008' group by year(Post_Date), month(Post_Date)
select count(*)as billed from tbl_Post_Job where Company_Id_Fk = 60
and Status = 'Inactive' and Post_Date between '01/01/2007' and '01/01/2008'
group by year(Post_Date), month(Post_Date)
select count(*)as billed , month(Post_Date) as month_date ,year(Post_Date)as year_date from tbl_Post_Job where Company_Id_Fk = 60 and Status='Inactive' group by year(Post_Date), month(Post_Date)
actually the main problem i m facing is this that when i need month and year together and active postion and billed position saperatlly..
actually i need this data in following form...
Date | Active | Billed
------------------------------------
9,2007 | 4 | 8
10,2007 | 7 | 2
12,2007 | 6 | 6
------------------------------------
please if any one of you can help me it's very good for me plzzzzzzzzzzzzzz
thanks
Ajay Rathi
software engineer
NOIDA(UP),INDIA
|
|
|
|
|
Hi Ajay
Try something like:
select month(Post_Date) as Post_Month,
year(Post_Date) as Post_Year,
sum(case when Status = 'Active' then 1 else 0 end) as Active,
sum(case when Status = 'Inactive' then 1 else 0 end) as Billed
from tbl_Post_Job
where Company_Id_Fk = 60
group by year(Post_Date), month(Post_Date)
order by year(Post_Date), month(Post_Date)The case expressions allow you to count the number of rows that match their criteria.
Regards
Andy
If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".
|
|
|
|
|
thx a lot of thx it's working
u gave me a big support....
but i have one more problem related to this problem...actually when i m using that query which u gave me that is not working when i m using a sub query in that query ...
following is that query..
sum(case when Type_of_view like 'Applicants' and Posting_Id_Fk in
(select Id_PK from tbl_Post_Job where Company_Id_Fk = @Company_Id_Fk and Job_Title like @Job_Title and Job_Type like @Job_Type and Job_Location like @Job_Location ) then 1 else 0 end) as Applicants,
in this query i know the value of @Company_Id_Fk, @Job_Type, @Job_Location ,@Job_Title
Ajay Rathi
software engineer
NOIDA(UP),INDIA
|
|
|
|
|
Hi,
I have 3 tables: City_master(cityid(PK),cityname), zone_master(zoneid(PK),zname,cityid(FK)) and city_x_zone(czid(PK),cityid(FK),zoneid(FK).
I am running a join query to get the cityid and zone id into city_x_zone.the query is something like this:
select a.cityid,b.zoneid from city_master as a inner join zone_master as b on a.cityid=b.cityid where cityname='tb1.text' and zname='tb2.text'
I am running an insert query for city_x_zone like this:
insert into cityzone values(newid(),select a.cityid,b.zoneid from city_master as a inner join zone_master as b on a.cityid=b.cityid where cityname='Bangalo' and zname='south')
this is throwing an error.
What can be the solution to this?
Regards,
Anuradha
|
|
|
|
|
Try something like:
insert into cityzone
select a.cityid,b.zoneid from city_master as a inner join zone_master as b on a.cityid=b.cityid where cityname='Bangalo' and zname='south'
|
|
|
|
|
Hi All,
Iam having two tables
table1
1.Rno Primary key
2.name
3.empid foreign key of table2
table2
1. empid primary key
2.address
3.Rno foreign key of table1
Here Rno and empid are auto generated. Now i want to insert data into first table i need sql query for this.Please help me on this
i want to join this group
|
|
|
|
|
veereshIndia wrote: i want to insert data into first table i need sql query for this
INSERT INTO tblEmployee(
EmpolyeeID,
EmployeeName)
VALUES(
@EmpolyeeID,
@EmployeeName)
Regards
J O H N ![Rose | [Rose]](https://www.codeproject.com/script/Forums/Images/rose.gif)
"Even eagles need a push." David McNally
|
|
|
|
|
Hi,
Please see my question once again.
Regard's
veeresh
i want to join this group
|
|
|
|
|
To Insert Into First Table
INSERT INTO tblEmp1(
EmpID,
EmpName
)
VALUES(
@EmpID,
@EmpName
)
To Insert Into Second Table
<code>INSERT INTO tblEmp2(
RowID,
EmpAddress,
)
VALUES(
@RowID,
@EmpAddress
)</code>
Regards
J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
both insert statements results in violation of foreign key constraint
Regards
KP
|
|
|
|
|
Table Relationship's are Violating the Normalization Rule.
Regards
J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
disable constraint then a dummy master data.
use the same while inserting into transaction table.
Regards
KP
|
|
|
|
|
Inserting Into these table's are possible only If one of the table's Foreign Key column Allows Null Value.
Regards
J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
You have two foreign keys, each referencing the primary key of the other table. Why do you have this circular reference?
Makes inserting records a little awkward...
|
|
|
|
|
You are right.
Regards
J O H N
"Even eagles need a push." David McNally
|
|
|
|
|
I know 
|
|
|
|
|
Hi,
Thanks for giving answer.But my application requires this things.Please help me
on this.
i want to join this group
|
|
|
|
|
I can't understand an application that would require this design, it seems fairly fundamentally flawed.
Think about it - if your foreign key fields can't accept nulls and you are trying to insert a record:
1. Try to insert a record into A, fails because corresponding field does not exist in B.
2. Try to insert a corresponding record into B, fails because initial record does not exist in A.
3. Keep trying because it doesn't work.
It seems like you are trying to enforce a one to one relationship between these two tables - why not combine them into a single table?
|
|
|
|
|
Hi,
I will try to combine into only one table.Thanks a lot.
Veeresh
i want to join this group
|
|
|
|
|
It looks like your purpose is to have roles list ant employees list and you need some how to assign roles to employees.
If this is the case - I would have done this with three Tables.
TableEmps -- empId, empName (PK - empId)
TableRoles -- roleId, RoleName (PK - roleId)
TableRoleToEmp -- roleId, empId (FK1 roleId ref - TableRoles.roleId, FK2 empId ref - TableEmps.empId)
|
|
|
|
|
Hi..
I have been assigned to make a database in some remote machine.The machine has MSSQL 2005.I need to connect to that remote machine's sql server via my pc using my SQL Management express.I do have that computer's IP address(say,123.456.789.000),and the username and password.But when i try to connect to the server ,I get the following error:
Cannot connect to 123.456.789.000\sqlexpress
An error has occured while establishing a connection to the server.When connecting to SQL server 2005,this failure may be caused by the fact that unser the default settings SQL server does not allow remote connections,(provider:SQL Network Interfaces,error:26-Error Locating Server/Instance specified)(Microsoft SQL Server)
I am a new programmer and dont know much.But i guess the problem is not in my server but in that server to which i am trying to connect.I guess that server has some instance created or it does not allow remote connections.But i am not sure.Is it so?Or is anything wrong in my server.Any ideas?
Thanks
confused
|
|
|
|
|
I too had same issue like this
I installed SQL Server SE, on an XP machine. How other users of same domain can access SQL server using SQL Server Express Edn.
I did create new logins for user, windows authentication. But whenever user try to connect it show message like
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error locating Server/Instance Specified).
How to get rid of this issue. Do i need to set/change in WInodws user accounts?
|
|
|
|
|
Does this issue occur with the SqlServer service started on the remote computer?
|
|
|
|
|
I could login from the local machine. When when other user try to login it show error.
It seems that Windows Authentication issues are there.
What all things to do in the SQL Ser. Standard Edition ( XP ) to enable remote login
( like user permission, adding new login , etc etc )
|
|
|
|
|
Contact the DBA responsible for the server and ask them to use SQL Server Surface Area Configuration tool to check whether SQL Server is configured to allow remote connections.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
