The table describes the tire and also the history of movement of the tire from one tire_position to another on same or a different truck. I need to capture the rotation of the tire. Rotation can be captured from changes in tire_position or changes in Truck_ID columns.
Rotation frequency is 0 if both tire_position and truck_ID stay thesame till the tire is scrapped (disposition_to = C )
If only one of them changes, rotation frequency is count the number of times it changes
If both change, rotation frequency is the maximum of the count of the two. So if truck_ID changes 2 times and tire_position changes 3 times. The rotation frequency is 3.
Obviously, if both change same number of times, rotation frequency can be coun anyone of them.
So I need to have a new column added to this table for the rotation frequency.
Checking them manually, the rotation frequency for the each serial_number is: 2361 is 4, 4853 is 3, 3007 is 1, 2743 is 2, 3949 is 2, 3995 is 2, 5666 is 1.
I have a SQL Server that generates a reasonable amount of log data in the SQL Event logs. I'm finding it difficult to use this data as the individual log files become rather large i.e. several hundred MB before they rotate.
What I would like to achieve: Set a constraint like: Each log will rotate when it reaches [XX]Mb in size and the files will be kept through [Y] rotations.
I can't seem to find how to do this through Enterprise Manager. I've had a decent look through google and there seems to be no info on this forum....can anyone point me in the right direction?
I am a newbie in using MS SQL server with analysis services. There seems to be no 'cross-validation' tool in MS SQL which is frequently used in data mining and even statistics. Is there anyone having similar difficulties? Is there any solution like a small scripts to divide the given dataset with multiple folds? Your valuable comments and feedbacks would be appreciated.
Is it possible to align a text box to display text with rotation? For example can I display it rotated 90 degrees left so that it reads from the bottom towards the top of the page? All I can find is left/right alignment and top/middle/bottom alignment. I want to rotate the text.
Hello I was very happy to have found the thread http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=491642&SiteID=1 that explained how to get the text to display Bottom-to-Top/Left-to-Right.
The solution was to setup a function that creates a bitmap the text to be displayed. This works well and correctly displays the text image in HTML and PDF. (Excel, XML and CVS won't export backgroud images).
To extend the solution to wrap text it requires a few additional lines...
Code Snippet Function LoadImage3(ByVal stText As String)
stText = stText.PadRight(10) stText = stText.PadLeft(10) Dim iMaxLength as Integer = 180 Dim iMaxWidth as Integer = 180 Dim f As Drawing.Font = New Drawing.Font("Arial",7, System.Drawing.FontStyle.Regular, System.Drawing.GraphicsUnit.Point) Dim bmpImage As New Drawing.Bitmap(1, 1) Dim MyGraphics As Drawing.Graphics = Drawing.Graphics.FromImage(bmpImage)
Dim imageSize As Drawing.SizeF = MyGraphics.MeasureString(stText, f) Dim i As New System.Drawing.Bitmap(iMaxWidth, iMaxLength) Dim g As Drawing.Graphics = Drawing.Graphics.FromImage(i) Dim rectF1 As New Drawing.Rectangle(-(iMaxWidth/2),-(iMaxLength/2),iMaxWidth,iMaxLength ) g.FillRectangle(Drawing.Brushes.White, 0, 0, i.Width, i.Height) g.TranslateTransform((iMaxWidth/2), (iMaxLength/2) ) g.RotateTransform(270.0F) 'flip the image 270 degrees
g.DrawString(stText, f,Drawing.Brushes.Black, rectF1) g.DrawRectangle(New Drawing.Pen(Drawing.Color.White, 1), rectF1) g.Flush() Dim stream As IO.MemoryStream = New IO.MemoryStream Dim bitmapBytes As Byte() i.Save(stream, System.Drawing.Imaging.ImageFormat.Jpeg) 'Create bitmap bitmapBytes = stream.ToArray stream.Close() i.Dispose() Return bitmapBytes
End Function
Items highlighted in yellow reflect changes made to orignal solution.
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
 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
I want to do something like this: SELECT COUNT (SELECT TOP(10) * FROM MyTable order by Date Desc) FROM MyTable where User = 'Scott' What I want is to return the number of affected rows where the column 'User' equals 'Scott'....But is should only check in the 10 latest inserted rows..... Hope you understand what I mean...
'<%# Eval("Username") %>' Hi: Everybody Today is not my day, I am trying to get a Count(*) from a table (name: Photos), I can get the total number if like this: WHERE (u_username = 'jamest85' ) Now, I want replace the 'jamest85' to a value from a Label, like: Label1.text or '<%# Eval("Username") %>', but always has error, so can you please check for me, how to make it right? Thanks. Below is the code: <asp:Label ID="Label1" runat="server" Text='<%# Eval("Username") %>'></asp:Label> <asp:SqlDataSource ID="SqlDataSource2"runat="server" ConnectionString="<%$ ConnectionStrings:myConString %>" SelectCommand="SELECT COUNT(*) AS TotalPhotos FROM Photos WHERE (u_username = 'jamest85' ) GROUP BY u_username"></asp:SqlDataSource> Thank you very much jamest85
Hello, I need to retrieve all records from a table named Blogs and the number of Posts associated with which Blog giving the name NumberOfPosts to that extra column. I have the following: SELECT b.*, p.COUNT(*) AS NumberOfPosts FROM dbo.Blogs b LEFT JOIN dbo.Posts p ON b.BlogId = p.BlogId I get the error: Incorrect syntax near '*'. Could someone, please, help me out? Thanks, Miguel
Hi all, Im using ASP.Net.In that i have an sql count statement which returns the count of the total messages in a particular date. Dim dt As Date smsuser = uname.SelectedItem.Text d = day.SelectedItem.Value m = mon.SelectedItem.Value y = year.SelectedItem.Value dt = dt.ToShortDateString.Concat(d, "/", m, "/", y) Dim StrSql As String = "select Count(Message) from Message where UserName='" & smsuser & "' AND Date='" & dt & "'" Dim cmd As New SqlCommand(StrSql, con) Dim reader As SqlDataReader Dim no As String Try con.Open() reader = cmd.ExecuteReader If reader.Read = True Then count.Text = reader.GetValue(0) End If The count is displayed in a text box. But, the sql statement is not giving the correct count.Wat is wrong? Pls help...
I want to count the number of records, so I tried this: SELECT COUNT(*) AS RecordCount FROM Categories WHERE Active = 1 ORDER BY Show_Order ASC But it gives me an error: error 8126: Column name 'Categories.Show_Order' is invalid in the ORDER BY clause because it is not contained in an aggregate function and there is no GROUP BY clause.
I have a table(sometable) in my db that looks like this: user item bob books bob pens bob frogs jay pencils rob cups rob plantsI run this script:SELECT DISTINCT user AS users FROM sometable to get:users bob jay robHow would I get it to include the # of rows ror each user like this: users number bob 3 jay 1 rob 2Thanks in advance.
SELECT *, (SELECT Count(*) FROM TPlanObjects where FMainID=tpm.FID and FType='ARTICLE') as FArtCount, (SELECT Count(*) FROM TPlanObjects where FMainID=tpm.FID and FType='FILE') as FObjCount FROM tPlanMain as tpm
I am usint DTS to transfer tables into a text file. I need to get a count of the number of rows that I transfer into each text file. Is there any way or an easy way to accomplish this?
I'm looking for a mean to be able to count records and to select them without having to write two SELECT lines : select count(*) AS VNBR from SSITE where... select Lenom = upper(nom), LeSite = Site+Page... from SSITE where...
WHERE... are the same for the two SELECTS ! Is there a solution ?
I have a table of issues with a column for issueID and another for issueType. For each issuetype, I want to count how many issues are but those with no issues, I want it to return with a zero.
Hi Folks, I'm having a problem generating a total figure from two seperate sums in a query. Basically what I need to do is generate the total of select count(*) from t1, select count from t2. without using a temp table. Thanks in advance Sirius
I have to create a statement that will find the next highest number of a field that is stored as a char(5), like ' 2' and add a new record adding one to that max number. The field is padded with leading spaces. I know how to get the next number. The question is counting how many spaces I need (is it a 9 or a 10) and then stuffing the leading spaces in front of the new number.
Thanks to Craig for helping me out with my first question. My next question is this. Is there a way to have it give me the counts for all tables without entering each tablename? I may have new tables every time and would like not to have to keep up with changing the script.
I manage an affiliate tracking database for some of our online advertising campaigns. In the database I have a "clicks" table, and a "Leads" table. I am trying to join these two tables on a "Source" fields then use "group by" so that I can return "Source" counts for both the clicks and leads. I want both counts to show up in the same row such that if I have 100 clicks and 10 leads for source "xyz" the results would be:
source Clicks Leads xyz 100 10
I have tried the followign query, but it returns the same value for both rows:
SELECT Leads.source, COUNT(Clicks.source),COUNT(leads.source) FROM Leads INNER JOIN Clicks ON Leads.source = Clicks.source GROUP BY leads.source
I can do this with a subquery or by looping through through a recordset, but was wondering if it is possible to do without using these methods.
Does anyone have any ideas? If so I would greatly appreciate your feedback.