CODEWITHSUNDEEP
sql-server
Jared Hilburn
Jared Hilburn

Reputation: 27

SQL Server to return text if there are transactions with multiple values

I am running a sale query where there could be multiple Order numbers per transaction. If this is the case, I would like the query to return the word "Multiple" in the column and if it only has one order, return the order #.

SELECT Distinct TOP 10000 [Store Number]
  ,[Store Name]
  ,[Register Sales Posting Date]
  ,[Transaction Date]
  ,[Register ID]
  ,[Transaction ID]
  ,[Net Sales]
  ,[Tax Exempt Sales]
  ,[Markdown]
  ,[Tax Amount]
  ,[Gross Sales]
  ,[Tax Exempt ID]
  ,CASE
     WHEN COUNT([Customer Order Number])>'1' THEN 'Multiple'
     ELSE[Customer Order Number]
   END
FROM [PropertyTax].[dbo].[SQLAExport]
WHERE [Store Number] = '4920'
  AND [Transaction Date] = '1/14/2011'
GROUP BY 
       [Store Number]
      ,[Store Name]
      ,[Register Sales Posting Date]
      ,[Transaction Date]
      ,[Register ID]
      ,[Transaction ID]
      ,[Net Sales]
      ,[Tax Exempt Sales]
      ,[Markdown]
      ,[Tax Amount]
      ,[Gross Sales]
      ,[Tax Exempt ID]
      ,[Customer Order Number]

Upvotes: 0

Views: 60

Answers (1)

jpw
jpw

Reputation: 44891

If you are using SQL Server 2012+ you can use count() over (partition by ...). If on a lower version you can get the count of orders per transaction (or w/e you're partitioning by) using a cross apply for example (see this SQL Fiddle for examples of both techniques).

Try removing the group by and change the case expression to use a windowed count instead (in this query I assumed that [Transaction ID] was the thing to partition by (that specifies a group), if it's not you might have to change it).

Maybe this is what you are looking for:

SELECT TOP 10000 
   [Store Number]
  ,[Store Name]
  ,[Register Sales Posting Date]
  ,[Transaction Date]
  ,[Register ID]
  ,[Transaction ID]
  ,[Net Sales]
  ,[Tax Exempt Sales]
  ,[Markdown]
  ,[Tax Amount]
  ,[Gross Sales]
  ,[Tax Exempt ID]
  , CASE WHEN COUNT([Customer Order Number]) OVER (PARTITION BY [Transaction ID]) > 1 
         THEN 'Multiple'
        ELSE [Customer Order Number] 
    END -- if [Customer Order Number] is a number and not a char you need to cast it
FROM [PropertyTax].[dbo].[SQLAExport]
WHERE [Store Number] = '4920'   
  AND [Transaction Date] = '1/14/2011'

Also, know that using top n without any order by clause won't give you a deterministic result, but rather a random selection of n rows (which might seem ordered but isn't guaranteed to be). Always include order by when you use top.

Upvotes: 3

Related Questions