Tag: sql-server-2012

Make Unique int column that allows NULLS in SSMS

How can I make an int column unique – on an existing table with data in – but allow multiple nulls – in SSMS. There are many records in the table now – and they all have NULL in this column now. I have seen ways to do this using a unique filtered index in […]

How do I create a temporary table for a dynamic query so that I can BCP the output

I am trying to write a stored procedure that executes all .sql files that are in a folder, and then outputs the results to CSV files. My code: DECLARE @Table table( [FileName] varchar(100), depth int, isFile int ) DECLARE @Result int DECLARE @FileName VARCHAR(100) DECLARE @exportFile VARCHAR(100) DECLARE @ExportPath VARCHAR(100) DECLARE @SQLText VARCHAR(MAX) DECLARE @FilePath […]

update unique id to table base on phone or email address in sql server 2008 r2

currently i have following table structure in sql server2008 r2 tbusertable userid username uid status 1 abc null null 2 yax null null 3 xcd null null 4 max null null 5 wax null null 6 ear null null 7 yes null null 8 sqt null null 9 ora null null tbphtable pid userid phnos […]

Formatting YYYYMM to MMMYYYY in SSRS

I would like to to format one of my data fields to be in the date format of MMM YYYY. For example, if “201209” is being returned, then I would like it to be displayed as Sep 2012. On the SQL side I am using a CAST to only look at the Year and Month, […]

Date Range Based on Today

I am trying to write a stored procedure that will be called by an automated report on the 1st, 8th, 15th, and 22nd of each month. Below I have the code I am currently trying to use however I keep getting the error Explicit conversion from data type int to date is not allowed. on […]

Original data type reference when using Sql_Variant

I am creating a table with an SQLVariant data type. The values stored within the variant column could be string, integer, and/or datetime. I read about the pitfalls of sqlvariant, but as the column isn’t being indexed, or used in WHERE clauses, it seems the best choice. However, I need to store a reference to […]

Select data by applying group by on one field and order by other field

I am facing one issue while fetching record set from my table say PostCodeDistances. Here is the source table: SourceId | PostCode | Distance ——– | ———-| ——— 1 | 200 | 4000 1 | 300 | 2000 1 | 400 | 1000 2 | 300 | 5000 2 | 400 | 3000 2 | […]

Query with wildcard and mandatory clauses

I have two tables, TableA and TableB which has to be join in such a way ColumnA is mandatory to match ColumnB and ColumnC can match (if not then should be considered as wildcard) I have written below query for this SELECT * FROM TableA JOIN TableB ON (TableA.ColumnA = TableB.ColumnA) AND (TableA.ColumnB = TableB.ColumnB […]

Convert start and end dates to normalized table

I have a table with customer records, with each customer having a start date and end date. I’m looking for the most efficient way to convert this into a table that counts the number of active customers for each day. For example: Existing table (Table A): Customer – Start Date – End Date A – […]

How to pivot, link and group a table with

I have a table beta with cols Id, Parent, Name, Level CREATE TABLE [beta]( [Id] [int] IDENTITY(1,1) NOT NULL, [Parent] [int] NULL, [Name] [varchar](150) NOT NULL, [Level] [int] NULL, CONSTRAINT [PK_Beta] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] […]

MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.