Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

If data exists in view return data otherwise return 1 percent for each farm & egg type

Status
Not open for further replies.
Stevan23

Stevan23

Technical User
Jul 20, 2012
11
AU
Hi all,

We are using Microsoft SQL Server 2008 and are querying a SQL view that contains information about our egg grade percentages for each farm.

A manager wants as part of the query if a farm does not exist for the last seven days then it is it return a default percentage of one for the farm per egg type.

If there is data, then the data will be returned as per the information in the view.

The below code is working, however I need to combine for each farm (we have 15 farms in total). I have tried UNION ALL but the SQL is not liking the statement.

My SQL knowledge is fairly good, but this is a bit beyond me. Please can someone help.

SQL: 
DECLARE @FromDate date
DECLARE @ToDate date

SET @FromDate = CONVERT(date, DATEADD(d,-7,getdate()))
SET @ToDate = CONVERT(date, getdate())

IF NOT EXISTS(
SELECT LEFT(t0.SupplierName,LEN(t0.SupplierName)-2) As Farm, t0.Dscription, (case when t0.Dscription = 'Oversize' then sum(t0.Eggs) / sum(t0.TotalEggs/2) else sum(t0.Eggs) / sum(t0.TotalEggs) end)*100 As EggPercent 
FROM dbo.Paul_MOBA_History_SB t0
WHERE t0.FirstDate >= @FromDate and t0.FirstDate <= @ToDate AND LEFT(t0.SupplierName,LEN(t0.SupplierName)-2) = 'Berkana Park'
GROUP BY t0.SupplierName, t0.Dscription)
BEGIN
   SELECT 'Berkana Park' As Farm, t0.Dscription, 1 As EggPercent
   FROM dbo.Paul_MOBA_History_SB t0
   WHERE t0.FirstDate >= @FromDate and t0.FirstDate <= @ToDate AND t0.SupplierName not like '%Strathbogie%' AND t0.SupplierName not like '%Dirties rewash%' AND t0.SupplierName not like '%End of Day%' AND t0.SupplierName not like '%NOSAP%'
   GROUP BY t0.Dscription
END
ELSE 
BEGIN
   SELECT LEFT(t0.SupplierName,LEN(t0.SupplierName)-2) As Farm, t0.Dscription, (case when t0.Dscription = 'Oversize' then sum(t0.Eggs) / sum(t0.TotalEggs/2) else sum(t0.Eggs) / sum(t0.TotalEggs) end)*100 As EggPercent
   FROM dbo.Paul_MOBA_History_SB t0
   WHERE t0.FirstDate >= @FromDate and t0.FirstDate <= @ToDate AND t0.SupplierName like '%Berkana%'
   GROUP BY t0.SupplierName, t0.Dscription
END
 
Sort by date Sort by votes
Hi,

You can use ISNULL function ; isnull(expression,1) as EggPercent


SQL: 
    SELECT LEFT(t0.SupplierName,LEN(t0.SupplierName)-2) As Farm, t0.Dscription
[indent][/indent], ISNULL((case when t0.Dscription = 'Oversize' then sum(t0.Eggs) / sum(t0.TotalEggs/2) else sum(t0.Eggs) / sum(t0.TotalEggs) end)*100,1) As EggPercent
   FROM dbo.Paul_MOBA_History_SB t0
   WHERE t0.FirstDate >= @FromDate and t0.FirstDate <= @ToDate AND t0.SupplierName like '%Berkana%'
   GROUP BY t0.SupplierName, t0.Dscription

Can you post some sample values?

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin
 
Upvote 0 Downvote
Hi SabinUE,

Thanks for the quick response. Would that still be okay if there is no data for the farm itself?

To clarify, the view's data is filled out when the eggs from a farm are graded (suppliername, date etc.) for a date. If no eggs are graded for that farm, the SupplierName, Date etc. is not filled out, so the farm and dscription doesn't exist for that farm for the day..

Some sample data:
Example 1 (data is found in view during the last 7 days):
Farm Dscription EggPercent
Berkana Park 55 9.9413602235984
Berkana Park Blood 0
Berkana Park Crack 3.44531521163296
Berkana Park Dirt 6.30057909062677
Berkana Park Jumbo 21.0683034654098
Berkana Park Large 2.16839297784111
Berkana Park Liquid 1.77563435085219
Berkana Park Medium 0.126048117498767
Berkana Park Oversize 0.593704901262308
Berkana Park Shell 0
Berkana Park SJumbo 3.55309548601597
Berkana Park Undersize 0.00182678431157633
Berkana Park XLarge 51.0257393909501

