FAQ Database Discussion Community


Return Data from Table Valued Function with Joins

sql,sql-server,stored-procedures,sql-server-2005
I have a query which contain table valued function. But when executing this attached error comming. How can I solve this? SELECT bg.Description, im.ERPItemCode, (select Opening,Topup1,Topup2,Topup3, Returnsv,SaleQty,SaleVal from dbo.GetOpeningByRepAcc(@Date,@RepAccId,@Cente![enter image description here][2]rId,im.Id)), (select Name from dbo.DistributionCenter where Id = @CenterId) as center, (select Name from dbo.SalesRepAcc where Id = @RepAccId)as...

Multiple rows are not showing when we convert the table data into XML in SQL

sql-server-2008,sql-server-2005,sql-server-2008-r2,sql-server-2012
I have two table as given below. OrderHeader: PKOrderHeader CustomerCode DocumentRef SiteCode 1 JOE TEST1 TH 2 POL TEST2 CO 3 GEO TEST3 KH OrderDetails: FKOrderHeader ProductCode RotationLineNo 1 PRD1 1 1 PRD2 2 2 PRD3 2 3 PRD4 3 I need to get the XML string as below after...

How to add leading zeros to my data?

sql,sql-server,sql-server-2005
I have a varchar column that has variations of following data: WKKT-FM, 2/21 WKKT-FM, 1/24-2/14 WKKT-FM, 3/14-3/21, 4/11-4/18 IKKT-FM, 12/29-1/12, 1/26-2/09, 6/01-6/15 And so on. I need to place leading zeros in front of days and months. As you can see some of them already have leading zeros. I tried...

Why I'm getting exception: InvalidOperationException?

c#,sql-server-2005,ado.net
The Exception is on the code The code is string cs=ConfigurationManager.ConnectionStrings["Sam"].ConnectionString; using(SqlConnection con = new SqlConnection(cs)) { SqlCommand cmd = new SqlCommand("Select * from tblProduct", con); con.Open(); SqlDataReader rdr = cmd.ExecuteReader(); GridView1.DataSource = rdr; GridView1.DataBind(); } So I have a one web-Config file.so I am connect with it. The code...

SQL Server : Query criteria - need to extract date from datetime

sql,sql-server-2005
I can't for the life of me get this to work. I've tried just about every example I've seen on here and all over the internet. I'm trying to query based on a month's worth of data. The data in the view I'm querying is in this format: 2012-03-20 00:00:00.000...

Delete Data From Table of Specific Month

sql,sql-server-2005
In My Table, I Have a Column Which Stores the Date. I am Passing Month Number as an argument to a stored Procedure. I would like to delete all entries of that month from table. Is it Possible....??...

Dotnetnuke migration from SQL 2005 to SQL 2012

sql-server-2005,sql-server-2012,dotnetnuke,database-migration
I'm trying to upgrade my DNN v6 from a SQL Server 2005 to a SQL Server 2012. My problem is, after modifying the web.config to match the new appSettings, my website automaticaly runs the install wizard. My IIS was running on a 2003 server and is now on a 2012...

Does JOOQ Support SQL Server 2005 or SQL Server 2008 R2 in Code Generator?

sql-server,sql-server-2008,sql-server-2005,sql-server-2008-r2,jooq
I've been trying to auto-generate artefacts for my SQL Server Database. Its originally from SQL Server 2005, and then I didnt find in the documentation of JOOQ, so i imported it on a 2008 R2. But then its still says the same thing: 1428 [main] WARN org.jooq.util.AbstractDatabase - No schemata...

Stored Procedure in SQL 2008 Not Working in 2005

stored-procedures,sql-server-2005,sql-server-2008-r2
Hi I'm Trying to Create Stored Procedured in SQL2005 below is my syntax CREATE PROCEDURE [dbo].[POBalance] @PONumber nvarchar(50) AS BEGIN declare @status bit = (Select status from tblPOHeader where PONo = @PONumber ) if @status = 'False' Select A.Description, C.qty as POqty, B.QtyDelivered as PDQty, case when A.partialflag ='false' then...

Second maximum value by using dense rank function

