How can i make a sum (concatenation) of strings of one column in a table. for example i have a table like this field1 field2 1 abc 1 bcd 2 sdf 2 sdd I want to get these strings added group by field 1 Thanks
I hv executed the following query in the Query Qnalyzer (Ofcourse I am Using SQL 2000 Enterprise Edition) and Surprisingly I am getting the Output as follows,
query:- select '5' + 10
Output:- 15
Can anybody please tell me, why it is happening like this? OR Did I miss any Configuration Parameter in the Server Settings?.
i have to find the total salary(i.e. salary+bonus)
this is how i managed to do it:
select empname,(salary + commission) as 'total salary' from emp where commission is not null union select empname,salary from emp where commission is null
now my question is,wht if the case now is different as below:
salary bonus hra 1 null 6 2 5 null 3 null null
now how do i calculate total salary(i.e. salary+bonus+hra)
I Have 3 columns. First 2 columns are Text and the third one is Number. I need to see if any duplicate values are there in first 2 columns. If so then i need to add the values of 3 columns and make it one. So the output should look like this,
Hi I have a query, what I would like to do is create a column that takes the results in two coulms and add them together: Col A Col B Col C Row1 1 1 2 Row2 2 3 5 Here is the query declare @t table( player_name varchar(100), BuyIn int, TopUp int, ReBuy int, Winnings int, Events int, Test int) INSERT INTO @t (player_name, TopUp) SELECT Player_name, SUM([Top-ups]) AS TOPUPS FROM (SELECT Event_data.Transaction_type, Players.Player_name, Events.Top_up, Event_data.Transaction_value, Events.Top_up * Event_data.Transaction_value AS [Top-ups] FROM Event_data INNER JOIN Events ON Event_data.Event_id = Events.id INNER JOIN Players ON Event_data.Player_id = Players.Player_id WHERE (Event_data.Transaction_type = 2)) AS Topups GROUP BY player_name INSERT INTO @t (player_name, ReBuy ) SELECT Player_name, SUM([Re-buys]) AS REBUYS FROM (SELECT Event_data.Transaction_value, Players.Player_name, Events.Rebuys, Event_data.Transaction_value * Events.Rebuys AS [Re-buys] FROM Event_data INNER JOIN Events ON Event_data.Event_id = Events.id INNER JOIN Players ON Event_data.Player_id = Players.Player_id WHERE (Event_data.Transaction_type = 3)) AS REBUYS GROUP BY Player_name Insert into @t (player_name, BuyIn) SELECT dbo.Players.Player_name, SUM(dbo.Events.Buy_in) AS BuyIn FROM dbo.Players INNER JOIN dbo.Event_data ON dbo.Players.Player_id = dbo.Event_data.Player_id INNER JOIN dbo.Events ON dbo.Event_data.Event_id = dbo.Events.id GROUP BY dbo.Players.Player_name, dbo.Event_data.Transaction_type HAVING (dbo.Event_data.Transaction_type = 1) ORDER BY SUM(dbo.Events.Buy_in) DESC
Insert into @t (player_name, Winnings) SELECT dbo.Players.Player_name, SUM(dbo.Event_data.Transaction_value) AS Winnings FROM dbo.Players INNER JOIN dbo.Event_data ON dbo.Players.Player_id = dbo.Event_data.Player_id GROUP BY dbo.Players.Player_name, dbo.Event_data.Transaction_type HAVING (dbo.Event_data.Transaction_type = 1) insert into @t (player_name, Events) SELECT dbo.Players.Player_name, COUNT(dbo.Event_data.Place) AS Expr1 FROM dbo.Players INNER JOIN dbo.Event_data ON dbo.Players.Player_id = dbo.Event_data.Player_id INNER JOIN dbo.Events ON dbo.Event_data.Event_id = dbo.Events.id GROUP BY dbo.Players.Player_name HAVING (NOT (COUNT(dbo.Event_data.Place) IS NULL)) insert into @t (player_name, test) select player_name, ((TopUp) + (Rebuy)) as Test from @t SELECT player_name, min (BuyIn) as BuyIn, min(TopUp) as TopUps, min(ReBuy)as ReBuy, min(Winnings) as Winnings, min(Events) as Events, min(test) as test FROM @t GROUP BY player_name ORDER BY BuyIn DESC --ORDER BY TOPUPS DESC
END
THis is where I attempt to add the coloms but I get a null result
insert into @t (player_name, test) select player_name, ((TopUp) + (Rebuy)) as Test from @t
I am trying one of the examples from the .NET Framework 2.0 Web-Based Client Development and I'm having a problem authenticating. I'm running everything on my local computer (IIS, SQL 2005 STANDARD ADDITION and Visual Studio). While in Visual Studio (in other words, debug mode) when I run the example, I get the following error: Cannot open database "pubs" requested by the login. The login failed.Login failed for user 'DPJ-6E95AFFA416ASPNET'. This could be more of a SQL question that a ASP.NET question, but I'm not sure. I'm running under the local administrator account, but this seems to indicate that ASP is using it's own credentials to access SQL. This is my first time using these tools, and I'm guessing this is a fairly well know issue. I was looking for a "User Admin" function in SQL and could not find it, so I'm a bit lost here. Can anyone help please. Thanks
I have a form set-up and am trying to add multiple items in my checkbox to the database; however, the only thing added to the database is the first item checked. Here is the code and any help will be greatly appreciated. Thank you!:<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConConnectionString %>" InsertCommand="INSERT INTO Consultations(FullName, Email, Business, BusinessInfo, Services, Comments) VALUES (@FullName, @Email, @Business, @BusinessInfo, @Services, @Comments)" SelectCommand="SELECT Consultations.* FROM Consultations"> <InsertParameters> <asp:ControlParameter Name="FullName" Type="String" ControlId="fullname" PropertyName="Text" /> <asp:ControlParameter Name="Email" Type="String" ControlId="emailTextBox" PropertyName="Text" /> <asp:ControlParameter Name="Business" Type="String" ControlId="bisDropDownList" PropertyName="SelectedValue" /> <asp:ControlParameter Name="BusinessInfo" Type="String" ControlId="businessTextBox" PropertyName="Text" /> <asp:ControlParameter Name="Services" Type="String" ControlID="checkboxlist" PropertyName="SelectedValue" /> <asp:ControlParameter Name="Comments" Type="String" ControlID="commentsTextBox" PropertyName="Text" />
@quantity is the number that the user has input in the txtbox. I want to use this value to ADD together with the current quantity value in the shopcartitem table. I do not know how, my current query only replaces the old quantity with the new .. can someone help me? strSql = "UPDATE ShopCartItem sci SET sci.Quantity" & _ "WHERE sci.ProductID=@ProductID AND sci.ShopCartID=@ShopCartID"Dim cmd2 As New SqlCommand(strSql, conn) cmd2.Parameters.AddWithValue("@Quantity", quantity)cmd2.Parameters.AddWithValue("@ProductID", productId) cmd2.Parameters.AddWithValue("@ShopCartID", ShopCartID)
I've two databases with different structures, i want to add a record with it's data from the first database into the second database in the place i define, this action must happen automatically as soon as the record is created in the first database.
Are there any differences between the Developer Addition and Enterprise Addition of 64 Bit SQL Server 05 that would prevent me from testing full capabilities of my production Enterprise Addition in a QA environment utilizing Developer Addition? Any known restrictions on number of DB connections, memory, CPUs etc??
How can I check for Null for the amounts if no records are returned in either select. Basically it errors out if one or both of the Amounts return no records. I need to do some sort of IF statement to set one of the amounts or both amounts to zero in those cases so it doesn't error out on me
SELECT (Coalesce(pd1_Amount, 0) + Coalesce(PD2_Amount, 0)) as Amount FROM (
SELECT pd.Amount as pd1_Amount FROM Master m (NOLOCK) LEFT JOIN dbo.pdc pd ON pd.number = m.number INNER JOIN dbo.Customer c ON c.Customer = m.Customer
WHERE pd.Active = 1 AND pd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate)) AND pd.Entered <> '1900-01-01 00:00:00.000' AND pd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate)) --AND pd.Deposit IS NOT NULL --AND pd.OnHold IS NULL AND c.customer <> '9999999'
UNION
SELECT pdd.Amount as PD2_Amount FROM Master m (NOLOCK) LEFT JOIN dbo.pdcdeleted pdd ON pdd.number = m.number INNER JOIN dbo.Customer c ON c.Customer = m.Customer
WHERE pdd.Entered BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate)) AND pdd.Entered <> '1900-01-01 00:00:00.000' AND pdd.Deposit BETWEEN DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate) + 1, @FirstDayMonthDate) AND DATEADD(DAY, -DATEPART(DAY, @FirstDayMonthDate), DATEADD(MONTH, 1, @FirstDayMonthDate)) --AND pdd.Deposit IS NOT NULL --AND pdd.OnHold IS NULL AND c.customer <> '9999999' ) as PDC_Main
Hi, I was wondering if you can help. In my vb.net form I am running a query to insert data from one database table to another. However what I need to do is to be able is to add the id of a record I have just created to this insert into sql command. I have managed to use @@ identity to get the id of my first sql insert statement but I am wondering how I can use it in the second insert into and select statement. At the moment my sql statement just copies exactly what is in the select statement. I can't figure out how to add the @@identity value to my second sql statement. My second sql statement is a follows: sql2 = "INSERT INTO ProjectDeliveData(ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded)" & " select ProjId,ProjDeliveId,RoleId,MeasurementId,ProjDeliveValue, ProjDeliveComments,ProjDeliveYear,FinDataTypeId, FinFileId, ProjDeliveMonthFrom, ResourceId,ProjDeliveDateAdded from ProjectDeliveData where FinFileId=" & strActualFileId I hope you can help Cheers Mark :)
I manage complete online system for two locations, connected via dialup lease line. I have to used merge replication for the same data should be at both locations. It is working fine, I don’t like disturbe it but current requirement is addition of two column in one table which one as also use in merge replication.
How I can add aforesaid columns in table x, column name c1 numeric , c2 character?
SQL Server allows addition of two "datetime" values. For example let d1='April 10,2004' and d2='April 11, 2004'. Now when I execute d1+d2 I get 2108-07-20 00:00:00.000. On what logic is this calculation done.
I am actually looking to derive the same result in Oracle. Hence wanting to know the funda behind this.
My goal is to be able to include test information in the change request that will make the operations manager confident that the change can proceed. Right now all I can think of is to add the index in dev and staging and compare some before and after stats. But I am concerned that an index change might also affect other operations besides SELECTs - such as updates, transactions, or scheduled jobs.
With the function below, I receive this error:Error:Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 1, current count = 0.Function:Public Shared Function DeleteMesssages(ByVal UserID As String, ByVal MessageIDs As List(Of String)) As Boolean Dim bSuccess As Boolean Dim MyConnection As SqlConnection = GetConnection() Dim cmd As New SqlCommand("", MyConnection) Dim i As Integer Dim fBeginTransCalled As Boolean = False 'messagetype 1 =internal messages Try ' ' Start transaction ' MyConnection.Open() cmd.CommandText = "BEGIN TRANSACTION" cmd.ExecuteNonQuery() fBeginTransCalled = True Dim obj As Object For i = 0 To MessageIDs.Count - 1 bSuccess = False 'delete userid-message reference cmd.CommandText = "DELETE FROM tblUsersAndMessages WHERE MessageID=@MessageID AND UserID=@UserID" cmd.Parameters.Add(New SqlParameter("@UserID", UserID)) cmd.Parameters.Add(New SqlParameter("@MessageID", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() 'then delete the message itself if no other user has a reference cmd.CommandText = "SELECT COUNT(*) FROM tblUsersAndMessages WHERE MessageID=@MessageID1" cmd.Parameters.Add(New SqlParameter("@MessageID1", MessageIDs(i).ToString)) obj = cmd.ExecuteScalar If ((Not (obj) Is Nothing) _ AndAlso ((TypeOf (obj) Is Integer) _ AndAlso (CType(obj, Integer) > 0))) Then 'more references exist so do not delete message Else 'this is the only reference to the message so delete it permanently cmd.CommandText = "DELETE FROM tblMessages WHERE MessageID=@MessageID2" cmd.Parameters.Add(New SqlParameter("@MessageID2", MessageIDs(i).ToString)) cmd.ExecuteNonQuery() End If Next i ' ' End transaction ' cmd.CommandText = "COMMIT TRANSACTION" cmd.ExecuteNonQuery() bSuccess = True fBeginTransCalled = False Catch ex As Exception 'LOG ERROR GlobalFunctions.ReportError("MessageDAL:DeleteMessages", ex.Message) Finally If fBeginTransCalled Then Try cmd = New SqlCommand("ROLLBACK TRANSACTION", MyConnection) cmd.ExecuteNonQuery() Catch e As System.Exception End Try End If MyConnection.Close() End Try Return bSuccess End Function
We are using SSRS 2012. Before we were with 2005. Both the versions have the problem. When a change made to parameters (like adding additional parameter to existing report or changing default value of parameter) using visual studio on developers machine and re-deploy to server, additional parameter or changes to parameter is not showing up. So, we are first deleting the report from server and then re-deploying. This works fine. But we realized that we are losing history of the report (like data related to this report run from execution log). We need the history of report. It is very important to top management to see the details.When I searched in forums, users say that it still exist in 2012 and 2014 and every one talks about deleting and re-deploying report. I would like to know if there is any other alternate route to redeploy additional parameter and not lose history.
I have an SSIS package (TransAgentMaster) that I recently modified to include a call to a child package via the file system. The child package creates a text file. When I run the package in dev studio then the child package/text file is produced.
I then imported the TransAgentMaster as a stored packagesfilesystem package into SQL SSIS and executed the package. The child package produced the text file.
I then ran the SQL Server Agent to see if the child package would work and it did not generate the text file. Thus after updating a SSIS package importing the package into SSIS the job that calls the package will not call the child package. Please not that the TransAgentMaster package calls 7 children packages €¦ just not my new one.
Any thoughts why the agent will not run the child newly crated childe package?
 set buyerset as exists(dimcustomer.leval02.allmembers,custoertypeisRetailers,"Sales") set saleset(buyerset) set custdimensionfilter as {custdimensionmemb1,custdimensionmemb2,custdimensionmemb3,custdimensionmemb4} set finalset as exists(salest,custdimensionfilter,"Sales") Set ProdIP as dimproduct.dimproduct.prod1 set Othersset as (cyears,ProdIP) (exists(([FINALSET],Othersset,dimension2.dimension2.item3),[DimCustomerBuyer].[ParentPostalCode].currentmember, "factsales")).count
