Running Total Of Records Related To Hierarchy

Apr 30, 2008

SQL Server 2005.
Schema is not fixed at this stage - small project, schema can be adapted if neccessary.
This needs to be produced in SQL Server - there is no client application.

Hi

I am really struggling to write the SQL for the following requirement and have got to the stage where I think I need someone to give me some pointers. I can't believe this is a novel requirement but I haven't had to do this before nor can find anything similar when googling.

I have a hierarchy detailing an organisation structure. Eventually the table will store many structures and these may one day be "pseudo structures" to enable really granular reporting. As such I cannot really say "there will never be more than N levels to this hierarchy". Currently I'm using a standard adjacency design. From now on I'll call the items in the table "business units".

There is a further table with a related FK declared which associates people with business units.

Requirement: Return all business units and cumulative total total of employees for each of these units (i.e. the total for each business unit will be the number of people in that unit plus the sum of the people in all the child units).

I've asked this question on dbforums but ended up a blind alley despite Peter's attempt to help. I'm starting again from scratch but feel free to review the (now dead) thread here:
http://www.dbforums.com/showthread.php?t=1629366

To summarise - I tried the CTE route but of course this evaluates the hierarchy from the top down. To fulfill the requirement, the bottom of the structure needs to be the starting point. I also tried reversing the hierarchy so that children, rather than the parent, are stored but my query fails if there are 3+ levels.

Code to reproduce problem (note - I expect that I will need to get the employee count per business unit prior to working with the hierarchy so I have flattened the schema to reflect this - I think I can handle the SQL to get the non-cumulative count per business unit ):
DECLARE @table TABLE
(
unit_codeTINYINT
, parent_codeTINYINT
, headcountINT
, PRIMARY KEY CLUSTERED (unit_code)
)

INSERT INTO @table (unit_code, parent_code, headcount)
SELECT 1, 4, 10 UNION ALL
SELECT 2, 4, 130 UNION ALL
SELECT 3, 10, 93 UNION ALL
SELECT 4, 10, 7 UNION ALL
SELECT 10, NULL, 1

Required output:

unit_codecumulative_headcount
------------------------------------
110
2130
393
4147
10241

Many thanks

View 5 Replies


ADVERTISEMENT

Running Total Of Records Related To Hierarchy

Apr 15, 2008

Hi guysI'm mulling over the best way to do something and would like your input. Forgive me if this is a bit 101 - I haven't ever had to do this in SQL before!Fairly standard set up - Hierarchy table modelling the structure of an organisation.Related table associating members of staff to the hierarchy.I want to return all levels of the hierarchy and for each level I would like to know the total number of people in the level (so for a division it would be the sum of all people in the child teams).Parameters - This table will be modelling many organisations' structures - I cannot guarentee anything like "there will never be more than n levels". As such - I would strongly prefer to have something that is iterative recursive. I can change the schema to suit the method I use if necessary. Database is not transactional - I am not concerned about updating speed. SQL Server 2K5.I've tried CTE but it turns out you cannot use group by in CTEs (even in derived tables). I have not yet tried feeding it a view or similar.I have not tried nested sets, materialised paths, accumulator table - I thought I would see if there is something obvious before I start piddling around with those.Ta!

View 11 Replies View Related

Cumulative Related Records From Hierarchy

May 30, 2008

