CODEWITHSUNDEEP
sqlsql-server
Ben
Ben

Reputation: 4371

MS SQL Failing to run - Correct syntax?

I have a PHP application which runs a large set of SQL files. It runs all of them successfully apart from this one which returns the result "1" as apposed to an array of objects. Is there a syntax issue with this sql?

set datefirst 1;

select  top 10 artistlabel_name
        , album_name
        , releasedate
        , label
        , sum(units) total_units
        , sum(sales) total_sales 
from    (
          select  c.[name] album_name
                  , isnull(d.[name]
                  , e.[name]) artistlabel_name
                  , c.releasedate
                  , e.[name] label
                  , sum(b.price) sales
                  , count(b.Id) units 
          from    [order] a 
                  left join orderalbum b on b.orderId = a.Id
                  left join album c on b.albumId = c.Id
                  left join label e on c.labelId = e.Id
                  left outer join artist d on c.artistId = d.Id
          where   a.successful=1
                  and datepart(ww, a.OrderDate) = datepart(ww, getDate())-1
                  and datepart(yyyy, a.OrderDate) = datepart(yyyy, getDate())
          group by 
                  c.[name]
                  , isnull(d.[name], ,e.[name])
                  , c.releasedate
                  , e.[name]
          union
          select  e.[name] album_name
                  , isnull(d.[name], f.[name]) artistlabel_name
                  , e.releasedate
                  , f.[name] label
                  , sum(b.price) sales
                  , count(b.Id) units 
          from    [order] a left join orderalbum b on b.orderId = a.Id
                  left join albumvariant c on b.albumvariantId = c.Id
                  left join album e on c.albumId = e.Id
                  left join label f on e.labelId = f.Id
                  left outer join artist d on e.artistId = d.Id
          where   a.successful=1
                  and datepart(ww, a.OrderDate) = datepart(ww, getDate())-1
                  and datepart(yyyy, a.OrderDate) = datepart(yyyy, getDate())
          group by 
                  e.[name]
                  , isnull(d.[name], f.[name])
                  , e.releasedate
                  , f.[name]
        ) x
group by 
        x.album_name, 
        x.artistlabel_name, 
        x.releasedate, 
        x.label
having  sum(x.sales) > 0 
        and album_name is not null
order by 
        total_units desc;

Hopefully there's an MSSQL ninja out there who can help me.

Upvotes: 0

Views: 115

Answers (3)

Michael Green
Michael Green

Reputation: 1491

Looks to me like ta.speot.is has the answer. However you need to be careful with dates in the WHERE clause, specifically if GetDate() falls in the first week of the year.

Upvotes: 0

ta.speot.is
ta.speot.is

Reputation: 27214

I would imagine that this query is returning two result sets, the first from set datefirst 1; and then the second.

Read the second result set in your PHP application.

http://php.net/manual/en/function.mssql-next-result.php

Upvotes: 3

Jan_V
Jan_V

Reputation: 4406

Note: Not having read/tried your script

If you are using MS SQL Server you can enter this query in Management Studio and let it validate. The button is available in the 'New Query' window. That way you could check if the syntax is alright. If the syntax is correct, it's probably an issue with the queries and the multiple joins in it, but it's hard to say anything about that without knowing the table structure and data in it (imo).

Upvotes: -1

Related Questions