December 19, 2017 at 10:00 am
Hi,
I've written a few SQL XML statements before where I can change the font colour and size using case, when, then colour = xxx
But how do I code it to change colour based on the output of a case statement? What I want to add to the below statement is to say if the store like 'ZZ%' then use substring and for those rows change the colour to xxx else just return the rows in the standard colour...
SELECT
td = CASE when TY.store like 'ZZ%' then (SUBSTRING(TY.store, 4, 99)) else TY.store end,
'',
Thank-you
Simon
December 19, 2017 at 10:17 am
You've been around long enough to know to provide sample data and expected results.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 19, 2017 at 10:48 am
drew.allen - Tuesday, December 19, 2017 10:17 AMYou've been around long enough to know to provide sample data and expected results.Drew
He he, if you cannot spell it, don't ask it!
😎
..and of course we all adjust the colour of our G-string using xml these days 😛
December 20, 2017 at 1:56 am
Sorry was just trying to get rid of clutter. It's reading from a coulpe of table variables built from a basic select statement.
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H2>Daily Sales by Store</H2>' +
N'<table border="1">' +
N'<tr>' +
N'<th>Store</th>' +
N'<th>Sales Value This Year</th>' +
N'<th>Sales Value Last Year</th>' +
N'<th>Percentage Difference</th>' +
N'</tr>' +
cast ( (
SELECT
td = CASE when TY.store like 'ZZ%' then (SUBSTRING(TY.store, 4, 99)) else TY.store end,
'',
td = TY.Quantity,
'',
td = LY.Quantity,
'',
td = CASE
WHEN TY.Quantity = 0 THEN -100
WHEN LY.Quantity = 0 THEN 100
ELSE convert(decimal(8,2),(((TY.Quantity-LY.Quantity)/ABS(LY.Quantity))*100)) end,
''
from @DailySales TY inner join @DailySalesLY LY
on TY.store = LY.store
where TY.Store != 'GRAND TOTAL'
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N' ' ;
The result will be a list of stores (rolled up by market) followed by a total value for that market. There are about 10 markets and I want to highlight the rolled up total for each market in a colour or just as bold font please...Thank-you
| Västra Frölunda | 44270.50 | 60752.28 | -27.13 |
| Växjö | 23687.52 | 26935.25 | -12.06 |
| Visby | 20388.33 | 21608.50 | -5.65 |
| Sweden TOTAL | 2774644.46 | 3525880.91 | -21.31 |
?CS?\m
December 20, 2017 at 3:17 am
Can you post the DDL for the table variables and some sample data as insert statements please?
😎
December 20, 2017 at 3:27 am
Hi
Yes ok, here's the whole statement...
DECLARE @Result numeric(20, 4), @lastyear datetime, @StartDateLastYear DateTime, @EndDateLastYear DateTime, @datetime DATETIME, @Hour INT, @StartDateToday DateTime, @EndDateToday DateTime
SET @datetime = GETDATE()
SET @Hour = DATEPART(hh, @datetime)
SET @lastyear = DATEADD(d,-364,@datetime)
SET @StartDateLastYear = CONVERT(DATETIME,(CONVERT(VARCHAR(12),@lastyear,13)))
SET @EndDateLastYear = DATEADD(hh,@Hour,@StartDateLastYear)
SET @StartDateToday = CONVERT(DATETIME,(CONVERT(VARCHAR(12),@datetime,13)))
SET @EndDateToday = DATEADD(hh,@Hour,@StartDateToday)
-- Start at midnight on the given day and end at the run time hour, THIS YEAR
DECLARE @DailySales TABLE (
Store varchar (64),
Quantity decimal (16,2))
insert @dailysales
select
CASE
WHEN (src.region) IS NOT NULL and src.store IS NULL THEN 'ZZ ' + (src.region) + ' TOTAL'
WHEN (src.region) IS NULL and src.store IS NULL THEN 'GRAND TOTAL'
ELSE (src.store)
END as store,
cast (src.qty as numeric(16,2)) as qty
from
(
select
distinct (sg.description) as Region,
st.description as Store,
SUM(effective_price_per_unit*units_sold_qty) as qty
from
storegroup sg(readuncommitted),
storegroupmem sgm (readuncommitted),
store st (readuncommitted),
sale s (INDEX = isalebytime readuncommitted),
saleline sl (INDEX = bysaleline readuncommitted)
where sg.storegroupclass_code = 'COUNTRY'
and sgm.storegroup_code = sg.storegroup_code
and st.store_code = sgm.store_code
and s.store_code = st.store_code
and s.sale_status_ind = 'A'
and s.transaction_date_time between @StartDateToday and @EndDateToday
and st.[description] NOT LIKE '%CLOSED%'
and sl.sale_code = s.sale_code
and sl.saleline_type_ind = 'N'
and sl.saleline_status_ind <> 'V'
group by sg.description, st.description with rollup
) as src
ORDER BY
CASE
WHEN region IS NULL
THEN 'TOTAL' ELSE region
END,
store
-- Start at midnight on the given day and end at the run time hour, LAST YEAR
DECLARE @DailySalesLY TABLE (
Store varchar (64),
Quantity decimal (16,2))
insert @dailysalesLY
select
CASE
WHEN (src.region) IS NOT NULL and src.store IS NULL THEN 'ZZ ' + (src.region) + ' TOTAL'
WHEN (src.region) IS NULL and src.store IS NULL THEN 'GRAND TOTAL'
ELSE (src.store)
END as store,
cast (src.qty as numeric(16,2)) as qty
from
(
select
distinct (sg.description) as Region,
st.description as Store,
SUM(effective_price_per_unit*units_sold_qty) as qty
from
storegroup sg(readuncommitted),
storegroupmem sgm (readuncommitted),
store st (readuncommitted),
sale s (INDEX = isalebytime readuncommitted),
saleline sl (INDEX = bysaleline readuncommitted)
where sg.storegroupclass_code = 'COUNTRY'
and sgm.storegroup_code = sg.storegroup_code
and st.store_code = sgm.store_code
and s.store_code = st.store_code
and s.sale_status_ind = 'A'
and s.transaction_date_time BETWEEN @StartDateLastYear AND @EndDateLastYear
and sl.sale_code = s.sale_code
and sl.saleline_type_ind = 'N'
and sl.saleline_status_ind <> 'V'
group by sg.description, st.description with rollup
) as src
ORDER BY
CASE
WHEN region IS NULL
THEN 'TOTAL' ELSE region
END,
store
DECLARE @tableHTML NVARCHAR(MAX) ;
SET @tableHTML =
N'<H2>Daily Sales by Store</H2>' +
N'<table border="1">' +
N'<tr>' +
N'<th>Store</th>' +
N'<th>Sales Value This Year</th>' +
N'<th>Sales Value Last Year</th>' +
N'<th>Percentage Difference</th>' +
N'</tr>' +
cast ( (
SELECT
td = CASE when TY.store like 'ZZ%' then (SUBSTRING(TY.store, 4, 99)) else TY.store end,
'',
td = TY.Quantity,
'',
td = LY.Quantity,
'',
td = CASE
WHEN TY.Quantity = 0 THEN -100
WHEN LY.Quantity = 0 THEN 100
ELSE convert(decimal(8,2),(((TY.Quantity-LY.Quantity)/ABS(LY.Quantity))*100)) end,
''
from @DailySales TY inner join @DailySalesLY LY
on TY.store = LY.store
where TY.Store != 'GRAND TOTAL'
FOR XML PATH('tr'), TYPE
) AS NVARCHAR(MAX) ) +
N' ' ;
December 20, 2017 at 8:01 am
INSERT/SELECT statements do us no good, because we don't have access to the tables in the SELECT part of the statement. You need to provide sample data as INSERT/VALUES statements. Again, you've been around long enough to know this.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 20, 2017 at 8:03 am
I have your answer and will provide it once you provide consumable data.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
December 20, 2017 at 8:22 am
Hi Drew, sorry I'm not quite sure what you mean. This is an insert into a table variable using a select statement. I've provided the whole code and the result set from it. Does the output from the query I sent above not help?
December 20, 2017 at 8:49 am
simon.letts - Wednesday, December 20, 2017 8:22 AMHi Drew, sorry I'm not quite sure what you mean. This is an insert into a table variable using a select statement. I've provided the whole code and the result set from it. Does the output from the query I sent above not help?
Without the underlying tables and data, this query will not run, most of us are too busy to guess the data and construct the schema and data.
😎
December 20, 2017 at 8:54 am
The problem is that we don't have access to your tables, so we cannot select from them, so we cannot insert into the table variable. If I try to run your code, I get
Msg 208, Level 16, State 1, Line 1
Invalid object name 'storegroup'.
Many of us have links in our signatures about how to provide sample data. I suggest you actually follow one and read it.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply