[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