Link to home
Start Free TrialLog in
Avatar of Fred Webb
Fred WebbFlag for United States of America

asked on

Display massage when no results found.

I have some code that displays the sales totals for the previous day for multiple warehouse sites (LocationCode) , and it works well however I also want 'No Sales for this date' displayed in the (DailySales) column if there are no sales for that date which works if there is absolutely no sales for any ItemClass in that LocationCode, however, if one of the LocationCodes has a sale for a few ItemClass's it will properly display the results but it it will also display  'No Sales for this date' for those ItemClass's it didn't have any sales for grouped into one row. See Image.

User generated image

SELECT

 'CA' As Company,CAST(DocumentDate AS VARCHAR) AS DocumentDate,ItemClass = CASE GROUPING(ItemClass) WHEN 1 THEN 'Total Sales-CA' ELSE ItemClass END,

  DailySales =  FORMAT(SUM(SALES),'C','en' )

FROM ACANI.dbo.SSG_SalesAna_ItemDtl

WHERE     (NOT (CustomerNumber LIKE 'RETAIL%')) AND (YEAR1 = 2023) AND (LocationCode = 'CENTRAL-CA') AND (DocumentDate >= dateadd(day,datediff(day,2,GETDATE()),0)

        AND DocumentDate < dateadd(day,datediff(day,0,GETDATE()),0)) AND (SOPTYPE = 3)AND (SALES) > 0

GROUP BY ROLLUP(ItemClass),DocumentDate

UNION 

SELECT 'CA' As Company,'','','No Sales for this date' AS DailySales

WHERE NOT EXISTS (SELECT DocumentNumber FROM ACRBI.dbo.SSG_SalesAna_ItemDtl WHERE     (NOT (CustomerNumber LIKE 'RETAIL%')) AND (YEAR1 = 2023) AND (LocationCode = 'CENTRAL-CA') AND (DocumentDate >= dateadd(day,datediff(day,2,GETDATE()),0)

        AND DocumentDate < dateadd(day,datediff(day,0,GETDATE()),0)) AND (SOPTYPE = 3) AND (SALES) > 0

GROUP BY ROLLUP(ItemClass),DocumentDate,DocumentNumber)

UNION

SELECT

 'PR' As Company,CAST(DocumentDate AS VARCHAR) AS DocumentDate,ItemClass = CASE GROUPING(ItemClass) WHEN 1 THEN 'Total Sales-CA' ELSE ItemClass END,

DailySales = CONVERT(nvarchar, FORMAT(SUM(SALES),'C','en' ))

FROM ACRBI.dbo.SSG_SalesAna_ItemDtl

WHERE     (NOT (CustomerNumber LIKE 'RETAIL%')) AND (YEAR1 = 2023) AND (LocationCode = 'CENTRAL-P2') AND (DocumentDate >= dateadd(day,datediff(day,1,GETDATE()),0)

        AND DocumentDate < dateadd(day,datediff(day,0,GETDATE()),0)) AND (SOPTYPE = 3)

      GROUP BY ROLLUP(ItemClass),DocumentDate

UNION 

SELECT 'PR' As Company,'','','No Sales for this date' AS DailySales

WHERE NOT EXISTS (SELECT DocumentNumber FROM ACRBI.dbo.SSG_SalesAna_ItemDtl WHERE     (NOT (CustomerNumber LIKE 'RETAIL%')) AND (YEAR1 = 2023) AND (LocationCode = 'CENTRAL-P2') AND (DocumentDate >= dateadd(day,datediff(day,1,GETDATE()),0)

        AND DocumentDate < dateadd(day,datediff(day,0,GETDATE()),0)) AND (SOPTYPE = 3) AND (SALES) > 0

GROUP BY ROLLUP(ItemClass),DocumentDate,DocumentNumber

)


Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Can you provide some raw sample data for SSG_SalesAna_ItemDtl?

You shouldn't have to access the table once for each location.  Once the SQL is combined, then we can address the 'No Sales' rows.

Should be able to do all this in a single query.
Avatar of Fred Webb

ASKER

What do you mean raw sample data

CSV/text data from the actual table we can use to set up a test case and post tested SQL.

Even better, the DDL and inserts like:
create table SSG_SalesAna_ItemDtl(.....)
insert into SSG_SalesAna_ItemDtl values
...
ASKER CERTIFIED SOLUTION
Avatar of Scott Pletcher
Scott Pletcher
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial

Scott,

This seemed to work If I want to add two more queries I would just repeat the CTEs

