Split Time Duration Into Hourly Intervals (T-SQL)

Posted: April 26, 2014 | Author: | Filed under: SQL Server | Leave a comment

http://social.msdn.microsoft.com/Forums/en-US/1b96351f-d4fb-4cf4-ba24-07b2fb5f5007/cartesian-time-data-across-multiple-hourly-rows?forum=transactsql

 
  create table test (EventTime    datetime,     EventLengthSeconds int)
  insert into test values ('2014-04-25 13:31:16.857', 6657)

  ;with mycte as 
  (
  select EventTime as EventTime1, number ,EventLengthSeconds
  ,datediff(second,Eventtime,DATEADD(hour, DATEDIFF(hour,0,EventTime)+number+1,0))  as EventLengthSeconds1
  ,DATEADD(hh, DATEDIFF(hh,0,EventTime)+number+1,0) as EventTime2 
  ,EventLengthSeconds -(number-1)*3600-datediff(second,Eventtime,DATEADD(hour, DATEDIFF(hour,0,EventTime)+1,0)) as EventLengthSeconds2
   FROM [master].[dbo].[spt_values],test
  where type='p'  and (EventLengthSeconds*1./3600)+1>=number
  )

  select  
  case when number=0 then EventTime1 else EventTime2 end EventTime,
  case 
  when number=0 then EventLengthSeconds1
  when EventLengthSeconds2>=3600 then 3600
  when EventLengthSeconds2<3600 then EventLengthSeconds2
  end as EventLengthSeconds

  from mycte



--Clean up
DROP TABLE test

Find Next Anniversary Date (T-SQL)

Posted: April 25, 2014 | Author: | Filed under: SQL Server | Leave a comment

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/9dceaf0b-c939-43fa-bf60-b2377a096d4a/anniversary-date-calculation?forum=transactsql#fa6c226a-ef27-4c5e-8d5f-7eaac4e4dec3

 
 create table test1
(InvestorID int ,pension_start_date datetime)
INSERT INTO test1 (InvestorID,pension_start_date) values (1,'01/01/14'), (2,'05/05/13'),(3,'01/31/13'),(4,'10/25/12'),(5,'03/25/10')

select InvestorID, pension_start_date, CASE WHEN
 (DATEADD(year,DATEDIFF(year, pension_start_date  ,current_timestamp) , pension_start_date) > current_timestamp)
THEN Dateadd(Year,DATEDIFF(year, pension_start_date  ,current_timestamp) , pension_start_date)
ELSE  Dateadd(Year,DATEDIFF(year, pension_start_date  ,current_timestamp)+1 , pension_start_date) END [Anniversary Date] 
FROM test1

--Clean up
DROP TABLE test1

How to SUM odd and even digits ina big number with T-SQL

Posted: April 17, 2014 | Author: | Filed under: SQL Server 2008 | Leave a comment

http://social.msdn.microsoft.com/Forums/en-US/958861e1-786b-4b51-8915-5ef33dc5657b/how-to-calculate-odd-and-even-place-digits-in-sql?forum=transactsql

 create table test (number varchar(50))
Insert into test values('196421744643'),('12121212121212')
select number,  SUM(Case WHEN num%2 =1 then Cast(substring(number,num,1) as int) end) OddSum,
SUM(Case WHEN num%2 =0 then Cast(substring(number,num,1) as int) end) EvenSum
 from test
 cross apply (Values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15)) d(num) 
 WHERE substring(number,num,1)<>''
 Group by number
 
 drop table test

Script DBMail Profiles

Posted: April 14, 2014 | Author: | Filed under: SQL Server 2005, SQL Server 2008 | Leave a comment

User Lowell posted a script to script out DBMail’s profile at this thread:
http://www.sqlservercentral.com/Forums/Topic982618-391-1.aspx

I include the script here for future reference:

USE msdb
GO

Declare @TheResults varchar(max),
        @vbCrLf CHAR(2)
SET @vbCrLf = CHAR(13) + CHAR(10)        
SET @TheResults = '
use master
go
sp_configure ''show advanced options'',1
go
reconfigure with override
go
sp_configure ''Database Mail XPs'',1
--go
--sp_configure ''SQL Mail XPs'',0
go
reconfigure
go
'
SELECT @TheResults = @TheResults  + '
--#################################################################################################
-- BEGIN Mail Settings ' + p.name + '
--#################################################################################################
IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''') 
  BEGIN
    --CREATE Profile [' + p.name + ']
    EXECUTE msdb.dbo.sysmail_add_profile_sp
      @profile_name = ''' + p.name + ''',
      @description  = ''' + ISNULL(p.description,'') + ''';
  END --IF EXISTS profile
  '
  +
  '
  IF NOT EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = ''' + a.name + ''')
  BEGIN
    --CREATE Account [' + a.name + ']
    EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name            = ' + CASE WHEN a.name                IS NULL THEN ' NULL ' ELSE + '''' + a.name                  + '''' END + ',
    @email_address           = ' + CASE WHEN a.email_address       IS NULL THEN ' NULL ' ELSE + '''' + a.email_address         + '''' END + ',
    @display_name            = ' + CASE WHEN a.display_name        IS NULL THEN ' NULL ' ELSE + '''' + a.display_name          + '''' END + ',
    @replyto_address         = ' + CASE WHEN a.replyto_address     IS NULL THEN ' NULL ' ELSE + '''' + a.replyto_address       + '''' END + ',
    @description             = ' + CASE WHEN a.description         IS NULL THEN ' NULL ' ELSE + '''' + a.description           + '''' END + ',
    @mailserver_name         = ' + CASE WHEN s.servername          IS NULL THEN ' NULL ' ELSE + '''' + s.servername            + '''' END + ',
    @mailserver_type         = ' + CASE WHEN s.servertype          IS NULL THEN ' NULL ' ELSE + '''' + s.servertype            + '''' END + ',
    @port                    = ' + CASE WHEN s.port                IS NULL THEN ' NULL ' ELSE + '''' + CONVERT(VARCHAR,s.port) + '''' END + ',
    @username                = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''' + c.credential_identity   + '''' END + ',
    @password                = ' + CASE WHEN c.credential_identity IS NULL THEN ' NULL ' ELSE + '''NotTheRealPassword''' END + ', 
    @use_default_credentials = ' + CASE WHEN s.use_default_credentials = 1 THEN ' 1 ' ELSE ' 0 ' END + ',
    @enable_ssl              = ' + CASE WHEN s.enable_ssl = 1              THEN ' 1 ' ELSE ' 0 ' END + ';
  END --IF EXISTS  account
  '
  + '
IF NOT EXISTS(SELECT *
              FROM msdb.dbo.sysmail_profileaccount pa
                INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
                INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id  
              WHERE p.name = ''' + p.name + '''
                AND a.name = ''' + a.name + ''') 
  BEGIN
    -- Associate Account [' + a.name + '] to Profile [' + p.name + ']
    EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
      @profile_name = ''' + p.name + ''',
      @account_name = ''' + a.name + ''',
      @sequence_number = ' + CONVERT(VARCHAR,pa.sequence_number) + ' ;
  END --IF EXISTS associate accounts to profiles
--#################################################################################################
-- Drop Settings For ' + p.name + '
--#################################################################################################
/*
IF EXISTS(SELECT *
            FROM msdb.dbo.sysmail_profileaccount pa
              INNER JOIN msdb.dbo.sysmail_profile p ON pa.profile_id = p.profile_id
              INNER JOIN msdb.dbo.sysmail_account a ON pa.account_id = a.account_id  
            WHERE p.name = ''' + p.name + '''
              AND a.name = ''' + a.name + ''')
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp @profile_name = ''' + p.name + ''',@account_name = ''' + a.name + '''
  END 
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_account WHERE  name = ''' + a.name + ''')
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_account_sp @account_name = ''' + a.name + '''
  END
IF EXISTS(SELECT * FROM msdb.dbo.sysmail_profile WHERE  name = ''' + p.name + ''') 
  BEGIN
    EXECUTE msdb.dbo.sysmail_delete_profile_sp @profile_name = ''' + p.name + '''
  END
*/
  ' 
FROM msdb.dbo.sysmail_profile p
INNER JOIN msdb.dbo.sysmail_profileaccount pa ON  p.profile_id = pa.profile_id
INNER JOIN msdb.dbo.sysmail_account a         ON pa.account_id = a.account_id 
LEFT OUTER JOIN msdb.dbo.sysmail_server s     ON a.account_id = s.account_id
LEFT OUTER JOIN sys.credentials c    ON s.credential_id = c.credential_id

   ;WITH E01(N) AS (SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1 UNION ALL  
                    SELECT 1 UNION ALL SELECT 1), --         10 or 10E01 rows  
         E02(N) AS (SELECT 1 FROM E01 a, E01 b),  --        100 or 10E02 rows  
         E04(N) AS (SELECT 1 FROM E02 a, E02 b),  --     10,000 or 10E04 rows  
         E08(N) AS (SELECT 1 FROM E04 a, E04 b),  --100,000,000 or 10E08 rows  
         --E16(N) AS (SELECT 1 FROM E08 a, E08 b),  --10E16 or more rows than you'll EVER need,  
         Tally(N) AS (SELECT ROW_NUMBER() OVER (ORDER BY N) FROM E08),  
       ItemSplit(  
                 ItemOrder,  
                 Item  
                ) as (  
                      SELECT N,  
                        SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf),CHARINDEX(@vbCrLf,@vbCrLf + @TheResults + @vbCrLf,N + DATALENGTH(@vbCrLf)) - N - DATALENGTH(@vbCrLf))  
                      FROM Tally  
                      WHERE N < DATALENGTH(@vbCrLf + @TheResults)  
                      --WHERE N < DATALENGTH(@vbCrLf + @INPUT) -- REMOVED added @vbCrLf  
                        AND SUBSTRING(@vbCrLf + @TheResults + @vbCrLf,N,DATALENGTH(@vbCrLf)) = @vbCrLf --Notice how we find the delimiter  
                     )  
  select  
    row_number() over (order by ItemOrder) as ItemID,  
    Item  
  from ItemSplit

Find Broken Loop COnnection from Two Columns

Posted: April 11, 2014 | Author: | Filed under: SQL Server 2005, SQL Server 2008 | Leave a comment 
 
 Create table Sample
 (
  Rec_Id Int Not null,
  Name varchar(30) null,
  ID1 varchar(10) null,
  ID2 varchar(10) null,
 CONSTRAINT [PK_Sample] PRIMARY KEY NONCLUSTERED 
(
  [Rec_Id] ASC
 ))
  

Insert into sample (Rec_ID,Name,ID1,ID2) values ( 1 ,'A', 'A1X31','A2XX1')
  Insert into sample (Rec_ID,Name,ID1,ID2) values ( 2 ,'A', 'A2XX1','B2C45')
  Insert into sample (Rec_ID,Name,ID1,ID2) values ( 3 ,'A', 'B2C45' ,'A1X31')  --> No need to display these 3 records becoz they form one loop( 1st row ID1='A1X31' value matches 3rd row ID2 ='A1X31'value)
 
 Insert into sample (Rec_ID,Name,ID1,ID2) values ( 4 ,'B',    'B1Y35' , 'B2Y15')
 Insert into sample (Rec_ID,Name,ID1,ID2) values (  5 , 'B',  'B2Y15', 'B5Y13')
 Insert into sample (Rec_ID,Name,ID1,ID2) values (  6 , 'B' , 'B5Y13' , null) --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='B1Y35' value doesn't matches 3rd row ID2 = null value)
 
 Insert into sample (Rec_ID,Name,ID1,ID2) values ( 7 ,'C',   'C5Z19' , 'C5B12')
  Insert into sample (Rec_ID,Name,ID1,ID2) values ( 8 ,'C' , 'C5B12' , 'C5JPQ')
  Insert into sample (Rec_ID,Name,ID1,ID2) values ( 9 ,'C' , 'C5JPQ' , 'C3JPQ')
  Insert into sample (Rec_ID,Name,ID1,ID2) values (10 ,'C' , 'C6JPQ' , 'C5Z19')  --> Need to display this 4th record becoz it is broken loop ( 3rd row ID2='C3JPQ' value doesn't matches 4th row ID1 = 'C6JPQ' value)

Insert into sample (Rec_ID,Name,ID1,ID2) values ( 11 ,'D', 'D5Z16' , 'D5Z17')
 Insert into sample (Rec_ID,Name,ID1,ID2) values ( 12 , 'D', 'D5Z17', 'D5Z18')
 Insert into sample (Rec_ID,Name,ID1,ID2) values ( 13 , 'D' , 'D5Z18' , 'D5Z17')  --> Need to display this 3rd record becoz it is broken loop ( 1st row ID1='D5Z16' value doesn't matches 3rd row ID2 =  'D5Z17' value)
 
;with mycte as
(select Rec_Id,name, id1, id2  
,ROW_NUMBER() over(partition by name order by Rec_Id) as rn1 
,ROW_NUMBER() over(partition by name order by Rec_Id DESC) as rn2
 
 
from sample )
, mycte1 as(
select m0.Rec_id,m0.name, m0.id1,  m0.id2 , Coalesce (m1.id2, m2.id2,'') id2_2, m0.rn2
, Sum(Case when m0.id1=Coalesce (m1.id2, m2.id2,'')   Then 0 Else 1 END) Over(Partition By m0.Name)  as cnt
from mycte m0 
Left Join mycte m1 On  m0.name =m1.name and m0.rn1=m1.rn1+1
Left Join mycte m2 On  m0.name =m2.name and m0.rn2=m2.rn1
)

Select Rec_id,name, id1,id2 
from mycte1
WHERE rn2=1 AND cnt>0
Order by Rec_id

/*
Rec_id	name	id1	id2
6	B	B5Y13	NULL
10	C	C6JPQ	C5Z19
13	D	D5Z18	D5Z17

*/



drop table sample

Merge Update Sample

Posted: April 9, 2014 | Author: | Filed under: SQL Server 2008 | Leave a comment 


Create table MinuteCategory([Minute] int, MinuteCategory varchar(50))
Insert into MinuteCategory values (1,'a1'),(33,'a1'),(65,'a1')
Create table MinuteLookup( MinuteCategoryId int identity(1,1), MinuteLow int, MinuteHigh int, MinuteCategory varchar(50))
Insert into MinuteLookup values(1,15,'<15min'),(16,30,'<30 min'),(31,45,'<45 min'),(46,60,'<60 minutes'),(61,75,'<1 hour 15 minutes')


Merge MinuteCategory mc 
Using MinuteLookup ml on mc.[Minute] between ml.MinuteLow and ml.MinuteHigh
When Matched Then
UPDATE 

SET MinuteCategory = ml.MinuteCategoryId;


select * from MinuteCategory mc inner join MinuteLookup ml on mc.MinuteCategory = ml.MinuteCategoryId

----Clean up
drop table MinuteCategory,MinuteLookup

How to select a max row for each group in SQL

Posted: April 7, 2014 | Author: | Filed under: SQL Server 2008 | Leave a comment 

CREATE TABLE [dbo].[test1]([Country] [varchar](4) NULL,  [grpid] [int] NOT NULL,  [Value] [int] NULL,  [Row_num] [int] NULL) 

INSERT [dbo].[test1]  VALUES (N'US', 49707, 604456458, 1)
, (N'US', 909, 604456458, 2),(N'US', 231, 604456457, 3)
, (N'GB', 5086, 497654945, 4), (N'GB', 50147, 405759433, 5),(N'GB', 909, 405759433, 6),(N'GB', 231, 405759433, 7)
, (N'CA', 49707, 353500201, 8), (N'CA', 5086, 353500201, 9),(N'CA', 909, 353500201, 10),(N'CA', 231, 353500201, 11)
, (N'JP', 49707, 198291290, 12),(N'JP', 5086, 198291290, 13), (N'JP', 909, 198291290, 14)
,(N'JP', 231, 198291290, 15)
, (N'MX', 49707, 181884714, 16), (N'MX', 909, 181884714, 17)
,(N'MX', 231, 181884714, 18),(N'BR', 49707, 105147054, 19), (N'BR', 5086, 105147054, 20),(N'BR', 909, 105147054, 21)
, (N'AR', 49707, 94774929, 22), (N'AR', 5086, 94774929, 23), (N'AR', 909, 94774929, 24),(N'AR', 231, 94774929, 25)
, (N'ZA', 49707, 84560514, 26),(N'ZA', 909, 84560514, 27),(N'ZA', 231, 84560514, 28), (N'CN', 49707, 68966682, 29)
,(N'CN', 909, 68966682, 30),(N'CN', 231, 68966682, 31),(N'CB', 49707, 65020665, 32)
, (N'CB', 5086, 65020665, 33), (N'CB', 909, 65020665, 34),(N'CB', 231, 65020665, 35)
,(N'GT', 49707, 42016510, 36), (N'GT', 5086, 42016510, 37),(N'GT', 909, 42016510, 38), (N'CR', 49707, 37116544, 39)
,(N'CR', 5086, 37116544, 40), (N'CR', 909, 37116544, 41),(N'CR', 231, 37116544, 42), (N'SV', 49707, 31103322, 43)
, (N'SV', 5086, 31103322, 44), (N'SV', 909, 31103322, 45),(N'NI', 49707, 17415843, 46),(N'NI', 5086, 17415843, 47)
, (N'NI', 909, 17415843, 48), (N'HN', 49707, 12516273, 49),(N'HN', 5086, 12516273, 50),(N'HN', 909, 12516273, 51)

GO

/*
Country	grpid	Value	Row_num
US	49707	604456458	1
GB	5086	497654945	4
CA	909	353500201	10
JP	231	198291290	15

*/

--source table with a generated row number based on value and grpid
;with mytest as (
select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn   from test1
)

,mycte1 as (select grpid,country,value, Row_num, row_number() over(Order by value DESC, grpid DESC) rn
from mytest a where not exists(select 1  from mytest b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte2 as (select grpid,country,value , Row_num, row_number() over(  Order by value DESC, grpid DESC) rn 
 from mycte1 a where not exists(select 1  from mycte1 b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )  
,mycte3 as (select grpid,country,value , Row_num, row_number() over(  Order by value DESC, grpid DESC) rn from mycte2 a 
where not exists(select 1  from mycte2 b where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) ) 

 --Add more
--,mycte4 as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn
-- from mycte3 a where not exists(select 1  from mycte3 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) ) 
--,mycte5 as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn from mycte4 a 
--where not exists(select 1  from mycte4 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
  

--Final combine all
select grpid,country,value , Row_num from mytest WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte1 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte2 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte3 WHERE rn=1


 ----Add more
--Union ALL
--select grpid,country,value , Row_num from mycte4 WHERE rn=1
--Union ALL
--select grpid,country,value , Row_num from mycte5 WHERE rn=1

 

drop table test1
 


--Early version
CREATE TABLE [dbo].[test1](
       [Country] [varchar](4) NULL,
       [grpid] [int] NOT NULL,
       [Value] [int] NULL,
       [Row_num] [int] NULL
) 

INSERT [dbo].[test1]  VALUES (N'US', 49707, 604456458, 1)
, (N'US', 909, 604456458, 2),(N'US', 231, 604456457, 3)

, (N'GB', 5086, 497654945, 4), (N'GB', 50147, 405759433, 5),(N'GB', 909, 405759433, 6),(N'GB', 231, 405759433, 7)
, (N'CA', 49707, 353500201, 8), (N'CA', 5086, 353500201, 9),(N'CA', 909, 353500201, 10),(N'CA', 231, 353500201, 11)
, (N'JP', 49707, 198291290, 12),(N'JP', 5086, 198291290, 13), (N'JP', 909, 198291290, 14),(N'JP', 231, 198291290, 15)
, (N'MX', 49707, 181884714, 16), (N'MX', 909, 181884714, 17)
,(N'MX', 231, 181884714, 18),(N'BR', 49707, 105147054, 19), (N'BR', 5086, 105147054, 20),(N'BR', 909, 105147054, 21)
, (N'AR', 49707, 94774929, 22), (N'AR', 5086, 94774929, 23), (N'AR', 909, 94774929, 24),(N'AR', 231, 94774929, 25)
, (N'ZA', 49707, 84560514, 26),(N'ZA', 909, 84560514, 27),(N'ZA', 231, 84560514, 28), (N'CN', 49707, 68966682, 29)
,(N'CN', 909, 68966682, 30),(N'CN', 231, 68966682, 31),(N'CB', 49707, 65020665, 32)
, (N'CB', 5086, 65020665, 33), (N'CB', 909, 65020665, 34),(N'CB', 231, 65020665, 35)
,(N'GT', 49707, 42016510, 36), (N'GT', 5086, 42016510, 37),(N'GT', 909, 42016510, 38), (N'CR', 49707, 37116544, 39)
,(N'CR', 5086, 37116544, 40), (N'CR', 909, 37116544, 41),(N'CR', 231, 37116544, 42), (N'SV', 49707, 31103322, 43)
, (N'SV', 5086, 31103322, 44), (N'SV', 909, 31103322, 45),(N'NI', 49707, 17415843, 46),(N'NI', 5086, 17415843, 47)
, (N'NI', 909, 17415843, 48), (N'HN', 49707, 12516273, 49),(N'HN', 5086, 12516273, 50),(N'HN', 909, 12516273, 51)

GO

/*
Country	grpid	Value	Row_num
US	49707	604456458	1
GB	5086	497654945	4
CA	909	353500201	10
JP	231	198291290	15

*/

;with mytest as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn   from test1)

,mycte1 as (select grpid,country,value , Row_num from mytest a where not exists(select 1  from mytest b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte2 as (select grpid,country,value , Row_num, row_number() over(Order by value DESC, grpid DESC) rn   from mycte1 )
,mycte3 as (select grpid,country,value , Row_num from mycte1 a where not exists(select 1  from mycte2 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte4 as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn   from mycte3 )
,mycte5 as (select grpid,country,value , Row_num from mycte3 a where not exists(select 1  from mycte4 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte6 as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn   from mycte5 )
,mycte7 as (select grpid,country,value , Row_num from mycte5 a where not exists(select 1  from mycte6 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
,mycte8 as (select grpid,country,value , Row_num,  row_number() over(  Order by value DESC, grpid DESC) rn  from mycte7 ) 
,mycte9 as (select grpid,country,value , Row_num from mycte7 a where not exists(select 1  from mycte8 b   where (a.Country = b.Country OR a.GrpID = b.grpId) AND rn=1) )
  

--Final combine all
select grpid,country,value , Row_num from mytest WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte2 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte4 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte6 WHERE rn=1
Union ALL
select grpid,country,value , Row_num from mycte8 WHERE rn=1

 

drop table test1

http://social.msdn.microsoft.com/Forums/sqlserver/en-US/dc8f52e2-a03d-4183-bf35-dc2ab14f2632/how-to-select-a-max-row-for-each-group-in-sql?forum=transactsql#5118d2c5-2068-417e-9578-865877eb8925

Four Ways to Insert Value into IDENTITY Column in SQL Server

Posted: April 3, 2014 | Author: | Filed under: SQL Server | Leave a comment

So we can summarize four ways to add values to identity column in SQL Server.

1.SET IDENTITY_INSERT ON;

SET IDENTITY_INSERT [dbo].test1 ON
Go
Insert into test1 (col1) values(11),(12)

SET IDENTITY_INSERT [dbo].test1 OFF
Go

2. Insert into table with default values ;

Insert into test1 default values ;

3. Bcp with -E flag along wiht a format file;

4. Bulk insert with KEPPIDENTITY.



use mytest4
go
 
create table test1 (col1 int primary key identity(1,1))
Go
Insert into test1 default values
go 5

 
SET IDENTITY_INSERT [mytest4].[dbo].test1  ON
Go 
Insert into test1 (col1) values(11),(12)
 
SET IDENTITY_INSERT [mytest4].[dbo].test1  OFF
Go
 
 
--
select * from test1
--drop table test1
  

--Create a format file for bcp and bulk insert
DECLARE @sql VARCHAR(4000) 
SET @sql='bcp [mytest4].[dbo].[test1] format nul -c -x -f  C:\temp\FORMATFIle.xml -T -t\t -S'+ @@servername
exec master..xp_cmdshell @sql
 
  --  -t"|" for pipe delimited
 --   -t\t for tab delimited
 --   -t, for comma delimited 
 
 
----bcp Export
--DECLARE @sql2 VARCHAR(4000) 
--SET @sql2='bcp [mytest4].[dbo].[test1] out "C:\temp\test1.csv" -f "C:\temp\FORMATFIle.xml" -F 1  -T  -t\t  -b 1000 -S'+ @@servername
--exec master..xp_cmdshell @sql2
 
--go
 
 --Remove data from table
 Truncate table test1
 
--Bcp Import
DECLARE @sql3 VARCHAR(4000) 
SET @sql3='bcp [mytest4].[dbo].[test1] IN "C:\temp\test1.csv" -f "C:\temp\FORMATFIle.xml" -F 1 -E -T  -t\t  -b 1000 -S'+ @@servername
exec master..xp_cmdshell @sql3

--/* "-E Specifies that identity value or values in the imported data file are to be used for
--the identity column. If -E is not given, the identity values for this column in the data file being imported are ignored.
--*/
 


select * from test1

 

 
/*
BULK Insert Options
*/
 
  --Remove data from table
 Truncate table test1
----Option 2: Bulk insert with identity insert
 
BULK INSERT [mytest4].[dbo].test1
   FROM 'C:\temp\test1.csv'
   WITH (
      KEEPIDENTITY
     -- ,FORMATFILE='C:\temp\FORMATFIle.xml'
   );
 /*
 KEEPIDENTITY:
 Specifies that identity value or values in the imported data file are to be used for the identity column. 
 */
 
 
select * from test1
 
drop table test1

Sample Code for Bulk Insert and BCP

Posted: April 3, 2014 | Author: | Filed under: SQL Server | Leave a comment 


use mytest4
go

Create Table subtest
(
[ID] [int] IDENTITY(1,1) NOT NULL,
emailID Varchar(50)
)
Go
Insert into subtest values('aaa@abc.com'),('asd@gmail.com')

SET IDENTITY_INSERT [mytest4].[dbo].[subtest]  ON
Go

Insert into [mytest4].[dbo].[subtest] (ID, emailID) values(11,'aaa@abc.com'),(12,'asd@gmail.com')

SET IDENTITY_INSERT [mytest4].[dbo].[subtest]  OFF
Go

go
--select * from Subtest
--drop table subtest
 


DECLARE @sql VARCHAR(4000) 
SET @sql='bcp [mytest4].[dbo].[subtest] format nul -c -x -f  C:\temp\FORMATFIle.xml -T -t\, -S'+ @@servername
exec master..xp_cmdshell @sql

  --    -t"|" for pipe delimited
 --   -t\t for tab delimited
 --   -t, for comma delimited 


--bcp Export
DECLARE @sql2 VARCHAR(4000) 
SET @sql2='bcp [mytest4].[dbo].[subtest] out "C:\temp\subids.csv" -f "C:\temp\FORMATFIle.xml" -F 1  -T  -t\,  -b 1000 -S'+ @@servername
exec master..xp_cmdshell @sql2

go


--Bcp Import
DECLARE @sql3 VARCHAR(4000) 
SET @sql3='bcp [mytest4].[dbo].[subtest] IN "C:\temp\subids.csv" -f "C:\temp\FORMATFIle.xml" -F 1 -E -T  -t\,  -b 1000 -S'+ @@servername
exec master..xp_cmdshell @sql3

/* "-E Specifies that identity value or values in the imported data file are to be used for 
the identity column. If -E is not given, the identity values for this column in the data file being imported are ignored.
*/


--Truncate table Subtest
--go


/*
BULK Insert Options
*/

--Option 1: Bulk insert WITHOUT  identity insert

BULK INSERT [mytest4].[dbo].[subtest]
   FROM 'C:\temp\subids.csv'
   WITH (
          FORMATFILE='C:\temp\FORMATFIle.xml'
   );


--Option 2: Bulk insert with identity insert

BULK INSERT [mytest4].[dbo].[subtest]
   FROM 'C:\temp\subids.csv'
   WITH (
      KEEPIDENTITY,
      FORMATFILE='C:\temp\FORMATFIle.xml'
   );

--Option 3:  Bulk insert with identity insert   No format file
Bulk Insert Subtest
From 'C:\temp\subids.csv'
With 
(
KEEPIDENTITY,
FieldTerminator =','
--FORMATFILE = 'C:\temp\FORMATFIle.xml'
);


select * from Subtest

drop table subtest

Handle #Error on SSRS Report with Missing Date (null value)

Posted: April 2, 2014 | Author: | Filed under: SQL Server Reporting Services (SSRS) | Leave a comment 

--Expression

=IIF(IsNothing(Fields!expireDate.Value), "",FormatDatetime(Fields!expireDate.Value,DateFormat.ShortDate))
← Older Entries