Example 2 (data is not found in view during the last 7 days):
Farm Dscription EggPercent
Berkana Park 55 1
Berkana Park Blood 1
Berkana Park Crack 1
Berkana Park Dirt 1
Berkana Park Jumbo 1
Berkana Park Large 1
Berkana Park Liquid 1
Berkana Park Medium 1
Berkana Park Oversize 1
Berkana Park Shell 1
Berkana Park SJumbo 1
Berkana Park Undersize 1
Berkana Park XLarge 1
 
Upvote 0 Downvote
SQL: 
declare @FromDate as DATE
	 ,@ToDate as DATE

SET @FromDate=CONVERT(date, DATEADD(d,-7,getdate()))
SET @ToDate=CONVERT(date, getdate())

;with Farm
AS
(select 'Berkana Park' as SupplierName, 'Blood' as Dscription ,0 as EggPercent ,'2014-03-15' as FirstDate union all
 select 'Berkana Park','Crack'	, 3.44531521163296,'2014-03-15' union all
 select 'Berkana Park','Dirt',6.30057909062677,'2014-03-15' union all
 select 'Berkana Park','Jumbo',21.0683034654098,'2014-03-15' union all
 select 'Berkana Park','BloodTest',21.0683034654098,'2014-02-16' union all

 select 'Berkana Park2','Crack',20,'2014-03-16' union all
 select 'Berkana Park2','JumboTest',20,'2014-02-02' union all
 select 'Berkana Park2','DirtTest',20,'2014-02-02'
 )
 


select SupplierName,Dscription,EggPercent--,FirstDate 
from Farm as t0
where t0.FirstDate >= @FromDate and t0.FirstDate <= @ToDate AND t0.SupplierName like '%Berkana%'

union all

select
	t0.SupplierName,t0.Dscription,1 as EggPercent
from 
(
	select t0.SupplierName,t0.Dscription
	from Farm as t0
	except
	select t0.SupplierName,t0.Dscription
	from Farm as t0
	where t0.FirstDate >= @FromDate and t0.FirstDate <= @ToDate AND t0.SupplierName like '%Berkana%'
)t0

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin
 
Upvote 0 Downvote
Hi SabineUE,

I've run the query and that's perfect.

I'm presuming I would only need this section for the view (and replace Farm with the name of the view?):

SQL: 
select SupplierName,Dscription,EggPercent--,FirstDate 
from Farm as t0
where t0.FirstDate >= @FromDate and t0.FirstDate <= @ToDate AND t0.SupplierName like '%Berkana%'

union all

select
	t0.SupplierName,t0.Dscription,1 as EggPercent
