Welcome Guest, Not a member yet? Register   Sign In
Run Microsoft .SQL within CI Model
#1

[eluser]Ben Bowler[/eluser]
I have a tricky little question for you here. I have a collection of MS SQL files like the following which were used with ToadSQL to retrieve data in the past.

Code:
-- RUN AS SCRIPT SO THAT MONDAY IS FIRST DAY OF THE WEEK
set datefirst 1;

select top 10 artist_name, album_name, sum(units) total_units, sum(sales) total_sales from (

-- Sales by album
select c.[name] album_name, d.[name] artist_name, sum(b.price) sales, count(b.Id) units from [order] a, orderalbum b , album c, artist d
where b.albumid=c.id
and c.ArtistId = d.Id
and a.successful=1 and a.id=b.orderid
and datepart(ww, a.OrderDate) = datepart(ww, getDate())-1
and datepart(yyyy, a.OrderDate) = datepart(yyyy, getDate())
group by c.[name], d.[name]

union

-- Sales by album variant
select d.[name] album_name, e.[name] artist_name, sum(b.price) sales, count(b.Id) units from [order] a, orderalbum b , albumvariant c, album d, artist e
where d.id=c.albumid and  b.albumvariantid=c.id  
and d.ArtistId = e.Id
and a.successful=1 and a.id=b.orderid
and datepart(ww, a.OrderDate) = datepart(ww, getDate())-1
and datepart(yyyy, a.OrderDate) = datepart(yyyy, getDate())
group by d.[Name], e.[Name]

) x
group by x.album_name, x.artist_name
having sum(x.sales) > 0
order by total_units desc;

I'm now building an application in CodeIgniter to automate the process but I don't want to reinvent the wheel with the queries. Is there anyway within a CI Model I can retrieve the responses of these complex queries by running the .sql file?

It's made particularly complex by the fact that some query two tables and use 'union' is this possible?

Hope there are some SQL heads out there who can help.

Ben
#2

[eluser]Ben Bowler[/eluser]
If anyone's interested I slept on it and came up with this ridiculously simple function:

Code:
/**
*
* Retrieve the results of an SQL file
*
*/

    function runsql($file = '')
    {
        
        $this->db_download = $this->load->database('download', TRUE);
        
        $sql = file_get_contents($file);
        
        $query = $this->db_download->query($sql);
        $result = $query->result();
        
        return $result;
        
    }




Theme © iAndrew 2016 - Forum software by © MyBB