Is it possible to group the below code into an Island type scenario? The data represents people in a location. If they are in the same place one after another, to group and provide the min/max scenarios.
DECLARE @Table TABLE
(
PersonVARCHAR(10)
,LocationCHAR(1)
,Order_WhenINT
[code]....
Happy Friday!
A while since I have posted a question, and this one is probably real easy.
I am trying to store numeric values from a php form in MSSQL 2000 database. However, the columns are set to float and if the value is 1.00, when entered into the table it is saved as 1
If I change the column type to money, the query fails, with an error message of conversion of datatype varchar to datatype money statement terminated.
anybody know what I need to do? do I need to do something in my query to specify that this is NOT varchar data?
My data is in 4 columns and multiple rows, like this
PartNo  Quantity TransactionType        TransactionDate
aaa      25        Incoming                   2015-03-01
aaa       25        Incoming                   2015-03-01
aaa      50        Transfer                    2015-03-02
bbb      30        Incoming                  2015-03-03
bbb      30        Transfer                    2015-03-03
ccc       50        Incoming                  2015-05-15
ccc       75        Incoming                  2015-05-20
ccc       50         Transfer                   2015-05-18
ccc       75        Transfer                   2015-05-21
What I need to achieve is sum the quantities for a given transaction type, group it by Part Number and add an additional column where the Transaction Date for Transfer Type rows would become the Transfer Date. Each part would have one row. The resulting data would look like this.
PartNo  Quantity  IncomingDate  TransferDate
aaa       50          2015-03-01     2015-03-02
bbb       30           2015-03-03     2015-03-03
ccc        125        2015-05-15      2015-05-21
How to achieve this goal.
I am building as Search page whereby a user passes in a variable and depending on the variable type, different results will come back ...if the search criteria is '123 somewhere' it would be a string and we would search the address field. If the search criteria is '123' i want to search the address field as well as the id field.that being said, in TSQL is there a way to determine if the variable coming in is a string or an int?
View 3 Replies View RelatedI am working with SQL 2012 Express and I have a table with all transactions of invoices and payments for each customer. I am looking to find the last transaction detail of either two particular types of transactions (but not both) for each customer. So far I have tried various combinations around
SELECT       MAX([sbt::dte]) OVER (PARTITION BY [sbt::code]) AS LastPayment, [sbt::folio], [sbt::typ], [sbt::net]Â
FROM dbo.tblSalbatTxn
WHEREÂ Â Â Â Â ([sbt::typ] = 13 OR [sbt::typ] = 17)Â
Then there are some cases where customers have made more than the one of the same transaction type on that same last day which I would then like to sum the net value for that day of that transaction type.
I have also worked around this but it filtered the transaction type after it found the last transaction for each customer irrespective of it's transaction type.
SELECT Â Â Â Â TOP (100) PERCENT a.[cl::code], a.[cl::alpha], b.[sbt::folio], b.[sbt::typ]
FROM Â Â Â Â Â Â dbo.tblSalAccounts AS a INNER JOIN
             dbo.tblSalbatTxn AS b ON a.[cl::code] = b.[sbt::code]
WHERE Â Â Â Â (b.[sbt::dte] =
               (SELECT     MAX([sbt::dte]) AS Expr1
                FROM       dbo.tblSalbatTxn
                WHERE     (b.[sbt::typ] = 11 OR
                             b.[sbt::typ] = 17) AND ([sbt::code] = b.[sbt::code])))Â
I have a field called PPH and this field type float. so, this field could be null, 0.0, or values. I am using case statement:
 Â
[PPH] = case when h.HoursFloat = 0 then 0 else
round(s.Pledges / h.HoursFloat,2) end,
I am trying to add where if h.HourFloat is null then 0 but, it does not work. I have tried different ways such as
[PPH] = CASE isnull (h.HoursFloat,0)Â
when 0 then 0 else ....
How to add another condition to check on null then 0.
Field is not listed as text in any of the databases it is a varchar(255) - and that can be changed if that is what causes the issue. Â
But here is my syntax which produces the error Msg 421, Level 16, State 1, Procedure, Line 2
The text data type cannot be selected as DISTINCT because it is not comparable.
DECLARE @c NVARCHAR(MAX)
WITH c1 AS (
SELECT [abcd] AS table_name
FROM [intranet].[dbo].[soccerfieldinfo]
where [abcd] IS NOT NULL
), c2 AS (
SELECT Row_Number() OVER (ORDER BY table_name) AS r
[Code] ....
This is a common error for SQL Server, but I got it in a uncommon way.I have a table called - tblIDNumber where there are two columns - IDN_Number [NVarchar(200)] and Temp [BigInt]
If I run,
SELECT Â *
FROM Â Â dbo.tblIDNumber
WHERE Â IDN_IDNumberTypeStaticValue = 33
    AND IDN_Removed = 0
    AND CAST(IDN_Number AS BIGINT) = 1
SQL Server give me the error:
Msg 8114, Level 16, State 5, Line 1
Error converting data type nvarchar to bigint.
I first thought IDN_Number in type 33 has characters, but it doesn't, becasue the below query works!!!
UPDATE dbo.tblIDNumber
SET Temp = CAST(IDN_Number AS BIGINT)
WHERE Â IDN_IDNumberTypeStaticValue = 33
    AND IDN_Removed = 0
To workaround, I ran the query,
UPDATE dbo.tblIDNumber
SET IDN_Number = '123'
WHERE Â IDN_IDNumberTypeStaticValue = 33
    AND IDN_Removed = 0
and then I ran the first query, and SQL Server does NOT give me the same error -Â Msg 8114, Level 16, State 5, Line 1 Error converting data type nvarchar to bigint.
Second query approved there is nothing wrong from converting the value in IDN_Number to a BigInt, but the third query gave the hint that data might be the cause?????
finally, I found the root cause to be an index that the first query uses :
CREATE NONCLUSTERED INDEX [IX_tblIDNumber_Covering] ON [dbo].[tblIDNumber]
(
[IDN_Removed] ASC,
[IDNumberCode] ASC
)
INCLUDE ( [IDN_Number],
[IDN_Reference]) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 85) ON [PRIMARY]
GO
If I remove the index, the first query works without the error.
Tried loading JSON data to Sql Server  ? Sample format is given below..Don't see any easy way doing it except writing some C# code deserialize it.
Â
[ {
 "name" : "peter_2.jpg",
 "createdDate" : 1259728960000,
 "lastModifiedDate" : 1308174976000,
 "Secondary" : [ {
  "Id" : 106275817,
  "Sid" : 1
 Â
[code]...
I want change all field in database to new datatype.I want change data from Small Integer to Integer but there are the relation in each table.
View 3 Replies View RelatedCASE WHEN NULLIF(NHSNo2, '') IS NULL THEN 1
WHEN NULLIF(Surname, '') IS NULL THEN 2
WHEN NULLIF(Forename, '') IS NULL THEN 3
WHEN NULLIF(DOB, '') IS NULL OR DOB < '01/01/1900' THEN 4
WHEN NULLIF(AddressLine1, '') IS NULL THEN 5
[code]...
The above code worth great but ideally instead of returning a number Id like to return text for example
1 = NHS
2= SUR
3=FOR
4=DOB
5=ADD
6=PCO
7=GPN
8=PCZ
9=GPD
10=CCG
Below is garbage data and structure (I think enough to get point across). Â How can I perform calculations needed?
Create Table #1234
(
abcd decimal(16,4)
,defg decimal(16,4)
,hijk decimal(16,4)
,logon datetime
,logoff datetime
[code]....
In the following code I want to compare 2 values: AccessVal and SQLVal. The values are stored as nvarchars, so I'm isolating the numeric values in a subquery. Notice I'm only selecting 1 row. The commented line, where I compare the values, is throwing the error.
SELECT QA_AutoID, AccessVal, SQLVal
,ROUND(ABS(CONVERT(float, AccessVal,1)),0) as AccessFloat
,ROUND(ABS(CONVERT(float, SQLVal,1)),0) as SQLFloat
FROM QA
WHERE QA_AutoID in (
SELECT TOP 1 QA_AutoID
FROM QA
WHERE ISNUMERIC(SQLVal) = 1 AND ISNUMERIC(AccessVal) = 1
)
--AND ROUND(ABS(CONVERT(float, AccessVal,1)),0) <> ROUND(ABS(CONVERT(float, SQLVal,1)),0)
ORDER BY ROUND(ABS(CONVERT(float, AccessVal,1)),0) DESC
,ROUND(ABS(CONVERT(float, SQLVal,1)),0) DESC
Here is the output with the comparison commented out...
Here's what I get with the comparison line activated:
I've tried converting to numeric, int and bigint instead of float. I've tried CAST instead of CONVERT. Nothing works.
i am trying to run to get a list of computers from a table based on the available free space. I'd like to group them based on the available space, for example, up to 1 GB, 1-2 GB, etc.Â
With Disk_Space (ResourceID, ComputerName, Description, DiskName, VolumeName, FileSystem, Size, FreeSpace)AS
(
SELECT distinct
SYS.ResourceID,
SYS.Name,
LDISK.Description0,
LDISK.DeviceID0,
LDISK.VolumeName0,
LDISK.FileSystem0,
[code]...
However when i run this query, i get an error :Â
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value '1GB To 2GB' to data type int.
I just need the date, hour, and minute...not the micro seconds. Do I have to DATEPART and concatenate each or is there any way to simply truncate the milliseconds from it? Or is there a date format to put extract and report on it as...
MM/DD/CCYY HH:MM:SS AM
I see there is a format 131 that puts it in..
DD/MM/YYYY HH:MM:SS:MMMAM
I am trying to convert all columns to rows in sql, but giving an error.
SELECT Employee, Orders
FROM
(SELECT CisId, [Z_Id], [ModuleType]
FROM CIS) p
UNPIVOT
(Orders FOR Employee IN
(CisId, [Z_Id], [ModuleType])
)AS unpvt;
Error:Â The type of column "Z_Id" conflicts with the type of other columns specified in the UNPIVOT list.If I remove "Z_Id" from selection then giving same error for ModuleType also.
this query is running fine in 2008 , but its not working in 2005 below is the error Msg 8115, Level 16, State 2, Line 1 Arithmetic overflow error converting expression to data type int.there is some problem in converting date in cte
with aÂ
asÂ
(
SELECT dbname = DB_NAME(database_id) ,
    [DBSize] = CAST( ((SUM(ms.size)* 8) / 1024.0) AS DECIMAL(18,2) )Â
    ,
COALESCE(CONVERT(VARCHAR(12), MAX(bus.backup_finish_date), 101),'01/01/1900') AS LastBackUpTime
FROM Â sys.master_files ms
inner join msdb.dbo.backupset bus ON bus.database_name = DB_NAME()
[code]....
In my database table has auto Identity file which is (1,1) But Its Increasing 1000 Some time 100 I don't Understand why It is happening in my every table.
View 4 Replies View RelatedI am using T-SQL I have a column (ColA)that has datetime format and I simply want to pull the next day but date only into ColB
ColA
3/12/2014 12:00AM
3/19/2014 12:00AM
ColB
3/13/2014
3/20/2014
I have been trying the command below but keep getting the error "Conversion failed when convertint the varchar value '03-03-2014' to data type int."
Convert (varchar(10), "StartDate", 110)+1 as Next Day
I am getting this error when I run the case statement below. I think I have to convert at some point, but not sure where or how.Â
Case when m.dispo = 2 then 'Inactive' else m.value end 'Score'
So my data is delivered as numeric(9,2)...like 100.00. I have to extract that data and store it as a varchar 0 filled without the decimal place...like 0000010000///I tried the following and it did not work...
RIGHT('000000000'+CONVERT(VARCHAR,[EODPosting].[Amount]),10),
In my inner query,Â
I have a column "Cost" (data type=varchar)Â and another column "Qty" (data type=int)Â
Now in outer query, I need to multiply this 2 columns Cost*Qty.But after trying some cast operation still getting "Error converting data type varchar to numeric."
I am trying to write a query that give me how many items were filed per year.
This is the error:
Conversion failed when converting the varchar value 'YEAR(1999)' to data type int.
Here is the query I am using:
SELECT COUNT (*),
CASE
WHEN ChargesFiledDate > 20141231 THEN 'YEAR (2015)'
WHEN ChargesFiledDate > 20131231 THEN 'YEAR (2014)'
WHEN ChargesFiledDate > 20121231 THEN 'YEAR (2013)'
ELSE YEAR (0000)
[Code] ...
And got the same error. Â I know there is probably a better way to get this data (being that I need it from 2007 to present), but don't know how to write it.
For each customer, I want to add all of their telephone numbers to a different column. That is, multiple columns (depending on the number of telephone numbers) for each customer/row. How can I achieve that?
I want my output to be
CUSTOMER ID, FIRST NAME, LAST NAME, TEL1, TEL2, TEL3, ... etc
Each 'Tel' will relate to a one or more records in the PHONES table that is linked back to the customer.
I want to do it using SELECT. Is it possible?
I have a query that performs a comparison between 2 different databases and returns the results of the comparison. It returns 2 columns. The 1st column is the value of the object being compared, and the 2nd column is a number representing any discrepancies.What I would like to do is use the results from this 1st query in the where clause of another separate query so that this 2nd query will only run for any primary values from the 1st query where a secondary value in the 1st query is not equal to zero.I was thinking of using an "IN" function in the 2nd query to pull data from the 1st column in the 1st query where the 2nd column in the 1st query != 0, but I'm having trouble ironing out the correct syntax, and conceptualizing this optimally.
While I would prefer to only return values from the 1st query where the comparison value != 0 in order to have a concise list to work with, I am having difficulty in that the comparison value is a mathematical calculation of 2 different tables in 2 different databases, and so far I've been forced to include it in the select criteria because the where clause does not accept it.Also, I am not a DBA by trade. I am a system administrator writing SQL code for reporting data from an application I support.
I have a column colC in a table myTable that has a value (e.g. '0X'). The position of a non-zero character in column colC refers to the ordinal position of another column in the table myTable (in the aforementioned example, colB).
To get a column name (i.e., colA or colB) from table myTable, I can join ("ON cte.pos = cn.ORDINAL_POSITION") to INFORMATION_SCHEMA.COLUMNS for that table catalog, schema and name. But I want to show the value of what is in that column (e.g., 'ABC'), not just the name. Hoping for:
COLUMN_NAME Value
----------- -----
colB Â Â Â Â 123
colA Â Â Â Â XYZ
I've tried dynamic SQL to no success, probably not executing the concept correctly...
Below is what I have:
CREATE TABLE myTable (colA VARCHAR(3), colB VARCHAR(3), colC VARCHAR(3))
INSERT INTO myTable (colA, colB, colC) VALUES ('ABC', '123', '0X')
INSERT INTO myTable (colA, colB, colC) VALUES ('XYZ', '789', 'X0')
;WITH cte AS
(
SELECT CAST(PATINDEX('%[^0]%', colC) AS SMALLINT) pos, STUFF(colC, 1, PATINDEX('%[^0]%', colC), '') colC
[Code] ....
I am trying to optimize a stored procedure in SQL 2008. Â When I look at an actual execution plan generated from when I run it in SSMS it shows a table being used in the plan that has no relation to what is actually in the query script and this is where the biggest performance hit occurs.
I've never seen a table show up before that wasn't part of the query. why this might occur and how to correct it? Â I can't just change the query script because the table in question isn't there.
Hello friends,
I am working on web application using sql server 2000, visual Studio 2005. In one of the table I have field 'NetAmount' purposely I had kept datatype as char. Now I want to update that field. for e.g. I have to add 100 to netAmount. How I can do it with sql query. Please let me know.
Thanks & RegardsGirish Nehte
I want to query XML datas.
I have one table T.this T has two columns,id int , datasXML xml.
the datasXML has some XML datas, there into one data is:
<root> <item val="true"></item> <others ... /></root>
Now,I want to query this table's datasXML column,and root/item value is true.
How to design this SQL string?
SELECT * FROM T WHERE datasXML = "<root>.." -- or other?
Please give me some samples.thanks!
Ray Lynn
Hi
I need help in finding the query which will provide the following resultset from the below table..
Table :
create table product_stocks(product_id int , product_type varchar(20) , no_of_units int)
Data:
insert into product_stocks values(1,'A',30)
insert into product_stocks values(2,'A',70)
insert into product_stocks values(3,'A',60)
insert into product_stocks values(4,'A',40)
insert into product_stocks values(1,'B',90)
insert into product_stocks values(2,'B',60)
insert into product_stocks values(3,'B',70)
insert into product_stocks values(4,'B',40)
insert into product_stocks values(1,'C',40)
insert into product_stocks values(2,'C',50)
insert into product_stocks values(3,'C',80)
insert into product_stocks values(4,'C',90)
Result Set:
product_type product_id no_of_units
--------------- ------------- --------------
A 2 70
A 3 60
A 4 40
B 1 90
B 3 70
B 2 60
C 4 90
C 3 80
C 2 50
i.e The result set gives the top 3 products in each product_type based on the no_of_units.
thanks
Hi,I have a table with the follow fields :ID - IntDate - DatetimeI need to make a simple query to result the records between to dateswith a single ID.Ex.: Get the records between 01/08/2003 to 30/08/2003 only from ID=230I´m using the follow :ADOQuery1.Close;ADOQuery1.SQL.Clear;ADOQuery1.SQL.Add('Select * from Apro where data between Inicio andFinal');ADOQuery1.Parameters[0].Value:=Inicio;ADOQuery1.Parameters[2].Value:=Final;ADOQuery1.Open;When I open the query it doesn´t work cos it´s result a null setHow can I solve this?I´m using SQL Server 2000 and Delphi 6Thanks for atention.*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 4 Replies View RelatedHi everyone,
Is it possible to perform a SELECT/INSERT statement with two different connection types? I want to do the "SELECT" statement with data from SQL Server and "INSERT" it into an Access database all in one query.
Sanctos
Hello Experts,
Can any one tell me the query to modify the existing Column character length using QUERY?