How to get list of sequential dates in MS SQL Transact SQL and cursors alternative

Let’s imagine that we have table where we store some service prices and that this price can vary regarding period of the year.

You would then have something like this in your table:

$50 from January 1st to May 31th
$70 from June 1st to October 31th
$60 from November 1st to December 31th

What we want is to get all daily prices for given date region. So, if we give range from May 15th to 15th June we want to get this:

May 15th $50
May 16th $50
May 17th $50
May 18th $50
May 19th $50
May 20th $50
May 21th $50
May 22th $50
May 23th $50
May 24th $50
May 25th $50
May 26th $50
May 27th $50
May 28th $50
May 29th $50
May 30th $50
May 31th $50
June 1st $70
June 2nd $70
June 3rd $70
June 4th $70
June 5th $70
June 6th $70
June 7th $70
June 8th $70
June 9th $70
June 10th $70
June 11th $70
June 12th $70
June 13th $70
June 14th $70
June 15th $70

In following piece of code is explained how to do it, plus there is a nice bonus how you can avoid using slow cursors for iterating through SQL records.

DECLARE @FromDate datetime, @UntilDate datetime, @Date datetime, @Id int
SET @FromDate = '2014-05-15'
--Trick to make date independant from year (always use the current year)
SET @FromDate = CAST(CAST(YEAR(GETUTCDATE()) AS varchar) + '-' + CAST(MONTH(@FromDate) AS varchar) + '-' + CAST(DAY(@FromDate) AS varchar) AS DATETIME)
SET @UntilDate = '2014-06-15'
--Trick to make date independant from year (always use the current year)
SET @UntilDate = CAST(CAST(YEAR(GETUTCDATE()) AS varchar) + '-' + CAST(MONTH(@UntilDate) AS varchar) + '-' + CAST(DAY(@UntilDate) AS varchar) AS DATETIME)

--Declare your temporary table for sequential date list with price
  aId int IDENTITY(1, 1),
  aDate datetime,
  aPrice decimal(15,2)

--Fill temporary table with sequential dates
;WITH T(date)
  SELECT @FromDate
  SELECT DATEADD(day, 1, FROM T WHERE < @UntilDate
INSERT INTO @results (aDate)

--This temp table will be used for iterating through the records (do not use slow cursors)
  bId int,
  bDate datetime

FROM    @results

  SELECT TOP 1 @Id = bId FROM @temp
  SELECT @Date = aDate FROM @results WHERE aId = @Id

  --Do you logic here (find specific row record from SQL table using @Date) and get price
  UPDATE @results SET
    aPrice = Price
  FROM YourTable
  WHERE IntervalStart <= @Date AND @Date <= IntervalEnd

  --"Decrease" counter
  DELETE FROM @temp WHERE bId = @Id

SELECT   aDate AS [Date],
        aPrice AS Price
FROM    @results

Note 1

If you want to make yearly independate date comparisons in MS SQL then you should create new temporary table where you would copy interval records (with prices) from YourTable and then update these record (IntervalStart and IntervalEnd dates) by changing their year date component to current year (see at the top for the syntax)

Note 2

If @FromDate and @UntilDate have different years, i.e. @FromDate = ‘2013-12-15’ and @UntilDate = ‘2014-01-15’ then, when doing sequential dates (days) you should call the described transforming function (search WITH keywoard) twice: first from @FromDate until the end of the year ‘2013-12-31’ and then from the first day of the year ‘2014-01-01’ until the @UntilDate

Check. Mate. 🙂


(source: Wikipedia)

Continue Reading

Instagram crashes on start – when big company make things wrong

I am having iOS 6 installed on my iPhone (intentionally didn’t want to switch to ugly iOS 7) and yesterday on March 4th 2014 I installed the Instagram update.

After doing this, the application is crashing on start. It doesn’t open. On opening the background graphic is showed very quickly and then everything disappear (crashes).

It is the perfect example how big company can make a mess making millions of users angry. I thing they are aware of this.

What is surprising for me is that the new update is not available yet. They should have some team of developers that can work on these kind of unwanted incidents overtime.

Maybe Facebook invested all in WhatsApp and let Instagram on cold? They paid 19 times more for it (or in measurement units let’s say 19 billion dollars).

After reinstalling the app it started to work.



Continue Reading

Geek weekend with Acer Iconia W700 tablet

Acer Iconia W700 is great piece of hardware for Windows type technical guys.  It can be a helpful tool when you are on the road or out of the office and need to get things done (no matter if you are a developer, system administrator or company owner).

It is basically a PC tablet with a touch screen, with i5 IvyBridge processor  (3337U at 1.8GHz) and with Toshiba 128GB SSD disk. It has 11,6″ 16:9 screen with great, full HD resolution 1920x1080px and it has great battery life – maybe 6 hours under full performance or more when you are not so demanding. And it has a fullsize keyboard which is great (not champion for long typing but acceptable). And it is quiet.

Not so good thing is that it is a quite heavy, eventhough is the size of an iPad. I think it is over 1kg. MacBookAir is great competitor but it is also more expensive.

In a full resolution, things are a little bit small on this screen, but hay, you have a big workspace to work in (in the terms of pixels). It would be better to have 8GB of RAM instead of 4GB but it is a capable machine.

It came with Windows 8 64-bit operating system which I personally does not like (as a many of other folks). It is very stable system on the other side, maybe runs even better then Windows 7 but I don’t like this hybrid mix of Start screen and standard Desktop screen and even this is a touch screen tablet I personally really don’t like these flat icons. Everything look the same to me, so distracting and that is also the main reason I didn’t switch from iOS6 to iOS 7 on my iPhone. I try to go to the desktop mode as soon as possible. There is a good completely free “Start” program that gives you Start button and put you instantly into the desktop mode after OS is loaded so you basically have more stable Windows 7. And that is a good compromise for me.

It was all nice until I upgraded to Windows 8.1 so if you didn’t do this yet (on this tablet) I suggest you not to do this. You will get leggy, buggy, not-responsive OS that will make you angry. Mouse pointer is stucking, not responsive, whole OS is so slow that it is unacceptable. Maybe it is the drivers issue, don’t know. And, there will not be possible for you to go back ro Windows 8 (if you didn’t made a full backup before).

On Friday I decided that I will restore my backup but I run into the problems because I didn’t get docking station with my Acer so I had only one USB 3.0 port.  I used Yumi multiboot USB creator to create different OS choices or backup recovery choices but booting was a problem. How to come into the BIOS of this device? Well, you need to do Shutdown first and then press and hold Windows button (in the middle, just below the screen) and while holding press Power On button and hold windows button. After initial logo you will come into the BIOS. I have changed UEFI booting option to Standard BIOS, turned off the Quick BIOS sequence and put USB Floppy in booting order at first place. This is the factory video tutorial but I don’t have portable DVD device, only an USB.

I put my created USB key with operating systems in the only port, saved the BIOS settings and I rebooted the tablet. It started to boot from USB and gave me the choice to choose operating system I want to install, but the keyboard wasn’t working and there were no more free USB ports so I was stucked. I couldn’t make any choice and after 30 second my bad Windows 8.1 OS is loaded automatically. Happily I remembered that I had somewhere an old 4-port USB hub (with “amazing” 1.0 speed) and it needed to get also the power so I used my laptop to give him that (used one USB port). In second USB I put the USB dongle for this handy small keyboard with a mouse pad, i third I put my USB disk with backup on it and the main cable of USB hub was inserted into the tablet’s USB port.

And it finally worked. Currently I am restoring my backup. It is working already for 10 hours and it will need around 10 hours more because this USB hub has really slow USB transfer. I hope it will save me, but I lost a lot of time this weekend. This is how it goes.

One image… How it looks. And this USB hub is really nice because it is also a heater so you can put a cup of your tea/coffee on it will not get cold 🙂

2014-03-02 19.56.24

Continue Reading