You don’t mention if the columns are nullable. If they are and you want the same semantics that the AVG aggregate provides you can do (2008) SELECT *, (SELECT AVG(c) FROM (VALUES(R1), (R2), (R3), (R4), (R5)) T (c)) AS [Average] FROM Request The 2005 version is a bit more tedious SELECT *, (SELECT AVG(c) FROM … Read more
If you aren’t familiar with join statements then that is where you need to start. Understanding how joins work is key to the rest. Once you’re familiar with joins then understanding the merge is easiest by thinking of it as a full join with instructions on what to do for rows that do or do … Read more
Easy: –start of last week SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 0) –end of last week SELECT DATEADD(wk, DATEDIFF(wk, 6, GETDATE()), 6) EDIT: The below will handle the Sunday date issue. DECLARE @input varchar(10) –SET @input=”9/9/2012″ — simulates a Sunday SET @input = GETDATE() –start of last week SELECT DATEADD(wk, DATEDIFF(wk, 6, CASE DATEPART(dw,@input) WHEN 1 … Read more
[NOTE: If you are going to downvote this answer, please leave a comment explaining why. It has already been downvoted many times, and finally ypercube (thank you) explained at least one reason why. I can’t remove the answer because it is accepted, so you might as well help to improve it.] According to this exchange … Read more
Local temporary tables (start with #) are limited to your session; other sessions, even from the same user/connection string, can’t see them. The rules for the lifetime depend on whether the local temporary table was created in a stored procedure: A local temporary table that is created in a stored procedure is dropped when the … Read more
Here is an example that does a union between two completely unrelated tables: the Student and the Products table. It generates an output that is 4 columns: select FirstName as Column1, LastName as Column2, email as Column3, null as Column4 from Student union select ProductName as Column1, QuantityPerUnit as Column2, null as Column3, UnitsInStock as … Read more
Sure, just add the same calculation to the GROUP BY clause: select dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 – datepart(weekday, mwspp.DateDue))), sum(mwspp.QtyRequired) from manufacturingweekshortagepartpurchasing mwspp where mwspp.buildScheduleSimID = 10109 and mwspp.partID = 8366 group by dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 – datepart(weekday, mwspp.DateDue))) order by dateadd(day, -7, Convert(DateTime, mwspp.DateDue) + (7 – datepart(weekday, mwspp.DateDue))) … Read more
NULL is already ignored so you can use NULLIF to turn 0 to NULL. Also you don’t need DISTINCT and your WHERE on ActualTime is not sargable. SELECT AVG(cast(NULLIF(a.SecurityW, 0) AS BIGINT)) AS Average1, AVG(cast(NULLIF(a.TransferW, 0) AS BIGINT)) AS Average2, AVG(cast(NULLIF(a.StaffW, 0) AS BIGINT)) AS Average3 FROM Table1 a WHERE a.ActualTime >= ‘20130401’ AND a.ActualTime … Read more
CASE is an expression that returns a value. It is not for control-of-flow, like IF. And you can’t use IF within a query. Unfortunately, there are some limitations with CASE expressions that make it cumbersome to do what you want. For example, all of the branches in a CASE expression must return the same type, … Read more
select name, compatibility_level , version_name = CASE compatibility_level WHEN 65 THEN ‘SQL Server 6.5’ WHEN 70 THEN ‘SQL Server 7.0’ WHEN 80 THEN ‘SQL Server 2000’ WHEN 90 THEN ‘SQL Server 2005’ WHEN 100 THEN ‘SQL Server 2008/R2’ WHEN 110 THEN ‘SQL Server 2012’ WHEN 120 THEN ‘SQL Server 2014’ WHEN 130 THEN ‘SQL Server … Read more