Forum Replies Created
Viewing 15 posts - 2,296 through 2,310 (of 3,544 total)
-
RE: Custom Sort
This assumes a maximum of four values with either hyphen or full stop delimiter and a maximum of 4 chars between delimiters
ORDER BY
ISNULL(RIGHT('0000'+PARSENAME(REPLACE([column],'-','.'),4),4),'')+
ISNULL(RIGHT('0000'+PARSENAME(REPLACE([column],'-','.'),2),4),'')+
ISNULL(RIGHT('0000'+PARSENAME(REPLACE([column],'-','.'),3),4),'')+
ISNULL(RIGHT('0000'+PARSENAME(REPLACE([column],'-','.'),1),4),'')
Far away is close at hand in the images of elsewhere.
Anon. -
RE: Pivot Rows into Columns
Bit long winded but might work
CREATE TABLE #temp (rowid int IDENTITY(1,1), colid int,
CustomerCode, AddressId, CustomerCode, a.AddressType, a.DateReceived, Line1, Line2, Line3, Line4, ZipCode)
INSERT INTO #temp
(colid, CustomerCode, AddressId, CustomerCode, a.AddressType, a.DateReceived,...
Far away is close at hand in the images of elsewhere.
Anon. -
RE: Importing Excel into SQL - some columns import as NULL
This because the engine used to read the spreadsheet tries to decide whether each column is char or numeric based on it's data (IIRC the first 7 rows). If any...
Far away is close at hand in the images of elsewhere.
Anon. -
RE: Passing variable parameter in SP
Maybe something like this
DROP PROCEDURE usp_test
GO
CREATE PROCEDURE usp_test
@a int = NULL,
@b int = NULL,
@c char(5) = NULL
AS
SELECT a,b,c
FROM tbl1
WHERE a = ISNULL(@a,a)
AND...
Far away is close at hand in the images of elsewhere.
Anon. -
RE: Creating report from one table
My query will return two rows per customer!!
We need more info if the query does not produce what you want
Far away is close at hand in the images of elsewhere.
Anon. -
RE: Finding no of weekend days between two dates
As I've said, use a calendar table. Maybe, maybe not, a lot of trouble for small return
Far away is close at hand in the images of elsewhere.
Anon. -
RE: Creating report from one table
or like this (will only work for one year!)
SELECT Customer, [Type],
SUM(CASE WHEN [Month] = 1 THEN [Value] ELSE 0 END) as [Jan],
SUM(CASE WHEN [Month] = 2 ...
Far away is close at hand in the images of elsewhere.
Anon. -
RE: Finding no of weekend days between two dates
SET DATEFIRST 7
SELECT ((DATEDIFF(week,[startdate],[enddate]) -
(CASE WHEN DATEPART(weekday,[startdate]) IN (1,7)
OR DATEPART(weekday,[enddate]) IN (1,7) THEN 1 ELSE 0 END)) * 2) +
(CASE WHEN DATEPART(weekday,[startdate]) =...
Far away is close at hand in the images of elsewhere.
Anon. -
RE: Nested Loops on cursors
The problem is I cannot use @@Fetch_Status... You can but as stated in BOL you have to be...
Far away is close at hand in the images of elsewhere.
Anon. -
RE: ABOUT TRIGGERS!!!
Is this what you are trying to do?
CREATE TRIGGER TRG_STOCKS ON [dbo].[T_STOCKS]
FOR INSERT, UPDATE
AS
BEGIN
UPDATE t
SET [DESCRIPTION] = (CASE WHEN d.ITEM_NO IS NULL THEN 'NEW RECORD...
Far away is close at hand in the images of elsewhere.
Anon. -
RE: delete rows less than 30 days
or this might even work
DECLARE @AUDITTIMESTAMP bigint
SET @AUDITTIMESTAMP = SELECT CAST(DATEDIFF(day,'01/01/1970',DATEADD(day,-30,GETDATE())) as numeric) * 24 * 60 * 60 * 1000
SELECT *
FROM [Table]
WHERE AUDITTIMESTAMP < @AUDITTIMESTAMP...
Far away is close at hand in the images of elsewhere.
Anon. -
RE: delete rows less than 30 days
If DATEADD(hh, - 05, DATEADD(ss, AUDITTIMESTAMP / 1000, '01/01/1970')
returns the correct date (seems like this data is from a legacy system!)
then you could do the following
WHERE DATEDIFF(day,DATEADD(hh, - 05, DATEADD(ss,...
Far away is close at hand in the images of elsewhere.
Anon. -
RE: DTS error handling when server down
I have lots of dts packages like this, I use an 'Execute SQL Task' to do a select on the source table (using a single key) and only execute the...
Far away is close at hand in the images of elsewhere.
Anon. -
RE: Need a RELIABLE table-exist test
I guess what I'm really asking is when should I check for object_id() is null object_id will return...
Far away is close at hand in the images of elsewhere.
Anon. -
RE: Counting a consecutive condition
nice
slight change to reduce data for aggregates
select distinct e1.emp_id
from #emp e1
INNER JOIN #emp e2
on e1.emp_id = e2.emp_id
and e2.met = 1...
Far away is close at hand in the images of elsewhere.
Anon.
Viewing 15 posts - 2,296 through 2,310 (of 3,544 total)