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 Chriss Miller 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: *

  1. JohnDTampaBay

    Conditional Visibility of Top Group

    Have you tried using an expression for the Visibility.Hidden property of that row? =IIF( Parameters!ViewReport.Value = "B", True, False) Good luck! --John [rainbow] ----------------------------------- Behold! As a wild ass in the desert go forth I to do my work. --Gurnie Hallock (Dune)
  2. JohnDTampaBay

    How To Remove Duplicates from a Count Query

    select count(DISTINCT A.mbr_ssn_nbr) from dsnp.pr01_t_mbr_sys A, dsnp.pr01_t_mbr_hist B where A.mbr_ssn_nbr = B.mbr_ssn_nbr and B.benef_stat_cd = 'K' --John [rainbow] ----------------------------------- Behold! As a wild ass in the desert go forth I to do my work. --Gurnie Hallock (Dune)
  3. JohnDTampaBay

    Validate Relationship

    FOREIGN KEY constraint on FKeyDoc referencing the audit should do the trick. The constraint does not validate NULL against the referenced table, only actual values. --John [rainbow] ----------------------------------- Behold! As a wild ass in the desert go forth I to do my work. --Gurnie...
  4. JohnDTampaBay

    Selecting the length of a column?

    What version of SQL Server are you running? This should work for 2000 and higher. SELECT Character_Maximum_Length FROM Information_Schema.Columns WHERE Table_Name = 'myTable' AND Column_Name = 'myColumn' --John [rainbow] ----------------------------------- Behold! As a wild ass in the desert go...
  5. JohnDTampaBay

    Trimming a data field

    Sorry about that. I wrote them as CASTs, then decided to change to CONVERTs mid-post. Glad you figured it. --John [rainbow] ----------------------------------- Behold! As a wild ass in the desert go forth I to do my work. --Gurnie Hallock (Dune)
  6. JohnDTampaBay

    Trimming a data field

    There is no CONVERT style for that. You will have to do it manually. Try this: CONVERT(Month(m.DOB), varchar(2))+'/'+CONVERT(Year(m.DOB), char(4)) --John [rainbow] ----------------------------------- Behold! As a wild ass in the desert go forth I to do my work. --Gurnie Hallock (Dune)
  7. JohnDTampaBay

    Need help with a query

    Give this a shot... good luck. DECLARE @UserID int -- or whatever SET @UserID = 123 -- or whatever SELECT ug.UserGroupID, ug.Description, CASE WHEN ugm.UserID IS NOT NULL THEN 'Yes' ELSE 'No' END AS UserX FROM UserGroup AS ug LEFT OUTER JOIN UserGroupMember AS ugm ON ug.UserGroupID =...
  8. JohnDTampaBay

    Trigger for inserting record into another table

    ...have identical structures, a simple trigger would be ... CREATE TRIGGER trg_Table1_Delete ON Table_1 AFTER DELETE AS INSERT INTO Table_2 SELECT * FROM Deleted --John [rainbow] ----------------------------------- Behold! As a wild ass in the desert go forth I to do my work. --Gurnie...
  9. JohnDTampaBay

    query help

    Ah, gmmastros beat me to the punch and is much more concise. [thumbsup2] --John [rainbow] ----------------------------------- Behold! As a wild ass in the desert go forth I to do my work. --Gurnie Hallock (Dune)
  10. JohnDTampaBay

    query help

    Will the order portion (a,b,etc) of the QuestionNumber always be a single character at the end? Do all QuestionNumber values have an order? Here's a little something to start with: SELECT QuestionNumber, Right(QuestionNumber,1) AS QuestionOrder, Replace(QuestionNumber...
  11. JohnDTampaBay

    Selecting column names in a query

    Here's one way. It isn't elegant but it works. Good luck! SELECT Column1, Column2 FROM ( SELECT 1 AS SortIt, 'Column1' AS Column1, 'Column2' AS Column2 FROM ...etc... UNION ALL SELECT 2 AS SortIt, Column1, Column2 FROM ...etc... ) a ORDER BY SortIt --John [rainbow]...
  12. JohnDTampaBay

    Auto Add Date

    You can use an ActiveX script to dynamically calculate the filename and change the DataSource property of the Destination connection, or possibly just a Dynamic Properties Task. Good luck! --John [rainbow] ----------------------------------- Behold! As a wild ass in the desert go forth I to do...
  13. JohnDTampaBay

    Add text to string

    UPDATE table SET column = Left(column, Len(column) - 2) + '.' + Right(column, 2) Something like this should work. --John [rainbow] ----------------------------------- Behold! As a wild ass in the desert go forth I to do my work. --Gurnie Hallock (Dune)
  14. JohnDTampaBay

    Error

    No need for parens around the datetime datatype. create table #commissions ( employeeid int, woid int, worid int, custid int, custcode char(20), custname varchar (255), Effdate datetime , WeekEnd datetime ) --John [rainbow] ----------------------------------- Behold! As a wild ass in...
  15. JohnDTampaBay

    Remove dup contents in a row

    SELECT Reason, Min(ClientNo) AS ClientNo FROM yourtable GROUP BY Reason How does this work for you? --John [rainbow] ----------------------------------- Behold! As a wild ass in the desert go forth I to do my work. --Gurnie Hallock (Dune)
  16. JohnDTampaBay

    Making a global SP?

    Prefix it with sp_ and create it in master. It will execute in the context of the database called from. --John [rainbow] ----------------------------------- Behold! As a wild ass in the desert go forth I to do my work. --Gurnie Hallock (Dune)
  17. JohnDTampaBay

    Filegroup backup/copy/restore?

    So I can't bring the database online until all filegroups have been restored? It was my understanding that tables would be available as soon as each filegroup was restored. Our endmonth process only requires about 1/3 of the tables in the database. I would love to get it churning without waiting...
  18. JohnDTampaBay

    Filegroup backup/copy/restore?

    I need advise on refining our backup/restore process. We have a big honkin' production database that needs to be backed up, BAK copied to 2nd server, then restored on 2nd server for Endmonth processing. Currently the backup/restore time is quite lengthy. It is my understanding that I can...
  19. JohnDTampaBay

    Execute job from stored procedure?

    Check out sp_start_job in BOL. --John [rainbow] ----------------------------------- Behold! As a wild ass in the desert go forth I to do my work. --Gurnie Hallock (Dune)
  20. JohnDTampaBay

    Need help writing a Stored Procedure

    How about creating a view that normalizes the data, then querying the view? CREATE VIEW SomeView AS SELECT Code, Year, Month_1 AS July FROM YourTable UNION ALL SELECT Code, Year, Month_2 AS August FROM YourTable UNION ALL ...etc... It will take a little more work to get the From and To...

Part and Inventory Search

Back
Top