Forum Replies Created

Viewing 15 posts - 2,551 through 2,565 (of 7,619 total)

  • 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, 7 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

  • Reply To: Index larger than table

    .1. The PK itself.

    .2. No.

    .3. The clustering key column(s) are stored in higher-level index entries.  The deeper the index depth, the more space the upper levels of the index take.

    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 7, 2020 at 8:21 pm

    #3713384

  • Reply To: Fifi Calculation

    Jeff Moden wrote:

    jcelko212 32090 wrote:

    >> I don't think I was particularly snarky.

    THAT, good Sir, is the crux of the problem. 😉

    I don't think his first post was particularly snarky, esp. for him,...

    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 7, 2020 at 7:41 pm

    #3713369

  • Reply To: Fifi Calculation

    jcelko212 32090 wrote:

    My next correction is a little more controversial. The reason that debits and credits exist has to do with the fact that when modern bookkeeping began in the Renaissance,...

    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 7, 2020 at 5:48 pm

    #3713303

  • Reply To: Difference between where and INNER JOIN comparing a value

    I left one out, the sample INNER JOIN:


    ;WITH cte_A AS ( SELECT * FROM (VALUES(1, 'A', 10),(2, 'B', 20), (3, 'C', 30)) AS data(key_col, col1, col2)
    ), cte_B...

    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 3, 2020 at 3:58 pm

    #3712169

  • Reply To: Difference between where and INNER JOIN comparing a value

    Here are queries to demonstrate the points above.  Assume for this example that B.col2 is a NOT NULL column.

    ;WITH cte_A AS ( SELECT * FROM (VALUES(1, 'A',...

    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 2, 2020 at 5:07 pm

    #3711894

  • Reply To: Difference between where and INNER JOIN comparing a value

    Grant Fritchey wrote:

    ...OUTER JOIN, if the filter is on the OUTER table, it needs to go to the JOIN criteria or it converts the OUTER JOIN to an INNER JOIN which...

    • This reply was modified 6 years, 8 months ago by ScottPletcher. Reason: Reworded, for clarity

    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 2, 2020 at 3:59 pm

    #3711830

  • Reply To: persisted computed column error

    You need to specify an explicit format (conversion code) for the date conversions, viz:

    CONVERT(varchar(100),ISNULL(despatch_group_start_date,'01/01/1900'), <format_code_needed_here>)

    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 2, 2020 at 3:52 pm

    #3711828

  • Reply To: Temporal Table Columns

    OK, so I guess the AFTER trigger would have to use the deleted table to check for UPDATEs vs. just relying on the UPDATE() function.

    CREATE TRIGGER dbo.TriggerSource_AFTER_UPDATE
    ...

    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".

    December 31, 2019 at 3:35 pm

    #3711462

  • Reply To: Temporal Table Columns

    I don't know, maybe I'm not thinking about it right, but I was anticipating something like below.  SQL checks a column value being UPDATEd and doesn't actually change it if...

    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".

    December 30, 2019 at 6:26 pm

    #3711252

Viewing 15 posts - 2,551 through 2,565 (of 7,619 total)