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
Input Set 2 HeadAmountIsPercentHead PercentGiven AmountCalculated AmountWorking Sequence
Wages 10YesFinancial8001 2
Financial 10YesTotal 10 1
Sum11
[code]....
View 0 Replies
ADVERTISEMENT
Apr 9, 2015
My data is looks like this,
Date---------------------------------------A
2015-03-01 13:38:07.343----------------1
2015-03-01 14:04:04.460----------------1
2015-03-02 19:33:55.117----------------3
2015-03-02 19:33:55.117----------------4
2015-03-02 19:39:26.580----------------1
I want data looks like this
Date-------------------------------------------A
Day 1------------------------------------------2
Day 2------------------------------------------8
View 7 Replies
View Related
Oct 8, 2015
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 .
CLIENT_IDEFF_FREFF_TO
1001 12/24/200712/8/2010
100112/13/20123/26/2013
1001 5/27/20138/2/2013
10019/24/201310/30/2016
for expl days between
12/8/2010 and 12/13/2012
3/26/2013 and 5/27/2013
8/2/2013 and 9/24/2013
then AVG them.
View 3 Replies
View Related
Jun 10, 2015
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
Any way to turn this into a meaningful metric?
View 0 Replies
View Related
Oct 16, 2015
I have 2 tables, one of those table is master formula which has some records like this :
Table1: MasterFormula
NoDescriptionFormula
1Occupancy
2Leased
3Rate Consumed
[Code] ....
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
NoAmount
1100
2100
310
43
510
610
720
810
920[3]+[8]
1020[6]+[8]
1140[7]+[9]
121.2[4]*[11]/100
134.58([11]+{12])/9
1445.78[11]+[12]+[13]
View 5 Replies
View Related
Jul 29, 2015
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.
View 8 Replies
View Related
Oct 7, 2015
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.
CustomerNumber SalesDate
1234 05/15/2010
1234 10/24/2010
1234 02/20/2011
1234 05/02/2012
1234 12/20/2012
What I want to know is the average number of months between the purchases. And do this for each customer.
View 6 Replies
View Related
Jun 4, 2015
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?
View 1 Replies
View Related
Sep 15, 2015
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.
So what I'm looking for is basically 2-fold.
View 5 Replies
View Related
Jan 5, 2015
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.
View 4 Replies
View Related
Jan 5, 2015
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.
View 0 Replies
View Related
Mar 10, 2015
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.
Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'
SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'
As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.
View 3 Replies
View Related
Apr 16, 2008
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.
View 4 Replies
View Related
Feb 6, 2000
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!"
View 7 Replies
View Related
Dec 2, 2014
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.
View 13 Replies
View Related
Jun 25, 2015
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?
View 2 Replies
View Related
Mar 31, 2008
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?
Jim
View 1 Replies
View Related
Apr 23, 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.
Any pointers to documentation or any advice?
Thanks,
A Brown
View 1 Replies
View Related
Feb 25, 2008
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
View 1 Replies
View Related
Mar 7, 2008
Hi all,
New to this so please be patient and please help.
I have developed an SQL 2005 Express command line option install for our company which has been working seamlessly for the last 18 months.
http://msdn2.microsoft.com/en-us/library/ms144259.aspx
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.
http://www.microsoft.com/downloads/details.aspx?familyid=19DB0B42-A5B2-456F-9C5C-F295CDD58D7A&displaylang=en
http://msdn2.microsoft.com/en-us/library/ms144259(SQL.100).aspx
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?
Thanks in advance.
View 1 Replies
View Related
Jun 11, 2007
ok so i have this table:
Code:
Products_Sold
priceSold - money
itemsSold - int
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
View 1 Replies
View Related
Jan 13, 2014
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)
View 7 Replies
View Related
Apr 16, 2015
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.
View 3 Replies
View Related
Mar 8, 2006
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?
Thanks to all.
View 3 Replies
View Related
Sep 14, 2015
I have update schema and I upload image with my desire result.
CREATE TABLE #NONAMETABLE(
sinGroup NVARCHAR(10)
,column1 INT
,column2 int
[Code] .....
View 7 Replies
View Related
Jun 20, 2007
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
View 4 Replies
View Related
Jun 9, 2006
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
View 2 Replies
View Related
Jul 28, 2014
Here is sample data I am working with:
Create table cattimelines (categoryID int, EffectiveDate datetime, CategoryValue varchar(11))
INSERT INTO cattimelines(categoryID, EffectiveDate, CategoryValue) VALUES(1000, '2014-01-01', 'A')
INSERT INTO cattimelines(categoryID, EffectiveDate, CategoryValue) VALUES(1000, '2014-02-01', 'B')
INSERT INTO cattimelines(categoryID, EffectiveDate, CategoryValue) VALUES(1000, '2014-04-01', 'C')
INSERT INTO cattimelines(categoryID, EffectiveDate, CategoryValue) VALUES(1000, '2014-07-01', 'A')
I need to calculates a term date for each record which will be 1 day before the effective date of any new record, thus:
CATEGORYIDEFFECTIVEDATETERMDATECATEGORYVALUE
10002014-01-012014-01-31A
10002014-02-012014-03-21B
10002014-04-012014-06-30C
10002014-07-01NULLA
View 3 Replies
View Related
Aug 31, 2014
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?
View 6 Replies
View Related
Sep 22, 2014
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?
View 1 Replies
View Related
Sep 4, 2015
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.
How can i do that in a query?
View 5 Replies
View Related
Dec 7, 2005
Can some one give me an over view of what I need to do:
Lets say I have a web form with the field: Arrival Date
A web user enters an arrival date of 2/10/06
How do I do a stored procedure that accepts that arrival date and lets me know (by an alert or email) 24 hrs in advance of the arrival date?
View 2 Replies
View Related
Nov 25, 2014
SQL query to calculate the total on-peak and off-values for a month as well as the Max/highest on-peak/offPeak hourly value for that month.
On a daily basis i store the hourly values of the meter in a SQL table.
On-Peak
Summer: Apr-Oct hours(7-22) on weekdays (M-F)
Winter: Nov-Mar hours(8-23) on weekdays (M-F)
off-Peak
Summer: Apr-Oct hours(0-6,23,24); Weekends (Saturday & sunday) ; all public holidays during those months as to be considered as off peak
Winter: Nov-Mar hours(0-7,24);Weekends (Saturday & sunday); all public holidays during those months as to be considered as off peak
Here is the DB Table Structure:
Column Name & Data Types
HourId - Uniqueidentifier
CustomerName - nvarchar(50)
Readingdate - datetime
IntegratedHour - TinyInt
Load - decimal(18,4)
Generation - Decimal (18,4)
LastModified - Datetime
ModifiedBy - nvarchar(50)
View 9 Replies
View Related
