Creating a Case Statement to generate a value

[andyfresh]

Hi This should be a simple one for those of you who know sql as im quite new to it.

Basically I need a value after InvoiceContents.QuantityDispatched AS Quantity and that value depends on the case statements. After that value has been determined I would like it then to carry on selecting the columns from InvoiceContents.Price like normal.

I dont think my attempt at it is correct

Regards

Andy


SQL



SELECT InvoiceDetails.InvoiceCode, OrganisationDetails.Name AS [Outlet Name], OrganisationDetails_1.Name AS [Company Name],
ProductGroupNames.GroupName, FamilyCode2.FamilyName, DescriptionCode3.DescriptionName AS [Product Name], InvoiceDetails.InvoiceDate,
InvoiceContents.QuantityDispatched AS Quantity,


CASE
WHEN InvoiceContents.Split = > 1 THEN SplitCode8.SplitName
WHEN InvoiceContents.Split = 0 THEN BulkCode9.BulkName
WHEN InvoiceContents.Split = NULL THEN CASE
WHEN InvoiceContents.Price < BulkProductsMinPrice.MinPrice THEN SplitCode8.SplitName
WHEN InvoiceContents.Price > BulkProductsMinPrice.MinPrice THEN BulkCode9.BulkName
Else SplitCode8.SplitName
End
End


InvoiceContents.Price, InvoiceContents.VAT AS Rate, InvoiceDetails.VAT,
OrganisationDetails_2.Name AS [Wholesaler Name], InvoiceDetails.OrderID, InvoiceDetails.DatePlaced,
InvoiceDetails.DateDispatched AS [Delivery Date]
FROM InvoiceContents INNER JOIN
InvoiceDetails ON InvoiceContents.InvoiceID = InvoiceDetails.OrderID INNER JOIN
OrganisationDetails ON InvoiceDetails.OutletID = OrganisationDetails.OrganisationID INNER JOIN
ProductDetails ON InvoiceContents.ProductID = ProductDetails.prod_id INNER JOIN
ProductGroupNames ON ProductDetails.code1 = ProductGroupNames.GroupID INNER JOIN
FamilyCode2 ON ProductDetails.code2 = FamilyCode2.FamilyID INNER JOIN
DescriptionCode3 ON ProductDetails.code3 = DescriptionCode3.DescriptionID INNER JOIN
OrganisationDetails OrganisationDetails_1 ON InvoiceContents.CompanyID = OrganisationDetails_1.OrganisationID INNER JOIN
OrganisationDetails OrganisationDetails_2 ON InvoiceContents.WholesalerID = OrganisationDetails_2.OrganisationID INNER JOIN
SplitCode8 ON ProductDetails.code8 = SplitCode8.SplitID INNER JOIN
BulkCode9 ON ProductDetails.code9 = BulkCode9.BulkID INNER JOIN
BulkProductsMinPrice ON InvoiceContents.ProductID = BulkProductsMinPrice.ProductID
WHERE (CONVERT(datetime, InvoiceDetails.InvoiceDate, 102) BETWEEN CONVERT(DATETIME, '2006-01-01 00:00:00', 102) AND CONVERT(DATETIME,
'2006-01-13 00:00:00', 102))
GROUP BY InvoiceContents.CompanyID, InvoiceContents.ProductID, InvoiceDetails.InvoiceCode, OrganisationDetails.Name, ProductGroupNames.GroupName,
FamilyCode2.FamilyName, DescriptionCode3.DescriptionName, InvoiceDetails.InvoiceDate, InvoiceContents.QuantityDispatched,
InvoiceContents.Price, InvoiceDetails.VAT, InvoiceContents.VAT, OrganisationDetails_1.Name, OrganisationDetails_2.Name, InvoiceDetails.OrderID,
InvoiceDetails.DatePlaced, InvoiceDetails.DateDispatched, InvoiceContents.Split, SplitCode8.SplitName, BulkCode9.BulkName
HAVING (InvoiceContents.CompanyID = 447)

 

[gmmastros]

This is probably your problem.

InvoiceContents.Split = NULL

Null is unknown, so knowing whether something is EQUAL to unknown cannot be known either. Try changing to...

InvoiceContents.Split Is NULL

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[gmmastros]

Oh, and your original question...

Field1,
Field2,
CASE 
WHEN InvoiceContents.Split = > 1 THEN SplitCode8.SplitName 
WHEN InvoiceContents.Split = 0 THEN BulkCode9.BulkName 
WHEN InvoiceContents.Split = NULL THEN CASE 
                WHEN InvoiceContents.Price < BulkProductsMinPrice.MinPrice THEN SplitCode8.SplitName 
                WHEN InvoiceContents.Price > BulkProductsMinPrice.MinPrice THEN BulkCode9.BulkName 
                Else SplitCode8.SplitName
                End
End [red]As FieldName,[/red]
AnotherField1,
AnotherField2

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom