| Author | Message | ||
     Driverramudu Side Hero Username: Driverramudu Post Number: 9289 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
oh thanks for letting me know  Life is Race. I am in. Driving is my PASSION. Ayya baaboi bullet baby Dhaga... Dhaga... Champuthondi chocolate baby Sega...Sega.. | ||
| Maverick Legend Username: Maverick Post Number: 41962 Registered: 01-2008 Posted From: 70.39.231.108 Rating: N/A |
>>i never heard of BI environments lo untundi..used in reporting. calendar dimension Who is this DB member? | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 9288 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
i never heard of this what DB u r using and is it available readymade? Life is Race. I am in. Driving is my PASSION. Ayya baaboi bullet baby Dhaga... Dhaga... Champuthondi chocolate baby Sega...Sega.. | ||
| Maverick Legend Username: Maverick Post Number: 41960 Registered: 01-2008 Posted From: 70.39.231.108 Rating: N/A |
OM, if u have a clander table, you usually will, i can just do a join to it to achieve the same result Who is this DB member? | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 9287 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
i forgot to use column alias here ,Increment = ROW_NUMBER() OVER (ORDER BY c1.column_id) Life is Race. I am in. Driving is my PASSION. Ayya baaboi bullet baby Dhaga... Dhaga... Champuthondi chocolate baby Sega...Sega.. | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 9286 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
ehe ee SQL complex? vaarni...  if not secret would you mind to share how did you achieve this. Just for my knowledge purpose. Life is Race. I am in. Driving is my PASSION. Ayya baaboi bullet baby Dhaga... Dhaga... Champuthondi chocolate baby Sega...Sega.. | ||
| Maverick Legend Username: Maverick Post Number: 41957 Registered: 01-2008 Posted From: 70.39.231.92 Rating: N/A |
>>did u try with below SQL? ledu le. inta sqls rase kante, calendar table tho join kottadam easy..but thats not what i wanted finally..so used a diff approach Who is this DB member? | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 9284 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
did u try with below SQL? is it not worked for you ? Life is Race. I am in. Driving is my PASSION. Ayya baaboi bullet baby Dhaga... Dhaga... Champuthondi chocolate baby Sega...Sega.. | ||
| Maverick Legend Username: Maverick Post Number: 41955 Registered: 01-2008 Posted From: 70.39.231.92 Rating: N/A |
thanks to all, went with a different approach other thann sql. Who is this DB member? | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 9283 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
in the first method if you wann N no of days between given two dates then go for cross join like this ; WITH ParameterDates AS ( -- keep change dates here up to 592 days SELECT '2012-01-01' AS FromDate ,'2012-02-01' AS ToDate ,'FIRST' AS DateDesc ) ,Incrementers AS ( SELECT 1 AS Counter ,Increment = ROW_NUMBER() OVER (ORDER BY column_id) FROM master.sys.columns AS c1 CROSS JOIN master.sys.columns AS c2 ) SELECT pd.FromDate ,CurrentDate = DATEADD(day,inc.Increment,pd.FromDate) ,pd.ToDate ,pd.DateDesc FROM ParameterDates AS pd CROSS JOIN Incrementers AS inc WHERE inc.Increment <= DATEDIFF(day,pd.FromDate,pd.ToDate) but have to see the performance as Increment CTE gives huge number of row(s) or simplest way is you can restrict top 100000 counters any way you dont give dates that givens that many number of days so you can use this SELECT TOP (100000) 1 AS Counter ,Increment = ROW_NUMBER() OVER (ORDER BY column_id) FROM master.sys.columns AS c1 CROSS JOIN master.sys.columns AS c2 Life is Race. I am in. Driving is my PASSION. Ayya baaboi bullet baby Dhaga... Dhaga... Champuthondi chocolate baby Sega...Sega.. | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 9282 Registered: 02-2009 Posted From: 76.95.148.203 Rating: N/A |
 Life is Race. I am in. Driving is my PASSION. Ayya baaboi bullet baby Dhaga... Dhaga... Champuthondi chocolate baby Sega...Sega.. | ||
| Driverramudu Side Hero Username: Driverramudu Post Number: 9281 Registered: 02-2009 Posted From: 166.137.209.27 Rating: N/A |
mav here are queries that you are looking for no table no variable no loops Method 1 : Making use of data dictionary ; WITH ParameterDates AS ( -- keep change dates here up to 592 days SELECT '2012-01-01' AS FromDate ,'2012-02-01' AS ToDate ,'FIRST' AS DateDesc ) ,Incrementers AS ( SELECT 1 AS Counter ,Increment = ROW_NUMBER() OVER (ORDER BY column_id) FROM master.sys.columns ) SELECT pd.FromDate ,CurrentDate = DATEADD(day,inc.Increment,pd.FromDate) ,pd.ToDate ,pd.DateDesc FROM ParameterDates AS pd CROSS JOIN Incrementers AS inc WHERE inc.Increment <= DATEDIFF(day,pd.FromDate,pd.ToDate) Method 2 : Making use of recursion concept but max 100 recurstions only allowed otherwise you have to change setting max_recursion number ; WITH ParameterDates AS ( SELECT '2012-01-01' AS FromDate ,'2012-02-01' AS ToDate ,'FIRST' AS DateDesc ) ,StrCTE(start, stop) AS ( SELECT 1, CHARINDEX(',' , REPLICATE('1,',DATEDIFF(DAY,FromDate,ToDate) )) FROM ParameterDates UNION ALL SELECT stop + 1, CHARINDEX(',' ,REPLICATE('1,',DATEDIFF(DAY,FromDate,ToDate)) , stop + 1) FROM StrCTE CROSS JOIN ParameterDates WHERE stop > 0 ) ,List AS ( SELECT SUBSTRING(REPLICATE('1,',DATEDIFF(DAY,FromDate,ToDate)) , start, CASE WHEN stop > 0 THEN stop-start ELSE 4000 END) AS Counter FROM StrCTE CROSS JOIN ParameterDates ) ,Incrementers AS ( SELECT * ,Increment = ROW_NUMBER() OVER (ORDER BY Counter) FROM List WHERE Counter > '' ) SELECT i.Increment ,FromDate ,CurrentDate = DATEADD(day,(Increment-1),FromDate) ,ToDate ,DateDesc FROM Incrementers AS i CROSS JOIN ParameterDates AS pd Life is Race. I am in. Driving is my PASSION. Ayya baaboi bullet baby Dhaga... Dhaga... Champuthondi chocolate baby Sega...Sega.. | ||
| Sachin Legend Username: Sachin Post Number: 32232 Registered: 04-2008 Posted From: 151.151.16.21 Rating: N/A |
oracle sql select val, date_from +level-1 , date_thru , val from test_connect connect by level <= date_thru- date_from+1 test_connect is the table name share ki gross ki thedaa theliyakapothenemi...box office disco lo iragadeeyochu...excel, palakol, 292 ki ki ki... | ||
| Chasing_perfection Junior Artist Username: Chasing_perfection Post Number: 577 Registered: 04-2009 Posted From: 12.110.114.133 Rating: N/A |
Not a sql expert kani...try chesa in T SQL --Original table declare @temp table ( a varchar(10), datefrom datetime, datethrough datetime, val varchar(10) ) -- Table with the desired data declare @temp1 table ( a varchar(10), datefrom datetime, datethrough datetime, val varchar(10) ) insert into @temp (a,datefrom,datethrough,val) values ('1','1/1/2012','2/1/2012','FIRST') declare @a1 varchar(10) declare @date1 datetime declare @date2 datetime declare @val1 varchar(10) declare vcursor cursor for select a,datefrom,datethrough,val from @temp open vcursor fetch next from vcursor into @a1,@date1,@date2,@val1 while (@@FETCH_STATUS = 0) begin while DATEDIFF(d,@date1,@date2) >= 0 begin insert into @temp1 (a,datefrom,datethrough,val) values(@a1,@date1,@date2,@val1) set @date1 = DATEADD(d,1,@date1) end fetch next from vcursor into @a1,@date1,@date2,@val1 end close vcursor deallocate vcursor select * from @temp1 Lotus Island | ||
| Newguy123 Hero Username: Newguy123 Post Number: 14937 Registered: 01-2009 Posted From: 192.200.5.41 Rating: N/A |
i can give you in oracle. select Date_from+rownum-1,Date_thru ,column_A from table1 ,dual connect by rownum<=Date_thru-Date_from | ||
| Pavala Hero Username: Pavala Post Number: 16364 Registered: 02-2008 Posted From: 85.80.227.204 Rating: N/A |
gootle anji ekkada dead ravi ni mana samajam full ga vaaduku dobbi eppudu rowdy ante ela. - Diviseema Danush gadi acting ni match chesetollu mana telugu lo evaroo leranukunta - Kfpremium | ||
| Maverick Legend Username: Maverick Post Number: 41938 Registered: 01-2008 Posted From: 70.39.231.172 Rating: N/A |
anji, like a sql server view Who is this DB member? | ||
| Sri_anji Hero Username: Sri_anji Post Number: 11446 Registered: 02-2008 Posted From: 63.66.34.226 Rating: N/A |
I think we can do it with SP or Function. Do you want only SQL? | ||
| Simba Side Hero Username: Simba Post Number: 8433 Registered: 02-2008 Posted From: 206.210.27.33 Rating: N/A |
mav, interview chestunnava leka istunnava? adhey... to filter the fakes  | ||
| Maverick Legend Username: Maverick Post Number: 41937 Registered: 01-2008 Posted From: 70.39.231.172 Rating: N/A |
oka scenario Table Columns Colmn A Date_from Date_thru Val 1 2012-01-01 2012-02-01 FIRST select should return 1 2012-01-01 2012-02-01 FIRST 1 2012-01-02 2012-02-01 FIRST 1 2012-01-03 2012-02-01 FIRST .. 1 2012-01-31 2012-02-01 FIRST 1 2012-02-01 2012-02-01 FIRST the first range record can be any days apart. off the fly with out using any other tables(like a calendar) can we do it? Who is this DB member? |
