Pivot 12 Months’ User Counts

https://social.msdn.microsoft.com/Forums/en-US/bb4c1214-cbbf-42e7-8626-145c0a8bfef4/sql-server-count-1-for-all-active-months-during-reporting-period?forum=transactsql

  
 
create table #base
(RecID int, FromDate
date , ToDate date
)
insert into #base
values (17669977,'5/13/2016','11/4/2016')
insert into #base
values (17683279, '4/19/2016',
'10/4/2016')
insert into #base
values (17693200, '5/2/2016',
'11/4/2016')
;with mycte0 as (
Select RecID, Datename(Month,Dateadd(month,-n+1,getdate())) mth
, Dateadd(month,-n+1,getdate())dt,n 
From #base, (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) ) d(n)
)
 
 

, mycte1 as ( 
 Select RecID,  Dateadd(month,n-1,FromDate) dt, Datename(Month,  Dateadd(month,n-1,FromDate)) mth from #base
Cross apply (values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12) ) d(n)
WHERE 
--RecID=17669977  And 
Dateadd(month,n-1,FromDate) Between FromDate and EOMONTH(ToDate)

)


select
-- m0.RecID ,
Sum(Case when m0.mth='September' and  m1.mth is not null then 1 else 0 End)  'September'
,Sum(Case when m0.mth='August' and  m1.mth is not null then 1 else 0 End)  'August'
,Sum(Case when m0.mth='July' and  m1.mth is not null then 1 else 0 End)  'July'
,Sum(Case when m0.mth='June' and  m1.mth is not null then 1 else 0 End)  'June'
,Sum(Case when m0.mth='May' and  m1.mth is not null then 1 else 0 End)  'May'
,Sum(Case when m0.mth='April' and  m1.mth is not null then 1 else 0 End)  'April'
,Sum(Case when m0.mth='March' and  m1.mth is not null then 1 else 0 End)  'March'
,Sum(Case when m0.mth='Feburary' and  m1.mth is not null then 1 else 0 End)  'Feburary'
,Sum(Case when m0.mth='January' and  m1.mth is not null then 1 else 0 End)  'January'
,Sum(Case when m0.mth='December' and  m1.mth is not null then 1 else 0 End)  'December'
,Sum(Case when m0.mth='November' and  m1.mth is not null then 1 else 0 End)  'November'
,Sum(Case when m0.mth='October' and  m1.mth is not null then 1 else 0 End)  'October'
 



 from mycte0 m0 left join mycte1 m1 on m0.RecID=m1.RecID and m0.mth=m1.mth
 --Group by  m0.RecID



drop table #base

  
   
 
 

 

Count of Consecutive Decending Values Up to the First Gap

https://social.msdn.microsoft.com/Forums/en-US/e083c5fa-c82b-47bc-bc9e-657a58344986/count-of-consecutive-decending-values-up-to-the-first-gap?forum=transactsql

  
 
   create table test (customer_no int,	membership_year int)
Insert into test values
(1,2016),(1,2015),(1,2014),(1,2008),(1,2007)
,(2,2012),(2,2011),(2,2010),(2,2007),(2,2006),
(3,2017),(3,2016),(3,2015),(3,2014),(3,2013),(3,2012)
 
 
  ;with mycte as (
select  customer_no, membership_year
, DATEADD(year, - ROW_NUMBER() OVER(PARTITION BY customer_no ORDER BY membership_year), datefromparts(membership_year,1,1)) AS grp
  from test
)
,mycte1 as (
select customer_no,  membership_year, grp , max(membership_year) Over (PARTITION BY customer_no) maxYear, Year(getdate()) curYear
, DENSE_RANK() OVER(PARTITION BY customer_no  ORDER BY grp desc) AS Ranking 
from mycte
)
Select customer_no, ISNULL(SUM(case when maxYear>=curYear and Ranking=1 then 1 else null end) -1 ,0)  as  cnt  
From mycte1
Group by customer_no
order by customer_no


/*
Customer 1 would have 2 consecutive years
Customer 2 would have 0 (zero) because they are no longer a member as of 2012
Customer 3 would have 5 consecutive years. 
*/

Drop table test


  
   
 
 

 

Fill Dataset to 10 Multipliers

  
 
   
  
create table test (id int, col1 int)

insert into test values(1,1),(2,2),(3,3),(4,4), (5,1),(6,2),(7,3),(8,4),(9,1),(10,2)
,(11,3),(12,4),(13,3),(14,4)
 

declare @cnt int
select @cnt=count(*) from test

--Select @cnt%10

;with mycte as (
select *  from test
union all
select 999,9 from 
(values(1),(2),(3),(4),(5),(6),(7),(8),(9),(10))   d(n)
where n<=10 - @cnt%10  and @cnt%10<>0
)
 
Select *  from mycte 

drop table test

 
 

 

A Sample To Export a Text File with Parameterized Stored Procedure through BCP

  
 
   
  
Use mydb1 --the DB name
go

