master sysmessages: Match numbers

I have a problem using the sql builder since i am trying to match a list of 5 numbers in TAble A with other 5 numbers in Table B

All i want to know is when there will be a match it will indicate in the report.

Example code of what i tried:

SELECT DBA.*,DBB.*
FROM (DBA INNER JOIN
DBB ON DBA.[ 1] = DBB.[ 1] OR DBA.[ 1] = DBB.[ 2] OR DBA.[ 1] =DBB.[ 3] OR
DBA.[ 1] = DBB.[ 4] OR DBA.[ 1] = DBB.[ 5] OR DBA.[ 2] = DBB.[ 1] OR
DBA.[ 2] = DBB.[ 1] OR DBA.[ 2] = DBB.[ 2] OR DBA.[ 2] = DBB.[ 3] OR
DBA.[ 2] = DBB.[ 4] OR DBA.[ 2] = DBB.[ 5] OR DBA.[ 3] = DBB.[ 1] OR
DBA.[ 3] = DBB.[ 2] OR DBA.[ 3] = DBB.[ 3] OR DBA.[ 3] = DBB.[ 4] OR
DBA.[ 3] = DBB.[ 5] OR DBA.[ 4] = DBB.[ 1] OR DBA.[ 4] = DBB.[ 2] OR
DBA.[ 4] = DBB.[ 3] OR DBA.[ 4] = DBB.[ 4] OR DBA.[ 4] = DBB.[ 5] OR
DBA.[ 5] = DBB.[ 1] OR DBA.[ 5] = DBB.[ 2] OR DBA.[ 5] = DBB.[ 3] OR
DBA.[ 5] = DBB.[ 4] OR DBA.[ 5] = DBB.[ 5])

I did like this and the result was not like i want since i had errors in the report and there where tables with the same ID number.

the error was :

Failed to enable constraints. One or more rows contain values violating non-null, unique, or foreign-key constraints.

Then i turned EnforceConstraints = False

When i turned it to false it worked but the results where not in the same line as i would like them to be they where seperated with the ID and Date

Example

Date ID Numbers

	11/16/2006 08:15:20 PM	6	13	26	29	42	56
	11/16/2006 08:18:20 PM	6	13	26	29	42	56

Any help please?

Thanks

have u tried not to use SELECT * but select only the values you need seperatly and then group the result?|||

Thanks for your reply.

I tried tried to remove date and ID number but still the same the only differnce it will not give me the error of constraints.

Let me give u an example what i have in the database

DBA

DBB

date

9/16/2006 8:10:20 PM

10/16/2006 8:10:20 PM

11/16/2006 8:15:20 PM

11/16/2006 8:18:20 PM

What I need is that ID6 and ID1 will display in the same line

The result of my query is this:

Id No

No1

No2

No3

No4

No5

Thanks

|||

based on your example, try

SELECT DISTINCT DBA.*
...

|||

WIth DISTINCT it worked from the SQL but when i try to access from VB2005 Reportviewer i cannot see the DBB since i only manage to see what i selected DBA.

Is there a way how i can add more than one DATASET in a table ?

I can see others DATASETS in the expression menu but if i try to use those the expression will be

=Sum(Fields!1.Value, "DBB")

Would it be possible that i add also DBB in the FIELDS Menu and use multiple datasets?

At the moment i can see only Fields(DBA)

Thanks in advance

|||

I believe you can't. You can try using multiple tables though.

In terms of the problem you are trying to solve, I am thoroughly confused. Any chance you could go back clarify what you are trying to achieve. Can you explain the colour coding in your previous posts?

|||

What I am trying to achieve is very simple I thought.
The color coding means that there is a match from list A to List B with 1 or all 5 numbers.

I also would like to know if there is a match with 1 number, 2 number etc.

The Red code means that there was a match

Example :

Date ID Numbers

11/16/2006 08:15:20 PM

11/16/2006 08:18:20 PM

Means that the first 13, and 26 = from date

11/16/2006 8:15:20 PM

And the second row 29,42,56 = from date

11/16/2006 8:18:20 PM

What I really need is that there will not be 2 rows and the date is not important so I can remove it, it is there just for reference.

Thanks

|||

To help me understand this, the closest thing I can compare this is a lottery. Let's think of DBA as user selected numbers and DBB as the draw dates and results.

You would like for every set of numbers in DBA to be compared with the draw results in DBB and any hits to be highlighted in red.

The results in your example above show me which set of numbers had a hit on which date and the highlight shows which number from that set was a match. If you condense these onto 1 row and remove the date then you will only know which of that set of numbers has ever had a hit. Is this what you need. Where else do you need to display data from DBB and why?

It feels like you are not showing the full example of what you are trying to do in your report.

|||

Yes this is like a lottery project for school.

I do not have nothing to show more

