After troubleshooting this for hours and sitting on the phone with my hosting group they discovered that there was a problem with their networking configuration. The solution was made clearer when during my testing one of the VM’s suddenly couldn’t find the domain, and a simple ‘ping’ to the IP of each box from the … Read more
I see this post is pretty old and there are some useful answers but this is a pretty crazy straight forward method… Select SUM(DISTINCT(n & 0x01)) + SUM(DISTINCT(n & 0x02)) + SUM(DISTINCT(n & 0x04)) as OrN From BitValues
This is likely not a problem of indexing, but one of data design. Normalization, to be precise. The fact that you need to query distinct values of a field, and even willing to add an index, is a strong indicator that the field should be normalized into a separate table with a (small) join key. … Read more
WITH q AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY crew, dayType ORDER BY [date]) AS rnd, ROW_NUMBER() OVER (PARTITION BY crew ORDER BY [date]) AS rn FROM mytable ) SELECT MIN([date]), MAX([date]), crew AS name, dayType FROM q GROUP BY crew, dayType, rnd – rn This article may be of interest to you: Things … Read more
Alt + F1 is a good shortcut key for sp_help. sp_helptext is another goodie for getting stored procedure text.
Try this: SELECT a.*, CASE WHEN totalPayments>1 THEN ‘NO’ ELSE ‘YES’ END IsFirstPayment FROM( SELECT p.payment_id, p.user_id, ROW_NUMBER() OVER (PARTITION BY p.user_id ORDER BY p.payment_date DESC) AS paymentNumber, SUM(1) OVER (PARTITION BY p.user_id) AS totalPayments FROM payment p ) a WHERE paymentNumber = 1
What you can do is alias the selected field like this: SELECT name as [name .] FROM … The spaces and the dot will expand the column width.
There is a schema called INFORMATION_SCHEMA schema which contains a set of views on tables from the SYS schema that you can query to get what you want. A major upside of the INFORMATION_SCHEMA is that the object names are very query friendly and user readable. The downside of the INFORMATION_SCHEMA is that you have … Read more
Over the years a pile of developer sweat has gone into efficiently paging result sets. Yet, there is no one answer–it depends on your use case. Part of the use case is getting your page efficiently, part is figuring out how many rows are in a complete result set. So sorry if i stray a … Read more
This should do it: UPDATE TheTable SET PhoneNumber = SUBSTRING(PhoneNumber, 1, 3) + ‘-‘ + SUBSTRING(PhoneNumber, 4, 3) + ‘-‘ + SUBSTRING(PhoneNumber, 7, 4) Incorporated Kane’s suggestion, you can compute the phone number’s formatting at runtime. One possible approach would be to use scalar functions for this purpose (works in SQL Server): CREATE FUNCTION FormatPhoneNumber(@phoneNumber … Read more