June 2, 2014 at 9:38 pm
Hello
I have a view FF_Email_IVGLVar which returns the following results
Account No IV Total GL Total Variance
4111-00-137 89,852.10 89,852.10 0.00
4101-00-137 83,673.86 83,673.80 0.06
6404-00-137 61,026.51 61,026.52 -.01
0000-00-137 174.95 NULL NULL
6202-22-506 100.00 NULL NULL
6217-00-137 217,498.50 217,498.52 -0.02
2101-00-137 2,033.60 2,033.60 0.00
6202-00-137 348,603.46 350,200.22 -1,596.76
I am only interested in anything with a variance of over 5c either way or NULL.
I have tried the follow 2 where clauses
select * from FF_Email_IVGLVar
where Variance IS NULL or Variance > '0.05' or Variance < '-0.05'
select * from FF_Email_IVGLVar
where Variance IS NULL or Variance NOT BETWEEN '-0.05' and '0.05'
I was expecting to see only the lines with the variances NULL, 0.06 and -1,596.76 but instead get the following results for either where clause
Account No IV Total GL Total Variance
4101-00-137 83,673.86 83,673.80 0.06
6404-00-137 61,026.51 61,026.52 -.01
0000-00-137 174.95 NULL NULL
6202-22-506 100.00 NULL NULL
6217-00-137 217,498.50 217,498.52 -0.02
It appears the NULL and greater than 5c part is working but the less than -5c is not giving the expected results.
If the script for the view is of any help this is what it looks like -
SELECT TOP (100) PERCENT T3.ACTNUMST AS [Account No], CONVERT(varchar, CONVERT(money, T1.[IV Total]), 1) AS [IV Total], CONVERT(varchar, CONVERT(money,
T2.[GL Total]), 1) AS [GL Total], CONVERT(varchar, CONVERT(money, T1.[IV Total] - T2.[GL Total]), 1) AS Variance
FROM (SELECT IVIVINDX, SUM(EXTDCOST) AS [IV Total]
FROM dbo.SEE30303
GROUP BY IVIVINDX) AS T1 LEFT OUTER JOIN
(SELECT A.ACTINDX, SUM(A.PERDBLNC) AS [GL Total]
FROM dbo.GL10110 AS A INNER JOIN
dbo.GL00105 AS B ON A.ACTINDX = B.ACTINDX
WHERE (B.ACTNUMST LIKE '%-137%') AND (B.ACTNUMST <> '0000-00-137')
GROUP BY A.ACTINDX) AS T2 ON T1.IVIVINDX = T2.ACTINDX INNER JOIN
dbo.GL00105 AS T3 ON T1.IVIVINDX = T3.ACTINDX
WHERE (T1.[IV Total] <> 0)
Also, any tips on how to get my results formatted in the forum post as a table? When I enter it as text it looks aligned at the time but the alignment looks all over the place when actually posted, took me a while to get it looking presentable.
Thanks 🙂
June 3, 2014 at 12:14 am
Try it without the quotes around the numeric constants.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2014 at 12:47 am
This comes back with the following error -
Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Seems to be an issue with how I've converted Variance to money format
CONVERT(varchar, CONVERT(money, T1.[IV Total] - T2.[GL Total]), 1) AS Variance
If I remove the convert and have
T1.[IV Total] - T2.[GL Total] AS Variance
The where clause returns the correct results but Variance is formatted to 5 decimal places. I know it's best practice to format the result at the front end which I generally do in Excel or SSRS, but this is a one off where it emails the results in a HTML table. I was trying to avoid figuring out formatting in HTML as my programming skills are still pretty amatuer.
Any ideas on why it doesn't recognise the negative in money format?
Thanks
June 3, 2014 at 1:02 am
Tania Mofflin (6/3/2014)
This comes back with the following error -Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Seems to be an issue with how I've converted Variance to money format
CONVERT(varchar, CONVERT(money, T1.[IV Total] - T2.[GL Total]), 1) AS Variance
If I remove the convert and have
T1.[IV Total] - T2.[GL Total] AS Variance
The where clause returns the correct results but Variance is formatted to 5 decimal places. I know it's best practice to format the result at the front end which I generally do in Excel or SSRS, but this is a one off where it emails the results in a HTML table. I was trying to avoid figuring out formatting in HTML as my programming skills are still pretty amatuer.
Any ideas on why it doesn't recognise the negative in money format?
Thanks
Look at this sample, observe the data types and the filter. You might want to either change the view or do a type cast.
😎
USE tempdb;
GO
DECLARE @FF_Email_IVGLVar TABLE
(
[Account No] VARCHAR(12) NOT NULL
,[IV Total] DECIMAL(18,5) NOT NULL
,[GL Total] DECIMAL(18,5) NULL
,[Variance] DECIMAL(18,5) NULL
);
INSERT INTO @FF_Email_IVGLVar
(
[Account No]
,[IV Total]
,[GL Total]
,[Variance]
)
VALUES
('4111-00-137',89852.10 ,89852.10 ,0.00)
,('4101-00-137',83673.86 ,83673.80 ,0.06)
,('6404-00-137',61026.51 ,61026.52 ,-.01)
,('0000-00-137',174.95 ,NULL ,NULL)
,('6202-22-506',100.00 ,NULL ,NULL)
,('6217-00-137',217498.50 ,217498.52,-0.02)
,('2101-00-137',2033.60 ,2033.60 ,0.00)
,('6202-00-137',348603.46 ,350200.22,-1596.76);
SELECT
*
FROM @FF_Email_IVGLVar FEI
WHERE ABS(ISNULL(FEI.Variance,1)) > 0.05
Results
Account No IV Total GL Total Variance
------------ ------------- ------------- ------------
4101-00-137 83673.86000 83673.80000 0.06000
0000-00-137 174.95000 NULL NULL
6202-22-506 100.00000 NULL NULL
6202-00-137 348603.46000 350200.22000 -1596.76000
June 3, 2014 at 1:11 am
Tania Mofflin (6/3/2014)
This comes back with the following error -Msg 8114, Level 16, State 5, Line 1
Error converting data type varchar to numeric.
Seems to be an issue with how I've converted Variance to money format
CONVERT(varchar, CONVERT(money, T1.[IV Total] - T2.[GL Total]), 1) AS Variance
If I remove the convert and have
T1.[IV Total] - T2.[GL Total] AS Variance
The where clause returns the correct results but Variance is formatted to 5 decimal places. I know it's best practice to format the result at the front end which I generally do in Excel or SSRS, but this is a one off where it emails the results in a HTML table. I was trying to avoid figuring out formatting in HTML as my programming skills are still pretty amatuer.
Any ideas on why it doesn't recognise the negative in money format?
Thanks
It's not the Money datatype that's the problem. It's the fact that you converted the data to VARCHAR.
--Jeff Moden
Change is inevitable... Change for the better is not.
June 3, 2014 at 1:24 am
OK so I'm not really totally around the difference between CAST and CONVERT but I will have a read up on them for future reference, always developing my skills.
I kind of fudged my way through it and changed it to
CAST(T1.[IV Total] - T2.[GL Total] AS decimal(18, 2)) AS Variance
and now the where clause returns the correct result.
Thankyou 🙂
June 3, 2014 at 1:58 am
It's not the Money datatype that's the problem. It's the fact that you converted the data to VARCHAR.
--Jeff Moden
OK now I remember why I converted the data to VARCHAR in the first place which I copied from another forum post I came across. CAST as Money and CAST AS decimal(18, 2) both return the result to 2 decimal places but I wanted it to have a comma eg 1,521,185.22. Neither of these achieve this?
Thanks
June 3, 2014 at 10:41 pm
Tania Mofflin (6/3/2014)
It's not the Money datatype that's the problem. It's the fact that you converted the data to VARCHAR.
--Jeff Moden
OK now I remember why I converted the data to VARCHAR in the first place which I copied from another forum post I came across. CAST as Money and CAST AS decimal(18, 2) both return the result to 2 decimal places but I wanted it to have a comma eg 1,521,185.22. Neither of these achieve this?
Thanks
Just out of interest.. My solution was to have another column Variance1 which was unformatted which I ran the where clause on and returned the correct results, but only displayed the Variance column on the HTML table. Probably not ideal but it's the only thing I could think that would give the result in the currency format I wanted
SELECT TOP (100) PERCENT T3.ACTNUMST AS [Account No],
CONVERT(varchar, CONVERT(money, T1.[IV Total]), 1) AS [IV Total],
CONVERT(varchar, CONVERT(money, T2.[GL Total]), 1) AS [GL Total],
CONVERT(varchar, CONVERT(money, T1.[IV Total] - T2.[GL Total]), 1) AS Variance,
T1.[IV Total] - T2.[GL Total] AS Variance1
Thanks
June 4, 2014 at 7:36 am
currency format, with symbol and commas, right aligned (output to text or file)
messy 😀
USE tempdb;
GO
DECLARE @testtable TABLE (
[AccountNo] VARCHAR(12) NOT NULL
, [IVTotal] DECIMAL(18, 5) NOT NULL
, [GLTotal] DECIMAL(18, 5) NULL
);
INSERT INTO @testtable (
[AccountNo]
, [IVTotal]
, [GLTotal]
)
VALUES
('4111-00-137',89852.10,89852.10)
,('4101-00-137',8.86,83673.80)
,('6404-00-137',61028.51555,61026.52)
,('0000-00-137',17.95678,NULL)
,('6202-22-506',1.01,2.1234)
,('6217-00-137',217498.50,217498.52)
,('2101-00-137',2033.60,2033.60)
,('6202-00-137',348603999.46,350200.22);
/*output to file or text*/
SELECT [AccountNo],
ISNULL(CAST(replicate(' ', 19 - len(CONVERT(VARCHAR, CONVERT(MONEY, IVTotal), 1)))
+ '£' + CONVERT(VARCHAR, CONVERT(MONEY, IVTotal), 1) AS VARCHAR(20)),
replicate(' ', 16) + 'null')AS IVTOTAL,
ISNULL(CAST(replicate(' ', 19 - len(CONVERT(VARCHAR, CONVERT(MONEY, GLTotal), 1)))
+ '£' + CONVERT(VARCHAR, CONVERT(MONEY, GLTotal), 1) AS VARCHAR(20)),
replicate(' ', 16) + 'null') AS GLTOTAL,
ISNULL(CAST(replicate(' ', 19 - len(CONVERT(VARCHAR, CONVERT(MONEY, IVtotal-GLTotal), 1)))
+ '£' + CONVERT(VARCHAR, CONVERT(MONEY, IVtotal - GLtotal), 1) AS VARCHAR(20)),
replicate(' ', 16) + 'null') AS variance
FROM @testtable
/*WHERE ABS(ISNULL([IVTotal] - [GLTotal], 1)) > 0.*/
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply