You can use a recursive CTE to build your list of 30 days, then join that to your data –test select cast(’05 jan 2011′ as datetime) as DT, 1 as val into #t union all select CAST(’05 jan 2011′ as datetime), 1 union all select CAST(’29 jan 2011′ as datetime), 1 declare @start datetime=”01 jan … Read more
Simply put the “qa bug” criteria in the join: select t1.*, t2.name from #bug t1 left join #blocking t2 on t1.id = t2.id AND t2.name=”qa bug”
You will want to use a PIVOT function for this. If you have a known number of columns, then you can hard-code the values: select name, [Bronze], [Silver], [Gold], [Platinum], [AnotherOne] from ( select c.name, cr.description, r.typeid from customers c left join rewards r on c.id = r.customerid left join customerrewards cr on r.typeid = … Read more
This is a long standing parser issue. You need to get rid of the table prefix or wrap it in square brackets. i.e. delete from @CompanyGroupSites_Master where CompanyGroupID = @CompanyGroupID or delete from @CompanyGroupSites_Master where [@CompanyGroupSites_Master].CompanyGroupID = @CompanyGroupID Not delete from @CompanyGroupSites_Master where @CompanyGroupSites_Master.CompanyGroupID = @CompanyGroupID
Find the first character that is not CHAR(13) or CHAR(10) and subtract its position from the string’s length. LTRIM() SELECT RIGHT(@MyString,LEN(@MyString)-PATINDEX(‘%[^’+CHAR(13)+CHAR(10)+’]%’,@MyString)+1) RTRIM() SELECT LEFT(@MyString,LEN(@MyString)-PATINDEX(‘%[^’+CHAR(13)+CHAR(10)+’]%’,REVERSE(@MyString))+1)
DBCC FREEPROCCACHE Will remove all cached procedures execution plans. This would cause all subsequent procedure calls to be recompiled. Adding WITH RECOMPILE to a procedure definition would cause the procedure to be recompiled every time it was called. I do not believe that (in SQL 2005 or earlier) there is any way to clear the … Read more
How about this? SELECT EventID, EventTime, AnnouncementValue = t1.EventXML.value(‘(/Event/Announcement/Value)[1]’, ‘decimal(10,2)’), AnnouncementDate = t1.EventXML.value(‘(/Event/Announcement/Date)[1]’, ‘date’) FROM dbo.T1 WHERE t1.EventXML.exist(‘/Event/Indicator/Name[text() = “GDP”]’) = 1 It will find all rows where the /Event/Indicator/Name equals GDP and then it will display the <Announcement>/<Value> and <Announcement>/<Date> for those rows. See SQLFiddle demo
SELECT post.id, post.title, comment.id, comment.message FROM post OUTER APPLY ( SELECT TOP 1 * FROM comment с WHERE c.post_id = post.id ORDER BY date DESC ) comment or SELECT * FROM ( SELECT post.id, post.title, comment.id, comment.message, ROW_NUMBER() OVER (PARTITION BY post.id ORDER BY comment.date DESC) AS rn FROM post LEFT JOIN comment ON comment.post_id … Read more
First day of next month: sql-server 2012+ DATEADD(d, 1, EOMONTH(current_timestamp)) sql-server 2008 and older: DATEADD(m, DATEDIFF(m, -1, current_timestamp), 0)
As you’ve noticed, the WHERE clause doesn’t allow you to use aggregates in it. That’s what the HAVING clause is for. HAVING t1.field3=MAX(t1.field3)