| Author |
 Topic  |
|
|
smh
Yak Posting Veteran
USA
82 Posts |
 Posted - 07/15/2012 : 22:41:28
|
I am updating a table called tblRouteCashSalesHeader by summing values in the related detail table, tblRouteCashSalesDetail.
Routecashsalesheader has a field for bananas total and produce total . The way to determine if the product is a banana type or not (the productId is stored in the detail table) is to check a boolean in the product table: IsBananas. Here is what I have which works fine if there are bananas and non-banana products.
update tblRouteCashSalesHeader
set BananasTotalBoxes = (select sum(boxes) from tblRouteCashSalesDetail where tblRouteCashSalesDetail.RouteCashSalesHeaderID = 2 and
tblRouteCashSalesDetail.ProductID in (select tblproduct.ProductID from tblproduct where isbananas = 1)),
ProduceTotalBoxes = (select sum(boxes) from tblRouteCashSalesDetail where tblRouteCashSalesDetail.RouteCashSalesHeaderID =2 and
tblRouteCashSalesDetail.ProductID in (select tblproduct.ProductID from tblproduct where isbananas = 0))
where tblRouteCashSalesHeader.RouteCashSalesHeaderID = 2
-------------------------------
The problem is that if there are no bananas or if no produce, the select sum(boxes) returns a null value and the procedure crashes. I tried using isnull (see the case statement) but I still get null.
(what I tried)
update tblRouteCashSalesHeader
set ProduceTotalBoxes =
case when (select sum(boxes) from tblRouteCashSalesDetail where tblRouteCashSalesDetail.RouteCashSalesHeaderID = 2 and
tblRouteCashSalesDetail.ProductID in (select tblproduct.ProductID from tblproduct where isbananas = 0)) = null then 0
else
(select sum(boxes) from tblRouteCashSalesDetail where tblRouteCashSalesDetail.RouteCashSalesHeaderID = 2 and
tblRouteCashSalesDetail.ProductID in (select tblproduct.ProductID from tblproduct where isbananas = 0))
end
What can I do?
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 07/15/2012 : 23:39:45
|
update h
set h.BananasTotalBoxes = d.BananaTotal,
h.BananasTotalBoxes = d.ProduceTotal
FROM tblRouteCashSalesHeader h
inner join (
select RouteCashSalesHeaderID ,
sum(case when isbananas = 1 then boxes else 0 end) AS BananaTotal,
sum(case when isbananas = 0 then boxes else 0 end) AS ProduceTotal
from tblRouteCashSalesDetail
where tblRouteCashSalesDetail.RouteCashSalesHeaderID = 2
group by RouteCashSalesHeaderID
)d
On d.RouteCashSalesHeaderID = h.RouteCashSalesHeaderID
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
 |
