Island and RunningTotal Sample

Posted: January 31, 2014 | Author: Jingyang Li | Filed under: SQL Server 2008 | Leave a comment


 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

Posted: January 30, 2014 | Author: Jingyang Li | Filed under: SQL Server 2008, SQL Server 2012 | Leave a comment

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

Posted: January 27, 2014 | Author: Jingyang Li | Filed under: SQL Server 2008 | Leave a comment

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)

&nbsp;

A useful link for calculate running total / running balance

Posted: January 24, 2014 | Author: Jingyang Li | Filed under: Uncategorized | Leave a comment

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

Posted: January 16, 2014 | Author: Jingyang Li | Filed under: SQL Server 2008, SQL Server Reporting Services (SSRS) | Leave a comment

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

Posted: January 16, 2014 | Author: Jingyang Li | Filed under: SQL Server, SQL Server 2008 | Leave a comment

 
 ----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

Posted: January 16, 2014 | Author: Jingyang Li | Filed under: SQL Server, SQL Server 2008 | Leave a comment

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

Combining Rows Based on Datetime Columns

Posted: January 16, 2014 | Author: Jingyang Li | Filed under: SQL Server, SQL Server 2008 | Leave a comment

TThe question came from:
http://social.msdn.microsoft.com/Forums/sqlserver/en-US/e4e8a618-dcff-4efe-b929-56f374c3d6e9/sorting-order?forum=transactsql#255b0847-c29f-47a0-ab54-cf22b1aff2c4
I didn’t see any correct answer there so I came up with one. Here you are:

 
 
 create table tbltmp (id int, col1 varchar(10), dt1 date,dt2 date)
INSERT INTO tbltmp
 VALUES
 (769,'BBB','2011-06-30','2011-09-30'),
 (769,'BBB','2011-12-31','2012-03-31'),
 (769,'BBB','2012-03-31','2012-06-30'),
 (769,'BBB', '2012-06-30', '2013-05-04'),
 (769,'BBB', '2014-01-01', '2014-03-07'),
 (769, 'BBB', '2014-03-07','2014-03-08'),
 (769,'BBB','2014-04-01','2014-04-10')

 
/*
EXPECTED OUTPUT:
 769	BBB	2011-06-30	2011-09-30
769	BBB	2011-12-31	2013-05-04
769	BBB	2014-01-01	2014-03-08
769	BBB	2014-04-01	2014-04-10

*/
 --Select * from tbltmp

 --UNPIVOT the source two date columns into one combined date column
 ;with mycte as
 (select id, col1, dt, count(*) Over(partition by id, col1, dt ) cnt from tbltmp 
 cross apply (values (dt1),(dt2))  d(dt)
 )

 --Select * from mycte 
 --Order by id, col1, dt


 --Get delta value with help of row_number function for different groups and retrieve only dates witout duplication
 -- uncomment the last line to see the values from each
 ,mycte2 as
 (
 select *
 , row_number() Over(Partition by id order by dt) - row_number() Over(Partition by id order by dt)/2 rn
 , row_number() Over(Partition by id order by dt)%2 grp 
 --,row_number() Over(Partition by id order by dt) e1, row_number() Over(Partition by id order by dt)/2 as e2
 
from mycte 
Where cnt=1)

 
 --Select * from mycte2 
 --Order by id, col1, dt


 --Finally PIVOT to connect startdate and enddate
 Select id, col1, [1] as dt1, [0] as dt2 
 From mycte2 Pivot(max(dt) For grp in ([1],[0])) pvt

drop table tbltmp

A Merge Code Sample and A Link for Issues

Posted: January 9, 2014 | Author: Jingyang Li | Filed under: SQL Server 2008, SQL Server 2012 | Leave a comment

 
If exists(Select object_id('dbo.mytest','U')) 
drop table dbo.mytest;
If exists(Select object_id('dbo.mylog','U')) 
drop table dbo.mylog;

CREATE TABLE dbo.mytest(id INT);
CREATE TABLE dbo.myLog(action varchar(10), idnew int, idold int);
GO
INSERT dbo.mytest VALUES(1),(5);
GO

select * from mytest

--Check Requests Log
INSERT INTO  dbo.mylog (action,  idNew, idOld)
SELECT  action,   idNew, idOld
FROM
( 
MERGE dbo.mytest WITH (HOLDLOCK) AS Target
USING (VALUES(1),(2),(3)) AS Source(id)
ON Target.id = Source.id
WHEN MATCHED THEN UPDATE SET Target.id = Source.id
WHEN NOT MATCHED THEN INSERT(id) VALUES(Source.id)
WHEN NOT MATCHED BY SOURCE THEN DELETE

 OUTPUT $action
, inserted.id 
, deleted.id
 )
AS Changes (Action, idNew, idOld)  ;

select * from mylog
select * from mytest

You can find a good article from Arron Bertrand about some issues he found:
http://www.mssqltips.com/sqlservertip/3074/use-caution-with-sql-servers-merge-statement/

Order Rows According to Score Column– A Solution

Posted: January 2, 2014 | Author: Jingyang Li | Filed under: SQL Server 2008 | Leave a comment


Create table test (Id int, Name varchar(50), Score int)
  Insert into test values
  (1,char(65),2),(2,char(66),1), (3,char(67),1),(4,char(68),1), (5,char(69),2),(6,char(70),2)
  , (7,char(71),2),(8,char(72),1),(9,char(73),1),(10,char(74),1), (11,char(75),2)
  ,(12,char(76),2),(13,char(77),2),(14,char(78),1),(15,char(79),1)
 
;with mycte as
(select id, Name, score,row_number() Over(Partition by score Order By id) rn  from test)

,mycte1 as (Select id, Name, score, row_number() Over(Order by  rn,score ) rn2 from mycte)

,
myfinal
as
(
    select id, Name, rn2, score, score as running_total
    from mycte1
    where rn2 = 1
    union all
    select  m.id, m.Name,m.rn2, m.score, m.score + r.running_total as running_total
    from myfinal as r inner join mycte1 as m on m.rn2 = r.rn2 + 1
)
SELECT id, Name, score,  CASE WHEN running_total%6=0 Then (running_total)/6 -1 Else (running_total)/6 End  as Sortord from myfinal m
Order By Sortord,rn2
--option (maxrecursion 0)

drop table test
--http://forums.asp.net/t/1959158.aspx?arrange+records+according+score

Jingyang Li

SQL Server

Island and RunningTotal Sample

BCP with Date (Datetime) SQL Server 2008

Relational division — some solutions

A useful link for calculate running total / running balance

Reporting Services (SSRS) Multi Value Parameter –2

Samples For Rows to Column

Sequencing Date Ranges

Combining Rows Based on Datetime Columns

A Merge Code Sample and A Link for Issues

Order Rows According to Score Column– A Solution

Recent Posts

Archives

Categories

Meta