Calculation For Totals Using Percentages
Jan 25, 2006I am trying to figure how write sql statement that will give the total cost.
unit price * quantity - discount = total cost
Unit price = 9.8
quantity = 30
discount = 0.15
ADVERTISEMENT
30, 60, 90+ Days Calculation Of Invoices Totals
Feb 27, 2008Hi all - am developing my client statement report on my accounts application...
My accounts team need the ability to print off monthly statements for clients. I have done this no probs. I end up with a table of invoices (invoiceid, invoicedate, amount) in my report table. At the bottom of this table, they require the following table:
Current: £amount
30-60 Days: £amount
60-90 Days: £amount
90 + Days: £amount
basically, Say I print the report on 15th Feb. The Current value should be a sum of invoice totals from February. 30-60 Days should be a sum of invoices which were raised in Dec and Jan. 60 - 90 Days should be a sum of invoices raised between Oct and Nov. 90 Days should be a sum of invoices raised before Oct.
I'm assuming I grab the current month from Globals!ExecutionTime but where would I go from there?
Thanks in advance as usual guys :)
Stephen.
Reporting Services :: Calculating Grand Totals From Group Totals
May 9, 2015I 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?
Trying To Get Daily Totals From Cumulative Totals In A Pivot
Oct 2, 2006I 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...
Analysis :: SSAS Calculation With Division Combined With A Time Calculation?
Sep 17, 2015I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.
(so I can use measure security on calculations
as explained here )
SCOPE [Measures].[C];
THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);
Converting Oracle Calculation To Sql Server 2005 Calculation
Jul 19, 2007Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the
calculations.
Oracle
TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')
this is a sfar as I have got with the sql version
SQLSERVER2005
= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )
visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.
using:
VS 2005 BI Tools
SQLServer 2005
Using Percentages
May 1, 2008The formula for percentage safe = sum(safe)/ sum(safe)+sum(unsafe)*100.0
This works well on a calculator. Could someone show me how to achieve this in sql please. If we use safe = 92 and unsafe = 117
This forum is great!
Regards Phill
Calculating Percentages For Each Row
Dec 10, 2007One more for you while I'm trying to remember my SQL skill from a few years ago (last one of the day - promise):
My database looks like:
(Ignore that the ERD is in Access, the database is SQL Server 2005)
How do I calculate sales percentages?
I.E. how do I calculate the percentage that each product has sold as a percentage of the total sales?
What I'm aiming for is each row of products is shown, with its percentage of the whole sales.
AVG is involved this time, isn't it?
The productID is of the int datatype (not that you need to know that field - but never mind) and the quantity is a smallint, before you ask
Thank you in advance for any replies.
Paul
Comparing Percentages
Mar 17, 2008hi
i have a three fields in my table..those are
FromPercentage -Varchar(30)
ToPercentage -Varchar(30)
Scale -Int(11)
i have the percenatge that 10%...now i want to check that whether this 10 % is in between the frompercentage and topercentage..if it is there i want to return the the scale value..how can i do this in sql.
i know comparing when it is integer using where and and operator..but i dont know how use if it has the percentages.
Ram
Stuck On Percentages...
Apr 18, 2008most my fields that I am interested in getting percentage values for have to be counted first.... I am having problems converting the counted values to percentages. I don't think I am doing it right...
I would greatly appreciate an example of a very simple matrix table with a counted value that displays percentages. I have found examples online but are part of a much more complex table and I can't seem to get thier formating to work on mine.
Kind regards,
Rich
Using OVER Clause To Calculate Percentages
Jun 25, 2015I've always user over with partitioning however recently I am looking at some other code and they have calculating percentages using over. I'm at a lost on why you would do this.
Example would be
CAST(TABLE1.UNIT AS DECIMAL (6,2)) / SUM(TABLE2.UNIT) OVER () AS UNIT_%
What about be the advantage of using OVER there?
Return Percentages In The Query.
Oct 13, 2006Hello,I'm trying to something that just works in Oracle, but does not in SqlServer.How can I get the percentage of two counts to be returned on each rowof the query?select count(sid), /* all not the not null */count(*),(count(sid) /count(*) ) as percent_not_null,4 as four,(3/4) as three_over_fourfrom dbo.sysusers7082040Incredibly, it changes even 3/4 into a zero!For efficiency, I want the percentage returned in the query.And to not use variables and coding. Efficiency,both of the server, and of my time.Note: I am using dbo.sysusers as an EXAMPLE only. My realquery will be on user defined application tables.What is the solution please?
View 4 Replies View RelatedExpressing Values As Percentages
Jul 20, 2005I have two values and I want to express a third derived value as apercentage of the other two values. I thought it would be a simpledivision of the first two numbers and then a multiplication by 100 togive me a percentage, but all I get is 0.Here is my select statement,SELECT dbo.Eligble.GRADETotal,dbo.nil1234_Faculties_Totals.FACTotal,dbo.nil1234_Faculties_Totals.FACTotal /dbo.Eligble.GRADETotal * 100 AS [PERCENT]FROM dbo.Eligble CROSS JOINdbo.nil1234_Faculties_TotalsCan anyone point out where I'm going wrong here?Thanks in advance
View 2 Replies View RelatedHow Do I Calculate Percentages? It's Supposed To Be EASY!
Jun 29, 2004here's my code...
DECLARE @Output FLOAT
SELECT @Output=(@Part/@Whole)*100
When I substitute my parameters like this:
SELECT @Output=(5/20)*100
The answer is always 0.0!
But if I do this:
SELECT @Output=(5*100)/20
I get the correct answer.
Why is this so?
What is the correct way in SQL to calculate percentage??!!!
Code To Calculate Percentages By Month?
Mar 30, 2015I have a query which provides the total number of each of 2 invoice flags per month, starting 1st Feb this year. I also need a percentage calculated for each of the 2 flags by month. However, it is showing the totals correctly for each of the flags by month, but the percentage is using the total of both flags for all months, and so it is wrong if I only want to look at one month. The query is as follows along with a sample output including % which are incorrect.
How can I correct this so that it shows the right % for each of the flags for each month?
Select
sk.Period Period,
sk.[Invoice Flag],
count(*) * 100.0 / Sum(count(*)) Over () Percentage,
count(*) Total1
From
(Select wh.worknumber [Work Order],
[code].....
Output at present:
Period Invoice Flag Percentage Total1
--------------------------------- ------------------------------- --------------------------------------- -----------
2015-02 Invoiced after 7 days 5.704697986577 136
2015-03 Invoiced after 7 days 2.097315436241 50
2015-02 Invoiced with Customer Approval 59.563758389261 1420
2015-03 Invoiced with Customer Approval 32.634228187919 778
Group Expression That Calculate Percentages.
Mar 8, 2007I have an expression in a group that calculates percent of sales:
=iif(Sum(Fields!BOOKD.Value)=0,0,IIF(Sum(Fields!NET.Value)=0,0,((Sum(Fields!BOOKD.Value)-Sum(Fields!NET.Value))/Sum(Fields!BOOKD.Value))))*100
The problem I'm having is that if both the
Sum(Fields!BOOKD.Value)=0 and
Sum(Fields!NET.Value)=0 ,
the expression returns the message #ERROR instead of a 0.
I have the initial query set to display 0 if the value is null.
,SUM(ISNULL(R.BOOKD,0))BOOKD
,SUM(ISNULL(R.NET,0))NET
I would greatly appreciate any help on how to solve this issue.
Thank Barb
Creating Dataset For Report - Percentages In Result
Nov 3, 2015Trying to create a dataset for a report. I need to bring back percentage in the result set. The fields that I am using to get the percentage have valid data but the result field is 0.00%. Is there reason I cant bring back a percentage field?
Code:
SELECT JobNum, Mailed, LT_7, BT_6_10, GT_10,
Format([LT_7]/[Mailed], 'p') AS PctScaned1,
Format([BT_6_10]/[Mailed], 'p') AS PctScaned2,
Format([GT_10]/[Mailed], 'p') AS PctScaned3,
[Code] ....
Calculating Percentages And Showing In Separate Columns
Aug 31, 2013SELECT DISTINCT CASE WHEN SM.SERVICE_TYPE_N = 1 THEN 'LABORATORY'
WHEN SM.SERVICE_TYPE_N = 2 THEN 'PODIATRY'
WHEN SM.SERVICE_TYPE_N = 3 THEN 'ADMINISTRATION'
WHEN SM.SERVICE_TYPE_N = 4 THEN 'DIET'
WHEN SM.SERVICE_TYPE_N = 5 THEN 'DENTAL'
[Code] ....
In the above query i need to calculate 100%,30% and percentage other than 100 and 30 and show them in separate columns how to do that?
1)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [100_PERCENT]
WHERE BM.BILL_AMOUNT_M=BM.CONCESSION_AMOUNT_M
2)ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [30_PERCENT]
WHERE AND BM.CONCESSION_AMOUNT_M=BM.BILL_AMOUNT_M * 0.30
3) ROUND(SUM(BM.CONCESSION_AMOUNT_M),2)AS [OTHER_CONCESSION_PERCENT]
WHERE BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.001 and BM.BILL_AMOUNT_M*0.299
OR BM.CONCESSION_AMOUNT_M between BM.BILL_AMOUNT_M*0.301 and BM.BILL_AMOUNT_M*0.999
Calculating Percentages Based On Multiple Rows
Oct 2, 2014I have two tables that look like this (below). One tells me the name of my product, the Amazon Category it is in & the amount that I want to sell it for. The other tells me the Category & the fee for that category. So far so good. Though it gets tricky in the sense that some categories have two tiers. So in Electronics, the fee for $0.00 - $100.00 is 15%. But from $100 and up it is 8%.
Since it has two columns & both of the new columns pertain to the fee of my product, I can't figure out how to use both at once. For my $599.99 example it would be ($100 * 0.15) + ($499.99 * 0.08) = $55.00. Would I pivot the data? If not, how would I group it to be considered together?
Category Example
IDAmazonCategoryIDAmazonCategoryNameFeePercentageStartPriceEndPrice
1apsAllDepartments0.150.000.00
2instant-videoAmazonInstantVideo0.000.000.00
3appliancesAppliances0.150.000.00
Product Example
1Product1Electronics9.99
2Product3Electronics99.99
3Product2Electronics599.99
Raw SQL
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE #Amzn_Category_FeeStructure(
[ID] [int] IDENTITY(1,1) NOT NULL,
[Code] ....
I use Microsoft SQL 2008
Label Stacked Bar/column Chart With Percentages
Mar 11, 2008Hi,
I need to label stacked bar /column charts with the percentage of each series at each axis point.
For example, if I have the following values in the chart:
Series 1 Series 2
Axis point 1 50 25
Axis point 2 12 12
I need the labels to read: Series 1 Series 2
Axis point 1 67% 33%
Axis point 2 50% 50%
It would seem that I need to format the data point labels to read something like: sum (data.label)/sum(data.label, "axis point")
Any ideas would be appreciated. Thanks in advance for your elegant solutions
Funky Formatting Of Percentages In Excel Export
Sep 18, 2007
I've created a report that has cells in it that I want to be formatted as percentages. I set the format code in the cell properties to "P1" and everything appears to work fine until I export to Excel. Some cells show up with only one digit after the decimal and some show up with two digits after the decimal.
I looked at the formatting in Excel (right click, Format Cells...) and it is set to "[$-1010409]#,##0.0#%". I don't have the first clue what that is "supposed" to do but what it does is this:
If I enter 100.00 in the cell it shows up as 100.0%. Expected
If I enter 100.10 in the cell it shows up as 100.1%. Expected
If I enter 100.15 in the cell it is displayed as 100.15%. Not expected
I have formatted the report to only show one digit after the decimal, but RS exports to Excel with some funky formatting that sometimes shows one digit and sometimes two digits after the decimal. Why wouldn't it simply format the cell as a percent? How can I get it to only show a single digit after the decimal?
This is Excel 2007 but Excel 2003 has the same behavior.
Thanks
--John
Reporting Services 2005 - Matrix Grouped Report - Calculate Percentages
Jun 29, 2007Hi,
Could someone help with the following problem, I what to create a cross-tab report of the following data, grouped by Colour:
RAW DATA
Name
Colour
Sarah
Black
Kim
Red
Jane
Black
Jane
Pink
Robert
Yellow
Tom
Green
Tom
Black
Billy
Black
Sarah
Black
Sarah
Black
Tom
Pink
Kim
Pink
Robert
Black
Group Colours by Names Report
Number
Proportions %
Name
Black
Green
Pink
Red
Yellow
Total by Name
Black
Green
Pink
Red
Yellow
Billy
1
1
100.00%
0.00%
0.00%
0.00%
0.00%
Jane
1
1
2
50.00%
0.00%
50.00%
0.00%
0.00%
Kim
1
1
2
0.00%
0.00%
50.00%
50.00%
0.00%
Robert
1
1
2
50.00%
0.00%
0.00%
0.00%
50.00%
Sarah
3
3
100.00%
0.00%
0.00%
0.00%
0.00%
Tom
1
1
1
3
33.33%
33.33%
33.33%
0.00%
0.00%
I want to produce a Matrix cross-tab report, like the above, within Reporting Services. Any suggestions welcome
Many Thanks,
Radha
Getting Totals Help
Feb 22, 2004I 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
Totals
Mar 16, 2007Hello 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
How To Include Row Totals?
Feb 4, 2002This 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.
Running Totals With SQL
Oct 1, 1999I 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?
Sum Totals Per Column
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
Running Totals
Jan 12, 2004Hi 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 ?
Running Totals
May 23, 2008On 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)
Running Totals
Jun 11, 2008Now 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
Percentage Of Totals
Nov 14, 2006I 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
Subquery For Totals
Jul 17, 2007Hi,
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
Totals On Change
Aug 25, 2006I 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