Tag: tsql

How to create a new database and tables inside it in SQL Server?

I’m using T-SQL in order to create a database, and then populate it with tables. The point is that I can create the database successfully, but then when I create the tables it adds them inside the master database, not the newly created one. Here is the code segment that I have: USE master; GO […]

SSRS Reporting – Multiple Selections Defining Multiple Queries

An SSRS dataset has two T-SQL queries. One query will only run if a certain parameter has two or more values selected. The other query will only run if the parameter has only one value selected. How can I let the dataset know whether one value or multiple values were selected from the parameter?

SQL Server: how to include count in the select query with where clause?

Let’s say I have this table: id out flag — — — 1 0 1 1 1 0 1 4 0 2 0 1 2 2 0 2 2 0 3 0 1 3 2 0 3 1 0 3 4 0 I want to count the number of rows with out=(0, 2, or 4) […]

ALTER COLUMN when table depends on the column

I’m trying to alter a column type to increase its size but SQL gives me the following message Msg 5074, Level 16, State 1, Line 1 The object ‘table_name’ is dependent on column ‘column_name’. Msg 4922, Level 16, State 9, Line 1 ALTER TABLE ALTER COLUMN column_name failed because one or more objects access this […]

Pass current month date in parameter and get get prior month data

I tried this but no desired result in where clause Convert(varchar(7), dateadd(mm, -1, getdate()), 120) =Convert(varchar(7), @Paramtername, 120)

Translate SELECT DISTINCT t-sql query to DAX expression

I need to create calculate table for the report in PowerBI Desktop. I know how to do that in t-sql but I am unable to interpret it to DAX. So should I use t-sql and add this query using “Get Data”? Or should I create calculate table using DAX? Which one is more efficient? select […]

SQL Server Write Permission but not delete

I’m pretty sure the answer is that once I grant write/alter access it explicitly gives the person access to delete.

Query XML in SQL Server

I have a XML formatted like this: <Maximum.Edm.CodeIncludedInCalculation > <CalculationsByProvince> <Maximum.Edm.IncludedInByProvince Province=”QC”> <RevenueCodeCalculations CalculationListType=”RevenueCode”> <Maximum.Edm.CalculationIncluded Code=”@Vacation” IsSelected=”True” /> <Maximum.Edm.CalculationIncluded Code=”@RRQ” IsSelected=”True” /> <Maximum.Edm.CalculationIncluded Code=”@EmploymentInsurance” IsSelected=”True” /> […] </RevenueCodeCalculations> <TaxableBenefitCalculations CalculationListType=”TaxableBenefits”> <Maximum.Edm.CalculationIncluded Code=”1″ IsSelected=”False” /> <Maximum.Edm.CalculationIncluded Code=”AV. VIE FE” IsSelected=”True” /> </TaxableBenefitCalculations> <DeductionCodeCalculations CalculationListType=”DeductionCode”> <Maximum.Edm.CalculationIncluded Code=”123″ IsSelected=”False” /> <Maximum.Edm.CalculationIncluded Code=”456″ IsSelected=”True” /> <Maximum.Edm.CalculationIncluded Code=”AC” IsSelected=”False” /> […]

How to increment column ordinal position in a while function?

Abstract: I have a table with multiple columns and I am trying to implement a while (loop) function to all the columns to pull out the average of each column. I coded this to attribute a number to each column (to ease the possibility of an incremental method) Declare @tablename as varchar(128) Declare @column1 as […]

Hierarchical structure, new columns, denormalization

Suppose I have table like this: id parent_id name 11 NULL Company 33 11 Department 1 44 33 Department 2 I would like to transform it into: id parent_id name Level1 Level2 Level3 11 NULL Company NULL NULL NULL 22 11 Company Department 1 NULL NULL 33 22 Company Department 1 Department 2 NULL I […]

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