Summing Textboxes With Values From Diff. Datasets

Apr 28, 2008



hello,

I have the following and what to do SUM the valueS in each in another textbox, what is the proper method?



=count(Fields!Month_Submitted.Value, "SALES")

=count(Fields!Month_Submitted_1.Value, "SALESDIR")

=count(Fields!Month_Submitted_2.Value, "SALESVP")

=count(Fields!Month_Submitted_3.Value, "CREDIT")



Thanks,
Rhonda

View 8 Replies


ADVERTISEMENT

Pulling 2 Values From 2 Textboxes Displayed In A Third

Jul 20, 2005

I have a problem trying to get a value from textbox 1 and textbox 2.If those two values match the data in the database then it has toreturn a third corresponding value to a third textbox (or label) aftera user clicks a submit button. Basically, I am trying to allow usersto enter a value of weight in pounds in textbox 1 and a zip code intextbox 2 then pulling the shipping amount from a courier's databaseand returning that dollar amount to a third textbox or label afterthey click submit. How would I write this code in vb.NET?Thanks,TG

View 1 Replies View Related

Ca't Insert Textboxes Values Into A Database Table

Oct 9, 2007

Hello, my problem is that I have 2 textboxes and when the user writes somthing and clicks the submit button I want these values to be stored in a database table. I am using the following code but nothing seems to hapen. Do I have a problem with the Query (Insert)??? Or do I miss something else. Please respond as I can't find this.
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Manufacturer2.aspx.cs" Inherits="Manufacturer2" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server"><title>Untitled Page</title>
</head>
<body>
<form id="form1" runat="server">
<div>
<asp:Label ID="Label1" runat="server" Text="Name"></asp:Label>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br />
<asp:Label ID="Label2" runat="server" Text="Password"></asp:Label>
<asp:TextBox ID="TextBox2" runat="server"></asp:TextBox>
<br />
<br />
<asp:Button ID="Button1" runat="server" Text="Submit" OnClick="Button1_Click" />&nbsp;
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="INSERT INTO Manufacturer(Name, Password) VALUES (@TextBox1, @TextBox2)">
<SelectParameters>
<asp:ControlParameter ControlID="TextBox1" Name="TextBox1" PropertyName="Text" />
<asp:ControlParameter ControlID="TextBox2" Name="TextBox2" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
 
</div></form>
</body></html>
 
 

View 10 Replies View Related

Selecting And Placing Values From Sql Query Into Textboxes

Jan 9, 2008

Hi. I have an sql query that i use to insert articles to a sql databse table and for that i use addWithValue to select witch textboxes etc goes where in the database.
Now i need to retrive these values and place them back into some other textboxes (some of them multiline) ,and i wonder if there are any similar ways to retrive values like AddWithparameter so i can easily do textBox.text = //whatever goes here ?

View 4 Replies View Related

Handling Large Data Values In Textboxes

Mar 28, 2007

Hi, Ive got a report using a List item that is vertically displaying the columns from a table. The problem I run into, is that some of the fields in this table contain large blocks of text where the users have entered comments and such.

I am using Textboxes to display this data.

So my report will look something like
-----
Field label 1 Field value 1
Field label 2 Field value 2
Field label 3


<white space>



<page break>

Field value 3 ---> this is a big block of text
Field label 4 Field value 4
etc
------
It appears as though the report attempts to keep the contents of each textbox together even if that means breaking onto an entirely new page to do this. I would prefer for the data to flow more natrually instead where the page breaks in the middle of the data being displayed should it be too large to fit on the page it started on.

-----
Field label 1 Field value 1
Field label 2 Field value 2
Field label 3 Field value 3 --- As much as can fit on this page

<page break>

Field value 3 ---> remaining data that broke over the page
Field label 4 Field value 4
etc
------

Any suggestions would be apprecaited.

View 3 Replies View Related

Summing Only The Greatest 5 Values In Each Group

Jan 10, 2008

Hello,
Here's one way to sum only the top 5 (greatest 5) values per group.
I assume there is a table called IdValues that contains two columns: id int, value int.

declare @lastId int
declare @value int
declare @count int
declare @idList varchar(5000)
declare @valuelist varchar(5000)
set @count=0
set @lastId = -1
set @value = 0
set @idList=''
set @valuelist=''