You would like for every set of numbers in DBA to be compared with the draw results in DBB and any hits to be highlighted in re

Yes correct thats what I need.

In this Report i would like to display ID number . no1 , no2 , no 3 , no 4, no 5 which had a hit and if possible, if all 5 numbers where guessed i will write a winner.

Thanks once again.

|||

Any ideas please how can I do

Thanks

|||Bear with me. I'm on a project. I'll try it in a dummy database and create the RDL and post it when I'm done.|||Thanks for your reply I will wait for your solution|||

I'm just playing around at the moment. Here's some output from a matrix report I knocked up. I know it's not exactly what you asked for but tell me what you think anyway. I haven't got the "Winner" text showing yet but I'll need to revert to using a table for that. Basically my thinking is that for every draw you want to see how your chosen numbers did. The assumption here is that the same numbers were played for each draw.

9/16/2006 08:10:20 PM

10/16/2006 08:10:20 PM

11/16/2006 08:15:20 PM

11/16/2006 08:18:20 PM

|||

Hi ,

Thanks once again for your reply,

Yes that is near what I need but the report must not repeat the tickets because imagine what will happen with 1000 tickets.

For example in your report with the 13

11/16/2006 08:15:20 PM

11/16/2006 08:18:20 PM

The 13 did not remain RED from the prevous date and thats want i do not know how to do.

To get my winner i need to have 5 Macthing numbers in RED either from the same date or number by number from each lottery of the end of the week, who is playing will not have an expiry date, the ticket will expire only if there will be a winner.

Thanks

|||

Ok so this is more like bingo where the numbers accumulate.

Anyway, below is the solution.

My tables have slightly renamed columns as follows

DBA

A_ID

A_N1

A_N2

A_N3

A_N4

A_N5

DBB

B_ID

B_date

B_N1

B_N2

B_N3

B_N4

B_N5

9/16/2006 8:10:20 PM

10/16/2006 8:10:20 PM

11/16/2006 8:15:20 PM

11/16/2006 8:18:20 PM

I also added an ID column to DBB and created a primary key on both the ID columns in the tables

Then I created a couple of views for your tables to unpivot the numbered columns as follows:

CREATE VIEW [dbo].[UDBA] AS
SELECT A_ID
, A_INDEX = CAST(RIGHT(A_INDEX, 1) AS INT)
, A_VALUE
FROM DBA
UNPIVOT
(
A_VALUE
FOR A_INDEX IN (A_N1, A_N2, A_N3, A_N4, A_N5)
) U

CREATE VIEW [dbo].[UDBB] AS
SELECT B_ID
, B_date
, B_INDEX = CAST(RIGHT(B_INDEX,1) AS INT)
, B_VALUE
FROM DBB
UNPIVOT
(
B_VALUE
FOR B_INDEX IN (B_N1, B_N2, B_N3, B_N4, B_N5)
) U

Then with the views in place I use a common table expression in the report query:

WITH lotteryCTE (id, col, val, hit)
AS
(
-- Bring back all the results
SELECT id = A_ID
, col = CAST(A_INDEX as varchar)
, val = CAST(A_VALUE AS VARCHAR)
, hit = MAX(CASE WHEN results.B_ID IS NULL THEN 0 ELSE 1 END)
FROM UDBA picks
LEFT JOIN UDBB results
ON picks.A_VALUE IN (results.B_VALUE)
GROUP BY A_ID
, A_INDEX
, A_VALUE
)
-- Bring back all the values from the CTE. If it was a hit then highlight in red
SELECT id
, col
, val
, is_red = hit
FROM lotteryCTE
UNION ALL
-- Append the rows for the winner column only for picks where the numbers of hits is equal to the number of values chosen
-- Winner cells always red
SELECT id
, col = 'Win'
, val = 'Winner'
, is_red = 1
FROM lotteryCTE
GROUP BY id
HAVING SUM(hit) = COUNT(DISTINCT col)
ORDER BY id
, col

The above query would not need to change if the number of values chosen or drawn changes.

For the report I then use 2 separate datasets one using the above the query, the other simply retrieving the contents of the DBB table. On the report I use a table to display DBB and a matrix to display the results as follows:

Draws

Date

9/16/2006 08:10:20 PM

10/16/2006 08:10:20 PM

11/16/2006 08:15:20 PM

11/16/2006 08:18:20 PM

Results

Win

Winner

The following is the RDL used for the report:

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2005/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<DataSources>
<DataSource Name="ds_lottery">
<DataSourceReference>ds_lottery</DataSourceReference>
<rd:DataSourceID>a2cbdf77-dd94-4883-b0e2-1538ed72012c</rd:DataSourceID>
</DataSource>
</DataSources>
<BottomMargin>2.5cm</BottomMargin>
<RightMargin>2.5cm</RightMargin>
<PageWidth>21cm</PageWidth>
<rd:DrawGrid>true</rd:DrawGrid>
<InteractiveWidth>21cm</InteractiveWidth>
<rd:GridSpacing>0.25cm</rd:GridSpacing>
<rd:SnapToGrid>true</rd:SnapToGrid>
<Body>
<ColumnSpacing>1cm</ColumnSpacing>
<ReportItems>
<Textbox Name="textbox6">
<Left>0.25cm</Left>
<Top>3cm</Top>
<rd:DefaultName>textbox6</rd:DefaultName>
<ZIndex>3</ZIndex>
<Width>2.53968cm</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.63492cm</Height>
<Value>Results</Value>
</Textbox>
<Textbox Name="textbox2">
<Left>0.25cm</Left>
<Top>0.25cm</Top>
<rd:DefaultName>textbox2</rd:DefaultName>
<ZIndex>2</ZIndex>
<Width>2.53968cm</Width>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Height>0.63492cm</Height>
<Value>Draws</Value>
</Textbox>
<Table Name="table1">
<Left>0.25cm</Left>
<DataSetName>dst_picks</DataSetName>
<Top>1.25cm</Top>
<ZIndex>1</ZIndex>
<Width>9.30291cm</Width>
<Details>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="B_date">
<rd:DefaultName>B_date</rd:DefaultName>
<ZIndex>5</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
<Left>1pt</Left>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Black</Bottom>
<Left>Black</Left>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!B_date.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="B_N1_1">
<rd:DefaultName>B_N1_1</rd:DefaultName>
<ZIndex>4</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!B_N1.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="B_N2">
<rd:DefaultName>B_N2</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!B_N2.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="B_N3">
<rd:DefaultName>B_N3</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!B_N3.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="B_N4">
<rd:DefaultName>B_N4</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Black</Bottom>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!B_N4.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="B_N5">
<rd:DefaultName>B_N5</rd:DefaultName>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Bottom>1pt</Bottom>
<Right>1pt</Right>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Bottom>Black</Bottom>
<Right>Black</Right>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!B_N5.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Details>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Header>
<TableRows>
<TableRow>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<rd:DefaultName>textbox3</rd:DefaultName>
<ZIndex>11</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Top>1pt</Top>
<Left>1pt</Left>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Top>Black</Top>
<Left>Black</Left>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>Date</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox4">
<rd:DefaultName>textbox4</rd:DefaultName>
<ZIndex>10</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Top>1pt</Top>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Top>Black</Top>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>1</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox5">
<rd:DefaultName>textbox5</rd:DefaultName>
<ZIndex>9</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Top>1pt</Top>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Top>Black</Top>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>2</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<rd:DefaultName>textbox11</rd:DefaultName>
<ZIndex>8</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Top>1pt</Top>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Top>Black</Top>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>3</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<rd:DefaultName>textbox14</rd:DefaultName>
<ZIndex>7</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Top>1pt</Top>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Top>Black</Top>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>4</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox17">
<rd:DefaultName>textbox17</rd:DefaultName>
<ZIndex>6</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Top>1pt</Top>
<Right>1pt</Right>
</BorderWidth>
<PaddingBottom>2pt</PaddingBottom>
<BorderColor>
<Top>Black</Top>
<Right>Black</Right>
</BorderColor>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>5</Value>
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
<Height>0.63492cm</Height>
</TableRow>
</TableRows>
</Header>
<TableColumns>
<TableColumn>
<Width>4.25cm</Width>
</TableColumn>
<TableColumn>
<Width>1cm</Width>
</TableColumn>
<TableColumn>
<Width>1cm</Width>
</TableColumn>
<TableColumn>
<Width>1cm</Width>
</TableColumn>
<TableColumn>
<Width>1.02646cm</Width>
</TableColumn>
<TableColumn>
<Width>1.02646cm</Width>
</TableColumn>
</TableColumns>
<Height>1.26984cm</Height>
</Table>
<Matrix Name="matrix1">
<MatrixColumns>
<MatrixColumn>
<Width>1.5cm</Width>
</MatrixColumn>
</MatrixColumns>
<Left>0.25cm</Left>
<RowGroupings>
<RowGrouping>
<Width>0.75cm</Width>
<DynamicRows>
<ReportItems>
<Textbox Name="A_ID">
<rd:DefaultName>A_ID</rd:DefaultName>
<ZIndex>1</ZIndex>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Right</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!id.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_A_ID">
<GroupExpressions>
<GroupExpression>=Fields!id.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicRows>
</RowGrouping>
</RowGroupings>
<ColumnGroupings>
<ColumnGrouping>
<DynamicColumns>
<ReportItems>
<Textbox Name="A_INDEX">
<rd:DefaultName>A_INDEX</rd:DefaultName>
<ZIndex>2</ZIndex>
<Style>
<BorderStyle>
<Top>Solid</Top>
<Left>Solid</Left>
<Right>Solid</Right>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Fields!col.Value</Value>
</Textbox>
</ReportItems>
<Grouping Name="matrix1_A_INDEX">
<GroupExpressions>
<GroupExpression>=Fields!col.Value</GroupExpression>
</GroupExpressions>
</Grouping>
</DynamicColumns>
<Height>0.63492cm</Height>
</ColumnGrouping>
</ColumnGroupings>
<DataSetName>dst_results</DataSetName>
<Top>4cm</Top>
<Width>2.25cm</Width>
<Corner>
<ReportItems>
<Textbox Name="textbox1">
<rd:DefaultName>textbox1</rd:DefaultName>
<ZIndex>3</ZIndex>
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</Corner>
<MatrixRows>
<MatrixRow>
<Height>0.63492cm</Height>
<MatrixCells>
<MatrixCell>
<ReportItems>
<Textbox Name="A_VALUE">
<rd:DefaultName>A_VALUE</rd:DefaultName>
<Style>
<BorderStyle>
<Bottom>Solid</Bottom>
<Default>Solid</Default>
</BorderStyle>
<TextAlign>Center</TextAlign>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<Color>=iif(Fields!is_red.Value = 1, "Red", "Black")</Color>
<PaddingRight>2pt</PaddingRight>
<PaddingTop>2pt</PaddingTop>
</Style>
<CanGrow>true</CanGrow>
<Value>=Max(Fields!val.Value)</Value>
</Textbox>
</ReportItems>
</MatrixCell>
</MatrixCells>
</MatrixRow>
</MatrixRows>
</Matrix>
</ReportItems>
<Height>5.26984cm</Height>
</Body>
<rd:ReportID>b64d7ed0-c87b-4f36-8cb8-3727005d5e2f</rd:ReportID>
<LeftMargin>2.5cm</LeftMargin>
<DataSets>
<DataSet Name="dst_results">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>WITH lotteryCTE (id, col, val, hit)
AS
(
-- Bring back all the results
SELECT id = A_ID
, col = CAST(A_INDEX as varchar)
, val = CAST(A_VALUE AS VARCHAR)
, hit = MAX(CASE WHEN results.B_ID IS NULL THEN 0 ELSE 1 END)
FROM UDBA picks
LEFT JOIN UDBB results
ON picks.A_VALUE IN (results.B_VALUE)
GROUP BY A_ID
, A_INDEX
, A_VALUE
)
-- Bring back all the values from the CTE. If it was a match then highlight in red
SELECT id
, col
, val
, is_red = hit
FROM lotteryCTE
UNION ALL
-- Append the rows for the winner column only for picks where the numbers of hits is equal to the number of values chosen
-- Winner cells always red
SELECT id
, col = 'Win'
, val = 'Winner'
, is_red = 1
FROM lotteryCTE
GROUP BY id
HAVING SUM(hit) = COUNT(DISTINCT col)
ORDER BY id
, col</CommandText>
<DataSourceName>ds_lottery</DataSourceName>
</Query>
<Fields>
<Field Name="id">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>id</DataField>
</Field>
<Field Name="col">
<rd:TypeName>System.String</rd:TypeName>
<DataField>col</DataField>
</Field>
<Field Name="val">
<rd:TypeName>System.String</rd:TypeName>
<DataField>val</DataField>
</Field>
<Field Name="is_red">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>is_red</DataField>
</Field>
</Fields>
</DataSet>
<DataSet Name="dst_picks">
<Query>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
<CommandText>select * from DBB</CommandText>
<DataSourceName>ds_lottery</DataSourceName>
</Query>
<Fields>
<Field Name="B_ID">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>B_ID</DataField>
</Field>
<Field Name="B_date">
<rd:TypeName>System.DateTime</rd:TypeName>
<DataField>B_date</DataField>
</Field>
<Field Name="B_N1">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>B_N1</DataField>
</Field>
<Field Name="B_N2">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>B_N2</DataField>
</Field>
<Field Name="B_N3">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>B_N3</DataField>
</Field>
<Field Name="B_N4">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>B_N4</DataField>
</Field>
<Field Name="B_N5">
<rd:TypeName>System.Int32</rd:TypeName>
<DataField>B_N5</DataField>
</Field>
</Fields>
</DataSet>
</DataSets>
<Width>28.02778cm</Width>
<InteractiveHeight>29.7cm</InteractiveHeight>
<Language>en-US</Language>
<TopMargin>2.5cm</TopMargin>
<PageHeight>29.7cm</PageHeight>
</Report>

Hope this gives you what you need.

Adam.

Saturday, February 25, 2012

Match numbers

No comments:

Post a Comment

master sysmessages

Blog Archive

About Me