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?
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
Hello Everyone I've created a report with a simple dataset that is similar to this
City , RequestID, Request Amount, ClaimID, ClaimAmount El Monte 791 52,982.00 2157 41,143.75 El Monte 3691 11,838.00 3140 8,231.14 El Monte 3691 11,838.00 3141 990.00 El Monte 3691 11,838.00 3142 2,615.00
So I group by City, RequestID. On the first group I specified the expression to be City and in the header I list the city and in the footer I list the sum of Request amount. On the second group I specified the group by Request so in the header I placed requestID and on the footer I placed Request Amount. I set request information to hide the duplicates and I even add =Sum(Fields!RequestApprovedGrandTotal.Value,"GroupByRequestID") the scope of the group. But this is what I get:
For requestID = 3691 for Request Amount is 35,514.00 not 11,838.00. All the claim sums are correct and they are located on the detail row.
I've read that a work around is to create multiple dataset but I honestly believe that something as simple as this should work on the reporting server 2000. So I've come to the conclusion that I must be doing something wrong. Can someone give me a hand on this. Thanks.
I have a situation where I want to get running totals of a potentionally very large table. Say I have a million records with a table with few fields. Table structure like this UID value 1 3 2 9 3 4 4 7 5 2
I want to return a result set of something like this select uid, value, (rtotal???) from table
This is to be used for sort of a lotery. Say I have 1 million users with a variety of points tward the lotery. I total the points, is say 5 million, determined the winner to be say 3,234,563 Now I have to determine which uid is the winner. So I planned to to do a running total till the winning value is less then or equal to the running total and I have my winner. I am looking for a fast way to do this on a large database.
I have a problem that I know that i should be able to code up but have drawn a blank due to it being monday. Anyway......
Have a table :
create table test_registrations ( date_maint smalldatetime, user_name1 varchar (255), user_address1 varchar (255), total_users int ) go
If i have a number of registrations on a particular date then I can tell all how many users have registered in any date by :
select date_maint , count (1) from test_registrations group by date_maint order by date_maint desc go
The qestion is how can I keep a total registared users count. Say if I have 10 users join on the 1st of Jan and 15 on the 3rd then I want the total users for the users on 1st to read 10 and total users on the 3rd to read 25.
I know i should be able to code this up but I'm being a dumb ass. Can someone show me a way to code it. Is it some sort of correlated sub query to keep a running total ?
On SQL Server 2005 at least, this works efficiently where we have an indexed row number.
It does seem to be very sensitive to the join condition in the recursive part of the CTE: changing it to the equivalent "ON T.rn - 1 = RT.rn" results in a scan of T each time instead of a seek!
DROP TABLE dbo.T
-- rn must have contiguous values for this to work: CREATE TABLE dbo.T (rn int PRIMARY KEY, f float NOT NULL)
-- 100000 random floats between 0 and 1: INSERT INTO dbo.T SELECT n+1 AS rn, RAND(CAST(NEWID() AS binary(4))) AS f FROM dbo.Numbers GO
;WITH RT AS ( SELECT rn, f AS rt FROM dbo.T WHERE rn = 1
UNION ALL
SELECT T.rn, RT.rt + T.f FROM RT INNER JOIN dbo.T AS T ON T.rn = RT.rn + 1 ) SELECT * INTO dbo.TRT FROM RT --ORDER BY rn OPTION (MAXRECURSION 0)
Now my code below brings everything i want it too, the problems comes is I need to get a running total of sales for each day. Currently it shows the sales for a store for each day and what there projections should be. I need a running total for each day so if you were to have todays date it would have the sum(sales) between today and the first or the month that im in. but still show what the total was on the 10th, 9th, and so on.
Declare @Brand as varchar(10) DECLARE @StartDate datetime Declare @EndDAte as Datetime
Set @Brand = 'business' SELECT @StartDate=CAST('1/1/'+CAST(YEAR(GETDATE()) AS varchar(4)) AS datetime) SET @EndDate =CAST('12/31/'+CAST(YEAR(GETDATE()) AS varchar(4)) AS datetime)
Select ttProjection.StoreID,S.StoreName , ttProjection.DailyProjection, ttProjection.DAYS, ISNULL(ttSales.Sales,0) as Sales From
/**********Finds projection per day****************/ (Select StoreID, Projection, Projection/Cast(DaysInMonth as INT) as DailyProjection, DAYS From (Select StoreID, Projection as Projection, Month, Day(DateAdd(m, 1,DateAdd(d,1 - Day(Month), Month))-1) As DaysInMonth From Reporting.dbo.RetailSalesComparison_ProjectionsView Where StoreID between 12000 and 12999 )ttTemp
Right Join
(SELECT DATEADD(dd,number,@StartDate) as DAYS FROM ( select number from master..spt_values WHERE type='p' union all select number+256 from master..spt_values WHERE type='p' ) as s where DATEADD(dd,number,@StartDate)<=@EndDate)ttDays on Month(ttTemp.Month) = Month(ttDays.DAYS))ttProjection
Left Join
(Select Date, StoreID, Sum(Sales) as Sales From Reporting.dbo.RetailSales_byStore_byDay Group By Date, StoreID)ttSales on ttProjection.StoreID = ttSales.StoreID and ttProjection.DAYS = ttSales.Date Inner Join DelSolNet2.dbo.Store S on ttProjection.StoreID = S.StoreID Where Month(Days) = Month(getdate()) Order By Days, ttProjection.StoreID
We are trying to create a report that shows a Week to Date, Month to Date, and Year to Date
Week to Date Month to Date Year to Date
Item Number
I've tried using an if statement (if date = current week, Qty, 0) and then sum the data but I get an error message that reportitems can only be summed in the page header and footer. I've also tried running totals, but it doesn't like the iif statement. Any ideas on how to do this?
I have a table for which I need a “special� running total. More specifically there should be a close communication between the credit and the debit column. The scenario should be in the following order “Credit�“Debit�“Balance credit�“Balance debit� 6 0 6 0 5 0 11 0 0 4 7 0 0 9 0 2 3 0 1 0 0 5 0 4 0 2 0 6
I have to point out that this is done in a grouped form, where the CustormerID is the grouped clause. Now I have achieved this to a point where the calculations work only if the credit column is bigger than 0, but when the account starts with debit I get only sums of the column not the needed subtractions. What am I missing? Thanks in advance. DECLARE @PrevRunBal MONEY --Overall running total SET @PrevRunBal = 0 DECLARE @PrevGrpBal MONEY --Running total resets when account changes SET @PrevGrpBal = 0 DECLARE @PrevRunCnt INT --Overall running count (ordinal rank) SET @PrevRunCnt = 0 DECLARE @PrevGrpCnt INT --Running count resets when account changes SET @PrevGrpCnt = 0 DECLARE @PrevAcctID INT --The "anchor" and "account change detector" SET @PrevAcctID = 0 changes SET @PrevGrpBalP = 0 update Temp
SET --===== Running Total @PrevRunBal = RunBal = @PrevRunBal + dolguva, -- @iznos =RunBal= dolguva - pobaruva + @sdol -@spob, --===== Grouped Running Total (Reset when account changes) @PrevGrpBal = Bcredit = CASE
WHEN CustomerID = @PrevAcctID THEN CASE WHEN CREDIt > DEBIT or CREDIT = 0 THEN (@PrevGrpBal+CREDIT)-(DEBIT) WHEN DEBIT>CREDIT or DEBIT =0 THEN (@PrevGrpBal+DEBIT)-DEBIt END ELSE CASE WHEN CREDIT >DEBIT THEN (CREDIT) WHEN DEBIT > CREDIT THEN DEBIT -- restarts from 0 if only 1 rec. END
END,
--===== Running Count (Ordinal Rank) @PrevRunCnt = RunCnt = @PrevRunCnt + 1, --===== Grouped Running Total (Ordinal Rank, Reset when account changes) @PrevGrpCnt = GrpCnt = CASE WHEN CustomerID = @PrevAcctID THEN @PrevGrpCnt + 1 ELSE 1 -- Restarts count at "1" END,
--===== "Anchor" and provides for "account change detection" @PrevAcctID = CustomerID Apologies if I'm posting in a wrong forum
I have a table for which I need a €œspecial€? running total. More specifically there should be a close communication between the credit and the debit column. The scenario should be in the following order
Credit
Debit
Balance credit
Balance debit
6
0
6
0
5
0
11
0
0
4
7
0
0
9
0
2
3
0
1
0
0
5
0
4
0
2
0
6 I have to point out that this is done in a grouped way where the CustormerID is the grouped clause. Now I have achieved this to a point where the calculations work only if the credit column is bigger than 0, but when the account starts with debit I get only sums of the column not the needed subtractions. What am I missing? Thanks in advance. DECLARE @PrevRunBal MONEY --Overall running total SET @PrevRunBal = 0 DECLARE @PrevGrpBal MONEY --Running total resets when account changes SET @PrevGrpBal = 0 DECLARE @PrevRunCnt INT --Overall running count (ordinal rank) SET @PrevRunCnt = 0 DECLARE @PrevGrpCnt INT --Running count resets when account changes SET @PrevGrpCnt = 0 DECLARE @PrevAcctID INT --The "anchor" and "account change detector" SET @PrevAcctID = 0 changes SET @PrevGrpBalP = 0 update Temp
SET --===== Running Total @PrevRunBal = RunBal = @PrevRunBal + Credit,
--===== Grouped Running Total (Reset when account changes) @PrevGrpBal = Bcredit = CASE
WHEN CustomerID = @PrevAcctID THEN CASE WHEN CREDIt > DEBIT or CREDIT = 0 THEN (@PrevGrpBal+CREDIT)-(DEBIT) WHEN DEBIT>CREDIT or DEBIT =0 THEN (@PrevGrpBal+DEBIT)-DEBIt END ELSE CASE WHEN CREDIT >DEBIT THEN (CREDIT) WHEN DEBIT > CREDIT THEN DEBIT -- restarts from 0 if only 1 rec. END
END,
--===== Running Count (Ordinal Rank) @PrevRunCnt = RunCnt = @PrevRunCnt + 1, --===== Grouped Running Total (Ordinal Rank, Reset when account changes) @PrevGrpCnt = GrpCnt = CASE WHEN CustomerID = @PrevAcctID THEN @PrevGrpCnt + 1 ELSE 1 -- Restarts count at "1" END,
--===== "Anchor" and provides for "account change detection" @PrevAcctID = CustomerID
I want to produce a query that shows on a month/year bases the number of users joined for each month AND show a running total of membership, I'm almost there....just got a problem getting the running totals:
this is what I have so far:
SELECT DATEPART(MONTH, DateJoined) AS Month, DATEPART(YEAR, DateJoined) AS Year, COUNT(UserID) AS NuAccounts FROM Accounts_Users GROUP BY DATEPART(MONTH, DateJoined), DATEPART(YEAR, DateJoined) ORDER BY DATEPART(YEAR, DateJoined), DATEPART(MONTH, DateJoined)
How do I produce a column that shows the running total of accounts?
I have a ms-sql 2012 for this task. I read that running totals / sum over is better supported here.
There are customers with an account where they can insert or withdraw cash. When they insert, the inserted amount will get a date where it expires. After the expiration date, the cash becomes unavailable.
I'm not sure how to design the tables to meet my needs, but this is my best guess. And below is the first problem in the view.
Table: deposit    This table will hold all deposits
customerId    (string) balanceType    (string) transactionDate   (date) expiresOnDate   (date) amount     (decimal) transactionText   (string)
The data set for the deposit table could be:
1 Bonus 01-05-2015 30-04-2016  500  Inserted 500 2 Bonus 01-05-2015 30-04-2016  500  Inserted 500 3 Bonus2 01-01-2015 31-12-2015  100  Inserted 100 2 Bonus2 01-01-2015 31-12-2015  100  Inserted 100
Table: withdrawal  This table will hold all withdrawals
customerId    (string) balanceType    (string) transactionDate   (date) amount     (decimal) transactionText   (string)
The data set for the withdrawal table could be:
2Â Bonus2Â 01-04-2015Â -100Â Â Needed 100 2Â Bonus2Â 02-04-2015Â -100Â Â Needed 100 2Â Bonus2Â 03-01-2015Â -100Â Â Needed 100 3Â Bonus2Â 10-04-2015Â -50Â Â Â Took out 50 3Â Bonus2Â 11-04-2015Â -100Â Â Took out 100
[Code] .....
Now I need to combine the two tables in a view with a running total, ordered by customerId, balanceType and transactionDate
customerId balanceType transactionDate amount Total Text
The view must show this, when selecting all records:
1 Bonus 01-05-2015 500  500  Inserted 500 1 Bonus 01-07-2015 -100 400  Yes, got 100 1 Bonus 02-07-2015 -100 300  Yes, got 100 1 Bonus 03-07-2015 -100 200  Yes, got 100
[Code] ....
And a last view that will show distinct totals for each customer and balanceType
A while back, a "quirky update" method was proposed for lightning fast running totals based on the three-part MSSQL UPDATE's SET statement and tally tables. However, some claimed this was not 100% absolutely guaranteed behavior.
How does the new OVER clause compare in terms of performance ?
DECLARE @Tbl TABLE ( pk int not null primary key identity, N int ) INSERT INTO @Tbl (N) SELECT TOP 1000 1 FROM syscolumns a CROSS JOIN syscolumns b SELECT pk, SUM(pk) OVER (ORDER BY pk ) FROM @Tbl
get the desired results for the following sample data set. I was able to come up with a query that returns the the expected results however only for a given day, so I'd need to union several select statements the get the desired results which is definitely not ideal. I'd like to pass a parameter in (number of days) instead of doing a unions for each select.
DECLARE @T TABLE (Id INT, Category VARCHAR(1), [Date] DATE) INSERT INTO @T SELECT 1 AS Id, 'A' AS Category, '2015-5-13' AS ActivationDate UNION ALL SELECT 1, 'A', NULL UNION ALL SELECT 1, 'A', '2015-5-13' UNION ALL SELECT 1, 'A', NULL UNION ALL
INSERT INTO #t1 (CODE,COLUMN1,COLUMN2) VALUES ('432', 0,100), ('TOTAL FOR 432',0,100), ('4320001',0,250), ('4320001',50,0), ('4320001',0,140), ('4320001',300,0), ('TOTAL FOR 4320001',350,390), ('432002',200,0), ('432002',0,100), ('TOTAL FOR 432002',200,100) drop table #t1
I want to have 4 column (named BALANCE). Balance must be column that represent running totals between two columns (Column1 - Column2) for each group of data. For each group total must start from zero.after total 432 it starts to count again for total 4320001 and again for total 432002. I'm using MS SQL SERVER 2014.
I've created a Report Builder Column chart with months as the x-axis (category) and revenue as the y-axis (value). This correctly displays the revenue for each month, but I'd like to display a running total of the revenue for the year.
I'm currently displaying: Jan $2 Feb $1 Mar $4
I want to display: Jan $2 Feb $3 Mar $7
I know this is possibly using the RunningTotal formula in Reporting Services, but I'd like to do this in Report Builder so the users can change and create their own graphs with running totals. Is there a way to do this in Report Builder?
I have some data grouped in a table by a certain criteria, and for each group it is computed a subtotal for the group. Of the values from each of the group, I want to create a grand total on the report by adding every subtotal from each group.
Example: ... .... Group1        Value               10               20 Sub Total 1:     30
Group2         Value                15                25 Sub Total 2:      40
Now, I would like to be able to add subtotal 1 (30) to subtotal 2 (40) and my grand total would be 70. Can I accomplish this task in SSRS?
I have been providing sales data for a few months now from a table that is set up like this:
Date WorkDay GasSales EquipmentSales
9/1/2006 1 100.00 200.00
9/4/2006 2 50.00 45.00
etc.
As can be seen, the data is daily, i.e., on the first workday of September we sold one hundred dollars in gas and two hundred dollars in equipment. On the second workday of September we sold fifty dollars in gas and forty-five dollars in equipment.
Now, however, the data I have to pull from is cumulative. So, using the last table as an example it would look like this:
Date_WorkDay_GasSales_EquipmentSales
9/1/2006 1 100.00 200.00
9/4/2006 2 150.00 245.00
etc.
To make things more complicated, the powers that be wanted this data presented in this fashion:
Total Sales:
1_2_etc.
300.00 95.00 etc.
So, I have been doing a pivot on a CRT to get the data to look like I want. The code is like this:
with SalesCTE (Month, WorkDay, [Total Sales])
as
(
SELECT
datename(month, cag.date),
cag.WorkDay AS [Work Day],
sum(cag.sales_gas + cag.sales_hgs) AS [Total Sales]
FROM CAG INNER JOIN
Branch ON CAG.[Oracle Branch] = Branch.OracleBranch
group by cag.date, cag.WorkDay
)
select * from SalesCTE
pivot
(
sum([Total Sales])
for WorkDay
in ([1],[2],[3],[4],[5],,[7],,[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23])
) as p
So, my question is:
How do I get the data to give back daily totals instead of the cumulative amounts for each workday? If the query was a simple one, I'd do something like
select [1] as [Day 1], [2]-[1] as [Day 2], [3]-[2] as [Day 3], etc.
but the query is far from normal, with the CRT and the pivot. I can't seem to get it to work how I'd like.
Any advice/answers? Thanks in advance!!!
P.S. I don't know how to get it to quit with the freakin' smileys.... I suppose you can figure out what my code is really supposed to look like above. Needless to say, it doesn't include a devil face and a damn music note...
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]
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.
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
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
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.
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.
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
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
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)
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?
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.
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
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