sql,sql-server-2005
I have table like: name marks raja 88 ravi 88 karthik 99 praveen 99 vijay 70 In that table I assume the rank of the table is: name marks rank karthirk 99 1 praveen 99 2 raja 88 3 ravi 88 4 vijay 77 5 I get that by using...

Not IN() Innaccuracy

sql-server-2005
I want to view a list of zip codes that are in list1 but not in list2 so that I can see which area was hit with which mailing (1st or 2nd). I have tried the below query but it is returning 0 records, and after a manual line by...

Script to concatenate columns and remove leading/trailing delimiter

sql,tsql,sql-server-2005,relational-database
I have a table like this and I want to return concatenated strings where the column values are in ('01', '02', '03', '04', '99'). Plus the values will be delimited by a ';'. So row 1 will be 01;04, row 3 will be 01;02;03;04 and row 5 will simply be...

How to change output of stored procedure on the fly in this query?

sql,sql-server-2005
I am trying to achieve something which I think is simply, but yet the syntax escapes me Here's the query: SET @T02_IDENTIFIANT_DSC = 'Hello ' + @Compagnie + ' ' INSERT INTO @TableDonnees EXEC THE_DATABASE..spHeader @ValDate, @ValDate, '65', @T02_IDENTIFIANT_DSC, @RecCount spHeader returns a record with two rows. How could I...

sqlsrv_query doesn't return false on faulty T-SQL query

php,sql-server-2005,error-handling,sqlsrv
I'm using sqlsrv to connect to a MSSQL database. A feature in a secured (and only available to a few people) intranet application provides a box to write T-SQL code and execute them to the database. At this moment I'm working on a feature to test the given query. First...

SQL Server WHERE Clause with optional parameter not working as expected

sql-server,sql-server-2008,sql-server-2005
I have a query shown below. The parameter @company is optional from .NET code. When I pass the value, it is working as expected and bringing matching results for this @company. When I don't pass a value, it should return more rows, for all all other companies also. Somehow it...

How to get latest record weekly within an SQL statement

sql,sql-server,date,sql-server-2005
I'm trying to export the latest records from SQL Server 2005 database once weekly. This is my table: agent_name date ID ALEX 2015-05-25 13 ALEX 2015-05-22 13 ALICE 2015-05-24 10 ALICE 2015-05-26 10 How to create output table should like this: agent_name date ID ALEX 2015-05-25 13 ALICE 2015-05-26 10...

How to run a subquery based on results of a query SQL

sql,sql-server,sql-server-2008,sql-server-2005
I have 2 queries i'd like to run. The idea here is to run a query on the transaction table by the transaction "type". Based on these results, I want to run another query to see the customers last transaction based on a specific type to see if the service...

ASP DataGrid - Must declare the scalar variable “@LCompanyIDInt” [closed]

sql,asp.net,vb.net,sql-server-2005,datagrid
I have an application I'm working on that have multiple grid views on panels, and those panels are selected by a dropdown list. The first one I'll put here works perfectly. I have a second grid set up exactly the same (I think) as the first. I've changed variable names...

WHY? UNION ALL is doing a Calculation

sql,sql-server,tsql,sql-server-2005
I have two data sets: DS_A and DS_B. My question is why I have two quantity for pro_id 71549 and not three? The ALL Incorporates all rows into the results. This includes duplicates. If not specified, duplicate rows are removed. loc_id pro_id quantity price ------------- ----------- ----------- ----------- 2310 5052...

Create datetime2 as custom datatype in SQL Server 2005

sql-server,sql-server-2005,types,datetime2
Many have come across the fact that SQL Server 2005 doesn't support datetime2. I was wondering if I can add it as a custom datatype instead. I created a custom type with the name datetime2 so that's done. Now I need to set the min date value, but is that...

Why TSQL convert a function's result in one way and a character string to other way?

sql,tsql,sql-server-2005,binary,md5
I try this command in SQL Server 2005 to obtain a MD5 from '123': select SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', '123' )), 3, 32) and I get this result: 202cb962ac59075b964b07152d234b70 I want to convert to binary format, select convert(varbinary(16), SUBSTRING(sys.fn_sqlvarbasetostr(HASHBYTES('MD5', '123')), 3, 32)) And I get this result: 0x32003000320063006200390036003200 Why does this code: select...