from 
(
	select t0.SupplierName,t0.Dscription
	from Farm as t0
	except
	select t0.SupplierName,t0.Dscription
	from Farm as t0
	where t0.FirstDate >= @FromDate and t0.FirstDate <= @ToDate AND t0.SupplierName like '%Berkana%'
 
Upvote 0 Downvote
Yes, you can try it

S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin
 
Upvote 0 Downvote
Hi SabinUE,

Thanks for the help, I've changed the query to look at my view but it also seems to pick up the other farms (example Kerr Farm)?

Do I change the first select statement after the Union All to only look for Berkana?

SQL: 
select SupplierName,Dscription,EggPercent--,FirstDate 
from Farm as t0
where t0.FirstDate >= @FromDate and t0.FirstDate <= @ToDate AND t0.SupplierName like '%Berkana%'

union all

select
	t0.SupplierName,t0.Dscription,1 as EggPercent
from 
(
	select t0.SupplierName,t0.Dscription
	from Farm as t0
        where t0.SupplierName like '%Berkana%'
	except
	select t0.SupplierName,t0.Dscription
	from Farm as t0
	where t0.FirstDate >= @FromDate and t0.FirstDate <= @ToDate AND t0.SupplierName like '%Berkana%'
 
Upvote 0 Downvote
u can drop the where condition :AND t0.SupplierName like '%Berkana%' from both selects

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin
 
Upvote 0 Downvote
Hi sabinUE,

I've made some adjustments to the query where I am only looking at Berkana Park for now and I'm getting the actual results for the last week but also I am receiving the 1 Eggpercent as below:

Farm Dscription EggPercent
Berkana Park 55 9.9413602235984
Berkana Park Blood 0
Berkana Park Crack 3.44531521163296
Berkana Park Dirt 6.30057909062677
Berkana Park Jumbo 21.0683034654098
Berkana Park Large 2.16839297784111
Berkana Park Liquid 1.77563435085219
Berkana Park Medium 0.126048117498767
Berkana Park Oversize 0.593704901262308
Berkana Park Shell 0
Berkana Park SJumbo 3.55309548601597
Berkana Park Undersize 0.00182678431157633
Berkana Park XLarge 51.0257393909501
Berkana Park 55 1
Berkana Park Blood 1
Berkana Park Crack 1
Berkana Park Dirt 1
Berkana Park Jumbo 1
Berkana Park Large 1
Berkana Park Liquid 1
Berkana Park Medium 1
Berkana Park Oversize 1
Berkana Park Shell 1
Berkana Park SJumbo 1
Berkana Park Undersize 1
Berkana Park XLarge 1
 
Upvote 0 Downvote
I've made some adjustments to the query
can we see the adjustments ?

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin
 
Upvote 0 Downvote
Hi sabinUE

Please find the code below:

SQL: 
declare @FromDate as DATE
	 ,@ToDate as DATE

SET @FromDate=CONVERT(date, DATEADD(d,-7,getdate()))
SET @ToDate=CONVERT(date, getdate())

select (case when SupplierName = 'Cage' then 'Cage' else LEFT(t0.SupplierName,LEN(t0.SupplierName)-2) end) As Farm, t0.Dscription, (case when t0.Dscription = 'Oversize' then sum(t0.Eggs) / sum(t0.TotalEggs/2) else sum(t0.Eggs) / sum(t0.TotalEggs) end)*100 As EggPercent 
from dbo.Paul_MOBA_History_SB as t0
where t0.FirstDate >= @FromDate and t0.FirstDate <= @ToDate AND t0.SupplierName like '%Berkana%'
GROUP BY t0.SupplierName, t0.Dscription
union all

select
	t0.SupplierName,t0.Dscription,1 as EggPercent
from 
(
	select t0.SupplierName,t0.Dscription
	from dbo.Paul_MOBA_History_SB as t0
	WHERE t0.SupplierName like '%Berkana%'
	except
	select t0.SupplierName,t0.Dscription
	from dbo.Paul_MOBA_History_SB as t0
	where t0.FirstDate >= @FromDate and t0.FirstDate <= @ToDate AND t0.SupplierName like '%Berkana%'
)t0
 
Upvote 0 Downvote
It's possible that , in view/table dbo.Paul_MOBA_History_SB, the value for FirstDate is null?

- add another column to those select :

select ..., 1 as express
union all
select ....,2

to be able to find from where is coming those rows (first select or second select)
depending of what result , you can comment the other select and run only the select in question

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin
 
Upvote 0 Downvote
Hi sabinUE,

Thanks for that, I have added the extra columns as you suggested and I'm getting these results:

Farm Dscription EggPercent express
Berkana Park 55 9.9413602235984 1
Berkana Park Blood 0 1
Berkana Park Crack 3.44531521163296 1
Berkana Park Dirt 6.30057909062677 1
Berkana Park Jumbo 21.0683034654098 1
Berkana Park Large 2.16839297784111 1
Berkana Park Liquid 1.77563435085219 1
Berkana Park Medium 0.126048117498767 1
Berkana Park Oversize 0.593704901262308 1
Berkana Park Shell 0 1
Berkana Park SJumbo 3.55309548601597 1
Berkana Park Undersize 0.00182678431157633 1
Berkana Park XLarge 51.0257393909501 1
Berkana Park 55 1 2
Berkana Park Blood 1 2
Berkana Park Crack 1 2
Berkana Park Dirt 1 2
Berkana Park Jumbo 1 2
Berkana Park Large 1 2
Berkana Park Liquid 1 2
Berkana Park Medium 1 2
Berkana Park Oversize 1 2
Berkana Park Shell 1 2
Berkana Park SJumbo 1 2
Berkana Park Undersize 1 2
Berkana Park XLarge 1 2
 
Upvote 0 Downvote
run this:
SQL: 
select t0.SupplierName,t0.Dscription
	from dbo.Paul_MOBA_History_SB as t0
	WHERE t0.SupplierName like '%Berkana%'
GROUP BY t0.SupplierName,t0.Dscription

and this:
SQL: 
select t0.SupplierName,t0.Dscription
	from dbo.Paul_MOBA_History_SB as t0
	where t0.FirstDate >= @FromDate and t0.FirstDate <= @ToDate AND t0.SupplierName like '%Berkana%'
group by t0.SupplierName,t0.Dscription


Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin
 
Upvote 0 Downvote
Hi sabinUE,

I think I've found out why it was showing two results.

From the 13/12/2013, the naming of the suppliers changed from Berkana Park to Berkana Park 1 and Berkana Park 2, so when the LEFT(t0.SupplierName,LEN(t0.SupplierName)-2) is taking place after the union all, it shows as 'Berkana Pa' and 'Berkana Park' when running just that segment.

Overall it hides the 'Berkana Pa' in the 'Berkana Park' (not sure how). The code I'm running is now:

SQL: 
declare @FromDate as DATE
	 ,@ToDate as DATE

SET @FromDate=CONVERT(date, DATEADD(d,-7,getdate()))
SET @ToDate=CONVERT(date, getdate())

select LEFT(t0.SupplierName,LEN(t0.SupplierName)-2) As Farm, t0.Dscription, (case when t0.Dscription = 'Oversize' then sum(t0.Eggs) / sum(t0.TotalEggs/2) else sum(t0.Eggs) / sum(t0.TotalEggs) end)*100 As EggPercent
from dbo.Paul_MOBA_History_SB as t0
where t0.FirstDate >= @FromDate and t0.FirstDate <= @ToDate AND t0.SupplierName like '%Berkana%'
GROUP BY t0.SupplierName, t0.Dscription
union all

select
	t0.Farm, t0.Dscription, 1 as EggPercent
from 
(
	select LEFT(t0.SupplierName,LEN(t0.SupplierName)-2) As Farm, t0.Dscription
	from dbo.Paul_MOBA_History_SB as t0
	WHERE t0.SupplierName like '%Berkana%' AND t0.FirstDate > '20131213'
	except
	select LEFT(t0.SupplierName,LEN(t0.SupplierName)-2) As Farm, t0.Dscription
	from dbo.Paul_MOBA_History_SB as t0
	where t0.FirstDate >= @FromDate and t0.FirstDate <= @ToDate AND t0.SupplierName like '%Berkana%'
)t0

And the results are now:
Farm Dscription EggPercent
Berkana Park 55 9.9413602235984
Berkana Park Blood 0
Berkana Park Crack 3.44531521163296
Berkana Park Dirt 6.30057909062677
Berkana Park Jumbo 21.0683034654098
Berkana Park Large 2.16839297784111
Berkana Park Liquid 1.77563435085219
Berkana Park Medium 0.126048117498767
Berkana Park Oversize 0.593704901262308
Berkana Park Shell 0
Berkana Park SJumbo 3.55309548601597
Berkana Park Undersize 0.00182678431157633
Berkana Park XLarge 51.0257393909501
 
Upvote 0 Downvote
Hi,

I hope that now it's ok.


S

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin
 
Upvote 0 Downvote
Hi sabinUE,

I think it is now. Thanks for all the help and for teaching me some more about what SQL can do.

Have a great rest of the night or day!
 
Upvote 0 Downvote
Hi,

Glade to help!
Have a nice day also!

Ce-am pe mine am si-n dulap, cand ma-mbrac zici ca ma mut
sabin
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

coorsman
  • Locked
  • Question
Column Data Type. 2
Replies
9
Views
318
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
SELECT TOP 1 of Each of a Group
Replies
5
Views
172
grnzbra
grnzbra
peac3
  • Locked
  • Question
fill the gap of hierarchy data
Replies
11
Views
385
Chris Miller
Chris Miller
grnzbra
  • Locked
  • Question
Is &quot;IF EXISTS()...&quot; better than &quot;IF (SELECT COUNT...) &gt; 0&quot;
Replies
4
Views
1K
gmmastros
gmmastros
Brianfree
  • Locked
  • Question
return unique rows only
Replies
11
Views
215
Brianfree
Brianfree

Part and Inventory Search

Sponsor

Back
Top