I need to calculate total discount on item in case when user has several discounts, and they each apply on discounted amount. I thought to have something like:
DECLARE @Disc float
SET @Disc = 0
SELECT @Disc = @Disc + (100 - @Disc) * Disc / 100
FROM UserDiscounts
WHERE UserID = 123
but, seems, it does not work.
Looking for single query without any loops?
Hi,
I'm new to Reporting Services, and I have to make a sort of timesheet-report out of a sharepoint list.
The sharepoint list works great, employees give in their start, stop and lunch times, and the project they are working for.
Now, on my report, everything looks great, except, I can not get the grand total of the worked hours.
I think I need some function in my totalHours field, but I don't know how to write this.
So I am looking for someone to help me write this, and I am willing to pay for it.
Can anyone help me out?
Thanks in advance, Natascha
I have developed a cube in my work place for analyzing current year sales with previous year sales in Time Hierarchy (Year- Quarter- Month) using Parallel period. If we want to see data for particular Quarters i.e. Q1 and Q2 then total at the year level should also get change. Currently if we only choose 2 quarters in the filter then current year data gets change, however data using parallel period is not getting change accordingly and its shows Total of full year.
View 4 Replies View RelatedI cannot understand why Total of calculated member is displayed incorrect. How should I change calculated member for it to work correctly?
Calculated Member:
CREATE MEMBER CURRENTCUBE.[Measures].AverageScore
AS IIF([Measures].[Distance]<2001,0,[Measures].[avgscore]/[Measures].[Date Count]),
VISIBLE = 1;
It seems that Total is calculated without checking value in "AverageScore" for that month.
I am using sql server 2008 to calculate the percentile value for each column.
--Script
CREATE TABLE #Table (Score Varchar(4),Percentile int)
INSERT INTO #Table (Score)
VALUES
('80'),('55'),('125'),('99'),('75'),('130'),('37'),('73'),('151')
select * from #Table
I would like the result to be shown in 25,50,75,100 percentile values . Here is the example
Score | percentile
80 | 75
37 | 25
151 | 100
75 | 50
I have the following problem with ROUND. When doing the calculation for each value by a percentage using round, the sum of the result does not equal the sum of the values for the percentage (also using round).
IF OBJECT_ID('Tempdb..#Redondeo') IS NOT NULL DROP TABLE #Redondeo
Create Table #Redondeo (Orden int Identity(1,1),Valores money)
Insert into #Redondeo
Select 71374.24 Union Select 16455.92
Union Select 56454.20 Union Select 9495.18
[Code] ...
RESULT: 177673.74; 37311.49
I am trying to produce a report that will show a duration in minutes of a time when a room was occupied for a category. Whilst I have the start and end dates and times, the end user must be able to specify not only a range of dates, but a start and endtime of hours in the day in which they are interested in (it will be applied to all days in the range - they are not allowed to specify a different start/endtime per day).
The example I have is a date range of 6 to 17 October, but they only want the times from 09:00 to 21:00, so if a room was occupied from 08:00 to 11:00 they would only want to know the duration as 120 minutes (09:00 to 11:00) not 180.
The data is supplied by a third party, and duration in minutes is supplied, but it is not much use when they are not interested in the 'real' duration.
CREATE TABLE [dbo].[Evts](
[Location_name] [nvarchar](200) NULL,
[Event_Category] [nvarchar](500) NULL,
[Start_Time] [datetime] NULL,
[End_Time] [datetime] NULL,
[Duration] [int] NULL
[Code] ....
---but the expected output is
Location_nameEvent_Categoryduration
RS8CM240
RS8OD480 -- eliminating 08:00 - 09:00 on 17th
RS8OOT300 -- eliminating 08:00 - 09:00 on 13th
RS8SCC150
RS8SODT1740
RS8 SODT180
RS8SODTB60
RS8SODTNT180
RS8TR60
Looking for info regarding to the minute calculation when the start time specified by the user differs from that of the actual startime?
I'm writing a query that will be used in Jasper Ireports, but prefer to have the values done ahead of time using SQL rather than relying on the report to do the lifting.The fields are pretty straight forward, only the display is where I have a question.
Fields Used: PERIOD ('MON-yyyy') and VALUE
The results must start with the CURRENT PERIOD (AUG-2014) in one column and the VALUE for the current period multiplied by 1/12 (VALUE*(1/12)).The next column should return the VALUE for CURRENT PERIOD - 1 (JUL-2014) and multiply by 2/12 (VALUE*(2/12))
This should continue for the last 11 months and would end with OCT-2013 with the value being multiplied (VALUE*(11/12)).Is the easiest solution to this a CASE statement looking at PERIOD then PERIOD minus one month, minus two months...etc?
I have zip code database and i am trying to get the records based on 100/80/60/40 miles radius. Below the sample structure of my table
Create table ZipCodes(ID bigint primary key identity(1,1),ZipCode varchar(10),
Address varchar(4000), city varchar(50),state varchar(50),Latitude float,Longitude float)If i pass the Zipcode as '10021'
I need to get zipcodes and other details of my table which is 20 mile radius circle.
I Google through and got couple of article explains about using Haversine Formula. Also, following link has a function to calculate the distance.
I have a field in a table which was VERY poorly designed, but that is a matter for another day. Long story short, this field contains, in most instances, where the sale was obtained, the sales man name, and a comment about the sale. A few records have garbage data in the field as the salesman name was not obtained so we want to attribute the info to 'unknown'Is it possible in SQL Server 2008 to write a query that will display the saleinfo for each salesman then a total row under the salesman? Something similar to this
Internet Mark .... statistics here
Phone Mark - applied for credit .... statistics here
Phone Mark - customer referral ..... statistics here
Marks Sales Totals .... statistics here
Next salesman data would go here
but break that down by each salesman and attribute the garbage data like 85623, albaca, racava to salesman 'Unknown'..This is garbage data that should suffice to achieve my desired end result. I know this is unable to occur with a simple select. I even tried a few CTE queries but couldn't get the syntax accurate due to saleinfo basically being a catch all column I was unsure of how to only extrapolate the data I needed
Create Table sales
(
saleid int
,saleinfo varchar(200)
,salestatus varchar(200)
)
Insert Into sales VALUES
(1, 'Phone Mark customer referral', 'Done'), (2, 'Phone Mark customer referral ', 'Done'), (3, 'Phone Mark - applied for credit', 'Holding Pattern')
,(4, 'Internet Mark', 'Done'), (5, 'Internet Mark', 'Holding Pattern'), (6, 'Internet Mark', 'Holding Pattern')
,(7, 'Phone Stan', 'Holding Pattern'), (8, 'Phone Stan', 'Done'), (9, 'Phone Stan', 'Holding Pattern')
,(10, 'Internet Stan - lives to far to drive', 'Done'), (11, 'Internet Stan', 'Done'), (12, 'Phone Vic', 'Done')
,(13, 'Phone Vic', 'Holding Pattern'), (14, '85623', 'Done'), (15, 'albaca', 'Done'), (16, 'racava', 'Done')
Here is example table and data
CREATE TABLE [dbo].[sales](
[date_value] [datetime]NOT NULL,
[monthly_total] [int] NOT NULL
)
insert into sales ( date_value, monthly_total)
[code]...
How can I create a query to get below result?
YearMonthMonthlySaleAccumulated Total
2012Jan110 110
2012Feb130 240
2012Mar30 270
2012Apr60 330
2012May10 340
[code]...
select col1,count(*) from client1..table1 group by col1
union
select col1,count(*) from client2..table1 group by col1
union
select col1,count(*) from client3..table1 group by col1
The results yields
33915
3405
3412
I am trying to get the following result but can't figure out how to get the total in the end.
33915
3405
3412
Total 22
My Table struct
create table cust
(
cust_id int ,
city varchar(20),
pincode int,
sales int,
latitude float,
longitude float
[code]....
like i wanna display each pincodes how to make a code?
I have a requirement to calculate the total outage time, based on logged fault tickets, of network nodes. Basically, multiple tickets may be raised for a single node and those tickets could overlap or sequence over a given period; the task here is to calculate the total time (hh:mm) of the outage in the period.
Ex:
3 tickets raised for a node outage over, say, a 48 hour period. Ticket 1 (spanning a total of 5 hours) overlaps with ticket 2 (spans 3 hours) by 1 hour; ticket 3 starts 5 hours after ticket 2 and spans 1 hour. Total outage time on the tickets is 7hrs + 1hr (T1+T2 minus the 1hr overlap) and the full time of T3.
In summary, it's calculating the total ticket time, allowing for overlaps of tickets, etc.
I have a field Char(10) named bank, and another one Char(15) named namebank.
I need to create a TSQL query, but i need to preserve the white space on my first field named bank and concatenate with the other namebank, this last i dont need white space.
B001 Bank 123
How can accomplish this query.
I've the table like
create table accutn_det
(
fs_locn char(50),
fs_accno varchar(100),
fs_cost_center varchar(100),
fs_tran_type char(50)
[Code] .....
Like all location details stored from all months in these table
here Dr=debit,Cr=Credit Formula= 'Dr-Cr' to find the salary wavges of amount
so i made the query to find the amount for may
select
fs_locn,
fs_accno,
amount=sum(case when fs_accno like 'E%' and fs_tran_type='Dr' then fs_amount
when fs_accno like 'E%' and fs_tran_type='Cr' then fs_amount * -1
end
)
from
accutn_det where fs_trans_date between '01-may-2014' and '31-may-2014'
groupby fs_locn,fs_accno
now i need the sum values of all costcenter for the particular account.how to do that?
I have a SP SPone. i have optimized that and kept it as SPone_Optimized. i would like to test the both SP's execution time to find out how best the optimized one fares.
i planned to test it as follows
declare @starttime datetime,@endtime datetime
declare @count int=0
select @starttime=getdate()
while(@i<10000)
begin
execute SPone_optimized @param='value1'
end
select @endtime=getdate()
select datediff(ms,@stattime,@endtime) 'total_exec_time'
----- for the SP that is before optimize
declare @starttime datetime,@endtime datetime
declare @count int=0
select @starttime=getdate()
while(@i<10000)
begin
execute SPone @param='value1'
end
select @endtime=getdate()
select datediff(ms,@stattime,@endtime) 'total_exec_time'
We sell & ship packages that contain multiple items within them. The actual package (we call it the "parent item") is in the same table as the items within it ("child items"). If the record is a child item within a package, its "ParentId" field will contain the ItemId of the package.
So some sample records of a complete package would look like this:
ItemId | ParentId | Name | QtyAvailable
----------------------------------------
1 | NULL | Package A | 10
2 | 1 | Item 1 | 2
3 | 1 | Item 2 | 3
ItemId's 2 & 3 are items contained within the ItemId 1 package.
Now however, the client wants us to build a report showing all packages (all items where ParentId is NULL) however, they want to see the QtyAvailable of not only the package but the items as well (a total of 15 when using the example above), all grouped into a single line. So a sample report line would look like this:
Name | Available Qty
--------------------------
Package A | 15
Package B | 100
How can I do a SELECT statement that SUMS the "QtyAvailable" of both the parent & child items and displays them along with the package name?
I have been tasked with writing a report that shows all open orders for an item and their quantities, along with a running total of what is left in stock. We start by building these two tables:
IF OBJECT_ID('tempdb..#Orders', 'U') IS NOT NULL DROP TABLE #Orders;
IF OBJECT_ID('tempdb..#Warehouse', 'U') IS NOT NULL DROP TABLE #Warehouse;
CREATE TABLE #Orders (OrderDate DATETIME, JobNumber NVARCHAR(10), Item NVARCHAR(20), QtyOrdered NUMERIC(10, 2))
INSERT INTO #Orders SELECT '20150801', 'JOB1', 'Widget1', 5
INSERT INTO #Orders SELECT '20150802', 'JOB2', 'Widget1', 3
[code]....
Hi all,
Hopefully a nice easy one to begin with. I have an access database which I use to build my web pages - I use sql to extract products which meet certain criteria then the pages are automatically generated. I can extract the data without any problem. An example I use is shown below:
===================================================
SELECT * FROM products WHERE products.group = 'LCD-HDTV' AND status = 'LIVE' AND tradeprice > 0 AND description3 > '' ORDER BY diagonal, tradeprice, description3
===================================================
I now want to get it to work out a discount of 1% on the products shown using the tradeprice field. I've tried many times using the SELECT and SUM functions but am not getting the syntax correct. Any help would be very much appreciated.
TIA
Jackmac
How to create a stored procedure, or many stored procedures for generating a monthly sales report in our company.
We have two tables:
ITEM_SALES which consists of:
Item_ID
Name
Store
Sales_Date
Sales_Price
Quantity
And then
ITEM_DISCOUNT which consists of:
Item_ID
Name
Store
Sales_Price
Date_From
Date_To
Explanation: After each month, our different stores will send us a report on which discounts they had. These discounts can vary from one, to many days and therefor we have the Date_From, Date_to.
To make this sales report, i need a procedure that first fetches all sales in ITEM_SALES, then checks all the discounts in ITEM_DISCOUNT and overwrites the ones that have similar Item_ID, Name, and Store for a selected period.
Example: So if a item originally had a sales_price on 99,- and then had a discount sales_price to 79,- for 2014-01-02 to 2014-01-10 it has to be overwritten for that period so the report shows the right numbers.
I have a problem with calculating discount in PowerPivot analysis. I have
dCalendar , dDiscount, fSalesBook .
dDiscount columns:
DiscKey, StartDate, EndDate, Product, DiscountName, DiscountPercent
fSalebook columns:
OrderDate, Product, Qty, Sales, Discount
dCalendar columns:
Date, Holiday, MonthNo, MonthName, QuarterNo, QrtName, Year.
The problem (at least I feel) is I defined some dates in the Startdate and Enddate for a particular year. But these discounts should apply for all of the years in my fSalesBook, not just for that year in the dDiscount table.
So in essence, when you calculate discount for a product in fSalesBook you should consider only the month and day of the transaction, if the sale is in between the discount dates.
I have the following relationships.
Table [Column] ------->Table [Column]
fSalesBook[OrderDate] -----> dCalendar[Date]
I am not sure how to relate the dDiscount table with the dCalendar table and not sure even there is a need to relate them to calculate Row level discount in fSalesBook. You can change the relations/model if there is need be.
PS: One more clause, Sometimes I offer discount only for a particular product in that Discount season, that's why there is a column for Product in the dDiscount table.
What should be my DAX formula to calculate Row level discount in fSalesBook?
Here is the link for the sample workbook.
[URL]
I 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]);
Hi 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
I have a job that runs in sql 2008r2 that declares a value to a variable. This variable is passed to a function. I need to include more values in and am unsure how to do this. @InCo could be 4,5,6,or 7.
See below.
Declare @ReportDateIN [datetime]=GETDATE(),
@InCo varchar(max) = 4,
@OutCo varchar(max) = 8,
@IncludeDetailIN [int]= 1,
@IncludeReleasedIN [int]= 1,
@IncludeHoldingIN [int]= 1
[Code] .....
declare @filename varchar(255),
@path varchar(255),
@sql varchar(8000),
@sql3 varchar(8000),
[Code].....
i need to set a Apostrophe in front of dir and at the end
I am trying to do the following:
DECLARE @MinBoundary DateTime, @s nvarchar(max)
declare @DBName sysname, @TableName sysname
select @DBName = 'MyDB', @TableName = 'MyTable'
select @s = '
SELECT Convert(DateTime, MIN(PRV.value))
[Code] ...
It works if I print @s query and run it this way, so the query is correct):
DECLARE @MinBoundary DateTime
SELECT @MinBoundary = Convert(DateTime, MIN(PRV.value))
FROM sys.partition_functions AS PF
JOIN sys.partition_parameters AS PP
ON PF.function_id = PP.function_id
[Code] ....
I'm going around in circles trying to visualise what's happening in this code,
declare @ReturnValue int, @ProcessFlag int
exec @ReturnValue=spGetProcessFlag @ProcessFlag output
select @ReturnValue as ReturnValue, @ProcessFlag as ProcessFlag
I get the feeling that the code runs spGetProcessFlag, and assigns the value to either @ReturnValue or @ProcessFlag.
By the way - next task is to direct SSIS whether to fail package or continue running.
Is there a difference in the following: DECLARE @T1 VARCHAR(50);
SET @T1 = (SELECT TOP(1) Col1 FROM MyTable); -- define variable first way
SELECT TOP(1) @t1 = Col1 FROM MyTable; --
define variable second wayI did a quick test and not finding a difference, however I was told that if nothing is returned by the query the result would be different and that the first way is better.
I am reviewing some code we have inherited (riddled with multiple nested cursors) and in the process of re-writing some of the code. I came across this and it has me puzzled.
As I understand it, if you declare a variable and then try to re-declare a variable of the same name an error is generated. If I do this inside a While loop this does not seem to be the case. What ever is assigned is kept and just added to (in the case of a table variable)
I understand things are in scope for the batch currently running but I would expect an error to return (example 1 and 2)
--Table var declaration in loop
SET NOCOUNT ON
DECLARE @looper INT = 0
WHILE @looper <= 10
BEGIN
DECLARE @ATable TABLE ( somenumber INT )
[Code] ....
I am using a cursor (i know - but this is actually something that is a procedural loop).
So effectively i have a table of names of stored procedures. I now have a store proc that loops around these procs and runs each one in order.
Now i am thinking i would like to be able to set the table it loops around in a variable at the start - is it possible to do this? So effectively use a tablename in a variable to use in the sql to define a cursor?
The below cursor is giving an error
DECLARE @Table_Name NVARCHAR(MAX) ,
@Field_Name NVARCHAR(MAX) ,
@Document_Type NVARCHAR(MAX)
DECLARE @SOPCursor AS CURSOR;
SET
@SOPCursor = CURSOR FOR
[Code] ....
The @Table_Name variable is declared, If I replace the delete statement (DELETE FROM @Table_Name ) with (PRINT @table_name) it works and print the table names.
Why does the delete statement give an error ?
declare @user varchar(30) = ''
if(@user is not null or @user <> '')
BEGIN
print 'I am not empty'
END
ELSE
BEGIN
print 'I am empty'
ENd
The output should be 'i am empty' but when i execute this i am getting ' i am not empty'. even i did browse through but i don't find the difference in my logic.