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 facing a problem that following query give me different result whenever I execute the query, it returns me same number of records but not all the rows are same everytime it is executed.
Select[Field1] From ( SelectRow_number() Over(Partition By [Field2], [Field3] Order By [Field2], [Field3], [Field4] Desc) ID, * From[dbo].[Table1] ) A WhereID > 1 OrderBy [Field1]
Those highlighted in yellow colours are duplicate records, I need to remove one of them.
I have a query that returns material(items) that are used in an event on a certain day.
SELECT C.categoryName, count(I.itemID) AS InMission from items as I RIGHT JOIN Categories AS C on I.categoryID = C.categoryID INNER JOIN LinkMissionItem as LM on I.itemID = LM.itemID INNER JOIN Missions as M on LM.missionID = M.MissionID where '2015/12/19' BETWEEN M.freightLeave and M.freightReturn AND isReturned = 0 GROUP BY C.categoryName, C.categoryID ORDER BY C.categoryID
There are a total of 20 categories and I would like all the categories listed in the result even though there are no items booked in a mission. At the moment, I can only get the categories that have items in that category booked in a mission. I hoped that the RIGHT JOIN on the categories table would do the trick but it doesn't.
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
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 have a query that based 2 tables. I wrote a query with a left join on the base table but the result set returns multiple rows for each occurrence in the second table because of the left join. I want but to return all records from on table A and only matching records from table B which id but I would wan tit to keep return them vertically as the because it make it difficult to read when put in a spreadsheet. It want it to return the values horizontally so the rows are not increasing for each occurrence on table b.
We run std 2008 r2. I haven't looked at my friend's function closely yet bur he showed me that when he selects from the function with one column and the same where clause he uses on same func with select *, he gets no data under the column he requested.
But when he selects * he gets a single row.
I took a peek and see a bunch of left joins followed by a bunch of outer applies in his func. I suppose (thinking out load) if anything random like the order of rows returned or sql decisions on how query runs can affect his function, that might explain it.
SQL Ver: 2008 (not r2) Problem: The following code returns correct results when moving variable declarations and update statement outside a stored procedure, but fails to return a value other than zero for the "COMPANY TOTAL" records. The "DEPT TOTAL" result works fine both in and outside the sp.This may have to do with handling NULL values since I was getting warning message earlier involving a value being eliminated by an aggregate function involving a NULL. I only got this message when running inside the sp, not when running standalone. I wrapped the values inside the SUM functions with an ISNULL, and now return a zero rather than NULL for the "COMPANY TOTAL" records when running inside SP.All variable values are correct when running.
SQL CODE: DECLARE @WIPMonthCurrent date = (SELECT TOP 1 WIPMonth FROM budxcWIPMonths WHERE ActiveWIPPeriod = 'Y') select @WIPMonthCurrent as WIPMonthCurrent
I've written sql code which takes a date and finds the Last Day of the Month one year ago. For example, it takes the date '2015-04-17' and returns the date '2014-04-30'. The code works fine in a query. Now I'm trying to turn this into a function. However, when I try to create the function I get the error:
Operand type clash: date is incompatible with int
Why is this error being returned?
Here is my function:
CREATE FUNCTION dbo.zEOM_LY_D(@Input Date) RETURNS date AS BEGIN; DECLARE @Result date; SET @Result = convert(DATE, DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,dateadd(m, -11, @Input)+1),0)),101) RETURN @Result; END;
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]);
Hello Friends I have 3 tables 1) Product Id, ShortName 2) IncomingStockId, ProductId, Quantity, InDate 3) OutGoingStock Id, OutDate, ProductId, Quantity I need to get the results like thisProduct name, quantity in stock today the "quantity in stock today" = sum (quantity recieved) -sum (quantity sent) Thank you for your timeSara
how do i calculate something like this if I have the table with names and count? Name Count Percent Name1 27 4.69% Name2 2 0.35% .... Totals 576 100.00%
I'm trying to run a dyncamic query that returns all records within a specific distance of a certain point. The longitude and latitude of each record is stored in the database. The query is constructed from two dynamic variables $StartLatitude and $StartLongitude with represent the starting point.
It runs fine until I add the 'HAVING (Distance <= 150)' clause, in which I recieve the error: Invalid column name 'Distance' It seems that Distance cannot be referenced in the HAVING clause.
I was trying to calculate GPA and Commulative GPA (CGPA) for this student and the formula of GPA is SUM(Honor)/SUM(CREDIT) and the formula of the CGPA is the Average of the SUM(Honor)/SUM(CREDIT) grouped by semester and I wrote the below query which is calculating the GPA correctly but wrongly calculating the CGPA.
How to get the correct CGPA shown in the below desired result set table.
select x.STUDENT_ID,AVG(x.gpa)as GPA,AVG(y.gpa)AS'COMMULATIVE GPA' from ( select STUDENT_ID,SUM(Honor)/SUM(CREDIT) gpa,ACAD_YEAR,SEMESTER
The Column bDebit has either value '0' or value '1' in it ('0' being debit - positive amount, and '1' - credit, negative amount).
I would like it to show the net amount for each account. Therefore in plain English I would like to take all GLCodes that are the same (eg 6843000701) and sum all amounts that have debit value of '0' and subtract all amounts that have debit value '1'. Therefore I would only see '6843000701' code once, and the amount would be '0' becase 600 - 600 = 0.
The current query is: SELECT dbo.tbGLTransactions.lLedgerCode, dbo.tbGLTransactions.sGLCode, dbo.tbGLTransactions.bDebit, SUM(curAmount)As TotalSum, dbo.tbLedgerCode.sGLDesc FROM dbo.tbGLTransactions INNER JOIN dbo.tbLedgerCode on dbo.tbGLTransactions.lLedgerCode = dbo.tbLedgerCode.lLedgerCode WHERE dbo.tbGLTransactions.lGLExtractRun = '452' Group By dbo.tbGLTransactions.lLedgerCode, dbo.tbGLTransactions.sGLCode, dbo.tbGLTransactions.bDebit, dbo.tbLedgerCode.sGLDesc Order By dbo.tbGLTransactions.bDebit, dbo.tbLedgerCode.sGLDesc
Is someone able to help me as to how i need to modify this query to get the desired result?
i have 3 question : 1. CAN MY MICROSOFT ACCESS Database imported to sql server 2005 ? 2. can i add field for queries with custom calculation like field 1*fiedld 2. like access can do? 3. is that any wizard to do no 1 and 2?
i have 3 question : 1. CAN MY MICROSOFT ACCESS Database imported to sql server 2005 ? 2. can i add field for queries with custom calculation like field 1*fiedld 2. like access can do? 3. is that any wizard to do no 1 and 2?
When I was using a simple query using select statement with where clauses, I can get the results. When I use AND to specify more conditions. It returns no row even though I get the result when query seperately. What should be the possible cause of this. I am using SQL Server 6.5. Thank you
Hi experts,I am working on SQL server 2005 reporting services and i am getting aproblem in writting a query.Situation is given below.There is one table in database Named ChildNow i have to find the All childrens whoes Age is 13 years Base onSome given parameter.If User select Augus 2007 then It has to calculate the Childs who bornin August 1994 And if he select September Then queryshould show only those child Who born in September 1994 and soon..... And use can select another year month also likeAugust 2009 ...I am writting the following querySelect Child_Name, DOb from Childwhere ((CONVERT(DateTime, A.Date_Of_Birth, 103) >= @ Parameter1And (CONVERT(DateTime, A.Date_Of_Birth, 103) <= @Parameter2)If i know already month and year then i can write easily parameter1and parameter2 But since these are comming from user so i m notfinding how to handle this.Now please suggest me what i have to write in Where statement I thinka lot but not getting any idea about it.Any help wil be appriciated.RegardsDinesh
select INVOICE.TarrifHeadNumber, SUM(INVOICEITEMS.ItemQuantity) From invoiceitems, invoice Where invoice.invoicenumber = invoiceitems.invoicenumber and month(InvoiceDate)='11' and year(InvoiceDate)= '2012' group by INVOICE.TarrifHeadNumber
Now if i use below query and add invoicetypecode field
select INVOICE.TarrifHeadNumber,CETSH.GoodsDescription, SUM(INVOICEITEMS.ItemQuantity),INVOICE.invoicetype code From invoiceitems, invoice , cetsh Where invoice.invoicenumber = invoiceitems.invoicenumber and month(InvoiceDate)='11' and year(InvoiceDate)= '2012' and cast(CETSH.CETSHNumber as varchar) = INVOICE.TarrifHeadNumber group by INVOICE.TarrifHeadNumber,CETSH.GoodsDescription,in voicetypecode
I have a SP that manipulates data for picking products and puts them into a temp table "#PickList" which is used for the basis of printing a picking note report.
I have also added code at the end of the SP to take the "#PickList" data and insert into a permanent table called "BWT_Lift_Transaction" and then use the bcp command to query it out to a text file. All this works fine, until the bcp command runs. Although the records are in the table, bcp returns nothing. Here is the code:
The earlier parts of the code create the filename for the text file and location to store it. If I insert a SELECT on the dbo.BWT_Lift_Transaction directly after the insert, I can see the data in there, but although the bcp command creates the file correctly, it returns no data:
output
NULL Starting copy... NULL 0 rows copied. Network packet size (bytes): 4096 Clock Time (ms.) Total : 1 NULL
If I remove the delete statement at the end and run the code twice, it will insert the data into the table twice. On the first run, nothing is returned by bcp. On the second run, the first set is returned by bcp, not both sets.
I don't understand why this is, but I guess it's something to do with transaction commitment and the way bcp works.
I'm trying to update column based upon the results of a subquery. I'm getting the error that my Subquery returns more than one result. I've tried adding the EXISTS or IN keywords and cannot get the syntax right. I can't find any examples of how to write this with an Update query.
Here's my query:
UPDATE temp_UpdateRemainingHours SET UsedHours =
(SELECT dbo.vw_SumEnteredHours_byCHARGE_CD.SumEnteredHours, dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD FROM dbo.vw_SumEnteredHours_byCHARGE_CD INNER JOIN dbo.temp_UpdateRemainingHours ON dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD = dbo.temp_UpdateRemainingHours.CHARGE_CD)
FROM dbo.vw_SumEnteredHours_byCHARGE_CD, temp_UpdateRemainingHours
WHERE dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD = dbo.temp_UpdateRemainingHours.CHARGE_CD
Hi,I have an Address table which contains more than one addresses for aparticular member. I want to write a query that would only displaymost current address. All addresses have a unique ID (addID).Example:memberID addID address1-------- ------ --------------------------------------------------295 69 13 Auster St295 70 465 Lorre Ct295 71 P.O. Box 321722 171 10 Hannaford Rd722 172 Dubai, United Arab Emirates