|
|
smh
Yak Posting Veteran
USA
82 Posts |
Posted - 07/16/2012 : 10:45:42
|
Thanks for your help. I see from your answser that I did not make clear the table structures.
tblRouteCashSalesHeader has bananatotalboxes and producetotalboxes.
The tblroutecashsalesdetail has productID and boxes. Each detail record will have a product ID and the number of boxes.
The tblproduct has productID and isbananas, a boolean.
A product is either a banana or not a banana item(a produce item). So all banana items are totaled in the bananaboxtotal and everything not a banana goes into the producetotalboxes of the header table.
so the summing has to reference the product table which is why, I guess, I am having the problems. I could add the field "isbananas" to the detail table and then there would be no problem. Is that the best way to handle this? I know it is the easiest.
so the 3 tables and their fields:
1. Header table
HeaderID
bananatotalboxes
producetotalboxes
2. detail table
DetailID
HeaderID
boxes
productID
3. tblProduct
productID
Isbananas |
Edited by - smh on 07/16/2012 10:57:11 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 07/16/2012 : 11:31:43
|
its just a matter of additional join
update h
set h.BananasTotalBoxes = d.BananaTotal,
h.BananasTotalBoxes = d.ProduceTotal
FROM tblRouteCashSalesHeader h
inner join (
select sd.RouteCashSalesHeaderID ,
sum(case when p.isbananas = 1 then sd.boxes else 0 end) AS BananaTotal,
sum(case when p.isbananas = 0 then sd.boxes else 0 end) AS ProduceTotal
from tblRouteCashSalesDetail sd
inner join tblProduct p
ON p.ProductId = sd.ProductId
where sd.RouteCashSalesHeaderID = 2
group by sd.RouteCashSalesHeaderID
)d
On d.RouteCashSalesHeaderID = h.RouteCashSalesHeaderID
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
smh
Yak Posting Veteran
USA
82 Posts |
Posted - 07/16/2012 : 14:54:18
|
Thank you very much for this solution. It works perfectly. I also needed a sum of the totals for the header field "TotalBoxes" so I added:
update h
set h.BananasTotalBoxes = d.BananaTotal,
h.ProduceTotalBoxes = d.ProduceTotal,
h.TotalBoxes = d.BananaTotal + d.ProduceTotal -- this was added for the total
from ......
This also works. I could not see any other way offhand to get the total of the 2 detail fields. Maybe there is a problem if both are null, but then I presume the totalboxes would be zero. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
Posted - 07/16/2012 : 22:08:16
|
update h
set h.BananasTotalBoxes = d.BananaTotal,
h.BananasTotalBoxes = d.ProduceTotal,
h.TotalBoxes = d.Total
FROM tblRouteCashSalesHeader h
inner join (
select sd.RouteCashSalesHeaderID ,
sum(case when p.isbananas = 1 then sd.boxes else 0 end) AS BananaTotal,
sum(case when p.isbananas = 0 then sd.boxes else 0 end) AS ProduceTotal ,
sum(sd.boxes) as Total
from tblRouteCashSalesDetail sd
inner join tblProduct p
ON p.ProductId = sd.ProductId
where sd.RouteCashSalesHeaderID = 2
group by sd.RouteCashSalesHeaderID
)d
On d.RouteCashSalesHeaderID = h.RouteCashSalesHeaderID
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
|
smh
Yak Posting Veteran
USA
82 Posts |
Posted - 07/18/2012 : 14:24:21
|
Thank you again. But here is a question. It seems that the 'd' in this query functions a bit like a separate table. Where can I find rules and examples on this technique and when is best to use it. This probably could be done with table varialbes but here it is not needed.
d:
(
select sd.RouteCashSalesHeaderID ,
sum(case when p.isbananas = 1 then sd.boxes else 0 end) AS BananaTotal,
sum(case when p.isbananas = 0 then sd.boxes else 0 end) AS ProduceTotal ,
sum(sd.boxes) as Total
from tblRouteCashSalesDetail sd
inner join tblProduct p
ON p.ProductId = sd.ProductId
where sd.RouteCashSalesHeaderID = 2
group by sd.RouteCashSalesHeaderID
)d
Here is another similar one (also a solution from this site and again, something called MaxTable has been created and is manipulated as a table. I would love to see someone do an article on this or if one has been done on these techniques, to post the reference. Thanks.
WITH MaxTable ( positionID, effectivedate)
AS
( select positionID, max(effectivedate) AS MAXDate
from tblBudgetMaster
WHERE effectivedate <= CONVERT(VARCHAR,@edate,1)
group by positionID
)
insert into tblbudgetmasterselected(BudgetMasterID,positionID,ProjectID, EffectiveDate, FiscalYear,
BudgetedSalaryRate, AnnualSalary, FTE)
(SELECT t.BudgetMasterID, t.positionID, 0, CONVERT(VARCHAR,t.EFFECTIVEDATE,101) AS EFFECTIVEDATE, FiscalYear,
BudgetedSalaryRate, AnnualSalary, FTE
FROM tblBudgetMaster t INNER JOIN MaxTable m ON t.positionID = m.positionID
AND t.effectivedate = m.effectivedate)
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47099 Posts |
|
|
smh
Yak Posting Veteran
USA
82 Posts |
Posted - 07/19/2012 : 11:20:52
|
| Thanks. This is very helpful indeed. |
|
|
| |
Topic |
|
update stored Proc returns null