select
@count=(case when @lastId<>id then 1 else @count+1 end),
@value=(case when @lastId<>id then value when @count<=5 then @value+value else @value end),
@idList=(case when @lastId<>id then cast(id as varchar)+','+@idList else @idList end),
@valuelist=(case when @lastId<>id then cast(@value as varchar)+','+@valuelist else cast(@value as varchar)+','+right(@valuelist,len(@valuelist)-charindex(',',@valuelist)) end),
@lastId=id
from IdValues
order by id desc, value desc

select @idList,@valuelist

It's a funny approach. I'd be interested to see a better method. In MySQL it is possible to do this much better and have it produce an actual resultset (since MySQL allows you to assign variables and product a resultset in the same query).

I also noticed something interesting. If you do any operation on the order-by columns, the query doesn't work. For example, if I do:
order by id+0 desc, value desc
something funny happens and you only get one id and one value in the list variables. Maybe someone else who actually some idea of how SQL Server works can explain this.

Thanks,
Thomas

View 3 Replies View Related

Calculating Textboxes Values Grouped In Diferent Lists

May 7, 2007

I have to Lists filtered by the same field. One List shows all records with the field IsClokedIn value = TRUE and the other list =FALSE. I have only one dataset that retrieve the ClockIn and Clock out for one user. In a perfect world, the user MUST clock in and always clock out. So, when i created my dataset i ordered by UserClockingID (whick is primary key). So, this MUST retrieve a dataset with alternate records IN / OUT (TRUE or FALSE)

Ex.

UserClokingID UserID Time IsClockedIN

2323 34 8:32:03 TRUE

2324 34 12:07:02 FALSE

2325 34 13:05:03 TRUE

2326 34 14:53:02 FALSE

2327 34 15:10:03 TRUE

2328 34 17:32:02 FALSE



All i need is to calculate (sum then divide by 60) the minutes returned by the method DateDiff between the first row in my TRUE filtered list and the first row in my FALSE filetered list... and so on for the 2nd, 3rd ..... I can get the minutes by taking the values directly from the Fields!IsClockedIn.Value and using a condition to "grab" the previous record if the current record ClockedIn.Value=FALSE. I already did that.

my problem is that when i try to perform a calculation of textboxes values(those with the ammount of minutes for every pair of records from the datalist) out of the group. It says you can only do that inside the group or dataregion.

Is there any work around?



I really need to get this solved.

View 2 Replies View Related

Reporting Services :: SSRS Datetimepicker And Textboxes Values Different When Using Weekday Function?

May 7, 2015

In my SSRS report i have a date parameter and i want to set to it a default value with a complicated logic. I arrived at this strange behaviour:

Today is May 6th, wednesday. If i use the following expression:

DateAdd("d",Weekday(Today(),DayOfWeek.Sunday),Today())

for the default time picker I get May 9th. If I use exactly the same expression in a textbox in the same report

DateAdd("d",Weekday(Today(),DayOfWeek.Sunday),Today()).ToLongDateString()

I get May 10th! The only thing that changes is the toString. Why are the two values different? I tried with different expressions and the difference arises when i start using Weekday(Today(), somevalue).

The result of Weekday should be independent from regional settings as I am forcing Sunday to be the first day of the week, right?

View 2 Replies View Related

Transact SQL :: Best Way To Make A Function For Summing Arbitrary Number Of Times Values

Jun 22, 2015

How is the best way to make a function for summing an arbitrary number of times values (table parm?)- I 've read it's necessary to convert to seconds, sum then convert back, but Im' wondering if there's an alternative.

Here's the example I want to sum:
00:02:01:30
00:01:28:10
00:01:01:50
00:06:50:30
00:00:01:50

View 8 Replies View Related

Reporting Services :: Export Excel Formula For Summing Column Values

Jun 22, 2009

In SQL server Reporting service we need to export excel formula for summing column values. scenario : After generating report we are exporting report to excel file using report viewer.when user will modify a column value we need to calculate(update) automatically sum of the column values.Basically we are setting excel formula.

View 3 Replies View Related

Reporting Services :: SSRS - Summing Up Positive And Negative Values Alone From Single Column?

Sep 18, 2015

MS SQL Server 2008 R2

I have both positive and negative values in a single column, where I want sum total of positive values & negative values. Is there any Expression for this to sort out.

View 8 Replies View Related

Report Services 2000 Totals On Group Are Incorrect. Summing Duplicate Values

