Hi to all. I want the user to be able to specify a value in MB for 'Size' parameter when executing a report. The value is stored in bytes.
My code below:
DECLARE @Size bigint
DECLARE @Domain NVARCHAR (10)
DECLARE @StartDate datetime
DECLARE @EndDate datetime
SET @Size = '100'
SET @Domain = 'MyDomain'
SET @StartDate = '24 Jan 2022'
SET @EndDate = '28 Jan 2022'
;
WITH cteDataTransfer AS
(
SELECT
EPName,
NTUserName,
ParsedUserName,
CONVERT(CHAR(10),TransferredDate,103) AS TransferredDate,
CAST(FileSize AS decimal(35,2)) AS FileSize,
SUBSTRING (NTUserName,0,CHARINDEX('\',NTUserName)) AS Domain
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 < DATEADD(DAY, 1, @EndDate)
)
SELECT ParsedUserName AS UserName,Domain,TransferredDate,
SUM([FileSize]) AS SumOfFileSize
FROM cteDataTransfer
WHERE Domain = @Domain
GROUP BY TransferredDate,ParsedUserName,Domain
--HAVING SUM([FileSize]) > 104857600
HAVING SUM([FileSize] / 104857600) > @Size
HAVING SUM([FileSize]) > 104857600 - This returns 15 rows
HAVING SUM([FileSize]) /104857600 > @Size - This returns 5 rows
So my logic is flawed!
As always your expert advise much appreciated.
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
February 1, 2022 at 10:10 am
Doh. it should have been:
HAVING SUM([FileSize]) /1048576.0 > @Size
So I think I am all set.
Feel free to offer a better approach.
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
February 1, 2022 at 10:19 am
HAVING SUM([FileSize]) > 104857600 - This returns 15 ROWS
What is the value of [SumOfFileSize] for the 15 rows? How many of them are greater than 10485760000?
Based on your code, that number should be 5.
On a side note, you declare @Size as bigint.
Why are you assigning a string to a bigint variable? This causes SQL to have to jump through extra hoops and do an implicit conversion.
February 1, 2022 at 11:32 am
Hi. Thanks for the response.
"On a side note, you declare @Size as bigint."
I am showing my lack of understanding 🙂
What would you suggest?
HAVING SUM([FileSize]) /1048576.0 > @Size - This returns what I expected.
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
February 1, 2022 at 11:38 am
I amended to smallint.
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
Hi. Thanks for the response.
"On a side note, you declare @Size as bigint."
I am showing my lack of understanding 🙂
What would you suggest?
When assigning values to a string data type (char, varchar) you wrap the values in single quotes.
DECLARE @TextString varchar(20);
SET @TextString = 'Plain Text';
When assigning values to a unicode data type (nchar, nvarchar) you wrap the values in single quotes, and prepend the 1st quote with "N".
DECLARE @Domain nvarchar(10);
SET @Domain = N'MyDomain';
When assigning values to a numeric data type (int, decimal etc) you do not wrap the values in quotes.
DECLARE @Size bigint;
SET @Size = 100;
February 1, 2022 at 4:43 pm
When assigning values to a numeric data type (int, decimal etc) you wrap the values in single quotes.
DECLARE @Size bigint;
SET @Size = 100;
Copy/paste error, I'm guessing? As shown, no single quotes used with a numeric data type. 🙂
February 1, 2022 at 6:48 pm
Thanks, I am a little rusty!
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
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply