T-SQL (SS2K8) :: Summing - Adding A Negative And A Positive

Mar 20, 2014

I have a query that brings in a result from a table that I have to display as a negative, so I use ABS (tbname.fieldname) *-1

This works fine. (Learnt it here from a previous post.)

I have another query that brings in a result from the same table but as a postive. So that works fine too.

Now, I need to sum them together in a new query. If the total value is a positive, fine. If the total value is a negative, it displays as a positive.

My question. Is it actually possible to achieve what I'm trying to do?

Output example below - narrow result for testing.

idpt2 Value2SumQty
N1141307-80970 3.80 2
N1141S2G00009D070 26.16 2

The 3.80 is correct, it is a positive.

The 26.16 is incorrect as it is the output from sum 235.44 -261.6 so I need it to display as -26.6

View 9 Replies


ADVERTISEMENT

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

QA Returning Negative #s As Positive

Mar 30, 2004

Hey guys,

I have a very peculiar issue going on. I have a table that contains a decimal(18,2) column called "Amount". Looking at this table through Enterprise Mgr, I can see that there are values in there that are negative. However, when I run a query in Query Analyzer, it displays all the negative values as positives.

The only workaround I've found right now is to change the column type to "real" and then change it back to decimal(18,2), and it starts showing the negatives as negative. However, without performing this absurd workaround, it doesn't work.

Is there a known bug in QA that would manifest itself as this? What is the cause for this?

Thanks in advance.

View 4 Replies View Related

Positive Negative Values In SUM

Apr 25, 2008





Hi,


I have table with a column which has both negative and positive values.

I want to calculate the sum of positve values and the sum of negative values from that column separately. Is it possible.

Plase help me.


Ex: My Input Values are

column1
-186.5499257
-141.8985222
-95.3846883
-71.1359768
-69.39071172
30.44750844
40.33666032
53.81527872
98.2967252
112.948667

OutPut goes like This

Column1 Column2
-186.5499257 null
-141.8985222 null
-95.3846883 null
-71.1359768 null
-69.39071172 -564.36
30.44750844 null
40.33666032 null
53.81527872 null
98.2967252 null
112.948667 335.84

View 5 Replies View Related

How Do You Convert A Number From Negative To Positive?

Jan 18, 2008

I was just wondering if there is a way of converting a number from negative to positive within Reporting Services, ie remove the "-" sign from the front of the number? One of the columns within my report contains a calculation which results in an integer, but it is always a negative number, even though it should really be positive (it would take too long to describe the exact reason for this, but it's to do with the underlying database, which I don't have access to anyway). When I right-click on the relevant cell within the table in Layout view in the Report Designer and go to Expression, there is a list of Conversion functions under the "Common Functions" heading but I can't find anything there which would convert a number from negative to positive. Does anyone know how this can be achieved?

View 5 Replies View Related

How Do I Delibrately Make A Positive Value A Negative Value

Feb 5, 2007



I want to convert a positive value to a negative value.

How do I do it...

View 3 Replies View Related

Treat Data Differently For Positive Vs Negative #

Oct 11, 2005

Ok, I have a table with IP addresses stored in decimal format using both positive and negative numbers.
The way that they are stored is:
Positve
1 thru 2147483647 = 0.0.0.1 - 127.255.255.255
Negative
-2147483648 thru -1 = 128.0.0.0 - 255.255.255.255
Conversion
positive
x/2^24 . (x/2^24)/2^16 . etc . etc
negative
(x+2^32)/2^24 . ((x+2^32)/2^24)/2^16 . etc . etc

I have a script which works by using UNION and the WHERE statements are x>0 x<0

My problem is I need to use a 3rd party app to run the script (McAfee ePO). McAfee does not recognize the UNION. My question is, can I acheive the same results as the script below, without using UNION.