Morning chucky eggsThis is duplicated over at SQLTeam...except I've moved on a bit so my question to you is different:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=103798SQL 2k5I have a standard hierarchy in an adjacency table modelling business units in an organisation. I have another table associating people with (one single) business unit. I need to return all the people in each business unit in a cumulative manner i.e. a unit will include all people assigned to that unit and assigned to all child units too.Below is1) Set up code (in red)2) Initial query that failed on last data load due to the nature of the data changing and me not considering the ramifications of my initial solution properly3) My new soulution.My question is - is there a better way to accomplish this? I don't rate my code that highly and could do with this being as efficient as possible as at present I can't see it scaling to larger and larger data volumes all that well.USE tempdbgo----------------------------------------------------------------- Set up the structure & data...---------------------------------------------------------------CREATE TABLE dbo.org ( unit_code VARCHAR(10) NOT NULL , parent_code VARCHAR(10) NULL , CONSTRAINT pk_org PRIMARY KEY CLUSTERED (unit_code) WITH (FILLFACTOR = 100) )GO CREATE TABLE dbo.org_peeps ( unit_code VARCHAR(10) NOT NULL , person_code INT NOT NULL , CONSTRAINT pk_org_peeps PRIMARY KEY CLUSTERED (unit_code, person_code) WITH (FILLFACTOR = 100) , CONSTRAINT fk_org_peeps_other_org_peeps FOREIGN KEY (unit_code) REFERENCES dbo.org (unit_code) )GO CREATE VIEW dbo.org_peeps_parents--WITH SCHEMABINDINGAS SELECT org.unit_code , org.parent_code , org_peeps.person_code FROM dbo.org LEFT OUTER JOIN dbo.org_peeps ON org_peeps.unit_code = org.unit_codeGO INSERT INTO dbo.org (unit_code, parent_code)SELECT 'a', 'y' UNION ALLSELECT 'b', 'y' UNION ALLSELECT 'c', 'z' UNION ALLSELECT 'y', 'z' UNION ALLSELECT 'z', NULL--Insert people data (NOTE - the people are in units at the BOTTOM of the structure only)INSERT INTO dbo.org_peeps (unit_code, person_code)SELECT 'a', 1 UNION ALLSELECT 'c', 3 UNION ALL--These data go into intermediate levels of the organisation...SELECT 'y', 4 UNION ALLSELECT 'y', 5 UNION ALLSELECT 'y', 6---------------------------------------------------------------SELECT 'Original query. Because there is data in the intermediate tables --> ' + 'duplicate outputs....'---------------------------------------------------------------;WITH materialised_pathsAS ( SELECT unit_code , parent_code , person_code , unit_path = '/' + CAST(unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org_peeps_parents WHERE parent_code IS NULL UNION ALL SELECT all_people.unit_code , all_people.parent_code , all_people.person_code , mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org_peeps_parents AS all_people INNER JOIN materialised_paths AS mp ON mp.unit_code = all_people.parent_code )SELECT parents.unit_code , children.person_codeFROM materialised_paths AS childrenCROSS APPLY --Correlated derived table - get the child records per unit ( SELECT unit_code , parent_code FROM dbo.org AS parents_sub WHERE children.unit_path LIKE '%/' + parents_sub.unit_code + '/%' ) AS parentsWHERE children.person_code IS NOT NULLORDER BY person_code , unit_code-----------------------------------------------------------------SELECT 'This query returns the correct results but man it is ugly. '+ 'Can it be refined optimised?'-----------------------------------------------------------------;WITH materialised_pathsAS ( SELECT unit_code , parent_code , unit_path = '/' + CAST(unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org WHERE parent_code IS NULL UNION ALL SELECT all_orgs.unit_code , all_orgs.parent_code , mp.unit_path + CAST(all_orgs.unit_code AS VARCHAR(MAX)) + '/' FROM dbo.org AS all_orgs INNER JOIN materialised_paths AS mp ON mp.unit_code = all_orgs.parent_code )SELECT all_orgs.unit_code , org_peeps.person_codeFROM dbo.org_peepsINNER JOIN ( SELECT org.unit_code , mp.unit_path FROM materialised_paths AS mp CROSS APPLY ( SELECT unit_code , parent_code FROM dbo.org WHERE mp.unit_path LIKE '%/' + org.unit_code + '/%' ) AS org ) AS all_orgsON all_orgs.unit_path LIKE '%/' + org_peeps.unit_code + '/'ORDER BY person_code , all_orgs.unit_code--Clean upIF EXISTS (SELECT NULL FROM sys.views WHERE object_id = OBJECT_ID('dbo.org_peeps_parents')) BEGIN DROP VIEW dbo.org_peeps_parentsENDIF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org_peeps')) BEGIN DROP TABLE dbo.org_peepsENDIF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org')) BEGIN DROP TABLE dbo.orgENDAs ever kudos and lavish thanks to anyone that can help :DOh - I nearly forgot - accounting for the Rudy clause - desired output:unit_code person_code---------- -----------a 1y 1z 1c 3z 3y 4z 4y 5z 5y 6z 6

View 14 Replies View Related

Cumulative Related Records From Hierarchy

May 29, 2008

Oh hai

This is closely related to this problem where Ryan & Peter helped me out loads (thanks again guys):
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=101989

I now have a slightly different problem. Instead of returning the cumulative counts of people related to a business unit in the hierarchy I now need to return the actual people records. I had some code, derived from the above, that worked fine until I loaded some new data. It took a while but eventually I figured out the (now obvious) reason for the error. I have tried several changes to the query and not got anywhere. Please could someone have a look and see if you can suggest the required alteration(s)?

USE tempdb
go

---------------------------------------------------------------
--Set up the structure & data...
---------------------------------------------------------------
CREATE TABLE dbo.org
(
unit_codeVARCHAR(10)NOT NULL
, parent_codeVARCHAR(10)NULL
, CONSTRAINT pk_orgPRIMARY KEY CLUSTERED (unit_code) WITH (FILLFACTOR = 100)
)
GO

CREATE TABLE dbo.org_peeps
(
unit_codeVARCHAR(10)NOT NULL
, person_codeINTNOT NULL
, CONSTRAINT pk_org_peepsPRIMARY KEY CLUSTERED (unit_code, person_code) WITH (FILLFACTOR = 100)
, CONSTRAINT fk_org_peeps_other_org_peepsFOREIGN KEY (unit_code) REFERENCES dbo.org (unit_code)
)
GO

