Island and RunningTotal Sample


 create table test ([week] int, Barcode bigint, Product varchar(50))
insert into test values(1,123456789012,'Prod A')
,(2,123456789012,'Prod A')
,(3,123456789012,'Prod A')
,(4,123456789012,'Prod B')
,(5,123456789012,'Prod A')
,(6,123456789012,'Prod X')
,(7,123456789012,'Prod Y')
,(8,123456789012,'Prod Y')
,(9,123456789012,'Prod A')
,(10,123456789012,'Prod A')

;with mycte as (
select [week],Barcode,Product, row_number() Over(Order by [week]) rn,
row_number() Over(Order by [week]) - row_number() Over(Partition By Product Order by [week]) delta

from test)

,mycte1 as (select [week],Barcode,Product, rn
, Case WHEN rank() Over(Partition by delta Order By [week])>1 Then 0 Else 1 ENd GenCode from mycte )

, mycte2 AS
(
 SELECT [week],Barcode,rn,Product, GenCode
 FROM mycte1
 WHERE rn = 1
 UNION ALL
 SELECT n.[week],n.Barcode,n.rn,n.Product, m.GenCode + n.GenCode
 FROM mycte2 m INNER JOIN mycte1 n
 ON n.rn = m.rn + 1
)
SELECT [week],Barcode, Product, GenCode FROM mycte2
 ORDER BY [week]

OPTION (MAXRECURSION 10000);
drop table test

/*
week Barcode Product GenCode
1 123456789012 Prod A 1
2 123456789012 Prod A 1
3 123456789012 Prod A 1
4 123456789012 Prod B 2
5 123456789012 Prod A 3
6 123456789012 Prod X 4
7 123456789012 Prod Y 5
8 123456789012 Prod Y 5
9 123456789012 Prod A 6
10 123456789012 Prod A 6

*/
 

BCP with Date (Datetime) SQL Server 2008

When you import data from text file with date value to a target table with column data type DATE, you need to make sure the data format in your text file is YYYY-MM-DD. Another walk around is to modify your target table column as a datetime column to avoid conversion error generated from running bcp command.

Check more from this thread:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/f587a76c-65e4-46f0-8c91-a63a6a9947d9/bcp-import-date-into-field-type-of-date-fails?forum=transactsql


Relational division — some solutions

Here is an example question:

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/26868ec7-54ad-42e9-9301-01b1337eb27a/ids-with-3-statuses?forum=transactsql


 DECLARE @tab TABLE
 (
 ID VARCHAR(3) ,
 OrderStatus VARCHAR(50)
 );

INSERT INTO @tab
VALUES ( 'ID1', 'Purchase Request' ),
 ( 'ID1', 'Pending' ),
 ( 'ID1', 'Processed' ),
 ( 'ID1', 'Processed' ),
 ( 'ID2', 'Pending' ),
 ( 'ID2', 'Pending' ),
 ( 'ID2', 'Pending' ),
 ( 'ID4', 'Purchase Request' ),
 ( 'ID3', 'Processed' );

;with mycte as (
SELECT ID, OrderStatus=CASE WHEN OrderStatus='Pending' THEN 1
WHEN OrderStatus='Processed' THEN 2
WHEN OrderStatus='Purchase Request' THEN 4
END
FROM @tab
GROUP By ID, OrderStatus)

SELECT ID from mycte
GROUP By ID
Having(SUM(OrderStatus)=7)

 
 

 


A useful link for calculate running total / running balance

Aaron Bertrand has summarized different ways to  calculate running total:

http://stackoverflow.com/questions/11310877/calculate-running-total-running-balance


Reporting Services (SSRS) Multi Value Parameter –2

Edit:

Actually, you don’t need any functions to make this work. Go ahead to set the parameter with Allow Multiple Value.

It works for both integer and string type parameter.

—————————————————————————————————–

You need to set @state parameter to allow multiple values;

Try these built-in functions


=Split(Join(Parameters!state.Value,","),",")

and your query looks like:

…WHERE state in (@state)
Here is the .rdl for a sample report:

<?xml version="1.0" encoding="utf-8"?>
<Report xmlns="http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition" xmlns:rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
  <Body>
    <ReportItems>
      <Textbox Name="textbox1">
        <CanGrow>true</CanGrow>
        <KeepTogether>true</KeepTogether>
        <Paragraphs>
          <Paragraph>
            <TextRuns>
              <TextRun>
                <Value>Test Project</Value>
                <Style>
                  <FontFamily>Tahoma</FontFamily>
                  <FontSize>20pt</FontSize>
                  <FontWeight>Bold</FontWeight>
                  <Color>SteelBlue</Color>
                </Style>
              </TextRun>
            </TextRuns>
            <Style />
          </Paragraph>
        </Paragraphs>
        <rd:DefaultName>textbox1</rd:DefaultName>
        <Height>0.36in</Height>
        <Width>10in</Width>
        <Style>
          <PaddingLeft>2pt</PaddingLeft>
          <PaddingRight>2pt</PaddingRight>
          <PaddingTop>2pt</PaddingTop>
          <PaddingBottom>2pt</PaddingBottom>
        </Style>
      </Textbox>
      <Tablix Name="Tablix1">
        <TablixBody>
          <TablixColumns>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
            <TablixColumn>
              <Width>1in</Width>
            </TablixColumn>
          </TablixColumns>
          <TablixRows>
            <TablixRow>
              <Height>0.25in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox3">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>stateid</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox3</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox5">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>name</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox5</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="Textbox7">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>state</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>Textbox7</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
              </TablixCells>
            </TablixRow>
            <TablixRow>
              <Height>0.25in</Height>
              <TablixCells>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="stateid">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!stateid.Value</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>stateid</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="name">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!name.Value</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>name</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
                <TablixCell>
                  <CellContents>
                    <Textbox Name="state">
                      <CanGrow>true</CanGrow>
                      <KeepTogether>true</KeepTogether>
                      <Paragraphs>
                        <Paragraph>
                          <TextRuns>
                            <TextRun>
                              <Value>=Fields!state.Value</Value>
                              <Style />
                            </TextRun>
                          </TextRuns>
                          <Style />
                        </Paragraph>
                      </Paragraphs>
                      <rd:DefaultName>state</rd:DefaultName>
                      <Style>
                        <Border>
                          <Color>LightGrey</Color>
                          <Style>Solid</Style>
                        </Border>
                        <PaddingLeft>2pt</PaddingLeft>
                        <PaddingRight>2pt</PaddingRight>
                        <PaddingTop>2pt</PaddingTop>
                        <PaddingBottom>2pt</PaddingBottom>
                      </Style>
                    </Textbox>
                  </CellContents>
                </TablixCell>
              </TablixCells>
            </TablixRow>
          </TablixRows>
        </TablixBody>
        <TablixColumnHierarchy>
          <TablixMembers>
            <TablixMember />
            <TablixMember />
            <TablixMember />
          </TablixMembers>
        </TablixColumnHierarchy>
        <TablixRowHierarchy>
          <TablixMembers>
            <TablixMember>
              <KeepWithGroup>After</KeepWithGroup>
            </TablixMember>
            <TablixMember>
              <Group Name="Details" />
            </TablixMember>
          </TablixMembers>
        </TablixRowHierarchy>
        <DataSetName>DataSet2</DataSetName>
        <Top>0.91542in</Top>
        <Left>0.56125in</Left>
        <Height>0.5in</Height>
        <Width>3in</Width>
        <ZIndex>1</ZIndex>
        <Style>
          <Border>
            <Style>None</Style>
          </Border>
        </Style>
      </Tablix>
    </ReportItems>
    <Height>2.48306in</Height>
    <Style />
  </Body>
  <Width>19in</Width>
  <Page>
    <LeftMargin>1in</LeftMargin>
    <RightMargin>1in</RightMargin>
    <TopMargin>1in</TopMargin>
    <BottomMargin>1in</BottomMargin>
    <Style />
  </Page>
  <AutoRefresh>0</AutoRefresh>
  <DataSources>
    <DataSource Name="myDS">
      <DataSourceReference>myDS</DataSourceReference>
      <rd:SecurityType>None</rd:SecurityType>
      <rd:DataSourceID>f30641da-683e-4eff-96f5-72d180c3dfe3</rd:DataSourceID>
    </DataSource>
  </DataSources>
  <DataSets>
    <DataSet Name="DataSet2">
      <Query>
        <DataSourceName>myDS</DataSourceName>
        <QueryParameters>
          <QueryParameter Name="@state">
            <Value>=Split(Join(Parameters!state.Value,","),",")</Value>
          </QueryParameter>
        </QueryParameters>
        <CommandText>SELECT [stateid],[name],[state] FROM [dbo].[states] WHERE [state] IN (@state)</CommandText>
      </Query>
      <Fields>
        <Field Name="stateid">
          <DataField>stateid</DataField>
          <rd:TypeName>System.Int32</rd:TypeName>
        </Field>
        <Field Name="name">
          <DataField>name</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="state">
          <DataField>state</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
    <DataSet Name="DataSet3">
      <Query>
        <DataSourceName>myDS</DataSourceName>
        <CommandText>SELECT [name],  state  FROM  [dbo].[states]
 </CommandText>
      </Query>
      <Fields>
        <Field Name="name">
          <DataField>name</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
        <Field Name="state">
          <DataField>state</DataField>
          <rd:TypeName>System.String</rd:TypeName>
        </Field>
      </Fields>
    </DataSet>
  </DataSets>
  <ReportParameters>
    <ReportParameter Name="state">
      <DataType>String</DataType>
      <Prompt>state</Prompt>
      <ValidValues>
        <DataSetReference>
          <DataSetName>DataSet3</DataSetName>
          <ValueField>state</ValueField>
          <LabelField>name</LabelField>
        </DataSetReference>
      </ValidValues>
      <MultiValue>true</MultiValue>
    </ReportParameter>
  </ReportParameters>
  <Language>en-US</Language>
  <ConsumeContainerWhitespace>true</ConsumeContainerWhitespace>
  <rd:ReportUnitType>Inch</rd:ReportUnitType>
  <rd:ReportID>c01d05e5-f0de-456c-aaba-651a5200f920</rd:ReportID>
</Report>
 

Samples For Rows to Column

 
 ----Sample1

create table test ( id int identity(1,1),  val varchar(50))
Insert into test values ('A'),('B'),('C') ,('E') ,('D')
SELECT distinct (SELECT  '' + val FROM test FOR XML PATH('')) AS vals

FROM test t
drop table test   



--Sample2

create table test (CATEGORY char(1),   EMAIL varchar(50))
Insert into test values 
('A','Smith@somewhere.com')
,('C','Jones@somewhere.com')
,('A','Bob@somewhere.com')
 ,('C','John@somewhere.com')
 ,('C','Tom@somewhere.com')


SELECT CATEGORY, STUFF( (SELECT  ';' + EMAIL FROM test AS t1
               WHERE      t1.CATEGORY = t1.CATEGORY FOR XML PATH('')), 1, 1, '') AS Emails

FROM test t Where CATEGORY='C'
GROUP BY CATEGORY 


drop table test       
 

Sequencing Date Ranges

The original question:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/980b137f-cf5d-4a91-8273-2b61aba79b0e/sequencing-date-ranges?forum=transactsql#fb3aafdc-a15d-4322-b443-bc2e3707ba18

Here is my answer:

 CREATE TABLE #DateRanges 
( 
PatientID integer NULL, 
StartDate date NULL, 
EndDate date NULL 
);

INSERT INTO #DateRanges (PatientID, StartDate, EndDate)
VALUES
( 1, '08-29-2012', '10-03-2012'),
( 1, '10-31-2012', '11-20-2012'),
( 1, '07-04-2013', '07-17-2013'),
( 1, '07-05-2013', '07-05-2013'),
( 2, '12-15-2008', '01-14-2009'),
( 2, '01-15-2009', '01-21-2009'),
( 2, '01-22-2009', '02-10-2009'),
( 2, '04-04-2011', '04-08-2011'),
( 2, '02-02-2012', '02-15-2012'),
( 2, '12-19-2012', '12-28-2012'),
( 2, '05-09-2013', '05-09-2013'),
( 2, '05-13-2013', '05-20-2013'),
( 3, '09-26-2001', '10-16-2001'),
( 3, '07-01-2002', '07-29-2002'),
( 3, '06-30-2003', '07-25-2003'),
( 3, '09-15-2003', '09-15-2003'),
( 3, '12-10-2003', '12-10-2003'),
( 4, '07-27-2000', '07-27-2000'),
( 4, '11-07-2000', '11-16-2000'),
( 4, '11-07-2000', '11-27-2000'),
( 4, '04-19-2001', '05-01-2001'),
( 4, '04-20-2001', '04-20-2001'),
( 4, '09-13-2001', '09-13-2001'),
( 4, '09-19-2001', '09-19-2001'),
( 4, '10-05-2001', '10-05-2001'),
( 4, '09-13-2002', '09-24-2002')

;with mycte as (SELECT

PatientID, StartDate, EndDate, TreatmentSequenceNumber = NULL
,row_number() Over(Partition by PatientID Order By StartDate) rn

FROM #DateRanges)
,mycte1 as (
SELECT m1.PatientID, m1.StartDate, m1.EndDate,m1.rn, CASE WHEN Datediff(day,m2.EndDate,m1.StartDate)<=5 THEN 0 Else 1 End cnt  
from mycte m1 LEFT JOIN mycte m2 On m1.PatientID=m2.PatientID AND m1.rn=m2.rn+1
)

Select m1.PatientID, m1.StartDate, m1.EndDate, sum(m2.cnt) TreatmentSequenceNumber
 from mycte1 m1 inner join mycte1 m2 on m1.PatientID=m2.PatientID and m1.rn>=m2.rn

GROUP BY m1.PatientID, m1.StartDate, m1.EndDate
ORDER BY m1.PatientID,m1.StartDate


DROP TABLE #DateRanges