Dec 19, 2007

Hello Everyone
I've created a report with a simple dataset that is similar to this

City , RequestID, Request Amount, ClaimID, ClaimAmount
El Monte 791 52,982.00 2157 41,143.75
El Monte 3691 11,838.00 3140 8,231.14
El Monte 3691 11,838.00 3141 990.00
El Monte 3691 11,838.00 3142 2,615.00


So I group by City, RequestID. On the first group I specified the expression to be City and in the header I list the city and in the footer I list the sum of Request amount. On the second group I specified the group by Request so in the header I placed requestID and on the footer I placed Request Amount. I set request information to hide the duplicates and I even add =Sum(Fields!RequestApprovedGrandTotal.Value,"GroupByRequestID") the scope of the group. But this is what I get:


For requestID = 3691 for Request Amount is 35,514.00 not 11,838.00. All the claim sums are correct and they are located on the detail row.



I've read that a work around is to create multiple dataset but I honestly believe that something as simple as this should work on the reporting server 2000. So I've come to the conclusion that I must be doing something wrong. Can someone give me a hand on this. Thanks.

View 6 Replies View Related

Time Diff Between 2 Values In The Same Column?

Aug 27, 2006

Here is the sample data
nType dCDServerTime
02006-05-12 03:50:46.770
12006-05-12 04:00:59.153
962006-05-12 04:01:23.827
962006-05-12 04:16:01.297
142006-05-15 05:02:46.857
202006-06-23 03:23:36.970

I am trying to find the time diff between dCDServerTime of the "first" ntype = 1 and the "first" ntype = 96. There can be any number of rows with 1 and 96 but rows with 1 always come before those with 96.

I tried by putting a subset of the above result into a table variable but don't seem to get ahead without declaring more variables.

declare @t table
(Type int, ServerTime datetime)
insert into @t
select top 1 nType,dCDServerTime from tblEvent
where nGlobalCaseID = 11901643 and nType = 1
UNION
select top 1 nType,dCDServerTime from tblEvent
where nGlobalCaseID = 11901643 and nType = 96
select * from @t

Is it possible to do it with a single select?

View 8 Replies View Related

How To Diplay Values Together From Two Datasets?

Mar 27, 2008

hi

I have one scenario where i have a sub table in main table where i m calling differnt data set than the dataset referiing to the main table. but its not displaying the the cuurrent fieild for the same , as i can diplay in the second table by '

First(Fields!CreatedBy.Value, "DataSet2"), but i need the current value frm data set not the first value

Can you please help?

Thanks



'

View 4 Replies View Related

T-SQL (SS2K8) :: Find Null Values Between 3 Datasets In One Table

Mar 21, 2014

I have a table with data in two columns, item and system. I am trying to accomplish is we want to compare if an item exists in 1, 2 or all systems. If it exists, show item under that system's column, and display NULL in the other columns.

I have aSQL Fiddle that will allow you to visualize the schema.

The closest I've come to solving this is a SQL pivot, however, this is dependent on me knowing the name of the items, which I won't in a real life scenario.

select [system 1], [system 2], [system 3]
from
(
SELECT distinct system, item FROM test
where item = 'item 1'
) x
pivot
(
max(item)

[Code]...

View 2 Replies View Related

Numeric Or Time-scale Values Apparently Produce 2 Datasets?

Mar 30, 2008



Hi All,
I have developed few charts - line graphs - and have checked the Numeric or time-scale values on the X-Axis. Now, when the graphs come out, they appear to have two datasets. For example, if the X axis has values from 1st march to 14th March, then there will be a line from 1st March to 14th March, but additionally there will be another one, pointing to different datapoint, and again starts from 1st March and ends at 14th March. Whereas I think it is because of the Numeric or time-scale values selection, but I am not sure. Also, is there a particular way to interpret such graphs??

Thanks a lot, I would have loved to give a picture, but I think I cannot give it.. I did not find an option..

Let me know if the question is not clear enough..

Manoj Deshpande.

View 4 Replies View Related

Select Data From Diff. Tables On Diff. Servers

Feb 12, 1999

I need to write a 'select' statement to fetch data from different tables, which are located on different servers.
Can any one help in writing this 'select' statement with out moving the tables on to same server.


Thanks in Advance.

Murali Raparla.

View 2 Replies View Related

Grid And Textboxes

Jun 17, 2007

hi, 
now theres something i want to achieve but, again dont know how :-(
In a grid of my site  it needs to be able to select an item, ( not with  checkboxes) but maybe when you click on the item in the FromName column then
the data in the column in my database messageTEXT needs to be able to show in a textbox.( textbox is outside of grid)
now i was thinking that maybe it is need to be done with datareaders or something?
Greetz
Roy

View 5 Replies View Related

Hide Textboxes

Feb 25, 2007

Hello Everyone. I need help again.
I have a reports made in VS business intelligence project with a lot of parameters. So it means it automatically creates textboxes of that parameters. I want to hide those textboxes. Pls help.

Thanks
-Ron-

View 3 Replies View Related

Paragrahs In Textboxes

Apr 2, 2007

Hi,



Can anyone advise me how to force paragraph breaks in a textbox in Reporting Services 2000? I've tried Shift + Enter and in layout view it forces a paragraph break, but as soon as the report is rendered, the breaks disappear.



Thanks.

View 5 Replies View Related

Referencing Textboxes

Feb 7, 2008

Is there a way to reference textboxes in an SSRS table like one would for cells in Excel? For example, something like =textbox1/textbox2? I am trying to replace a spreadsheet by turning it into an automated report and need to do some horizontal formulas and when I put in the grouped total, it always defaults to averaging percetages from the rows above, which isnt a true summary in my case.

View 11 Replies View Related

Update Database With Textboxes

Aug 22, 2007

Hi
 I'm new to all of this. I have a database that holds customer information (fictitious) and i can select that data and display it in a set of textboxes. I also have an SQL command "UPDATE" that is designed to update the text field that i want to edit. However the problem i'm having is that it'll let me write the info in the textbox but as soon as i click my update button it just flashses and goes back to what it says before
 e.g. FIRST NAME: LEE      i enter TOM and then it reverts it back to LEE
  1
2 Partial Class Update
3 Inherits System.Web.UI.Page
4
5 Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
6 custIDTextBox.Text = Session("Label2")
7
8 Dim updatepage As System.Data.DataView = CType(SqlDataSource1.Select(DataSourceSelectArguments.Empty), System.Data.DataView)
9
10 For Each update As Data.DataRow In updatepage.Table.Rows
11
12 firstnameTextbox.Text = update.Item("First Name").ToString
13 lastnameTextBox.Text = update.Item("Last Name").ToString
14 addressTextBox.Text = update.Item("Address Line 1").ToString
15 townTextBox.Text = update.Item("Town").ToString
16 postcodeTextBox.Text = update.Item("Postcode").ToString
17 telephoneTextBox.Text = update.Item("Tel Number").ToString
18
19 Next
20
21 End Sub
22
23 Protected Sub updatebutton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles updatebutton.Click
24
25 'Dim parameters firstnameTextBox, lastnameTextBox, addressTextBox, townTextBox, postcodeTextBox, telephoneTextBox
26 'Dim UpdateParameters As QueryStringParameter
27
28 SqlDataSource1.Update()
29 SqlDataSource1.UpdateParameters.Add("@CustomerDetails", System.TypeCode.String)
30 'SqlDataSource1.UpdateParameters.Add("@Last Name", System.TypeCode.String)
31 'SqlDataSource1.UpdateParameters.Add("@Address line 1", System.TypeCode.String)
32 'SqlDataSource1.UpdateParameters.Add("@Town", System.TypeCode.String)
33 'SqlDataSource1.UpdateParameters.Add("@Postcode", System.TypeCode.String)
34 'SqlDataSource1.UpdateParameters.Add("@Tel Number", System.TypeCode.String)
35
36 'Label2.Text = ("Update successful")
37 End Sub
38 End Class
39

This is my SQL UPDATE command statement:
UPDATE CustomerDetails SET [First Name] = @firstnameTextBox, [Last Name] = @lastnameTextBox, [Address line 1] = @addressTextBox, Town = @townTextBox, Postcode = '@postcodeTextBox', [Tel Number] = '@telephoneTextBox' 
 
 

View 2 Replies View Related

Whitespace In Textboxes. How Do I Insert A Tab?

Feb 6, 2007

So...I'm trying to insert a tab (or just a few spaces) at the beginning of a line in a textbox. Is this possible? If so, what do i have to do?

View 1 Replies View Related

How To Count The Number Of Textboxes

Feb 5, 2007

Hi all,
 
In the report I am working on, I have a "textbox39" in a table which has groups. I want to have another "textbox29" outside the table to count the number of "textbox39"s that are actually displayed and also the number of "textbox1"s that have a certain value (e.g. "1") in the final report. I tried to use "Sum(ReportItems!textbox39.Value)" but the compiler complains
 
Error 1 [rsAggregateReportItemInBody] The Value expression for the textbox 'textbox29' uses an aggregate function on a report item.  Aggregate functions can be used only on report items contained in page headers and footers. d:perfperfreportingprojectPerformanceTestDetails v.3.rdl 0 0 

 
Error 7 [rsReportItemReference] The Value expression for the textbox €˜textbox29€™ refers to the report item €˜textbox39€™.  Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope. d:perfperfreportingprojectPerformanceTestDetails v.3.rdl 0 0 
 
Anybody has any idea how to solve it?
Thanks so much,
Zhiyan

View 2 Replies View Related

Summing

Jul 20, 2005

I am trying to check a list (MyList) against another List(SupplierList).I want sum the Qty's of UniqueID on MyList and extract the sum of thesame UniqueId's on SupplierList.BTW There are more than one instances of Unique Id on each list.The Script below is providing me with the correct answer for someproducts (UniqueId), but incorrect amounts for others.The incorrect answer is always a multiple of the correct answer.What am i doing wrong???Regards,CiaránSELECT MyList.[Unique ID], SupplierList.[Unique ID], Sum(MyList.[SHP_QTY]), Sum (SupplierList.[Qty new])FROM MyList LEFT OUTER JOIN SupplierList ON MyList.[Unique ID]= SupplierList.[Unique ID]GROUP BY MyList.[Unique ID], SupplierList.[Unique ID]

View 1 Replies View Related

Displaying Data From Database In Textboxes

Jan 29, 2008

Im trying to display data from a database based on an input value. The value in the Label12.Text which is("hotmail") is the input value thats stored in the database, this value is been searched for in the strSQL.
 Dim strSQL As String = "SELECT Name FROM Jobseeker WHERE Email='" & Label12.Text & "' "
strSQL = Label5.Text
 
the code builds successfully, but Label5.Text appears blank.
 
 

View 2 Replies View Related

How Do I Populate Textboxes In A Form With A Dataset?

Aug 10, 2004

I have successfully built a form page to enter customer info into sqlserver
2000. I would now like to be able to pull up the customer info and edit it in a Windows form
format rather than a datagrid or datalist. Dell computer is doing what I want to do on there customer checkout page.

I have been looking for examples of
VB code to do this with no luck.

Here is the dataset I use to display customrer info,with my feeble attempt to convert it to a form that populates from the database. (The datalist works fine.) Can someone get me started or send me to
good code examples please?

<%@ Page Language="VB" Debug="true" ContentType="text/html" ResponseEncoding="iso-8859-1" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script language="VB" runat="server">
sub Page_Load(Sender as Object, e as EventArgs)
Dim objConn As SqlConnection = New SqlConnection("Data Source=localhost;" & _
"Integrated Security=true;UID=Newlogin;PWD=cool147;Initial Catalog=PLF")
dim objCmd as SqlDataAdapter = new SqlDataAdapter _
("select * from Customers where CustomerID like 28", objConn)
dim ds as dataset = new Dataset()
objCmd.Fill(ds, "Customers")

firstname.datasource=ds.tables(0).defaultview

'select data view and bind to server control
'MyDataList.DataSource = ds.Tables("Customers"). _
'DefaultView
'MyDataList.Databind()
objConn.close()
end sub
</Script>



<html>
<head>
</head>
<body>
<link REL="stylesheet" HREF="css/maincss.css" TYPE="text/css">
<form runat="server

<asp:TextBox size="12" id="FirstName" runat="server" Font-Size="10pt" Font-Name="tahoma" />
&nbsp;
<asp:RequiredFieldValidator ControlToValidate="FirstName" Display="dynamic" Font-Name="verdana" Font-Size="9pt" ErrorMessage="'Firstname' must not be left blank." runat="server"></asp:RequiredFieldValidator
</Form>

</body>
</html>

View 1 Replies View Related

Blank Space When Hiding Textboxes

Jan 20, 2008

Hello,

Ik have a problem with the free space if I'am hiding textboxes.

I put 2 textboxes in a rectangle and when I hide the rectangle then the textboxes are gone but the space that the rectangle needed is now blank.
Can I suppress the blank space?

Mvg,

Hans

View 3 Replies View Related

Using A Textbox's Value For Format Expression Of Other Textboxes

May 3, 2007

I'm calculating the format string for currency values based upon what's selected as a report currency parameter value. Just to mention, it's a call to a .net dll. Because this call is in every textbox in the details section of the report,I believe it's getting called for every textbox that is displayed on the report - but I really don't need to calculate it at this level. The format string will only be one way per report execution.



Is it possible to only call this function once, stuff the format expression in a textbox (or some method that's similar) and then reference that ReportItems!textbox3.Value for the format string of other textboxes? I threw the calculation in a textbox in the report header and also body, but I get an issue either way:



Report item expressions can only refer to other report items within the same grouping scope or a containing grouping scope



I understand that it doesn't seem possible, but can anyone help me think out of the box? I see that a database call and another dataset holding the FormatString might work... I'll try this out, but in the meantime, is there a solution to not do this?

View 3 Replies View Related

Overlay Of Textboxes Are Not Rendering Properly

Sep 12, 2006

In the page header I have a rectangle that has 2 textboxes on the same line and both are the full length of the page. One textbox has text centered and the other has text right justified. When I render report in Studio or print it, the text appears on the same line and justified like I want, but when the report is rendered when I call the report by URL, the textboxes wrap, like it's taking the width of each textbox in account. Has anyone seen this? Any suggestions on how to overlay text on the same line and the text keep their own individual characteristics?

View 5 Replies View Related

How Can I Split The Rows In 2 Textboxes - Urgent

Feb 20, 2007

Hi,

I have a report and its been populating from a sproc. and i have 2 text boxes called both of them are poplulated by Fields!Investment Names, but right i can display the data left to right but i want to display the Data starting top to bottom and then towards the right.

I tried grouping the data in this way for one text box = CountRows()/2 > 10 . and this shows all the records one below the other, so is there a way that i can display half the records in one text box and the other half in the other text box.

I am going kinda nuts over this. Can someone please help me.

Regards

Karen

View 15 Replies View Related

Placing Textboxes In Headers Of Reports

Jan 4, 2008

Hi,

After exporting the report the column headers in the rreport should not scroll, it has to fix with report headers. If i move the column headers to report header the columns are merging in excel, some columns are hiding.

For ex: I have 10 columns, it has to take from A to K but it is taking A to P. one column is occupying two columns. how to avoid this merging and how to place the column headers in the report header.

Thanks
Dinesh

View 4 Replies View Related

Summing With A Sub-query?

Apr 30, 2008

Hi,

I'm trying to get a sum but not doing too well. I think I need a subquery but am unsure how to phrase it.

Problem:
I need to sum timesheet hours logged at work-code level to project-level (for named projects), where a project consists of 0-to-many work-codes. The 'Project' table is used for both projects and work-codes; the 'pr_code' contains the unique code (i.e. the work-code or the project-code), 'pr_master' field contains the parent. The Timesheet table will contain pr_code's for work-codes, but won't contain an entry for a work-code if no-one has logged any time to a work-code.

Sample input:

Timesheet table
===============
pr_code|ts_hours
QWER.01|6
QWER.01|7
QWER.02|3
QWET.01|2


Project table
=============
pr_code|pr_master
QWER.01|QWER
QWER.01|QWER
QWER.02|QWER
QWET.01|QWET
QWER|QQQQ
QWET|QQQQ
QWEY|QQQQ



Intended output:
For named projects QWER, QWET & QWEY:

QWER|16
QWET|2
QWEY|0



I've got the following so far which almost gets there, but appears to be summing up as it goes i.e. QWER=16, QWET=18, QWEY=18:


SELECT p1.PR_Master AS Expr1, SUM(Timesht.TS_Hours) AS Expr2
FROM Timesheet LEFT OUTER JOIN
Projects ON Timesheet.PR_Code = Projects.PR_Code LEFT OUTER JOIN
Projects p1 ON Timesht.PR_Code = p1.PR_Code
WHERE (p1.PR_Master IN ('QWER', 'QWET', 'QWEY'))
GROUP BY p1.PR_Master


Any help most appreciated.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved