1) How to write the below SQL query in Magento collection :
SELECT * FROM 'table1' WHERE active=0 AND '2014-07-08' between 'not_available_from_date' and 'not_available_to_date'
2) Also how to write this SQL query in Magento Collection :
Select sum(qty + product_id - any_other_columns) as total from table2 GROUP BY product_id;
Without using ->columns(' ') function.
Thanks for your help
Assuming you have a model mapped to your table1 and one mapped to table2 you can try something like this.
$date = '2014-07-08';
$collection = Mage::getModel('[module]/table1')->getCollection()
->addFieldToFilter('active', 0)
->addFieldToFilter('not_available_from_date', array('date' => true, 'to' => $date))
->addFieldToFilter('not_available_to_date', array('date' => true, 'from' => $date));
And for the second one.
$collection = Mage::getModel('[module]/table2')->getCollection()
->addExpressionFieldToSelect('total', 'SUM({{qty}} + {{product_id}} - {{any_other_columns}})', array('{{qty}}'=>'qty', '{{product_id}}'=>'product_id', '{{any_other_columns}}'=>'any_other_columns'));
//since SUM requires a GROUP BY...group by some column
$collection->getSelect()->group('product_id');
The examples above are for flat entities.
If you have an EAV entity similar to products or categories use
addAttributeToFilter instead of addFieldToFilter
addExpressionAttributeToSelect instead of addExpressionFieldToSelect
EDIT
After an other investigation I found that for flat entities you can use directly the column names. The attribute placeholders {{...}} are needed only for EAV entities.
So your second collection should look like this:
$collection = Mage::getModel('[module]/table2')->getCollection()
->addExpressionFieldToSelect('total', 'SUM(qty + product_id - any_other_columns)', array('qty', 'product_id', 'any_other_columns'));
$collection->getSelect()->group('product_id');
The third parameter for addExpressionFieldToSelect is required but it can be an empty array. for addExpressionAttributeToSelect that last parameter should contain the mapping between the placeholders and the attribute codes. In this case seams that they are useless. I don't know if this is a bug or a feature.
getData you loop through the collection like this: foreach ($collection as $item){Mage::log($item->getData())}
echo $collection->getSelect(). That should give you an idea if something is wrong. You may even run it manually to see the result.