SQL Server 2008 :: How To Calculate Charge From Given Input Set
Mar 17, 2015
Work on sql server 2008 r2, need recursively charge amount calculation process.want to write an sp, In my sp I need to calculate head sum base on parameter head and given amount: Picture describe my db input set, from the input set I need to calculate total charge amount on given head,
Input set 1 HeadAmountIsPercentHead PercentGiven AmountCalculated AmountWorking Sequence
Utility10NoTotal400101
Sum10
I have client table which has client_id Eff_from and Eff_to columns.Eff_from and Eff_to are the dates that client is eligible for service. I need to know the average number of days from the day that he became not eligible and new eligibility date .
I've been asked to put together an estimation for the performance impact that replication would have on our database server during a particular operation. I know that this depends on a lot of different factors, including:
* Number of articles being replicated * Types of articles being replicated * Number of DML transactions that would result in delivery of replicated data
Table2: TransactionFormula Only has 2 fields, No & Amount
the formula fields means, we sum the Amount from TransactionFormula table for each No., for example if the formula is [3]+[8] means, we sum the Amount from No=3 added with the Amount from No=8 and we added records on TransactionFormula for row 9
Here's the example result for TransactionFormula records
Our fiscal year starts on July 1st. Each month they call a period - so July is period 1, August is period 2, etc.
They are wanting a report that pulls numbers for a given period. There are parameters for them to select the fiscal year and the fiscal period, and then it calculates the numbers for that period. That part works fine.
Now they want me to do some calculations, and one of them is to divide one of the numbers by the # of days since the fiscal year. So if they choose July, it would be 31 days. If they choose August, it would be 61 days, etc. How can I set this up to calculate the number of days when they really aren't entering a start date, it's just a fiscal year and period.
Is there a way to calculate a date field that is 07/01/xxxx where xxxx is the fiscal year they chose? Also a way to calculate a date field that would be the last date of the month for the fiscal period and year they chose?
I suppose I could add 2 other parameters where they enter the start of the current fiscal year, and the last day of the period they're running it for, and use a datediff to calculate that. Just seems kind of redundant.
I have a challenge and I'm not sure the best route to go. Consider the following dataset.
I have a table of sales. The table has fields for customer number and date of sale. There are 1 - n records for a customer. What I want is a record per customer that has the customer number and the average number of months between purchases. For example, Customer 12345 has made 5 purchases.
One people created a word input file (15 pages, including check boxes, text boxes, drop down lists...). Is it possible to save data in word input file to SQL table?
I'm seeing where previous developers have used a single stored procedure for multiple reports, where each report required different columns to be returned. They are structured like this:
CREATE PROCEDURE dbo.GetSomeData (@rptType INT, @customerID INT) AS BEGIN IF @rptType = 1 BEGIN SELECT LastName, FirstName, MiddleInitial
[Code] ....
As you can see, the output depends on the given report type. I've personally never done this, but that's more because it's the way I learned as opposed to any hard facts to support it.
I have to produce a report to calculate no of days based on user input start date and end date. I have tried to explain below.
say for eg: in the tables I have emp name user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days Total days 53 Days
For this :
I calculated datediff + 1 and got sub jobs days BUT
say financial director wants to see Title of 'Sub Jobs' with 'Days' from 1st Dec to 31st Dec
so on paper I calulated as :
1-31 Dec 2014 PhaniMarketing NULL (Do not fall in Req Dt) PhaniAdmin 20 (Deduct 2 days of Nov & calculated 20 days of Dec) PhaniCRM 11 (Deduct 20 days of Nov and deduct 11 days of Jan so for Dec , we got 11 days) Total days 31
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
I have to produce a report to calculate no of days based on user input start date and end date.
say for eg: in the tables I have emp name user 'Phani' started work from - EStart 20/11/2014EEnd 10/01/2015 - total days --datediff within his work period he did different roles:
PhaniMarketing (prSt Date) 20/11/2014prE date (28/11/2014) Total 9 days PhaniAdmin (prSt Date) 29/11/2014prE date (20/12/2014) Total 22 days PhaniCRM (prSt Date) 20/12/2014prE date (10/01/2015) Total 22 days Total days 53 Days for this :
[code]...
HOW CAN I USE Case statement to calculate days for given start date and end date. I have to include all three totals, 1 for Job dates, 2, subjobs dates, 3 cal of days for a requested period.
I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.
Not really sure if the is the right place to ask... But I was wondering... We have a data warehouse system setup where we host multiple DB's for differant customers. Now currently the way they are charging for the usage is like $0.005 a row for all major tables... IE not the tables we need for processing data for that customer. However we are looking at implimenting Sharepoint Services here and using this SQL Server as the backend SQL Server. Well I have been asked to see if I could come up with a better way of billing for the SQL Usage. Here are some of my thoughts and just wondering what you all do or how you feel about this:
For the Data Warehouse Part when billing by the per row instance someone with a 2 column table and 500 rows will be billed the exact same amount as a 50 column table with 500 rows but yet the 50 column entries would use more space.
Billing for hard drive space utilization could be tricky cause I would either A need to more all my staging type tables to another db for processing or make sure I wipe the staging tables out after each update.
Now I am not sure how Sharepoint works or how it saves data... but can I put each site I have under share point in their own DB? IE Client1 SP site is in the Client1 DB and Client2 SP site is in Client2 DB but both of them are running through the same Sharepoint Instance?
Would be it better to bill a flat rate in number of columns in a table and then number of rows in that table? I am not sure... Any advice you all can give would be greatly appricated.
I have a product where we feed an SQL 7 DB data collected from Manufacturing. Presently, the Data transport Program is in charge of getting prepared data from machines and inserting into the DB. This design assumes SQL7 is always ready and able - which is not true due to customer queries or backups or etc. consuming resources. There is a low level buffer in system at manufacturing level if transport dies, but transport is ignorant of SQL distress, so keeps hammering DB's frontdoor. I'm looking for help in putting SQL server in charge of allowing data in - when resources are adequate. Seems I need a function that can determine server stress QUICKLY to forestall transport program and a buffer for records at the transport layer. Anyone know / done a system where SQL server CHECKS for waiting records or OK's an external program to send until told to stop? What indicates (reliably) low server resources? Anyone ever used MSMQ?
"Black Holes are proof SOMEBODY, SOMEWHERE really did have a particularly bad Y2K problem!"
I am creating matrix report with grouping on WEEK and Fiscalyearweek,I need to calculate of difference between FY14W01,FY15W01 ande  percentage of those..how to calculate in ssrs level.
I'm modifying a report that uses a date parameter as a report filter. The original report had no restrictions on what dates may be entered, so it displayed a neat Calendar picker tool for use in selecting the date for the parameter.
Thing is, this new version needs to have the dates limited to only those available in the source data. So, when I provide a query to describe the available values in the parameter properties window, instead of the nifty Calendar picker, I get a texbox dropdown list. [insert sad sound here]
I was hoping that it would still provide the Calendar picker, but with available dates highlighted in bold or some color or the unavailable dates greyed out, something along those lines; not an unimaginative dropdown list. To define the available values, I use a very simple query;
SELECT DISTINCT Load_Date FROM Census ORDER BY Load_Date
Is there a way to get it to display a Calendar tool rather than the dropdown list, if the parameter is given a list of available dates?
There are a few features in the new SQL Server - Reporting Services that I really need in production. I have tested everything and it works great. I am running the CTP version since Microsoft is saying they aren't releasing the release version until 3rd quarter 2008.
Since Microsoft won't sell SQL 2008 until 3rd quarter, can I run the CTP in production until the release and then purchase SQL 2008?
Hello - does anyone have experience w/SQL Server 2005 in a virtual environment? I'm considering this for a production environment but not sure if performance will suffer. Our databases will have a lot of writing but not too much reading. A SSRS solution is currently the only app. connecting to the SQL db. Max users to server at any given time will be very low (~10 users max). But the databases are pulling in data from other, outside multiple data sources on a daily basis.
Hello! Recently, I set up server with Windows Web Server 2008 RC1, SQL 2008 Express beta, .NET 3.5, IIS 7. I'm running ASP.NET web application with SQL database. Everything works fine until the first application state on the server expires. After that, any postback that starts a new application state on the server and connects to the database, results in the following error: Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed. Is this a bug that will be fixed in release of Windows / SQL or am I doing something wrong? Many thanks for help, Jan
I downloaded the €œMicrosoft SQL Server 2008 Express CTP, February 2008€? from http://www.microsoft.com/downloads/details.aspx?FamilyId=749BD760-F404-4D45-9AC0-D7F1B3ED1053&displaylang=en
I simply replaced the 2005 file €œSQLEXPR.EXE€? with the 2008 file €œ€?, recompiled the installation and tested only for it to fail. I than read the 2008 books online and noted the change in command line options.
I then changed the command line to suit the Microsoft 2008 books online, recompiled the installation and tested only for it to fail once more.
Interestingly I tested the install from the default GUI and at the point of adding the €œsa€? login credentials it fails to allow the installation to proceed. Strangely by selecting the windows authentication credentials, €œnext€? than €œback€? it now allows me to add the €œsa€? login credentials and continues to install correctly as required.
I hope I have explained this clearly enough.
1. Is this a bug in the €œMicrosoft SQL Server 2008 CTP, February 2008€? installation? 2. If so is this causing the command line install options to fail? 3. How do I obtain a version of €œMicrosoft SQL Server 2008 Express€? that will work installing from the command line?
An example of 4 rows on my table would be like this $1400 80 $1500 85 $1560 82 $1700 81
to calculate the average of the price sold related to the number of sold items just have to do Select avg(priceSold*itemsSold)
But sometimes i just want the average price of the first 100 sold items, so how can i make my query to just use the first 100 sold items?
in math it would be like this average= ( (1400*80) + (1500*20) ) / 100
but if i wanted the first 200 it would be like this average= ( (1400*80) + (1500*85) + (1560*35)) / 200
and if i wanted the first 300 would be like this average= ( (1400*80) + (1500*85) + (1560*82) + (1700*53)) / 300
but of course the number i want will always be a variable which is less than the total of the products sold. So, how the heck do i program this query where the number of the items sold is variable and it will take the rows of the database depending on how many items were sold.
I hope i didnt wrote my explanation too confusing and that i can get any help from you guys. thank you a lot for the help and byye
We have SQl Server in the production with dozens databases that reside on a SAN in the RAID 1 configuration with SAS 10K disks. Few databases have a huge overload on the SAN (more writes than reads). As there are some performance issues we are considering new SAN.
Is there any way we can get average IOPs in this server? I tried to use PerMon but without success. I don't know if I can chose "Total" for Logical drives in PerMon for the counters? I read dozens of pages: use sqlio, take this counters, use that, but no straight way to calculate. I know that SAN could have the relevant info for IOPs but our old one doen't provide it.
how can I calculate it (without use these links or use this counters with no calculation procedure in the end)
I have been trying to calculate age and the results either round the age up one year or down one year. I have tried CASE, DATEDIFF, FLOOR functions but nothing works.
My apologies if my question seems rather naive, but I'm looking for some info on how SQL Server (2K) calculates the GETDATE info. Is it from an internal algorithm of some type, or does it simply pull the info from the local server info?
hy, i wrote an input function to put some data in my database with click of button it doesnt work and i cant find the mistake =/ anyone of you can help? now theres one thing that isnt right, and that is that the datasiz of messagetext is set to max, and here i put it in to 50, cause dont know how to put it to max cause you can only put in ant integer , also in the insert into, i did not put all of the columns cause the data i input is only for certain columns, ( don't think thats a problem) Greetz Roy1 2 Private mocon As clsAdocon 3 Dim naam As String 4 Dim type As String 5 Dim folder As String 6 Dim sUDL = ConfigurationManager.ConnectionStrings("masterConnectionString").ConnectionString 7 8 Protected Sub btnopslaan_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnopslaan.Click 9 10 folder = "Out" 11 Select Case True 12 Case rdbMail.Checked 13 type = "Mail" 14 Case rdbFax.Checked 15 type = "Fax" 16 Case rdbSms.Checked 17 type = "Sms" 18 End Select 19 20 If type = "Mail" Then 21 Dim strSql As String 22 strSql = "INSERT INTO Message(ToName, ToEmail, Subject, MessageText, TypeBericht, Folder) VALUES(@ToName,@ToEmail,@Subject,@MessageText,@TypeBericht,@Folder);" 23 24 Try 25 'connectie met database 26 Dim objCn As New SqlConnection(sUDL) 27 Dim objCmd As SqlCommand = objCn.CreateCommand() 28 objCmd.CommandText = strSql 29 30 objCmd.Parameters.Add("@ToName", SqlDbType.NVarChar, 50).Value = naam 31 objCmd.Parameters.Add("@ToEmail", SqlDbType.NVarChar, 50).Value = txtAan.Text 32 objCmd.Parameters.Add("@Subject", SqlDbType.NVarChar, 50).Value = txtOnderwerp.Text 33 objCmd.Parameters.Add("@MessageText", SqlDbType.NVarChar, 50).Value = txtbericht.Text 34 objCmd.Parameters.Add("@Folder", SqlDbType.NChar, 10).Value = folder 35 objCmd.Parameters.Add("@TypeBericht", SqlDbType.NChar, 10).Value = type 36 37 objCn.Open() 38 objCmd.ExecuteNonQuery() 39 objCn.Close() 40 Catch ex As Exception 41 42 End Try 43 End If 44 45 End Sub
I'm trying to connect to a sql database, but I don't know what myserver is in the following code.Dim strConn As String = "server=myserver;database=Northwind"I can't get the code to link up with my Northwind database.I'm running everything locally if that helps.Thanks!Jon
In a sql server 2012 database, I have a field called date of birth that is stored in a tha that is stored in a datetime format. I need to determine the age of various students in the school system. Thus can you show me sql to that I can use to determine the a child is currently?
What's the best way to calculate a customers age and value by month and year?
I need to be able to calculate customer value by month and year, and then to calculate their age at each month in time. I've found a way of grouping sales by month and year that includes age for a particular contact like this:
select fh.contact_number , concat(year(fh.transaction_date), '-', month(fh.transaction_date)) as transaction_month_year , cast(fh.transaction_date as date) as transaction_date , sum(fh.amount) as ttl_amount_in_month
[Code] .....
It seems to work, but any better way to achieve this?
I have a table with appdt as first appointment date and the another record for the same customer# has follow up appointment.
Each customer is uniquely identified by a customer#
I need to find out if the customer came back after 200 days or more when the first appointment date was between jan12014 and Aug 31 2014. I am only interested in first follow up appointment after 30 days or more.