Yes.  That may not be terribly efficient (you'd have to check the query plans to be sure; better chance of Enterprise Ed being more efficient there), but it should work.  If your data size is not too large, it may work ok as is for your data.

Yes, check the plan.  Betting it accesses the table twice for each company.

You shouldn't have to do that.

The data is not too large so it should work.

See if this works better for you.  Should only access the table once and make the SQL a LOT cleaner as you add additional warehouses.

To add a new warehouse just add it to the warehouse CTE:
with warehouses as (
	select 'CA' company, 'CENTRAL-CA' locationcode
	union all
	select 'PR' company, 'CENTRAL-P2' locationcode
),
sales_data as (
	select
		locationcode,
		cast(DocumentDate as varchar) as DocumentDate,
		itemclass,
		grouping(ItemClass) ItemClass_group,
		convert(nvarchar, format(sum(sales),'C','en' )) as DailySales 
	from
		SSG_SalesAna_ItemDtl
	where
		(not (CustomerNumber like 'RETAIL%')) and
		year1 = 2023 and
		DocumentDate >= dateadd(day,datediff(day,1,GETDATE()),0) and
		DocumentDate < dateadd(day,datediff(day,0,GETDATE()),0) and
		SOPTYPE = 3
	group by
		locationcode,
		DocumentDate,
		rollup(ItemClass)
)
select
	company,
	documentdate,
	case ItemClass_group when 1 then 'Total Sales-' + company else ItemClass end ItemClass,
	coalesce(dailysales, 'No Sales for this date') as dailysales
	from warehouses w
		left join sales_data s on s.locationcode=w.locationcode
;

Open in new window

Yes, that would work if they were all from the same database, but they are not so I have to define the databases.

ACANI.dbo.SSG_SalesAna_ItemDtl

ACRBI.dbo.SSG_SalesAna_ItemDtl

>>but they are not so I have to define the databases.

That wasn't in your original requirements.

Anyway,
Should be able to do a multi-database search by adding a union all inside the sales_data CTE and keep everything else the same.

At least you don't have to hit each table twice in the same database like you would with the NOT EXISTS method.

Something like:
with warehouses as (
	select 'CA' company, 'CENTRAL-CA' locationcode
	union all
	select 'PR' company, 'CENTRAL-P2' locationcode
),
all_sales_data as (
	select
		locationcode,
		cast(DocumentDate as varchar) as DocumentDate,
		itemclass,
		sales
	from
		ACANI.dbo.SSG_SalesAna_ItemDtl
	where
		(not (CustomerNumber like 'RETAIL%')) and
		year1 = 2023 and
		DocumentDate >= dateadd(day,datediff(day,1,GETDATE()),0) and
		DocumentDate < dateadd(day,datediff(day,0,GETDATE()),0) and
		SOPTYPE = 3
	union all
	select
		locationcode,
		cast(DocumentDate as varchar) as DocumentDate,
		itemclass,
		sales
	from
		ACRBI.dbo.SSG_SalesAna_ItemDtl
	where
		(not (CustomerNumber like 'RETAIL%')) and
		year1 = 2023 and
		DocumentDate >= dateadd(day,datediff(day,1,GETDATE()),0) and
		DocumentDate < dateadd(day,datediff(day,0,GETDATE()),0) and
		SOPTYPE = 3

)
rollup_sales_data as (
	select
		locationcode,
		cast(DocumentDate as varchar) as DocumentDate,
		itemclass,
		grouping(ItemClass) ItemClass_group,
		convert(nvarchar, format(sum(sales),'C','en' )) as DailySales 
	from
		all_sales_data
	group by
		locationcode,
		DocumentDate,
		rollup(ItemClass)
)
select
	company,
	documentdate,
	case ItemClass_group when 1 then 'Total Sales-' + company else ItemClass end ItemClass,
	coalesce(dailysales, 'No Sales for this date') as dailysales
	from warehouses w
		left join rollup_sales_data s on s.locationcode=w.locationcode
;

Open in new window

I don't know the SQL Server optimizer very well so I'm not sure how it handles indexes and CTEs but to make the SQL even cleaner, try something like this.

Run an explain plan on it to make sure the optimizer uses the correct indexes.

with warehouses as (
	select 'CA' company, 'CENTRAL-CA' locationcode
	union all
	select 'PR' company, 'CENTRAL-P2' locationcode
),
all_sales_data as (
	select
		customernumber,
		year1,
		soptype,
		locationcode,
		DocumentDate
		itemclass,
		sales
	from
		ACANI.dbo.SSG_SalesAna_ItemDtl
	union all
	select
		customernumber,
		year1,
		soptype,
		locationcode,
		DocumentDate
		itemclass,
		sales
	from
		ACRBI.dbo.SSG_SalesAna_ItemDtl
)
rollup_sales_data as (
	select
		locationcode,
		cast(DocumentDate as varchar) as DocumentDate,
		itemclass,
		grouping(ItemClass) ItemClass_group,
		convert(nvarchar, format(sum(sales),'C','en' )) as DailySales 
	from
		all_sales_data
	where
		(not (CustomerNumber like 'RETAIL%')) and
		year1 = 2023 and
		DocumentDate >= dateadd(day,datediff(day,1,GETDATE()),0) and
		DocumentDate < dateadd(day,datediff(day,0,GETDATE()),0) and
		SOPTYPE = 3
	group by
		locationcode,
		DocumentDate,
		rollup(ItemClass)
)
select
	company,
	documentdate,
	case ItemClass_group when 1 then 'Total Sales-' + company else ItemClass end ItemClass,
	coalesce(dailysales, 'No Sales for this date') as dailysales
	from warehouses w
		left join rollup_sales_data s on s.locationcode=w.locationcode
;

Open in new window