FAQ Database Discussion Community


Convert row to columns SQL dynamically [duplicate]

sql,sql-server,sql-server-2008,tsql
This question already has an answer here: Pivot Dynamic Columns, no Aggregation 1 answer Simple way to transpose columns and rows in Sql? 2 answers I have a table Dev with the data below YYYMMDD Atest BTest CTest 20150525 100 200 300 20150526 110 210 310 20150527 120 220...

How to get distinct records in sqlserver

sql-server,sql-server-2008,distinct
FUNDFAMILY_ID FUND_NAME REPORTSECTION_ID -------------------------------------------------------------- 172295295 VANGUARD GROWTH FUND (Retail) 126345 172295295 VANGUARD GROWTH FUND (Retail) 126354 -------------------------------------------------------------- But i need to get the results like below: FUNDFAMILY_ID FUND_NAME REPORTSECTION_ID -------------------------------------------------------------- 172295295 VANGUARD GROWTH FUND (Retail) 126345 Inside a stored procedure i have a query like below which gives duplicate rows...

how to sort alphanumeric data in sql..?

sql,sql-server,sql-server-2008,sql-server-2012
i have items table which is having Size as follows: 1sq.mm. 1.5sq.mm. 0.5sq.mm. 2.5sq.mm. 0.75sq.mm. 4sq.mm. 20mm 25mm 20mtr 75mm x 50mm 100mm x 50mm 100mm x 100mm 75mm x 75mm i wanted to display it has 0.5sq.mm. 0.75sq.mm. 1.5sq.mm. 2.5sq.mm. 4sq.mm. 20mm 20mtr 25mm 75mm x 50mm 75mm x...

Using GROUP BY in SQL Server

sql-server,sql-server-2008
With this query I got 2 columns of distinct values SELECT DISTINCT Transport.ID, Transport.Address FROM Cars LEFT JOIN Transport ON Cars.TransportFrom=Transport.ID ORDER BY address Now I need get one more column with sum: SUM(CASE WHEN Active='True' THEN 1 ELSE 0 END) But I get error if I do queries like...

SQL Message Broker leaving messages in Sending Queue

sql-server,sql-server-2008,service-broker
We're putting messages into a SQL Server message queue, by a trigger on a table. (When a field is updated, we build some XML, and call the trigger below). CREATE PROCEDURE [dbo].[up_CarePay_BrokerSendXml] -- Add the parameters for the stored procedure here @Data VARCHAR(MAX) AS BEGIN DECLARE @InitDlgHandle UNIQUEIDENTIFIER DECLARE @RequestMessage...

Inline SQL Not Updating

sql-server-2008
My print statement fails each time and so does my exec, anytime I try to run this statement it just tells me command completed successfully. I added the print statement to try to see what was actually being executed, but I am still mind-blown on this. Can someone here please...

SQL : Split one row into two rows depending on column

sql,sql-server,sql-server-2008
I am sorry if this is duplicate. Please point me to correct question. I am using SQL SERVER 2008. I am using below query since I need to get data from 3 tables. SELECT qc.FileID as [FileID], qc.QID1 as [QID1], xqs.SID1 as [SID1], xqc.CID1 as [CID1], xqs.Comments as [SComments], xqc.Comments...

SQL Server : copy data from columns to another table with many columns

sql,sql-server,sql-server-2008
I have an existing table wherein I store my data that I got from another server. Now, I want to copy that data to one table wherein that will be my final table on where I could do the CRUD operation. Fist table (reference database) to restore to my TempTable...

Insert Into two Tables with Foreign Key relationship

sql-server,asp.net-mvc,entity-framework,sql-server-2008,stored-procedures
In my MVC application, I have two entities called Ticket and Attachment, and when inserting a new ticket record, I also want to insert new records for attachments with the FK of previously inserted Ticket's ID. I have a look at some samples as this page, but I need to...

How to insert into a table variable with a dynamic query?

sql,sql-server-2008
I tried to develop this stored procedure using a temp table but that wouldn't work so I switched to using a table variable. I need to execute an interim dynamic query into the table variable and then I use that table variable to execute the final query. The problem is...

SQL Server Job history 2008 R2

sql-server,sql-server-2008,datetime
I have many jobs scheduled in SQL Server R2. And each job may contain several Steps under it. I want the lastrundate, lastrunstatus, lastrunduration, lastrunmessage, nextrundate FOR EACH AND EVERY STEP under EACH JOB. I have this query with me which works fine. The only issue is, it is not...

Finding a gap in dates

