1
2
3
* (unscheduled visit) (should be 3.01)
* (unscheduled visit) (should be 3.02)
Basically when there is an unscheduled visit, it should take the previous visit number and add .01
I am not sure how to count using non integers
Thanks
I'm writing a stored procedure where one of the arguments (WHERE area) really only needs to be used in some circumstances. I.e., when the procedure is passed a USER_ID it needs to check that against the database, but in some instances I'll send 0 instead of a real USER_ID, and in those cases it should return all records regardless of the ID.
Here's what I've got:
...
and b.user_ID = CASE @user_ID WHEN 0 THEN '%'
ELSE @user_ID
...
...the problem being the '%' part. That won't work on an integer column.
Does anyone have any ideas here?
Thanks,
Al
The code below has this line
SET @SOGallons = @ODTGallons
I need it to add the Current value of @SOGallons to the newly selected value of @ODTGallons and set that as the new value of @SOGallons.
I've tried
SET @SOGallons = @SOGallons + @ODTGallons
SET @SOGalTemp = @SOGallons
SET @SOGallons= @SOGalTemp + @ODTGallons
Neither Worked
<CODE>
FROM [CSITSS].[dbo].[Orderdt] as ODT LEFT OUTER JOIN [CSITSS].[dbo].[Orddtcom] as OCOM
ON ODT.[Companydiv] = OCOM.[Companydiv] AND ODT.[OrderNumber] = OCOM.[OrderNumber] AND
ODT.[Sequence] = OCOM.[Sequence] WHERE ODT.[Companydiv]= 'GLPC-TRANS' AND ODT.[OrderNumber] = @OrdNum AND
([LineType] = 'IP' OR [LineType] = 'SO' OR [LineType] = 'DL' OR [LineType] = 'PU')
OPEN TC1
FETCH NEXT FROM TC1 INTO @LT, @ODTGallons, @ODTComm
WHILE @@FETCH_STATUS=0
BEGIN
IF @LT = 'SO'
BEGIN
SET @SplitTest = 1
SET @SOGallons = @ODTGallons
IF @SOGallons > 0
BEGIN
SET @SOGalTest = 1
END
ELSE
BEGIN
SET @SOGalTest = 0
END
IF @SplitTest <> @SOGalTest
BEGIN
SET @SOGalTest = 0
END
END
ELSE
BEGIN
SET @SOGalTest = 1
END
FETCH NEXT FROM TC1 INTO @LT, @ODTGallons, @ODTComm
END
CLOSE TC1
DEALLOCATE TC1</CODE>
I have a table which measures the changes in a feedback rating, measured by an integer. Most of my records are the same. Only the primary key & the timestamp change.
How do I query just the changes?
Example dataset:
idrating
15
25
35
45
56
66
[code]....
There are 20 rows & 5 changes. The query I want will result in just those that are different from the ones before them:
idrating
45
56
97
118
189
I use Microsoft SQL 2008
Here is what I am trying to do...
I want to goup "members" togethers in a "group."
A table for members and a table for groups.
each containing coluns...
but inside Groups I would like a column that contains ID numbers for the members that be long to that group.
Being that members can belong to multiple groups - I can not use a GroupID in Members - if so I also need a way of it modular.
Obvisiously I am not a very good DB programmer - but I want the least amount of empty/unused space in my tables.
Hope this makes sense
I'm wondering if there is a function in SQL that works like SUBSTRING function but for integers. Like for example if I have a number like 20010112 and I want to cut it to the first for digits so that it reads 2001?
View 5 Replies View RelatedI am having difficulty trying to figure out how to compare two integers stored in a table to return a third. I have two integer fields in one table and two in another like this:
Table1.SomeNumber1 = 1
Table1.SomeNumber2 = 2
Table2.SomeNumber1 = 2
Table2.SomeNumber2 = 1
I need to be able to compare the first number from the first table to the first number in the second table. If the values are different I need to set a variable or field to 0. If the numbers are the same I need to set my variable or field to 1.
I need to follow the same procedure comparing the second number in the first table to the second number in the second table. In addition, I need to be able to do it in a single select statement.
Does anyone have any ideas on how this could be done? Thank you for any help you may be able to provide.
Gmz
It seems I am facing again an unsurmountable problem It should be so simple but one has to spend hours researching how to handle it. The MSDN help on this subject is increadibly obscure.
I have input parameters @months int, @days int, @years int in a stored procedure.
All I want to do is to get a DateTime variable out of them.
DECLARE @dated DateTime.
Thus I want @dated to be set to a DateTime value with month = @months, day = @days and year = @years. The MSDN help says that no CAST should be used since the conversion from int to DateTime should be implicit!!
No examples are given. They seem to show how to CAST or CONVERT varchar to DateTime. Shall I first convert my int to varchar?
It is rudiculous. I've tried dozens of variants. Please help.
Thanks.
I am trying use the decimal data type for a field in SQL Server. When I input the values below, they round off.
73.827 Rounds to 74
1925.1 Rounds to 1925
119.79 Rounds to 120
What am I missing? Access never gave me this issue. Do you see any reason this would happen? I am entering the values into the table directly!
I'm embarassed I haven't figured this out already but here goes.
Lets say you need a percentage from dividing two integers for example
"select 2000/4000"
This will produce a zero and I'm assuming that is because of the datatypes involved (the values are coming from columns where the datatype is int)
I've tried converting the values to decimal types but I keep getting overflow erros unless I use very small values.
As always, thanks VERY MUCH for the kind advice.
I have a table with below data. Requirement is to replace all integers with continuous 6 or more occurrences with 'x'. Less than 6 occurrences should not be replaced.
create table t1(name varchar (100))
GO
INsert into t1
select '1234ABC123456XYZ1234567890ADS'
GO
INsert into t1
select 'cbv736456XYZ543534534545XLS'
GO
EXPECTED RESULT:
1234ABCxxxxxxXYZxxxxxxxxxxADS
cbvxxxxxxXYZxxxxxxxxxxxxXLS
drop table t1
-----Table Proc Index Performance TSQL &&%$#@*(#@$%.......------------
Deep Into SQL Jungle
I'm having trouble adding a 4-byte integer with an 8-byte integer. Here's what I'm doing:
Column Name: BIG_ID
Derived Column: < add as new column >
Expression: (DT_I8)[ID] + 840230000538058
Data Type: eight-byte signed integer {DT_I8]
The error I get:
The literal 840230000538058 is too large to fit into type DT_I4. The magnitude of the literal overflows the type.
Then I try the expression:
(DT_I8)[ID] + (DT_I8)840230000538058
and
[ID] + 840230000538058
and get the same error.
What am I doing wrong? Is it possible to add 2 8-byte integers in regular expression? Why does it still think the literal is DT_I4?
Thanks,
Michael
Hello, all.
Why is it that, despite what is said in the sketchy SQL Help content, it appears to be impossible to cast a string to an integer in the Expression Builder to generate a value for a variable? More specifically, why does the following expression cause an error?
(DT_UI4) (SUBSTRING(@[User::FullDataPath], LEN(@[User:ataPath]) + 1, 2))
I'm iterating over files and using the name of a given file as an ID for an operation. I simply want to grab a file name using the Foreach Loop Container and process that file, while at the same time use the name in another operation. The file name will be something like "2.txt" (full path something like "c:somethingsomething2.txt"). I can use string functions to return the file name, which is a number as a string, and it should be no problem to cast that number as a string to a number (an Int32). SQL Server 2005 help has a chart that indicates such a cast is legal.
Maybe it's a crazy thing to be doing. Maybe I have to go about this a completely different way, but casting from "2" to 2 should be possible in the Expression Builder.
Thanks for any help.
I was told that, when possible, use integer fields for the equality comparison in INNER JOINS. Today someone suggested that using character fields that are indexed should be just as efficient. What do you think?
TIA,
barkingdog
I am working with a database named €œDocuments€? that contains 4 categories of text documents, each having its own number designation in an integer datatype column named SectionTypeId:
1 = Text
2 = Report
3 = Background
4 = Index
I would like to create a new column named €œDocType€? in which the integer data type for each document is replaced with a varchar data type letter (1 = T, 2 = R, 3 = B, 4 = I). I was able to easily create the new column and cast the data type from integer to varchar:
--CREATE NEW COLUMN €œDocType€? WITH VARCHAR DATATYPE
ALTER TABLE FullDocuments ADD DocType VARCHAR(1) NULL
Go
--UPDATE NEW COLUMN WITH CAST STRING
UPDATE FullDocuments SET DocType = CAST(SectionTypeID AS VARCHAR(1))
Go
But I have problems with the REPLACE method for replacing the numbers with letters. First I tried this based on the examples in MSDN Library:
--REPLACE NUMBERS WITH LETTERS
UPDATE Fulldocuments REPLACE (DocType,"1","T")
Which produced an error message: €œIncorrect syntax near 'REPLACE'.€?
Thinking that the datatype may be the problem, I tried this to convert to DT_WSTR data type prior to replace:
UPDATE Fulldocuments REPLACE ((DT_WSTR,1)DocType,"1","T")
Which produced the same error message: €œIncorrect syntax near 'REPLACE'.€?
I have never done a REPLACE before, so any suggestions for accomplishing this would be appreciated.
I have a table where each entry represents a range:
id, num_ini, num_fim
1, 1, 19
2, 20, 39
3, 40, 59
etc
Is there any way to select a recordset on this table with the following format?
id, num
1, 1
1, 2
1, 3
etc
2, 20
2, 21
2, 22
etc
I'm using MSSQL 2005
tks
My ERP software stores all dates as integers. So originally, I wrote a T-SQL function to convert these integer dates to normal people dates in the query I use as the recordset for my report. Well...that worked fine on 1,000 rows, but NOT for 100,000. So I've figured out that if I convert my normal person date parameter to an integer date, then SQL only has to convert my 1 parameter instead of having to convert 100,000 fields, (actually, 300,000 because I have 3 date columns).
So my question is, what is the best way to do this? This is what I have so far:
SET @Macola = Cast(Datepart(yy,@MacolaDate) as varchar) + Cast(Datepart(mm,@MacolaDate) as varchar) + Cast(Datepart(dd,@MacolaDate) as varchar)
However, I want the leading zeros for the month and day. For example if I enter '1/1/2004' into this function, it returns 200411, but I need it to return 20040101.
Any suggestions would be greatly apprectiated. Thank you.
Hi all,
I have a wrong €œdbo.Samples€? table:
SampleID SampleName Matrix SampleType ChemGroup ProjectID
1 Blueriver01 Soil QA VOCs 1
7 Greentree01 Water Primary VOCs 1
8 Greentree02 Water Duplicate VOCs 1
9 Greentree03 Water QA VOCs 2
10 Greentree11 Soil Primary VOCs 1
11 Greentree11 Soil Duplicate VOCs 1
12 Greentree11 Soil QA VOCs 3
13 Redrock01 Water Primary VOCs 1
14 Redrock02 Water Duplicate VOCs 1
15 Redrock03 Water QA VOCs 2
16 Redrock11 Soil Primary VOCs 1
17 Redrock12 Soil Duplicate VOCs 1
18 Redrock13 Soil QA VOCs 3
I used the following sql code to correct the wrong ProjectIds:
USE ChemDatabase
GO
ALTER TABLE Samples
SET ProjectID = 4 WHERE SampleID = 7
SET ProjectID = 4 WHERE SampleID = 8
SET ProjectID = 5 WHERE SampleID = 9
SET ProjectID = 4 WHERE SampleID = 10
SET ProjectID = 4 WHERE SampleID = 11
SET ProjectID = 6 WHERE SampleID = 12
SET ProjectID = 7 WHERE SampleID = 13
SET ProjectID = 7 WHERE SampleID = 14
SET ProjectID = 8 WHERE SampleID = 15
SET ProjectID = 7 WHERE SampleID = 16
SET ProjectID = 7 WHERE SampleID = 17
SET ProjectID = 9 WHERE SampleID = 18
GO
I got the following error message:
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'SET'.
Please help and tell me what it is the right syntax for my €˜SET€™
used in this sql code. I think there are more mistakes in this set of sql code. Please enlighten me and advise me how to make this set of code right.
Thanks,
Scott Chang
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
I am working with a legacy SQL server database from SQL Server 2000. I noticed that in some places that they use decimal data types, that I would normally think they should be using integer data types. Why is this does anyone know?
Example: AutomobileTypeId (PK, decimal(10,0), not null)
Hi,
I have soma ado.net code that inserts 7 parameters in a database ( a date, 6 integers).
I also use a self incrementing ID but the date is set as primary key because for each series of 6 numbers of a certain date there may only be 1 entry. Moreover only 1 entry of 6 integers is possible for 2 days of the week, (tue and fr).
I manage to insert a row of data in the database, where the date is set as smalldatetime and displays as follows: 1/05/2007 0:00:00 in the table.
I want to retrieve the series of numbers for a certain date that has been entered (without taking in account the hours and seconds).
A where clause seems to be needed but I don’t know the syntax or don’t find the right function
I use the following code to insert the row :
command.Parameters.Add(new SqlParameter("@Date", SqlDbType.DateTime, 40, "LDate"));
command.Parameters[6].Value = DateTime.Today.ToString();
command.ExecuteNonQuery();
and the following code to get the row back (to put in arraylist):
“SELECT C1, C2, C3, C4, C5, C6 FROM Series WHERE (LDate = Today())�
WHERE LDate = '" + DateTime.Today.ToString() + "'"
Which is the correct syntax? Is there a better way to insert and select based on the date?
I don’t get any error messages and the code executes fine but I only get an empty datatable in my dataset (the table isn’t looped for rows I noticed while debugging).
Today’s date is in the database but isn’t found by my tsql code I think.
Any help would be greatly appreciated!
Grtz
Pascal
I am creating a table on SQL Server. One of the columns in this new table contains whole integer as wells as decimal values (i.e. 4500 0.9876). I currently have this column defined as Decimal(12,4). This adds 4 digits after the decimal point to the whole integers. Is there a data type that will have the decimal point only for decimal values and no decimal point for the whole integers?
View 2 Replies View Relatedbelow data,
Countery
parentid
CustomerSkId
sales
A
29097
29097
10
A
29465
29465
30
A
30492
30492
40
[code]....
Â
Output
Countery
parentCount
A
8
B
3
c
3
in my count function,my code look like,
 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
it will take 12 to 15 min to execute.
I am trying to get count on a varchar field, but it is not giving me distinct count. How can I do that? This is what I have....
Select Distinct
sum(isnull(cast([Total Count] as float),0))
from T_Status_Report
where Type = 'LastMonth' and OrderVal = '1'
Good day experts,
I wonder if i got an answer for this.
How can i iliminate a letters from a set of integers and characters using a SQL Statement
for ex:
ABC9800468F
is that possible?
is there a function that i can use to iliminate them?
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.
View 5 Replies View RelatedThe 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
group by substring(m.PTNumber, 3,3)
order by AUPct Desc
Thanks. Dan
Hi all
i using lookup error output to insert rows into table
Rows count rows has been inserted on the table 59,123,019 mill
table rows count 6,878,110 mill ............................
any ideas
Is there a difference in performance when using count(*) or count(columnname)?
View 10 Replies View RelatedI 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.
Thank you for your assistance in advance.
Joel
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
Hi,
This is my table structure ,
Name
John
John
Raj
John
Raj
From the above table i want to count the repeated name and my output should be
Name Count
John 3
Raj 2
since no of jone in my table is 3 and number of Raj is 2 .
how to write the query for this ?