SqlClient.SqlException (0x80131904) The identifier that starts with

vb.net,sql-server-2005
I am using stored procedure as below anyhow for some files i am retreiving following error. What could be the reason?: System.Data.SqlClient.SqlException (0x80131904): The identifier that starts with 'D:\DISKSEC\TETRAIBMSolution\TETRA\Data\IDJSKDOSD\Parsed\IOPDCVv3995ReportErgfdgfgdfIOPShun_e6c5b926-fb91-484e-bfe0-9c13a96f' is too long. Maximum length is 128. Stored procedure i am using: ALTER PROCEDURE [dbo].[BulkInsertDelimeted] @InputFileName Varchar(700), @TableName Varchar(200) AS BEGIN...

How can I test the syntax on SQL Server 2005 without install it

sql,sql-server,sql-server-2008,sql-server-2005,version
I am using SQL Server 2008. Everything goes right in my local development. But when I deploy the program and the stored procedure into client workstation which is using SQL Server 2005, errors are showing up. I believe it is caused by the syntax problem between SQL Server 2005 and...

Distinct Clause not working in SQL Server 2005

sql,sql-server-2005
I have so many records having duplicate taskid assigned to multiple person, but i want to show distinct records means only one taskid in output in SQL Below is my query not working give me solution SELECT DISTINCT taskid, taskname, person, userid, dept, date, status, monitor, comments, monitor_comments, respondtime, assignedby,...

Select nth to nth row while table still have values unselected with python and pyodbc

python-2.7,sql-server-2005,rabbitmq,pyodbc,python-pika
I have a table with 10,000 rows and I want to select the first 1000 rows and then select again and this time, the next set of rows, which is 1001-2001. I am using the BETWEEN clause in order to select the range of values. I can also increment the...

store sql result in a variable in python