CREATE VIEW dbo.org_peeps_parents
--WITH SCHEMABINDING
AS
SELECT org.unit_code
, org.parent_code
, org_peeps.person_code
FROMdbo.org
LEFT OUTER JOIN
dbo.org_peeps
ONorg_peeps.unit_code= org.unit_code
GO

INSERT INTO dbo.org (unit_code, parent_code)
SELECT 'a', 'y'UNION ALL
SELECT 'b', 'y'UNION ALL
SELECT 'c', 'z'UNION ALL
SELECT 'y', 'z'UNION ALL
SELECT 'z', NULL

--Insert people data (NOTE - the people are in units at the BOTTOM of the structure only)
INSERT INTO dbo.org_peeps (unit_code, person_code)
SELECT 'a', 1UNION ALL
SELECT 'c', 3

---------------------------------------------------------------
SELECT'This works great....'
---------------------------------------------------------------
;WITHmaterialised_paths
AS
(
SELECTunit_code
, parent_code
, person_code
, unit_path= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents
WHEREparent_code IS NULL
UNION ALL
SELECTall_people.unit_code
, all_people.parent_code
, all_people.person_code
, mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents AS all_people
INNER JOIN
materialised_paths AS mp
ONmp.unit_code= all_people.parent_code
)

SELECTparents.unit_code
, children.person_code
, children.unit_path
FROMmaterialised_paths AS children
CROSS APPLY--Correlated derived table - get the child records per unit
(
SELECTunit_code
, parent_code
FROMdbo.orgAS parents_sub
WHEREchildren.unit_path LIKE '%/' + parents_sub.unit_code + '/%'
) AS parents
WHEREchildren.person_code IS NOT NULL
ORDER BY person_code
, unit_code


--Add data to INTERMEDIATE levels of organisation
INSERT INTO dbo.org_peeps (unit_code, person_code)
SELECT 'y', 4UNION ALL
SELECT 'y', 5UNION ALL
SELECT 'y', 6

---------------------------------------------------------------
SELECT'Since there is now data in the intermediate tables there are ' +
'duplicate outputs....'
---------------------------------------------------------------
;WITHmaterialised_paths
AS
(
SELECTunit_code
, parent_code
, person_code
, unit_path= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents
WHEREparent_code IS NULL
UNION ALL
SELECTall_people.unit_code
, all_people.parent_code
, all_people.person_code
, mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents AS all_people
INNER JOIN
materialised_paths AS mp
ONmp.unit_code= all_people.parent_code
)
SELECTparents.unit_code
, children.person_code
, children.unit_path
FROMmaterialised_paths AS children
CROSS APPLY--Correlated derived table - get the child records per unit
(
SELECTunit_code
, parent_code
FROMdbo.orgAS parents_sub
WHEREchildren.unit_path LIKE '%/' + parents_sub.unit_code + '/%'
) AS parents
WHEREchildren.person_code IS NOT NULL
ORDER BY person_code
, unit_code

