I am trying to come up with a SQL statement to determine the following.
I have an OrderDetails table with items for rental. In order to determine the proper billing I have created a second table that is a foreign key to the OrderDetails table that lists the shipping and returns of each line item. the Columns in this table include a ShipActionType, Quantity and ShipActionDate. If the type is equal to 1, the action is the shipping out of an item and if the type is equal to 2, the action is the return of an item.
For example, I might have a line in OrderDetails that says Widget A, Quantity 10.
The shipping Table then might show the following in relation to this line item:
Type Qty Date
1 10 03/07/2008
2 5 03/21/2008
2 3 03/27/2008
2 2 03/29/2008
What i'd like to determine is the total number of billable days this item was out to the customer.
I have tried a variety of DateDiff formulas but have not been bale to nail it just yet.
Any suggestions? Do I need to structure my data a different way?
I have a form with two date fields that the user will submit their requested vacation time off with. When they insert it, I am trying to say find the difference between the request_start_date and request_end_date in days MINUS any of the days they would already have off like weekends or holidays that are included in another table. Everything inserts okay, but I am getting null for the request_duration. If I put dates in quotes and run the query it comes back with the right results. If I put the dates in the form and submit it, I get Null for the request_duration. Thank you in advnace for any help on this! INSERTrequest ( emp_id, request_submit_date, request_start_date, request_end_date, request_duration, request_notes, time_off_id ) Select@emp_id, GETDATE(), @request_start_date, @request_end_date, 1 + DATEDIFF(day, @request_start_date, @request_end_date) - (select count(*) from WeekEndsAndHolidays where DayOfWeekDate between @request_start_date and @request_end_date), @request_notes, @time_off_id
I've gotten sort of fed up with dealing with regional date settings onthe client side and am considering the following scheme - justwondering if anyone has a negative view of it or not:Instead of@StartDate datetime,@EndDate datetimeUse:@StartDaysDiff int,@EndDaysDiff intIn the front end app take the desired date and do a DATEDIFF with thecurrent date, then pass the date diff as a parameter as an integerrather than deal with the dates at all.Then...DECLARE @TodayDate datetime, @StartDate datetime, @EndDate datetimeSELECT @TodayDate = GETDATE()SELECT @StartDate = DATEDIFF(DAY,@StartDaysDiff,@TodayDate)SELECT @EndDate = DATEDIFF(DAY,@EndDaysDiff,@TodayDate)SELECTa.SomethingFROMdbo.Appointments aWHEREa.AppointmentDate BETWEEN @StartDate and @EndDateJust wondering ...Thanks,lq
How can I do this with Parameters? I can get a single parameter to filter for a single date (or even a combo list of the dates in DB). But I want my parameters to interact so that they specify a range. Is this possible?
Today I have got one scenario to calculate the (sum of days difference minus(-) the dates if the same date is appearing both in assgn_dtm and complet_dtm)/* Here goes the table schema and sample data */
IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[temp_tbl]') AND type in (N'U')) DROP TABLE [dbo].[temp_tbl] GO CREATE TABLE [dbo].[temp_tbl]( [tbl_id] [bigint] NULL, [cs_id] [int] NOT NULL, [USERID] [int] NOT NULL,
I have a table that has hotel guests and their start stay date and end stay date, i would like to insert into a new table the original information + add all days in between.
I want to know if there is a way to compare dates in the sql statement with dates that I input into a database and todays date. the datatype that I'm using is smalldatetime.The statement I used is:Select Date from Table where Date > 'Today.now'I get an errorCould this be done or is there another approach?
I am create a database and want to store some value automatically in some field i.e say i have 3 column 1) salary(int),2)tax(int) and 3rd field total salary(float) . i want to automatically fill total salary field as Column1-Column2 while i daont have any Idea How to do that? please help me? any idea or tutorial..example anything...
hi,my users can make posts in my web application, i mean they fill a form and the information they filled will be saved in sql server 2000 and can be shown in web application,now i want to give each post an Id and save it in the database, how can i do that? does sql server have the abilities or i should do sth in my c# application thanx
i wonder what is the best approach to use !!!i have creditLimit column in Customer Table the default value will be 500 and this limit to allow users to send sms from my website ..... every month they will be allowed to send 500 sms referring to the credit limit column.... now !!if the user sent today some sms and after few days sent another 20 and after one week he sent 150 SMS as a total so that means he has only 350 SMS as credit to use this month!!!so what is the best approach to implement this solution ? shall i have another field in the table called 'CreditUsed' and that will be updated each time the user will send SMS and this value will be compared with the credit limit ORi use the formula for the column to calculate the credit left and do the maths !!!what is the best approach as you think ???thnaks for reading this question !!
hi there. I have asked this question before in a different section of the forum without much reply, and its probably because i was asking the wrong people. I need to calculate a qouta for an election. SqlCommand SqlCmd1 = new SqlCommand("SELECT count(vote)FROM PRTest", SqlCon1); int quota = (count(Vote) + 1) / ((11) + 1); My problem is this: how to i get the count(vote) value from the Sql Statement to the formula.
This does nothing:([rush24] + [rush6] + [addLocationsTotal] + 50) This gives me my total correctly:([rush24] + [rush6] + 50) All column are numeric(9) except the "total" column which is numeric(13). Why is this happening?
I want to use the proprity Formula related to a column propreties : Exempel : table XFRS i want to pupulate the field XF2 (int 4) using the value of another Field XF1 (int 4) Which is an identity field. But When i try to insert into the table XFRS then i had an error "Insert faild because the following set options have incorrect settings" 'ARITHABORT'"
I have a quick question. created a report with this formula below. It works when I put a date range from 1-1-2007 to 2-1-2007, but when I put in a date range of 03-01-2008 through 03-31-2008. i get an error message of arithmetic overflow error converting numeric to data type numberic.
So instead of haveing the formula below be (4,2) I put it as (5,2) and now it work. Why is that?
CAST(clm_sppo / clm_tchg * 100 AS decimal(4, 2)) AS PercentSavings
in front end application,iam using pivot table,there is no option to create the growth rate calcuation/formula in FE.
my table data consists like below: country_name Revenue PERIOD_TYPE_OUT Amount_out UK Solutions01 Apr 2007 To 31 Mar 200856.83000000 UK Solutions01 Apr 2006 To 31 Mar 2007116.07000000
while iam using the cross tab in front end application data view is coming as country_name
01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008 solution solution uk 116.07 56.83
Actual o/p should be:
01 Apr 2006 To 31 Mar 2007 01 Apr 2007 To 31 Mar 2008 Growth solution solution uk 116.07 56.83 -0.51
can anybody tell how to calcualate growth in stored procedure level formala for calculating growth rate is : (56.83-116.07)/116.07 ([current year]-[previous year])/[previous year]
i have two tables .Table 1 contains certain columns with values.Table 2 shud get those data from table1 into its column and display the calculated result. Any idea how do i go abt it,bcuz table2 shud contain a formula so as to calculate.
SELECT ID, CalibScoreAVGQA, COUNT(CalibScoreAVGQA) AS Expr1 FROM dbo.TechPhonesCalibScoreAVGQA
Any idea why I'm getting "dbo.TechPhonesCalibScoreAVGQA.ID is invalid in the select list because it is not contained in an aggregate function and there is no GROUP BY clause
Hi,I have three tables in the following structure (simplified):Table 1: Containing the customers-------------------------------------------------create table Customers([cusID] int identity(1, 1) not null,[cusName] varchar(25) not null)Table 2: Containing the customer data fields---------------------------------------------------------------create table Data([datID] int identity(1, 1) not null,[datName] varchar(25) not null,[datFormula] varchar(1500))Table 3: Containing the customer data values-----------------------------------------------------------------create table Values([cusID] int not null,[datID] int not null,[valValue] sql_variant)In this structure the user can add as many data fields to a customer ashe wants (e.g. Country, City, Email, Phone, ...). I have added triggerswhich create a view similar to a pivot (I am working in SQL 2000) andadd triggers to the view so it is insertable, deletable and updateable.What I would like to do, is allow the user to create new fields wherethe values are based upon a calculation. This calculation would be donethrough a formula similar to what he would do e.g. in excel (thisformula is stored in the dimFormula field then).An example might help. Let's assume the user created a field 'Sales'(containing last year's sales) and 'Invoices' (containing the number ofinvoices that were created for him last year). Now, he wants to createa field 'AvgSales' with the formula '[Sales]/[Invoices]'.(Note that through adding these data fields, the above view was created(let's assume it is called vw_Customers and contains the columns [ID],[Name], [Sales], [Invoices], [AvgSales]).What I am looking for is a function which can parse this formula into at_sql query which runs the calculation. So, the formula'[Sales]/[Invoices]' would be translated into (let's assume there areno records with NULL or zero invoices):update vw_Customersset [AvgSales] = [Sales]/[Invoices]from vw_CustomersI am able to do the above with simple calculations (where you can evenuse sql functions e.g. year, len, ...). Now I would like to take thisone step forward into the possibility of using functions with morevariables.For example. Let's assume, the user wants to add a rating (field called'Rating') to his customers based upon the result of 'AvgSales. Heenters the formula 'if([AvgSales] > 2500, 'A', 'B')'.If anyone could help me on this, I would be very grateful. Thanks.M
env: sql server 2000objective:add a formula for an INT column to the FORMULA field or the DEFAULTVALUE field in DESIGN VIEW.all of the following attempts failedIIF (columnName = 0, "1", columnName + 1)IIF (columnName = 0, 1, columnName + 1)IIF (0, "1", columnName + 1)caveat: cannot use IDENTITYREASON: I'd like have sets of repeatable values for this row, e.g.set a1 -- row 12 -- row 2345set b1 -- row 623set c1 -- row 9234Underlying rationale is to support OO design I understand probably it'sgoing to be huge headache for lots of people down the road but ...TIA.
HI,I have a problem in formula column.I have 8 1 bit varibles in a tablefor ex: Flag1, falg2, flag3 ...Flag8Now I want to create another variable as a small integer and copy allthe flgas to that field.For ex:(flag1 << 0x80) | (flag2 << 0x40) | ..... | flag8I tried all possible ways?Let me know how to write the formula for this column.Thanks,Venkat.
Hi,I would like to create a calculated column using the formulasection for a table. I am having some trouble doing this.The table's name is ReportParameter. The calculated column's name istbcalculatedcolumn and tb1 and tb2 are boolean columns in the table.I would like to use an If then statement such as the following (inpsuedo code):If tb1 = 1 then tbcalculatedcolumn = 1Elseif tb2 = 1 then tbcalculatedcolumn = 2EndifThanks for the help,Bill
/ (POWER(PD_Data.dbo.db_drill_hole_base.loaded_diameter, 2) * dbo.Drillability.ROP)) * 0.006894757 AS SPEC_ENERGY_MPa
but my problem is that i have ceros in some records, i was thinking in to use ISNULL but when i tryed to run the query I got error close to 'as'........someone can help me whit this. cheers edwin
I'm really sure it's not some complicated formula (because I managed to recover other fields) . I just want to reverse the formula used to "encrypt" this data. Thank you. Excuse my english.
In the third column I'm trying to stated if the dates are different but when i change the first column to show just the date and not the time it still says they are different. How can i round or change the first column to just reflect the date and not the time so i can compare it to the second column.
Hi everyone, I have a small dilema here. I am running SSRS 2000, and I have a report that is grouped by Part Number. I have =Fields!Formula.Value < .1 so I can view only what is greater than .1 Well, my problem is that when I put it under the group Visibility properties, it does not pull all the data. and if i put it under detail Visibility. It does show me everything but any Part Number that is less than .1 shows just the part number but no detail. How can I make my report show all my data but without leaving data out????
OK, I'm new to Excel 2007 but have used excel for a long time. I have a column of numbers and I'm trying to add them up. I click Sum, I highlight to cells I want to add and hit enter and it comes up with zero. The formula looks fine =SUM(G125:G127) and there are numbers in those cells (that don't add to zero) but it keeps adding them to zero. Can someone tell me why before I put my fist through my monitor?
When I go in and just click on once cell and then hit + and then another and + and another and + and then hit enter, I get #VALUE. So there must be something about how the numbers in the cells are formatted?? They look like numbers to me? Thanks, Peter
101 Martha Steward Corp Martha Steward John Pickle Ray Lettuce
102 Gloria Gardens Gloria Williams Bob Jeans null
103 Judi's Hockey Service Judi McClair John Nolte null
104 Randy Bowling Ball Randy Koolaid Ike Smith Blade McRay
105 Byron Ad Agency Byron Exec Jim Rayburn null
My knowledge level? create a scalar procedure to loop thru Table B using cursor three time to bring back one contact at a time. this just seem inefficient. can I make one pass thru the data.