python,sql-server-2005
I need help in storing the query result in a variable after executing the select statement. this is my code below.. there are no error but i need help in storing it in a variable in PYTHON. i am using pymssql and python 2.7.9. thanks in advance conn = pymssql.connect(host='localhost',...

Different order by clause for different columns

sql-server,sql-server-2005
I hope my title made sense, here's a sample of my table. My table has 1 column for a unique product barcode, and 4 date columns which is for storing what date and time a product passed an inspection area(4 of them), i removed the date in my example so...

Multiple joins on same table?

sql,sql-server,join,sql-server-2005
I have two table: tbl_EmploymentSegmentEM: ╔══════╦═════════════╦════════════╦═══════════════╦═══════════════════════════════╦════════════╦═════════════╦══════════════════════════╦════════════════╗...

Total Minutes by Hour

sql-server,sql-server-2005
I have two fields in a data table - "startTime" and "endTime." These two fields represent a duration of time the user spent on a particular task. These are varchar fields. So, let's say we have a startTime of "21:05:00" and an endTime of "22:09:00." I need the code to...

Inserting concatenate Identity column with other column

sql-server,sql-server-2008,sql-server-2005,sql-server-2012
I have a identity column and i have other column while inserting a new row in table i need to insert into third column with concatenate of two columns result For reference please see below table ------------------------------------------------ A | B | c ---------------------------------------------- 1 | 33 | 133(1 [identity result]...

Steps to upgrade database from SQL Server 2005 to SQL Server 2012

sql-server-2005,sql-server-2012
Could anyone please help me out in knowing the step by step process to be done to upgrade the database from SQL Server 2005 to SQL Server 2012? I already surfed about the process but I am not able to understand the complete idea and I don't know on how...

How break a time range between n numbers of equal intervals?

sql,sql-server,tsql,sql-server-2005
TimeMin: 2015-04-29 10:57:56.623 TimeMax: 2015-04-29 11:04:35.133 I am trying to write a select query to break this into n equal intervals This is my attempt: declare @Min int select @Min = min(DATEDIFF(ss,'1970-01-01', biddate)) from tbl_bids declare @Max int select @Max = max(DATEDIFF(ss,'1970-01-01', biddate)) from tbl_bids declare @NumParts int select @NumParts...

SQL - Select first n occurrences of each value

sql,sql-server,tsql,sql-server-2005
Let's say I have a table like so, only with thousands of records. | Foo | Bar | | 1 | A_1 | | 1 | A_2 | | 2 | B_1 | | 3 | B_1 | | 3 | B_2 | | 3 | B_1 | | 4...

Get Parent and grand parents of a particular child

sql-server,sql-server-2005,sql-cte
i have table like below tablename :ExampleTable ChildID ChildCommonID ParentID 1 2 0 2 3 0 3 4 1 4 5 3 5 6 4 The Problem is : i have a child id example :ChildID= 5 so i need to check wheather it has a parent or not if...

SSRS 2005 Subscription changes

sql-server-2005,reporting-services,subscriptions
OK this is my question(s) and its SSRS 2005 and SQL Server 2005/2008 I had been tasked with rebuilding a dozen or so reports that our users use on their data systems. We just build them and since every DB instance is schematically the same for all our clients, we...

View as table with range od date for each record

sql,for-loop,sql-server-2005,view,date-range
I have table employees with columns surname, names, birth date etc. What I want to have is a table that for each row in employees table have range of dates, for example: surname0 , day()+0 surname0 , day()+1 surname0 , day()+2 ................. surname0 , day()+30 surname1 , day()+0 surname1 ,...

SQL - Add Data to Existing Data From Another Table

sql,sql-server-2005
I have a temp table @table in my stored procedure that looks like this: AgtID | Bonus ------------- 5063 | 0 1104 | 0 And a table bonus that looks like this: AgtID | Contest | Points | Event -------------------------------- 5063 | 21 | 1000 | 1 5063 | 21...

Show Query Results in Month/Year Order

sql-server-2005
I have a query that sales and it shows the month and year (field names are salemonth & saleyear) of the sale. Example return-set would be January 2014 February 2014 March 2014 December 2014 January 2015 Now obviously I can't set it that way in my straight query as if...

XML string to xml unable to switch encoding

sql-server,xml,sql-server-2005,encoding
I have an XML string that is malformed. DECLARE @xmlt TABLE(xstr nvarchar(max), xml xml) INSERT INTO @xmlt(xstr) VALUES ( ' <?xml version="1.0" encoding="windows-1257" ?> - <objects><object id="778913">a</object> - <object id="785491">b</object> - <object ...goes on... - </objects> ' To be able to use that XML I convert it to XML UPDATE...

Sum across columns and rows

sql,sql-server,sql-server-2005,sum,sybase-ase
Consider a table like this table +--------+---------+-----------+---------+-----------+ | BookId | ItemId1 | Quantity1 | ItemId2 | Quantity2 | +--------+---------+-----------+---------+-----------+ | 1 | 1 | 2 | 2 | 1 | | 1 | 3 | 1 | 2 | 1 | | 2 | 1 | 1 | 2 |...

How to Calculate Gap Between two Dates in SQL Server 2005?

tsql,sql-server-2005
I have a data set as shown in the picture. I am trying to get the date difference between eligenddate (First row) and eligstartdate (second row). I would really appreciate any suggestions. Thank you ...

SQL Server - cumulative sum on overlapping data - getting date that sum reaches a given value

sql-server,sql-server-2005,sum,overlapping
In our company, our clients perform various activities that we log in different tables - Interview attendance, Course Attendance, and other general activities. I have a database view that unions data from all of these tables giving us the ActivityView that looks like this. As you can see some activities...

Scheduled SQL Server Data Export to CSV using SQLCMD

sql-server-2005,cmd,server,bcp,sqlcmd
This is my command line: sqlcmd -S DEVSERVER\SQLEXPRESS -i c:\SQL_Query.sql -o c:\CSV_Output.csv -s”,” And my sql script: SELECT * FROM dbo.pay The file with this error message: Msg 208, Level 16, State 1, Server DEVSERVER\SQLEXPRESS, Line 1 Invalid object name 'dbo.pay'. ...

How do I return records based on which records only exist during a certain time period?

sql-server-2005
I've got a table of invoices and customer codes. I'd like to pull only the customers that have not done any business with us since 2009. My regular select statement is simple SELECT INVOICE.CUSTOMER_CODE FROM INVOICES WHERE INVOICES.ORDER_DATE <= '01-01-2010' But, this statement also pulls customer codes of customers who...

SQL statement to select from 2 different tables, from two different databases (same server)

sql,sql-server,sql-server-2005
How do I select from multiple tables in different databases on the same server? Also, Is there a way to have an identifying marker so I could see where the results came from? So for example: SELECT db1.table1.name, db2.table2.name, fromTbl FROM db1.table1, db2.table2 WHERE db1.table1.name LIKE '%j%' OR db2.table2.name LIKE...

How do I match a substring of variable length?

sql-server,regex,sql-server-2005
I am importing data into my SQL database from an Excel spreadsheet. The imp table is the imported data, the app table is the existing database table. app.ReceiptId is formatted as "A" followed by some numbers. Formerly it was 4 digits, but now it may be 4 or 5 digits....

How to identify the binary compression type from the content?

c#,sql-server-2005,compression,gzip,binary-data
I am trying to restore files which are stored in MS SQL database (used by third party application which has stopped their support) as an image data type(byte arrays). So what I do is to write those rows of byte arrays to file to convert for know file extensions. However...

Concatenate column values in SQL Server

sql,asp.net,sql-server,vb.net,sql-server-2005
I want to select two columns (first and last name) from a database, combine them into one, and stick them into a data set to be displayed in a datagrid. I also need to add a space between them for formatting. My normal SQL statement: SELECT first_name + ' '...

Query to get report name, reports first and last run time and date, how often it was run and method of delivery (email or locations)

tsql,sql-server-2005,reportingservices-2005
I am new to sql server. I have been trying to write a query to get report name, reports first and last run time and date, scheduled by ,how often it was run and method of delivery (email or locations) for SQL Server 2005. Please help me with this....

Populate new ID Column with Sequential Identifiers

sql-server,tsql,sql-server-2005
I need to populate a column nvarchar(12) with a unique sequence starting at "PM1000000000" and increment by 1 for each row. There is no identity column or Primary key (edit) to loop around which makes this problem quite challenging as most of the example I found use an identity column...

How to generate tables in MS SQL?

sql-server,sql-server-2008,sql-server-2005
I need to generate tables like 'test_table_1', 'test_table_2'... What I am doing wrong? declare @i int = 1; declare @i_str varchar(1024); declare @table VARCHAR(1024); while (@i < 500) BEGIN SET @i = @i + 1; SET @table = 'TEST_TABLE_' + convert(varchar, @i); IF NOT EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA...

Reduce SQL query execution time

sql-server,sql-server-2005,crystal-reports
I have a situation where our web application generates a Crystal Report based on the query result which takes almost 1 hr to execute. I know this is crazy but, can anyone suggest any optimization or changes in the following giant query. Thanks. Note: I have shorten some of the...

Complex sorting based on next and previous records in SQL

sql,sql-server,sql-server-2005
This is a follow-up question on Sorting based on next and previous records in SQL But now it gets a little more complex, for example: If any letter of 1 matches any letter of 2, I want to change the ordering, so that the letter matches with the following record....

Todays latest and yesterdays latest record

sql,sql-server,sql-server-2008,sql-server-2005,sql-server-2012
I have a table with code and Date Code Date ---------------------------- A1 21 May 2015 15:47 A2 21 May 2015 10:30 A3 20 May 2015 10:30 A4 21 May 2015 10:30 A1 19 May 2015 15:20 A2 21 May 2015 12:30 A3 19 May 2015 05:30 A4 18 May 2015...

SQL Server 2000 query conversion [closed]

sql,sql-server,sql-server-2005,sql-server-2000
I have a query written in SQL Server 2000 and I want to re-write that to use in SQL Server 2005 and newer versions. CREATE TABLE #TEMP(AffinityGroupName VarChar(100), MostPopularVehicle VarChar(100), LeadCount Int, CNTL_ID int, CNTT_ID int, DayTime datetime, MemberFirstName varchar(50), MemberLastName varchar(50), MemberAddress1 varchar(150), MemberAddress2 varchar(150), MemberCity varchar(50), MemberState varchar(2),...

getting the range of a number column(min/max) if there are missing numbers in between

sql,sql-server,sql-server-2005
How would I query the range of a number column if the number ends somewhere then picks up again at a higher number? If I had a column like: Number ------- 1 2 3 4 5 11 12 13 How can I return a result like Min | Max ----------...

How to get manager,employee pair output?

sql,sql-server,sql-server-2005
I just require Required output as i posted below.This question asked in an interview. Table Structure: create table #test ( id int, emp char, roles char ) insert into #test values(1,'A','M') insert into #test values(2,'B','E') insert into #test values(3,'C','E') insert into #test values(4,'D','M') insert into #test values(5,'E','E') insert into #test...

An INSERT INTO in MSSQL Server 2005 that groups by a column leads to unexpected duplicate key violation

sql-server,stored-procedures,sql-server-2005,unique-constraint
I am executing a stored procedure on Microsoft SQL Server 2005. The code in question has worked flawlessly for about 12 months, but is now failing on "duplicate key violation error" How is it possible to get a duplicate key violation error with the following? This is the error: Violation...

SQL Query to SUM data from 3 different tables

sql,sql-server-2005
I have 3 SQL Server 2005 tables, an order table, quote table and invoice table, structured like this: SO table Rep Code SO Amount SO Date ---------------------------- 1 100 1/2/2015 2 50 2/15/2015 3 20 2/20/2015 2 50 2/20/2015 1 85 2/20/2015 SQ table Rep Code SQ Amount SQ Date...

Sum Colum case () after pivot

sql-server,sql-server-2005,pivot-table
Sorry if I don´t explain very well, and the title isn´t very clear. I´m using SQL Server 2005. I have a query with Pivot that is working fine, but know I must add a new query that get resuls from quarterly. This is my query to get result from Month...

Calculate extra column in group by

sql-server,sql-server-2005,group
In sure this should be simple, but I'm having a brain fart over it. In SQL Server 2005, how do you add an "extra count" to a GROUP BY query? Consider the following... ;WITH DATA AS ( SELECT 1 AS ID, 1 AS TYPEID, 0 AS ACTIONNEEDED UNION SELECT 2...

SQL Insert Date Mystery on 2012 from 2005

sql-server,sql-server-2005,sql-server-2012
I have migrated a 2005 db to 2012 There is a stored procedure that run to insert todays date into a table IF NOT EXISTS(SELECT * FROM tblTime WHERE [Day] = DATEPART(day,GETDATE()) AND [Month] = DATEPART(month,GETDATE()) AND [Year]= DATEPART(year,GETDATE()) ) BEGIN INSERT INTO tblTime (Period,[Day],[Month],[Year],MonthPrefix) VALUES (CONVERT(VARCHAR(10), GETDATE(), 105), DATEPART(day,GETDATE()),...

ASCII error when fetching results from database with pyodbc and sql server 2005

python,sql-server-2005
I have this error when fetching results from the database. UnicodeEncodeError: 'ascii' codec can't encode character u'\xf1' in position 3: ordinal not in range(128) Upon closer inspection, I found out that several of the records have ñ and Ñ in them. And I cannot just replace them or ignore them...

I want to select random questions from a Table from each catogories

sql-server,database,sql-server-2005,random
SQL Server 2005 query needed, please help. I have a table which have questions and all questions are categorized TopicWise wise. And another table which Contains topic and its weightage of number in the Subject Now, I need to select 50 random questions from Table QUESTIONS which are based on...

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 |...

I've lost a scalar function in Access 2010 - how do I put it back? (I have the function in SQL)

function,sql-server-2005,ms-access-2010
I have a form in Access 2010 (.adp, not .accdb) that references a function titled dbo.search which seems to be missing. I am getting this error: Cannot find either column "dbo" or the user-defined function or aggregate "dbo.search", or the name is ambiguous. I cannot find the function "search" in...

MSSQL - Select distinct products that are found in two columns

sql,sql-server,database,sql-server-2008,sql-server-2005
Suppose I have the following combinations in my dataset: **ProductA** **ProductB** Apple Banana Apple Orange Apple Pear Banana Orange Banana Pear Orange Pear How would I return a complete list of unique products in a single column? Desired output below: **Products** Apple Banana Orange Pear If I do select distinct,...

SQL Insert with Select and OUTPUT to create a temp copy table

sql,sql-server,sql-server-2005
How do I make a copy of the inserted records and keep the original id of the record it copied and put it in the Temp table. When I add Docs.DocID to the OUTPUT I get. "The multi-part identifier "Docs.DocID" could not be bound." DECLARE @CopiedDocIDs TABLE(NewDocID int, CurrentDocID int)...

How do I get Sum of two Colunms with Same Foreign Key

sql,sql-server-2005
I have three tables: Carrier(C), DropShipper(D) and ShoppingCart(S) with the following schema Table C c.id(Pk,int,not null) c.dropshipperid(Fk,int,not null) c.Prodid(int, not null c.cost(money null) Table D D.Dropshipperid(Pk,int,not null) D.Dropshipper(nvarchar(50)) D.Remarks(nvarchar(50)) Table S s.cartid(PK, char(36)) s.prodid(pk,fk,int not null) s.qty(int not null) Here are sample data: c.id c.dropshipperid c.prodid c.cost -------------------------------------------- 1 1...

SQL Server Filtering by DateTime column, when TIME portion is provided sometimes

datetime,sql-server-2005,where-clause
In an SSRS report, the user searches based on start date and end date. The challenge is, as I discovered recently, he sometimes, not always, provides the time component while searching. Currently, the filter is done like this: if @pEndDate is null SET @pEndDate = getdate() SET @PEndDate = DateAdd(dd,1,@PEndDate)...

Different SELECT's for an INSERT INTO

sql,sql-server-2005
I'm trying to have an IF block after an INSERT INTO statement and depending on some conditions, execute a different SELECT that will feed the INSERT INTO. Here's a (failing) example of what I'm trying to do: INSERT INTO #TempTable (COL1, COL2, COL3) IF @VAR = 'YES' BEGIN SELECT *...

SQL Server DATEDIFF keeps ignoring seconds part of date

sql,sql-server,tsql,sql-server-2005
I am getting 0 on executing the following statement: SELECT DATEDIFF(mi,'1970-01-01 00:00:00','1970-01-01 00:00:01') * CONVERT(BIGINT,60)*1000 as BidTicks Whereas I get 6000 on executing this: SELECT DATEDIFF(mi,'1970-01-01 00:00:00','1970-01-01 00:01:01') * CONVERT(BIGINT,60)*1000 as BidTicks What are my options?...

Set-based approach to applying payments to bills

sql,sql-server,sql-server-2005
I'm trying to show payments applied to bills and I'd like to know if there is a way to do this without using cursors and imperative logic. I have a Bills table and Payments table. Payments are not always in the amounts of the bills, sometimes over, sometimes under. I'm...

Regular expressions in TSQL

sql-server,tsql,sql-server-2005
In cells of column e_vis_name I have organization structure where divisions divided with \ symbol, e.g. Moscow\Direction Yaroslavl\Sales Omsk\Commercial center\Sales I need to cut everything after first \ symbol to get the following result: Moscow Yaroslavl Omsk How can I do it?...

UNION ALL on 2 tables select with Cases

sql,sql-server,sql-server-2005,union-all
I'm running SQL Server 2005. I have 2 tables with the same columns but holding very different data. SELECT * FROM Table1 WHERE ItemID IN ('4','2','1') ORDER BY CASE WHEN ItemID = 4 then 1 WHEN ItemID = 2 then 2 WHEN ItemID = 1 then 3 END UNION ALL...

Sorting based on next and previous records in SQL

sql,sql-server,sql-server-2005
I am trying to order a specific query by taking the next and previous records into account, but I can't seem to get it done. I would like to order by a number and a letter, but if, for example, the last letter of number 1 is equal to one...