SELECT ReportFullPathNode.FullPathName,
cast(cast(IPSubnetMask.IP_Start as bigint)/16777216 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_Start as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_Start as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_Start as bigint)%16777216%65536%256 as varchar),
cast(cast(IPSubnetMask.IP_End as bigint)/16777216 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(IPSubnetMask.IP_End as bigint)%16777216%65536%256 as varchar),
cast(IPSubnetMask.LeftMostBits as varchar),
IPSubnetMask.IP_Start
FROM IPSubnetMask, ReportFullPathNode ReportFullPathNode
WHERE IPSubnetMask.IP_Start>0 and IPSubnetMask.ParentID = ReportFullPathNode.LowestNodeID
UNION ALL
SELECT ReportFullPathNode.FullPathName,
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)/16777216 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_Start as bigint)%16777216%65536%256 as varchar),
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)/16777216 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)%16777216/65536 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)%16777216%65536/256 as varchar) + '.' +
cast(cast(4294967296+IPSubnetMask.IP_End as bigint)%16777216%65536%256 as varchar),
cast(IPSubnetMask.LeftMostBits as varchar),
IPSubnetMask.IP_Start+4294967296
FROM IPSubnetMask, ReportFullPathNode ReportFullPathNode
WHERE IPSubnetMask.IP_Start<0 and IPSubnetMask.ParentID = ReportFullPathNode.LowestNodeID

View 2 Replies View Related

Stacked Column Bar With Negative And Positive Value. Cannot Cross At Zero

Jul 27, 2007



I've a stacked column chart (vertical) both with positive and negative value.

I want an horizontal line only for the value = 0.

I tried to add a serie and to plot it as line, but I see it in the legend and I don't want to see it.

Can I show the gridlines only for the value 0?
Simona

View 1 Replies View Related

Group By Positive Or Negative Number In Table

May 7, 2008

Sorry if this question has been answered before--i tried a search but nothing came close to what i needed.

I have a report I am creating where I need to group on whether a number is positive or negative. The number will never be zero. This number is a decimal (currency), if that makes a difference.

I have tried creating a formula field with the following expression:





Code Snippet

IIf(Fields!Num.Value > 0, 1, 0)

I tried creating a group using this field (essentially, positive = 1 and negative = 0), but it gave me no results at all.

If you need any more information, please let me know and I will provide what I can. Thanks for your answers!

View 3 Replies View Related

Creating A Blank Row In A Table Between Positive &&amp; Negative Values

Oct 29, 2007

I have a table that displays a bunch of data based on their percentage changes.

