Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Rhinorhino on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Search results for query: *

  • Users: drlex
  • Order by date
  1. drlex

    Creating a date filter for rpt runs on monday but pulls from the previous Sat-Fri

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

    Help with Stored Procedure

    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...
  3. drlex

    SQL 2005 Problem with pivot - could be because is varchar of date?

    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...
  4. drlex

    VB read Peachtree files

    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 -...
  5. drlex

    add minus every 5 characters

    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 =...
  6. drlex

    Link to Dbase IV with SQL Server 2012

    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é
  7. drlex

    Evaluating the alias

    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 ...
  8. drlex

    Inserting Data from a 'WITH' statement

    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é
  9. drlex

    Evaluating the alias

    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 =...
  10. drlex

    move where statement

    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...
  11. drlex

    rank() OVER / PARTITION BY vs CTE for numbered groups

    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é
  12. drlex

    SQL increment a number based on a column value

    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...
  13. drlex

    Scheduled daily report does not run on Saturdays and Sundays

    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...
  14. drlex

    IMR Data output shown differently on different PCs

    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é
  15. drlex

    Scheduled daily report does not run on Saturdays and Sundays

    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...
  16. drlex

    Scheduled daily report does not run on Saturdays and Sundays

    What Cognos product are you using? How is the report scheduled? Have you tried running the report manually on either Saturday or Sunday to see whether data is being returned? soi là, soi carré
  17. drlex

    Automatic report generation on a monthly basis

    Ian, This is not only possible, but anticipated by Cognos. You can use Cognos Scheduler in conjunction with Cognos Script (VBA-like). There should be some pdfs in the Documentation of your installation which should guide you here. Mailing is sometimes an issue, as your IT department ought to be...
  18. drlex

    Another Grouping and Self Join problem

    Whilst echoing bborissov's comment, can you also indicate why simple grouping and aggregate functions will not give you the required output? SELECT [Sets], [Lots], MIN([Dates])as Earliest_Date, MAX([Dates]) as Latest_Date FROM My_Table GROUP BY [Sets], [Lots] ORDER BY [Sets], [Lots] soi là...
  19. drlex

    Trying to SELECT the most recent Patient.Weight prior to an Order.OrderDate.

    If you're only wanting the most recent weight prior to the order date, may I propose a small amendment to imex's code? select o.OrderID, o.OrderDate, o.PatientID, p.LastName, p.FirstName, (select Top 1 Weight from PatientWeights as w where w.PatientID =...
  20. drlex

    Problems with formatting when saving report as Excel

    Are you publishing to Impromptu web? If not, try using ExportExcelWithFormat command: ImpRepObject.ExportExcelWithFormat "\\cs\groups\public\EXTRA IMPROMPTU REPORTS\ADR_Laser.xls" Also, depending on the layout of the report (groupings/headings), try changing the Excel Version of the Report...

Part and Inventory Search

Back
Top