July 10, 2015 at 6:34 am
Hello All,
Any help would be appreciated on the following:
SQL to Set up Environment Tables:
CREATE TABLE vAmount
([RefID] int
,[AField] varchar (50)
,[FField] varchar (50)
,[AValue] numeric(7)
,[FValue] numeric(7)
,[A Expected] numeric(7)
,[IS_DIFF] varchar(2) NULL
,[kdiffer] numeric(7));
INSERT INTO #vAmount
( [RefID] ,[AField],[FField],[AValue],[FValue],[A Expected],[IS_DIFF],[kdiffer])
VALUES
(1234, 'Amount', 'Amount','100.0','100.0','200.0','Y',100 ),
(1235, 'Amount', 'Amount','120.1','130.1','120.1','Y',0 ),
(1236, 'Amount', 'Amount','129.3',NULL,'129.3','N',0 )
CREATE TABLE #vFee
([RefID] int
,[AField] varchar (50)
,[FField] varchar (50)
,[AValue] date
,[FValue] date
,[A Expected] date
,[IS_DIFF] varchar(2)
,[kdiffer] int);
INSERT INTO #vFee
( [RefID] ,[AField],[FField],[AValue],[FValue],[A Expected],[IS_DIFF],[kdiffer])
VALUES
(1234, 'Fee', 'Fee','2011-12-21','2011-12-21','2011-12-21','N',0 ),
(1235, 'Fee', 'Fee','2011-12-22','2011-12-21','2011-12-21','Y',1 ),
(1236, 'Fee', 'Fee','2011-12-21','2011-12-21','2011-12-21','N',0 )
Wanted results from combined view
CREATE TABLE #Wantedresults
([AllField] varchar (50)
,[Differences_Count] int --where value ='Y'
,[% Differences] numeric(7) --% Differences = Differences_Count / Total Number rows * 100
,[Total Difference (+ve)] numeric(7) -- Sum ([kdiffer]) where >= 0
,[Total Difference (-ve)] numeric(7)); -- Sum ([kdiffer]) where < 0
INSERT INTO #Wantedresults
([AllField],[Differences_Count],[% Differences],[Total Difference (+ve)],[Total Difference (-ve)])
VALUES ('Fee',1,33.33,100,0),
('Amount',1,50,1,0)
QUESTION: is Attached as I am not sure how to put the data in table format.
July 10, 2015 at 7:32 am
Happy to have the consumable data, but unfortunately, me understanding your requirement isn't happening. I see two slightly different source tables, where one appears to represent amounts versus expected amounts, and the amount of the difference, but the data doesn't appear to be consistent, as in one case, there's a clear difference of 100, and in another case, a difference of 10 appears, but is recorded as zero with no obvious reason why; and the other table represents dates and differences, but other than the ID value, there's no obvious relationship associated with any of the other values in that table to anything in the first table. Based on the desired results, it appears that the 2nd table is irrelevant, so I have to ask for more detail on exactly what these tables represent, and a better explanation of why your desired result is what it is, as that result table has percentages that don't make any sense. Clearly, some piece of information is missing from my understanding. Please elaborate.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply