Sum records if Null is present in second record
-
Not sure if this should even ben done in SQL or not...my director believes we could script prior to import...but I don't know how to script outside SQL...help!!
Okay, so here's what my data looks like
Line # Customer # Ticket # Opp Code Labor Amt Parts Amt 1 1234 57 891 $100.00 2 971 $50.00 3 $75.00 4 654 $500.00 5 3456 56 999 $20.00 254 $12.00 (Line number being the Identity column in the staging table that this file is imported into)Records 2, 3, and 4 are line item's on ticket 57, lines 2 and 3 are parts amounts for the same opp code. I need to sum these two lines and import as one (into a final table...doesn't need to be summed during transformation...unless it would be easier to do??)This needs to be performed on a nightly basis after hours, it can't be something that requires manual intervention on a daily basis. There is already a sproc in place which inserts child records (2, 3, and 4) into a Detail/Line Item table and relates them to the Parent record (Line 1) in a Service/Header table.Alternatively, I could import lines 2 and 3 as individual records into the service table...however I would need to update the null Opp Code on Line 3 to the value on Line 2...but this method would require me to make a "sub child" record. So far this is the only method I can think of...but if anyone has a better idea PLEASE let me know!! -
If you already have something that gives you 57 on records 1 through 4, then a normal sum query should do the job, right?
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
No...not really, because I only want to sum lines 2 and 3 not 1 through 4. Lines 2 and 3 constitute 1 line item.
I think I'll just have to create a "sub-child" category in the existing sproc, similar to how child (line item records) are associated with parent (header item records). The sproc loops through the staging table looking for all null values below the first header row...so I'll add another loop to loop through the opp codes field. I had kind of hoped to avoid this...so if anyone has an idea please let me know.
Thanks
-
Oh, sorry - I misread what you wanted to group by.
The key here is just to create a query that gives you that value for 'Opp Code'. Bear with me for a few minutes.
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
Try this, using SQL2005.
create table #tmp (id int, oppcode int, partsamt int)
insert into #tmp values (1,891,100)
insert into #tmp values (2,971,50)
insert into #tmp values (3,null,75)
insert into #tmp values (4,654,500)
select * from #tmp
select t2.oppcode, sum(t1.partsamt)
from #tmp t1
cross apply
(select top 1 oppcode from #tmp t2
where t2.id <= t1.id
and not t2.oppcode is null
order by t2.id desc) t2
group by t2.oppcode
drop table #tmp
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
Thanks for the quick reply! Unfortunately though, we're using SQL 2000...I don't believe Cross Apply is applicable here. I did try to run the query using a cross join...but SQL doesn't like the t1 reference in the sub query. I see where you're going with this, I'll try some further testing over here and let you know if I come up with anything
-
--You can create a function to do something similar:
create function dbo.find_megans_oppcode(@id int) returns int
begin
declare @res int
select top 1 @res = oppcode from megan_tmp t2
where t2.id <= @id
and not t2.oppcode is null
order by t2.id desc
return (@res)
end
--And then you can:
select dbo.find_megans_oppcode(id), sum(t1.partsamt)
from megan_tmp t1
group by dbo.find_megans_oppcode(id)
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
Hi,
Use isnull to handle nulls in Sum.
Select sum(isnull(col,0)) from TABLEA
Regards,
AMIT GUPTA..
-
Aargh
didn't come through, have to write again.To be honest, I would insist that the import file must have values in all columns. This approach - "if you don't find a value, dig back until you find some and use it" - is IMHO very unreliable.
If change of import file is not an option, or not at the moment, my preferred choice would probably be a separate update of staging table before the actual data processing starts. This update would fill gaps in all columns (or at least OppCode, if you've solved the rest somehow - but I'd vote for all). One advantage of this solution is that once you start getting data in better shape, you just skip the extra update and the rest can remain in place. Of course, it needs to be tested on actual dataload; I have no idea how many rows you import every night, how long it takes etc. Anyway, I think that an extra update (set based) should not be worse for performance than a UDF.
-
Yeah, you can just do:
update megan_tmp
set oppcode = dbo.find_megans_oppcode(id)
where oppcode is null
after importing the data.
Rob Farley
LobsterPot Solutions & Adelaide SQL Server User Group
Company: http://www.lobsterpot.com.au
Blog: http://blogs.lobsterpot.com.au -
You totally rock Rob!!!!
The function works perfectly!! Thanks for you help...and everyone else too
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply