February 25, 2015 at 9:22 am
CREATE TABLE #tblTemplateBlocks
(
TemplateID int,
BlockID int,
OrderID int
)
GO
CREATE TABLE #tblFields
(
FieldID int IDENTITY(1,1) NOT NULL,
TemplateID int,
BlockID int,
StageBase bit,
WeekStart int
)
GO
INSERT INTO #tblTemplateBlocks (TemplateID, BlockID, OrderID)
SELECT 1, 1, 1 UNION ALL
SELECT 1, 2, 2 UNION ALL
SELECT 1, 3, 3
INSERT INTO #tblFields (TemplateID, BlockID, StageBase, WeekStart)
SELECT 1, 1, NULL, NULL UNION ALL
SELECT 1, 1, NULL, NULL UNION ALL
SELECT 1, 1, 1, 0 UNION ALL
SELECT 1, 2, NULL, NULL UNION ALL
SELECT 1, 2, 1, 3 UNION ALL
SELECT 1, 2, NULL, NULL UNION ALL
SELECT 1, 3, 1, NULL UNION ALL
SELECT 1, 3, NULL, NULL UNION ALL
SELECT 1, 3, NULL, NULL
SELECT * FROM #tblTemplateBlocks ORDER BY OrderID
SELECT * FROM #tblFields
DROP TABLE #tblTemplateBlocks
DROP TABLE #tblFields
I have a table called TemplateBlocks which contains which Blocks are on a Template. In this example - just one template - with three Blocks.
Table tblFields contains a list of Fields that are on each TemplateID/BlockID. In this example there are 3 fields on each TemplateID/BlockID pair.
Before I can use a template, I have to check that, in tblFields, for each Template/BlockID pairing - one of the fields must be set as the Stage Base (I cannot have 2 fields as StageBase or no fields as StageBase). In the example data above, the data would be okay as each Template/BlockID pairing has one row where StageBase is true.
Having checked that each Template/BlockID pairing has a StageBase, I need to check that each row where StageBase is true has a value for the WeekStart column and that, taking into account the order of the Blocks in tblTemplateBlocks, the values in WeekStart for each TemplateID/BlockID pairing are getting progressively bigger.
So, for example, the example data above would fail because the third TemplateID/BlockID pairing has no value for the WeekStart column in the row where StageBase is true.
If I added a value of 2 for WeekStart in the row for the third TemplateID/Block that has a StageBase of true - again the data would fail because, taking into account the order of the Blocks - the values for WeekStart would be 0,3,2 and these numbers need to increase.
0,3,4 would be fine.
0,3,10 would be fine.
0,3,3 would fail.
I can do this easily using a cursor or two - but I'm hoping someone can show me how to do this without cursors.
Thanks for any help.
February 25, 2015 at 9:43 am
Excellent job posting ddl and sample data. I can't even begin to wrap my head around what you are trying to do here. What are these subsets? Where do they come from? Are you looking for a query that receives a set and checks?
_______________________________________________________________
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/
February 25, 2015 at 10:05 am
Sorry, it's not easy to explain in words ... without a sketch or something - and my terminology is probably wrong.
Taking things one step at a time ...
in tblFieldss there are 3 records for each TemplateID/BlockID pair.
For TemplateID/BlockID 1/1 there are the rows with FieldID 1,2 and 3
For TemplateID/BlockID 1/2 there are the rows with FieldID 4,5 and 6
For TemplateID/BlockID 1/3 there are the rows with FieldID 6, 7 and 8
I am calling the rows that belong to the same TemplateID/BlockID a 'subset'. (In my real world data, there might be 50 rows for each TemplateID/BlockID pair but, in my example data, there are 3 rows for each pair.
The first thing I need to do is to check that for each Template - every Block has one row set with StageBase = true.
I am only showing data for one Template - with an ID of 1. Template 1 has 3 Blocks - BlockID 1, 2 and 3
So, taking the template with a TemplateID of 1 (which is all I am showing in my example data):
For TemplateID/BlockID 1/1 one of the rows with FieldID 1,2 or 3 must have StageBase = true
For TemplateID/BlockID 1/2 one of the rows with FieldID 4,5 or 6 must have StageBase = true
For TemplateID/BlockID 1/3 one of the rows with FieldID 7,8 or 9 must have StageBase = true
If any of those 'subsets' of data that belong to TemplateID 1 do not have a StageBase set - the data fails.
That's the first thing I need to determine.
Having checked that each group of fields in tblFields with the same TemplateID/BlockID values has one row with StageBase = true, I need to determine if the values of WeekStart for those rows are in ascending order - and that none are null.
Using my example data - if you run this query:
SELECT #tblTemplateBlocks.TemplateID, #tblTemplateBlocks.BlockID, #tblFields.WeekStart, #tblTemplateBlocks.OrderID FROM #tblTemplateBlocks
INNER JOIN #tblFields ON #tblTemplateBlocks.TemplateID = #tblFields.TemplateID AND #tblTemplateBlocks.BlockID = #tblFields.BlockID
WHERE StageBase = 1
ORDER BY OrderID
you get 3 rows with WeekStart values of 0,3 and null
A null value is not allowed so this would fail.
The values of WeekStart must be greater in each row than in the row before it.
February 25, 2015 at 10:17 am
OK I sort of think I get what you are trying to do. I added another set so you have one group that "fails".
CREATE TABLE #tblTemplateBlocks
(
TemplateID int,
BlockID int,
OrderID int
)
GO
CREATE TABLE #tblFields
(
FieldID int IDENTITY(1,1) NOT NULL,
TemplateID int,
BlockID int,
StageBase bit,
WeekStart int
)
GO
INSERT INTO #tblTemplateBlocks (TemplateID, BlockID, OrderID)
SELECT 1, 1, 1 UNION ALL
SELECT 1, 2, 2 UNION ALL
SELECT 1, 3, 3 union all
select 1, 4, 4
INSERT INTO #tblFields (TemplateID, BlockID, StageBase, WeekStart)
SELECT 1, 1, NULL, NULL UNION ALL
SELECT 1, 1, NULL, NULL UNION ALL
SELECT 1, 1, 1, 0 UNION ALL
SELECT 1, 2, NULL, NULL UNION ALL
SELECT 1, 2, 1, 3 UNION ALL
SELECT 1, 2, NULL, NULL UNION ALL
SELECT 1, 3, 1, NULL UNION ALL
SELECT 1, 3, NULL, NULL UNION ALL
SELECT 1, 3, NULL, NULL union all
select 1, 4, null, null
--SELECT * FROM #tblTemplateBlocks ORDER BY OrderID
--SELECT * FROM #tblFields
select TemplateID
, BlockID
, isnull(max(cast(StageBase as int)), 0) as IsValid
from #tblFields
group by TemplateID
, BlockID
DROP TABLE #tblTemplateBlocks
DROP TABLE #tblFields
_______________________________________________________________
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/
February 25, 2015 at 10:38 am
Thanks for your reply. Adding a bit more to your code ...
SELECT #tblFields.WeekStart
FROM #tblFields
INNER JOIN
(select #tblFields.TemplateID
, #tblFields.BlockID
, isnull(max(cast(StageBase as int)), 0) as IsValid
from #tblFields
group by #tblFields.TemplateID
, #tblFields.BlockID) A ON #tblFields.TemplateID = A.TemplateID AND #tblFields.BlockID = A.BlockID
INNER JOIN #tblTemplateBlocks ON #tblFields.TemplateID = #tblTemplateBlocks.TemplateID AND #tblFields.BlockID = #tblTemplateBlocks.BlockID
WHERE A.IsValid = 1 AND #tblFields.StageBase = 1
ORDER BY #tblTemplateBlocks.OrderID
returns data that looks like:
0
3
null
This is what I really need to be testing.
0, 3, null ... is no good ... the numbers in the column WeekStart must increase
0, 3, 10 would be fine.
0, 3, 2 would be no good.
February 25, 2015 at 10:47 am
sku370870 (2/25/2015)
Thanks for your reply. Adding a bit more to your code ...This is what I really need to be testing.
0, 3, null ... is no good ... the numbers in the column WeekStart must increase
0, 3, 10 would be fine.
0, 3, 2 would be no good.
Here is where you lose me. Are you saying that you want to make sure that the max value of week start in each group is greater than the max value of week start for the previous group? Do you use OrderID to indicate the order of groups?
Maybe you should extend your sample data to have three templateIDs. One for each of these scenarios. You will need to do that to test your code anyway.
_______________________________________________________________
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/
February 25, 2015 at 10:58 am
Here is where you lose me. Are you saying that you want to make sure that the max value of week start in each group is greater than the max value of week start for the previous group? Do you use OrderID to indicate the order of groups?
We have tested that each group has a row where StageBase is true. That same row will have a value for WeekStart. (I am not interested in the value of WeekStart in any other rows - just the rows where StageBase = true.)
So, based on the OrderID of the Blocks on the Template (in tblTemplateBlocks) I need to determine if the values in WeekStart are not null and are increasing.
If you run the query in my last post - you'll see that 0, 3 and null are returned - because the row with a FieldID of 7 (when you run the code) - which is the 'StageBase = true' row for the group of records with TemplateID = 1, BlockID = 3 (in tblFields) has a null value for WeekStart.
February 25, 2015 at 11:16 am
Sorry this has got so complicated. I understand the way you have suggested to determine whether StageBase is true. If we go past that and just consider this:
CREATE TABLE #tblFields
(
FieldID int IDENTITY(1,1) NOT NULL,
TemplateID int,
BlockID int,
StageBase bit,
WeekStart int
)
GO
INSERT INTO #tblFields (TemplateID, BlockID, StageBase, WeekStart)
SELECT 1, 1, NULL, NULL UNION ALL
SELECT 1, 1, NULL, NULL UNION ALL
SELECT 1, 1, 1, 0 UNION ALL
SELECT 1, 2, NULL, NULL UNION ALL
SELECT 1, 2, 1, 3 UNION ALL
SELECT 1, 2, NULL, NULL UNION ALL
SELECT 1, 3, 1, 2 UNION ALL
SELECT 1, 3, NULL, NULL UNION ALL
SELECT 1, 3, NULL, NULL
SELECT FieldID, WeekStart
FROM #tblFields
WHERE StageBase = 1
ORDER BY FieldID
DROP TABLE #tblFields
If you run that and look at the result set - how can I determine if WeekStart is greater in each row than in the row before?
February 25, 2015 at 12:26 pm
sku370870 (2/25/2015)
Sorry this has got so complicated. I understand the way you have suggested to determine whether StageBase is true. If we go past that and just consider this:
CREATE TABLE #tblFields
(
FieldID int IDENTITY(1,1) NOT NULL,
TemplateID int,
BlockID int,
StageBase bit,
WeekStart int
)
GO
INSERT INTO #tblFields (TemplateID, BlockID, StageBase, WeekStart)
SELECT 1, 1, NULL, NULL UNION ALL
SELECT 1, 1, NULL, NULL UNION ALL
SELECT 1, 1, 1, 0 UNION ALL
SELECT 1, 2, NULL, NULL UNION ALL
SELECT 1, 2, 1, 3 UNION ALL
SELECT 1, 2, NULL, NULL UNION ALL
SELECT 1, 3, 1, 2 UNION ALL
SELECT 1, 3, NULL, NULL UNION ALL
SELECT 1, 3, NULL, NULL
SELECT FieldID, WeekStart
FROM #tblFields
WHERE StageBase = 1
ORDER BY FieldID
DROP TABLE #tblFields
If you run that and look at the result set - how can I determine if WeekStart is greater in each row than in the row before?
Assuming you are running 2008 you can do this with a cte.
with sortedValues as
(
SELECT FieldID
, WeekStart
, TemplateID
, BlockID
, ROW_NUMBER() over(order by FieldID) as RowNum
FROM #tblFields
WHERE StageBase = 1
)
select min(isnull(sv2.WeekStart, sv.WeekStart) - sv.WeekStart)
, sv.TemplateID
, sv.BlockID
from sortedValues sv
left join sortedValues sv2 on sv.RowNum + 1 = sv2.RowNum
group by sv.TemplateID
, sv.BlockID
having MIN(isnull(sv2.WeekStart, sv.WeekStart) - sv.WeekStart) < 0
This will tell you which rows do not have a WeekStart greater than the previous row. If you are on 2012 you can use LAG instead which will do the same thing but is much simpler from a syntax point of view.
_______________________________________________________________
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/
February 25, 2015 at 2:48 pm
Thanks again for that. I just had to change ...
;with sortedValues as
(
SELECT FieldID
, WeekStart
,TemplateID ...
to
;with sortedValues as
(
SELECT FieldID
, CASE WHEN WeekStart IS NULL THEN -1 ELSE WeekStart END AS [WeekStart]
,TemplateID ...
and it copes with null values in WeekStart.
Thanks again for your help.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply