Tag: sql-server-2012

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] […]

Move Columns to next if null

I want to move color forward to next column if the required columns are null however I used COALESCE function but I am unable to get expected result Below is my table structure ID. Level1. Level2. Level3 Level5 1. Xyz. Null. Null. Null 2. Abc. Xyz. Null. Null 3. Mno. Abc. Xyz. Null 4. Ggg. […]

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