---------------------------------------------------------------
SELECT'This is the output I want - but I can''t simply apply distinct - the volumes of data preclude this'
---------------------------------------------------------------
;WITHmaterialised_paths
AS
(
SELECTunit_code
, parent_code
, person_code
, unit_path= '/' + CAST(unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents
WHEREparent_code IS NULL
UNION ALL
SELECTall_people.unit_code
, all_people.parent_code
, all_people.person_code
, mp.unit_path + CAST(all_people.unit_code AS VARCHAR(MAX)) + '/'
FROMdbo.org_peeps_parents AS all_people
INNER JOIN
materialised_paths AS mp
ONmp.unit_code= all_people.parent_code
)
SELECTDISTINCT
parents.unit_code
, children.person_code
, children.unit_path
FROMmaterialised_paths AS children
CROSS APPLY--Correlated derived table - get the child records per unit
(
SELECTunit_code
, parent_code
FROMdbo.orgAS parents_sub
WHEREchildren.unit_path LIKE '%/' + parents_sub.unit_code + '/%'
) AS parents
WHEREchildren.person_code IS NOT NULL
ORDER BY person_code
, unit_code


--Clean up
IF EXISTS (SELECT NULL FROM sys.views WHERE object_id = OBJECT_ID('dbo.org_peeps_parents')) BEGIN
DROP VIEW dbo.org_peeps_parents
END

IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org_peeps')) BEGIN
DROP TABLE dbo.org_peeps
END
IF EXISTS (SELECT NULL FROM sys.tables WHERE object_id = OBJECT_ID('dbo.org')) BEGIN
DROP TABLE dbo.org
ENDJust paste and run the code and hopefully it should be reasonably clear. If you need any further info please let me know

View 5 Replies View Related

Looping Thru Records To Find Related Records

Oct 31, 2007

Hi, I have had this problem for a while and have not been able solve it.

What im looking at doing is looping thru my patient table and trying to organise the patients in to there admission sequence, so when patient "A" comes in and is treated at my hospital and is discharged and admitted to another Hospital within one day then patient "A" will get a code of 1 being there first admission.

then if patient "A" is admitted again but there admission date is greater than one day they get a code of 2 being for there second admission but will need to loop thru table looking for other admissions and discharges.

The table name is Adm_disc_Match_tbl

Basically what i have 4 fields.
Index_key = which is the patient common link (text)
ur_episode = this wil change for each Hospital (text)
Admission_datetime = patient admission date and time (datetime)
Discharge_datetime = patient discharge date and time (datetime)

example of data


Code: ( text )
Index_key,ur_episode,Admission_datetime,discharge_ datetime
HERBERT-7/1929,513884-1686900,4/07/2006 10:58,17/07/2006 13:37
HERBERT-7/1929,C023092-1698859,17/07/2006 13:20,24/07/2006 0:30
ELSIE-5/1916,G148445-1720874,8/08/2006 11:00,30/08/2006 10:00
STANISLAWA-3/1918 ,G119981-1720045,8/08/2006 13:01,22/08/2006 12:13
FREDA-11/1925,183772-1998910,27/03/2007 9:53,3/04/2007 11:06
FREDA-11/1925,G147858-2007408,3/04/2007 10:49,26/04/2007 12:39
FREDA-11/1925,183772-2037727,28/04/2007 17:05,9/05/2007 11:41
FREDA-11/1925,G147858-2052082,9/05/2007 12:00,25/05/2007 11:17


If anyone could help it would be much appreciated.

View 6 Replies View Related

Deleting Related Records.

Oct 17, 2007

Hi
I wanted to do a delete rows from a group of table. These tables have a common column UserID. I heard that there is something called ondelete cascade. But I don't know how to set it up and utilise it. Could someone tell me how to do it. Or point me to a tutorial which shows how to do it.
Thanks

View 4 Replies View Related

Count Of Related Records??

Jul 23, 2005

Hi,I was wondering if it was possible to build a query that will include acolumn that will provide a count related records from another table.Although there is a way to achieve this through programming in thefront end, I would like to know if it possible to achieve the samething through a SQL statement alone.For example, say you have two related tables, Invoices andInvoiceItems. InvoiceID is the primary key of Invoices.Invoices tableInvoiceID PO_Num CompanyID-------------------------------1 37989 32 87302 43 78942 3InvoiceItems tableItemID InvoiceID PartNo Qty---------------------------------------1 1 ABA 32 1 ASLKDJ 23 1 9LF 84 2 IEPOW 185 2 EIWPD 36 2 DSSIO 17 2 EIWP 58 2 DC93 49 3 85LS0 8Then a query that has the Invoices table plus a count of InvoiceItemsfor each InvoiceID would generate this:InvoiceID PO_Num CompanyID ItemCount-------------------------------------------------1 37989 3 32 87302 4 53 78942 3 1Does anyone have any ideas how this would be done?Thank you.

View 1 Replies View Related

Running Total

Mar 8, 2006

Help can anyone see anything wrong with this I am trying to get a running total to show on my form for the users to see, as a team member makes a payment they should be able to see the remaining amount

SELECT SUM(DISTINCT TotalPayments) AS Payment, SUM(AmountDue) AS Due, SUM(FinePayment) AS Fine, [TM #]
FROM dbo.FinePayments_Tbl
GROUP BY [TM #]
HAVING ([TM #] = 9282)

View 2 Replies View Related

Running Total In SQL

Jul 24, 2006

Good day,

I have been trying to figure out how to get a running total using my sql statement and just can't.
My current table has 2 fields:
Value
Name

Then there are a whole bunch of entries:
31 Jon
23 George
3 Bob
4 Jon
9 Jon
98 Bob

What I would like is from my outputed SQL is something like:
23 Geogre 23
43 Jon 66
101 Bob 167

Can anyone help me, this is really confusing for me and need it to work. I am not able to change the table layout in anyway what so ever and only have the 2 fields, Value Name to use.

Please help me

View 14 Replies View Related

Running Total

Mar 25, 2008

I'm using the following codein a MSSQL/PHP intranet project for paging through records:



DECLARE
@PageSize INT,
@PageNumber INT,
@FirstRow INT,
@LastRow INT

SELECT@PageSize = $pageLength,
@PageNumber = $pageNumber;

SELECT@FirstRow = ( @PageNumber - 1) * @PageSize + 1,
@LastRow = (@PageNumber - 1) * @PageSize + @PageSize;


WITH results AS


( SELECT


t1.Mortgage_Number,
t1.Advance_Number,
t1.Effective_Date,
t1.Due_Date,
t1.Transaction_Type,
t1.Posting_Type,
t1.Transaction_Amount,
t1.Account_Source,
t1.Sub_Posting_Type,
CASE
WHEN t2.description IS NOT NULL
THEN t2.description
ELSE t1.Transaction_Description
END AS Transaction_Description,
ROW_NUMBER() OVER (ORDER BY [Effective_Date] $direction) AS RowNumber

FROM format_transactions AS t1

LEFT JOIN sd_posting_types AS t2
ON t1.Transaction_Type = t2.Transaction_Type
AND t1.Posting_Type = t2.Posting_Type
AND t1.Sub_Posting_Type = t2.Sub_Posting_Type
AND t1.Account_Source = t2.Account_Type

WHERE (Mortgage_Number = '$mortgage' $additional_date_params)



)

SELECT

Mortgage_Number,
Advance_Number,
Effective_Date,
Due_Date,
Transaction_Type,
Posting_Type,
Transaction_Amount,
Account_Source,
Sub_Posting_Type,
Transaction_Description

FROM results

WHERE RowNumber BETWEEN @FirstRow AND @LastRow
ORDER BY [Effective_Date] $direction

View 7 Replies View Related

How To Keep A Running Total

Aug 28, 2005

Hi there. As you will see from my questions, I am an SQL newb. Idabble but never get to spend enough time to get proficient so base anyfeeedback on that basis please. This is all theoretical information atthis point so I am also going to post this in a MySQL related group. Iwill create some designs and post back to the group if I get anyfeedback I can use.Problem:I would like to be able to keep a running percentage total in a fieldassociated with my users. In order to calculate the totals, I willparsing a text file with entries from my users in it. The parser (AWKetc) will search the file for specific text, compare it to informationin another file and output some entries into a csv file which cansubsequently be imported into the database.The users make posts that are considered good and bad and the ratingpercentage must be based on that. For example, if a user makes 10posts in a day, and 4 of them are considered 'bad' by my criterion, therating should reflect a score of 60% for that day.However, the rating is an ongoing value that will be adjusted daily andI must maintain a running total against all previous posts. So, letssay on day two the same user posts 10 more times and 3 are 'bad', Imust adjust his score to reflect a total percentage rating which wouldthen be 20 posts with 7 being bad for an overall rating of 65% etc.My question is, how should I go about recording and calculating allthis information?Here are my thoughts. I have a users table with a field calledsomething like 'Rating' which stores the overall value (65% etc). Thisvalue would have to be calculated from fields in another table like'Posts' which records each post in 'Good' and "Bad' fields thatincrement. The Good and Bad fields would be incremented (populated)from the text that gets imported etc.Looking for thoughts from experienced db designers please. Thanks alot in advance for any responses.

View 5 Replies View Related

Running Total Of Another Running Total

Mar 28, 2007

I have a running total (RunningValue) in one of the column, in my report. I want another running total of that existing running total. This is not allowed in SQL Reporting Services (Aggregate inside an aggregate function is not allowed).

How do I do that? Is it possible to use Visual Basic...using Code function? I'm not familiar with VB syntaxes. Would someone please help me with this... how to pass values to VB code... do the calculation... and display the answer in another column in the report.

Is there any other way of doing it (without using VB)?

View 1 Replies View Related

Delete Records In Two Related Tables?

Jan 30, 2014

I am trying to delete the records in 2 related tables. The 'child' table has a field called [SETA],

I want to delete all the records in this table that contain the same info, as well all the fields in the parent table that is related to this table. They share the [ID] field as key. This is my code:

Code:
DELETE FROM Student a full outer JOIN Qualification b on a.[ID] =b.[ID] WHERE b.[SETA] = @SETA

View 4 Replies View Related

How To Insert Records Into Related Tables?

Sep 24, 2007

Hi,
I have 3 tables:
Employees with the fields:idEmployee and employeeName
Roles with the fields:idRole and roleName.
An employee can have one or many roles.
I created the table EmployeeRoles with the fields: id,idEmployee,idRole.
idEmployee and idRole are foreign keys.
I want to insert a new employee into Employees table, but I have to insert idEmployee field into EmployeeRoles table.

How can I do this?
Thanks

View 6 Replies View Related

Running Total In Table

Apr 28, 2004

Hi, i have an assets table with a purchase cost column and an updates table with an update cost column.

is there any way for me to add a total cost column to the assets table that will automagically add the purchase cost to the sum of the updates costs for each asset so that when queried, the total cost column can be returned with no calculation in the client?

am i looking at creating a view with the calculation in it?

If so, how do i define the total cost column please?

TIA
Fatherjack

View 3 Replies View Related

Running Total Cursor

Apr 16, 2006

I have two tables. ID table that has these fields
Code_id,YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.

View 1 Replies View Related

Trying To Get Running Total In Server

Jun 2, 2015

get Running Total in SQL Server.I found that new OVER clause.

SELECT
CompanyId
,IMNId
,RowNumber
,Sum(Qty) OVER(ORDER BY CompanyId, IMoneyNetId, RowNumber ROWS UNBOUNDED PRECEDING) As CumulativeShareQty2
From#ReportData
WhereSequenceNumber <> 3
Group by CompanyId
,IMNId
,RowNumber

But somehow i get below error, when i try to run above query:

Msg 8120, Level 16, State 1, Line 957
Column '#ReportData.Qty' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

View 7 Replies View Related

Running Total Issue

May 28, 2008

Using a trigger that when a record is inserted it calculates a running total after the insert. Code below:
CREATE TRIGGER [UPDATEOrderRebatewithRunningTotal] ON dbo.OrderRebate
AFTER INSERT
AS

DECLARE@RunningTotal DECIMAL(16, 6),
@OrderNumber VARCHAR(8)

UPDATEOrderRebate
SET@RunningTotal =CASE
WHEN @OrderNumber IS NULL THEN Ext_Rebate
WHEN Ord_No <> @OrderNumber THEN Ext_Rebate
ELSE round((@RunningTotal + Ext_Rebate),2)
END,
RunningRebateAmt = @RunningTotal,
@OrderNumber = Ord_No


My problem is that it is not calculating the last record.
Order is entered with 4 items I get the following.

Ord_no ext_rebate RunningRebateAmt
102 2.095 2.095
102 1.744 3.84
102 2.41 6.25
102 .0415 NULL

View 2 Replies View Related

Running Total Cursor

Apr 16, 2006

I have two tables. ID table that has these fields
Code_id,YearPeriod, ReachedDate fields. And table
Amount that has Code_id,Amount,Date fields. What I
need to do is write a cursor that when the
amount.Amount adds up to $100.00 it Updates the
ReachedDate in the ID table to the amount.date fields
value of that record. Any help would be greatly appreciated.

View 6 Replies View Related

How To Trace The Records That Have Been Related To The Child Table

Aug 23, 2007

 
The problem of mine is, I have a datagrid, Which displays data from a Employee(parent) table.
Now I want to delete some records based on the user selected checkbox,only those records which has no related records in the EmployeeProject(child) can be deleted.I want to know which are all the record that cannot be deleted?
How can I achieve this?
 

View 3 Replies View Related

Transact SQL :: How To Get Time Span Across Several Related Records

Nov 19, 2015

I need a boost to understand how to get started on solving this SQL Query.

TicketId Seq StatusDateTime

T10001 1 2015-05-04 15:54:00.000
T10001 2 2015-05-04 16:35:00.000
T10001 3 2015-05-04 16:42:00.000
T10001 4 2015-05-05 08:19:00.000
T10002 1 2015-05-04 15:56:00.000
T10002 2 2015-05-04 15:57:00.000

I need to find the total StatusDateTime for each TicketId
I need to find the average StatusDateTime for all TicketIDs
Ex. TicketId, "T10001", has 4 records based on the Seq column.

By using this, I should be able to find the amount of time between the first Seq and the last Seq to get a total time span for Ticket.

Expanding on this, I should be able to add up all of the Ticket's calculated time spans and divide by the number of tickets to get the average time span.

View 6 Replies View Related

Run-Time Running Total *(SOLVED)

May 14, 2008

I am re-writing a old query that update's it's value based on values at run time. Is there anyway to accomplish the following query where column s1.AI referances the values that were updated during run-time.

I want to avoid a Loop (i.e. cursor or a CTE).

Please help!



Declare @Stage Table(StartDate datetime,BenefitInterestID INT PRIMARY KEY, Amount MONEY, InterestAmount MONEY, Interest DECIMAL(10, 4), ai DECIMAL(10, 4))
Insert Into @Stage
Select
convert(datetime,'2006-12-01 00:00:00.000',101) as StartDate,1 as BenefitInterestID,1701.00 as amount,79.605 as InterestAmount ,0.1000 as Interest,0.0000 as ai
Union all
select '2007-12-01 00:00:00.000',2,172.80,7.92,0.0500,0
Union all
select '2008-12-01 00:00:00.000',4,0.00,0.00,0.0700,0




UPDATEs1
SETs1.ai = s1.Interest * coalesce((SELECT SUM(coalesce(s2.Amount,0) + coalesce(s2.InterestAmount,0)+coalesce(s2.ai,0)) FROM @Stage AS s2 WHERE s2.StartDate < s1.StartDate ),0)

FROM@Stage AS s1

select * from @Stage

My desired Results are


2006-12-01 00:00:00.00011701.0079.6050.10000.0000
2007-12-01 00:00:00.0002172.807.920.050089.0303
2008-12-01 00:00:00.00040.000.000.0700143.5300


But I am geting

2006-12-01 00:00:00.00011701.0079.6050.10000.0000
2007-12-01 00:00:00.0002172.807.920.050089.0303
2008-12-01 00:00:00.00040.000.000.0700137.2928


Due to as you can see on row 3 that the amount does not factor in the previous Years ai column when I sum, due to the update takes place at runtime.

Please help!

View 15 Replies View Related

Updating Table With Running Total

May 28, 2008

Have a table where I need to update a field with a running total.

Table OrderRebate
I need to calculate a running total on ext_rebate for each order number. So I need the running total to reset to zero when the order number changes.

I've got as far with the code below but it never resets. Does a running total for every record.

DECLARE @runningtotal decimal(16, 6)
SET @runningtotal = 0
UPDATE OrderRebate
SET @runningtotal = RunningRebateAmt = @runningtotal + ext_rebate
WHERE ord_no = ord_no

View 3 Replies View Related

Running Total On Rows Between Groups

Dec 5, 2006

I want to write a stored procedure to add all of the flying time on a given airplane for each flight until now and then reset the total when it gets to a new airplane. I want to be able to pull from the table all flying time from a given date for a given airplane.

For example:

AircraftNo Date Flight_Hrs Total
38 3/1/06 1 4.5
38 5/10/06 1.5 3.5
38 7/10/06 2 2

100 8/19/03 4 8.5
100 10/04/04 3 4.5
100 9/17/06 1.5 1.5

I pulled the following code from the internet but I'm getting errors.

select AircraftNo
,Actual_departing_date,Flight_Hours
,sum(Flight_hrs) over partition by AircraftNo
order by Date
rows between current row and unbounded following) total
from (Select Distinct
F.AircraftNo
,F.Date
,C.Flight_Hrs
From GDB_01_4_Test.dbo.Flight_Log F,
GDB_01_4_Test.dbo.Flight_Cycle_Count C
Where F.Doc_No = C.Flight_log_Doc_No

Am I doing something wrong. I'm getting the following syntax errors (Using SQL 2000):

Server: Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'over'.
Server: Msg 170, Level 15, State 1, Line 18
Line 18: Incorrect syntax near 'Flight_log_Doc_No'.

View 5 Replies View Related

Running Total Across Matrix Groups

Dec 17, 2007

Hi

I have a report that calculates shipping quantities based on customer orders, and color codes them based on whether or not we have stock for a full shipment (green), have partial stock for a shipment (blue), or if we have none (red). Every week we get customer orders and build accordingly.

I have this all worked out except for parts with multiple ship to locations. RS seems to parse the entire row of the first ShipTo, then the second ShipTo. I'm using the running total function to calculate if there is a need or not. Anybody have any ideas?





Here is an example of the output I am getting. On the 17th, the 360 should be in green not red. I double checked the grouping, and it is on part number. This is for a matrix.

http://img519.imageshack.us/img519/8650/productionqg0.png

View 1 Replies View Related

Transact SQL :: How To Get Running Total In Server

Oct 21, 2015

I need to get a cumulative total for row by row basis. I need this grouped on name, id, year and month. ID is not a auto incremented number. ID is  a unique number same as name. The out put I need is as below,

Name     ID     Year   Month  Value  RunningValue
XX         11     2013  Jan      25          25
xx          11     2013  Feb      50          75
yy          22     2015   Jan     100        100
yy          22     2015   Mar     200        300

How I could get this query written? I am unable to use SQL Server 2012 version syntaxes. Writing a cursor would slow the process down because there is a large data set.

View 12 Replies View Related

SQL Server 2012 :: Getting Related Records Set Based Results

Sep 29, 2015

I have a table that has multiple transactions for stock items.

This table holds all records relating to items that are inducted onto the system and there movement. For each stock item i am interested in getting the drop destination, if it has one, and only when it follows the sequential order of "Inducted>OnTransport>Dropped" (this sequence isn't always the case). Also note the CreatedDate for the Inducted and OnTransport records for the valid sequences are always the same. Below is a valid sequence for a stock item so i would want to return 'Lane01' for the Destination of this occurrence of the stock item, if this item didn't have a valid drop location then destination would be blank. Also note each stock item can be inducted more than one time per-day.

I think i have managed to build the below sql but it will only do one item at a time, so would have to wrap it in a function. Is there a way of writing a set based select statement that gets all the inducted items and for the ones that do follow the "Inducted>OnTransport>Dropped" return the destination it was dropped at? I've attached scrips below:

DECLARE @StockItemID nVarchar(128)
DECLARE @CreateDate DATETIME

Set @StockItemID='8cbe17da-6079-4170-b27a-41c0d38830f6'
Set @CreateDate = CAST('2015-08-31 13:52:39.890' AS datetime)

[Code] ....

View 9 Replies View Related

Terminology Question - Set Of Related Records In Multiple Tables

Jul 20, 2005

Terminology question:Is there a term for a set of records related directly or indirectly by keyvalue in several tables? For example, a single invoice record and its lineitem records -or- a single customer, the customer's orders, the order linesfor those orders, the customer's invoices, and the invoice lines for thoseinvoices.I'm thinking the term might be graph, but I'm not at all certain of this.Thanks,Steve J

View 17 Replies View Related

SQL Server 2012 :: Running Total On 2 Columns

Jul 25, 2014

Table A has day to day transactions, Table B has beginning balance. I'd like to get a running total balance day to day. Really what I want to do is use the previous days total to add the current days transaction to, but I don't know how to do it. The basic layout is below, but as you can see, I'm not getting the totals correct.

create table #current(acctNum int,
dates date,
transtype char(10),
amt INT
)
insert into #current(acctNum, dates, transtype, amt)

[Code] .....

View 4 Replies View Related

SQL Server 2012 :: Running Total With Thresholds?

Mar 18, 2015

I have a data table in below format and the need the desired output in 2nd table format

TABLE1

RANKBOOLEANREVENUE
1TRUE100
2FALSE150
3FALSE200
4FALSE250
5FALSE300

[code]....

Desired Output to be:

RANKBOOLEANREVENUERUNNINGTOTAL
1TRUE 100250
2FALSE150400
3FALSE200600
4FALSE250850
5FALSE3001150

[code]....

View 3 Replies View Related

Running Total Count In Stored Procedure

Jul 20, 2005

in my procedure, I want to count the number of rows that have erroredduring an insert statement - each row is evaluated using a cursor, soI am processing one row at a time for the insert. My total count tobe displayed is inside the cursor, but after the last fetch is called.Wouldn't this display the last count? The problem is that the count isalways 1. Can anyone help?here is my code,.... cursor fetchbegin ... cursorif error then:beginINSERT INTO US_ACCT_ERRORS(ERROR_NUMBER, ERROR_DESC, cUSTOMERNUMBER,CUSTOMERNAME, ADDRESS1, ADDRESS2, CITY,STATE, POSTALCODE, CONTACT, PHONE, SALESREPCODE,PRICELEVEL, TERMSCODE, DISCPERCENT, TAXCODE,USERCOMMENT, CURRENCY, EMAILADDRESS, CUSTOMERGROUP,CUSTINDICATOR, DT_LOADED)VALUES(@ERRORNUM, @ERRORDESC,@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY,@STATE, @POSTALCODE, @CONTACT, @PHONE, @SALESREPCODE,@PRICELEVEL, @TERMSCODE, @DISCPERCENT, @TAXCODE,@USERCOMMENT, @CURRENCY, @EMAILADDRESS, @CUSTOMERGROUP,@CUSTINDICATOR, @DTLOADED)SET @ERRORCNT = @ERRORCNT + 1END --error--FETCH NEXT FROM CERNO_US INTO@CUSTOMERNUMBER, @CUSTOMERNAME, @ADDRESS1, @ADDRESS2, @CITY, @STATE,@POSTALCODE, @CONTACT,@PHONE,@SALESREPCODE, @PRICELEVEL,@TERMSCODE,@DISCPERCENT, @TAXCODE, @USERCOMMENT, @CURRENCY,@EMAILADDRESS,@CUSTOMERGROUP, @CUSTINDICATOR, @DTLOADED--IF @ERRORCNT > 0INSERT INTO PROCEDURE_RESULTS(PROCEDURE_NAME, TABLE_NAME, ROW_COUNT,STATUS)VALUES('LOAD_ACCOUNTS', 'LOAD_ERNO_US_ACCT', @ERRORCNT, 'FAILEDINSERT/UPDATE')END -- cursorCLOSE CERNO_USDEALLOCATE CERNO_US

View 1 Replies View Related

Running Total: Summing On Distinct Amount

May 9, 2007

I am trying to do a sum on a goal amount that is repeated for each record. But what is the the forumla to only sum on the distinct goal amount.



Example:

Month Year Goal Other Value

March 2007 500 5568

March 2007 500 5568

March 2007 500 5569

April 2007 600 5568

April 2007 700 5569



Total (I am receive)

March-April 2007 1600 5568

March-April 2007 1200 5569



Total (I excpect)

March-April 2007 1100 5568

March-April 2007 1200 5569



I haven't found anything online to help.



Thanks,

Sam

View 5 Replies View Related

Calculated Field In Footer...running Total

Apr 26, 2007



Hello,



How do I add unique values on the report? For example say I have this in my report:



Customer: Food Purchased: Amount:

Judy Cat Food $12

Sarah Dog Food $13.50

Diane Rabbit Food $17

Jason Dog Food $16

Tammy Dog Food $15



In the footer of the report I want to print a summary box that looks like this:



Product: Number Purchased: Total:

Cat Food 1 $12

Dog Food 3 $44.50

Rabbit Food 1 $17





How do I do this?



Thanks!

View 9 Replies View Related







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