Incorrect Totals In My Header Rows
Mar 31, 2008
All-
Forgive me if this has been answered in previous posts but I need some assistance fast . I have created a report that shows Activity Time per Area. On my table, Group #1 is grouped by the Request Area and Group #2 by the Ticket Num. If you look at the 2nd group (ticket #'s), the totals at the ticket level for Avg Time Open are correct but when I try to use the same formula (=IIF(Fields!active_flag.Value = "0", (Fields!close_date.Value-Fields!open_date.Value)/3600, nothing) for Group #1, it doesn't calculate correctly (it seems to only select one record and not always the first record). For Example:
Please help...
Request Area
Total
Open
Closed
Avg Time Open
Enterprise
4
0
4
71.57
1600459
1
0
1
71.57
1603766
1
0
1
0.03
1606463
1
0
1
0.02
1607862
1
0
1
1.96
Thx!!!
View 3 Replies
ADVERTISEMENT
Nov 6, 2007
I have a report that I created and the report was working until I added some fields to a group footer row in a table.
My table has 5 group levels. I had information displaying in the 5th level header group and detail. It was working fine. Then I added some fields to the 4th level group footer. Now it displays only the Page header, Table header, and the 4th level group footer data.
What happened to the rest of the data?
All the cells and rows I want to display have the Visibility Hidden set to false. I tried removing the objects I added (to the 4th level group footer) and it still does not work. Is this a bug or did I set something that is hiding the data.
Thanks,
Fred
View 1 Replies
View Related
Dec 19, 2007
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.
View 6 Replies
View Related
Feb 11, 1999
SQL mail is configured and working (SQL6.5 sp3). I configured scheduled tasks to notify me of completion status, which it does.
I'm just wondering why the header time is off by 5 hours.(time SQLServer Service sent the message)
The SQL server time and time zone are correct, and so are the time and zone on my workstation.
I tested sending a message from a user on the same Exchange Server as the SQL Server user to the user that recvs the scheduled task mail, and the time is correct in that message header.
Any ideas?
Thanks in advance.
View 1 Replies
View Related
Jun 29, 2007
First off, here is the application.
An airport baggage handling system distributes bags using multiple conveyors.
Bag counts are logged every 15 minutes. There is a count for each conveyor.
Example Log Table layout is as follows (The TIME column is DateTime, the Convx columns are TinyInt)
Time Conv1 Conv2 Conv3 Conv4 Conv5 Conv6
i 3 2 3 4 2 1
i+15min 2 3 4 2 2 2
i+30min etc.....................
i+45min
i+60min
etc...
The management team wants a throughput report which will take the following parameters in order to filter the results:
Begin Date
End Date
Time Interval (selectable as 15mins, 30 mins, 45mins, 60 mins and Daily)
My question is this. Given that my raw data has 1 row for every 15 minutes, if they select 60 minutes as their interval I need to run the query with the start and end dates but Sum every 4 rows and display it as 1 row, likewise if they select 30 minute interval, I need to sum every 2 rows. How do I run a query and SUM the Conv count data for every x number of rows and use the 1st TIME value in the returned x row summary?
Thanks for your help and let me know if I need to clarify anything
View 3 Replies
View Related
May 30, 2008
Hello.
I am seeing some behaviour I don't understand. I can't believe it hasn't been discussed here before, but my searches have returned nothing.
Here it is.
I have a CSV file. Its first rows look like this:
# 3_results/ABC.csv
# Created from data/clean/
# OS Unix, host smac.local, user ge
col1,col2,col3,col4,col5,col6,col7,col8,col9,col10,col11,col12,col13
38, 2008-05-05 14:35:05, 92,0,0,FFF,1,0,1,0,1,0,0
41, 2008-05-05 14:35:10, 4,1,0,GGG,1,0,0,1,0,0,0
75, 2008-05-05 14:35:26, 2,1,0,HHH,1,0,0,1,0,0,0
183, 2008-05-05 14:35:26, 4,1,0,III,0,0,0,0,0,1,0
347, 2008-05-05 14:35:28, 11,0,0,JJJ,0,0,0,0,0,1,0
365, 2008-05-05 14:35:28, 0,1,0,KKK,0,0,0,1,0,0,0
41, 2008-05-05 14:35:29, 18,0,0,LLL,1,0,0,1,0,0,0
286, 2008-05-05 14:35:30, 3,1,0,MMM,0,0,0,0,1,0,0
So, there are three lines of miscellaneous information, one line of column names, and the data to be imported begins on row 5.
So I import with this:
BULK INSERT tableName FROM 'file.csv'
WITH (FIELDTERMINATOR = ',',FIRSTROW = 5)
And the first line to be imported is the one beginning with 183!
This does what I want:
BULK INSERT tableName FROM 'file.csv'
WITH (FIELDTERMINATOR = ',',FIRSTROW = 2)
I can insert as many lines starting with # as I want and it works fine.
What is going on?
Thanks.
View 4 Replies
View Related
Jul 4, 2005
Is there any way to add a header to a Matrix row in RS SP2? Much like a header for fields in a table.
View 4 Replies
View Related
May 9, 2015
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?
View 5 Replies
View Related
Aug 9, 2007
Hi All:
My below sub in application is returning only the Header Row instead of the relevant rows I guess therez problem in my "str" Syntax. However I fail to understand where exactly is it faultering. Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Try
con.Open()Dim empID As String = txtEmpID.Text
Dim FName As String = txtFName.Text
Dim LName As String = txtLName.TextDim str As String = "Select Emp_ID, Emp_FName,Emp_LName,Emp_Address1,Emp_HNo,Emp_MNo from Emp_Details where Emp_ID='" & empID & " ' Or Emp_LName='" & LName & " ' Or Emp_FName=' " & FName & "'"""Dim da1 As New SqlDataAdapter(str, con)
da1.Fill(ds, "Emp_Details")
dgEmp.DataSource = ds
dgEmp.DataMember = "Emp_Details"
dgEmp.DataBind()
Finally
con.Close()
End Try
End Sub
Thanks in Advance for your quick help.
Regards,
Brandy
View 2 Replies
View Related
Feb 18, 2015
I create table with automatically generate the next ID.
create table #people
(id integer identity primary key not null,
name varchar(20),
surname varchar(30),
number Char(11)
);
and next I would like to add many lines
insert into #people (name, surname, number)
VALUES
('Anne', 'Ferguson', '123456789'),
('Eve', 'Atkinson', '234567891'),
('John', 'Smith','345678912');
and I've got:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near ','.
This is something wrong with the row ('Anne...
I use sql MS server 2008
View 1 Replies
View Related
Jan 13, 2006
I have a table with a column in it called Date, which is of the type DateTime, and for the last two years I have been adding data which I found out was incorrect.
My dates are all a day in the future, so I need to reduce each date by one day.
I can easily use a select script to reveal the 25,000 rows which are all incorrect dates. But I can't figure out how to update each and every row to subtract one day from each date.
So where I have:
26/01/2005
I would like to have:
25/01/2005
and of course for every record. Obviously way too many to do manually :-(
Can anyone show me a script that will get what I'm after.
Tia
Tailwag
View 6 Replies
View Related
Oct 2, 2006
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...
View 12 Replies
View Related
Sep 10, 2015
I have an SSRS report I'm working on. I have Row Groups plus details.
A) How can I get the row group headers to repeat on every line?
B) I would still like to use the drill down, click the plus sign, and have that work when I export to excel.
Here is the report header: URL....My first thought was is there a way simply to cause the header to repeat. I cannot find that anywhere to do that.My second though was to split the cells and copy the fields to all the rows.Here is the same header with split cells:URL....two problems.
A) I cannot split one cell; See the "X" in the second capture.
B) How do I still hid the rows until I drill down.
View 3 Replies
View Related
Jun 20, 2007
Hi,
I have requirement to display Total number of Rows in a Report in Page Header.
I have written the following code in Page header it shows RowCount for the Page only.
=Count(ReportItems!textboxInTableCell.Value)
Can anyone please help on this?
Regards
Raghav
View 3 Replies
View Related
Aug 29, 2006
Just attempting to import a simple tab delimited text file into my SQL Server 2005 database using the SQL Server Import and Export wizard. Column names are specified within the first line of the file. The Header Rows to Skip field value is listed as 0, but the wizard indicates that "The field, Header rows to skip, does not contain a valid numeric value".
Why isn't zero (0) a valid numeric value? I don't want to skip any rows. PLUS, I get the same error when trying to export to a text file although the header rows to skip field does not exist. I can increase the number to 1 or more, but the wizard will skip part of my data .. unacceptable.
What am I missing here? I installed SP1 of SQL server 2005, but that did not help.
Thanks in advance.
View 1 Replies
View Related
Dec 21, 2006
Hi all
I have a flat file.I am trying to set the value for the property "HeaderRowsToSkip" during runtime.I have set an expression for this in my "flat file connection manager". But this is not working.The connection manager is not able to take the value during runtime.
My expression is as follows:
DataRowsToSkip : @[user:: Var]
where "Var" is my variable which gets the value from the rowcount component and trying to set it back to the "HeaderRowsToskip" property.
I ve even tried setting the value to the "HeaderRowsToSkip" property in the expression builder.
Its not working....
Can anyone help me out in solving this????
Thanks in advance
Regards
Suganya
View 22 Replies
View Related
Mar 13, 2008
I need some help. I am writing a report in SSRS 2005 that I then need to export to Excel. When I put a report header I would expect the header to not display in the Excel spreadsheet until the Print Preview or the Print. The report footer works just fine I put some text in the footer, and it shows up in the footer. The header though, shows up as a row in the Excel spreadsheet that then causes columns to merge. How do I get the report header to act like a page header?
View 1 Replies
View Related
May 13, 2015
I am making a book-like report, I am using a report that has a header and calling a sub-report that has it's own header. However the sub-report header is not showing on the parent report. Parent report header is prevailing over the sub-report. Is it possible to have both headers displaying?
View 3 Replies
View Related
Nov 6, 2007
Hi Friends,
There is a one header in the report, when I publish and hit the report in IE(internet explor) the header appears fine on first page when I go to next page this header does not appear.
But in mozilla the header is visible on every page of the report. so it is working fine in mozilla.
I donot why it is happening?
Your help is highly appreciated.
Thanks
Novin
View 1 Replies
View Related
Oct 7, 2015
I have a sql query
create table temp
(
date1 datetime,
category varchar(3),
amount money
[code]....
In above format:15-10 means Oct 15, 15-09 means Sept 15I am getting issues while formatting this in SSRS.I need SSRS table headers to be dynamic. I need that it should be according to the sql table header...The table header should change according to the sql query
View 2 Replies
View Related
May 27, 2008
This is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
End Sub
View 2 Replies
View Related
Feb 22, 2004
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
Thanks in advance,
Lee
View 2 Replies
View Related
Mar 16, 2007
Hello All,
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.
Thanks,
Rashi
View 3 Replies
View Related
Feb 4, 2002
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:
Location Attendance Showings
======== ========== ========
JOHNS 210 3
SEREN 116 2
total 326 5
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.
View 2 Replies
View Related
Oct 1, 1999
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
uid value rtotal
1 3 3
2 9 12
3 4 16
4 7 23
5 2 25
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.
Any Ideas?
View 1 Replies
View Related
May 27, 2002
--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
View 2 Replies
View Related
Jan 12, 2004
Hi Folks,
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 ?
View 3 Replies
View Related
May 23, 2008
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)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'Worktable'. Scan count 2, logical reads 600001, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T'. Scan count 100000, logical reads 200002, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3500 ms, elapsed time = 3724 ms.
(100000 row(s) affected)
View 4 Replies
View Related
Jun 11, 2008
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
View 3 Replies
View Related
Nov 14, 2006
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.
Please help if you can!!
Thanks, Kris
View 4 Replies
View Related
Jul 17, 2007
Hi,
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,
Deni
www.tabletennis.gr
View 9 Replies
View Related
Aug 25, 2006
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.
View 3 Replies
View Related
Dec 29, 2006
Hi,Am using robvolks crosstab-procedure to generate a crosstab query.I get this result:Total A B Cjuli 455 1 107 347okt 83 1 9 73aug 612 1 113 498juni 451 1 108 342So I get a total for each month. But I would also like a total of eachletterTotal A B Cjuli 455 1 107 347okt 83 1 9 73aug 612 1 113 498juni 451 1 108 342Total 1601 4 337 1260Is that possible?/jim---call to procedureexecute crosstab 'select DATENAME(month,(theDate)) as '' '', count(*) as'MonthsTotal'' from tblData group byDATENAME(month,(theDate))','count(letter)','letter ','tblData'-----------Robvolks procedure----CREATE PROCEDURE crosstab@select varchar(8000),@sumfunc varchar(100),@pivot varchar(100),@table varchar(100),@where varchar(1000)='1=1'ASDECLARE @sql varchar(8000), @delim varchar(1)SET NOCOUNT ONSET ANSI_WARNINGS OFFSET LANGUAGE DanishEXEC ('SELECT ' + @pivot + ' AS pivot INTO ##pivot FROM ' + @table + ' WHERE1=2')EXEC ('INSERT INTO ##pivot SELECT DISTINCT ' + @pivot + ' FROM ' + @table +' WHERE '+ @where + ' AND ' + @pivot + ' Is Not Null')SELECT @sql='', @sumfunc=stuff(@sumfunc, len(@sumfunc), 1, ' END)' )SELECT @delim=CASE Sign( CharIndex('char', data_type)+CharIndex('date',data_type) )WHEN 0 THEN '' ELSE '''' ENDFROM tempdb.information_schema.columnsWHERE table_name='##pivot' AND column_name='pivot'SELECT @sql=@sql + '''' + convert(varchar(100), pivot) + ''' = ' +stuff(@sumfunc,charindex( '(', @sumfunc )+1, 0, ' CASE ' + @pivot + ' WHEN '+ @delim + convert(varchar(100), pivot) + @delim + ' THEN ' ) + ', ' FROM##pivotDROP TABLE ##pivotSELECT @sql=left(@sql, len(@sql)-1)SELECT @select=stuff(@select, charindex(' FROM ', @select)+1, 0, ', ' + @sql+ ' ')EXEC (@select)SET ANSI_WARNINGS ONGO
View 3 Replies
View Related
