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
January 24, 2022 at 4:39 pm
Looks like you need BIGINT instead of INT.
Max for INT is 2,147,483,647
Your value is 16,520,105,984
January 24, 2022 at 5:08 pm
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
January 24, 2022 at 7:00 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 25, 2022 at 9:54 am
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
January 25, 2022 at 1:23 pm
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
Change is inevitable... Change for the better is not.
January 25, 2022 at 3:14 pm
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
January 25, 2022 at 3:18 pm
--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
January 25, 2022 at 3:49 pm
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) A socialist is someone who will give you the shirt off *someone else's* back.
January 25, 2022 at 6:41 pm
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
Change is inevitable... Change for the better is not.
March 1, 2023 at 7:46 am
This was removed by the editor as SPAM
April 24, 2023 at 6:50 am
This was removed by the editor as SPAM
September 5, 2023 at 4:15 am
This was removed by the editor as SPAM
October 11, 2023 at 4:48 am
This was removed by the editor as SPAM
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply