|
Hi Friends,
I need an sql query help.i need to implement distinct staetement in my select query.I need the Assets.AsstId should be distinct in query.i attach the query below .please help me i need its urgently.The bolded value should be distinct.
SELECT aspnet_Users.UserName, AssetsSettingsTypes.AssetSettingsName, CONVERT(varchar(MAX), Assets.AssetShortDescrition)
AS AssetShortDescrition,Assets.AssetId, '<img src="' + CONVERT(varchar(MAX), Images.ThumbUrl) + '" width="100" height="100"/>' AS Image,
dbo.GetFriendlyDateTimeValue(UserAssets.CreationTime) AS TimeDiff, UserAssets.CreationTime as CreationTime,
'profileUser/Profileview.aspx?' + CONVERT(varchar(MAX), aspnet_Users.UserName) AS UrlProfile,
'profileUser/userPhotos.aspx?AssetId=' + CONVERT(varchar(MAX), UserAssets.AssetId)
+ '&UserName=' + aspnet_Users.UserName AS NavUrl, 'forntend/Commentview.aspx?' + CONVERT(varchar(MAX), UserAssets.AssetId)
+ '&' + CONVERT(varchar(MAX), UserAssets.UserId) AS UrlComment, '(' + CONVERT(varchar(MAX),
(SELECT COUNT(*) FROM UserComments WHERE (UserAssetId = UserAssets.UserAssetId)))+ ')' AS CommentCount
FROM AssetsSettingsData INNER JOIN
Assets ON AssetsSettingsData.AssetId = Assets.AssetId INNER JOIN
AssetsSettingsTypes ON AssetsSettingsData.AssetSettingsTypeId = AssetsSettingsTypes.AssetSettingsTypeId INNER JOIN
Images ON Assets.AssetId = Images.AssetId INNER JOIN
UserAssets ON Assets.AssetId = UserAssets.AssetId INNER JOIN
aspnet_Users ON UserAssets.UserId = aspnet_Users.UserId
WHERE (aspnet_Users.UserId = '9D4C2631-BB10-47C0-A4EE-5B1E92FE1AB9')
|
|
|
|
|
While Deploying the SSRS 2008 Project I am getting a "Reporting Services Login" Dialog box, which does not accept any logins. I have tried Windows , SQL server Login credentials but did not get thru it.
|
|
|
|
|
I had this problem recently also. I made two corrections in the Project properties. In the Data Source and target folder sections, I had included the http:// and the reporting server name and reports folders. I deleted those so they just started at the project name. Finally on the target url, I changed it to http:// the reporting server name and /reportserver. That fixed the problem for me. Good luck
|
|
|
|
|
Thanks dirty12,
I have tried this but it did not work, right now I have TargetServerURL as "http://localhost/reportserver". All I want to know is What is this "Reporting Services Login" Dialog and why doesn't it accept any Login credentials.
|
|
|
|
|
Sorry, I don't have a clue as to why this was happening. Although I had the same problem when I tried "localhost" and when I actually put the server name in, the problem went away. Good Luck.
|
|
|
|
|
I had the same problem. It was complicated by the fact that I have two instances of SSRS running on my system one is 2005 the other 2008, and I thought I knew the 2008 instance's name. I had accidently typed the service account name when I actually needed to type in the Virtual Directory.
I resolved this by finding the exact name I needed. I started Reporting Service Configuration Manager, conected to my instance, clicked on the "Web Service URL" to find the correct name. In my case, I needed to replace http://computername/Reportserver$MSSQL10/ with http://computername/Reportserver_MSSQL10/. This now allows me to deploy (without prompts).
-Will
|
|
|
|
|
Hi All,
I have tables called CALL and Operator.
Select Distinct(Mobile),MIN(DATE_OF_CALL),NAME_OPERATOR from Call inner Join Operator on
Call.CustomerId=Operator.CustomerID
where Datename(month,DATE_OF_CALL)='APRIL'
Group by Mobile
What i want is to get the first date of entry for each Mobiles in the month of april and the operator who attended them.
However i Have to group by Name_operator to run the above script.But i dont want them to be grouped by OPerator.I want the first entry of a mobile in the database together with the name of the operator who attended them.
How do i go about it Please?
Thank you so much.
|
|
|
|
|
Presumably you are using SQL 2005+ as you database (I know it may be an unfounded assumption)
Look into Row_Number and partition. These will allow you to select the records for month# with a rownumber partitioned over mobileNo ordered by date of call having a rownumber = 1
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Than you very much.Its most appreciated.I am Using SQL 2005.
Could you please give me an example please? What i want is the first entry of a mobile number for a certain month.I have the operator name in a diffrent table called Operaor.
For instance a mobile number 12 has called mike on 2009-04-01 and andy on 2009-04-02. I want only to get the first entry that is
Mobile:12 DateofCall :2009-04-01 : operatorName: Mike.
That is the first entry for this number on the month of april.
How could i do that on the above script? Please if you can give me an example.
Thank you onece again
|
|
|
|
|
HI I got an error with this one ?
Can any one advice?
Select distinct(Mobile), ROW_NUMBER() OVER(PARTITION BY mobile ORDER BY dateofcall DESC ) AS 'RowNumber' from Call
having RowNumber=1
Error :Invalid Column RowNumber
|
|
|
|
|
|
I'd do something like this:
SELECT [Call].*
,Operator.Fullname
FROM
(SELECT [Caller],FirstCall=MIN(CallTime) FROM [Call] WHERE DATEPART(MM,CallTime)=4 GROUP BY [Caller]) T
INNER JOIN [Call]
ON T.[Caller]=[Call].[Caller]
AND T.FirstCall=[Call].CallTime
INNER JOIN [Operator]
ON [Call].Operator=Operator.ID
Just be sure to have indices on Caller and CallTime.
|
|
|
|
|
How to Show Vertical Text in SQL Server 2008 Reporting Services, Since it is possible in SQL Server 2005 Reporting Services. In SQL 2005(SSRS) Textbox has a property for Rotating and showing the text verically, but in SQL 2008(SSRS) does not any such property. Kindly help.
|
|
|
|
|
The textbox property names are Writing Mode (Horizontal/Vertical) and Direction (LTR/RTL).
|
|
|
|
|
|
1) Application looks good and usefull
2) This post is the worst advertisement I seen in weeks
I are troll
|
|
|
|
|
|
Following points should be considered while doing the performance comparison between normal sql query and sql views:
(i) Executing a View creates an extra effort for SQL Server for looking into system tables.
(ii) Improper implementation of JOIN and UNION can create performance bottlenecks in views.
Regards
Saanj
Either you love IT or leave IT...
|
|
|
|
|
Is not it like for Views SQ L Server know its only Read Only Mode so it will get data with minimum cursors while for table it will go for all the options
Because i read one article / topic in which the Author says that we should not expose even database whole schema to the end use , we should only expose our views so they can manipulate with the data
|
|
|
|
|
Ya,
I am agree with this point that you have rightly mentioned. Indexed views can be a better option. But , nested views also can create a performance issue.
Either you love IT or leave IT...
|
|
|
|
|
There exists at least one view that is faster than its equivalent simple query.
|
|
|
|
|
Please can you explain this
Thanks
|
|
|
|
|
Good evening
I have a table in database like following one
---------------------------------------------------------------------
rollNo | Name | Subjects
---------------------------------------------------------------------
101 | abc | english, maths, social science, science, hindi
102 | def | maths, social science, science
103 | ghi | english, social science, hindi
101 | jkl | english, maths, social science, science, hindi
----------------------------------------------------------------------
now i want to select only those records which have "science" in the "Subjects" column
so i use
Select * from tblStudent Where Subjects LIKE "%Science%"
but it also give the records that have "Social Science" ie record no 3
please help me
thanks in advance
modified on Saturday, May 2, 2009 8:40 AM
|
|
|
|
|
This is where you pay the price for BAD data design, If you were good you would create another table called Subject and link it to your student table in the correct foreign key data structure. Now you're screwed, you have to invent a work around for your crappy design.
Options Are:
Redesign you data structure correctly - this is the recommended solution.
Play around with the text structure (', Science') will get most except where it is the first subject.
Try and NOT like "Social Science"
Create a split function based on the "," and filter on the required value (most sql devs have a split function floating around in their tools set)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
No doubt DB structure has million dollar worth butt still you can use
you can use In
"Select Col1 ,Col2 from tbl Where Col2 not In (SELECT Col2 FROM tbl ) "
and can also use the XML as now is supported in SQL SERVER
|
|
|
|