Tag: sql

Get count of data returned by a SP in another SP in SQL

I have a SP called GetDailyData in SQL Server 2008 like: CREATE PROCEDURE [dbo].[GetDailyData] @date datetime AS BEGIN select * from myTable where date >= @date END I would like to call the stored procedure in another stored procedure and print the number of returned data. I tried this, but did not work: Select COUNT(EXEC […]

SQL Server index not considered for execution plan

I have the below index in a table. But while looking into query plan, looks like the index is not being considered for Index-Scan. Could someone please let me know whats is going missing here? Index : CREATE NONCLUSTERED INDEX [IX_Profile_ProfileStatusId_IsVoiceMailDeposit_IsVoiceMailRetrieve] ON [dbo].[Profile] ([ProfileStatusId], [IsVoiceMailDeposit],[IsVoiceMailRetrieve]) INCLUDE ([Id], [LocationId], [SubscriberId], [PersonId], [PartitionId],[ProfileTypeId], [InternalCallerId], [Extension], [IsPrimary], [IsInDirectory],[IsSuspended], […]

Pick one row for each pair of parent-child and child-parent relationships

I’m stuck with this problem and I could use some help. I’m working with a table called dependencies. A simple example ID, parent, dependent, relationship 1234, John, Mike, Parent 1235, Mike, John, Child 1236, Nancy, John, Spouse 1237, John, Nancy, Spouse 1238, Peter, Mike, Sibling 1239, Mike, Peter, Sibling Some of this dependencies are “mirror […]

SQL UNION Not Working

I’m using SQL Server 2014 and have two table: ApplicationProjectLO and V_SIP_ALLSTAFF: Table ApplicationProjectLO: |———————|—————————| | StaffEmailId | Type | |———————|—————————| | chinkim1 | L | |———————|—————————| | kandiah1 | A | |———————|—————————| Table V_SIP_ALLSTAFF |———————|—————-|——————-|—————| | lOGINID | displayname | Email Address |MainOfficeTelNo| |———————|—————-|—————- |————– | | chinkim1 | james | 1 | 5 […]

Add an attribute to the root node

Please see the DDL below: create table #Dataset1 (id int not null identity,firstname varchar(30),surname varchar(30), primary key (id)) insert into #Dataset1 (firstname,surname) values (‘Mark’,’Williams’) And the SQL below: select firstname,surname from #Dataset1 FOR XML PATH(‘Dataset1′) Which returns: <Dataset1> <firstname>Mark</firstname> <surname>Williams</surname> </Dataset1> How can I get the SQL to return: <Dataset1 URN=’1’> <firstname>Mark</firstname> <surname>Williams</surname> </Dataset1> URN=1 […]

SQL Job not producing same output as the query

I run this query to get landesk info. It returns 4 columns of info that I want but when I try to set it up as a scheduled job the output doesn’t format the results the same. I’d like a column for each. Under Steps I just run the same query and under advanced I […]

INSERT When multiple values not present

I’m using SQL Server 2012, and the code I’m using does work, but I know it isn’t the correct way to do it. I’m trying to add rows to a table only when the values are not already present, but from two different columns. INSERT INTO #TEMP_Final SELECT a.ClaimNbr ,a.DisputeNbr ,a.DisputeAmt ,a.CardAcctNbr ,b.CreditAmt ,b.CreditRefNbr FROM […]

Bulk update field using SQL INNER JOIN

I have two tables Invoices and payments. Invoice has many payments and I want to update field called total_payments_amount for all invoices rows with sum of all associated payments to each one in a single SQL query. So here is what I tried UPDATE invoices SET total_payments_amount = pays.total_amount FROM invoices inv INNER JOIN ( […]

Linking tables using Scope_Identity on Insert…Values

I am writing a query to insert values from excel to sql server. I have a table with an Identity(1,1) field and I want this to link to another table. Example: ID Vegetables 1 Corn/Peas 2 Beans/Spinach Want to link to a table that looks like: ID Vegetable 1 Corn 1 Peas 2 Beans 3 […]

Calculate sum in tier pricing. SQL Server. Tier pricing table

I am having trouble with my sum query in SQL Server. I have a tier price table set up like. The name of the table is called TierPricing. CID RangeID MinValue MaxValue Price Class 1 1 1 5 1.50 1 2 2 6 10 1.25 1 3 3 11 999999999 1.00 1 . I have […]

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