Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Need T-SQL optimization Help Expand / Collapse
Author
Message
Posted Saturday, January 4, 2014 2:32 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:49 PM
Points: 138, Visits: 389
Hi There,

Following is the sample data ,

DECLARE @maxval int, @minval int
select @maxval=201301,@minval=201312

SELECT top 100001 CAST(((@maxval + 1) - @minval) *
RAND(CHECKSUM(NEWID())) + @minval AS int)Date ,ceiling (RAND(CHECKSUM(NEWID())) *1000+25692*RAND(CHECKSUM(NEWID())) )*ceiling (RAND(CHECKSUM(NEWID())) *1000+25692*RAND(CHECKSUM(NEWID())) )ID
into #sample from sysobjects , syscolumns

Problem :
* there are 2 columns date & id
* take first month id's , say for example 201302
* need to find whether those id's are available or not, in the next 3 months. so here 201303,201304,201305
* need to repeat this for all months

Here is the my code, It requires more manual intervention and time taking. Please assist.

select distinct   date from #sample order by DATE 

declare @processingdate varchar(6),@from varchar(6),@to varchar(6)
set @processingdate = '201302'
set @from = '201303'
set @to = '201305'


select distinct id into #Source from #sample where DATE = @processingdate
select distinct id into #lookup from #sample where DATE between @from and @to

select @processingdate,count(*) from #Source where ID not in (select * from #lookup)


Thanks
Post #1527792
Posted Saturday, January 4, 2014 12:47 PM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Yesterday @ 8:14 PM
Points: 37,107, Visits: 31,665
To be honest, whomever designed the table with an integer representation of year and month did a great disservice to anyone and everyone using the table and it's going to be a real bugger when the year roles over. Are you allowed to change the datatype of that column?

--Jeff Moden
"RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".

First step towards the paradigm shift of writing Set Based code:
Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #1527856
Posted Saturday, January 4, 2014 7:39 PM


SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Yesterday @ 1:08 PM
Points: 80, Visits: 351
I agree with Jeff that a date should be stored as a date in the source system (OLTP). I would try to change it. However, I know how hard it is sometimes to get buy in at the Business Level.

I have work on a few data warehouses (OLAP) in my time. One part of a star schema is a date dimension. Usually some number is given to a date. That number is used throughout the tables.

How about a date decode table that translates the 201301 (a number stored as a char(6)) to '2013-01-01' as a date. Thus, adding one more join to the equation allows you to work with dates.

I would check Aaron's article on handling dates.

http://sqlblog.com/blogs/aaron_bertrand/archive/2009/10/16/bad-habits-to-kick-mishandling-date-range-queries.aspx

I would keep away from BETWEEN.

Good luck ...


John Miner
Crafty DBA
www.craftydba.com
Post #1527876
Posted Monday, January 6, 2014 1:50 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: 2 days ago @ 10:49 PM
Points: 138, Visits: 389
Jeff Moden (1/4/2014)
To be honest, whomever designed the table with an integer representation of year and month did a great disservice to anyone and everyone using the table and it's going to be a real bugger when the year roles over. Are you allowed to change the datatype of that column?



Yes I can change the datatype ... Its complex bet one time activity..
so I could ...
Post #1527997
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse