I want to calculate the Total_Cost and get the result of the formula from SELECT Query. Following is the formula of Total_Cost. I have declared variables for formula. How I can create SELECT/RUN query successfully for mathematical calculation to calculate the Total_Cost.
--Formula of Total_Cost = [min (price, Pay1) x Interest1 + max (min (price - Pay1, Pay2), 0) x Interest2 + Max ((price - Pay2), 0) x Interest3, LMT]
declare @price numeric(18,7)=255550
declare @Pay1 numeric(18,7)=645500
declare @Pay2 numeric(18,7)=235000
declare @Interest1 numeric(18,7)=0.05500
declare @Interest2 numeric(18,7)=0.03533
declare @Interest3 numeric(18,7)=1.00000
declare @LMT numeric(18,7)=10000.00
Select [min (@price, @Pay1) x @Interest1 + max (min (@price - @Pay1, @Pay2), 0) x @Interest2 + Max ((@price - @Pay2), 0) x @Interest3, LMT] as Total_Cost
How I can get the desired result using query. I don't want any
'@' variable in the result. DECLARE @ST AS varchar(10)='AA' DECLARE @desc AS int=8 DECLARE @STID AS int=4 DECLARE @PP AS int=63 DECLARE @SS AS int=22 /* Desired Result */ Update #RT Set ST='AA', desc=8, STID=4 Where PP=63 and SS=22
This is my syntax, and if I print the value that is stored in each variable @goodtries = 120 @badtries = 25 but when I run the syntax below it gives me 0.00
Declare @goodtries as int, @badtries as int select @goodtries = convert(decimal(18,4),count(userID)) from table1 WHERE logintype IN ('Valid', 'Success') select @badtries = convert(decimal(18,4),count(userID))
i am using visual web developer 2005 and SQL 2005 with VB as the code behindi am using INSERT command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString1").ToString() test.InsertCommandType = SqlDataSourceCommandType.Text test.InsertCommand = "INSERT INTO try (roll,name, age, email) VALUES (@roll,@name, @age, @email) " test.InsertParameters.Add("roll", TextBox1.Text) test.InsertParameters.Add("name", TextBox2.Text) test.InsertParameters.Add("age", TextBox3.Text) test.InsertParameters.Add("email", TextBox4.Text) test.Insert() i am using UPDATE command like this Dim test As New SqlDataSource() test.ConnectionString = ConfigurationManager.ConnectionStrings("DatabaseConnectionString").ToString() test.UpdateCommandType = SqlDataSourceCommandType.Text test.UpdateCommand = "UPDATE try SET name = '" + myname + "' , age = '" + myage + "' , email = '" + myemail + "' WHERE roll 123 " test.Update()but i have to use the SELECT command like this which is completely different from INSERT and UPDATE commands Dim tblData As New Data.DataTable() Dim conn As New Data.SqlClient.SqlConnection("Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Database.mdf;Integrated Security=True;User Instance=True") Dim Command As New Data.SqlClient.SqlCommand("SELECT * FROM try WHERE age = '100' ", conn) Dim da As New Data.SqlClient.SqlDataAdapter(Command) da.Fill(tblData) conn.Close() TextBox4.Text = tblData.Rows(1).Item("name").ToString() TextBox5.Text = tblData.Rows(1).Item("age").ToString() TextBox6.Text = tblData.Rows(1).Item("email").ToString() for INSERT and UPDATE commands defining the command,commandtype and connectionstring is samebut for the SELECT command it is completely different. why ?can i define the command,commandtype and connectionstring for SELECT command similar to INSERT and UPDATE ?if its possible how to do ?please help me
I need to run a select on Mondays to pull data for 7 days prior to the Thursday of last week; i.e. Friday - Thursday inclusive. I'm sure this is simple, but I work with dates so infrequently that I need a refressher.
This is on SQL Server 2008. Please find a detailed description and the file of the data, that I am working on.
Requirements:
1. If 'Channel' is not equal to "Omnibus" where the 'Trans Description'is equal to "Purchase" and "Redemption" for one purchase and one redemption that match on 'System' , 'Account TA Number' , 'Product Name' , 'Settled Date' , and where the 'Trade Amount' of the purchase and redemption is within 5%, then display those set of records.
2. If deemed wash trades, allow user to update the purchase and redemption pair 'Trans Description' from "Purchase" to "Exchange In" and 'Trans Description' from "Redemption" with "Exchange out"
System Channel Dealer Name Firm Name Product Cusip Product Name Product Share Class Trade ID Settled Date Account TA Number Trans Description Trade Amount
SCHWABPORTAL US - ASG MILLIMAN MILLIMAN 64128K777 Strategic Income Fund A 29806259 30-Jan-15 000BY00F2RW Redemption $ 25,68,458.15
I am trying to compare Sales value of year 2015 with sales value of 2016 and the difference stored in alias column as Sales_growth for year 2016 , for year 2015 the alias column be as '1' similarly difference between margin of 2015 and 2016 and result stored in alias column as margin_rate in year 2016for 2015 as 1 but when there is no record for year 2015 and record present in 2016 for a given (month, SM,SG,CUST,SP) then the alias column sales_growth and margin_rate should be 100
last record : as there is no record for year 2015 and record present in 2016 for a given (month, SM,SG,CUST,SP) then the alias column sales_growth and margin_rate should be 100
In t-sql 2012, I have the following sql that I would like the following to occur:
1. commit or rollback a transaction based upon the results of a calculation listed below, 2. I would like to have a message appear if the commit was successful or the rollback needed to occur. I basically want a way to be able to tell from messages if a rollback occurred or a commit happened.
DECLARE @TransactionName varchar(20) = 'Transaction1'; @STARTLOCKERCNT INT = 0, @LOCKDIFCNT INT = 0, @ENDLOCKERCNT INT = 0 DECLARE @lockmap TABLE (lockID int NOT NULL PRIMARY KEY, schoolID int NOT NULL, UNIQUE(schoolID,lockID) )
[Code] ....
Thus can you modify the sql I just listed above so that I meet the goals that I just listed above?
I am trying to do a calculation where I show the [YesEmail] / [TotalCustomers]. It wont let me do it unless i do two queries. Is there a way to put this all in this select statment.
Code:
SELECT customer_lifetime_totals.occurrence_1_store_no as [StoreNo], COUNT( case when customer.email_address IS NULL then 1 end) as [NoEmail], COUNT( case when customer.email_address IS NOT NULL then 1 end) as [YesEmail], COUNT(customer.customer_no) AS [TotalNewCustomers]
FROM customer_lifetime_totals INNER JOIN customer ON customer_lifetime_totals.customer_id = customer.customer_id WHERE (occurrence_1_transaction_date > CONVERT(DATETIME, '2006-08-01 00:00:00', 102)) GROUP BY customer_lifetime_totals.occurrence_1_store_no ORDER BY customer_lifetime_totals.occurrence_1_store_no
This works ... but only when i put the previous query in a temp table and then run another query referring to it.
Code:
cast(100*(cast(YesEmail as float) / TotalNewCustomers) as numeric (5,2)) as [Percent]
I just read this article. The kind of select is called dirty read. So select with nolock might have inaccurate result...? PLEASE COMMENT ON THIS. I am using it to count some huge tables, and has problem on the result..
NOLOCK Using NOLOCK politely asks SQL Server to ignore locks and read directly from the tables. This means you completely circumvent the lock system, which is a major performance and scalability improvement. However, you also completely circumvent the lock system, which means your code is living dangerously. You might read the not-necessarily-valid uncommitted modifications of a running transaction. This is a calculated risk.
For financial code and denormalized aggregates (those little counters of related data that you stash away and try desperately to keep accurate), you should play it safe and not use this technique.
Now each table holds different measures e.g. 326, 229 & 278. I would like to subtract the measure_value of 229 from 278 and then add the measure_value from 278 from this. e.g
(326-229) + 278 = new measure_value.
This is for each id-product where the following 3 conditions meet:
the ID_PRODUCT, ID_MARKET AND ID_BUCKET match. Im lost, any help would be great.
thanks
Jay
p.s this what i have started with at the moment as a test however it dont work.
SELECT ID_PRODUCT, ID_MARKET, ID_BUCKET, ID_COLLECTION, ID_MEASURE, MEASURE_VALUE FROM (SELECT TOP 100 PERCENT dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT, dbo.DPOUT_EXCEPTIONS_326.ID_MARKET, dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET, dbo.DPOUT_EXCEPTIONS_326.ID_COLLECTION, dbo.DPOUT_EXCEPTIONS_326.ID_MEASURE, dbo.DPOUT_EXCEPTIONS_326.MEASURE_VALUE + DBO.DPOUT_LSBP_229.MEASURE_VALUE as 'MEASURE_VALUE' FROM dbo.DPOUT_EXCEPTIONS_326 INNER JOIN dbo.dpout_lsbp_229 on dbo.dpout_exceptions_326.id_product = dbo.dpout_lsbp_229.id_product where dbo.DPOUT_EXCEPTIONS_326.ID_PRODUCT = dbo.dpout_lsbp_229.id_product and dbo.DPOUT_EXCEPTIONS_326.ID_MARKET = DBO.DPOUT_LSBP_229.ID_MARKET and dbo.DPOUT_EXCEPTIONS_326.ID_BUCKET = DBO.DPOUT_LSBP_229.ID_BUCKET)
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]);
The select command below will output one patient’s information in 1 row:
Patient id Last name First name Address 1 OP Coverage Plan 1 OP Policy # 1 OP Coverage Plan 2
[code]...
This works great if there is at least one OP coverage. There are 3 tables in which to get information which are the patient table, the coverage table, and the coverage history table. The coverage table links to the patient table via pat_id and it tells me the patient's coverage plan and in which priority to bill. The coverage history table links to the patient and coverage table via patient id and coverage plan and it gives me the effective date.
select src.pat_id, lname, fname, addr1, max(case when rn = 1 then src.coverage_plan_ end) as OP_Coverage1, max(case when rn = 1 then src.policy_id end) as OP_Policy1,
If I run the following command in a Query window it works:
RESTORE DATABASE CIS_Source_Data_Test FROM DISK = 'y:CIS_Source_Data_backup_2015_06_17_085557_7782407.bak' WITH RECOVERY, REPLACE
If I dynamically put together the command and store it in variable @cmd and then execute it using exec sp_executesql @cmd or exec (@cmd) it does not work. I get the following:
Msg 2745, Level 16, State 2, Procedure CIS_Source_Data_Refresh, Line 92 Process ID 62 has raised user error 50000, severity 20. SQL Server is terminating this process. Msg 50000, Level 20, State 1, Procedure CIS_Source_Data_Refresh, Line 92 RESTORE DATABASE is terminating abnormally. Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
Why it won't work when I try to create and run it dynamically?
I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:
Insert into #temp Exec(@sqlcommand)
For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.
I'm upgrading to SQL 2012 from 2008R2, while doing so i will be rebuilding all the indexes on all the database. In my previous environment while doing so, i got space related error in primary filegroup for insufficient space in the primary filegroup. Is there any rule of thumb about how much space is required by index rebuild command for each database, or is there a safe threshold for free space in the database?
select computer, count(*) as MissedCount from WInUpdates_Neededreq WHERE LoggedDate BETWEEN DATEADD (DAY, - 5, GETDATE()) AND GETDATE() and LastReportTime !< DATEADD (DAY, -5, GETDATE()) group by computer
I need to make a join onto another table but don't want to lose the coutn(*) as MissedCount.
How can I join to another table and still keep the count form the original table. I want ot join to tblogons.workstationname and return computer from the original query...
How to launch Oracle SQL Developer through "Run" window in windows as we can launch SQL Server Management studio through "Run" command line using command line "sqlwb -S localhost -d MyDB -U sa -P
I'm using the following Select Command:SELECT MAX(Document) AS DOC FROM dbo.Communicator WHERE (ReleaseDate <= { fn NOW() })It shows the most current date in the ReleaseDate column - even if the date is in the future. I don't want it to show future dates. If I change the command to WHERE (ReleaseDate >= { fn NOW() }) it doesn't work at all. I only want it to return one row - the latest releases date that is equal to or less than now.Any ideas?
I have a web form that has textbox1, textbox2 and DropDownList1. Let’s say textbox one is first name, textbox2 is last name and dropdownlist1 is age. How do I write a query that will select from database table dbo.emoployee where last name = dbo.employee.lastname and all other fields are blank, I want it to return all employees with that last name. If someone types in the last name and selects the age from the drop down list then I want to return all employees where last name = dbo.employee.lastname and age = dbo.employee.age. If someone types in just the first name then I want to return all employees where first name = pub.employees.firstname? I have been trying to do this using the SQLDatasource but cannot seem to figure it out.
I have a web form that has textbox1, textbox2 and DropDownList1. Let’s say textbox one is first name, textbox2 is last name and dropdownlist1 is age. How do I write a query that will select from database table dbo.emoployee where last name = dbo.employee.lastname and all other fields are blank, I want it to return all employees with that last name. If someone types in the last name and selects the age from the drop down list then I want to return all employees where last name = dbo.employee.lastname and age = dbo.employee.age. If someone types in just the first name then I want to return all employees where first name = pub.employees.firstname? I have been trying to do this using the SQLDatasource but cannot seem to figure it out.
I currently have a webpage that allows visitors to post links to their own website. As a spam filter I want to create a scheduled task that selects repeat entries in the database under the column name domain. I already made a filter to take everything out of the link the provide and leave it with just the domain name. I tested it and it works. Now I need a SQL command to select all the rows with repeats of the domain. Look at the following table example to better understand what I mean uid x y domain1 100 110 www.spam.com2 100 120 www.spam.com3 110 130 www.homepage.com4 210 220 www.myaspspam.com5 510 560 www.myaspspam.com in this example I would like 1 and 2 to be selected as well as 4 and 5 into a dataadapter so that I can delete them accordingly.
im trying to write a select command that gets info from 1 table and counts 11 differnt things in it im not sure if this is even posiable but if it is could someone help this is what i got for counting all of them SELECT owner, COUNT(*) AS TotalPots FROM Items WHERE (Name = 'Holy Potion') OR (Name = N'Arcane Potion') OR (Name = N'Shadow Potion') OR (Name = N'Fire Potion') OR (Name = N'Kinetic Potion') OR (Name = N'Potion of Holy Resistance') OR (Name = N'Potion of Arcane Resistance') OR (Name = N'Potion of Shodow Resistance') OR (Name = N'Potion of Fire Resistance') OR (Name = N'Potion of Kinetic Resistance') GROUP BY owner ORDER BY COUNT(*) DESC the 11 coloums i want are Holy, Arcane, Shadow, Fire, Kinetic, Holy Resist, Arcane Resist, Shadow resist, Fire Resist, Kinetic Resist, And Total Pots Also would like it on my Asp.net page at the bottom of the grid view to have a total row that counts all the colums up
I am trying to run a select command on an OLE DB Command transform such as:
Select * from table where id = ?
I have the mapping to the parameter working and the command is working but how to map the select output to columns when you can't create output columns on the OLE DB Command?
Thanks for this help. My problem is a little different. I have a stored procedure with an output parameter that I want to use in an OLE DB transform. I can see how to add output columns but can't figure out how to set them from the stored procedure return. Any ideas?
I am using <asp:SqlDataSource ID and for the Select Command, the following, where the WHERE clause ... for an exact match (=) works correctly: SelectCommand="SELECT [PatientID], [MedRecord] , [Accession], [FirstName], [LastName], [Address1] FROM [ClinicalPatient] WHERE (LastName = @LastName) ORDER BY [LastName]DESC"> I would like to do a "LIKE" search where the LastName Parameter is matched using "LIKE". In this situation how would the syntax be written.... I tried: LastName LIKE '%" & LastName & "%'" But I get an error???? Any suggestions, please... Thanks !!
