query join problem

I am using CTE and joining it to other tables in my query. The issue is I am not creating my joins correctly. I am using the CTE twice but the calues in it should only appear one time on amount based on the amount type. attached is the
code

More Related Resource Links

Hii All,ÃÂ ÃÂ ÃÂ I am using wss 3.0 and i have one site and site has 4 to 5 lists. lists are interconnected with lookup column and same column. I need to display some column fron one list and some from another.. i need to join that lists by where condition like project =project..

Hi,
I'm trying to create an sql query with multiple inner joins, but I can't manage this.
I have 3 tables (bookings, accountplan, costs), where the data come from.
In accountplan are hold the accounts, in costs the cost codes and their names, in bookings the booked events.
Based on a booking event, I need the account name, the name of the cost code and all data from bookings (I need the cred account name too, but I can't figure out yet, how I can get this)
I tried this:
"SELECT bookings.*, accountplan.accountname, costs.costname FROM bookings INNER JOIN accountplan ON bookings.debaccount=accountplan.account INNER JOIN costs ON bookings.costcode=costs.costcode"
It is here a where clause too, but I tested it, it's ok.
The above query results no rows. If I remove the costs table and the inner join with it, it works. What am I missing?

Hello All,
I have a requirement where i need to query the data from two sharepoint lists on a same site by doing a join between those lists
and get the result into datatable.I am doing this using sharepoint object model.Is there any possibility to do this.
Any pointers in the right direction are greatly appreciated.
Thank you
Smith
smith

so i have a datagridview in a vb from. my plan is to display a table named emplyees, having the following fields: [employees] ([lastname], [firstname], [cost_center], [division], [job_title], [hiring_date], [exit_date], [functional_manager], [member_of],
[backup_by], [out_of_office], [desk_phone], [mobile_phone], [comments]. But I need to make an inner join with 3 other tables. job_titles, divisons and rss. How should i writethe select command for this, having the inner join forthe follwing fields: division-
division.name,job_title-job_titles.name , and member_of- rss.group_name.
I have no idea how to do it. pls help
Thanks

Dear Community,
I am using Sharepoint 2007's content query webpart to access content types from 2 lists each contained in a different sitecollection. To achieve this, I used this tutorial: http://www.heathersolomon.com/blog/articles/CustomItemStyle.aspx.
The tutorial worked great. By using the property CommonViewFields and modifing the XML Stylesheets accordingly, i could access almost every field.
Even User Fieldtypes like editor and author were accessible by adding (editor,User) or (author,User) to the CommonViewFields property.
But when I tried to access the custom made userfield "Username" in the same way (adding Username,User to the property), the webpart threw an error and couldn't be displayed anymore. When I changed it to Username,Text , no error occured but it naturally didn't
show up, since it is the wrong fieldtype.
I hope some of you can help me, since all my researches on this topic only brought up some more people with the same problem, but no solution to it.
Thanks in advance,
SoundofSilence

Hey,
While working SSIS in 2008 we have encountering some weird problems with Merge, Merge Join and Condition Spilt transformations, here are the details...
Condition Split and Merge Join and Condition Split:
Please check the below diagram(not really just tried) for the complete details.
Just FYI data is in sorted order
condition Split -> 420000
| 18 | 419982
Merge Join(left) -- src1(6000)
|10
Condition Split
|9 |1
the merge join transformation is not returning the complete 18 rows to the next transformation that's why we are missing 8 rows.
But if we adds a sort transformation (getting warning as data is already sorted) before merge then complete 18 rows were passing to the condition split.
Also tried modifing the source query just to return 18 rows then also it was passing the 18 rows to next trasnformation except with full load.
Merge:
We have two sorted data sources and first one has 40000 records and second source have 12000 records and after the Merge transformation we have Condition Split transfor

Hello,
i´m developing a webpart under Sharepoint 2010, i need to launch some different query´s to filter data but i´m having problems.
First of all, i launch this query and i get all the users;
SELECT PreferredName, LastName, Department, OfficeNumber, JobTitle, WorkEmail, PictureUrl FROM Scope() WHERE ("scope" = 'Personas') ORDER BY "Rank" DESC
with that results i fill different dropdownlist with each of the columns, then I can filter for each column.
For example:
I filter by PreferredName, Lastname or WorkEmail, with this query
SELECT PreferredName, LastName, Department, OfficeNumber, JobTitle, WorkEmail, PictureUrl FROM Scope() WHERE ("scope" = 'Personas') AND (("LastName" Like '%MOSS%')) ORDER BY
"Rank" DESC
But when i try to use the columns Department, OfficeNumber and Jobtitle every query returns 0 rows.
SELECT PreferredName, LastName, Department, OfficeNumber, JobTitle, WorkEmail, PictureUrl FROM Scope() WHERE ("scope" = 'Personas') AND (("Department" Like '%Dirección%'))
ORDER BY "Rank" DESC
The main thing is if i get all the data i see that these columns (officeNumber, Jobtitle, Department) are not empty. Some of them have values some of them don´t.
All the columns have the property "Reduce storage recquirement for text propert

Hi,
I have encountered a problem with a query execution plan on MS SQL Server 2008. It is a simple query on a single table. The table has a primary key RNUM (number(10)) with a clustered index. The query is executed via ODBC using fast forward cursors and is constructed
like this:
select [field_list_here] from table_name where RNUM>@P and TYPE='A' order by RNUM.
The field TYPE has 2 possible values and is not indexed. The table has about 2 000 000 rows of static data (only reads, no inserts and updates).
For some time my query executes using the efficient query execution plan.
Below a copy from Management Studio from an ad-hoc query:
SELECT (0%) <- Clustered Index Seek (100%)
But after 2 days of executing other type of queries SQL Server starts to use other execution plan (live copy):
Fetch query (0%) <- Clustered Index Seek [CWT_PrimaryKey] (0%)
|
\/
Fast forward (0%) <- Population quer

hi every body
I have two web pages one for inbox messages and second for details for one message
in first page I have gridview include name the sender , address the message and details message
alongside with buttonfield to go to another page to view full message
the code of gridview<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False"
DataKeyNames="id" DataSourceID="SqlDataSource1" Width="648px"
onrowcommand="GridView1_RowCommand">
<Columns>
<asp:BoundField DataField="sender" HeaderText="sender"
SortExpression="sender" />
<asp:BoundField DataField="address" HeaderText="address"
SortExpression="address" />
<asp:BoundField DataField="details" HeaderText="details"
SortExpression="details" />
<asp:ButtonField Text="More..." CommandName="Show_More" />
<asp:TemplateField ShowHeader="False">
<ItemTemplate>
</ItemTemplate>

Hi everyone,
I have the following query which works fine:
select OriginalFileName from Document_Entries where EntryType like 'File%'
and substring(entry,charindex('file_',entry),LEN(entry)) in (
SELECT filename FROM OPENQUERY(MySearchCat, 'SELECT Directory, FileName FROM SCOPE() WHERE
CONTAINS('' "green" '') ')
)
It finds all documents in the document system which contain the word "green" using the index catalog. My problem is that i need to include this query in a larger stored procedure which accepts a parameter for key words amongst others.
I can't work out the syntax to get the @keywords parameter into query. The closest I've come is the following which runs but comes back with "incorrect syntax near keyword 'green'". The @keywords parameter will contain any key words the user
enters.
declare @keywords nvarchar(500)
set @keywords='green'
Declare @query nvarchar(max)
set @query = '
select OriginalFileName from Document_Entries where EntryType like ''File%''
and substring(entry,charindex(''file_'',entry),LEN(entry)) in (
SELECT filename FROM OPENQUERY(MySearchCat, ''SELECT Directory, FileName FROM SCOPE() WHERE
CONTAINS(''' + @keywords + ''') '')
)
)'
exec(@query)
Any ideas?
thanks
Gus

Hi,
I have following measures
- Period
- NumberOfPeriods
- Amount
I want to add a calculated member to a cube where I get the sum of amount for all entries with "Number of Periods" = Period.
Is there a way to do that?

Hey guys. This is one of the queries pasted from BOL. I'm having problems excuting this query. The problem lies in the CROSS APPLY part. When I copy this query and run it in SSMS, it gives me an error saying 'Incorrect syntax near .' It doesn't like the qs.sql_handle part. If I remove that and pass the actual handle in for some query, it works. Can someone please tell me what I'm doing wrong?????? Also, I've sp1 installed on my SQL Server 2005 Enterprise, just in case if this matters. Below is the query pasted which is giving me problems. Thank you.
SELECT TOP 5 total_worker_time/execution_count AS [Avg CPU Time],
SUBSTRING(st.text, (qs.statement_start_offset/2)+1,
((CASE qs.statement_end_offset
WHEN -1 THEN DATALENGTH(st.text)
ELSE qs.statement_end_offset
END - qs.statement_start_offset)/2) + 1) AS statement_text
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS st
ORDER BY total_worker_time/execution_count DESC;

Hi All,
I am looking at the query plan for a stored procedure and I am seeing things like 1400% on the execution plan, the stored procedure I believe is called within a loop quite a lot of times, but I don’t quite understand why the percentages will be over 100
for one section of the execution plan.
Any explanation for this ?
Thanks in advance.