Do Math on Two Scalar Queries in SQL

I’m trying to get the difference of two sum() queries. This is the idea of the code I’m trying to figure out. (SELECT sum(depamt) FROM Daily1) – (SELECT sum(depamt) FROM Daily2) I tried declaring variables but I couldn’t get the result of the select into the variables. Is there any way of doing this?

Get only first result outer join SQL Server

I got two tables, one with a list of customers, and another with a list of contract. Each customer can have multiple contracts. I want to get all customer’s Nos, Names, and if they have a contract or not. This is my request : SELECT c.[no_], c.[name], s.[contract no_] FROM [contact] AS c LEFT OUTER […]

Insert XML file into SQL Server

I’m trying to import an XML file into a SQL table. I found a few examples of code to do this, but I can’t seem to get it to work. I’ve tried a few variations in my code but at this point I’m not sure if the issue is the XML file structure or my […]

SQLBulkCopy with CLR UDT gives “Could not find method 'Read' for type 'MyNamespace.MyType' in assembly 'MyType'”

I have written a SQL Server CLR User-defined type (UDT) in SQL Server 2012. I have been able to access it though SQL test scripts, and have used it as a local variable, defined it in a table, and tested it through Visual Studio and SQL Server Management Studio. We have an service that uses […]

SQL Server stored procedure passing datepart year as a parameter

I was wondering if anyone could help please, I’ve got the below procedure, I’m trying to enable it so that an end user can pass the year portion of the date only and it will query based on that parameter so at the moment I’m querying between ContractDate.ContractDate >= 01/01/2015 AND <= 31/12/2015 but I […]

What is an elegant way to use a column of sql output for the tag of xml output within mssql?

I have a stored procedure that returns xml. Sample call is get_organization_user_export_data_permission_procedure @organizationId = ‘64483342-af49-4a04-b25d-ce7346cb5375’ , @userId = ‘5324B48E-B560-4345-B290-12CC72D722FA’ Sample output is <root> <all>false</all> <analytics>false</analytics> <none>true</none> </root> Which is correct. The base SQL is select lower([type]) name, value permission, count(permissionType) found from [GroupExportRestrictionTypeConstants_RestrictionType_view] export left join ( select permissionType from [OrganizationUserDataPermission] where organizationId = ‘64483342-af49-4a04-b25d-ce7346cb5375’ […]

Exclude whole group based on single value

Table T CID Code 1 A 1 B 1 C 2 C 2 A 2 B 3 A 3 D I want the output to have only those CID groups where Code <> ‘C’ Desired Output: CID Code 3 A 3 D Query Attempt: SELECT DISTINCT CID ,Code FROM Table T WHERE Code <> ‘C’ […]

Entity Framework added 3 foreign keys for 1 relationship

I have a simple User Class public class User { public int ID { get; set; } [Required] public virtual ApplicationUser LoginID { get; set; } [Required] public string Name { get; set; } public string JobTitle { get; set; } [DefaultValue(UserRole.Standard)] public UserRole Role { get; set; } public virtual Company Company { get; […]

SQL Server : how to combine CASE WHEN IS NULL with OR statement

I have a working statement that I need to expand to include another value; that value is 0. Right now, my statement is as follows: CASE WHEN dbo.tbl.Price IS NULL THEN CAST(dbo.tbl.cost AS NUMERIC(18 , 1)) * 2.2 ELSE dbo.tbl.Price END I have tried several ways of to re-write this with the OR operator without […]

Getting single entrances from a JOIN

I have these two tables: stores: +———-+————–+ | store_id | store_number | +———-+————–+ | 1 | 231 | | 2 | 132 | | 3 | 321 | +———-+————–+ entrances: +————-+———-+—————–+ | entrance_id | store_id | entrance_number | +————-+———-+—————–+ | 1 | 1 | 1 | | 2 | 1 | 2 | | 3 […]

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