sql,sql-server,sql-server-2008,tsql,stored-procedures
I have a table with 3 columns: Date smalldatetime not null Val1 decimal not null Val2 decimal not null And a lot of data in this table. Not all calendar dates are presented in the table. How can I find all places where the dates gap is more than x...

Count number of occurrences of each month in db table asp.net linq

c#,asp.net,linq,sql-server-2008
I have a table which contains a date col. I want to use LINQ query and count the occurrence of each month and display it like month, occurrence of month in the table 1=2(1 is month, 2 is number of occurrence),2=5,3=12 and so on using asp.net. Can somebody please tell...

Always Get 1 record from Table 2 based on some condition

sql,sql-server,sql-server-2008,sql-azure
I have two tables, Product and ProductImages. Product table has 2 columns: ProductID and Name ProductImages table has 4 columns: ID, ProductID, ImageName and Primary(bit). The relation between Product and ProductImages is one to many, so one product can have many images, but for each product only one ProductImage will...

Find Mondays between 2 dates

sql-server,sql-server-2008,ssrs-2008
I need to display dates of all Mondays in the given date range. For example, if my start date is 01/05/2015 and end date is 31/05/2015, I need to show 04/05/2015 11/05/2015 18/05/2015 25/05/2015 How is it possible?...

Adding a Computed column with After clause

sql,sql-server-2008
I am trying to create a computed column after a specified column. But the SQL gives syntax error for the query below. Please help me with the correct syntax/way to do this. ALTER TABLE service_ServiceClass ADD LichenClassName AS ([dbo].[UfnGetServiceClassName]([Id])) AFTER Description ...

List All Tables That Contain A Specific Employee Name

