Viewing 15 posts - 316 through 330 (of 541 total)
Testing has proved my amendments to the query are incorrect as the results are not as expected.
I posted a small sample set of data but have removed as I want...
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 10, 2009 at 4:13 am
Hi. I resolved the divide by zero by adding:
SELECT ProductId
,COUNT(1) OrderCount
,CASE WHEN COUNT(1) = 1 THEN SUM(MAN)/NULLIF(MANCount,0) ELSE AVG(MAN) END MAN
,CASE WHEN COUNT(1) =...
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 9, 2009 at 3:47 pm
Hi NULL. When I ran against the table I received:
Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Many thanks for your time and efforts.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 9, 2009 at 3:00 pm
Hi NULL. So the average cost is calculated by AVG(CASE of ELEMENTS / NumOfOrders ..emmmm.....
Recent testing has proved my original brief was incorrect to return the costs of the last...
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 9, 2009 at 10:44 am
Hi. I amended SUM(CASE to AVG(CASE as suggested. Still not quite there. I have attached the output.
The costs against the ProductId I queried all appear as COST1 and are as...
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 9, 2009 at 1:57 am
Thanks once again NULL.
Firstly apologies for the error in the attached query. The original query was amended to add an INNER JOIN to table Products, this can be commented out...
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 8, 2009 at 3:31 pm
Hello again. Testing has thrown up a bit of a problem. Where the OrderId field = ' ' the values are being summed and not averaged?
I have attached more sample...
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 8, 2009 at 10:37 am
Many thanks for all your comments. Mr. Celko I am a true SQL novice with many minutes of programming experience 🙂
Do not let the code posted mislead you to the...
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 8, 2009 at 6:48 am
Ha! Ha!
Keep up the good work guys. The day I start answering posts is the day I know I have learnt those new tricks.
Regards,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 6, 2009 at 2:14 pm
Thanks guys and yes I am an old git 🙂
Old Dogs, new tricks....possible?
Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 6, 2009 at 9:46 am
Chris I was just playing with the CTE concept as I am quite new to TSQL.
Moving forward part of the learning process is to know when to employ the correct...
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 5, 2009 at 2:56 pm
Thanks, not a million miles away from getting one right! 🙂
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 5, 2009 at 6:46 am
Thanks Chris.
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 2, 2009 at 12:57 pm
Thanks Guys. Null there are 4 product Types.
The table in question is a temporary table created from an excel file (will be dropped at some stage). I need to populate...
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 2, 2009 at 9:45 am
Hi. I have joined code provided by Null to an additional table as follows:
FROM Cost
INNER JOIN Products ON dbo.Cost.ProductId = dbo.Products.CrossReference
GROUP BY Cost.ProductId,OrderId) t1) t2
WHERE OrderRank <=5
GROUP BY ProductId
Referring back...
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper
January 1, 2009 at 3:28 pm
Viewing 15 posts - 316 through 330 (of 541 total)