Tag: tsql

Add total row to bottom of SQL Query

I have an SQL Statement that gets daily sales, but I would like to total these rows, possibly with the use of CTE. My code is as follows, I tried using GROUPING and ROLLUP but to no avail. Any help is much appreciated! DECLARE @StartDate NVARCHAR(MAX) = ‘20161101’ DECLARE @FinishDate NVARCHAR(MAX) = ‘20161102’ SELECT salesquery.Department, […]

SQL View based on a xml column

My problem is to build a SQL view based on a XML column. My current table has two columns. One for timestamp and second for data. The data-column is from XML datatype and the content is: <data> <entry header =”Parameter1″ value=”1″ type=”System.Boolean”/> <entry header =”Parameter2″ value=”0″ type=”System.Boolean”/> <entry header =”Parameter3″ value=”140″ type=”System.int16″/> <entry header =”Parameter4″ […]

Finding the Datediff between Records in same Table

IP QID ScanDate Rank 101.110.32.80 6 2016-09-28 18:33:21.000 3 101.110.32.80 6 2016-08-28 18:33:21.000 2 101.110.32.80 6 2016-05-30 00:30:33.000 1 I have a Table with certain records, grouped by Ipaddress and QID.. My requirement is to find out which record missed the sequence in the date column or other words the date difference is more than […]

Need for Reorganize/Rebuild indexes after frequent DELETE?

I have a table with 40 million rows (and growing) with an identity primary key clustered index and a non-clustered index. I often need to Delete old or obsolete information. Because this is a frequent action, should I care about fragmentation and plan to take some maintenance on the indexes to keep performance? What should […]

SQL Server output and return value in procedure

I’m new to SQL Server, and i have this error that i was not able to fix: Msg 245, Level 16, State 1, Procedure info, Line 11 Conversion failed when converting the varchar value ‘Steven’ to data type int Can you please show me where is the error, and how can I fix it? The […]

Using pivot in tsql

Let’s say I have this table: Name Occupation Samantha Doctor Julia Actor Maria Actor Meera Singer Ashely Professor Ketty Professor Christeen Professor Jane Actor Jenny Doctor Priya Singer I want to have this output: Doctor Professor Singer Actor Jenny Ashley Meera Jane Samantha Christeen Priya Julia NULL Ketty NULL Maria I want to convert the […]

Local Variable not showing all the values

I am new to programming so I do not know how this question to ask. what I am doing is firstly finding @userid value on different query, then after when I do select a,b from tableA where userid = @userid and active=1 and payments=1 then it executes to show me number of rows(lets say:10 rows) […]

how can I use a sproc @param as column alias?

How can use a sproc param as a column alias? For example: SELECT ‘12345’ AS @MySprocParam I tried the following sql but it doesn’t work: DECLARE @MySprocParam VARCHAR(50) = ‘TestAlias’ SELECT ‘ASDF’ AS @MyProcParam

SQL XML Multiple Elements Per Row

I have a setup as follows: TableA – ID, PolicyReference, PolicyNumber TableB – ID, Drivers, DOB, Sex A record is inserted into TableA that has a UNIQUE ID, the Policy Reference and the Policy Number of that policy. Each ‘Policy’ can then have multiple drivers on that policy, in TableB the ID is UNIQUE and […]

Need help grouping multiple rows into single string based on GROUP BY?

This question may have been asked before but I’ve been doing some digging and can’t find anything quite like it. Thanks in advance EDIT: Totally forgot. We’re using SQL 2005 (otherwise I’d likely just use GROUP_CONCAT) Sample Table: ID CATEGORY ATTRIBUTE 1 A Piano 1 A Saxophone 1 B Jazz 1 C Mellow 1 C […]

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