Firehose mode: following

Showing posts with label following. Show all posts

Friday, March 30, 2012

Grouping Row Border

Hi,
I'm trying to achieve the following in a table. Have border after/before the
grouping changes to the next value.
Nov 1 v1 v2 v3 v4
v1 v2 v3 v4
_____________________
Nov 3 v1 v2 v3 v4
_____________________
Is there an easy way to do this?Yes. Add either a top or bottom border to group footer row in the table. The
report at end of this posting demonstrates how to accomplish this task.
--
Bruce Johnson [MSFT]
Microsoft SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Reg" <reg@.dsl.za.org> wrote in message
news:O%23n$LYymEHA.3684@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I'm trying to achieve the following in a table. Have border after/before
the
> grouping changes to the next value.
> Nov 1 v1 v2 v3 v4
> v1 v2 v3 v4
> _____________________
> Nov 3 v1 v2 v3 v4
> _____________________
> Is there an easy way to do this?
>
BorderBetweenGroups.rdl
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Table Name="table1">
<Height>1.125in</Height>
<Style />
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox1">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>14</ZIndex>
<rd:DefaultName>textbox1</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox2">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>13</ZIndex>
<rd:DefaultName>textbox2</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox3">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>12</ZIndex>
<rd:DefaultName>textbox3</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Details>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="CompanyName">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>2</ZIndex>
<rd:DefaultName>CompanyName</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!CompanyName.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="City">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>1</ZIndex>
<rd:DefaultName>City</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!City.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox6">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<rd:DefaultName>textbox6</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Details>
<DataSetName>Northwind</DataSetName>
<TableGroups>
<TableGroup>
<Header>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="Country">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>11</ZIndex>
<rd:DefaultName>Country</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value>=Fields!Country.Value</Value>
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox11">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>10</ZIndex>
<rd:DefaultName>textbox11</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox12">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>9</ZIndex>
<rd:DefaultName>textbox12</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Header>
<Grouping Name="table1_Group1">
<GroupExpressions>
<GroupExpression>=Fields!Country.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Footer>
<TableRows>
<TableRow>
<Height>0.125in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox13">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>5</ZIndex>
<rd:DefaultName>textbox13</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox14">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>4</ZIndex>
<rd:DefaultName>textbox14</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox15">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<BorderWidth>
<Default>2pt</Default>
</BorderWidth>
<BorderStyle>
<Bottom>Solid</Bottom>
</BorderStyle>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>3</ZIndex>
<rd:DefaultName>textbox15</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
</TableGroup>
</TableGroups>
<Footer>
<TableRows>
<TableRow>
<Height>0.25in</Height>
<TableCells>
<TableCell>
<ReportItems>
<Textbox Name="textbox7">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>8</ZIndex>
<rd:DefaultName>textbox7</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox8">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>7</ZIndex>
<rd:DefaultName>textbox8</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
<TableCell>
<ReportItems>
<Textbox Name="textbox9">
<Style>
<PaddingLeft>2pt</PaddingLeft>
<PaddingBottom>2pt</PaddingBottom>
<PaddingTop>2pt</PaddingTop>
<PaddingRight>2pt</PaddingRight>
</Style>
<ZIndex>6</ZIndex>
<rd:DefaultName>textbox9</rd:DefaultName>
<CanGrow>true</CanGrow>
<Value />
</Textbox>
</ReportItems>
</TableCell>
</TableCells>
</TableRow>
</TableRows>
</Footer>
<TableColumns>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
<TableColumn>
<Width>2.16667in</Width>
</TableColumn>
</TableColumns>
</Table>
</ReportItems>
<Style />
<Height>2in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>76d8d0cd-7eef-42fe-aa1e-81920ad6bfed</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>data source=localhost;initial
catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.5in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="CustomerID">
<DataField>CustomerID</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="CompanyName">
<DataField>CompanyName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactName">
<DataField>ContactName</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="ContactTitle">
<DataField>ContactTitle</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Address">
<DataField>Address</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="City">
<DataField>City</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Region">
<DataField>Region</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="PostalCode">
<DataField>PostalCode</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Country">
<DataField>Country</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Phone">
<DataField>Phone</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
<Field Name="Fax">
<DataField>Fax</DataField>
<rd:TypeName>System.String</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>select * from customers</CommandText>
<rd:UseGenericDesigner>true</rd:UseGenericDesigner>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>2fd0260c-7640-49fb-9128-9a9647b7cdcf</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

Grouping records

Hi,

I have the following tables :

Town
towncode
townname

Area
areano
areaname
towncode

Locality

locno
areano

RequestType
reqid
reqdtls

Eg:
1 - Addition
2 - Removal
3 - Relocate

WebSummit

SummitId

RequestorName

DateOfRequest

reqid

Areano

I want to find out the Town/Area/Locality wise Addition/Removal/Relocation request that have come
during the last 1 month.

The query I have written so far is as follows :

SELECT WebRequest.SummitId, RequestType.reqdtls, Area.areaname, Locality.locno, Town.townname
FROM RequestType INNER JOIN
WebRequest ON RequestType.reqid = WebRequest.reqid INNER JOIN
Area ON WebRequest.areano = Area.areano INNER JOIN
TownList ON Area.towncode = TownList.towncode INNER JOIN
Locality ON Area.areano = Locality.areano

However the results are entirely incorrect with a lot of duplicates.

Kindly suggest me the right query

Regards,

Vidya.

Vidya:

You have given a good description of your problem that includes (1) table schema (2) current query and (3) the specific problem -- duplicate rows. What is missing here is (1) sample data and (2) desired output. Without this we must to some extent guess at the problem. You might be able to fix the problem by simply adding a DISTINCT clause to your select statement. This can be done by changing the word SELECT into SELECT DISTINCT.

|||

IF I understand you correctly, you may want something more like this:

Code Snippet

SELECT DISTINCT
w.SummitId,
r.ReqDtLs,
a.AreaName,
l.LocNo,
t.TownName
FROM TownList t
JOIN Area a
ON t.TownCode = a.TownCode
JOIN Locality l
ON a.AreaNo = l.AreaNo
JOIN WebRequest w
ON w.AreaNo = a.AreaNo
JOIN RequestType ar
ON r.ReqID = w.ReqID
WHERE ( w.DateOfRequest >= dateadd( month, datediff( month , 0, getdate() ) -1 , 0 )
AND w.DateOfRequest < dateadd( month, datediff( month, 0, getdate() ), 0 )

Without sample data, we can't test our suggestions.

|||

Hi,

Yes I understand that without sample data it is difficult to test the query. I am sorry.

I will try and take care of this point the next time i need some help.

Thanks for the help anyways. I will test it at my end and revert back.

Regards,

Vidya.

|||

Hi,

The results are the same as I got

Here's some sample data

Town

TownCode TownName

1 Conteck

Area

AreaNo AreaName TownCode

1 Area1 1

2 Area2 1

3 Area 3 1

4 Area4 1

5 Area5 1

6 Area6 1

7 Area7 1

Locality

LocNo AreaNo

1 1

2 1

3 1

1 2

2 2

1 3

2 3

3 3

4 3

1 4

2 4

1 5

2 5

3 5

RequestType

reqid reqdtls

1 Addition

2 Removal

3 Relocate

WebSummit

SummitId RequestorName DateOfRequest reqid AreaNo

1 John 12/6/2007 1 1

2 Jack 13/6/2007 1 1

3 Bill 12/6/2007 2 2

4 Ben 12/6/2007 2 2

5 Dale 14/6/2007 2 3

6 Evjen 15/6/2007 3 1

7 Fuller 16/6/2007 1 4

8 Jimmy 16/6/2007 3 4

9 Kart 16/6/2007 1 5

10 Fuller 16/6/2007 1 5

Regards,

Vidya.

|||

Hello,

Any clues why the query is returning duplicate records. The data gets duplicated for every locality in that area.

Regards,

Vidya

|||

Thanks for providing the table information. It is now possible to realize that it is impossible to get the information you desire. There are significant 'design problems' that will prevent you from accurately getting what you want.

You are getting duplicates because each Area has Two Localities, there is NO way to associate a Town to a specific Locality, AND there is no way to associate a specific town to WebSummit Request.

Therefore, the result shows all Localities and all Towns for an Area.

(I think you want the specific town related to a WebSummit request.)

Here are my suggestions about 'design problems' that should be corrected.

The Towns table 'should' have LocNo. Locality is an attribute of the Town. From the LocNo, we can get the Area.

The Areas table 'should NOT' have the TownCode. TownName is NOT an attribute of the Area.

The WebSummit table 'should' have the TownCode -NOT the AreaNo. Without the TownCode, you cannot break it down below the level of the Area. You have NO way to associate a Locality or Town to a WebSummit row.

Think of it this way: A Ttown belongs to a Locality, a Locality belongs to an Area. A WebSummit Request is related to a specific Town, NOT to all towns in a Locality, or NOT to all towns in an Area.

(At least that is my interpretation of your outline -and it's late, so I could be totally wrong...)

|||

Hi Arnie,

You have written :

A Ttown belongs to a Locality, a Locality belongs to an Area.

whereas

A Town has areas, Areas have locality so...

A locality belongs to an area, an area belongs to a town.

Regards,

Vidya.

|||

Thanks for clarifying that. This is why this process works so much better when we are provided table DDL, sample data, desired results, AND an explanition of the data model.

Well, I was making assumptions based upon my interpretation of the data you provided. As I said, it's late, and I made some incorrect assumptions.

In your sample data, you have a Location (LocNo 1) belonging to multiple Areas. Is that correct?

INSERT INTO @.Locality VALUES ( 1, 1 )
INSERT INTO @.Locality VALUES ( 2, 1 )
INSERT INTO @.Locality VALUES ( 3, 1 )
INSERT INTO @.Locality VALUES ( 1, 2 )
INSERT INTO @.Locality VALUES ( 2, 2 )

And the WebSummit rows are related to Areas, not Locality. So any attempt to bring Locality into the resultset will entail having all Localities for an Area.

It still seems that the WebSummit data does not capture the finest granular information and most likely should have LocNo instead of AreaNo. Otherwise, by removing Locality from the resultset, you can get one row per row in the WebSummit table.

My apologies for the misinterpretation...

|||

Hi Arnie,

Its my fault again. I shortened the structure of the websummit table to explain my problem.

The websummit contains the localityno as well as the areano.

I also forgot to mention that in table locality, both the LocNo and AreaNo together form a primary key.

You can easily change the data to avoid confusion :

Locality

LocNo AreaNo

1 1

2 1

3 1

33 2

34 2

35 2

1 3

2 3

6 3

66 4

67 4

22 5

23 5

24 5

Now even though locality 1 may be in area 1 or area 3 as shown in teh data above, the uniqueness is achieved using both the locno and areano together.

SummitId RequestorName DateOfRequest reqid AreaNo LocNo

1 John 12/6/2007 1 1 1

2 Jack 13/6/2007 1 1 2

3 Bill 12/6/2007 2 2 34

4 Ben 12/6/2007 2 2 35

5 Dale 14/6/2007 2 3 6

6 Evjen 15/6/2007 3 1 3

7 Fuller 16/6/2007 1 4 66

8 Jimmy 16/6/2007 3 4 66

9 Kart 16/6/2007 1 5 23

10 Fuller 16/6/2007 1 5 24

So in short, a locality is just a no. given to divide areas, however its important. A locality belongs to only one area since both locno and area no together form a unique row.

If i have caused much confusion, I am ready to start over again with a fresh query, explaining everything in one go.

Regards,

Vidya.

|||

Vidya,

Thanks, that was the 'missing' piece. Now it becomes possible to link all the data. (Amazing what we can do when we have all the 'facts'.)

This 'should' work for you as you have explained the problem. Note that I changed the WebSummit dates so the the previous month filter will work properly. I added a few rows of data that should be out of range in order to test exculsion.

Code Snippet

SET NOCOUNT ON

DECLARE @.Towns table
( TownCode int,
TownName varchar(20)
)

INSERT INTO @.Towns VALUES ( 1, 'Conteck' )
INSERT INTO @.Towns VALUES ( 3, 'BigConteck' )

DECLARE @.Areas table
( AreaNo int,
AreaName varchar(20),
TownCode int
)

INSERT INTO @.Areas VALUES ( 1, 'Area1', 1 )
INSERT INTO @.Areas VALUES ( 2, 'Area2', 1 )
INSERT INTO @.Areas VALUES ( 3, 'Area3', 1 )
INSERT INTO @.Areas VALUES ( 4, 'Area4', 1 )
INSERT INTO @.Areas VALUES ( 5, 'Area5', 1 )
INSERT INTO @.Areas VALUES ( 6, 'Area6', 1 )
INSERT INTO @.Areas VALUES ( 7, 'Area7', 1 )
INSERT INTO @.Areas VALUES ( 8, 'Area1', 3 )

DECLARE @.Locality table
( LocNo int,
AreaNo int
)

INSERT INTO @.Locality VALUES ( 1, 1 )
INSERT INTO @.Locality VALUES ( 2, 1 )
INSERT INTO @.Locality VALUES ( 3, 1 )
INSERT INTO @.Locality VALUES ( 33, 2 )
INSERT INTO @.Locality VALUES ( 34, 2 )
INSERT INTO @.Locality VALUES ( 35, 2 )
INSERT INTO @.Locality VALUES ( 1, 3 )
INSERT INTO @.Locality VALUES ( 2, 3 )
INSERT INTO @.Locality VALUES ( 6, 3 )
INSERT INTO @.Locality VALUES ( 10, 3 )
INSERT INTO @.Locality VALUES ( 11, 3 )
INSERT INTO @.Locality VALUES ( 66, 4 )
INSERT INTO @.Locality VALUES ( 67, 4 )
INSERT INTO @.Locality VALUES ( 22, 5 )
INSERT INTO @.Locality VALUES ( 23, 5 )
INSERT INTO @.Locality VALUES ( 24, 5 )

DECLARE @.RequestType table
( ReqID int,
ReqdTLS varchar(20)
)

INSERT INTO @.RequestType VALUES ( 1, 'Addition' )
INSERT INTO @.RequestType VALUES ( 2, 'Removal' )
INSERT INTO @.RequestType VALUES ( 3, 'Relocate' )

DECLARE @.WebSummit table
( SummitId int,
RequestorName varchar(20),
DateOfRequest datetime,
ReqID int,
AreaNo int,
LocNo int
)

INSERT INTO @.WebSummit VALUES ( 1, 'John', '2007/05/12', 1, 1, 1 )
INSERT INTO @.WebSummit VALUES ( 2, 'Jack', '2007/05/13', 1, 1, 2 )
INSERT INTO @.WebSummit VALUES ( 3, 'Bill', '2007/05/12', 2, 2, 34 )
INSERT INTO @.WebSummit VALUES ( 4, 'Ben', '2007/05/12', 2, 2, 35 )
INSERT INTO @.WebSummit VALUES ( 5, 'Dale', '2007/05/14', 2, 3, 6 )
INSERT INTO @.WebSummit VALUES ( 6, 'Evjen', '2007/05/15', 3, 1, 3 )
INSERT INTO @.WebSummit VALUES ( 7, 'Fuller', '2007/05/16', 1, 4, 66 )
INSERT INTO @.WebSummit VALUES ( 8, 'Jimmy', '2007/05/16', 3, 4, 66 )
INSERT INTO @.WebSummit VALUES ( 9, 'Kart', '2007/05/16', 1, 5, 23 )
INSERT INTO @.WebSummit VALUES ( 10, 'Fuller', '2007/05/16', 1, 5, 24 )
INSERT INTO @.WebSummit VALUES ( 11, 'Kart', '2007/06/16', 1, 5, 23 )
INSERT INTO @.WebSummit VALUES ( 12, 'Fuller', '2007/06/16', 1, 5, 24 )

SELECT DISTINCT
w.SummitId,
r.ReqDtLs,
a.AreaName,
a.AreaNo,
l.LocNo,
t.TownName
FROM @.WebSummit w
JOIN @.Locality l
ON ( w.AreaNo = l.AreaNo
AND w.LocNo = l.LocNo
)
JOIN @.Areas a
ON w.AreaNo = a.AreaNo
JOIN @.Towns t
ON t.TownCode = a.TownCode
JOIN @.RequestType r
ON w.ReqID = r.ReqID
WHERE ( w.DateOfRequest >= dateadd( month, datediff( month , 0, getdate() ) -1 , 0 )
AND w.DateOfRequest < dateadd( month, datediff( month, 0, getdate() ), 0 )
)

|||

Bingo!!

Thanks a ton Arnie. Indeed, that's the query I was trying out.

Do me another favour. Suggest me a book to study T-sql or Sql server. I would now like to dedicate a lot of time to get strong in Sql.

See arnie, what you just did!! You inspired me!!

Regards,

Vidya.

|||

Itzak Ben-Gan has two books out on Microsoft Press -they are excellent!

A VERY worthwhile investment in your skills.

Good luck!

Grouping question

I have the following query:
SELECT PR_NO,
Total = CASE Items.Use_Item_Calc_Qty
WHEN 0 THEN CONVERT(money, SUM
(items.unit_price * items.qty))
ELSE CONVERT(money, SUM(items.unit_price *
items.qty * ITEM_CALC_QTY))
END
FROM Items
where pr_no = 5816
Group By PR_NO, Use_Item_Calc_Qty
The query is returning two records because a record in
the items table has a value of 0 in Use_item_calc_qty and
another record has a value of one.
What I want to return is only one record showing the
total for the Purchase Request. Can anyone help me with
this. I appreciate it.Vic,
I think this is what you wanted to do (your statement of problem is not
quite clear):
SELECT PR_NO,
Total = CONVERT(money, SUM(items.unit_price *
items.qty * (CASE Items.Use_Item_Calc_Qty when 0 then 1 else
Use_Item_Calc_Qty end)))
FROM Items
where pr_no = 5816
Group By PR_NO, Use_Item_Calc_Qty
hth
Quentin
"Vic" <vduran@.specpro-inc.com> wrote in message
news:000d01c3c0dc$56b27560$a501280a@.phx.gbl...
> I have the following query:
> SELECT PR_NO,
> Total => CASE Items.Use_Item_Calc_Qty
> WHEN 0 THEN CONVERT(money, SUM
> (items.unit_price * items.qty))
> ELSE CONVERT(money, SUM(items.unit_price *
> items.qty * ITEM_CALC_QTY))
> END
> FROM Items
> where pr_no = 5816
> Group By PR_NO, Use_Item_Calc_Qty
> The query is returning two records because a record in
> the items table has a value of 0 in Use_item_calc_qty and
> another record has a value of one.
> What I want to return is only one record showing the
> total for the Purchase Request. Can anyone help me with
> this. I appreciate it.sql

Wednesday, March 28, 2012

grouping problem...

I am migrating an Oracle database to SQL. I can't the following query to
work
insert into JDE911Z1
(vnani,vnaa,vndgm,vndgd,vndgy,vnctry,vndct,vnexa,v nlt,vnedbt)
select approp_no + '.1354', sum(isnull(tot_cost,0)),
datepart(mm, GETDATE()) mm, datepart(dd, GETDATE()) dd,
substring( cast(DATEPART(yy, GETDATE()) as varchar(4)), 3,2) yy ,
'0','JE','DASNY CHARGEBACK','AA','AA'+@.client+cast (@.pay_no as varchar(2))
from perptot
where pay_no = @.pay_no and client = @.client'
and approp_no is not null
and b_cat = 'B'
group by approp_no + '.1354', datepart(mm, getdate()),
datepart(dd, getdate()), substring( cast(DATEPART(yy, GETDATE()) as
varchar(4)), 3,2),
'O', 'JE','DASNY CHARGEBACK','AA','AA','AA'+ @.client + cast (@.pay_no as
varchar(2))
The error I get is "GROUP BY expressions must refer to column names that
appear in the select list"
Group by approp_no + '.1354', datepart(mm, getdate()), datepart(dd,
getdate()) works. As soon as I add substring( cast(DATEPART(yy, GETDATE())
as varchar(4)), 3,2) it breaks.
That value just uses getdate() so should be a constant as far as the query is concerned and so does not need to appear in the group by clause.

grouping problem...

I am migrating an Oracle database to SQL. I can't the following query to
work
insert into JDE911Z1
(vnani,vnaa,vndgm,vndgd,vndgy,vnctry,vnd
ct,vnexa,vnlt,vnedbt)
select approp_no + '.1354', sum(isnull(tot_cost,0)),
datepart(mm, GETDATE()) mm, datepart(dd, GETDATE()) dd,
substring( cast(DATEPART(yy, GETDATE()) as varchar(4)), 3,2) yy ,
'0','JE','DASNY CHARGEBACK','AA','AA'+@.client+cast (@.pay_no as varchar(2))
from perptot
where pay_no = @.pay_no and client = @.client'
and approp_no is not null
and b_cat = 'B'
group by approp_no + '.1354', datepart(mm, getdate()),
datepart(dd, getdate()), substring( cast(DATEPART(yy, GETDATE()) as
varchar(4)), 3,2),
'O', 'JE','DASNY CHARGEBACK','AA','AA','AA'+ @.client + cast (@.pay_no as
varchar(2))
The error I get is "GROUP BY expressions must refer to column names that
appear in the select list"
Group by approp_no + '.1354', datepart(mm, getdate()), datepart(dd,
getdate()) works. As soon as I add substring( cast(DATEPART(yy, GETDATE())
as varchar(4)), 3,2) it breaks.That value just uses getdate() so should be a constant as far as the query i
s concerned and so does not need to appear in the group by clause.sql

Grouping on part of a composite key in a dimension on one axis

Hi,

I've already posted the following: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=990233&SiteID=1&mode=1 but I think the way I presented the problem might be confusing, so I will rephrase the problem here:

We want to follow certain measures according to a parent-child dimension (related to the organisational chart of our company). Building a "conventional" Parent-Child using Analysis Services is fairly straight-forward: define the key and the parent.

But the conventional way does not answer one of our needs which is the following:

As the chart evolves, we want to keep the measure were they were in the chart, during a specific moment in time. E.g.:

Say Member A is under B during period X and A is under C during period Y (due to a "reorg"). If we follow the members count evolution of B and C we want to see A counted under B during period X and under C during period Y.

With a "conventional" parent-child dimention, I don't think we can achieve that, as we would only have the last hierarchy with A under C and it would seem as if A had always been under C.

So the idea was to have a table build like this:

Child Parent Period
=====================
A B X
B B X
C C X
A C Y
B B Y
C C Y

Here, we say B and C are both "root" of the tree during period X and Y.

Based on this table, how would I build a cube which will give me the evolution of the members count per member?

I've already tried the following:

1- Parent-Child dimension (let's call it "Hierarchy") where the key attribute is a composite key of Child+Period and the parent key is Parent+Period
2- Regular (or time) dimension based on Period (let's call it "Period")
3- A single Row count measure

This doesn't work perfectly because of the composite key. If I do an MDX query like this:

SELECT {(Period, Measures)} on Columns, {Hierarchy} on Rows FROM Cube, i'll get something like this:

All X Y
Hie... Count Count Count
B (BX) 2 2 null
B (BY) 1 null 1
C (CX) 1 1 null
C (CY) 2 null 2
All 6 3 3

Note: B (BX) means B (the name column) is displayed but the composite key is BX. the 2 under column X comes from A(1) + B(1), i.e. the count includes the parent.

So although I get the right numbers, I can't follow B or C on a single line (because of the composite key.

I've also tried using a Parent-Child dimension with only Child as the Key Column for the key and Parent as the key column for the parent, but then, I don't get the correct numbers.

Is there any way to structure a database/cube which will allow me to follow the information I want, the way I want it and/or a way to write a DMX query on the cubes I've tried which will give me the result I want?

Here is what I would like to see:

All X Y
Hie... Count Count Count
B 3 2 1
C 3 1 2
All 6 3 3

So? Anyone wants to give it a try? Still haven't had any luck making this work. I also haven't seen anything similar being done elsewhere.

I'd be gratefull for any help.

Thanks

Grouping on part of a composite key in a dimension on one axis

Hi,

But the conventional way does not answer one of our needs which is the following:

As the chart evolves, we want to keep the measure were they were in the chart, during a specific moment in time. E.g.:

With a "conventional" parent-child dimention, I don't think we can achieve that, as we would only have the last hierarchy with A under C and it would seem as if A had always been under C.

So the idea was to have a table build like this:

Child Parent Period
=====================
A B X
B B X
C C X
A C Y
B B Y
C C Y

Here, we say B and C are both "root" of the tree during period X and Y.

Based on this table, how would I build a cube which will give me the evolution of the members count per member?

I've already tried the following:

This doesn't work perfectly because of the composite key. If I do an MDX query like this:

SELECT {(Period, Measures)} on Columns, {Hierarchy} on Rows FROM Cube, i'll get something like this:

All X Y
Hie... Count Count Count
B (BX) 2 2 null
B (BY) 1 null 1
C (CX) 1 1 null
C (CY) 2 null 2
All 6 3 3

Note: B (BX) means B (the name column) is displayed but the composite key is BX. the 2 under column X comes from A(1) + B(1), i.e. the count includes the parent.

So although I get the right numbers, I can't follow B or C on a single line (because of the composite key.

I've also tried using a Parent-Child dimension with only Child as the Key Column for the key and Parent as the key column for the parent, but then, I don't get the correct numbers.

Here is what I would like to see:

All X Y
Hie... Count Count Count
B 3 2 1
C 3 1 2
All 6 3 3

So? Anyone wants to give it a try? Still haven't had any luck making this work. I also haven't seen anything similar being done elsewhere.

I'd be gratefull for any help.

Thanks

sql

grouping on date without time

Hello,

I have the following problem.

I a making reports based on a database that i do not control.

In that database i have a table with statistical data including a field with datetime informtion.

The format of the data I receive is "5/04/2007 7:43:27".

In my report i want to create a group which groups my event by date : "05/04/2007"

In my output i always get subgroups by date & time so "5/04/2007 7:43:27", "5/04/2007 7:43:28", ....

How can i group only on the date.

Vincent

Hello Vincent,

Right click on your group row for the dates, and select 'Edit Group...' In the 'Group on:' section, change your expression from =Fields!DateField.Value to =Format(Fields!DateField.Value, "MM/dd/yyyy")

Jarret

|||

Use this expression for grouping:

DateValue(Fields!DateField.Value)

This will set the time part of the datetime field to 00:00:00 and use only the date part. This gives better performance than formatting or any other solution.

Shyam

|||

Vincent,

I forgot to mention this in my last post...

You will probably want to show the date without the time as well, so just put the format statement as your textbox's expression in the group row. You could use Shyam's suggestion for the DateValue function (I didn't know it is more efficient, but I haven't noticed any performance degredation from using format), I just use the format for simplicity; both the group on expression and the textbox expression being shown will be the same.

Jarret

|||

Jarrett,

As you know, any operation (be it grouping or sorting or whatever) based on string is going to be more costlier (if not much more) than other datatypes and unfortunately Format function returns a string though we can still convert it using CDate and use that expression for grouping (which again becomes a 2 level conversion). Maybe there wont be a significant difference in performance unless there are millions of records.

Shyam

|||this is helpful

|||hi Lifesavers

I am getting this default format
3/6/2007 12:00:00 AM|||somehow I am using this following format and it seems to work.

=FormatDateTime(Fields!LOGINDTTIME.Value, 3)

now i am not able to sort it properly ...when i do sort it give following output

03/01/07 01:51:09 pm 03/01/07 11:46:35 am 03/01/07 04:42:53 pm 03/02/07 12:40:08 pm 03/01/07 03:56:04 pm

Please notice 03/01/07 11:46:35 am on second line, it is sorting it on numeric value not in am /pm...any ideas?
|||

Hello Anand,

If you want to display as 03/06/07 12:00:00 AM, you could use this: =Format(Fields!LOGINDTTIME.Value, "MM/dd/yy hh:mms tt"). The FormatDateTime uses your computer's regional settings to display the date/time.

As for the sorting on the table, you need to go to the Properties of the table, then click on the Sorting tab. Instead of using the Format in here, you should sort by the value in the field. You should be sorting by Fields!LOGINDTTIME.Value.

Jarret

|||

The best way to do it is to format the text box where you keep your date. (If it is a table it still will be a text box whithin a table)

So :

-> Right click -> Properties -> Format (tab) -> Format code: -> Ellipsis button [...] ->

... and here you have all kind of Standard formating e.g. date,time, currency ... Good Luck

|||

Hello Bernardo,

One of Anand's questions was how to format it like this 03/06/07 12:00:00 AM. Since there is no standard format that matches this, a custom format had to be used.

I'm not so sure that either way is a 'best' way (putting in a format code through the properties or using the format function), aren't they both doing the same thing? At least with the Format function, you can see the format directly in the textbox without navigating through the dialog box to find it or looking in the properties window.

Jarret

|||thanks Jarret,

now formating and sorting on date works as I wanted

thanks,
anand

Monday, March 26, 2012

Grouping Data with Weekly break out?

Hi All,
I'm trying to figure out and learn how to do the following:
/*I have the following query which works:*/
SELECT DataSource AS [Service Line], NamePrep AS [PO Created By],
COUNT(PoNumber) AS [Total Of PONumber]
FROM OPW
WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
GROUP BY DataSource, NamePrep
My question here is how can I get a w

ly breakout of the COUNT(PoNumber)?
I want a column for each w

and the number of PONumbers Per Datasource and
NamePrep for that w

.
Example Output:
Service Line | PO Created By | Total Of PONumber | 3/5/2005 |
3/12/2005 | ETC...
MNS JOHN 10
MNS ERIC 25
FMS CARL 8
Hope my question makes sense :)
John.If you had a calendar table, this would be easier, but if not, you need to
know the columns you want, or use dynamic SQL...
Select DataSource AS [Service Line], NamePrep AS [PO Created By],
COUNT(PoNumber) AS [Total Of PONumber],
Sum(Case When ReqSubmitDate
Between '03/ 01/2005' AND '03/8/2005' Then 1 End) Wk1Count,
Sum(Case When ReqSubmitDate
Between '03/ 09/2005' AND '03/16/2005' Then 1 End) Wk2Count,
Sum(Case When ReqSubmitDate
Between '03/ 17/2005' AND '03/24/2005' Then 1 End) Wk3Count,
Sum(Case When ReqSubmitDate
Between '03/ 25/2005' AND '03/31/2005' Then 1 End) Wk4Count
FROM OPW
WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
GROUP BY DataSource, NamePrep
If you want it dynamic, you have to write code to dynamic construct an SQL
statement like the one above, based on the date ranges you pass it, and then
execute that SQL Statement using EXECUTE, or sp_ExecuteSQL() functions
"John Rugo" wrote:

> Hi All,
> I'm trying to figure out and learn how to do the following:
> /*I have the following query which works:*/
> SELECT DataSource AS [Service Line], NamePrep AS [PO Created By],
> COUNT(PoNumber) AS [Total Of PONumber]
> FROM OPW
> WHERE (ReqSubmitDate BETWEEN '03/ 01/2005' AND '03/31/2005')
> GROUP BY DataSource, NamePrep
> My question here is how can I get a w

ly breakout of the COUNT(PoNumber)
?
> I want a column for each w

and the number of PONumbers Per Datasource a
nd
> NamePrep for that w

Grouping Data and Selecting highest date

In SQL 2005 I have the following view:

SELECT TOP (100) PERCENT StockCode, Warehouse, QtyOnHand, QtyAllocated, QtyOnOrder, QtyOnBackOrder, DateLastSale, DateLastStockMove,
DateLastPurchase
FROM dbo.MBL_VW_AgedStock_Sales
ORDER BY StockCode

This basically shows a list of stock codes (there are multiple stock codes the same) and the last sold date. What i need to do is group the stock codes which are the same together, and show the latest date.

For example I could have the following:

STOCK CODE Last Date Sold

PC1113 11/01/2007

PC1104 15/03/2007

PC1113 15/02/2007

What I want to see is a list that shows PC1113 with its latest sold date, i.e.

STOCK CODE Last Date Sold

PC1113 15/02/2007

PC1104 15/03/2007

Any ideas?

Thanks

Kris

select StockCode, max(DateLastStockMovie) from your_table_or_view

group by stockcode

order by stockcode

|||

Something like this:

SELECT

'Stock Code' = StockCode,

'Last Sold Date' = max( DateLastSale )

FROM dbo.MBL_VW_AgedStock_Sales

GROUP BY StockCode

ORDER BY StockCode

Grouping consecutive rows of data

This is driving me crazy, I have the following table
Unit Line ProductCode StartTime EndTime
-- -- -- --
--
3 1 120064 2002-08-30 12:31:26.810 2003-05-27
11:39:51.157
3 1 283724 2003-05-27 11:39:51.157 2003-05-27
12:51:52.423
3 1 283724 2003-05-27 12:51:52.423 2003-05-30
07:12:38.997
3 1 285775 2003-05-30 07:12:38.997 2003-06-16
10:37:01.813
3 1 230571 2003-06-16 10:37:01.813 2003-07-04
12:40:32.097
3 1 260775 2003-07-04 12:40:32.097 2003-07-07
14:25:18.483
3 1 260775 2003-07-07 14:25:18.483 2003-07-08
07:02:04.303
3 1 265775 2003-07-08 07:02:04.303 2003-07-14
12:37:42.513
3 1 215690 2003-07-14 12:37:42.513 2003-07-14
12:38:23.450
3 1 255779 2003-07-14 12:38:23.450 2003-07-15
08:09:30.030
3 1 255779 2003-07-15 08:09:30.030 2003-07-15
16:05:53.323
3 1 205716 2003-07-15 16:05:53.323 2003-07-15
16:06:17.527
3 1 203716 2003-07-15 16:06:17.527 2003-07-18
10:53:48.927
3 1 203716 2003-07-18 10:53:48.927 2003-07-18
10:55:17.677
3 1 102001 2003-07-18 10:55:17.677 2003-07-18
10:55:54.190
3 1 203716 2003-07-18 10:55:54.190 2003-07-18
15:40:57.113
3 1 203716 2003-07-18 15:40:57.113 2003-07-18
15:41:05.660
3 1 203716 2003-07-18 15:41:05.660 2003-07-18
16:22:23.050
3 1 203716 2003-07-18 16:22:23.050 2003-07-23
16:25:37.407
I want to be able to group each Unit, Line and ProductCode together so
I can pick up the StartTime and EndTime for consecutive product runs.
i.e.ProductCode 203716 starts at 2003-07-15 16:06:17.527 and runs
until 2003-07-18 10:55:17.677.
This is then replaced by ProductCode 102001 which runs from 2003-07-18
10:55:17.677 until 2003-07-18 10:55:54.190 and is then replaced by
203716 which runs from 2003-07-18 10:55:54.190 until 2003-07-23
16:25:37.407.
I tried the following view:
SELECT TOP 100 PERCENT Unit, Line, ProductCode, MIN(StartTime) AS
StartTime, MAX(EndTime) AS EndTime FROM D_ProductionLog
GROUP BY Unit, Line, ProductCode
ORDER BY StartTime, Unit, Line
But it takes the first time a Product is run and the last time it was
run i.e.
for ProductCode 203716
StartTime 2003-07-15 16:06:17.527
EndTime 2003-07-23 16:25:37.407
Instead of two records.
Many thanks
JimThis is a multi-part message in MIME format.
--=_NextPart_000_014B_01C35111.500514B0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Try:
select
Unit
, Line
, ProductCode
, Type
, case when Type =3D 1 then min (StartTime) else max (EndTime)
from
MyTable
cross join
(
select 1 as Type
union all
select 2
) as x
group by
Unit
, Line
, ProductCode
, Type
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Jim" <jim.holmes@.devro-casings.com> wrote in message =news:68dfae14.0307230741.2f1a9b49@.posting.google.com...
This is driving me crazy, I have the following table
Unit Line ProductCode StartTime EndTime
-- -- -- --
--
3 1 120064 2002-08-30 12:31:26.810 2003-05-27
11:39:51.157
3 1 283724 2003-05-27 11:39:51.157 2003-05-27
12:51:52.423
3 1 283724 2003-05-27 12:51:52.423 2003-05-30
07:12:38.997
3 1 285775 2003-05-30 07:12:38.997 2003-06-16
10:37:01.813
3 1 230571 2003-06-16 10:37:01.813 2003-07-04
12:40:32.097
3 1 260775 2003-07-04 12:40:32.097 2003-07-07
14:25:18.483
3 1 260775 2003-07-07 14:25:18.483 2003-07-08
07:02:04.303
3 1 265775 2003-07-08 07:02:04.303 2003-07-14
12:37:42.513
3 1 215690 2003-07-14 12:37:42.513 2003-07-14
12:38:23.450
3 1 255779 2003-07-14 12:38:23.450 2003-07-15
08:09:30.030
3 1 255779 2003-07-15 08:09:30.030 2003-07-15
16:05:53.323
3 1 205716 2003-07-15 16:05:53.323 2003-07-15
16:06:17.527
3 1 203716 2003-07-15 16:06:17.527 2003-07-18
10:53:48.927
3 1 203716 2003-07-18 10:53:48.927 2003-07-18
10:55:17.677
3 1 102001 2003-07-18 10:55:17.677 2003-07-18
10:55:54.190
3 1 203716 2003-07-18 10:55:54.190 2003-07-18
15:40:57.113
3 1 203716 2003-07-18 15:40:57.113 2003-07-18
15:41:05.660
3 1 203716 2003-07-18 15:41:05.660 2003-07-18
16:22:23.050
3 1 203716 2003-07-18 16:22:23.050 2003-07-23
16:25:37.407
I want to be able to group each Unit, Line and ProductCode together so
I can pick up the StartTime and EndTime for consecutive product runs.
i.e.ProductCode 203716 starts at 2003-07-15 16:06:17.527 and runs
until 2003-07-18 10:55:17.677.
This is then replaced by ProductCode 102001 which runs from 2003-07-18
10:55:17.677 until 2003-07-18 10:55:54.190 and is then replaced by
203716 which runs from 2003-07-18 10:55:54.190 until 2003-07-23
16:25:37.407.
I tried the following view:
SELECT TOP 100 PERCENT Unit, Line, ProductCode, MIN(StartTime) AS
StartTime, MAX(EndTime) AS EndTime FROM D_ProductionLog
GROUP BY Unit, Line, ProductCode
ORDER BY StartTime, Unit, Line
But it takes the first time a Product is run and the last time it was
run i.e.
for ProductCode 203716 StartTime 2003-07-15 16:06:17.527 EndTime 2003-07-23 16:25:37.407
Instead of two records.
Many thanks
Jim
--=_NextPart_000_014B_01C35111.500514B0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

Try:
select
=Unit
, =Line
, ProductCode
, =Type
, case when =Type =3D 1 then min (StartTime) else max (EndTime)
from
=MyTable
cross join
(
select 1 as =Type union all select 2) as x
group by
=Unit
, =Line
, ProductCode
, =Type-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Jim" wrote in message news:68dfae=14.0307230741.2f1a9b49@.posting.google.com...This is driving me crazy, I have the following tableUnit =Line ProductCode StartTime &nbs=p; EndTime-- -- -- ----3 =; 1 120064 2002-08-30 =12:31:26.810 2003-05-2711:39:51.1573 =1 283724 2003-05-27 11:39:51.157 2003-05-2712:51:52.4233 =1 283724 2003-05-27 12:51:52.423 2003-05-3007:12:38.9973 =1 285775 2003-05-30 07:12:38.997 2003-06-1610:37:01.8133 =1 230571 2003-06-16 10:37:01.813 2003-07-0412:40:32.0973 =1 260775 2003-07-04 12:40:32.097 2003-07-0714:25:18.4833 =1 260775 2003-07-07 14:25:18.483 2003-07-0807:02:04.3033 =1 265775 2003-07-08 07:02:04.303 2003-07-1412:37:42.5133 =1 215690 2003-07-14 12:37:42.513 2003-07-1412:38:23.4503 =1 255779 2003-07-14 12:38:23.450 2003-07-1508:09:30.0303 =1 255779 2003-07-15 08:09:30.030 2003-07-1516:05:53.3233 =1 205716 2003-07-15 16:05:53.323 2003-07-1516:06:17.5273 =1 203716 2003-07-15 16:06:17.527 2003-07-1810:53:48.9273 =1 203716 2003-07-18 10:53:48.927 2003-07-1810:55:17.6773 =1 102001 2003-07-18 10:55:17.677 2003-07-1810:55:54.1903 =1 203716 2003-07-18 10:55:54.190 2003-07-1815:40:57.1133 =1 203716 2003-07-18 15:40:57.113 2003-07-1815:41:05.6603 =1 203716 2003-07-18 15:41:05.660 2003-07-1816:22:23.0503 =1 203716 2003-07-18 16:22:23.050 2003-07-2316:25:37.407I want to be able to group each Unit, =Line and ProductCode together soI can pick up the StartTime and EndTime for consecutive product runs.i.e.ProductCode 203716 starts at 2003-07-15 =16:06:17.527 and runsuntil 2003-07-18 10:55:17.677.This is =then replaced by ProductCode 102001 which runs from =2003-07-1810:55:17.677 until 2003-07-18 10:55:54.190 and is then replaced by203716 which runs =from 2003-07-18 10:55:54.190 until 2003-07-2316:25:37.407.I tried =the following view:SELECT TOP 100 PERCENT =Unit, Line, ProductCode, MIN(StartTime) ASStartTime, MAX(EndTime) AS =EndTime FROM D_ProductionLogGROUP BY Unit, Line, ProductCodeORDER =BY StartTime, Unit, LineBut it takes the first time a Product is =run and the last time it wasrun i.e.for ProductCode 203716 StartTime 2003-07-15 16:06:17.527 EndTime 2003-07-23 16:25:37.407 Instead =of two records.Many thanksJim

--=_NextPart_000_014B_01C35111.500514B0--|||I am confused, do you want to get one record per ProductCode or you just
want to order them together?
"Jim" <jim.holmes@.devro-casings.com> wrote in message
news:68dfae14.0307230741.2f1a9b49@.posting.google.com...
> This is driving me crazy, I have the following table
>
> Unit Line ProductCode StartTime EndTime
> -- -- -- --
> --
> 3 1 120064 2002-08-30 12:31:26.810 2003-05-27
> 11:39:51.157
> 3 1 283724 2003-05-27 11:39:51.157 2003-05-27
> 12:51:52.423
> 3 1 283724 2003-05-27 12:51:52.423 2003-05-30
> 07:12:38.997
> 3 1 285775 2003-05-30 07:12:38.997 2003-06-16
> 10:37:01.813
> 3 1 230571 2003-06-16 10:37:01.813 2003-07-04
> 12:40:32.097
> 3 1 260775 2003-07-04 12:40:32.097 2003-07-07
> 14:25:18.483
> 3 1 260775 2003-07-07 14:25:18.483 2003-07-08
> 07:02:04.303
> 3 1 265775 2003-07-08 07:02:04.303 2003-07-14
> 12:37:42.513
> 3 1 215690 2003-07-14 12:37:42.513 2003-07-14
> 12:38:23.450
> 3 1 255779 2003-07-14 12:38:23.450 2003-07-15
> 08:09:30.030
> 3 1 255779 2003-07-15 08:09:30.030 2003-07-15
> 16:05:53.323
> 3 1 205716 2003-07-15 16:05:53.323 2003-07-15
> 16:06:17.527
> 3 1 203716 2003-07-15 16:06:17.527 2003-07-18
> 10:53:48.927
> 3 1 203716 2003-07-18 10:53:48.927 2003-07-18
> 10:55:17.677
> 3 1 102001 2003-07-18 10:55:17.677 2003-07-18
> 10:55:54.190
> 3 1 203716 2003-07-18 10:55:54.190 2003-07-18
> 15:40:57.113
> 3 1 203716 2003-07-18 15:40:57.113 2003-07-18
> 15:41:05.660
> 3 1 203716 2003-07-18 15:41:05.660 2003-07-18
> 16:22:23.050
> 3 1 203716 2003-07-18 16:22:23.050 2003-07-23
> 16:25:37.407
> I want to be able to group each Unit, Line and ProductCode together so
> I can pick up the StartTime and EndTime for consecutive product runs.
> i.e.ProductCode 203716 starts at 2003-07-15 16:06:17.527 and runs
> until 2003-07-18 10:55:17.677.
> This is then replaced by ProductCode 102001 which runs from 2003-07-18
> 10:55:17.677 until 2003-07-18 10:55:54.190 and is then replaced by
> 203716 which runs from 2003-07-18 10:55:54.190 until 2003-07-23
> 16:25:37.407.
> I tried the following view:
> SELECT TOP 100 PERCENT Unit, Line, ProductCode, MIN(StartTime) AS
> StartTime, MAX(EndTime) AS EndTime FROM D_ProductionLog
> GROUP BY Unit, Line, ProductCode
> ORDER BY StartTime, Unit, Line
> But it takes the first time a Product is run and the last time it was
> run i.e.
> for ProductCode 203716
> StartTime 2003-07-15 16:06:17.527
> EndTime 2003-07-23 16:25:37.407
> Instead of two records.
> Many thanks
> Jim|||Greetings,
Unless you breathe,sniff,snort and live S2k sql queries like
this are difficult to construct.An easy way to solve this
problem is to create a column with the same value for every
combination of Unit,Line and ProductCode based on the sort
order of StartTime.This new column or rank can then be used
as a grouping column to find the min and max times for each
Unit,Line and ProductCode combination.Current versions of
Oracle and DB2 have this sql99 functionality,S2k does not.
create table #D_ProductionLog(Unit int,Line int,
ProductCode int,StartTime datetime,EndTime datetime)
go
insert #D_ProductionLog values(3,1,120064,'2002-08-30
12:31:26.810','2003-05-27 11:39:51.157')
insert #D_ProductionLog values(3,1,283724,'2003-05-27
11:39:51.157','2003-05-27 12:51:52.423')
insert #D_ProductionLog values(3,1,283724,'2003-05-27
12:51:52.423','2003-05-30 07:12:38.997')
insert #D_ProductionLog values(3,1,285775,'2003-05-30
07:12:38.997','2003-06-16 10:37:01.813')
insert #D_ProductionLog values(3,1,230571,'2003-06-16
10:37:01.813','2003-07-04 12:40:32.097')
insert #D_ProductionLog values(3,1,260775,'2003-07-04
12:40:32.097','2003-07-07 14:25:18.483')
insert #D_ProductionLog values(3,1,260775,'2003-07-07
14:25:18.483','2003-07-08 07:02:04.303')
insert #D_ProductionLog values(3,1,265775,'2003-07-08
07:02:04.303','2003-07-14 12:37:42.513')
insert #D_ProductionLog values(3,1,215690,'2003-07-14
12:37:42.513','2003-07-14 12:38:23.450')
insert #D_ProductionLog values(3,1,255779,'2003-07-14
12:38:23.450','2003-07-15 08:09:30.030')
insert #D_ProductionLog values(3,1,255779,'2003-07-15
08:09:30.030','2003-07-15 16:05:53.323')
insert #D_ProductionLog values(3,1,205716,'2003-07-15
16:05:53.323','2003-07-15 16:06:17.527')
insert #D_ProductionLog values(3,1,203716,'2003-07-15
16:06:17.527','2003-07-18 10:53:48.927')
insert #D_ProductionLog values(3,1,203716,'2003-07-18
10:53:48.927','2003-07-18 10:55:17.677')
insert #D_ProductionLog values(3,1,102001,'2003-07-18
10:55:17.677','2003-07-18 10:55:54.190')
insert #D_ProductionLog values(3,1,203716,'2003-07-18
10:55:54.190','2003-07-18 15:40:57.113')
insert #D_ProductionLog values(3,1,203716,'2003-07-18
15:40:57.113','2003-07-18 15:41:05.660')
insert #D_ProductionLog values(3,1,203716,'2003-07-18
15:41:05.660','2003-07-18 16:22:23.050')
insert #D_ProductionLog values(3,1,203716,'2003-07-18
16:22:23.050','2003-07-23 16:25:37.407')
You can use the RAC utility for S2k to easily create this
virtual column.The 'drank' column binds together the same Unit,Line
and ProductCode combinations in the sort order of StartTime.
Exec Rac
@.transform='_dummy_',@.style='121',@.datelen='25',
@.rows='Unit & Line & ProductCode & StartTime(date) & EndTime(date)',
@.rowsort='StartTime & Unit & Line & ProductCode',
@.pvtcol='Report Mode',
@.from='#D_ProductionLog',
-- create the virtual column drank using the @.rowindicators parameter.
@.rowindicators='ProductCode{drank}',@.counterdatatype='integer',
@.grand_totals='n',@.rowbreak='n',@.racheck='y',@.defaultexceptions='dumy'
This result shows drank* column and how it binds.It is simply an
incrementing integer for each combination.
Unit Line ProductCode StartTime EndTime
drank*
-- -- -- -- --
-- --
3 1 120064 2002-08-30 12:31:26.810 2003-05-27 11:39:51.157
1
3 1 283724 2003-05-27 11:39:51.157 2003-05-27 12:51:52.423
2
3 1 283724 2003-05-27 12:51:52.423 2003-05-30 07:12:38.997
2
3 1 285775 2003-05-30 07:12:38.997 2003-06-16 10:37:01.813
3
3 1 230571 2003-06-16 10:37:01.813 2003-07-04 12:40:32.097
4
3 1 260775 2003-07-04 12:40:32.097 2003-07-07 14:25:18.483
5
3 1 260775 2003-07-07 14:25:18.483 2003-07-08 07:02:04.303
5
3 1 265775 2003-07-08 07:02:04.303 2003-07-14 12:37:42.513
6
3 1 215690 2003-07-14 12:37:42.513 2003-07-14 12:38:23.450
7
3 1 255779 2003-07-14 12:38:23.450 2003-07-15 08:09:30.030
8
3 1 255779 2003-07-15 08:09:30.030 2003-07-15 16:05:53.323
8
3 1 205716 2003-07-15 16:05:53.323 2003-07-15 16:06:17.527
9
3 1 203716 2003-07-15 16:06:17.527 2003-07-18 10:53:48.927
10
3 1 203716 2003-07-18 10:53:48.927 2003-07-18 10:55:17.677
10
3 1 102001 2003-07-18 10:55:17.677 2003-07-18 10:55:54.190
11
3 1 203716 2003-07-18 10:55:54.190 2003-07-18 15:40:57.113
12
3 1 203716 2003-07-18 15:40:57.113 2003-07-18 15:41:05.660
12
3 1 203716 2003-07-18 15:41:05.660 2003-07-18 16:22:23.050
12
3 1 203716 2003-07-18 16:22:23.050 2003-07-23 16:25:37.407
12
Using the drank column in a GROUP BY easily solves the problem in
a single execution of RAC.
Exec Rac
@.transform='_dummy_',@.style='121',@.datelen='25',
@.rows='Unit & Line & ProductCode & StartTime(date) & EndTime(date)',
@.rowsort='StartTime & Unit & Line & ProductCode',
@.pvtcol='Report Mode',
@.from='#D_ProductionLog',
@.rowindicators='ProductCode{drank}',@.counterdatatype='integer',
@.grand_totals='n',@.rowbreak='n',@.racheck='y',
-- Use a simple group by query to solve the problem.
-- You could also include the count of each combination if desired.
@.select='select Unit,Line,ProductCode,MIN(StartTime) AS StartTime,
MAX(EndTime) AS EndTime
from rac
group by drank,Unit,Line,ProductCode
order by drank'
Unit Line ProductCode StartTime EndTime
-- -- -- -- --
--
3 1 120064 2002-08-30 12:31:26.810 2003-05-27 11:39:51.157
3 1 283724 2003-05-27 11:39:51.157 2003-05-30 07:12:38.997
3 1 285775 2003-05-30 07:12:38.997 2003-06-16 10:37:01.813
3 1 230571 2003-06-16 10:37:01.813 2003-07-04 12:40:32.097
3 1 260775 2003-07-04 12:40:32.097 2003-07-08 07:02:04.303
3 1 265775 2003-07-08 07:02:04.303 2003-07-14 12:37:42.513
3 1 215690 2003-07-14 12:37:42.513 2003-07-14 12:38:23.450
3 1 255779 2003-07-14 12:38:23.450 2003-07-15 16:05:53.323
3 1 205716 2003-07-15 16:05:53.323 2003-07-15 16:06:17.527
3 1 203716 2003-07-15 16:06:17.527 2003-07-18 10:55:17.677
3 1 102001 2003-07-18 10:55:17.677 2003-07-18 10:55:54.190
3 1 203716 2003-07-18 10:55:54.190 2003-07-23 16:25:37.407
RAC v2.2 and QALite released.
www.rac4sql.net

Grouping by week starting on Sunday

I have a matrix report where I group employee hours by week. I have the
grouping working using the following expressions:
=DatePart(DateInterval.WeekOfYear,Fields!businessdate.Value)
=DatePart(DateInterval.Year, Fields!shift_date.Value)
It looks like the week of year starts on monday. Is there a way to change
this to start on Sunday? Or is there a better way to group by week starting
on Sunday? Any help is appreciated.I tried expression:
=DatePart("ww", Fields!shift_date.Value, FirstDayofWeek.Sunday)
and
=DatePart("ww", Fields!shift_date.Value, vbSunday)
neither of these work either.
"TBraun" wrote:
> I have a matrix report where I group employee hours by week. I have the
> grouping working using the following expressions:
> =DatePart(DateInterval.WeekOfYear,Fields!businessdate.Value)
> =DatePart(DateInterval.Year, Fields!shift_date.Value)
> It looks like the week of year starts on monday. Is there a way to change
> this to start on Sunday? Or is there a better way to group by week starting
> on Sunday? Any help is appreciated.

Friday, March 23, 2012

Grouping by datecolumn

Hi,

i have a table which contains the following columns:

Id = bigint id 1 +1
Machineid = bigint
logdate = datetime
vtp = bigint,

There are more records for 1 machine p/day
i want to group them together by complete day and get the sum of vtp for every different machine for each day.

Someone knows how to do this?
Thanx in front!
CHeers Wimselect machineid, convert(char(10),logdate,120), sum(vtp)
from <your_table>
where <if any conditions apply>
group by machineid, convert(char(10),logdate,120)
order by machineid, convert(char(10),logdate,120)

/*
look up cast / convert in BOL for other date / time formats
*/

Grouping by Age

I have a table Age and need to create Report by Grouping Salesfigures according to Age.
I put the following expression into Grouping and Sorting Properties/General/Filter /Sorting Expression... as well as in Textbox Properties/ values..

=IIF(Fields!Age.Value < 16, "<16",IIF(Fields!Age.Value <21,"16-20")

Errormessage: Value expression for textbox "Age" error: Argument not specified for parameter 'FalsePart' of 'Public function IIF(Expression as Boolean, TruePart as Object, Falsepart As Object) As Object'.

Question 2 .
How to return Month as January, February... In correct order?
"DATENAME(mm, Sales.time) AS Month" (Ascending ) returns starting with April, August...
DATEPART(mm, Sales.time) AS Month (Ascending ) returns starting with 1, 10, 11 ...

Answer 1

Your expression is incomplete. As the error message says, you are missing the FlasePart of the nested Iif function, as well a parentheses

=IIF(Fields!Age.Value < 16, "<16",IIF(Fields!Age.Value <21,"16-20", ">20" ) )

Can you clarify Quaestion 2. E.g. where are you putting this code, can you paste your query?

|||

For Question 1 you don't have a false path to follow in your second iif.

=IIF(Fields!Age.Value < 16, "<16",IIF(Fields!Age.Value <21,"16-20",""))

|||Thank you so much!

SELECT SUM(Cd.Price) AS Sales, DATENAME(mm, Purchase.time) AS Month, Staff.Name
FROM Staff INNER JOIN
Purchase ON Staff.Staff_id = Purchase.Salesperson_id INNER JOIN
Cd ON Purchase.Cd_id = Cd.Cd_id
GROUP BY DATENAME(mm, Purchase.time), Staff.Name
ORDER BY Month Asc

The outcome is starting with April...
(not with January)?|||Thank you.. still, "Argument not specified for false part". Something I've misunderstood?

=IIF(Fields!Age.Value < 16, "<16",IIF(Fields!Age.Value <21,"16-20"),IIF(Fields!Age.Value <31,"21-30"), IIF(Fields!Age.Value < 41,"31-40"),IIF(Fields!Age.Value <51,"41-50",">50"))

I put this value expression In textbox "Age"/Expression, as well as in Grouping Sorting properties /General/Filter/ Sorting|||

Yes you have misunderstood this slightly. The definition of the Iif function is

Iif(<<condition>>, TruePart, FalsePart)

Your expression puts many Iif's all passed into a single Iif. You actually have to nest the Iif's as the FalsePart of the previous Iif

Your expression:
=IIF
( Fields!Age.Value < 16 <- condition
, "<16" <- TruePart
, IIF(Fields!Age.Value <21,"16-20") <- FalsePart nested IIF
, IIF(Fields!Age.Value <31,"21-30") <- Error 4th argument
, IIF(Fields!Age.Value <41,"31-40") <- Error 5th argument
, IIF(Fields!Age.Value <51,"41-50",">50") <- Error 6th argument
)

Correct Expression:
=IIF
( Fields!Age.Value < 16 <- condition
, "<16" <- TruePart
, IIF <- FalsePart nested IIF
( Fields!Age.Value < 21 <- condition
,"16-20" <- TruePart
, IIF <- FalsePart nested IIF
( Fields!Age.Value < 31 <- condition
, "21-30" <- TruePart
, IIF <- FalsePart nested IIF
( Fields!Age.Value < 41 <- condition
, "31-40" <- TruePart
, IIF <- FalsePart nested IIF
( Fields!Age.Value < 51 <- condition
, "41-50" <- TruePart
, ">50" <- FalsePart
)
)
)
)
)

just make sure you get the parentheses right and you remove the comments

|||

As far as your query goes, display the name but order by number. for this to work you must include both in the GROUP BY clause

|||

Would it be possible to create a field from a select e.g.

SELECT Age, AgeGroup =

CASE

WHEN (age >= 1 and age <= 3) THEN 'Age 1-3'

WHEN (age >= 4 and age <= 5) THEN 'Age 4-5'

WHEN (age >= 6 and age <= 7) THEN 'Age 6-7'

ELSE 'Over age'

END

FROM tblAge

|||In fact, I prefer this option, give RS less work to do.

grouping and concatenating

i have a the following table:

CREATE TABLE [dbo].[tIndex] (
[indexID] [int] IDENTITY (1, 1) NOT NULL ,
[wordID] [int] NULL ,
[wordPos] [int] NULL ,
[paraID] [int] NULL
) ON [PRIMARY]
GO

for each wordID i have many paraID and for each wordID,paraID i have many wordPos

i will use the following convention:
paraID=p
wordID=w
wordPos=wp

i want to concatenate the columns to get the following format:

row1: w1 p1,NB1,wp1,wp2,w3... | p2,NB2,wp4,wp5,wp6... | ...
row2: w2 ...
row3: w3 ...

where NB1 is the number of wp having w1 and p1

Note: the length of a row may exceed 8000 charsOriginally posted by samham
Note: the length of a row may exceed 8000 chars If any column exceeds 8000 characters (which is what I think you are trying to say), then you have no choice... You must build those columns on the client.

-PatP|||the total rows of the table is 15 million

i am trying to copy the table content to a text file in the format i described

I put the note about the 8000 chars to say that 1 row cannot be contained in a varchar(8000) variable in case concatenating using a varchar(8000) is a solution

i am using c# as my programming language so my last option is to do this by c# code by selecting wordID and then for each rowID select the paraID and then for each wordID,paraID select the wordPos

but i was wondering if this can be done by sql and then send the result directly to a textfile|||Sorry, SQL Server can't do what you want. According to SQL Maximum Capacity Specifications (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_ts_8dbn.asp), the maximum row size for SQL Server is 8060 bytes. The maximum single string is 8000 bytes.

In your case, your last resort is the only one that might work.

-PatP|||Ok Pat thank you
i will just go for the c# solutionsql

grouping / count question

I am trying to obtain a sum of the various sequences from the following
table. I was thinking I could do some sort of select sum( <union query
here>) but that's not the case.
so for the following sample I would like to count the number of times the
values 1 & 2 occur in the same testsets.
Thanks
create table test (testset int, testnumber int, value int )
insert into test values(1,1,1)
insert into test values(1,2,2)
insert into test values(1,3,3)
insert into test values(1,4,4)
insert into test values(1,5,5)
insert into test values(2,1,1)
insert into test values(2,2,2)
insert into test values(2,3,7)
insert into test values(2,4,8)
insert into test values(2,5,9)
insert into test values(3,1,2)
insert into test values(3,2,3)
insert into test values(3,3,6)
insert into test values(3,4,7)
insert into test values(3,5,8)
--select testset from test where value = 1 and value = 2 group by testset
select testset as t from test where testnumber = 1 and value = 1
union
select testset as t from test where testnumber = 2 and value = 2
drop table testSELECT testset
FROM test
WHERE testnumber = value
AND testnumber IN (1,2)
--
Jacco Schalkwijk
SQL Server MVP
"D" <Dave@.nothing.net> wrote in message
news:%23KJBL3bRFHA.244@.TK2MSFTNGP12.phx.gbl...
>I am trying to obtain a sum of the various sequences from the following
>table. I was thinking I could do some sort of select sum( <union query
>here>) but that's not the case.
> so for the following sample I would like to count the number of times the
> values 1 & 2 occur in the same testsets.
> Thanks
> create table test (testset int, testnumber int, value int )
> insert into test values(1,1,1)
> insert into test values(1,2,2)
> insert into test values(1,3,3)
> insert into test values(1,4,4)
> insert into test values(1,5,5)
> insert into test values(2,1,1)
> insert into test values(2,2,2)
> insert into test values(2,3,7)
> insert into test values(2,4,8)
> insert into test values(2,5,9)
> insert into test values(3,1,2)
> insert into test values(3,2,3)
> insert into test values(3,3,6)
> insert into test values(3,4,7)
> insert into test values(3,5,8)
> --select testset from test where value = 1 and value = 2 group by testset
> select testset as t from test where testnumber = 1 and value = 1
> union
> select testset as t from test where testnumber = 2 and value = 2
> drop table test
>|||Thank you but I am trying to sum the total number of times that 1 and 2 have
come out. Optimally I'd like to be able to sum any number of combinations
from within the same testset. For example how many times has the sequence
1,2 and 7 come out? The ultimate would be something that told me the top
sequence is 1,2 & 7 with 55 occurrences.
Thanks
Regards
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:OPNWKFcRFHA.1176@.TK2MSFTNGP12.phx.gbl...
> SELECT testset
> FROM test
> WHERE testnumber = value
> AND testnumber IN (1,2)
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "D" <Dave@.nothing.net> wrote in message
> news:%23KJBL3bRFHA.244@.TK2MSFTNGP12.phx.gbl...
>>I am trying to obtain a sum of the various sequences from the following
>>table. I was thinking I could do some sort of select sum( <union query
>>here>) but that's not the case.
>> so for the following sample I would like to count the number of times the
>> values 1 & 2 occur in the same testsets.
>> Thanks
>> create table test (testset int, testnumber int, value int )
>> insert into test values(1,1,1)
>> insert into test values(1,2,2)
>> insert into test values(1,3,3)
>> insert into test values(1,4,4)
>> insert into test values(1,5,5)
>> insert into test values(2,1,1)
>> insert into test values(2,2,2)
>> insert into test values(2,3,7)
>> insert into test values(2,4,8)
>> insert into test values(2,5,9)
>> insert into test values(3,1,2)
>> insert into test values(3,2,3)
>> insert into test values(3,3,6)
>> insert into test values(3,4,7)
>> insert into test values(3,5,8)
>> --select testset from test where value = 1 and value = 2 group by testset
>> select testset as t from test where testnumber = 1 and value = 1
>> union
>> select testset as t from test where testnumber = 2 and value = 2
>> drop table test
>|||Can you give the expected results for your questions with the sample data
you have given (Add more data if necessary)? Also, what is the Primary Key
on the table 'test'?
--
Jacco Schalkwijk
SQL Server MVP
"D" <Dave@.nothing.net> wrote in message
news:uuMUNjcRFHA.2964@.TK2MSFTNGP15.phx.gbl...
> Thank you but I am trying to sum the total number of times that 1 and 2
> have come out. Optimally I'd like to be able to sum any number of
> combinations from within the same testset. For example how many times has
> the sequence 1,2 and 7 come out? The ultimate would be something that told
> me the top sequence is 1,2 & 7 with 55 occurrences.
> Thanks
> Regards
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:OPNWKFcRFHA.1176@.TK2MSFTNGP12.phx.gbl...
>> SELECT testset
>> FROM test
>> WHERE testnumber = value
>> AND testnumber IN (1,2)
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "D" <Dave@.nothing.net> wrote in message
>> news:%23KJBL3bRFHA.244@.TK2MSFTNGP12.phx.gbl...
>>I am trying to obtain a sum of the various sequences from the following
>>table. I was thinking I could do some sort of select sum( <union query
>>here>) but that's not the case.
>> so for the following sample I would like to count the number of times
>> the values 1 & 2 occur in the same testsets.
>> Thanks
>> create table test (testset int, testnumber int, value int )
>> insert into test values(1,1,1)
>> insert into test values(1,2,2)
>> insert into test values(1,3,3)
>> insert into test values(1,4,4)
>> insert into test values(1,5,5)
>> insert into test values(2,1,1)
>> insert into test values(2,2,2)
>> insert into test values(2,3,7)
>> insert into test values(2,4,8)
>> insert into test values(2,5,9)
>> insert into test values(3,1,2)
>> insert into test values(3,2,3)
>> insert into test values(3,3,6)
>> insert into test values(3,4,7)
>> insert into test values(3,5,8)
>> --select testset from test where value = 1 and value = 2 group by
>> testset
>> select testset as t from test where testnumber = 1 and value = 1
>> union
>> select testset as t from test where testnumber = 2 and value = 2
>> drop table test
>>
>|||for the sample results if I were searching for the total of the number of
occurences of 1,2 it would be 2, one in testset 1 and one in test set 2.
For the primary key I have testset and testnumber
Thanks for your help today
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:edDfhucRFHA.648@.TK2MSFTNGP14.phx.gbl...
> Can you give the expected results for your questions with the sample data
> you have given (Add more data if necessary)? Also, what is the Primary Key
> on the table 'test'?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "D" <Dave@.nothing.net> wrote in message
> news:uuMUNjcRFHA.2964@.TK2MSFTNGP15.phx.gbl...
>> Thank you but I am trying to sum the total number of times that 1 and 2
>> have come out. Optimally I'd like to be able to sum any number of
>> combinations from within the same testset. For example how many times has
>> the sequence 1,2 and 7 come out? The ultimate would be something that
>> told me the top sequence is 1,2 & 7 with 55 occurrences.
>> Thanks
>> Regards
>>
>> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
>> wrote in message news:OPNWKFcRFHA.1176@.TK2MSFTNGP12.phx.gbl...
>> SELECT testset
>> FROM test
>> WHERE testnumber = value
>> AND testnumber IN (1,2)
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "D" <Dave@.nothing.net> wrote in message
>> news:%23KJBL3bRFHA.244@.TK2MSFTNGP12.phx.gbl...
>>I am trying to obtain a sum of the various sequences from the following
>>table. I was thinking I could do some sort of select sum( <union query
>>here>) but that's not the case.
>> so for the following sample I would like to count the number of times
>> the values 1 & 2 occur in the same testsets.
>> Thanks
>> create table test (testset int, testnumber int, value int )
>> insert into test values(1,1,1)
>> insert into test values(1,2,2)
>> insert into test values(1,3,3)
>> insert into test values(1,4,4)
>> insert into test values(1,5,5)
>> insert into test values(2,1,1)
>> insert into test values(2,2,2)
>> insert into test values(2,3,7)
>> insert into test values(2,4,8)
>> insert into test values(2,5,9)
>> insert into test values(3,1,2)
>> insert into test values(3,2,3)
>> insert into test values(3,3,6)
>> insert into test values(3,4,7)
>> insert into test values(3,5,8)
>> --select testset from test where value = 1 and value = 2 group by
>> testset
>> select testset as t from test where testnumber = 1 and value = 1
>> union
>> select testset as t from test where testnumber = 2 and value = 2
>> drop table test
>>
>>
>|||Please provide the complete expected resultset, with column names and
values. That will make it a lot clearer for me than a textual description.
Also, what will the resultset be if you add the row insert into test
values(3,1,1) to the test data?
--
Jacco Schalkwijk
SQL Server MVP
"D" <Dave@.nothing.net> wrote in message
news:%23cNBW1cRFHA.3140@.tk2msftngp13.phx.gbl...
> for the sample results if I were searching for the total of the number of
> occurences of 1,2 it would be 2, one in testset 1 and one in test set 2.
> For the primary key I have testset and testnumber
> Thanks for your help today
>
> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
> wrote in message news:edDfhucRFHA.648@.TK2MSFTNGP14.phx.gbl...
>> Can you give the expected results for your questions with the sample data
>> you have given (Add more data if necessary)? Also, what is the Primary
>> Key on the table 'test'?
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "D" <Dave@.nothing.net> wrote in message
>> news:uuMUNjcRFHA.2964@.TK2MSFTNGP15.phx.gbl...
>> Thank you but I am trying to sum the total number of times that 1 and 2
>> have come out. Optimally I'd like to be able to sum any number of
>> combinations from within the same testset. For example how many times
>> has the sequence 1,2 and 7 come out? The ultimate would be something
>> that told me the top sequence is 1,2 & 7 with 55 occurrences.
>> Thanks
>> Regards
>>
>> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
>> wrote in message news:OPNWKFcRFHA.1176@.TK2MSFTNGP12.phx.gbl...
>> SELECT testset
>> FROM test
>> WHERE testnumber = value
>> AND testnumber IN (1,2)
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "D" <Dave@.nothing.net> wrote in message
>> news:%23KJBL3bRFHA.244@.TK2MSFTNGP12.phx.gbl...
>>I am trying to obtain a sum of the various sequences from the following
>>table. I was thinking I could do some sort of select sum( <union query
>>here>) but that's not the case.
>> so for the following sample I would like to count the number of times
>> the values 1 & 2 occur in the same testsets.
>> Thanks
>> create table test (testset int, testnumber int, value int )
>> insert into test values(1,1,1)
>> insert into test values(1,2,2)
>> insert into test values(1,3,3)
>> insert into test values(1,4,4)
>> insert into test values(1,5,5)
>> insert into test values(2,1,1)
>> insert into test values(2,2,2)
>> insert into test values(2,3,7)
>> insert into test values(2,4,8)
>> insert into test values(2,5,9)
>> insert into test values(3,1,2)
>> insert into test values(3,2,3)
>> insert into test values(3,3,6)
>> insert into test values(3,4,7)
>> insert into test values(3,5,8)
>> --select testset from test where value = 1 and value = 2 group by
>> testset
>> select testset as t from test where testnumber = 1 and value = 1
>> union
>> select testset as t from test where testnumber = 2 and value = 2
>> drop table test
>>
>>
>>
>|||after adding the 3,1,1 set the data looks like '
>> insert into test values(1,1,1)
>> insert into test values(1,2,2)
>> insert into test values(1,3,3)
>> insert into test values(1,4,4)
>> insert into test values(1,5,5)
>> insert into test values(2,1,1)
>> insert into test values(2,2,2)
>> insert into test values(2,3,4)
>> insert into test values(2,4,5)
>> insert into test values(2,5,9)
>> insert into test values(3,1,1)
>> insert into test values(3,1,2)
>> insert into test values(3,2,3)
>> insert into test values(3,3,4)
>> insert into test values(3,4,7)
if I could get a result set that looked like
x y total
1 2 3
3 4 2
4 5 2
I started with sets of 2 and figured once I have that I can expand it to 3
or 4 if needed to so that the results would look like
x y z total
1 2 3 2
2 3 4 2
If I could get that, that would be most excellent. Basically I'm trying to
determine what patterns (grouped by testsets) occur the most. And the value
never repeats within a testset.
Thanks alot.
"Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid> wrote
in message news:eUCV39cRFHA.252@.TK2MSFTNGP12.phx.gbl...
> Please provide the complete expected resultset, with column names and
> values. That will make it a lot clearer for me than a textual description.
> Also, what will the resultset be if you add the row insert into test
> values(3,1,1) to the test data?
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "D" <Dave@.nothing.net> wrote in message
> news:%23cNBW1cRFHA.3140@.tk2msftngp13.phx.gbl...
>> for the sample results if I were searching for the total of the number of
>> occurences of 1,2 it would be 2, one in testset 1 and one in test set 2.
>> For the primary key I have testset and testnumber
>> Thanks for your help today
>>
>> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
>> wrote in message news:edDfhucRFHA.648@.TK2MSFTNGP14.phx.gbl...
>> Can you give the expected results for your questions with the sample
>> data you have given (Add more data if necessary)? Also, what is the
>> Primary Key on the table 'test'?
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "D" <Dave@.nothing.net> wrote in message
>> news:uuMUNjcRFHA.2964@.TK2MSFTNGP15.phx.gbl...
>> Thank you but I am trying to sum the total number of times that 1 and 2
>> have come out. Optimally I'd like to be able to sum any number of
>> combinations from within the same testset. For example how many times
>> has the sequence 1,2 and 7 come out? The ultimate would be something
>> that told me the top sequence is 1,2 & 7 with 55 occurrences.
>> Thanks
>> Regards
>>
>> "Jacco Schalkwijk" <jacco.please.reply@.to.newsgroups.mvps.org.invalid>
>> wrote in message news:OPNWKFcRFHA.1176@.TK2MSFTNGP12.phx.gbl...
>> SELECT testset
>> FROM test
>> WHERE testnumber = value
>> AND testnumber IN (1,2)
>> --
>> Jacco Schalkwijk
>> SQL Server MVP
>>
>> "D" <Dave@.nothing.net> wrote in message
>> news:%23KJBL3bRFHA.244@.TK2MSFTNGP12.phx.gbl...
>>I am trying to obtain a sum of the various sequences from the
>>following table. I was thinking I could do some sort of select sum(
>><union query here>) but that's not the case.
>> so for the following sample I would like to count the number of times
>> the values 1 & 2 occur in the same testsets.
>> Thanks
>> create table test (testset int, testnumber int, value int )
>> insert into test values(1,1,1)
>> insert into test values(1,2,2)
>> insert into test values(1,3,3)
>> insert into test values(1,4,4)
>> insert into test values(1,5,5)
>> insert into test values(2,1,1)
>> insert into test values(2,2,2)
>> insert into test values(2,3,7)
>> insert into test values(2,4,8)
>> insert into test values(2,5,9)
>> insert into test values(3,1,2)
>> insert into test values(3,2,3)
>> insert into test values(3,3,6)
>> insert into test values(3,4,7)
>> insert into test values(3,5,8)
>> --select testset from test where value = 1 and value = 2 group by
>> testset
>> select testset as t from test where testnumber = 1 and value = 1
>> union
>> select testset as t from test where testnumber = 2 and value = 2
>> drop table test
>>
>>
>>
>>
>|||On Wed, 20 Apr 2005 23:50:35 -0400, D wrote:
>after adding the 3,1,1 set the data looks like '
>>> insert into test values(1,1,1)
>>> insert into test values(1,2,2)
>>> insert into test values(1,3,3)
>>> insert into test values(1,4,4)
>>> insert into test values(1,5,5)
>>>
>>> insert into test values(2,1,1)
>>> insert into test values(2,2,2)
>>> insert into test values(2,3,4)
>>> insert into test values(2,4,5)
>>> insert into test values(2,5,9)
>>> insert into test values(3,1,1)
>>> insert into test values(3,1,2)
>>> insert into test values(3,2,3)
>>> insert into test values(3,3,4)
>>> insert into test values(3,4,7)
>if I could get a result set that looked like
>x y total
>1 2 3
>3 4 2
>4 5 2
>I started with sets of 2 and figured once I have that I can expand it to 3
>or 4 if needed to so that the results would look like
>x y z total
>1 2 3 2
>2 3 4 2
>If I could get that, that would be most excellent. Basically I'm trying to
>determine what patterns (grouped by testsets) occur the most. And the value
>never repeats within a testset.
Hi D,
If I understand you correctly, you need a technique known as relational
division. Try if the following code helps:
create table test (testset int, testnumber int, value int,
primary key(testset, testnumber),
unique(testset, value))
insert into test values(1,1,1)
insert into test values(1,2,2)
insert into test values(1,3,3)
insert into test values(1,4,4)
insert into test values(1,5,5)
insert into test values(2,1,1)
insert into test values(2,2,2)
insert into test values(2,3,7)
insert into test values(2,4,8)
insert into test values(2,5,9)
insert into test values(3,1,2)
insert into test values(3,2,3)
insert into test values(3,3,6)
insert into test values(3,4,7)
insert into test values(3,5,8)
go
create table wanted (value int not null primary key)
insert into wanted values (1)
insert into wanted values (2)
go
SELECT test.testset
FROM test
INNER JOIN wanted
ON test.value = wanted.value
GROUP BY test.testset
HAVING COUNT(*) = (SELECT COUNT(*) FROM wanted)
go
drop table wanted
drop table test
go
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thanks. It does work but I'm wondering how I can make it more flexible in
terms of the wanted values. Let's say I have a couple hundred different
combinations of 3 numbers, I wonder how would I cycle through that.
Another way I just figured out is to use a self join
select distinct a.testset from test a, test b where a.testset = b.testset
and a.value = 1 and b.value = 2
Thanks for your help
> Hi D,
> If I understand you correctly, you need a technique known as relational
> division. Try if the following code helps:
> create table test (testset int, testnumber int, value int,
> primary key(testset, testnumber),
> unique(testset, value))
> insert into test values(1,1,1)
> insert into test values(1,2,2)
> insert into test values(1,3,3)
> insert into test values(1,4,4)
> insert into test values(1,5,5)
> insert into test values(2,1,1)
> insert into test values(2,2,2)
> insert into test values(2,3,7)
> insert into test values(2,4,8)
> insert into test values(2,5,9)
> insert into test values(3,1,2)
> insert into test values(3,2,3)
> insert into test values(3,3,6)
> insert into test values(3,4,7)
> insert into test values(3,5,8)
> go
> create table wanted (value int not null primary key)
> insert into wanted values (1)
> insert into wanted values (2)
> go
> SELECT test.testset
> FROM test
> INNER JOIN wanted
> ON test.value = wanted.value
> GROUP BY test.testset
> HAVING COUNT(*) = (SELECT COUNT(*) FROM wanted)
> go
> drop table wanted
> drop table test
> go
>
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)|||On Sun, 24 Apr 2005 07:29:09 -0400, D wrote:
>Thanks. It does work but I'm wondering how I can make it more flexible in
>terms of the wanted values. Let's say I have a couple hundred different
>combinations of 3 numbers, I wonder how would I cycle through that.
Hi D,
All the more reason to store the values you want to find in a table. Only,
you'll have to add another column, so you can store different combinations
at once. And you'll have to adapt the query, of course. See if the code
below helps.
create table test (testset int, testnumber int, value int,
primary key(testset, testnumber),
unique(testset, value))
insert into test values(1,1,1)
insert into test values(1,2,2)
insert into test values(1,3,3)
insert into test values(1,4,4)
insert into test values(1,5,5)
insert into test values(2,1,1)
insert into test values(2,2,2)
insert into test values(2,3,7)
insert into test values(2,4,8)
insert into test values(2,5,9)
insert into test values(3,1,2)
insert into test values(3,2,3)
insert into test values(3,3,6)
insert into test values(3,4,7)
insert into test values(3,5,8)
go
create table wanted (combination int not null,
value int not null,
primary key(combination, value))
insert into wanted (combination, value)
-- Testset 1: values 1 and 2
select 1, 1 union all
select 1, 2 union all
-- Testset 2: values 1 and 3
select 2, 1 union all
select 2, 3 union all
-- Testset 3: values 1, 2, and 3
select 3, 1 union all
select 3, 2 union all
select 3, 3
go
SELECT w.combination, t.testset
FROM test AS t
INNER JOIN wanted AS w
ON t.value = w.value
GROUP BY w.combination, t.testset
HAVING COUNT(*) = (SELECT COUNT(*)
FROM wanted AS w2
WHERE w2.combination = w.combination)
go
drop table wanted
drop table test
go
>Another way I just figured out is to use a self join
>select distinct a.testset from test a, test b where a.testset = b.testset
>and a.value = 1 and b.value = 2
Yeah, but the problem with that approach is that you have to increase the
number of joins as the number of values you want to find goes up. Imagine
what the query would look like if you have to find all testsets where all
of the values 1 through 10 are present - you'd have to join the same table
tenfold!!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||thanks ALOT!! This works really good.
"Hugo Kornelis" <hugo@.pe_NO_rFact.in_SPAM_fo> wrote in message
news:vv1n61pnl9tr20phih2mg4n0ps4ml16226@.4ax.com...
> On Sun, 24 Apr 2005 07:29:09 -0400, D wrote:
>>Thanks. It does work but I'm wondering how I can make it more flexible in
>>terms of the wanted values. Let's say I have a couple hundred different
>>combinations of 3 numbers, I wonder how would I cycle through that.
> Hi D,
> All the more reason to store the values you want to find in a table. Only,
> you'll have to add another column, so you can store different combinations
> at once. And you'll have to adapt the query, of course. See if the code
> below helps.
> create table test (testset int, testnumber int, value int,
> primary key(testset, testnumber),
> unique(testset, value))
> insert into test values(1,1,1)
> insert into test values(1,2,2)
> insert into test values(1,3,3)
> insert into test values(1,4,4)
> insert into test values(1,5,5)
> insert into test values(2,1,1)
> insert into test values(2,2,2)
> insert into test values(2,3,7)
> insert into test values(2,4,8)
> insert into test values(2,5,9)
> insert into test values(3,1,2)
> insert into test values(3,2,3)
> insert into test values(3,3,6)
> insert into test values(3,4,7)
> insert into test values(3,5,8)
> go
> create table wanted (combination int not null,
> value int not null,
> primary key(combination, value))
> insert into wanted (combination, value)
> -- Testset 1: values 1 and 2
> select 1, 1 union all
> select 1, 2 union all
> -- Testset 2: values 1 and 3
> select 2, 1 union all
> select 2, 3 union all
> -- Testset 3: values 1, 2, and 3
> select 3, 1 union all
> select 3, 2 union all
> select 3, 3
> go
> SELECT w.combination, t.testset
> FROM test AS t
> INNER JOIN wanted AS w
> ON t.value = w.value
> GROUP BY w.combination, t.testset
> HAVING COUNT(*) = (SELECT COUNT(*)
> FROM wanted AS w2
> WHERE w2.combination = w.combination)
> go
> drop table wanted
> drop table test
> go
>
>>Another way I just figured out is to use a self join
>>select distinct a.testset from test a, test b where a.testset = b.testset
>>and a.value = 1 and b.value = 2
> Yeah, but the problem with that approach is that you have to increase the
> number of joins as the number of values you want to find goes up. Imagine
> what the query would look like if you have to find all testsets where all
> of the values 1 through 10 are present - you'd have to join the same table
> tenfold!!
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)sql

Friday, March 30, 2012

Grouping Row Border

Grouping records

Grouping question

Wednesday, March 28, 2012

grouping problem...

grouping problem...

Grouping on part of a composite key in a dimension on one axis

Grouping on part of a composite key in a dimension on one axis

grouping on date without time

Monday, March 26, 2012

Grouping Data with Weekly break out?

Grouping Data and Selecting highest date

Grouping consecutive rows of data

Grouping by week starting on Sunday

Friday, March 23, 2012

Grouping by datecolumn

Grouping by Age

grouping and concatenating

grouping / count question

Firehose mode

Blog Archive

About Me