|
For example, given a table, I want to group it every 10/20/30 records per group.
One medthod is that create one view for each cases, in the example, I need create 3 views.
Because the views for the same exception the count of records per group, how to unify them
(take the count per group as a parameter)?
Thanks.
system
|
|
|
|
|
You cannot use a parameter with a view, it needs to be a stored proc.
Having said that you can do something silly like store the value in a settings table and then create a view that uses that value in the where clause thereby allowing you to change the grouping from elsewhere. Seems like another weird requirement to me, but you often see them here.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi!
I am facing problem in INSERT statement of SQL 2005 express. Actually I want to save data from one table to another two tables using SQL statement. I want to do something like that, when I will press Create button from DataGride Column. The data of that row of that Registration table will insert in some other table like Account and Directory. I have tried but I cannot do it. Please help me if anybody can.
|
|
|
|
|
Structure of two tables must be same for code down below,if structure is not same then in select command you have to specify columns which corresponding with destionationtable.
Insert into DestionationTable select * from SourceTable
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.
|
|
|
|
|
there ae three tables, i want to save data from one table to other two tables using SQL query. Please help me
|
|
|
|
|
He did, gave you the exact syntax for copying data from 1 table to another, surely you can work out how to copy from 1 table into 2 destination tables (hint call the script twice)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I have give clue how to solve problem but sometimes some people are lazy and want ready solutions from others. 
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.
|
|
|
|
|
do what blue_boy said. 
it´s simply and fast but you can do also as following, may need in future
declare @id_t1 int
set @id_t1 = (select id from my_table1 where id = @whatever)
declare @f_name_t1 varchar(50)
set @_name_t1 = (select f_name from my_table1 where id = @whatever)
declare @l_name_t1 varchar(50)
set @l_name_t1 = (select l_name from my_table1 where id = @whatever)
insert into my_table2 (id,f_name,l_name) values (@id_t1,@f_name_t1,@l_name_t1)
nelsonpaixao@yahoo.com.br
trying to help & get help
|
|
|
|
|
Thanks!
I think it will help me
|
|
|
|
|
When I run the following query
SELECT COUNT(*) AS Expr1
FROM Current
GROUP BY Amount
HAVING (Amount > .09)
I get two answers. I need to either add them together somehow in the SQL statement or get SQL to only return the total rather than placing them on separate rows.
|
|
|
|
|
SELECT .... FROM ... GROUP BY Amount will return the count(s) for each group. i mean
amount1 count1
amount2 count2
amount3 count3
amount4 count4
amount5 count5
...
Then, HAVING is applied to that. So, let's say that amount1 is > .09 and amount4 is > .09; the result becomes
count1
count4
which is the result you query returns.
If you want to get the count of rows whose Amount is > .09, you should
SELECT COUNT(*) AS Expr1
FROM Current
WHERE Amount > .09
Eslam Afifi
|
|
|
|
|
Hey Guys,
I have a table similar to the follwing structure:
AID BID
1 1
2 1
3 2
I need to get all AIDs where count(AID)>1 when grouping by BID
so in the above sample, query will only return AIDs 1 and 2 but will not return 3 because only one AID is in BID 2.
Your help is appreciated.
Modified:
is there a better sollution other then using:
AND EXISTS (
SELECT COUNT(AID)
FROM TableA
GROUP BY BID
HAVING COUNT(AID) >1
)
|
|
|
|
|
I don't know if this is better or not.
SELECT AID FROM TableA WHERE BID IN
(SELECT BID
FROM TableA
GROUP BY BID HAVING Count(AID) > 1);
Eslam Afifi
|
|
|
|
|
|
Glad to help.
Eslam Afifi
|
|
|
|
|
tblVisitor is a list of visitors that is linked to a record in the tblEmployee table and what I need to do is select the last visitor based on the ClockedIn column, which is a DateTime.
So my question is how do I insert this :-
SELECT TOP 1 * FROM tblVisitors WHERE intEmpID = tblEmployee.intEmpID ORDER BY ClockedIn DESC
into this SELECT statement :-
SELECT TOP (100) PERCENT employee.intEmpID AS ID, employee.strEmpID AS CardReference,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
Employee.strTitle + ' ' + Employee.strForename + ' ' + Employee.strSurname
END AS Visitor,
Visitor.Company,
Visitor.VisitorCardEmpId AS ResponsiblePersonnel,
CASE Employee.intStatus
WHEN 1 THEN
-1
ELSE
0
END AS ClockedIn,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
Visitor.TimeIn
END As ArrivalTime
FROM tblEmployee AS employee LEFT OUTER JOIN
dbo.tblVisitors AS Visitor ON Visitor.intEmpId = employee.intEmpID
WHERE (employee.strEmpID LIKE 'V%')
ORDER BY CardReference
So that the result is one row pre employee.
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Not sure I quite understand, but how about something like
.... Where Clockedin = (select max(clockedin) from tblvisitors)
i.e. you are selecting the latest clocked in time and using that to match back to your visitor.
BTW, why the SELECT TOP (100) PERCENT .., surely thats just selecting them all, but causing the server extra work?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Bob,
Thanks very much, you pointed me in the right direction. I did n't spot the SELECT TOP (100) percent, which was added by the View designer.
My final solution if anyone is interested :-
SELECT DISTINCT employee.intEmpID AS ID, employee.strEmpID AS CardReference,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
Employee.strTitle + ' ' + Employee.strForename + ' ' + Employee.strSurname
END AS Visitor,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
(SELECT TOP 1 Company FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC)
END As Company,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
(SELECT TOP 1 VisitorCardEmpId FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC)
END As ResponsiblePersonnelID,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
dbo.fnGetEmployeeName((SELECT TOP 1 VisitorCardEmpId FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC))
END As ResponsiblePersonnel,
CASE Employee.intStatus
WHEN 1 THEN
-1
ELSE
0
END AS ClockedIn,
CASE WHEN Employee.intStatus = 0 THEN
NULL
ELSE
(SELECT TOP 1 TimeIn FROM tblVisitors WHERE tblVisitors.intEMPId = employee.intEmpId ORDER BY TimeIn DESC)
END As ArrivalTime
FROM Tensor.dbo.tblEmployee AS employee LEFT OUTER JOIN
dbo.tblVisitors AS Visitor ON Visitor.intEmpId = employee.intEmpID
WHERE (employee.strEmpID LIKE 'V%')
ORDER BY CardReference
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Pleased to have been of use.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Good morning,
I have a question regarding if something is possible to do in SQL. Is it possible to setup a Linked Server in SQL, or some other SQL connection that will access an existing Web Service? I have found various web pages about setting up SQL data to be accessable as a Web Service, but I found nothing that says if it's possible for SQL to access a Web Service to pull back records as part of a stored procedure.
The reason I'm wondering about this is because as we are moving to a new database engine, it has been suggested to turn off access to Linked Servers.
David
|
|
|
|
|
I my be wrong, but I think you will need to write a .NET extension for SQL Server, which can then subscribe to a Web Service
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
I have a need to return a table of data, for a MS SQL Server 2005 Stored Procedure, based on the following :-
ID = Table1.ID
If Table1.Status = 0 Then
Name = Table1.Name
Company = NULL
Else
Name = Table2.Name
Company = Table2.Company
Where Table2.ID = Table1.ID
End
I hope this mud is clear.
My T-SQL is not bad for the run-of-mill type tasks, but I do struggle with the more complex ones. (But I am learning)
Any pointers on where I should start would be great.
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Hi,
the exact approach depends on whether you just want to invoke the procedure and check it's return value or if you need to retrieve other results from it as well (a procedure returns an integer with the RETURN statement, but can also return a data set from a SELECT statement or another proc). If you wish to retrieve a dataset returned by the procedure, the simplest approach is to use a data adapter and configure it's SelectCommand to call the procedure. If you only need the return value it's more efficient to use an SqlCommand directly without an adapter.
In any case, you configure the command as follows.
First, set command text and type to specify a proc and it's name:
cmd.CommandText = "myproc"; // name of stored procedure
cmd.CommandType = CommandType.StoredProcedure;
Next, create parameter objects:
SqlParameter name = cmd.Parameters.Add("@name");
If you need the return value from the procedure, add a parameter of type ReturnValue using another Add() overload.
Assign the parameter values according to your logic:
If ...
name.Value = Table1.Name
...
Else
name.Value = Table2.Name
...
Then execute: DataAdapter.Fill() if you're using the adapter, or SqlCommand.ExecuteNonQuery() if you only need the return value. Note that ExecuteNonQuery() returns number of rows affected (in the last batch of the procedure, see @@ROWCOUNT in Sql Server docs), not the return value from the procedure. To get this, read the Value property of the SqlParameter you added.
|
|
|
|
|
try this
select ID = Table1.ID
If Table1.Status = 0 Then
Name = Case when Table1.Status = 0 then Table1.Name else Table2.Name end,
Company = case when Table1.Status = 0 then NULL else Table2.Company end
from Table1
inner join Table2 on Table2.ID = Table1.ID
Bob
Ashfield Consultants Ltd
|
|
|
|
|
Thanks Bob, with a bit of refinement I have what I need based on what you gave me.
Cheers
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