There is a parameter (let's say 5%) that the user can set to display only the data which either increased more than 5% or decreased more than 5%.

I want to have a space between the positive changes and the negative changes. ie, to create a blank row between the +'s and the -'s. Does anyone know of a way to do this?

Thanks,
Steven

View 3 Replies View Related

SQL Server 2012 :: Pattern Matching (positive Across Records - Negative Within A Field)

Jan 6, 2015

I am doing some analysis on our customer base and their payment profiles. I have generated two profile strings, one for whether the balance of an account has gone up or down and one for the size of the balance in relation to the normal invoice amount for the customer. So (for example) the balance movement string will look like this:

UUUDUUUDUUUD-D00 Where U = Up, D = Down, - = no change and 0 = no change and no balance

I want to analyse these strings in two ways. The first is that I want to find customers with a similar pattern: in the example below the first and last patterns are the same, just one out of sync but should be considered the same

Movement Multiple CountRecords
UUUDUUUDUUUD1230123012301175
------------0000000000001163
UDUUUDUUUDUU3012301230121082

The second type of analysis is to find customers whose pattern has changed: in the examples above the patterns are repeated and therefore 'normal' in the records below the patterns have changed in that the first part does not match the second part.

Movement Multiple CountRecords
UUDUUUDUUUUU-----------07
UDUUUDUUUUUU------------7

good way to approach this without either a cursor or a hidden REBAR. The challenge as I see it is that I have to interrogate every string to find out if there is a repeating pattern and if so where it starts and how long it is (heuristic because some strings will start with a repeating pattern and then the pattern may change or deteriorate) and then compare the string for N groups of repeating characters to see if and when it changes and I can't think of an efficient method to do this in SQL because it is not a set based operation.

View 9 Replies View Related

Reporting Services :: Make Bar Chart Color Red If Number Is Negative And Green If It Is Positive?

May 27, 2015

I have a 2012 report builder chart that has two series (one area chart and one bar chart) combined into one chart.  The problem I'm having is the bar chart has much smaller numbers than the area chart and the scaling is messed up.

Is there any way to put the bar chart on the right axis and keep the area chart on the left axis?  My goal is to increase the size of the bar chart in relation to the area chart.

Also, is there any way to make the bar chart color red if the number is negative and green if it is positive?

View 4 Replies View Related

Query Analyzer Shows Negative Numbers As Positive Numbers

Jul 20, 2005

Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?

View 7 Replies View Related

T-SQL (SS2K8) :: Adding Columns On The Fly

Apr 21, 2014

I have a report that looks like the following

NameCity Client NoAccount No Balance
SmithSydney 1234561258792 3.95
JonesMelbourne 2589641000657 9.54
BrownPerth 9876541000879 5.46
BrownPerth 9876541000880 7.51
WhiteSydney 6548521007562 10.65

HOWEVER I need it to look like the following:

NameCity Client NoAccount No BalanceAccount No Balance
SmithSydney 1234561258792 3.95
JonesMelbourne 2589641000657 9.54
BrownPerth 9876541000879 5.461000880 7.51
WhiteSydney 6548521007562 10.65

The requirement is that if the Client No is the same that the Account No and Balance appear on the same row but just additional columns. There is no restriction on how many extra columns there would be.

Once the query is working it needs to go into Visual Studio so that the report can be set up as a subscription.

View 1 Replies View Related

T-SQL (SS2K8) :: Adding Two Month - How To Make Year Change

Oct 15, 2014

This statement adds two additional months to which is fine :

DATENAME(MM,dd.date)+ ' ' + DATENAME(D,dd.date) + ', ' + DATENAME(YY,dd.date)

but if my month is November and two months is added, the year does not change, it stays the same. how do I make the year change when two months are added toward the end of the year.

View 7 Replies View Related

T-SQL (SS2K8) :: Adding Minutes (varchar) To Datetime Field

Mar 27, 2015

I have a datetime field that is just the date and zero for the time element. I also have a varchar field that is of the format 09:25:30

is there an easy way to add these together?

I don't mind if they get converted to integer as it will be used for comparison.

View 9 Replies View Related

T-SQL (SS2K8) :: Adding N Prefix In Update Statement In Order To Store The Text Correctly?

Sep 4, 2014

The FirstName and LastName values being passed in are in cyrillic text.

How do I add the N prefix in the update statement in order to store the text correctly.

I've tried FirstName = N@Firstname

or FirstName = '''N''' + @FirstName + '''

[dbo].[sp_UpdateDealerPeopleInfo](
@PersonId char(9),
@OriginalSID char(9),
@FirstName Varchar(50),
@LastName Varchar(50),

[code].....

View 8 Replies View Related

Positive Value

Nov 6, 2007


I have the following query

OutPut:

ChildName BillPayerName Cost
Mary Fred £50

Total Balance Blank

The problem is when I check for a value > 00.1 in the having clause the total balance column disappears.
But its ok if I use any other comparison operator.
I.e < 00.1.


Any ideas.


create procedure Rpt_pos1 --(@cmb1 as varchar(50)) with ENCRYPTION as

--declare @startdate smalldatetime

AS

set dateformat ymd

set datefirst 7


--loop through weeks

create table #getTable (thesite integer null,thechild integer null,cost money null)

insert #getTable select s.siteid,c.childid,0

from site s,child c

where --s.siteName=@cmb1

c.siteID=s.siteID


and c.active=1

and c.potential=0


insert #getTable

select s.siteid,c.childid,isnull(sum(round(bt.total,2,2)),0)*-1

from site s,child c,billingTotal bt

where --s.siteName=@cmb1

s.siteID=bt.siteid


and c.siteID=s.siteID

and bt.siteID=s.siteID

and bt.childid=c.childid

and c.active=1

and c.potential=0

group by s.siteID,c.childid


insert #getTable select s.siteid,c.childid,isnull(sum(round(p.amount,2,2)),0)

from site s,child c,payment p

where --s.siteName=@cmb1

p.siteID=s.siteid


and c.siteID=s.siteID

and p.childID=c.childID

and c.active=1

and c.potential=0

group by s.siteID,c.childid


create table #results (ChildName varchar(200) null,BillPayerName varchar(200) null, Cost varchar(20) null)

insert

#results

select c.forename + ' ' + c.surname

,case b.telhome when '' then b.Forename + ' ' + b.Surname

else b.Forename + ' ' + b.Surname + ' (' + b.telhome + ')' end

,case when sum(round(g.cost,2,2)) < 0 then cu.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15))

when sum(round(g.cost,2,2)) > 0 then cu.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15))

when sum(round(g.cost,2,2)) = 0 then cu.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15)) end

from

#gettable g

,child c

,billpayer b

,currency cu

,site s

where

cu.currencyid=s.currency


and s.siteID=c.siteID

and c.siteID=g.thesite

and c.childID=g.thechild

and b.billpayerID=c.billPayerID

group by

c.forename,

c.surname,

b.telhome,

b.surname,

b.forename,

c.siteID,

cu.symbol


having sum(round(g.cost,2,2)) > 00.1

order by

c.surname asc

insert #results

select '','',''

insert #results

select

'Total Balance'

,''

,c.Symbol + cast(sum(round(g.cost,2,2)) as varchar(15))

from

#gettable g

,site s

,currency c

where

g.thesite=s.siteid

and c.currencyID=s.currency



group by

c.symbol


having sum(round(g.cost,2,2)) > 00.1

drop table #gettable

select * from #results

drop table #results

go

View 4 Replies View Related

Check For Positive Value

Oct 31, 2007

I need to be able to check & display people who have a positive value in the bills.
i.e > 1p.
On the billingtotal table i need to sum > 1p.



CREATE PROCEDURE rpt_PositiveAccountBalances

(@cmb1 as varchar(100)) WITH ENCRYPTION

AS

BEGIN

SELECT DISTINCT




Site.siteName,

Child.surname + ', ' + Child.forename AS ChildsName,

billPayer.surname + ', ' + billPayer.forename AS BillPayerName,

BillingTotal.total

FROM

Site

INNER JOIN


Child

ON


Site.siteID = Child.siteID

INNER JOIN


BillingTotal

ON


Site.siteID = BillingTotal.siteID

CROSS JOIN


billPayer

WHERE


site.sitename=@cmb1

END

View 3 Replies View Related

Nagative To Positive, Format

Apr 2, 2008

one value should be always positive in report

I just replace "-" to " " using Replace(Fields!sumamount.Value,"-","") and format is currency like $ 1234.12

but after replace I get 1234.1200 in report



format of cell is currency like $ 1234.12 and if I don't use replace - it's ok



how to remove two last zero and add "$" ? or may be other ideas how to show alway positive value ?

View 6 Replies View Related

Count Number Of Positive Dollar Values In Query?

Feb 3, 2012

How do I count the number of positive dollar values in a query? I have various negative and positive dollar values and want to count how many positive I have then I will know how many are negative. I cannot put >0 or >0.00 in the where because it still returns everything cuz the programmer set the datatype to be money

View 4 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

Data Warehousing :: DateDiff Function To Return Positive Value Irrespective Of Values Passed

Aug 7, 2015

I have a requirement to use DateDiff(Months,DateTime1,DateTime2) and this must return positive integer values.

Currently negative numbers are being returned because DateTime1 < DateTime2  or DateTime1 > DateTime2 .

The DateTime1 and  DateTime2  values are dynamic and either of them can be bigger than the other.

Any query solution so that always positive value is returned.

View 3 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

Summing An Equation

Jun 6, 2008

This is a working 12 month intrest equation. I used this for the layout section but I am trying to take this and it gives me the correct values. But what I need to do next is have it sum those values.

I tried =SUM( whole expression but that didnt work) you can laugh at me I know but any help would be great!

=Switch(Fields!eqprecdt.Value< CDate("1 Jan 2007"),Fields!bookvalue.Value*datediff("d",Now(),#1/1/2007#)* .07/365,Fields!eqprecdt.Value> CDate("1 Jan 2007"), Fields!bookvalue.Value * datediff("d",Now(),Fields!eqprecdt.Value)* .07/365)*-1

View 5 Replies View Related

Summing Prices Day By Day

Oct 23, 2013

Let suppose that we have a table which look like this

BillDate Price
01.01.2013 2.00
01.01.2013 1.00
02.01.2013 3.00
02.01.2013 2.00
03.01.2013 1.00

I would like to sum a prices day by day and output to be like this

BillDate SumDaylyPrice
01.01.2013 3.00
02.01.2013 5.00
03.01.2013 1.00

To point I’ve reached myself is a query:

SELECT BillDate, (SELECT SUM( Price) FROM Table1 ) AS SumDaylyPrice
FROM Table1
WHERE BillDate BETWEEN
(SELECT Min(BillDate) FROM Table1)
AND
(SELECT Max(BillDate) FROM Table1)
GROUP BY BillDate

but this doesn’t work- summing everityng

I don’t know how to indicate in first row of query
SELECT BillDate, (SELECT SUM( Price) FROM Table1 WHERE DATE = ????) AS SumPrice
a WHERE clause for every day separately.

View 2 Replies View Related

Summing The Value Of A Text Box.

Feb 5, 2008



As my name shows I am about read to pull my hair out on this and will take any help that I can get.
I have a table with the following values
field1,field2,field3
a | p | 1
a | n | 1
a | p | 2
b | p | 2

b | p | 2
b | n | 3

I am grouping by first column
a
p 1
n 1
p 2
-------------------
a 3 1

b
p 2
p 2
n 3
------------------
b 4 3
What I want to do if it have a value of p I want the value in one column if it has a value of n I want it in another column.
The columns are not a problem, I use a iif statement iif( field2 = p, value, 0) iif ( field2 = n, value, 0)

the problem comes when I try to total the columns.
I was trying to use the =sum(field3) in my group total.
the above example is what I want to see the below example is what I get.
a
p 1
n 1
p 2
-------------------
a 4 4

b
p 2
p 2
n 3
------------------
b 7 7

I hope this makes some since to someone out there that can help me out.
I am getting kind of thin in the hair department so I cannot afford to loose any more.



View 3 Replies View Related

Grouping And Summing

Jul 20, 2006

I need help in summing a column by dates in the format of "YYMMDD". We have multiple orders of the same product each day. I am importing this table to Excel and creating a dashboard. My ultimate goal is to reduce the size of the imported table and still have daily totals of each product. We run thousands of line orders per class which really bogs down Excel. My table in MS Query is as follows (the actual table contains approximately 8,000 lines per month):

date prod class qty
060101 a101 1a 100
060101 a101 1a 100

I would like to have the following:

date prod class qty

060101 a101 1a 200

Any other suggestions would be greatful!!
Thanks in advance

View 4 Replies View Related

Problem Summing Each Column

Jun 4, 2007

 Hi,I need some help in summing each column in a gridview.id    name    sun    mon    tue    wed    total1      Tim       5        6        5      10      263      Sam      6        6        6      5        23The above is how the gridview looks like. In the database, I have all the fields except for total. So, I know I have to use the SUM function in SQl to get the Total. So, I am wondering how do I sum each column to get the total. I have something like this but it doesn't work:"SELECT ID, name, Sun, Mon, Tue, Wed, SUM(Sun + Mon + Tue + Wed) AS Total FROM testTable"Please helpahTan 

View 7 Replies View Related

Summing Different Combinations Of Selections

May 7, 2008

I am quite new in sql. I am writing a report which takes data of one same column and summing them according to the type as described in another column("TR_1"."TTYPE"). So far I have succeeded to get the sum of only one type at a time (by putting WHERE "TR_1"."TTYPE" = or not equal the desired type). For example: I want to create two columns, one showing the sum of the budget and the other the some of the actuals: here is my SQL instruction (the column "TR_1"."TTYPE" give the record type):
******************************************************************
SELECT SUM("TR_1"."AmountLCU")*-1 "Budget",rtrim("TR_1"."COSTCENTER") "Cost Centre",rtrim("TR_1"."ACCOUNT") "Account Num",rtrim("TR_1"."DONOR") "Donor Num", "TR_1"."AmountLCU"*-1 "Amount","TR_1"."TTYPE", rtrim("TR_1"."ACTIVITY") "Activity Code" FROM "scalaDB"."dbo"."A_GL0601_PREVIOUS" "TR_1"
WHERE NOT ("TR_1"."TTYPE"='' OR "TR_1"."TTYPE"='a' OR "TR_1"."TTYPE"='c') AND NOT ("TR_1"."COSTCENTER"=N'' OR "TR_1"."COSTCENTER"=N'0000') AND (("TR_1"."ACCOUNT">=N'26' AND "TR_1"."ACCOUNT"<N'7100') OR ("TR_1"."ACCOUNT">N'7100' AND "TR_1"."ACCOUNT"<=N'7999'))
GROUP BY "TR_1"."COSTCENTER","TR_1"."ACCOUNT","TR_1"."DONOR","TR_1"."ACTIVITY","TR_1"."AmountLCU","TR_1"."TTYPE"

**********************************************************************
Note: the report is written in Crystal reports and the database is SQL Server (not sure of the version)

Thanks in advance
I.Shaame

View 9 Replies View Related

Summing Rows Into A Column

Mar 16, 2006

Hello, This is my first post so please be kind. I have been attempting to convert a query I built in MS Access for use in MSSQL 2000, the syntax for these is different so I was frustrated to find out I could not use the access query.

I have 4 columns one containing a user Id and the others costs, I wish to total the costs per user ID at the end of each row.

So far I have managed to convert about half of my access query, this gives mev the clientID's and costs in columns but I cannot for the life of me get the costs in a total. It's annoying because my access query works perfectly.

This is my Access query:
SELECT DISTINCT Holiday_Bookings.ClientID, Holiday_Bookings.Booking_Cost, Room_Facilities.FacilityCost, Rooms.CostPerNight,
Rooms!CostPerNight*Nights_Stayed+Holiday_Bookings!Booking_Cost+Room_Facilities!FacilityCost AS TotalCost,
[TotalCost]*17.5/100+[TotalCost] AS [Total+VAT]
FROM Room_Facilities INNER JOIN (Hotels INNER JOIN (Holiday_Bookings RIGHT JOIN Rooms ON Holiday_Bookings.ClientID = Rooms.ClientID) ON Hotels.HotelID = Rooms.HotelID) ON Room_Facilities.FacilityID = Rooms.FacilityID;

And this is what I have been able to salvage into MSSQL format:

SELECT
Holiday_Bookings.ClientID,
Holiday_Bookings.Booking_Cost,
Rooms.CostPerNight,
Room_Facilities.FacilityCost
FROM
Rooms
INNER JOIN Room_Facilities ON (Rooms.FacilityID = Room_Facilities.FacilityID)
INNER JOIN Holiday_Bookings ON (Rooms.Clients_ID = Holiday_Bookings.ClientID)

How can I total the three columns and add the tax?

View 3 Replies View Related

Difficult Summing Query

Nov 23, 2005

Hello,Here is a brief summary:Table 1 = All Accounts- with fields such as Customer ID and Account #Table 2 = Deposit Balance Table- with fields such as Account #, BalanceTable 3 = Loan Balance Table- with fields such as Account #, BalanceAll accounts are either deposit accounts or loan accounts. What I needto do is to gather information about total balances in both depositsand loans for each customer. I haven't been able to hit the right queryfor doing this. I can easily get information about one or the other,such as the following:SELECT All_Accounts.Customer_ID, COUNT (DISTINCT(Deposit_Balance_Table.Account_Number)), Sum(Deposit_Balance_Table.Balance)FROM Product_Table, Deposit_BalanceWHERE (Product_Table.Account_Number=Deposit_Balance.Acco unt_Number)GROUP BY Product_Table.Customer_ID ORDER BY 1Which will give me one row for each user, and show me the total numberof deposit accounts each customer has and a sum of the balances in eachof those accounts. I can make a similar query involving Loan Accounts.As soon as I try to draw both, however, I wind up below my depth.Something to do with the handedness of my joins, I believe. Often Iwill get one column of information (either deposits or loans), or thequery will fail because the join I'm attempting is invalid, etc. I needto take every row in the All_Accounts table, match each one to itsbalance in either the Deposit or Loan table, and then group them all bythe Customer ID and sum them, so that I can find out the totalrelationship balance per customer. Any help would be appreciated.

View 5 Replies View Related

Summing From Multiple Tables

Jul 20, 2005

We recently added a new database at the company. It has only onepurpose - to hold massive amounts a daily data generated by telephonecalls on a network.The amount of data was so large (several gigabytes a day) that the guywho set up the database creates a new table for it each day.His thinking was that if we only need to query one day's worth of datathen it would be a lot faster to query a table with one day's datathan having to query many days of data in one table.I see his reasoning. Any comments or alternatives to this schemewould be appreciated.Here's the question though...I'm writing a front end for this and waswondering if the most efficient way to query and sum data acrossmultiple tables (days) is in the form of the following statement.Suppose three days of worth of data are wanted:select sum(ET) from (select sum(vc_elapsed_time) AS ET fromswitch2030608 where init_cell_info_cell = 196 union all selectsum(vc_elapsed_time) as ET from switch2030609 whereinit_cell_info_cell = 196 union all select sum(vc_elapsed_time) as ETfrom switch2030610 where init_cell_info_cell = 196 ) tIn my front end, based on user input, I plan to keep extending thisstatement with more union alls. Is this the best way to implement thegoal of this query?-David

View 1 Replies View Related







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