SUM(CAST(....

  • Hi to all. It has been a while since I posted. I am attempting to output a value and convert it to display a file size value in MB. I would like to round the value output then apply a WHERE clause so only records over a certain size are returned (say over 50MB).

    This is what I have (which works): I am sure there are better ways to write it 🙂

    DECLARE @User nvarchar(50)
    DECLARE @StartDate smalldatetime
    DECLARE @EndDate smalldatetime
    DECLARE @Domain NVARCHAR (10)

    SET @StartDate = '15 Dec 2021'
    SET @EndDate = '16 Dec 2021'
    SET @Domain = 'mydomain.local'
    ;

    WITH cteDataTransfer AS
    (
    SELECT
    EPName,
    NTUserName,
    ParsedUserName,
    FileSize,
    SUBSTRING (NTUserName,0,CHARINDEX('\',NTUserName)) AS Domain,
    CONVERT(DATE,TransferredDate) AS [TransferredDate],
    CAST(DATENAME(WEEKDAY,TransferredDate) AS NVARCHAR(20)) AS [Weekday],
    CAST(DATENAME(DAY,TransferredDate) AS NVARCHAR(20)) AS [Day],
    CAST(DATENAME(MONTH,TransferredDate) AS NVARCHAR(20)) AS Month,
    DATEPART(YEAR,TransferredDate) As Year
    FROM [dbo].[vDCDeviceLogs]
    INNER JOIN dbo.DeviceClass AS Dclass ON dbo.vDCDeviceLogs.DeviceClassId = Dclass.DeviceClassId
    WHERE
    [FileName] IS NOT NULL
    AND [FileName] <> ''
    AND ActionName = 'WRITE-GRANTED'
    AND TransferredDate >= @StartDate
    AND TransferredDate <= @EndDate
    GROUP BY NTUserName,ParsedUserName,TransferredDate,EPName,NTUserName,FileSize
    )
    SELECT ParsedUserName,TransferredDate,Weekday,Day,Month,Year,CONVERT(NVARCHAR(440),
    SUM(CAST([FileSize] AS NUMERIC(35,2))/1048576.0)) + ' MB' AS [FileSizeMB],
    --SUM(CAST([FileSize] AS BIGINT)/1048576.0)) + ' MB' AS [FileSizeMB],
    Domain
    FROM cteDataTransfer
    WHERE Domain = @Domain
    GROUP BY TransferredDate,ParsedUserName,Weekday,Day,Month,Year,Domain

    If I change to WHERE Domain = @Domain AND Filesize > 50000  it returns error:

    Msg 248, Level 16, State 1, Line 14

    The conversion of the nvarchar value '16520105984' overflowed an int column.

    Filesize in the table is nvarchar(440)

    Many Thanks,

    Philip

     

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • Looks like you need BIGINT instead of INT.

    Max for INT is 2,147,483,647

    Your value is 16,520,105,984

    https://docs.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql?view=sql-server-ver15

    • This reply was modified 4 months ago by  homebrew01.
    • This reply was modified 4 months ago by  homebrew01.
    • This reply was modified 4 months ago by  homebrew01.
    • This reply was modified 4 months ago by  homebrew01.
  • Hi. Where?

    Looks like you need BIGINT instead of INT.

    I tried:

    SUM(CAST([FileSize] AS BIGINT)/1048576.0)) + ' MB' AS [FileSizeMB]

    Kind Regards,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • on your where clause - do WHERE Domain = @Domain AND convert(bigint, Filesize)  > 50000

  • ...
    (
    SELECT
    EPName,
    NTUserName,
    ParsedUserName,
    CAST(FileSize AS decimal(35,2)) AS FileSize, --<<--
    SUBSTRING (NTUserName,0,CHARINDEX('\',NTUserName)) AS Domain,
    ...

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • Thank you for your replies.

    I added: CAST(FileSize AS decimal(35,2)) AS FileSize,

    +

    WHERE Domain = @Domain AND convert(bigint, Filesize) > 50000

    That worked so many thanks.

    One last question...

    --I added
    ROUND(SUM(CAST([FileSize] AS BIGINT)/1048576.0),2)) + ' MB' AS [FileSizeMB],
    ROUND(SUM(CAST([FileSize] AS BIGINT)/1073741824.0),2) + ' GB' AS [FileSizeGB],

    This resulted in error:

    Msg 8114, Level 16, State 5, Line 14

    Error converting data type varchar to numeric.

    + ROUND did not round.

    Kind Regards,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • 2Tall wrote:

    Thank you for your replies.

    I added: CAST(FileSize AS decimal(35,2)) AS FileSize,

    +

    WHERE Domain = @Domain AND convert(bigint, Filesize) > 50000

    That worked so many thanks.

    One last question...

    --I added
    ROUND(SUM(CAST([FileSize] AS BIGINT)/1048576.0),2)) + ' MB' AS [FileSizeMB],
    ROUND(SUM(CAST([FileSize] AS BIGINT)/1073741824.0),2) + ' GB' AS [FileSizeGB],

    This resulted in error:

    Msg 8114, Level 16, State 5, Line 14 Error converting data type varchar to numeric.

    + ROUND did not round.

    Kind Regards,

    Phil.

    Look at what you have in those formulas and then look at the error message.  Do you think that SQL Server might just be having a problem adding the strings of MB or GB go a number and is there a way to change the number to a string?  You've gotta know there is and that you've done it before.  Give it a shot.

     

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

  • Thanks for your response Jeff.  Glad to see you are still going strong. I will have a play. Keep up the great work.

    Kind Regards,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • --This did the trick
    ROUND(SUM(CAST([FileSize] AS BIGINT)/1048576.0),2)) AS [FileSizeMB],
    ROUND(SUM(CAST([FileSize] AS BIGINT)/1073741824.0),2) AS [FileSizeGB]

    Thanks,

    Phil.

    -------------------------------------------------------------------------------------
    A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

    Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

    Tommy Cooper

  • I kinda wondered why the column data needed to have 'MB' in it when the column name said 'MB'?!

    SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

  • 2Tall wrote:

    Thanks for your response Jeff.  Glad to see you are still going strong. I will have a play. Keep up the great work.

    Kind Regards,

    Phil.

    It's been a while since I've seen you around, as well.  Hope this finds you and yours safe and going strong, as well.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
    "Change is inevitable... change for the better is not".

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)
    Intro to Tally Tables and Functions

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply