I�m working on SSRS 2005 trying to calculate YTD for the total sale. This is what I got so far:
Code Snippet
WITH MEMBER [Measures].[YTD Amount] AS
'SUM(PeriodsToDate([DATE].[Year]),[Amount])'
SELECT
{[Measures].[Amount],[Measures].[YTD Amount]} ON COLUMNS,
[DATE].[Month].Members ON ROWS
FROM [SKY]
My Date hierarcy is:
Year
Month
When running this query all I get is very small number for Amount column, almost zero for all months and the YTD Amount Column is only showing (null).
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]);
My predicament is - where do I do these calculations - in my vb.net code or in an SQL stored procedure?
My manager has handed me a task of converting an excel file she uses in to a web aplication.
While it has been easy to devise what should be the screens and how to capture data, I am struggling over how to code the calculations.
The calculations in excel are pretty simple. These are just sequential calculations (about a 150 calculation for average 500 rows). Mathametical operations include sum, average, max min - regular excel stuff. Some calculations involve vlookup (equvalent to calculation based on value derived from a reference table).
So I am stil wondering - where do I do these calculations - in my vb.net code or in an SQL stored procedure?
Since these calculations are required a produce a result in an online environment, what will be faster?
I tried to do a proof of concept by creating a sample calculation in a .NET class and an in a stored procedure. The choice is still not clear. SQL code execution time was not bad. But SQL code tended to be very messy.VB.net code seemed to be a little slow. But seemed a more organised to look at.
Any views that you can offer will be very helpful.
I need to calculate the overall GPA for a student in a particular class.
YEAR SCHOOL STUDENT IDENT GRADE TEACHER CLASS GPA 2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 0.0000 2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 1.6700 2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.3300 2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.6700 2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.0000 2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.6700 2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 1.0000
The problem I'm having is that a student may not taken the class for four terms (as in the Elementary Algebra example above). So I can't hard code it to sum the gpa and divide by 4; it needs to be the number of terms the student took the class.
Here's my sql:
select trnscrpt.schyear as [Year], school.schname as School, rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student, trnscrpt.suniq as suniq, stugrp_active.graden as Grade, trnscrpt.teachname as Teacher, trnscrpt.descript as Class, gpamarks.gpavallvl0 AS GPA
from dbo.trnscrpt inner join dbo.stugrp_active on trnscrpt.suniq = stugrp_active.suniq INNER JOIN school ON stugrp_active.schoolc = school.schoolc INNER JOIN gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark
where trnscrpt.graden >= 6 and trnscrpt.markawd1 not in ('NC','NG','P','W','WA','WF','WI','WP') and trnscrpt.subjectc in ('LA', 'MA', 'CP', 'CB') and trnscrpt.schyear = 2007 and stugrp_active.schoolc = 725
In period = 2, status code change from InStock to OutOfStock: Product 1 (Count=1) In period = 1, number of products with status code = InStock: product 1 and product 2 (Count=2)
sector RefDate price pharm 22 august 2007 100.21 gap 15 august 2007 10.32 pharm 21 august 2007 99.99 pharm 9 oct 2007 100.99 pharm 2 oct 2007 98.34 pharm 8 oct 2007 96.34 ...
I would like to have the result as follows: sector RefDate price priceChangeSinceYesterday priceChangeSinceLastWeek priceChangeSinceLastMonth pharm 9 oct 2007 100.99 100.99-96.34 100.99-98.34 100.99-lastmonth's price value
select sector, RefDate, price, priceChangeSinceYesterday??, priceChangeSinceLastWeek???, priceChangeSinceLastMonth?? from table1
My aim is to do something like what I have explained below and I was planning on building this logic at the Database level only rather than on the frontend code.
There are certain allocations(transactions) that happen on a periodic basis and I am storing these transactions in the PurchaseTranMaster and PurchaseTranDetail table. These transactions are categorized as 'Main' type and the amount could be allocated for one or many categories in a single transaction. Below is how it will be saved in the 2 table
PurchaseTranMaster
TranID TranDate TranType 1 14-March-2008 Main 2 17-March-2008 Main 3 1 9-March-2008 Main
PurchaseTranDetail
TranID Amount Category Debit_TranId 1 1000 A 1 1000 B 2 2000 B 3 300 A 3 400 C
Now what happens is users of my application can make purchases under all these categories only until the Balance under these categories is > than purchase amount. The Balance is calculated as sum of all transactions. It means that w.r.t the above data the balances for each category is(this is not stored in the database)
A 1300 B 3000 C 400
So lets say a user does make a purchase(Trantype is 'SUB') of 300 under A and 400 under B in a single transaction. The data would then be stored in the tables as
PurchaseTranMaster
TranID TranDate TranType 1 14-March-2008 Main 2 17-March-2008 Main 3 19-March-2008 Main 4 20-March-2008 SUB
PurchaseTranDetail
TranID Amount Category Debit_TranId 1 1000 A 1 1000 B 2 2000 B 3 300 A 3 400 C 4 300 A 1 4 400 B 1
In the PurchaseTranDetail the Debit_TranId value means that the amount has been marked against the TranID 1. This TranId is not handpicked by the user and the system should allocate it accordingly based on the amount available for a particualar category for a Main Transaction. It means that before TranId 4 was saved in the database then the system would first check whether the Total available balance for A >=300 and B>=400 (in our example above it is 1300 and 3000 resp) Then if the Balance is > than the puchase amount then the allocation would be done by the system and this would be done against the TranID whose TranDate was the earliest, so thats why the Debit_TranId column has 1 as TranId 1 was the earliest.so logically now the balance for the categories would be (this is not saved in the database)
A 1000 B 2600 C 400
So next time again when a user would make a purchase(transaction) under A for 800 and under B for 1000 then if the balance is greater than the purchase amount(which in this case it is) the allocation would happen according to the earliest TranId and this time amount would be partly marked against TranId 1 , TranID 2 and TranID 3. The data would look like this
PurchaseTranMaster
TranID TranDate TranType 1 14-March-2008 Main 2 17-March-2008 Main 3 19-March-2008 Main 4 20-March-2008 SUB 5 21-March-2008 SUB
TranID Amount Category Debit_TranId 1 1000 A 1 1000 B 2 2000 B 3 300 A 3 400 C 4 300 A 1 4 400 B 1 5 700 A 1 5 100 A 3 5 600 B 1 5 400 B 2
I need to do the above taking into consideration that there could be multiple users making purchases(concurrency). Also I was building my logic on doing the above whether to use cursors or loops. I just need to know how do I write my stored procedure and what would be the most efficeint way of doing the above.
The design for creating the above sample tables is below
insert into PurchaseTranMaster values(convert(datetime,' 14-March-2008',103),'Main') insert into PurchaseTranMaster values(convert(datetime,' 17-March-2008',103),'Main') insert into PurchaseTranMaster values(convert(datetime,' 19-March-2008',103),'Main') insert into PurchaseTranMaster values(convert(datetime,' 20-March-2008',103),'SUB') insert into PurchaseTranMaster values(convert(datetime,' 21-March-2008',103),'SUB')
insert into PurchaseTranDetail values(1,1000,'A',0) insert into PurchaseTranDetail values(1,1000,'B',0) insert into PurchaseTranDetail values(2,2000,'B',0) insert into PurchaseTranDetail values(3,300,'A',0) insert into PurchaseTranDetail values(3,400,'C',0) insert into PurchaseTranDetail values(4,300,'A',1) insert into PurchaseTranDetail values(4,400,'B',1) insert into PurchaseTranDetail values(5,700,'A',1) insert into PurchaseTranDetail values(5,100,'A',3) insert into PurchaseTranDetail values(5,600,'B',1) insert into PurchaseTranDetail values(5,400,'B',2)*/
Hello Friends I have 3 tables 1) Product Id, ShortName 2) IncomingStockId, ProductId, Quantity, InDate 3) OutGoingStock Id, OutDate, ProductId, Quantity I need to get the results like thisProduct name, quantity in stock today the "quantity in stock today" = sum (quantity recieved) -sum (quantity sent) Thank you for your timeSara
Hi. i have the code : cmd = New SqlCommand("SELECT sales,country,year FROM salesTable WHERE (country = " & (CountryBlk) & " AND branch = " & (NameSnif) & " AND datepart(yyyy,year)=" & (YearBlk) & ") order by datepart(mm,year) ", cnn) cnn.Open() rdr = cmd.ExecuteReader( _ CommandBehavior.CloseConnection) While x < 12 If rdr.Read = Nothing Then Exit While End If varcount(x) = rdr("sales") TempMonth = rdr("year") MonthNumber(x)=datepart(DateInterval.Month,TempMonth) x = x + 1 End While
i want to calculate and put into the varcount(x) value all sales of the same month
I need to create a user defined function to calculation the difference between today and a future date. The result needs to be in days, hours, and minutes formatted as per the following example: 1d / 4h / 30m. I have a moderate level of SQL exprience. however, I would appreciate some expert advice as the best way to approach this.
hello everyone, I have a problem of calculating a date, for example, how do i find out the begining date of the week and ending date of the week for certain date, and how do i find out the beginning date of the month and end date of the month for a certain date, thanks
Hi All! I need a query to find all dates from today to one-year back. If I start from today day I need find all dates until 11/12/98. Thanks a lot. Greg.
I have scenerio that find out the Bandwitdh size between clint and server. I wanted find out howmuch size of data recieveing from server at a time. Any advice regarding this. Thanks, Ravi
HI, getdate() gives me today's date. I have a sql query which returns me a date. I want to see if the difference between today's date and the date returned by sql query is 12 months(1 year) or less. If yes I want to print it. (If the difference is more than 12 months or 1 year i don't want to consider that record.)
Example: how to do calculate: 10/05/2004-10/3/2003
how do i calculate something like this if I have the table with names and count? Name Count Percent Name1 27 4.69% Name2 2 0.35% .... Totals 576 100.00%
I am performing a calculation in both SQL and MSACCESS. ACCESS is correct and SQL is not. It is off by -.05 Here is the calculation:
Sum((ch.FaceAmount)-(((ch.FaceAmount*Ex.CAN_Exchange_Rate+0.005)*100)/100)) AS TransAmount,
Both FaceAmount and CAN_Exchange_Rate are defined as money in a SQL table. The ACCESS front-end attaches this table, so it uses the same columns in the calculation.
Now, I know SQL looks at money with 4 decimal places. The attached SQL tables in ACCESS looks at these columns as currency which I believe is 2 decimal places. I think this is where the descrepency is. But it still doesn't explain why SQL is calculating the incorrect total.
In the following calculation I am receiving my output as 000000.9000. I need it to look like 00000000.90. I cant seem to loose the 2 zeros at the end. Can anyone help me with this? Keep in mind that my output has to be varchar.
right('00000000000' + cast((st.rate_dlr * .01) * 10 as varchar),11)
Hi: I am having a problem with the Syntax. I am trying to get the daily value for a contract_nbr. The selection below shows that I am selecting by Case statement when day = 2 (2nd Day) and the month is a parameter provider by the user. For this particular example, I am using 2 (February). Thanks for the help !!!!
[code] Set @Req_Month = '2'
SELECT Distinct a.contract_nbr, Case when Day(c.Beg_eff_date) = 2 and month(c.Beg_eff_date)= @Req_Month Then c.rcpt_nom_vol-c.rcpt_fuel-rcpt_act_vol Else 0 End As Day_2 from TIES_Gathering.dbo.contract a Inner Join TIES_Gathering.dbo.NOm b on a.contract_nbr = b.contract_nbr Inner Join TIES_Gathering.dbo.Nom_vol_detail c on c.Nom_id = b.Nom_id where (a.contract_sub_type = 'INT') and (a.Contract_type_code ='GTH') and (DATEADD(mm, DATEDIFF(mm,0,getdate()), 0) < a.current_expirtn_date) and (c.rcpt_dlvry_ind ='R') Group by a.contract_nbr [code]
Hello experts! After surfing this forum and reading many articles in the web I still haven't succeeded with a certain calculation that I need to perform. Thus I wanted to address you to ask if you could help me on this one:
I have 3 tables in a SQL Server 2005 DB and want to carry out the following calaculation:
[sum of lmenge/lmenge]* [1/werksmassFM]*p_transpreis
However, whatever I attempt to do, it seems to fail! I am quite a newbe and was never focussed with such comprehensive statements before. I would appreciate any help!
My statement looks like this so far (without the calculation):
SELECT l.bestellnr, l.lfsnr, l.ziel, l.lfs_refnr, l.lfsGuid, CONVERT(NUMERIC(9, 3), l.werksmassFM) AS werksmassFM, ls.lmenge, ls.pguid_ref, p.punktName, p.holzart, CONVERT(NUMERIC(9, 2), p.laenge) AS laenge, CONVERT(NUMERIC(9, 2), p.p_transpreis) AS p_transpreis FROM dbo.lfs AS l INNER JOIN dbo.lfs_sub AS ls ON l.lfsGuid = ls.lfsGuid_ref INNER JOIN dbo.polter AS p ON ls.pguid_ref = p.pguid WHERE (l.lfsnr LIKE '%1253')
i am trying to calculate the time that is spend by user on the internet per site. the file that we receive from the WebProxLog contains the following fields ClientIP, ClientUname,logdate,logTime, ProcessingTime, DestinationHost,DestinationIP. I want to check per user per site how much time the user spend per date. Now my challenge is that if the user connects to the side today and logout and reconnects again to the same side how do i calculate the time spend on the site because the file does not show the logout time
Hi .I just got a serious problem.the problem is I have a table with to field as money and as real.so I try to have expresion like * but I get uncorrect answer . for eaxample if is 1805957.1429 and is 110 the answer will be 1.986553E+08 and I try to convert to meney it comes to 198655280.00 what should I do
Can someone please tell me how I can take a set of sales orders and generate a table or set of data that show the percentage of sales per product which are in different sales ranges
Hi I have got two tables and have one to many relationship on them. i ll show you the sample data
Table 1 ID DoctorName Practice Full_Time Cost Expenditure 1 ABC qw yes $100 2 ABD wer no $566 3 ZXA ddf yes $22 ........................
table 2 Practices qw wer ffgg hhjk ddf .. The scenario is like this a doctor can work in more than one practice. and he can be a full time practioner or a part time practitioner
The problem is if a doctor is a full time practioner in 1 service and a part time practitioner in another, 75% of cost goes to first practice and the rest 25% goes to other
if the doctor is non full time in more than one practice, the cost gets distributed equally in all practices.
I have a problem that I can't find a good solution for. The problem is I have a set of data that shows the monthly hours that staff work or leave taken. Below is the sample data:
What I have done is added variables for leave where 'Type' = leave and for worked hours where 'Type' = project. I have also added another column that derives that default work hour for the particular month. Now the problem is when I want to do a calculation for the utilization of a staff with the calculation: (hours worked / (monthly work hour - leave) ) * 100. However the value is way off as the monthly work hours in example 100 hours will be repeated for every line of record. Can anyone help suggest a better way of doing it. I am displaying it in a list table with grouping on 'Name'
ex: Correct - 65 / (100-23)*100 = 84.4% What I get - 65 / (1100-23)*100 = 6%
Hello, I have a problem I wish to share and get help if possible. I am trying to create a database function that will calculate working hours lost between two dates for every employee, however, I am new to database functions and I cannot find information on how to solve my rather "unique" problem. The dates for calculations are based on employee absences for a given period of time. The tables I have and fields of interest are as follows: I have two tables T.Abs and T.Job where T.Abs contains absence records while T.Job contains job records. The tables are in a star schema with T.Job being the main fact table. the tables have the following fields (keys are underlined) T.Abs T.Job EMPLID EMPLID EMP_RCD EMP_RCD EFFSEQ EFFSEQ EFFDT EFFDT ABS_START (7 other columns as follows) MON_HRS, TUE_HRS, WED_HRS .....up to....... SUN_HRS ABS_END
Each time a report is generated for an absence, two prompts come up to define the report period. @prompt(begin_date) and @prompt(end_date) The actual period to calculate hours lost is determined as follows:
Report start period If ABS_START BETWEEN @prompt(begin_date) AND @prompt(end_date) THEN ABS_START ElseIf ABS_START < @prompt(begin_date) AND ABS_END BETWEEN @prompt(begin_date) AND @prompt(end_date) THEN @prompt(begin_date) ElseIf ABS_START < @prompt(begin_date) AND ABS_END > @prompt(end_date) THEN (If ABS_START < @prompt(begin_date) THEN @prompt(begin_date) ELSE END If) ELSE END If
Report End period If ABS_END BETWEEN @prompt(begin_date) AND @prompt(end_date) THEN ABS_END ElseIf ABS_END > @prompt(end_date) AND ABS_START BETWEEN @prompt(begin_date) AND @prompt(end_date) THEN @prompt(end_date) ElseIf ABS_START < @prompt(begin_date) AND ABS_END > @prompt(end_date) THEN ( If ABS_END > @prompt(end_date) THEN @prompt(end_date) ELSE END If) ELSE END If
Given the above parameters, T.Job.EFFDT can fall outside the desired range but the calculation for the working Hours lost should not be affected by this. I am new to SQL in general therefore suggestive code would be more than welcome.
Another thing to bear in mind, I am dealing with a transactional database therefore I have to use max(T.Job.EFFDT) to avoid any duplications and max(T.Job.EFFSEQ) for the same reasons.
