Recent content by drlex

What Cognos product(s) is/are involved here? soi là, soi carré

You'll need to change the code that is casting the rate as a character field ... CONVERT(float,SUM(CASE WHEN DAJS.Code IN (48,96) THEN 1 ELSE 0 END)) / CONVERT(float,SUM(DAJS.connectFlag))* 100 as [AbandonRate] I tend to use decimal prior to varchar conversion: select cast(cast(0.9455 as...

Fee, I'm sure George will offer the full explanation; ISTR that datetimes are held as two integers; days after 1st Jan 1900 and then 300ths/second after midnight, so you're right to convert a datetime to a varchar for pivoting. Since convert format 6 always gives 2 characters for the day part...

AFAIK, Sage 50 is not a SQL database, and your title suggests involvement of VisualBasic, so I'm not sure what help you might get in the SQL server programming forum - have you tried the VBA area or even Sage fora like SageCity? If you're in NA, here's one link -...

I'd be looking at the "STUFF" command here. If you can't be sure of how long the string is, it may be easier to loop for the length of the string - perhaps make it a function to call? example: DECLARE @string as varchar(max), @loop as int SET @string = '1234567890123456789' SET @loop =...

Have you tried following the steps enumerated in the MS TechNet document on this topic? http://technet.microsoft.com/en-us/library/aa337084.aspx soi là, soi carré

I missed seeing the repetition of Hydrodate in the query, although I suspect George may be offering up something shortly. SELECT DateAdd(yyyy, SCBABottles.HydroInterval, (SELECT Max(SCBAHydro.HydroDate) from SCBAHydro where SCBABottles.BottleID = SCBAHydro.BottleID) ) As HydroDue ...

Brian, It's merely an issue of ordering your statements; the Update or Insert needs to follow the common table expresssion. ;with A as (SELECT ....) INSERT into #temp SELECT * from A soi là, soi carré

If it's just one piece of data, and you've only one record per item in table "SCBABottles", then how about going with a subquery and losing the need to group? SELECT DateAdd(yyyy, SCBABottles.HydroInterval, (SELECT Max(SCBAHydro.HydroDate) from SCBAHydro where SCBABottles.BottleID =...

Hmmm - I don't think that last code is right; add in a record 29.75, 12345, 29/09/13 and that will show as the latest date for 34.00 Try this modification: select MAX(DocDate) as LastDate, t.price as MaxPrice from @T t join ( select MAX(Price) as price from @T where Item= '12345' ) d...

As Bborisov said "Could you post some example data and desired result from it? " The data that you post as a sample input does not appear to match the sample output, as the output range (198879-96) precedes the input. soi là, soi carré

I'm confused by your code not corresponding to your example data. However, using the latter: ; with cte as (Select 1 as [number], 'A100' as [partner], 5 as [row] Union all select 2,'A100', 5 Union all select 3 ,'A100', 5 Union all select 4 ,'A200', 28 Union all select 5 ,'A200', 28 Union all...

Ian, I'd want to be checking that the machine is running at the time that the job has been scheduled; can you be certain that there's not a weekend restart job that co-incides? Event log on the machine is a place to start, but your plan of a time-change is sound. Scheduler itself may be getting...

Could it be due to different styles/formats in the Impromptu configuration files on the client machines? I'd start with comparing the "impromptu.ini" (cer5>data>bin) files. soi là, soi carré

Ian, You're welcome. By asking "which product", I was hoping to learn whether it was ReportNet, Impromptu, PowerPlay, Visualizer, etc. Working on the basis that it is either Impromptu or PowerPlay, and assuming that 7.5 is not greatly different to the 7.4 that I last used, I'd suggest that the...