I use SQL 2000 I have a Column named Bool , the value in this Column is 0�0�1�1�1 I no I can use Count() to count this column ,the result would be "5" but what I need is "2" and "3" and then I will show "2" and "3" in my DataGrid as the True is 2 and False is 3 the Query will have some limited by a Where Query.. but first i need to know .. how to have 2 result count could it be done by Count()? please help. thank you very much
SQL 2000I have a table with 5,100,000 rows.The table has three indices.The PK is a clustered index and has 5,000,000 rows - no otherconstraints.The second index has a unique constraint and has 4,950,000 rows.The third index has no constraints and has 4,950,000 rows.Why the row count difference ?Thanks,Me.
The following query returns a value of 0 for the unit percent when I do a count/subquery count. Is there a way to get the percent count using a subquery? Another section of the query using the sum() works.
Here is a test code snippet:
--Test Count/Count subquery
declare @Date datetime
set @date = '8/15/2007'
select -- count returns unit data Count(substring(m.PTNumber,3,3)) as PTCnt, -- count returns total for all units
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date) as TotalCnt, -- attempting to calculate the percent by PTCnt/TotalCnt returns 0 (Count(substring(m.PTNumber,3,3)) /
(select Count(substring(m1.PTNumber,3,3))
from tblVGD1_Master m1
left join tblVGD1_ClassIII v1 on m1.SlotNum_ID = v1.SlotNum_ID
Where left(m1.PTNumber,2) = 'PT' and m1.Denom_ID <> 9
and v1.Act = 1 and m1.Active = 1 and v1.MnyPlyd <> 0
and not (v1.MnyPlyd = v1.MnyWon and v1.ActWin = 0)
and v1.[Date] between DateAdd(dd,-90,@Date) and @Date)) as AUPct -- main select
from tblVGD1_Master m
left join tblVGD1_ClassIII v on m.SlotNum_ID = v.SlotNum_ID
Where left(m.PTNumber,2) = 'PT' and m.Denom_ID <> 9
and v.Act = 1 and m.Active = 1 and v.MnyPlyd <> 0
and not (v.MnyPlyd = v.MnyWon and v.ActWin = 0)
and v.[Date] between DateAdd(dd,-90,@Date) and @Date
I would like to AUTOMATICALLY count the event for the month BEFORE today
and
count the events remaining in the month (including those for today).
I can count the events remaining in the month manually with this query (today being March 20):
SELECT Count(EventID) AS [Left for Month], FROM RECalendar WHERE (EventTimeBegin >= DATEADD(DAY, 1, (CONVERT(char(10), GETDATE(), 101))) AND EventTimeBegin < DATEADD(DAY, 12, (CONVERT(char(10), GETDATE(), 101))))
Could anyone provide me with the correct syntax to count the events for the current month before today
and
to count the events remaining in the month, including today.
I have an sql command for when you add a new name to the database it counts to see how many of the name entered to the textbox exist in the database. If the count is 0 then it will add the name to the table. Else it displays an error message. This works fine for inserting a new name but on my update page where you may update the name I have the same code which on button click counts to see how many exist. But if you leave the textbox the same value and click the button the count obviously results in 1 and brings up an error message. Is there a way I can do a count but not including the name that is currently the value of the textbox?protected void UpdateSharedArea(object sender, EventArgs e) { SqlConnection connection = new SqlConnection(docShare_ConString);
//Count the amount of area names that are the same as typed by user SqlCommand existCheck = new SqlCommand("SELECT COUNT(doc_area_name) FROM document_area WHERE doc_area_name = @doc_area_name", connection); SqlParameter areaname = new SqlParameter("@doc_area_name", SqlDbType.VarChar); areaname.Value = AreaText.Text; existCheck.Parameters.Add(areaname);
connection.Open(); int count = (int)existCheck.ExecuteScalar(); connection.Close();
//If the area name does not exist within the table if (count == 0) { //Update name Cheers, Mark