April 4, 2014 at 7:13 am
Wow - that's a lot of stuff!! May take a while to get it deciphered.
As for generating the data, I recommend SSMS Tools Pack from Mladen Pradjic or the free util_generate_inserts from Vyas Kondreddi (circa 2001 - still works and I still use it!) to generate your data.
Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service
April 4, 2014 at 7:37 am
Thanks for your response, Since seeing the prior comments about my post and detail it possessed i thought I'd give you all the information possible at the moment. I've actually made a little progress i've now got the results showing by using Use HP_Sales_Builder
Go
SELECT distinct
GBPriceList.[Mfr_Part_Num],
[HP_ Long_ description],
[HP_ Short_ Description],
--Exhibit_Unpivoted.Exhibit,
WeightKGS,
WarrantyCode,
ProductLine,
Serialized,
ListPrice,
Prod_Class,
Exhibit_Discount.Discount AS Discount
FROM GBPriceList,LongDescr,ShortDescr, Exhibit_Discount
right Join Exhibit_Unpivoted
on Exhibit_Discount.Exhibit = Exhibit_Unpivoted.Exhibit
Where
GBPriceList.[Mfr_Part_Num] = Exhibit_Unpivoted.Mfr_Part_Number
AND
GBPriceList.[Mfr_Part_Num] = LongDescr.[Mfr_ Part_ Num]
AND
GBPriceList.[Mfr_Part_Num] = ShortDescr.[Mfr_ Part_ Number]
--ORDER BY [Mfr_ Part_ Num]
--Group By GBPriceList.[Mfr_Part_Num], [HP_ Long_ description],
--[HP_ Short_ Description],
--WeightKGS,
--WarrantyCode,
--ProductLine,
--Serialized,
--ListPrice,
--Prod_Class,
--Discount
i tried adding this Where DISCOUNT = ISNULL(NULL,0) OR Discount LIKE '0.%%'
but that didn't work either.
I've now got the zeros but i've realised the problem is due to null values, i still only have 240 roughly 0 discounts and the rest are contained as nulls which i just need to update as 0's in this query somehow.
April 4, 2014 at 7:44 am
You're where predicate here doesn't make sense.
Where DISCOUNT = ISNULL(NULL,0) OR Discount LIKE '0.%%'
The isnull is replacing NULL with a 0. And I don't quite understand the Like predicate at all since Discount is a float.
Try this and see if helps.
Where ISNULL(DISCOUNT, 0) = 0
Also, you really should switch your joins around like I showed you several posts ago.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 4, 2014 at 7:45 am
The IsNull should be used like this;
SELECT distinct
GBPriceList.[Mfr_Part_Num],
[HP_ Long_ description],
[HP_ Short_ Description],
--Exhibit_Unpivoted.Exhibit,
WeightKGS,
WarrantyCode,
ProductLine,
Serialized,
ListPrice,
Prod_Class,
IsNull(Exhibit_Discount.Discount, 0) AS Discount
rather than in the WHERE clause if you want to show null discounts as zero.
April 4, 2014 at 7:46 am
i'll give it ago now, thanks 😀
April 4, 2014 at 8:03 am
In response to your post Sean i haven't changed around the joins as i don't fully understand how they work yet i need to do a little more reading into that but i can see how it's easier. I removed the Where DISCOUNT = ISNULL(NULL,0) OR Discount LIKE '0.%%' replaced it with IsNull(Exhibit_Discount.Discount, 0) AS Discount and that worked fine so thanks for that. I've now all zero discounts but the major problem is the duplicates in the rows, one thing i should note is that i need to have the discounts so the highest value is displayed e.g. 0.00<0.12 so take the 0.12. i wonder if with this sort of filtering i could resolve the duplicate mfr-part-numbers.
April 4, 2014 at 8:33 am
gedhinch (4/4/2014)
In response to your post Sean i haven't changed around the joins as i don't fully understand how they work yet i need to do a little more reading into that but i can see how it's easier.
+100000 to that!!!! It is refreshing to see somebody not want to change code when they don't fully understand it. I would recommend reading up on joins and you will gain the understanding (and comfort) as you progress.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply