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
ADVERTISEMENT
Jun 7, 2006
Hi all,
until recently ive been using a rank equation to calculate rank,
essentially doing a select statement and selecting this as the rank field, where query 2 is the same as query 1:
((select count(*) from (query1) where (query1).value < (query2).value) +1)) as Rank
problem is that this is now running like a dog (takes 10 secs) and i'd like to try and do this another way- 2005 has a rank function, how can i do this in 2000?
here is the full statement :
SELECT StudentId, GCSE_Score, LTRIM(STR
((SELECT COUNT(*)
FROM dbo.[Score2004-05]
WHERE GCSE_score < s.GCSE_Score) + 1)) AS GCSE_Rank, SetId
FROM dbo.[Score2004-05] S
WHERE (SetId = '2004/2005')
greg
View 4 Replies
View Related
Sep 25, 2007
I am not sure if I am posting this is the right place, please let me know if a better place exists.I am trying to figure out the distance from a point on the earth to a route between two other points. All points that I have are in latitude and longitude secondsIE: 51-52-40.6700N => 186760.6700NI am able to find the distance between two points on the globe using a custom function FindDist(latitude1, longitude1, latitude2, longitude2)I did the legwork for the math and was able to come up with this psudo code: p1 = start of routep2 = end of routep3 = point of referencep4 = INTERSECT(LINE(p1,p2), PERPENDICULAR_LINE(p1,p2,p3)) The direct distance between p4 and p3 is somewhere in the range of 0 - 70 and encompasses all points in the US (We only deal in the US) which is incorrect. If I do the conversion of p4 to latitude and longitude I get numbers that start out at 1000 which is also incorrect.I am doing the conversion with the following: DECLARE @lat2 VARCHAR(25)SET @lat2 = (@p4x * 3600)IF @p4x < 0BEGINSET @lat2 = @lat2 + 'S'
ENDIF @p4x > 0BEGINSET @lat2 = @lat2 + 'N'
ENDDECLARE @lng2 VARCHAR(25)SET @lng2 = @p4y * 3600IF @p4y < 0BEGINSET @lng2 = @lng2 + 'W'
ENDIF @p4y > 0BEGINSET @lng2 = @lng2 + 'E'
END I am not sure, however, how to either:a) find the latitude / longitude of p4b) find the distance in miles between p3 and p4Either of these will solve my problemI know this is a rather odd question, but if anyone can help me that would be great. If you need more information, let me know and I will try and explain things further.
View 1 Replies
View Related
Feb 19, 2008
Hello!
In AS 2008 it is possible to build time series model using ARIMA algorithm. But how exactly the ARIMA model eqation can be interpreted? For example, I have the following ARIMA equation in the mining legend of the (only) node:
ARIMA equation:
ARIMA ({1,1.06331498820371E-02,-9.1320555134096E-04,6.82290767622866E-03,5.16804012070969E-02,5.33387901298971E-02,7.27630334027759E-04,-4.08433722450516E-02},1,{1,0.637381531245194}) X ({1,8.85242031097984E-02},0,{1,3.46939494788227E-02})(26) X ({1},1,{1,-0.94291566648386})(13) Intercept:706.18948950439
I am used to the notations like ARIMA(Autoregressive order, Order of differencing, Moving average order). So, I can guess, that term like {1,1.06331498820371E-02,-9.1320555134096E-04,6.82290767622866E-03,5.16804012070969E-02,5.33387901298971E-02,7.27630334027759E-04,-4.08433722450516E-02} expresses coefficients in either autoregressive or moving average terms. But how can I interpret patterns followed by X like ARIMA(...)X(...)X(...)? Do they express periodical patterns? If so, how can I pick periods from this equation?
Thank you very much in advance.
View 3 Replies
View Related
Dec 28, 2007
How to get the CASE results highlighted in BOLD into this equation; "(LogOut - LogIn) + (LunchBreak) -(AMBreak) + (PMBreak) AS TimeWorked" ?
Thank you.
CREATE VIEW dbo.vwu_ReportViewASSELECT EmployeeID , LastName , FirstName , LocationCode , UserID , Today , Login , AMBreakOut , AMBreakIn , CASE WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 19 THEN '0' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 34 THEN '15' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 49 THEN '30' WHEN ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) > = 50 AND ISNULL(DATEDIFF(Minute, AMBreakOut, AMBreakIn),0) <= 64 THEN '45' ELSE '60' END AS AMBreak , LunchOut , LunchIn , CASE WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 66 THEN '0' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 67 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 81 THEN '15' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 82 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 96 THEN '30' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 97 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 111 THEN '45' WHEN ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) >= 112 AND ISNULL(DATEDIFF(Minute, LunchOut, LunchIn),0) <= 126 THEN '60' ELSE '75' END AS LunchBreak, PMBreakOut , PMBreakIn , CASE WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 0 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 19 THEN '0' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 20 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 34 THEN '15' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 35 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 49 THEN '30' WHEN ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) >= 50 AND ISNULL(DATEDIFF(Minute, PMBreakOut, PMBreakIn),0) <= 64 THEN '45' ELSE '60' END AS PMBreak , Logout , Comments , LoginLogon , AMBreakOutLogon , AMBreakInLogon , LunchOutLogon , LunchInLogon , PMBreakOutLogon , PMBreakInLogon , LogoutLogon ,(LogOut - LogIn) + (LunchBreak) -(AMBreak) + (PMBreak) AS TimeWorked
View 7 Replies
View Related
Sep 6, 2015
I get an ARIMA equation in a form like below:
ARIMA ({1,-6.88479117532726E-02,-0.41430691637436},0,{1,4.42986549117398E-03}) X ({1,-0.473450289323228},0,{1,0.013772719862356,-2.14724317829266E-02,-4.80711350511344E-02,-1.15173259713497E-02,-9.86142925337024E-03,1.06803677786174})(6)
Intercept:18561.1721123981
I do understand the coefficients part and the seasonal part as well, however, I have trouble deciphering what exactly is the intercept here. Initially, I thought it would be equivalent to the value of mu in the original Box-Jenkins equation (so indicate a level), then I thought it's an intercept for the AR terms. It seems that none of these interpretations is good. The only official explanation I found at msdn was:
"Normally, the intercept (VALUETYPE = 11) or residual in a regression equation tells you the value of the predictable attribute, at the point where the input attribute, is 0. In many cases, this might not happen, and could lead to counterintuitive results.
For example, in a model that predicts income based on age, it is useless to learn the income at age 0. In real-life, it is typically more useful to know about the behavior of the line with respect to average values. Therefore, SQL Server Analysis Services modifies the intercept to express each regressor in a relationship with the mean.
This adjustment is difficult to see in the mining model content, but is apparent if you view the completed equation in the Mining Legend of the Microsoft Tree Viewer. The regression formula is shifted away from the 0 point to the point that represents the mean. This presents a view that is more intuitive given the current data.Therefore, assuming that the mean age is around 45, the intercept (VALUETYPE = 11) for the regression formula tells you the mean income.
View 2 Replies
View Related
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
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
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
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
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
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
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
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
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
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
May 12, 2008
I have a report with several columns which include Status(0,1,2) and Time in hours. I want to be able to total up the time for each status. I Sum up the all of the fields fine. I'm new to reporting services so any help would be appreciated. Thanks
View 3 Replies
View Related
Nov 9, 2007
In below table, the light blue rows, represents the subgroup I am trying to sum up.
The value in the "Inventory Value" contains the latest inventory value for said inventory.
Light green row is grouped by Store name, Light blue is grouped by inventory name. the expression in Inventory row for the inventory value column is =First(Fields!InventoryValue.Value).
Doing a store summation, sums up all detailed values. For example in Store A, instead of 70K, the sum would be 130K... double summing the toy soldiers and cute dolls inventory. Is there a way to do the "correct" summation or a different approach to this? Thanks!
STORE
TOTAL STORE INVENTORY VALUE
INVENTORY
% OF STORE INVENTORY
INVENTORY VALUE
WARNING DATE
STORE A
1,000,000.00
TOY SOLDIERS
2.00%
20,000.00
20,000.00
11/5/2007
20,000.00
11/2/2007
PLUSH TOYS
1.00%
10,000.00
10,000.00
11/5/2007
CUTE DOLLS
4.00%
40,000.00
40,000.00
11/2/2007
40,000.00
10/31/2007
1,000,000.00
7.00%
70,000.00
STORE B
10,000,000.00
TOY SOLDIERS
0.20%
20,000.00
20,000.00
11/5/2007
20,000.00
11/2/2007
PLUSH TOYS
0.10%
10,000.00
10,000.00
11/5/2007
CUTE DOLLS
0.40%
40,000.00
40,000.00
11/2/2007
40,000.00
10/31/2007
10,000,000.00
0.70%
70,000.00
View 4 Replies
View Related
Apr 14, 2008
Hi, I'm converting an existing report to an RDLC report using VS2008 C#. I'm a relative newbie to creating RDLC files, and I'm having a problem with one part of the report. I have three Table controls on the report. Two show detail breakouts from different data sources and the third Table is supposed to show some grand total information by summing the totals of the first two tables. Since it appears that a table can only be bound to one datasource, I can't seem to figure out how to get a sum by peaking into the totals of the first two tables. How can I do this?
Thanks,
Ian
View 4 Replies
View Related
Apr 22, 2008
Hi There,
I have the following query:
SELECT NATNLACCT, IDCUST, TEXTSNAM, AMTBALDUEH
FROM
VIEW_ARCUS
where amtbaldueh != .000
order by NATNLACCT
but i want to display the data something similar as below. How do I create a column to display with the natinlacct name(I have many) concatenated to Total and then sum amtbaldueh?
Natinlacct idcust textsnam amtbaldueh
Doda 1234 abcd $101
Doda 5678 efgh $200
Doda 9876 ijkl $300
Doda Total $601
Nava 5847 jhgf $230
Nava 5487 lfde $130
Nava 3587 lrsd $100
Nava Total $460
Thanks
Rhonda
View 5 Replies
View Related
Jun 16, 2008
I have a question on how to sum data by a certain date range. Here is the data I'm looking at. I have volume measured usually (but not always) every day. I want to sum the volume from the 2nd of the month to the first of the next month. I want to do this for every month. I have the columns of my data listed below. Can anyone help me with this? I've been trying to read up on it, but I'm not finding anything.
Entity Date Measured Volume
1 4/01/2008 5
1 4/02/2008 4
1 4/03/2008 6
1 4/04/2008 5
1 4/08/2008 7
1 4/12/2008 8
1 4/13/2008 5
1 4/14/2008 7
1 4/25/2008 8
1 4/30/2008 9
1 5/01/2008 6
1 5/02/2008 8
Thanks in advance for any help!
View 4 Replies
View Related
Jul 9, 2014
I am working with a MS SQL database associated with SCCM 2007. SCCM collects software product usage data, and I am tasked with generating a report that will return results between two user-chosen date ranges. I set up prompts for month, year, endmonth, endyear. They would enter 2, 2014, 4, 2014 for example. Timekey would equal 201402, endtimekey = 201404. The tables I am concerned about look like this:
v_MonthlyUsageSummary
TimeKey ResourceID FileID UserID UsageCount TSUsageCount UsageTime LastUsage
201402108343 253362240038122 0 2550272014-02-28 13:29:39.000
201402108340 253362240665 2 0 122014-02-27 15:58:13.000
201402108932 253362231708 0 10 216702014-02-14 20:41:35.000
201403124035 504006542145 15 0 1756292014-03-31 19:37:37.000
201403124036 504006537579 46 0 7737112014-04-01 00:00:00.000
v_SummarizationInterval
TimeKey IntervalStart
2013102013-10-01 00:00:00.000
2013112013-11-01 00:00:00.000
2013122013-12-01 00:00:00.000
2014012014-01-01 00:00:00.000
2014022014-02-01 00:00:00.000
2014032014-03-01 00:00:00.000
2014042014-04-01 00:00:00.000
2014052014-05-01 00:00:00.000
2014062014-06-01 00:00:00.000
2014072014-07-01 00:00:00.000
The problem is I only know how to grab one timekey, but I need to combine multiple timekeys to sum up the usage counts for each workstation.
Here is the full query.
declare @TimeKey int
declare @months float
declare @endTimeKey int
set @TimeKey=100*@Year+@Month
set @endTimeKey=100*@endYear+@EndMonth
select @months=DATEDIFF(d,@timekey,@endTimeKey)
[Code] .....
View 20 Replies
View Related
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
Jul 28, 2006
Hi everyone, I have some trouble writing a SQL stored procedure thatcan do the following:We have data in one table in numeric form, but we want to sum the datain this table based on the values of two different alpha fields. Toillustrate, let me write the following example:Table with these records:A B 1.1 2.2 Blah1 Blah1A B 2.3 5.6 Blah2 Blah2B C 7.8 9.1 Blah3 Blah3B C 4.5 1.0 Blah4 Blah4R F 1.1 4.3 Blah5 Blah5B A 3.1 2.7 Blah6 Blah6I need to write a query that will return the following result set fromthe above table:A B 3.4 7.8 Blah1 Blah1B C 12.3 10.1 Blah3 Blah3R F 1.1 4.3 Blah5 Blah5B A 3.1 2.7 Blah6 Blah6If the alphanumberic keys are the same, the sum the numeric columns upwhilst displaying one one of the records, the blah fields don't matterif one only one is displayed. Can anyone recommend the best way to dothis? I'm running MS SQL 2005.Thanks,Herman
View 1 Replies
View Related
Jun 23, 2015
I have a table with duration values for different machine states. I 'm trying have a sum of the duration value of each state ( the duration sum , was an earlier question).
declare
@tblDurations
TABLE
(StateName
nvarchar(30),Duration
nvarchar(30))
[code]...
I want the output to have sum of the duration ( from my function ), grouped by the state.So output should be :
Breaks 00:02:03:40
Meetings 00:00:01:50
Running 15:21:07:16
I think this can be done with windows functions, but how I don't know.
View 10 Replies
View Related
Jun 29, 2007
What I need to do in seperate a group of numbers into two different categories based on a phase code. I have acheived this through two conditional statements, but when I try to total the numbers that were returned for each group I receive an #error.
This is an example of the switch statement I used in order to return the correct values for the Implemenataion.
=Switch(Fields!Phase_Code.Value="PILOT", Fields!LedgerQuantity.Value, Fields!Phase_Code.Value="DATAMIGRAT", Fields!LedgerQuantity.Value/2, 1=1, "")
I've tried several different methods for aggregating the numbers that are returned.
=SUM(Switch(Fields!Phase_Code.Value="PILOT", Fields!LedgerQuantity.Value, Fields!Phase_Code.Value="DATAMIGRAT", Fields!LedgerQuantity.Value/2, 1=1, ""))
I've tried substituting a 0 in for the "" at the end of each statement. I've also tried to take the first statement and put it into its own table field named ImplementationLedger, and them summing it. ie. =SUM(Fields!ImplementationLedger.Value)
Please Help!
View 1 Replies
View Related
Nov 6, 2006
Hello again
How do I sum up and group numeric values into one output column in SSIS?
I have data that looks like this:
Refno date credit amount
11675 1987-04-23 7.03 13501.27
and I want to add the credit and amount columns together and output them as one column, In T-SQL, i'd do this:
SELECT Refno, SUM(Credit + Amount)
FROM Transfer
GROUP BY Refno, Date
I could add this SQL into my SSIS package easily enough but I wan't to learn how to do it the SSIS way
View 4 Replies
View Related
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
View Related
Sep 12, 2013
field prodDate puts out values as such:
8/12/2013 7:50:15
8/12/2013 7:51:03
8/12/2013 7:53:42
8/12/2013 7:54:12
8/12/2013 7:56:02
...
...
8/12/2013 14:57:57
8/12/2013 14:59:59
I'd like to get a time span sum beginning with the first prodDate entry and ending with the last.
The idea is I'll then use that to divide lbs produced, thus giving me a gauge for lb per hour.
View 3 Replies
View Related
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
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
View Related
Sep 13, 2006
hello, i'm using sql200 and i am attempting to create a table that has an hourly-incrementing 'Date_Time' column, with a corresponding 'Total' column (which keeps a running total of values off of another table) . The code I am using right now is...
declare @date as smalldatetime
set @date = dateadd(yy, -1, cast(convert(char(11), current_timestamp, 101) + '00:00:00' as smalldatetime))
select dateadd(hh, i, @date) as Date_Time, sum(Subtotal) as Total
into #POGtable
from Pivot, OrderGroup
where
i between 0 and 24 and
CreationDate between @date and dateadd(hh, i, @date)
group by i
select dateadd(hh, i, @date) as Date_Time, 0 as Total
into #Ptable
from Pivot
where i between 0 and 24
group by i
select *
from #POGtable
union
select * from #Ptable p
where not exists(
select * from #POGtable pog
where p.Date_Time >= pog.Date_Time)
the solution is ugly, but the problem i'm having is that values for 'SubTotal' don't usually appear before 8 or 9 am. what you see above is me getting all the times (hours) that a subtotal present, creating another table with every possible hour in it (and with a 'Total' column as just zero), and then combining the two tables to create one flowing table over a 24-hour period.
there has GOT to be a better way to do this; the main point being that i want the sum( ) function to start adding up values immediately so i don't have to union two tables
View 3 Replies
View Related
Jun 17, 2015
I am using sql server and I have a table called accnt with the fields ven1 and amnt1 and a table called acc1167 with fields ven, job#, and amnt. for this example these tables look like this
    accnt              acc1167
  ven1   amnt1        ven   job#  amnt
  1167   100         1167   1    200   Â
  1152   50          1167   2    300
  1167   110         1167   3    100
  1167   300         1167   4    200
  1252   1050        1167   5    200
  1167   210         1167   6    150
  1167   1150Â
  1167   130Â
  2113   800Â
  1167   550
  1167   1200
I need to sum amnt1 for all the records in accnt with the ven1 of 1167, we will call this sumA. Then sum amnt in acc1167 for all records, we will call this sumB. next I need to divide sumB by sumA to get a ratio. finally I need to multiply each amnt value from acc1167 by the ratio and get a number that will then replace the acc1167 amnt value.
for example, sumA = 3750, sumB = 1150. taking these values, sumB/sumA = 0.307. I then replace every value in acc1167 amnt with 0.307*itself, so the final table should look like this:
     acc1167
  ven  job#   amnt
  1167  1    61.4
  1167  2    92.1
  1167  3    30.7
  1167  4    61.4
  1167  5    61.4
  1167  6    46.05
i have tried to use the sum function and and some insert, but i am very new to SQL and have never used sum before and don't know how to call from multiple tables, or how to store a ratio. Ive tried this:
  UPDATE   acc1167
  sum1 = sum amnt1 where ven1 = '1167'
  from accnt
  sum2 = sum amnt
  from accnt
  SET     amnt = sum2/sum1*amnt
  FROM    acc1167
View 2 Replies
View Related
