Datename begins with April ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Reporting Services Development

Datename begins with April ?

Author

Topic

Royal
Starting Member

32 Posts

Posted - 2006-12-25 : 12:59:16

Wishing you all a prosporous New year!

Having a problem though, grateful for some help, wondering how to define Month, starting w January?? Now following sql returns Month starting w April..August)???

SELECT SUM(Cd.Price) AS Sales, DATENAME(mm, Purchase.time) AS Month
FROM Tables
GROUP BY DATENAME(mm, Purchase.time), DATEPART(mm, Purchase.time),
ORDER BY DATEPART(mm, Purchase.time)ASC

Furthermore how to select the top sales person from each shop?
SELECT TOP (1) shop.name, SUM(Cd.Price) AS Sales, Personal.Name AS Salesperson
FROM Purchase INNER JOIN
Personal ON Purchase.Salesperson_id = Personal.Personal_id RIGHT OUTER JOIN shop ON Personal.work_id = shop.shop_id FULL OUTER JOIN
Cd ON Purchase.Cd_id = Cd.Cd_id
GROUP BY Shop.Name, Personal.Name
ORDER BY Sales DESC

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-26 : 14:26:15

The query you gave should sort in calendar month order, not alpha order. You have a comma after the second GROUP BY expression, which is a syntax error, but if you remove that and run it as is (with that ORDER BY) it will give you calendar month order.

For you second query try this

SELECT shop.name, SUM(Cd.Price) AS Sales, Personal.Name AS Salesperson
FROM Purchase 
INNER JOIN Personal ON Purchase.Salesperson_id = Personal.Personal_id 
RIGHT OUTER JOIN shop ON Personal.work_id = shop.shop_id 
FULL OUTER JOIN Cd ON Purchase.Cd_id = Cd.Cd_id
GROUP BY Shop.Name, Personal.Name
HAVING SUM(Cd.Price) = (SELECT TOP 1 SUM(Cd1.Price) 
    FROM Purchase Purchase1
    INNER JOIN Personal Personal1 ON Purchase1.Salesperson_id = Personal1.Personal_id 
    RIGHT OUTER JOIN shop Shop1 ON Personal1.work_id = Shop1.shop_id 
    FULL OUTER JOIN Cd Cd1 ON Purchase1.Cd_id = Cd1.Cd_id 
    WHERE Shop1.Name = Shop.Name 
    GROUP BY Shop1.Name, Personal1.Name 
    ORDER BY SUM(Cd1.Price) DESC)
ORDER BY Sales DESC

Royal
Starting Member

32 Posts

Posted - 2006-12-28 : 14:17:22

Thank you so much!!
Somehow this last part is not accepted by Reporting services;
HAVING SUM(Cd.Price) = (SELECT TOP 1 SUM(Cd1.Price)
FROM Purchase Purchase1
INNER JOIN Personal Personal1 ON Purchase1.Salesperson_id = Personal1.Personal_id
RIGHT OUTER JOIN shop Shop1 ON Personal1.work_id = Shop1.shop_id
FULL OUTER JOIN Cd Cd1 ON Purchase1.Cd_id = Cd1.Cd_id
WHERE Shop1.Name = Shop.Name
GROUP BY Shop1.Name, Personal1.Name
ORDER BY SUM(Cd1.Price) DESC)
ORDER BY Sales DESC

..then this Datepart confuses me. When running in the Result Pane, it starts with January. When showing the layout in the Preview it start mith April... How come?

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-28 : 14:37:12

When you say it is not accepted by Reporting Services - do you mean the in the Graphical Query Designer? You'll need to use the Generic Query Designer for this query because it uses a subquery, which is not supported by the Graphical Query Designer. The query itself should be accepted by Reporting Services.

Do you have a sort on your report, if so it will sort by the text that is returned by the query, and it will be alphabetical. Don't sort in the report because the query is already doing the sorting.

Royal
Starting Member

32 Posts

Posted - 2006-12-30 : 10:14:39

Thank you so much! Now it works fine!!!

Then I wonder how to make a list, starting from the end-results.
Today I have a list of best CD-sales result where=RowNumber(Nothing) returns a list of 1,2,3.. instead of sales-sums. Now I would like to turn the list, sort the worst sales result starting with 52,51,50, 49...

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2006-12-30 : 17:52:48

