i need to write an sql which calculates # of billing cycles for an
order for which ive order start and end date. the first invoice will
be cut on the order start date. Also if i apply a coupon for lets say
one month (30 days) to that order i need to calculate how many billing
cycles that coupon will apply to.
ive start and end date of coupon ina diff table for that order
for eg: if an order was started on 1/15 and ended on 4/14 i have 3
billing cycles (the invoice was suppose to cut on. 1/15 , 2/15 and
3/15)
lets say on 3/15 if a users applied one month coupon which means he
didnt have to pay for billing cycle 3/15.
I monitor a few "perfmon" counters which includes under the "system" object, "bytes transmitted/sec" and "file read bytes/sec". Every once in awhile, these counters will skyrocket, which can also be verified by the hard drive lights flickering like mad.
The only software installed on the machine is SQL Server 2K.
I was wondering if anyone knew how I could monitor within SQL 2K what process or user is using all of these cycles. If anyone could shed some light on this it would be greatly appreciated. Specifically, I would like to find out which database/query is doing this to minimize in the future as this affects all of the other connections.
Hi, The sqlsrv.exe process has been consuming a high number of cpu cycles and time and seems not be decreasing at all. Can somebody point me in the right direction as to how to identify the bottleneck and resolve the same at the earliest
Greetings; I recieve a monthly phone bill on cd. I am trying to autoimport these CD's into a SQL server database with a DTS in MSSQL 2000. I have noticed on the bills every so often they change the structure of one of the ACCESS DB tables by one row or something small. I am trying to figure out how I can test the structure of the database before trying to do the actual imports, I want to do this so someone else can actually do the imports as we get the bills. Is this a possibility? Or am I going about this all wrong?
Table & Columns: ID field is identity. Currency is either CDN or USD. Order: ID, DateOrder, Currency, AmountOrder Invoice: ID, NumOrder, DateInvoice, AmountInvoice Credit: ID, NumOrder, NumInvoice, DateCredit, AmountCredit Rate: YYYY, MM, US
Relationship among tables: Order & Invoice : on Order.ID = Invoice.NumOrder, one to many Order & Credit: on Order.ID = Credit.NumOrder, one to many Invoice & Credit: on Invoice.ID = Credit.NumInvoice, one to many
Other conditions: For orders of currency USD, the rate of the DateOrder is used to convert the Order.AmountOrder to CDN. The rate of DateInvoice is used to convert the Invoice.AmountInvoice to CDN. A credit always uses the rate of DateInvoice of its associated invoice.
Report: Total billings of USD sales in a period of time (startDate, endDate), converted to CDN
Total billing of USD sales converted to CDN = sum [AmountInvoice * rate of DateInvoice – sum(AmountCredit * rate of DateInvoice)] WHERE the DateInvoice and DateCredit must be in the specified period. AND associated Order.Devise = USD
SQL: 1 SELECT SUM (AmountInvoice*dbo.GetRate(DateInvoice) – SUM (AmountCredit*dbo.GetRate(DateInvoice)) as TotalBilling 2 FROM Order IINNER JOIN Invoice ON Order.ID = Invoice.NumOrder 3 LEFT JOIN Credit ON Invoice.ID = Credit.NumInvoice 4 WHERE Order.Devise = ‘USD’ 5 AND DateInvoice >= startDate AND DateInvoice <= endDate 6 AND DateCredit >= startDate AND DateCredit <= endDate
Actual Results: Only orders which have both invoices & credits. The orders which invoices but no credit are not included in the results.
Hi there.I've been searching for this error specifically but I haven't found anything yet.I have these two tables (USERS and REQUESTS):USERS ( [LOGIN] [varchar] (10) NOT NULL , [NAME] [varchar] (20) NOT NULL)where LOGIN is the primary key.The problem comes when I try to create the "REQUESTS" table.In these requests there's one user who types the request. After one or two days, there's other user who aproves the request. The problem is that I need two foreign keys referencing the table "USERS".CREATE TABLE REQUESTS ([ID] [numeric](5, 0) NOT NULL ,[DATE] [datetime] NOT NULL ,[NOTES] [varchar] (100) NOT NULL ,[TYPED_BY] [varchar] (10) NOT NULL ,[APROVED BY] [varchar] (10) NULL) ON [PRIMARY]GOALTER TABLE REQUESTS ADD CONSTRAINT [PK__REQUESTS__07DE5BCC] PRIMARY KEY ( [ID]) ON [PRIMARY] GOALTER TABLE REQUESTS ADD CONSTRAINT [FK__REQUESTS__TYP__15702E88] FOREIGN KEY ([TYPED_BY]) REFERENCES [USERS] ([LOGIN]) ON UPDATE CASCADE ,CONSTRAINT [FK__REQUESTS__APR__12742E08] FOREIGN KEY ([APROVED_BY]) REFERENCES [USERS] ([LOGIN]) ON UPDATE CASCADEAnd SQL returns:Introducing FOREIGN KEY constraint 'FK__REQUESTS__APR__12742E08' on table 'REQUESTS' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.Could not create constraint. See previous errors.Ok, after that, I tried creating a new table to store aprovals (Table with two fields: "REQUEST_ID" and "APROVED_BY").So, I removed "APROVED_BY" field from "REQUESTS" and its FK constraint.The same error comes up.I don't think this structure goes into "cycles" or "multiple cascades".How can I do this?Thanks in advanceRegardsRoland
I have two tables called a and b. a has one row called aId where aId is the PK. b has bId, aId_1 and aId_2 where bId is the PK and aId_1 and aId_2 both allow nulls.
If I have a relationship between aId and aId_1, and another relationship between aId and aId_2, where I set the delete rule for both to SET_NULL, then I get the error -
"may cause cycles or multiple cascade paths"
But its completely reasonable that I might wish to do this.
The funny thing is that under Visual Studio, I can create a Data Set with these tables and using the designer, I can set the both foreign key relationships to Delete Rule Set Null and everything works as expected.
So, I'm unsure now if I just need to set the Delete rules using the Dataset designer and not bother with them in the database itself.
I have weekly billing cycle. And the billing cycle can start on any day of the week.
Assumption: Start day of week is MONDAY
Hence, the following
DayNumber Day 1 Monday if start day is 1, then billing cycle is Monday - Sunday 2 Tuesday if start day is 2, then billing cycle is Tuesday - Monday 3 Wednesday if start day is 3, then billing cycle is Wednesday - Tuesday 4 Thursday if start day is 4, then billing cycle is Thursday - Wednesday 5 Friday if start day is 5, then billing cycle is Friday - Thursday 6 Saturday if start day is 6, then billing cycle is Saturday - Friday 7 Sunday if start day is 7, then billing cycle is Sunday - Saturday
For a given date, i need find start date and end date of current billing cycle.
For example, if my billing cycle starts on 3rd day of week (wednesday), then for the input 09-Oct-2015, i need to get two output 07-Oct-2015 and 13-Oct-2015
Imagine a 5 column dataset with the following fields - Organiser, Date of Invoice, Total Invoice Value, Reimbursements and Service tax charged. Using the PowerPivot, I want to determine the "Growth in Professional fee billed over the previous period" - please note that previous period need not be previous year because a client may be billed after a gap of 1-2 years as well.To compute growth, I first need to determine the absolute value of Professional fee billed over the previous period. This is where I am getting stuck. Since the billing periods for different clients need not be consecutive, I cannot use the SAMEPERIODLASTYEAR function.
In trying to solve the problem, I tried to frame a calculated field formula but could not do so. Therefore, I tried solving it via a calculated column formula in the PowerPivot window. My idea here was to determine the client wise previous financial year for each row and then use this column in a calculated field formula to get my desired result. I am getting an error when I write this formula (see Billing data tab of PowerPivot window).
hai, iam new to ssrs pease help me below calculations.iam taking the parameters as begin date,Enddate,region,year,site,
My report having outstanding bills information, i need to calculate, here resters are nothing but students, you take any relavent input data.
1)Rosters Outstanding 6-10 Days (Rosters expected, but not received for up to 10 days in the rears), 2)Rosters Outstanding > 30 Days 3)Rosters Current (Rosters expected as of the report end date) 4)Total # Rosters Outstanding (Total rosters expected, but not received) 5)Rosters Queued For Entry Into 3rd Party Systems (Total rosters entered into the SIS System and expected to be entered into, but not already in 3rd party systems) 6)Rosters Entered Into 3rd Party Systems (Total rosters entered into 3rd party systems) 7)Display Total Billable Dollar Amounts Associated With All Outstanding Rosters For All Aging Columns At Bottom Of Report By Daily, Weekly, Monthly, SYTD
