Hi Everyone I have an ADP and I need to create a report that will give me the total number of each item. I need the report to show how many [Violation Type], and the total [Loss] per violation. Can anyone help please
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 a BOM table with all finished item receipes and semi items recipes. create a query where semi item materials are also listed in finished item recipe.
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...
Here's my problem. I have 2 tasks defined in my Control Flow tab:
EXECUTE SQL--------->EXECUTE DTS 2000 PACKAGE
When I attempt to run it, by right-clicking the EXECUTE SQL task, and selecting "Execute Task", it only runs the EXECUTE SQL part (successfully), and does not "kick off" the EXECUTE DTS 2000 PACKAGE, after it is done running (even though it completes successfully, as shown by the green box).
Yes, they are connected by a dark green arrow, as indicated in my diagram above.
Why is this?? Am I missing something here? Need help.
Hello,Basically, I have a table with 2 fieldsId item#1 33332 33333 22224 22225 22226 33337 33338 3333I would like to only select the last identical Item# which in this case would be the id 6,7 and 8Any idea how could I do that?Thanks
I am having trouble finishing the last bit of a report. The report shows orders that customers have placed that contain 0 promo items, All promo items (all items in order are promo items), and a mix of promo and non promo (at least 1 promo item and 1 non-promo item). Ive simplified this a bit for ease of understanding but lets assume we have 2 tables: A Promo table that contains the items on promotion and the dates that promotion is valid, and a Sales table, that contains the order number, order date, and sku ordered.
I've already written code that finds orders that have at least 1 promo item in them, and using that, I can determine what orders have 0 promo items in them. Where I am stuck is taking the orders that have at least 1 promo item in them, and separating them into orders that have only promo items, and those that have both promo and not promo items in them. Also, there are several promos throughout the year (called "Offers") so in my code below, you can see 2 different Offers ("JF" and "MA") with their corresponding dates they are valid. They will never overlap. My results also have to be split out by Offer so management can look at the results of each offer separately. Here is some code:
Code: create table #Promos ( Offer varchar(2) null, SKU int null, StartDt date null, EndDt date null
[Code] ....
So my results should show OrderNo A1111 in the Promo and No Promo group because of SKU 5 not being promotional during the time that order was placed. OrderNo A2222 should be in the Promo Only group because both SKUs on the order were promotional at the time the order was placed.
I am using a web page to enter records into a table that tracks production on machine parts.
I get two recordsets and loop through them using vbscript to get the totals.
I use two because I first get all production data greater than the last date an event occurred. The second I get the totals for the day of because I have to back out shifts of production prior to the event, the day of the event (ie 3 shift per day, 3rd shift begins the day so a day looks like this 3, 1, 2. We do an event on shift 1. I have to back out shift 3 as it happened prior to 1. Once I have the two seperate totals I then write a record in the historical data table with the event, shift, production totals, etc.
My question is can I get the production totals from within SQL or is looping through the rs's in vb better?
the vb for the shift is similar to this If last_event_shift = 1 then DayProd = shift1prod + shift2prod elseif last_event_shift = 2 then DayProd = shift2prod elseif last_event_shift = 3 then DayProd = shift1prod + shift2prod + shift3prod else end if TotProd = DayProd + GreaterProd
Here is the SQL for each rs Data Greater than last date Select Sum(Production)AS TotProd, dbo.Production.LineNum, EventType
From dbo.Production INNER JOIN dbo.EventDate ON dbo.Production.LineNum = dbo.EventDate.LineNum AND dbo.Production.EntryDate > dbo.EventDate.EntryDate
Where dbo.EventDate.LineNum = @Line Group By dbo.Production.LineNum, dbo.EventDate.EventType
Data for the last date Select Max(dbo.EventDate.ShiftRemoved) AS RemovedShift, dbo.EventDate.Set_Pos, Max(dbo.EventDate.CutOff)AS CutOff, dbo.EventDate.EntryDate, Sum(Production)AS TotProd, dbo.Production.LineNum, EventType, Shift
From dbo.Production INNER JOIN dbo.EventDate ON dbo.Production.LineNum = dbo.EventDate.LineNum AND dbo.Production.EntryDate = dbo.EventDate.EntryDate
Where dbo.EventDate.LineNum = @Line
Group By dbo.Production.LineNum, dbo.EventDate.EventType, Shift, dbo.EventDate.EntryDate, dbo.EventDate.Set_Pos
I have done a report that shows all the subtotals however, I want to show the grand total (the sum of each subtotal) in a row. I am using SQL server BI studio-2005. I know I can add a row but all the rows are detailed rows and subsequenlty reflect the subtotals for last record in the report. All I need to know what type of row do I need to add to show my totals.
This is probably a simple task but I just don't know how to do it. I need to return a recordset of details with a row of totals for selected columns. Something with a result like this:
I know I could do this with a stored procedure, but I'd prefer to do it with just one sql statement. I tried compute but because I have a total on more than one column, the returned recordset is actually 3 rows. Thanks in advance for any suggestions.
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.
--Select Capital_Amount + Interest_Amount + Insurance_Amount + Admin_Fee --from Loan Where loan_no = '9110001AA667' --Select top 3* from loan
Select sum(Capital_Amount) As Capital_Amount from Sales Select * from Sales
How can l run these two Queries in the same view. l want to display what l have in the salesNew View and at the same time sum all the amount columns.But l'm not being successful. Is it achievable or l have to have two separate views?
CREATE View SalesNew AS SELECT DISTINCT tr.Transaction_Date, tr.Loan_No, n.Store, n.User_Issued, n.LoanBook As Company, p.Product, n.Capital_Amount, n.Interest_Amount, n.Insurance_Amount, n.Admin_Fee, n.Total_Amount FROM Transaction_Record tr INNER JOIN Loan n ON tr.loan_No = n.loan_No AND tr.loan_No = n.loan_No INNER JOIN Product p ON n.product = p.product
--2nd query
Select n.loanbook As Company,Sum(n.Capital_Amount) As Capital_Amount, Sum(n.Interest_Amount) As Interest_Amount, Sum(n.Insurance_Amount) As Insurance_Amount, Sum(n.Admin_Fee) As Admin_Fee, Sum(n.Total_Amount) As Total_Amount From Loan n Group By n.loanBook with RollUp
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
I am relatively new to SQL i.e. I know the basics!
What I am trying to do is: I have a database of customer spend and I am trying to segment them into certain %ages of the search results. So, for example, find all of the customers that are in the top 10% of spenders (I also want to only select customers with a spend of greater than x!). I am trying to do this using a Case When but feel that I may be out of my depth.
I use the following query in my stats page to find the unique visitors and the pages they visited per day in a given month.
SELECT CONVERT(CHAR(10),LogDate,103) As Date_, Count(DISTINCT LogRemote_Addr) As Visitors, Count(Lid) As Pages FROM Log WHERE LogMonth=7 Group by CONVERT(CHAR(10),LogDate,103) ORDER BY CONVERT(CHAR(10),LogDate,103)
I would like to have the totals (Sum) of the "Visitors" and "Pages" also for the given month. I think I have to use a subquery to accomplish that but I can't figure it out. I would appreciate your help. Thanks,
I have a tblTax. It has fields InvoiceNum, TaxAuthority, TaxableTotal,NonTaxableTotal, TaxCollected.Sample data1,county,10.00,0.00,0.401,city,10.00,0.00,0.102,state,0.00,15.00,0.15When totaling invoice 1 should have totals of 10.00,0.00,0.50 becausethe 10.00 is the total for the invoice, but 0.50 is the total taxcollected. I nee these totals in a report. In crystal reports i couldjust do a total on change of invoice number for the Taxable andnonTaxable Totals. but i have to this on an Access adp. I was hoping icould get a query to return something likeinv,auth,Taxable,nonTaxable,Collected,TaxableTot,N onTaxableTot,CollectedTot1,county,10.00,0.00,0.40,10.00,0.00,0.501,city,10.00,0.00,0.10,10.00,0.00,0.502,state,0.00,15.00,0.15,0.00,15.00,0.15I'm not sure how to do a group by that would allow this, although imsure its possible.
I'm writing reports that absolutely require page totals for several columns. I don't need a cumulative total for everything in the report, just for the items on the current page. Is this doable in SSRS? If not, is there another reporting package that supports putting data in the header or footer?
I have a report, using a table, that is grouped by acct. The acct indicates either revenue or expenses. I have a total in my table that will give me the totals for revenue, and the total for expenses. At the end of the report, in the table footer, I want to add a Surplus/Deficit total, which would be the total revenue - total expenses, but I can't seem to get it right. I tried the following: =Sum(ReportItems!table1_Group1.Value) thinking that it would give me the total by the group, but I get the error that an aggregate function can only be used on page header and footer. How do I just get a basic grand total in my report?
Below is part of a matrix report. Sooo close, but I have two problems I have not been able to solve. Please help as a I have several similar reports to do.
1. Row totals. I have been able to get a row total by adding a row group (i.e., $849.7 in the first row). However it does not display a group total (i.e., the sum of $849.7 and $371.3 for Economic Development) for that column.
I have not been able to add a static column outside of the dynamic groupings. I thought this might be a resolution by displaying the sum of the Estimated Costs in a static column and hoping it would show the group totals the same as with the dynamic column totals. Is there a way to do this?
2. Sorting. The report needs to display the groups based on the descending total values. I have specified the following sorts on the groups: sum(Fields!Estimated_Cost),"matrix1_Proj_Typ_Group" descending (this is the first column) and sum(Fields!Estimated_Cost),"matrix1_Proj_Typ" descending (this is the second column). Neither sort appears to be work.
City County Federal Joint
Estimated Cost % of Total Estimated Cost % of Total Estimated Cost % of Total Estimated Cost % of Total
Economic Development Business District Development $849.7 $816.0 66.83% $27.2 2.23% $0.0 0.0% $6.5 0.53%
I have a matrix that displays the number of employees grouped by their grade and department and business group. At the bottom i have a subtotal cell displaying total like so for each group
CENFIN 1 1 0 SUB TOTAL 2 1 0 BUILDINGS BIRST1 0 1 1 CHBRS1 0 1 1
SUB TOTAL 0 2 2 TOTAL 2 3 2
Is there a way i can add another row below the subtotals that only appears at the bottom and that sums either the subtotals together or the entire columns?
I put a detail group on the report for this output. I wanted to add a summary at the end of the report. which will show me the total according to filter like (if i add group footer it shows sum for all description and only in grouped row but i wanted to show total at the end and only desired groups) Here is an example. I also had created a calculated filed to calculate totals =IIF( Fields!AccountClass.Value="CHRGE", Fields!SalesTax.Value,0) it works fine but when i try to "sum" this filed it shows error instead of result.
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?
Hi, Is there a way to display the sum of a group of a field? I've created a group, but when I put the expression of SUM(Field) in the group footer, it gives me the total of Field for the whole dataset. Is there a way I can display the just the Totals of the Groups?
so if my data looks like :
a | 1 a | 2 a | 3 b | 4 b | 5
b | 6
I want to display :
a | 1 a | 2 a | 3 Total a | 6 b | 4 b | 5
b | 6
Total b | 15
but instead, when i add the SUM expression into the footer group, I get :
I have a db that tracks clients, payments, and clients codes. A client can be assigned many codes. In my query I'm asking to see any clients that have two selected codes and also of those show me those that have a sum of payments between 1 and 100000 dollars. The query returnes the clients with the selected codes, but the total payments is multiplied by the number of the codes they have of the ones that I select. In this case 'email group' or 'member' So if a client had a total of $20,000 dollars in payments and they had both of the codes I selected the sum of payments is returned as $40,000. Any thoughts are appreciated! Here is a sample example: SELECT DISTINCT [Salutation], [Client_ID] AS 'Client ID', SUM([dbo].[vw_ClientTotals].[Total Payments]) AS 'Sum(Total Payments)'FROM [dbo].[All_Constituents]INNER JOIN [dbo].[tblClientCodes] ON [dbo].[tblClientCodes].[Client_ID]=[dbo].[All_Constituents].[Client_ID]INNER JOIN [dbo].[tblClientCodeLookUp] ON [dbo].[tblClientCodeLookUp].[ClientCode_ID]=[dbo].[tblClientCodes].[ClientCodeLookup_ID]INNER JOIN [dbo].[vw_ClientGiftTotals] ON [dbo].[vw_ClientGiftTotals].[Client_ID]=[dbo].[All_Constituents].[Client_ID]WHERE ( ([dbo].[tblClientCodeLookUp].[ClientCode] = 'Member') OR ([dbo].[tblClientCodeLookUp].[ClientCode] = 'Email Groups') )GROUP BY [dbo].[All_Constituents].[Salutation], [dbo].[All_Constituents].[Client_ID]HAVING (SUM([dbo].[vw_ClientTotals].[TotalPayments]) BETWEEN '1' AND '100000')
Been trying to wrap my head around this one, I'm sure its quite possible but can't figure it.I've got a massive table of Items, each of which has a category. I want to bring back a list of categories and a total number of items in each category, so a result set which would look like:Category NoOfItemsInCategory--------------------------------------------------Category1 15Category2 1287Category3 25How would I write this query? I've been messing around with Group By and Distinct but can't get the effect I'm wanting.Thanks!
I have the following code which essentially lists the total sales for an items on the first row, and the total credits for the same item on the second row.
Code: SELECT T0.ItemCode, SUM(T0.LineTotal) as 'Total Sales' FROM INV1 T0 WHERE T0.ItemCode = 'ACR2401010' GROUP BY T0.ItemCode
[code]...
What I would like to do is write a code block that subtracts the total credits from the total sales, leaving me with only one row of data for the ItemCode.
I am not a SQL Server expert, normally work with a few other databases. We are running SQL Serer 2008 R2. I need to determine by day how much log space is being used. This is needed so I can size the file system appropriately to handle an outage of our backup system. The goal would be to have the log file system large enough to handle 24 hours worth of logs. I have found statements for current log usage, but not one for daily total logs generated.
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
