Pivot table how to not allow nulls in multiple columns

I want to put a condition if [2015] and [2016] are NULL then hide row, but I cant pull it off. I don’t have interest in the billname if it doesn’t have data. SELECT IssuedByUserGroup, BILLNAME, SUM([2016]) AS [2016], SUM([2015])AS [2015] FROM Sum_Orders PIVOT ( SUM(Sum_SellPrice) FOR OrderperiodYear IN ([2016],[2015]) ) AS pvt WHERE ( […]

SQL Server : when to use collation and nvarchar

Currently my column datatype is varchar in my SQL Server table. I want to store both English and Chinese characters in my column. What steps do I have to follow to use collation, or do I have to change the datatype to NVARCHAR and insert with N’ as unicode? If I have to use collation […]

SQL Server: GROUP BY multiple segments of similar/matching rows

I need to be able to do a group by but in segments. So in the example below, there are 4 ‘groups’representing a leg of a journey. SELECT id, mode, distance FROM TravelLog; Output: —————————————————————————– id mode distance —————————————————————————– 1 on_foot 0.1 2 on_foot 0.2 3 on_foot 0.4 4 on_foot 0.6 5 on_foot 0.7 6 […]

permanent damage from SELECT query?

I want to execute some SELECT statements to get some data out of an application database (production environment, not test), can I cause any permanent damage? I’ve heard its not recommended to use SELECT * because it slows things down etc. but I assume there is nothing that could be affected in the long run? […]

How can i get the days between results dates for each seperate pat_id?

I’m looking to get the days between result dates for each patient: only looking at result dates where the result value is <90.00 ;WITH patient_results AS ( SELECT * FROM (VALUES (1, ‘EA11AEE3-1D90-4602-9A37-0000007E2293’, ‘85.10’ ,’2015-12-11′), (1, ’27BCD3E4-2381-4139-B420-0000025B4113′, ‘91.50’ ,’2016-01-05′), (1, ‘D8969360-45D6-487B-AF94-0000035F78B0’, ‘81.00’ ,’2016-07-21′), (5, ‘446E6413-442A-452A-BCF4-000006AA9896’, ‘58.00’ ,’2014-07-01′), (5, ‘00305129-BC14-4A12-8368-00000AC04A9B’, ‘53.00’ ,’2014-12-13′), (5, ’96A67E53-2D6C-430B-A01F-00000AE4C37B’, ‘42.80’ ,’2015-02-01′), […]

Echo JSON in a d3.js chart

I have a chart created using d3.js where if you hover over the months,the weekly scores are found. Fiddle link to the chart I am talking about here : https://jsfiddle.net/qp7L1hob/1/ I also have a table in my DB where I maintain the record of the scores of the Employees every week.What you see here is […]

Do I need to create an Index manually when setting up a foreign key?

I have these schema in my SQL Server database: CREATE TABLE [dbo].[Phrase] ( [PhraseId] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL, [Text] NVARCHAR (MAX) NOT NULL, [CategoryId] INT DEFAULT ((1)) NOT NULL, PRIMARY KEY CLUSTERED ([PhraseId] ASC), CONSTRAINT [FK_PhrasePhraseCategory] FOREIGN KEY ([CategoryId]) REFERENCES [dbo].[PhraseCategory] ([PhraseCategoryShortId]) ); CREATE TABLE [dbo].[PhraseCategory] ( [PhraseCategoryId] UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL, [PhraseCategoryShortId] […]

SQL Left Outer join with where clause reduces results from left outer join

I have the following query that works exactly how I would expect it to. It returns back all of the Statuses with the counts. SELECT ProcessStatuses.Status, COUNT(SecretProcesses.ProcessStatusID) AS Count FROM ProcessStatuses LEFT OUTER JOIN SecretProcesses ON ProcessStatuses.ProcessStatusID = SecretProcesses.ProcessStatusID GROUP BY ProcessStatuses.Status Result: Status Count ————- status1 0 status2 0 status3 0 status4 0 status5 […]

how many result test we can give a for execute sql task

I have an ssis package which have a exectue sql task it. I tried to modify the task by adding a stored procedure as sql statement. The stored procedure returns two result sets. stored procedure: create proc test as begin select EmpID from Employee select PersonID from person end I mapped the result sets to […]

Not firing after update trigger when updating from insert trigger

I have two triggers on one table, one fires on insert and updates the row with additional info from other tables CREATE TRIGGER [dbo].[eTteamTg] ON [dbo].[entryTable] AFTER INSERT AS BEGIN UPDATE entryTable SET shiftTeam = (SELECT TOP 1 shiftTeamMemb.teamId FROM shiftTeamMemb WHERE shiftTeamMemb.personalNumber = i.personalNumber) FROM entryTable INNER JOIN inserted i ON i.ID = entryTable.ID […]

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