sql-server-2008
I have a list of employee names, and a list of tables. I need to iterate all employee names and find which table(s) the employee name exists in. All of the tables to iterate exist on the same server and the same database. Sample data structure Create Table TableNamesToCheck (dbName...

One identifier for set of values

sql,sql-server,sql-server-2008,sql-server-2008-r2
I have this two columns. One is filled with some data, second is null. col1|col2 --------- null| 72 null| 72 null| 72 null| 33 null| 33 null| 12 null| 12 null| 55 null| 72 I want to generate values for col1 that will gather and group values from col2. So...

Aggregative sum of objects belonging to objects residing inside hierarchy structure

sql,sql-server,sql-server-2008,hierarchy
My problem is similar in a way to this one, yet different enough in my understanding. I have three tables: Units ([UnitID] int, [UnitParentID] int) Students ([StudentID] int, [UnitID] int) Events ([EventID] int, [EventTypeID] int, [StudentID] int) Students belong to units, units are stacked in a hierarchy (tree form -...

Getting Round The SubQuery Returned More Than 1 Value Error

sql-server-2008
I am trying to get a reading of time spent in class, and my query keeps producing the error of Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression....

Use data separated by delimiters in Sql Procedure

sql,sql-server,sql-server-2008,stored-procedures
I have a Sql Procedure that receives a string as a parameter. Now the task that I need to perform is that the parameter contains data that is separated by two different kinds of delimiters i.e ":" and "," There are two columns in the DB Id and Value. sample...

Sql Server TVP Merge with Where/Case Statement

sql,sql-server,sql-server-2008,tvp
What I am doing is pretty straight forward dug around at a few posts and couldn't figure out how to express it properly TVP Declaration CREATE TYPE [dbo].[CustomSeoDic] as table ( [RecordID] [int] NULL, [Name] [nvarchar](125) NULL) GO Attempt 1 BEGIN MERGE INTO Listings L USING @CustomSeo AS Tvp ON...

Force Update from a filtered DataGridView to Database

c#,sql-server-2008
I have created an application to fix a date conversion problem when transferring data from the machines. The conversion works great. I create a DataGridView that contains all records. I then sort the DataGridView to display the data that needs updating using SqlDataAdapter and DataSet. I then make the changes...

Return rows only if 2 values both exist

sql,sql-server-2008
I need to return a list of customer names from a purchase summary table but only if the customer has bought 2 definitive items within the 1 transaction. For example table 'transaction' ╔══════════════╦════════╦══════════════╦════════╗ ║ CustomerName ║ Item ║ Transaction# ║ Amount ║ ╠══════════════╬════════╬══════════════╬════════╣ ║...

SQL QUERY, 2 Summed Values (Columns) with same grouped by except that years are different

sql,sql-server,sql-server-2008,pivot
SQL Newbie here. I would like to ask for help from anyone regarding getting the sum of 2 values based on multiple groups but the only difference is the years. Please see my sample below: Database: ============================================= NAME COUNTRY CITY YEAR VALUE ============================================= Alpha Philippines Manila 2015 5 Beta Philippines...

Returning Top Customers by Territory For Each of the Last 4 Years and Summing Them SQL Server 2008

sql-server-2008
Forgive me for the long title, but that is succinctly as I could put this problem. I have a table that contains sales data by sale as well as the sales date, territory, customer info etc for each sale. I want to return the top 10 customers in each territory...

MS SQL Computed column

sql,sql-server,sql-server-2008
I want to create a column based on COUNT(*) on another table, and when a record is deleted from that table it should decrease the value in this new column and vice versa. So, here is the query: SELECT COUNT (*) FROM dbo.Korisnik1_FakturaStavka GROUP BY dbo.Korisnik1_FakturaStavka.FakturaID And it returns this:...

I need to dynamically set the date in a where statement to only pull results from a table to occur in County's fiscal year

sql,sql-server-2008,date,datetime,visual-studio-2008
Here is my current code: SELECT nature, inci_id, calltime, agency, gp FROM inmain AS inmain WHERE (calltime >= CONVERT(DATETIME, '2013-07-01 00:00:00', 102)) AND (calltime <= CONVERT(DATETIME, '2014-06-30 23:59:59', 102)) ORDER BY nature, calltime I need this to pull data at the end of every fiscal year without going in and...

Get work time in minutes based on shift schedule

sql,sql-server,sql-server-2008,ms-access,user-defined-functions
In production we have 3 shifts. Each Shift timing is described in table tbl_ShiftSched: WT - work time, PT - break time. ShiftTmID - schedule for 2 and for 3 shifts. I am looking for easy way to get work time in minutes having start and end time. For example,...

Show different fields on one when, despite multiple IDs

sql,sql-server,sql-server-2008,tsql
This is how the table looks: CountryId CountryName MemberType 143 AUT 1 171 AUT 2 202 AUT 3 206 BE 1 And this is the result I get when i run my query: CountryName IsType1 IsType2 AUT Y N AUT N Y AUT N N BE Y N My problem...

SSRS re-sorts data meticulously sorted from the SQL query! Why?

sql-server,sql-server-2008,sorting,reporting-services
I'm writing up a report using SQL Server Reporting Service via Visual Studio 2008. I have a stored procedure on a SQL Server database that returns a list in which I need the entry with value "F" in colulmn [Column] to be always on top, as such: F A B...

VB.NET SQL: Getting multiple columns to go together as 1 DisplayMember in a ColumnBox?

sql,sql-server,vb.net,sql-server-2008
I have a combobox created and a table where I have first and last name both as columns. I want my columnbox to list the names of the first and last names put together and I'm not sure how to progress. I tried changing my DisplayMember to "First Name" but...

Join SQL query Results and Get-ChildItem Results

sql-server,sql-server-2008,powershell
Background: I have a directory with a number of files that are imported to SQL server. Task: Creating a PowerShell script which will pick up files within this directory and use the filenames as in the SQL query. Ultimate objective: To display SQL results besides the filenames but the resultset...

Find All Parents based on certain record

sql,sql-server,sql-server-2008
I need some helps about SQL code. I have 2 tables, the first one is table name NameID Name 1 John 2 Paul 3 Jessica 4 Nancy 5 Sam 6 Jane 7 Jimmy The second one is table Family FamilyID NameID ChildID 1 1 2 2 1 3 3 2...

Trigger to run stored procedure SQL Server 2008

sql,sql-server,sql-server-2008,triggers
I have staging table for data, every month company insert data in staging table. I am using a stored procedure to copy data form staging to multiple tables. I want to create a trigger to run these stored procedures. For example my staging table blk_data and my stored procedure is...

sql query to join and concat values

sql,sql-server,sql-server-2008,tsql,sql-server-2005
sql server: 2012 These are 3 tables. First table is the user's (consultants) table The second one is the prices table, each user from the first column has a price The last table define in what area (city) the consultants offer services Consultants Prices Area uid | Name uid |...

How to access another database located on a different server in a SP? [duplicate]

sql-server,sql-server-2008,tsql,stored-procedures
This question already has an answer here: Selecting data from two different servers in SQL Server 7 answers I am working on a stored procedure that has been written a few years ago. The stored procedure accesses another database in the join query as shown below, select * from...

SQL SELECT multiple conditions many to many relationship

sql,sql-server,sql-server-2008
I am struggling to find an elegant solution to this problem. I have 5 tables and their relationships are described in the image. A page can have multiple products and each product can have many ProductRates. A Page with specific Product could have many rates as well. To get around...

Concatenated string as parameter argument value does not give output in stored procedure

sql-server,sql-server-2008
I am using SQL SERVER 2008 management studio. I have a stored procedure which accepts several parameters. One parameter name is @recvemail with default value as 'B'. If the value of @recvemail is 'Y' then all the users who opted for email news letter should be displayed in the output....

TSQL - Error in stored procedure due to conversion failure

sql-server,sql-server-2008,tsql
I have this situation in a stored Procedure: SET @DATE_RELEASE_START = '2015-01-01'; SET @DATE_RELEASE_END = '2015-05-31' SELECT @statement = ' SELECT * FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*) * 100/ 15032) AS decimal(10,7)) AS Percentage FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE...

Pivot with ID and Dates

sql-server,sql-server-2008,pivot-table
I have this data set and Im trying to create a pivot table for the data below. I having some issues when trying to put in the date for each type across. Member ID Type Date 1 A 12/5/2014 1 b 3/6/2014 2 a 6/9/2015 2 b 3/2/2015 2 c...

How to fix this logic?

sql-server,sql-server-2008
i have a query with 3 variable tables: @result, @order and @stock. the logic is the stock qty must be allocated by lotsize (here i set=1) to all order based on priority (FIFO). the stock qty must be allocated till zero and the allocateqty must <= orderqty. the problem is...

finding values of corresponding columns in a query using group by

sql,sql-server-2008
I have a table that contains donation history (id, transdate, amt to simplify) and I need to calculate some totals. Some of them I can get using the max and min functions but others I'm having trouble pulling out. What I need is: ID Number of donations Sum of all...

What is the execute command to use here

sql,sql-server,sql-server-2008,tsql
protected void Page_Load(object sender, EventArgs e) { string sqlConnectionString = @"Data Source=phriz-webapp01;Initial Catalog=PFTracking;Integrated Security=True"; string script = "if not exists(select * from sys.servers where name=N'CNCTC-WEB01')begin exec sp_addlinkedserver @server='CNCTC-WEB01'exec sp_addlinkedsrvlogin 'CNCTC-WEB01','false',null,'svc_Phils',[email protected]' end INSERT INTO [PFTracking].[dbo].[TempTable] SELECT c.[pf_id],a.[RequestDate],c.[pf_carrierUsed],b.[PiecePrice] * b.[PartQuantity] as [Amount]...

How to retrieve a part of a stored procedure's header?

sql-server,sql-server-2008,stored-procedures
I have a C# WinForms application that manages stored procedures used by different services. What the users see is something like that: exec stored_procedure_name param1, param2, param3 And since param1 doesn't mean anything to them (they can't see the stored procedure), I would like to present to them small descriptions...

Displaying SQL Data For Same Day

sql-server-2008
I need to be able to display information where the search parameters show same day. I thought this query would cut the mustard, however it presents an error of Msg 241, Level 16, State 1, Line 13 Conversion failed when converting date and/or time from character string. And this is...

Check for two decimal digit number in string

sql,sql-server,sql-server-2008,select
I have two sets of records Set 1: -11 -12 -12 AN '' -134 -125 +135 Set 2: 1.15 1.1 In Set 1 I need to check which values are either blank '' or start with a + sign and are greater than 125. In Set 2 I need to...

How to install ssas AdventureWorks DW with SQL Server 2008 R2

sql-server,sql-server-2008,sql-server-2008-r2,ssas,adventureworks
I am doing a proof of concept to explain the team using cubes improves the application's query performance. I am allowed to use VS 2008 BI tools. I am in need of the AdventureWorks2008R2 DW to be installed to write some mdx queries to query the cubes. I have landed...

passing parameter values to stored procedure when calling from another procedure

sql,sql-server-2008,stored-procedures
i have a stored procedure sp1 which takes parameters @id int, @type int, @ orderno int Now, I am trying to call sp1 from sp2 alter proc sp2 /insert into @temp_tbl exec sp1 1,2,3 set @select = 'select * from @temp_tbl' [email protected] is already declared exec (@select) Now when I...

SQL Server 2008 two tables with common dates field how to select by datewise from both tables

sql-server,sql-server-2008,date,datetime
two tables with common field with dates Here i mentioned how i need output kindly any help me. table1 table1.col1 table1.col2 table1.col2 a ab 2015-05-01 00:00:00.000 as as 2015-05-01 00:00:00.000 as asasd 2015-05-01 00:00:00.000 asd aa 2015-05-02 00:00:00.000 asd asd 2015-05-04 00:00:00.000 table2 table2.col1 table2.col2 table2.col3 asd aasd 2015-05-01 00:00:00.000...

How to increment and format integer in results table in SQL Server 2008?

sql,sql-server,sql-server-2008
I have read many posts related to this issue but unfortunately none of the suggested solutions worked for me. I am trying to set up a SQL statement that would create a column in my results table with values formatted as follows: date + 8 digit number incremented by 1...

SQL Customized search with special characters

sql,sql-server,sql-server-2008
I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma , and minus -. I know a relational database engine is designed from the principle that a cell holds a single value and obeying to this rule...

Microsoft SQL Insert into subset of table columns fails [on hold]

sql-server,sql-server-2008
Can anyone help me find the column or columns in this table where data would be truncated? I am trying to execute the SQL insert below, but it is failing with the error: String or binary data would be truncated. Which I understand means a value is too big to...

Update Query With Inline

sql-server-2008,sql-update
Is it possible to do an update query like this? It may not be, just a thought I had as to possibly a solution to my predicament of terrible data-structure. What I am trying to accomplish is: To update the table prodinformation with a count where the entrytype exists in...

Is it possible to iterate a calculation across distinct values in a column?

sql-server,sql-server-2008
I want to iterate a calculation which takes counts of people that fit particular criteria and calculates percentages based on those counts across distinct regions. My code: USE Database1; GO declare @ShouldRegister as float declare @Registered as float SET @ShouldRegister = (SELECT COUNT(*) FROM dbo.TABLE WHERE field1 in.. AND field2...

Split comma separated string table row into separate rows using TSQL

sql,sql-server-2008,tsql
Say I have a query that returns the following ID SomeValue 1 a,b,c,d 2 e,f,g Id like to return this as follows: ID SomeValue 1 a 1 b 1 c 1 d 2 e 2 f 2 g I already have a UDF calls Split that will accept a string...

Calculating the duration between two dates in sql

sql,sql-server,sql-server-2008,tsql
I'm having some difficulty with an SQL query for reporting machine efficiency from a CNC monitoring system. The monitoring software records the duration the machines are in different states like machining, stopped, out of work setup etc. I want to total up the duration of these states for each machine...

JPA annotation for MS SQL Server 2008 R2 IDENTITY column

hibernate,sql-server-2008,jpa,spring-data-jpa
I want to know proper JPA annotation setup to handle MS Server 2008 R2 IDENTITY column. MS Server 2008 R2 (RTM) doesn't support SEQUENCE, but the IDENTITY gives auto increment when I tested by raw sql. However, by JPA I can't make it work by any strategy (IDENTITY, AUTO, TABLE)...

The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument

sql,sql-server,sql-server-2008
I am getting the following error message when I am trying to do replace null to zero. The column name "jan" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. Query below: select * from(select isnull(jan,0),isnull(feb,0),sum(data) as amount )as p pivot( sum(amount) for month...

Msg 8115, Level 16, State 2, Line 2 Arithmetic overflow error converting expression to data type int

sql,sql-server,sql-server-2008
I am getting an error after firing this query. select COUNT(*) from [DB1].dbo.Transaction(nolock) t join [DB2].dbo.visits (nolock) v on t.V_ID=v.V_ID where t.Ct_ID=11 and t.Timestamp>'06-08-2015' and v.C_ID is null Error: Msg 8115, Level 16, State 2, Line 2 Arithmetic overflow error converting expression to data type int ...

Can we use CASE statement outside select or update query. CASE statement on Input Parameters

sql,sql-server,sql-server-2008
I want to use CASE statement something like below code or any other suggestion to implement said scenario. I will be using this in some SP where @UpdateStatus I will be getting as input parameter. Please suggest. DECLARE @UpdateStatus nvarchar(20) SET @UpdateStatus='P' CASE WHEN @UpdateStatus='P' THEN BEGIN BEGIN TRAN Print...

SQL Server Versions - 2012 vs 2014 [closed]

sql-server,oracle,sql-server-2008,sql-server-2008-r2,sql-server-2012
My team needs to get SQL Server as our backend database. I would like to find out if we should get 2014 or 2012 R2. The rest of the company is probably still SQL Server 2008 R2, Oracle and probably MySQL. I wonder if we get 2014 version which is...

Zeroth Row for a OLTP table

sql,sql-server,sql-server-2008,sql-server-2012
I have come across a OLTP Database where all the tables has a default row,value 0 for a primary key and other columns with its default value or a null value.I am not sure why is this row required in first place? can anyone explain what are the advantages and...

SQL Server query syntax with single quote

sql,sql-server,sql-server-2008,select,escaping
I have in my database KIA car model like CEE'D. How to write right syntax to get all car where model = CEE'D Now I am using semipart of model SELECT make, model FROM Cars WHERE model LIKE '%CEE%' But now I need to get by full model name. How...

Title search in SQL With replacement of noice words [on hold]

sql,sql-server,sql-server-2008
I have Two tables first us IMG_detail S.NO Title 1 women holding stack of gifts 2 Rear view of a man playing golf 3 Women holding gifts 4 Close-up of a golf ball on a tee 5 Businessman reading a newspaper and smiling and Second is tbl_NoiceWords SN Key 1...

Update Statement with Inline SQL

sql,sql-server,sql-server-2008,syntax-error
This syntax keeps giving me an invalid column. All I want to do is insert the value into the table. How should I change my syntax to make it possible? This is the error presented. Msg 207, Level 16, State 1, Line 1 Invalid column name 'Alpha12'. Code: Declare @vector...

Case statement to find 90 days between two columns

sql,sql-server,sql-server-2008
I have two different table and I'm trying to create a case where if the 'A date' is between 90 days from the 'rollment date'. First table Member ID rollment date 1 11/1/2014 2 4/1/2015 3 1/1/2014 4 2/1/2014 5 6/1/2014 Second Table Member ID A A date 1 Yes...

Force date format regardless of culture

c#,sql-server-2008,currentuiculture
I have an issue with certain cultures (Spanish in particular), causing the date string, ToShortDateString(), to come in as dd/mm/yyyy where END_DATE between '" + asOfDate.AddDays(-30).ToShortDateString() + "' and '" + This is causing SQL sever errors because we get incorrect date literal strings such as this: where END_DATE between...

Script to add new columns to table [closed]

sql,sql-server,sql-server-2008,sqlite
how to write a script to add 3 new columns to a stockout table in all databases in sql server

SQL Server DATEPART for year and month

sql,sql-server,sql-server-2008
I have a call in SQL Server 2012 that I need to have working on Server 2005 and 2008. FORMAT(Date, 'yyyyMM') = 201501 is what I am currently using and need to use DATEPART older versions of SQL Server but cannot see a way of doing it easily. Any help...

Access SQL Query Results in Drop Down List

c#,asp.net,sql-server-2008
How can I access 2nd value returned from SQL Query? Obviously, I assign the 1st value returned to my drop down list, but how can I later in my runaquery method get the employeeid value? The below is what I would like to do, but unsure of how to get...

Retrieving Online Users By Login and Logout Records

sql,sql-server,sql-server-2008
Consider UserLog Table as above. I save login or logout operation of users into this table. how can i retrieve Online users and Login Time?...

Use A Sub-Query With A Case Statement

sql-server-2008,case
This may be impossible to achieve, and if so how would I re-write my query to still achieve the same results? I keep getting this error: Msg 130, Level 15, State 1, Line 2 Cannot perform an aggregate function on an expression containing an aggregate or a subquery. And this...

SQL - Max dates

sql,sql-server-2008
Essentially I want to pull the highest [effective date] for each [typecode] in the dataset... this HIC CODE PROVIDER DATE TYPECODE EFFECTIVE DATE 028207696D 10 347330 2014-09-15 00:00:00.000 0 2014-10-10 00:00:00.000 028207696D 10 347330 2014-09-15 00:00:00.000 0 2015-01-30 00:00:00.000 028207696D 10 347330 2014-09-15 00:00:00.000 1 2015-01-23 00:00:00.000 028207696D 10 347330...

How to parse a VARCHAR passed to a stored procedure in SQL Server?

sql,sql-server,sql-server-2008
I have two tables tbl_Products and tbl_Brands, both are joined on BrandId. I have a stored procedure which should return all the products belong to the brand ids passed to it as parameter. My code is as follows. create proc Sp_ReturnPrdoucts @BrandIds varchar(500) = '6,7,8' AS BEGIN SELECT * FROM...

SQL server 2008 - rows are mixed when inserting into another table

sql,sql-server-2008
I have one store procedure and inside that one i used 4 tables(tb1,tb2,tb3,rs1).each table has only one column(x-varchar type).starting of the procedure I deleted the existing rows in the contents first 3 tables except rs1 then I ll insert some 10 to 15 rows in three tables.. End of procedure...

Find Duplicates in SQL Server Table

sql-server-2008,duplicates
Somehow there are duplicate entries in my table. Data structure is set-up like this: RecordID, UserID, clockin, clockout So if one user has 2 entries with the same clockin/clockout time then it is a duplicate. A duplicate would look like this. The entry should only exist once, and the clockin...

SQL Server 2008 - Replace Text Values in Column with Values from Another Table

sql,sql-server,sql-server-2008
I've tried flexing my Google-fu to no avail so here I am! Unfortunately I cannot change anything about these tables as they are coming out of an application that I have to report out of. In SQL Server 2008, I'm trying to replace multiple values in one text string column...

SQL select fields containing uppercase characters

sql,sql-server,sql-server-2008
I have a simple SQL Server 2008 update query using LOWER(FIELD_NAME) that should only update where FIELD_NAME contains uppercase characters. UPDATE TABLE SET FIELD_NAME = LOWER(FIELD_NAME) WHERE FIELD_NAME *contains uppercase characters* How can I select only columns with uppercase characters in the where clause?...

Table design for questionaire

sql,sql-server,sql-server-2008,database-design
We are working on an application which will ask users various questions, these questions can have branches, so they don't run in a straight order. for example, if the below is a structure of questions and where they can flow to: Question 2 has a branch if the user answers...

SQL query for details sum on each [closed]

sql,sql-server,sql-server-2008
I want to display sum of sales for each month and also all the year For e.g. : january 2000 feb 5000 ... decem 4000 And total sum = sum of january to december. for the given year...

How to write a Sql query for retrieving one row from duplicate rows?

sql,sql-server,sql-server-2008
i have a User table which has many users but some users are having same first name and Last Name but only one user will have status active . So my requirement is if the user is unique i need the user regardless of Status but if the user is...

Grouping by similar categoies over time sql

sql-server,sql-server-2008,group-by,group,aggregates
I looked around for awhile, but couldn't find anything. I have a table that looks like this: DATE | Shift | Parts Used 1/1/15 1:15.....1........1 1/1/15 2:06.....1........2 1/1/15 3:45.....1........3 1/1/15 7:33.....2........1 1/1/15 8:14.....2........2 1/1/15 9:00.....2........3 1/1/15 23:01....1........1 1/1/15 23:55....1........2 I would like to group by each individual shift. UNFORTUNATELY shift...

Access filter on time

sql-server,sql-server-2008,ms-access,access-vba,ms-access-2007
I have a table with a time in a datetime field on an MSSQL2k8R2 server. I have linked the table in Access 2007. The table was migrated with the migration assistant from Microsoft. i.e. the Managemantstudio will give on SELECT TOP 3 [AbZeit], [AbBrP] FROM [dbo].[tSollFahrten] the Result AbZeit AbBrP...

How to format % and in 2 decimal points?

sql,sql-server,sql-server-2008
How do I code format the return data in 2 decimals and with percentage format like 100.00% or 67.39% instead of 100.000000 or 67.391304? SUM(qa.scripting1+qa.conduct1+qa.conduct2+qa.conduct3)*100.0/46 as 'C%' I tried ROUND() but I got the error stating that the round function requires 2 to 3 arguments? ROUND(SUM(qa.scripting1+qa.conduct1+qa.conduct2+qa.conduct3)*100.0/46) as 'C%' Thanks!...

The columns in 'table_XXX' do not match an existing primiary key or UNIQUE constraint

sql,sql-server,sql-server-2008
I have 2 simple tables designed as shown below: Users: Categories: Now I would like to set the username as foreign key on categories table so this is what I have done but getting the error as shown below: Am I doing the right way? Or can anyone suggest me...

Merge two SQL query results into one result

sql,sql-server,sql-server-2008,sql-server-2012
I have query like below: SELECT COUNT(*) AS AppleSupports FROM VendorItemPricing WHERE VendorName = 'Apple' SELECT COUNT(*) AS HpSupports FROM VendorItemPricing WHERE VendorName = 'HP' Above queries give me results like below: AppleSupports 63 HpSupports 387 How can make my query to get results in one row like below? AppleSupports...

Get Numbers Range from Table in MSSQL

sql-server,sql-server-2008,sql-server-2008-r2,user-defined-functions
I have a table in MSSQL 2008R2: ID | PinAddress ------------------------------------- 1 | 1 1 | 2 1 | 3 1 | 4 1 | 5 1 | 6 1 | 16 1 | 31 2 | 55 2 | 56 2 | 57 2 | 81 2 | 82...

SQL Server 2008 R2 syntax error

sql-server,sql-server-2008
Could somebody please tell me why this is causing a syntax error in SQL Server 2008 R2? It works fine in SQL Server 2014. SELECT CustomerId, RequestDate, SUM(InitialActualCount + (KeyCardCount * x.Activity)) OVER (PARTITION BY CustomerId ORDER BY RowNumber) AS TotalActualCount, RowNumber FROM (SELECT *, ROW_NUMBER() OVER (PARTITION BY CustomerId...

C++ [ODBC]-MSSQLExpress 2008 SQLSTATE: 28000 || 42000

c++,sql,windows,sql-server-2008,odbc
I'm facing some issues trying devloping a program that has to get informations from database and tables created under MSSQL 2008 management. I have to admit that I'm new to ODBC... If anyone could help me with this issues? I tried two solutions to connect to my database [CodeA] and...

Which is more efficient for Selecting records that 'happened' - from 00:00:00 to 23:59:59 of a date in a another record, in the same JOIN

sql,sql-server,sql-server-2008,tsql
I would like to fetch data for records for a given date/ 1 day interval. This means from 00:00:00 to 23:59:59 of that day. The date value comes from another table in a join statement... i.e, it's not a parameter. Suppose I have a Transactions table with a DateOccured (Datetime)...

Sql inner join with three tables

sql,sql-server-2008
I have this problem, I am a newbie in SQL. I have 3 tables: TBL_PRODUCT: prod_id, prod_type, prod_code, prod_desc TBL_DOCUMENT: doc_id, doc_name, doc_expiry, doc_term, enable_amt TBL_DOCUMENT_GROUP: grp_id, prod_type, doc_type, doc_id For every document, there are corresponding products, one to many. What I can't figure out is how can I display...

Return when 1 value is bigger than another

sql,sql-server-2008
I have a table that contains hardware items. The item and their manufacturer are contained in the table together with a price. I need to return the manufacturer;'s name when one of its items is more expensive that the other. For example, in the table below I only want to...

INSERT single quoted value for all column types in SQL Server

sql-server,sql-server-2008,sql-server-2008-r2,sql-server-2012
Say you have a column of int (also bit, etc.) CREATE TABLE TEST (a int) It seems it doesn't matter if you insert value with '1' or 2 INSERT INTO TEST VALUES ('1') INSERT INTO TEST VALUES (2) Is there any side-effect if I insert/update values by single-quoting them (e.g.'value')...

copy table and drop it

sql,sql-server,sql-server-2008,tsql,stored-procedures
I know we can simply create a new table and copy the old table by doing select * into tbl2 from tbl1 i would like to check if table tbl2 exists, if it does then copy all the rows, if it doesn't then create a new one without having to...

Implement reference key in SQL Server

sql-server,sql-server-2008
I have a TestPack table that can have n no of Lines associated with it. But a Line may exist without a TestPack. Later on, a Line might be assigned a TestPack#. What this relationship is called and how do I implement this relationship in SQL Server 2008?...

get name from another table while using sub query

sql-server,sql-server-2008
i have a query like this: select F_ExhibitionCode, F_ExhibitorCode from T_ExhibitorLocation where F_ExhibitorCode in ( select F_Exhibitor_Code from T_Exhibitor where F_Exhibitor_Name LIKE '%''%') i have two more tables , one is *T_Exhibition* and another one is "T_Exhibitor, F_exhibiton name is coming form T_Exhibition and F_ExhibitorCode is coming from the T_Exhibitor...

Copy SQL column from one to another maintaining ID

sql,sql-server,sql-server-2008
I am looking to copy SQL long binary data from the "photo" column to the "id_photo_c" column. Both columns are in separate tables. A got a query to show exactly what I need, but unfortunately you cannot copy & paste outputs from the "Results Pane" of mssql. I cannot copy...

SQL compute AVG time, but the given time is in NVARCHAR format

sql-server-2008
I'm having some trouble in converting nvarchar to int. What I want to do is to get the longest and shortest time of a column and then compute the AVG time, however the column is nvarchar....

How to use OR in join while using Entity Framework

entity-framework,sql-server-2008
Can any one convert following SQL statement to Entity Framework C# or VB.net for me? SQL statement: select t1.*, t2.* from tblWISTransacs t1 inner join tblWCBTransacs t2 on t1.TicketNo = t2.TicketNo or t1.TicketNo = t2.customernumber ...