I'm not sure I understand, either sort with ORDER BY and no DESC (for ascending) or sort with ORDER BY and DESC for descending.

Royal
Starting Member

32 Posts

Posted - 2006-12-31 : 05:21:41

Thank you so much...
order by DESC returns a CD-list from 1, 2, 3 ...51.
But how do I turn the list (the last 10), create a list of the last CD's;
51 , 50, 49, 48, 47, 46, 45, 44, 43, 42

snSQL
Master Smack Fu Yak Hacker

1837 Posts

Posted - 2007-01-03 : 00:29:12

That doesn't make any sense though because DESC means descending and the list should be in descending order just like you want, so you need to check that you have the DESC in the right place in the ORDER BY immediately after the name of the column you want to sort in descending order. Post the query exactly as you now have it.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-03 : 04:23:47

select * from (select top 10 * from cdlist order by cdnum desc) order by cdnum

Peter Larsson
Helsingborg, Sweden

Royal
Starting Member

32 Posts

Posted - 2007-01-03 : 14:50:28

thank you! Something like this?
SELECT SUM(Cd.Price) AS Sales, Artist.Name
FROM Cd INNER JOIN
purchase ON Cd.Cd_id = purchase.Cd_id INNER JOIN
Cd_artist ON Cd.Cd_id = Cd_artist.Cd_id INNER JOIN
Artist ON Cd_artist.Artist_id = Artist.Artist_id
GROUP BY Artist.Name
HAVING SUM(Cd.Price)=select * from (select top 10 * from cdlist order by cdnum desc) order by cdnum
ORDER BY Sales DESC

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-03 : 14:58:33

SELECT top 10 SUM(Cd.Price) AS Sales, Artist.Name
FROM Cd INNER JOIN
purchase ON Cd.Cd_id = purchase.Cd_id INNER JOIN
Cd_artist ON Cd.Cd_id = Cd_artist.Cd_id INNER JOIN
Artist ON Cd_artist.Artist_id = Artist.Artist_id
GROUP BY Artist.Name
ORDER BY SUM(Cd.Price) DESC

Peter Larsson
Helsingborg, Sweden

Royal
Starting Member

32 Posts

Posted - 2007-01-08 : 13:33:16

I meant a list like this... a list starting with the least sold
CD's. Due to "=RowNumber(Nothing)" numbers are return instead of salesfigures.But how does the list start from the end??

Least sold cd
51 Martin
50 Elvis
49 Diva
.
.
.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 13:35:44

Make the ORDER BY DESC instead of default ASC.

Peter Larsson
Helsingborg, Sweden

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-08 : 18:50:23

Do the rownumber thingy in frontend application!

Peter Larsson
Helsingborg, Sweden

Royal
Starting Member

32 Posts

Posted - 2007-01-09 : 13:35:08

Hmmm... frontend? How do you mean?
I have "=RowNumber(Nothing)"

The right version would be...
ORDER BY SUM(Cd.Price) ASC AND order BY ROWNUMBER DESC

(returning the highest rownumber)

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-01-09 : 14:32:22

[code]DECLARE @Stage TABLE (RowID INT IDENTITY(1, 1), Sales MONEY, ArtistName VARCHAR(100))

INSERT @Stage
(
Sales,
ArtistName
)
SELECT SUM(Cd.Price),
Artist.Name
FROM Cd
INNER JOIN purchase ON Cd.Cd_id = purchase.Cd_id
INNER JOIN Cd_artist ON Cd.Cd_id = Cd_artist.Cd_id
INNER JOIN Artist ON Cd_artist.Artist_id = Artist.Artist_id
GROUP BY Artist.Name
ORDER BY SUM(Cd.Price) DESC

SELECT RowID,
Sales,
ArtistName
FROM @Stage
WHERE RowID IN (SELECT TOP 10 RowID FROM @Stage ORDER BY RowID DESC)
ORDER BY RowID DESC[/code]

Peter Larsson
Helsingborg, Sweden

Royal
Starting Member

32 Posts

Posted - 2007-01-14 : 13:23:29

Thank you so much! Now it works fine!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources