Forum Replies Created

Viewing 15 posts - 2,536 through 2,550 (of 7,613 total)

  • Reply To: Create temp table in Openquery?

    I don't see how OPENQUERY would provide any performance benefit here.  But, no, you can't create a temp table using OQ.

    If you can provide more details of your specific situation,...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 14, 2020 at 7:34 pm

    #3715386

  • Reply To: tyring to convert a varchar to a Date

    Don't limit yourself to a single format.  Just CAST it to date: if it's a valid date in any format, you'll get a date back.  If not, you'll have to...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 14, 2020 at 7:28 pm

    #3715369

  • Reply To: Query stops retuning records after 6:00pm - Datetime datatype

    oogibah wrote:

    I'm a noob, but what if you casted them as date so it doesnt include the  time portion?

    where CAST([TransactionDate]  AS DATE)=CAST(GETDATE() AS DATE)

    That seems like a slick solution.  The...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 14, 2020 at 6:40 pm

    #3715348

  • Reply To: Query stops retuning records after 6:00pm - Datetime datatype

    What is the data type of the TransactionDate column?  If it's in the date/datetime category, then this condition:

    where [TransactionDate] >= DATEADD(day, DATEDIFF(day, 0, GETDATE()), 0)

    should pull all rows on or...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 14, 2020 at 5:52 pm

    #3715331

  • Reply To: Error - Column 'INV1.LineNum' is invalid in the select list

    SQL can't execute the query as you've written it because there may be multiple values of T1.LineNUM for a given combination of DocEntry and ChapterID.

    For example, suppose these two rows...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 13, 2020 at 10:19 pm

    #3715089

  • Reply To: @@ROWCOUNT in a Exec() with identity_insert

    You need to use sp_executesql rather than just exec(), because sp_exec allows value(s) to be returned:

    DECLARE @SQLSTR nvarchar(500)
    DECLARE @DELCOUNT int
    ...
    SET @SQLSTR = 'Set IDENTITY_INSERT Table_2 ON;DELETE from...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 13, 2020 at 6:52 pm

    #3715035

  • Reply To: Datediff between dates, but exclude extra leap year day

    I think the code below is much simpler.  The code is complete, no function needed.  And, yes, I took a short-cut, so, yes, this code will fail for 2100.  If...

    • This reply was modified 6 years, 5 months ago by ScottPletcher. Reason: Tweaked the code

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 13, 2020 at 6:37 pm

    #3715033

  • Reply To: Fifi Calculation

    x wrote:

    Just want to clarify here Scott, who's the idiot you are having difficulties with?

    That q obviously makes it self-evident.

    Accounting entries are not properly classified simply by a + or...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 9, 2020 at 8:39 pm

    #3714121

  • Reply To: Fifi Calculation

    x wrote:

    Matt Miller (4) wrote:

    x wrote:

    So can you do this or can't you? The ONLY question you have to ask, is if the OP's design allows negative numbers. If the OP NEEDS...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 9, 2020 at 8:36 pm

    #3714119

  • Reply To: Fifi Calculation

    x wrote:

    x wrote:

    x wrote:

    ScottPletcher wrote:

    x wrote:

    I defy you to produce a proper Balance Sheet without distinguishing between debits and credits.  Show me how that would be done.

    I gotta go with...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 9, 2020 at 6:58 pm

    #3714088

  • Reply To: list tables that all contain the same 3 column names

     

    SELECT OBJECT_NAME(c.object_id) AS TableName
    FROM sys.columns c
    INNER JOIN sys.tables t ON t.object_id = c.object_id
    WHERE c.name IN ('Column1','Column2','Column3')
    GROUP BY c.object_id
    HAVING COUNT(*) = 3
    ORDER BY TableName

    • This reply was modified 6 years, 5 months ago by ScottPletcher. Reason: To correct GROUP BY from "OBJECT_NAME(c.object_id)" to just "c.object_id"

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 9, 2020 at 5:33 pm

    #3714051

  • Reply To: Fifi Calculation

    x wrote:

    I defy you to produce a proper Balance Sheet without distinguishing between debits and credits.  Show me how that would be done.

    I gotta go with Joe on this. The...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 9, 2020 at 3:39 pm

    #3714019

  • Reply To: Index larger than table

    The slight extra space is to store the upper level(s) (root level and intermediate level(s)) of the index, which are the b-tree structure that allows quick searches to specific row...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 8, 2020 at 5:07 pm

    #3713700

  • Reply To: Two tables but if 2nd table has one row that is to be ignored

     

    SELECT ClientID
    FROM #Projects
    GROUP BY ClientID
    HAVING COUNT(DISTINCT ClientRole) > 1
    ORDER BY ClientID

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 8, 2020 at 2:43 pm

    #3713602

  • Reply To: Comparing to Columns from the same table

    I prefer using CROSS APPLY to assign alias names.  CAs can also cascade, where one alias can be used to create another alias(es).  That's very helpful sometimes.

    select BSEG_ID,SP_ID,FINAL_REG_QTY,START_REG_READING, END_REG_READING,

    ActualRead

    from CI_BSEG_READ

    cross...

    SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

    January 8, 2020 at 2:39 pm

    #3713601

Viewing 15 posts - 2,536 through 2,550 (of 7,613 total)