Just adapt your current start of day code! All you want is start of month, start of hour. Its just the same… select dateadd(month, datediff(month, 0, getdate()),0) select dateadd(hour, datediff(hour, 0, getdate()),0)
One useful trick in SQL is the ability use @var = function(…) to assign a value. If you have multiple records in your record set, your var is assigned multiple times with side-effects: declare @badStrings table (item varchar(50)) INSERT INTO @badStrings(item) SELECT ‘>’ UNION ALL SELECT ‘<‘ UNION ALL SELECT ‘(‘ UNION ALL SELECT ‘)’ … Read more
You could use the ‘CASE .. WHEN .. THEN .. ELSE .. END’ syntax in SQL.
You should give alias to your table. So try this: SELECT A FROM ( SELECT A, B FROM TableA UNION SELECT A, B FROM TableB ) AS tbl WHERE B > ‘some value’
Rather than grouping, go about it like this… select * from account a join ( select account_id, row_number() over (order by account_id, id) – rank() over (order by account_id) as row_num from user ) first on first.account_id = a.id and first.row_num = 0
Its a way of getting an empty set; for example to create a new empty table with the same columns as an existing one; SELECT TOP 0 * INTO new_table FROM old_table Or to act a as source for column names Or as a way to return column details but no data to a client … Read more
Combining your advice and a script I found online, I made a procedure that will produce SQL you can run to perform a cascaded delete regardless of ON DELETE CASCADE. It was probably a big waste of time, but I had a good time writing it. An advantage of doing it this way is, you … Read more
OK, we have had similar issues like this before. The way we fixed this, was by making local parameters inside the SP, such that DECLARE @LOCAL_Contract_ID int, @LOCAL_dt_From smalldatetime, @LOCAL_dt_To smalldatetime, @LOCAL_Last_Run_Date datetime SELECT @LOCAL_Contract_ID = @Contract_ID, @LOCAL_dt_From = @dt_From, @LOCAL_dt_To = @dt_To, @LOCAL_Last_Run_Date = @Last_Run_Date We then use the local parameters inside the SP … Read more
If one or more of those columns have no constraints against them (like a foreign key, index, default, rule, etc), you should be able to change each one quickly by doing ALTER TABLE monster ALTER COLUMN MyIntCol1 bigint Management Studio’s change SQL is rarely the most efficient and tends to favour temporary tables for anything … Read more
1) Drop the existing clustered index first (IX_TableX_FieldB): DROP INDEX TableX.IX_TableX_FieldB 2) Create a (temporary) UNIQUE constraint on the unique fields referenced in the primary key ALTER TABLE TableX ADD CONSTRAINT UQ_TableX UNIQUE(FieldA) 3) Drop the PRIMARY KEY ALTER TABLE TableX DROP CONSTRAINT PK_TableX 4) Recreate the PRIMARY KEY as CLUSTERED ALTER TABLE TableX ADD … Read more