|
Hi,
I have a table (tblregfundprice) with the column name "Unit_Price, Fund_Code, and Date".
The table contains the following data.
Fund_Code.....Unit_Price.....Date
AF............0.1254.........10/17/2008
AF............0.5487.........10/16/2008
AF............0.2114.........10/15/2008
AF............0.8744.........10/14/2008
AF............0.1254.........10/13/2008
AF............0.5487.........10/12/2008
AF............0.2114.........10/11/2008
AF............0.8744.........10/10/2008
AF............0.1254.........10/09/2008
SC............0.5487.........10/14/2008
SC............0.2114.........10/13/2008
SC............0.8744.........10/12/2008
GH............0.1254.........10/11/2008
GH............0.5487.........10/10/2008
GH............0.2114.........10/09/2008
GH............0.8744.........10/08/2008
Now if you notice, the most recent date is 10/17/2008 and SC is having the most date of 10/14/2008. How can i do to create below result table for TOP 10 Fund_Code = "SC"?
Unit_Price.....Date
NULL..........10/17/2008
NULL..........10/16/2008
NULL..........10/15/2008
0.5487.......10/14/2008
0.2114.......10/13/2008
0.8744.......10/12/2008
0.1254.......10/11/2008
NULL..........10/10/2008
NULL..........10/09/2008
NULL..........10/08/2008
I need the NULL value to appear as i need it for my system to capture it... I hope there's a way for this.
Pls help....Many thx.
|
|
|
|
|
try this
SELECT *<br />
FROM (SELECT DISTINCT Date FROM TABLE) D<br />
LEFT JOIN (SELECT * FROM TABLE WHERE Fund = 'SC') SC ON SC.Date = D.Date
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi,
I am retreiving a data from a table by self join. I am getting all the clients of a doctor and and group them into pairs. I need to have a unique record for each pair that is pair clientAclientB is the same as pair clientBclientA.How can this be done please, since I am retreiving both records. The sql I am using is written be;low
Select Distinct c.id, c2.id
FROM Client c, Client c2
Where c.doc = c2.doc
And c.id != c2.id
Thanks for your time
|
|
|
|
|
A quick thought (not tested)
Select Distinct c.id, c2.id
FROM Client c, Client c2
Where c.doc = c2.doc
And c.id < c2.id --less than instead of not equal
Eslam Afifi
|
|
|
|
|
Can anyone tell me that how can I store images in database. I am using SQL 2005 and I want to store image in that. I know there is a image datatype but it is not working. Please remember I want to store the image not it's path.
|
|
|
|
|
I have already answered in C# forum
|
|
|
|
|
i use identity_insert for a column in one of my tables. when I first insert 5 rows, this column will show number from 1 to 5 (identity set as 1,1), but when I delete this 5 rows and re-insert this 5 rows, the number get incremented (6-10), how can I reset it and make it still start from 1 ??? can I run any simple query to do that?? thanks.
|
|
|
|
|
The intent of an identity column is that it uniquely defines a row in a table, FOREVER ! You can always create another row in that table with the exact other values, but it will never be known by an identity value previously assigned.
It may be possible to re-assign the identity sequence generator, but don't do it. Have you considered what would happen if another table was referencing your master table with this identity column ?
|
|
|
|
|
For example DBCC CHECKIDENT (Transact-SQL)
[^].
However, identity is used to generate surrogate key values so the number generated should always be irrelevant. The database does not quarantee anything but uniqueness.
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
In case you did not get it - DO NOT CHANGE THE IDENTITY VALUES - this is a basic tennant of relational databases.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
again, I tried to run the following query
INSERT INTO table1 (SID, Time) VALUES (1, SYSDATE)
but got the error message as below:
Msg 128, Level 15, State 1, Line 1
The name "SYSDATE" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.
Can anyone tell me what the problem is? Thanks....
|
|
|
|
|
SYSDATE is an Oracle function. Use GETDATE().
The need to optimize rises from a bad design.
My articles[ ^]
|
|
|
|
|
i am working on SQL Server 2005(SQL Server Management Studio 2005).i want to ask that how can i import data from a text file into the table.i am really new to it,so please explain it in detail..
thanx..
|
|
|
|
|
Check out this link.[^] also check this google result[^]
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.
|
|
|
|
|
Asks the same question in multiple forums, can't use Google, and can't understand that I am MOCKING him.
Blog link to be reinstated at a later date.
|
|
|
|
|
i am working on Visual Studio 2005 n SQL server 2005.i have written some data value in random order in a table,now i want that,when i access that table in a DropDownList then those values should appear in ALPHABETICAL ORDER...
kindly help..thanx a lot..
|
|
|
|
|
From memory, the dropdown list has a sort property? Or maybe use ORDER BY in your select?
Bob
Ashfield Consultants Ltd
|
|
|
|
|
You can do it from SQl with
select ColumnName from my_table
Order by ColumnName Asc
Or you can do what is suggested by the other guy here.
Hope this helps
Vuyiswa Maseko,
Sorrow is Better than Laughter, it may Sadden your Face, but It sharpens your Understanding
VB.NET/SQL7/2000/2005
http://vuyiswamb.007ihost.com
http://Ecadre.007ihost.com
vuyiswam@tshwane.gov.za
|
|
|
|
|
History of posting same question in multiple forums and not using Google for basic questions.
Blog link to be reinstated at a later date.
|
|
|
|
|
Hi friends...
The requirement goes like this...
we already have 20 reports, like 1.rdl ,2.rdl.......20.rdl.
All reports takes a single parameter: @AssessmentId=Parameters!AssessmentId.Value
At present when client wants to view reports they have to click 20 different links for each report and then submit the assessmentid value as the parameter.
Now what they want is submit the parameter value in a single report and able to view all the reports one below another.
so how to go about this using sub reports
i am using sql server reporting services 2000. i have one shared datasource for all reports and different datasets.
thanx and regards
Hashmat
modified on Thursday, October 16, 2008 4:17 AM
|
|
|
|
|
Pull each sub report into the designer and set the parameters passed to each?
And nice naming convention...
|
|
|
|
|
Hi Paddy
As per you ,i have dragged a report(2.rdl) from the solution explorer into the designer of 1.rdl.It generated the sub report in the designer.
On right clicking sub report and selecting proerties ,I set the parameter as @AssessmentId=Parameters!AssessmentId.Value. which is same in report 2.rdl
now i m getting this error :
A parameter in the subreport ‘subreport1’ has the name ‘@AssessmentId’. Parameter names must be CLS-compliant identifiers.
how to resolve this error.
The other way i m thinking is copy and paste all tables in a single report and generate the report but i consider this as inefficient because must be better ways than doing this.
plz share ur thought on this.
|
|
|
|
|
SELECT Table1.AutoID,'RA No' = CASE WHEN Table1.RANo <= 9 THEN Table2.Assessment_Name + '/' + cast(Table1.Year_Year as varchar) + '/00' + cast(Table1.RANo as varchar) WHEN Table1.RANo >= 10 and Table1.RANo<=99 THEN Table2.Assessment_Name + '/' + cast(Table1.Year_Year as varchar) + '/0' + cast(Table1.RANo as varchar) WHEN Table1.RANo >= 100 THEN Table2.Assessment_Name + '/' + cast(Table1.Year_Year as varchar) + '/' + cast( + Table1.RANo as varchar) END,
'BU (consolidated Lev 1 - 4)' =
if len(Table1.BULevel1) <> 0 BEGIN SELECT Table1.BULevel1 + '/' from Table1 where len(Table1.BULevel1) <>0 END
if len(Table1.BULevel2) <> 0 BEGIN SELECT Table1.BULevel2 + '/' from Table1 where len(Table1.BULevel2) <>0 END
if len(Table1.BULevel3) <> 0 BEGIN SELECT Table1.BULevel3 + '/' from Table1 where len(Table1.BULevel3) <>0 END
if len(Table1.BULevel4) <> 0 BEGIN SELECT Table1.BULevel4 + '/' from Table1 where len(Table1.BULevel4) <>0 END
,Table1.RATitle AS [RA Name],Table1.RADesc AS [RA Desc],Table1.SME AS [SME],Tab_RiskRate.RiskRate_Name AS [Risk Rating (Pre Miti)],Tab_Location.Location_Name AS Location,Table1.PlannedMonth AS [Planned month],Table1.RescheduleMonth AS [Rescheduled Month],Table1.DateStarted AS [Date started],Table1.RAInitiated AS [No. of days since RA initiated],Table1.Signoff AS [Date Signed Off],Table1.ActualDate AS [Project Live Date],Tab_Status.Status_Name AS Status FROM Table1 INNER JOIN Table2 ON Table1.Assessment = Table2.AutoID INNER JOIN Tab_RiskRate ON Table1.PreMitigating = Tab_RiskRate.AutoID INNER JOIN Tab_Location ON Table1.Location = Tab_Location.AutoID INNER JOIN Tab_Status ON Table1.Status = Tab_Status.AutoID WHERE (Table1.Mode = 1)
i m getting Incorrect syntax near the keyword 'if'. and Line 8: Incorrect syntax near ','. error.
please guide me - KARAN
|
|
|
|
|
You if statement needs to be a case statement, much like the first line - can't have if statements inline like this. You might also want to revisit your design - this doesn't seem like the most logical structure.
|
|
|
|
|
how to restrict a value in a column like int 1000 to 2000 should only get inserted in the table otherwise that entry should not insert in the database.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
