|
That was a sample, not something to be used exactly as written.
vhassan wrote: Position is not incremental instead it is calculated value.
Incrementing is one kind of calculation. I have no way of knowing what calculation you need. You would need write whatever calculations and record selection criteria you actually need.
|
|
|
|
|
I have a stored procedure that creates Pivot/cross tab resultset. for example my data is as follows :
Id, Date, Value
1, 01/12/2009, 100
1, 02/12/2009, 110
2, 01/12/2009, 50
2, 02/12/2009, 55
2, 03/12/2009, 75
3, 02/12/2009, 100
The Stored Procedure would result a resultset as follows :-
Id, 01/12/2009, 02/12,2009, 03/12/2009
1, 100, 110, 0
2, 50, 55, 75
3, 0, 100, o
So far so good.
Now I need to join the results of the stored procedure to a another table joined by Id, either in a View or another Stored Procedure. Can I do this, keeping in mind that the number of columns and the names of the colums are variable. The only constant is the Id column name.
Thanks
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
You have a number of choice on how to transport a resultset from a proc
Turn the proc into a view - always the first choice but rarely possible with a pivot
Use a UDF - also dies on the requirement of dynamic SQL to define the columns
Shove the resultset into an XML variable and pass it back as an out parameter - there are a number of articles on CP on the subject. I have used this once and while it worked I have a strong dislike for XML.
Use a global temp table. This is my default option but it has some drawbacks like using the tempdb, house keeping the tables and race issues if it is a popular proc.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for the reply. I think I can discount the first three options.
I will investigate the other two options.
Thanks again
Steve Jowett
-------------------------
Real programmers don't comment their code. If it was hard to write, it should be hard to read.
|
|
|
|
|
Try turning the procedure into a table-valued function.
|
|
|
|
|
Hi Experts
i am using SQL Server 2005. I encrypted the Stored procedure with the help
of SQL Statment WITH ENCRYPTION But Now it is not open to me.
So any body can tell me how to decrypt it to orginal Text.
Thank u
Dinesh
|
|
|
|
|
|
I have been working on report creation in Business Object XI R2. The report i have created contains aggregated data(summary information) and deals with around 250 GB of database. The database is on SQL Server 2005 standard edition.
I have to show summarized information from 2 years for more than 20 different criteria.
The approach i am following is I am bringing the aggregated information in one DP and all different informations in other 4 DP. And i am using Synchrnization to link DP's to get the required groupings and present in different tab.
I have successfully created the report and refreshing independent DP correctly. But the problem is when we are refreshing the complete report in one go.
I am getting the error "There is not enough memeory to run this program. Please quit one or more programs, then press ok to try again."
Is there any way around to improvise the performance of report and use less memory.
Thanks in Advance
CodeManiac
xxxxxxxxxx
xxxxxxxxxx
|
|
|
|
|
|
What's involved in handling mutl-users in a C# WinForms app targeting SQL 2005?
Everything makes sense in someone's mind
|
|
|
|
|
Deploy multiple copies of the client application!
SQL Server is a server based database and as such is multi user by default.
You need to build a client application (in c#) that connects to the database to manipulate the data, the client is single user with multiple deployments!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hello guys,
Anyone knows how to script SQL jobs from a sql server database so that i can script them back into another database? I am using Quest Toad For sql server to view the sql jobs on the server.
Regards,
Christian Pace
|
|
|
|
|
For SQL2000 using SQL Server Enterprise Manager:
Right-click on the job, choose for all tasks/Generate SQL Script...
Wout Louwers
|
|
|
|
|
Hi,
I am having 3 tables A,B,C
table A:-
AID date
23 01/25/2010
24 01/25/2010
table B:
BID AID FileName date
1 23 1.doc 01/25/2010 1.00 AM
2 23 2.doc 01/25/2010 1.05 AM
3 23 3.doc 01/25/2010 1.06 AM
4 24 4.doc 01/25/2010 2.00 AM
5 24 5.doc 01/25/2010 2.01 AM
table 3
BID Status
1 E
2 M
3 P
4 P
5 P
Now I want Query that I want Unique number of records whos status is 'P' that is only Last file whos status is 'P'
Means I want the following output
AID FileName date status
23 3.doc 01/25/2010 1.06 AM P
24 5.doc 01/25/2010 2.01 AM P
Thanks
|
|
|
|
|
Try following query hope it will work for you
select tablesA.AID AID,FileName,tablesA.date date,status
from tablesA
inner join
(
SELECT FileName,BID,AID from tablesB where bid in
(select max(bid) from tablesB group by aid)
) as b on tablesA.AID = b.AID
inner join
tablesC on b.BID = tablesC.BID
|
|
|
|
|
It works....
Great
Thanks for your Help
|
|
|
|
|
|
How to write a MS_SQL query to populate a column from another column.
I have a table:
name col_1 col_2
name1 null value1
name2 null value2
The result should be as follows:
name col_1 col_2
name1 value1 value1
name2 value2 value2
|
|
|
|
|
never mind.
update mytable set tbl.col_1 = tbl_col2 where col_1 is null
|
|
|
|
|
Hi!
I'm using just .NET Framework 1 and Visual Studio 2003.
Will I have any problem if I install .NET Framework 2.0 or 3? Some controls will stop working? Will I have access to new controls / methods while programming, or It will onley install runtime libraries? I.E: Will I be able to use BindigSource / BindingNavigator, or I have to keep on CurrencyManager?
Thanks!
|
|
|
|
|
I think you will probably have a better chance of getting a good answer to this question if you post it in the .NET Framework forum.
|
|
|
|
|
Hi,
1.
wrong forum, this is not a DB question.
2.
the .NET versions can live together, no worries at all.
3.
the corresponding Visual Studio versions can live together too; the one you install last will dictate the file associations.
4.
never versions of Visual Studio will convert your project files when opening them, making them no longer readable by older versions.
5.
Why would anyone still use .NET 1.0 or 1.1?
2.0 has fixed a lot and added very useful stuff; 3.0 and 3.5 have added a lot more, not always very useful, functionality. And 4.0 will emerge pretty soon.
Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]
I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that. [The QA section does it automatically now, I hope we soon get it on regular forums as well]
|
|
|
|
|
I want to know how can I calculate the person's age from a person_birthday datetime field to show the year and month(if any) so if the age is exactly 25 then it should be 25y or if it's less than a year it will be 4m otherwise 25y 2m?
|
|
|
|
|
Use DateDiff(yyyy,birhtday,getdate()) to get the difference, the rest is just an excercise in maths and logic. DATEDIFF is the tool you need.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
try this
select cast(DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12 as varchar(10))+ ' Y '
+
cast (((DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate())) - (DATEDIFF ( month , '1999-10-26 12:30:02.960' , getdate()) /12) ) as varchar(10)) + 'M'
and for your scenario you also have to use case when
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
