Tag: tsql

T-SQL; How to add sentences before and after a table in an html format using sp_send_dbmail

The MS Library site shows how to create an email and embed an html table result. https://msdn.microsoft.com/en-us/library/ms190307.aspx This is fine, but how should you add the beginning and end wording to the email body table please? (it must be obvious, surely?) If the initial code is as per below, then the optimum output would be: […]

SQL Server 2016 – Linked server error

I have an issue with connecting to a linked server from SQL Server 2016 to an Ingres 10.1 database via an Ingres 64bit driver ODBC connection setup in ODBC data source admin – odbc connection succeeds and the linked server connection test succeeds. T-SQL used for testing has been: select * from openquery(“linked server”, ‘select […]

can't sort data for February 2016 by week number in SSRS

For some reason when I break down 12 month by week in SSRS the month February looks weird. Weeks starting from 10, then it goes 6,7,8,9. It sorted by WeekNumber A to Z. Why does it looks like that only for February? How can I fix it? My data in SSMS look like this:

Create a Counter that resets with every change in name

This site has been a great resource for me over the years but I finally ran into an issue i couldn’t find a solution for already posted. I have a list of names, dates and values. I need to turn the dates into the numbers and for them to start over for each well where […]

How to show output for group of people instead of single individual?

I have written this code to show the FEE PAID, PENDING etc for an individual student but actually it was supposed to be for the whole class of students. I tried a lot but can’t do it for the whole class. How to do this ? Ignore @studentID, I just want it for @ClassID. ALTER […]

Converting a flat table into an XML that encodes parent child relations, but without heirarchical tags

I had an ask to convert a flat table into a particularly formatted XML which showed a parent child relationship, but had a flat structure of tags. I produced this SQL which grinds out the result, I’ve added some dummy data generation so it will run standalone. DECLARE @root varchar(250); SET @root = ‘AB&C’; DECLARE […]

Get items and dates checked in and out by person

SQL Server 2012, how do I return a single row per person, per item for a single continuous time period when multiple check-in’s are done? We have 3 tables: Person, Id, Fullname Relationship PersonId, ItemId, DateTime Item Id, ItemName The relationship table is updated whenever a person does a check in with the items they […]

COALESCE working in only one case

I am trying to update an ERP software and I block on a syntax in a table made with PIVOTS Let me explain, I would like in my table (which is functional) that all NULL values ​​be replaced by a 0. In another table almost identical with the same syntax I succeeded but in the […]

TSQL Put same rank/row number on same column A but increment once column A changes

I have this table and I want to put a rank/row number to the rows with the following condition Same IO will have same rank/row number Rank/row number will increment once IO in next row is different from previous row. Table is sorted by AccessDate then AccessTime and grouped by UserID I’m using this query […]

Displaying annual data from current year and previous year side by side

SO I created a temp table that would dump monthly data per carrier and policy number. What I want to output is for these two table to output the data side by side so I can have SSRS aggregate the data from current minus the previous year value. Here is my example code: select carrier, […]

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