If object_id('dbo.Classtests','U') is not null
drop table dbo.Classtests;
go--

create table Classtests (ClassID  int,  DepartID int, year_code int, trm_code int)
Insert into Classtests (ClassID,DepartID,year_code ,trm_code )
values(1,1,2015,20),(2,1,2017,20),(3,1,2016,20),(4,1,2016,20),(5,1,2016,20),(6,1,2016,20)
insert into Classtests   
Values (7,1,2017,20),(8,1,2017,20),(9,1,2017,20),(10,1,2017,20)


If object_id('dbo.usp_Classtest','P') is not null
drop procedure dbo.usp_Classtest;
go
create procedure usp_Classtest 
@year_code int
,@trm_code varchar(100)
As
Begin
Select * from Classtests
WHERE year_code=@year_code and trm_code= @trm_code;
End
Go
If object_id('dbo.bcpClassFile','P') is not null
drop procedure dbo.bcpClassFile;
go

Create PROCEDURE bcpClassFile 
 @year_code int
,@trm_code varchar(100)
AS
  BEGIN
	DECLARE  @sql VARCHAR(8000)=''	
	SET @sql = 'bcp "EXEC mydb1..usp_Classtest ''' + CONVERT(VARCHAR(4), @year_code) + ''',''' + @trm_code +  '''" Queryout "c:\temp\test\TheFile'
				 + @trm_code + CONVERT(VARCHAR(4), @year_code)
				 + '.txt " -c -t"\t" -T -S'
				 + @@SERVERNAME
	EXEC master..xp_cmdshell  @sql
  END


  --Test 
  exec bcpClassFile 2016,'20'

  --Check file in  c:\temp\test\TheFile202016.txt

 
 

 

Subset From Different Groups

http://forums.asp.net/t/2102617.aspx?Select+data+without+use+subselect

  
 
create table #a(product varchar(10),quality int)
insert into #a values('tri',2),('vse',2),('sve',2),('bun',1),('kal',3),('sve',3),('vse',3),('bun',2),('bun',1),('tri',3),('bun',3),('tri',3),('kal',2),('kal',1), ('kal111',1),('kal333',3)

-----Condition for select is: select all of product which are in quality 2 and 3 but not in quality 1. 
 
  
 --  option 1
 ;with mycte as (
select product, quality, Max( Case when quality=1 then POWER(2*1,1) 
when quality=2 then POWER(2*1,2) 
when quality=3 then POWER(2*1,3)  end) bits
from #a
group by product, quality
) 
,mycte1 as (
select  product, quality,
Case when  2 &  Sum( bits) Over(Partition by product) =2 then 1 else 0 End as val1 
,Case when  4 &  Sum(bits) Over(Partition by product) =4 then 1 else 0 End as val2 
,Case when  8 &  Sum(bits) Over(Partition by product) =8 then 1 else 0 End as val3 
from mycte)

Select distinct product from mycte1
WHERE val1=0 and val2=1 and val3=1


--option2 
Select product from (
select product from   #a
Where quality=2
intersect
select product from   #a
Where quality=3) t
except
select product from   #a
Where quality=1

 
drop table #a

 
 

 

Round Money to Nearest Quarter

  
 
  
 create table test (col decimal(10,2))
Insert into test values(1.33),(2570.47),(100020.10),(100.62),(100.63),(99.99)

select Cast(Round(col * 4,0)/4.0 as decimal(10,2))  col from test
 
select 
cast(col as int)+ 
Case
when col%1 <= 0.12     then 0.00
when col%1 > 0.12  And col%1 <= 0.37  then .25
when col%1 > 0.37  and col%1 <= 0.62  then .50
when col%1 > 0.62  and col%1 <= 0.87  then .75
when col%1 > 0.87  then 1.00
end
 

from test


drop table test


 --https://social.msdn.microsoft.com/Forums/en-US/c22b2cac-0a78-4d61-bfcc-00d2e2d22303/round-to-nearest-25-cents?forum=transactsql
 

 

Script to Drop Constraints from Linked Tables Before Drop Table

  
 
  

DECLARE @sql varchar(max)=''
SELECT @sql = @sql + 'ALTER TABLE ' +  Quotename(OBJECT_SCHEMA_NAME(parent_object_id)) +'.' + Quotename(OBJECT_NAME(parent_object_id) )+ 
' DROP CONSTRAINT ' + name + ';'+ CHAR(13)
FROM sys.foreign_keys
WHERE referenced_object_id = object_id('myscm.thetable' )
or referenced_object_id = object_id( 'myscm.anothertable')

SELECT @sql = @sql+ CHAR(13)+ ' DROP TABLE ' +s.name+ '.'+ t.name  
FROM sys.Tables t INNER JOIN sys.schemas s ON s.schema_id = t.schema_id
where s.name='myscm' and  t.name in ('thetable', 'anothertable')


-----uncomment below line to execute the generated script
 --EXEC sp_executesql @sql 

PRINT @sql