help with a query
-
hello,
Here is what I have so far
SELECT [city_code], [outlet_code], sum([sold_units]) as 'units sold 01-04'
FROM [MerchDB].[dbo].[tblOutletSales]
where active = 'yes'
and issue_year = 2004
and issue between 1 and 4
group by city_code, outlet_code
order by city_code, outlet_code
what I would like to do is have more sold units columns for different issue and issue year periods so the end result would look something like
city_code outlet_code sold units 01-04 sold units 05-08 and so on
any help would be apreciated
thanks
-
Why don't you add a column Period and another group by by this period?
-
sounds like a good plan. I am unsure how though is that inside the Sum function?
thanks
-
This will need some tweaking but it can give you an idea :
SELECT [city_code], [outlet_code], sum([sold_units]) as UnitsSold, CAST(Issue - 1 AS INT) / 4 AS UnitPeriod
FROM [MerchDB].[dbo].[tblOutletSales]
where active = 'yes'
and issue_year = 2004
group by city_code, outlet_code, CAST(Issue - 1 AS INT) / 4
order by city_code, outlet_code
-
pardon my newbieness but im not sure exactly what the cast part of that shows but maybe if I am more specific i can help some
I am trying to see in a row the city outlet and various periods of sales data going further out the row.
the issue is an int between 1 and 53 and the issue year is the year which I believe is an Int in this database.
I orginally thought I could use the sum function like this
select city, outlet, sum(issue_year = 2004, issue between 1 and 4, units_sold) , issues 5-8 and so on
thanks for your help
-
Well, the problem seems to be that we don't know for sure how many columns there will be. Do YOU know that? If not, then you can't do it in SQL that way. You can not write a query that depending on situation will have 5 to 40 columns... unless you use dynamic SQL - but that should only be used if there is no other way. And if you want to have a column for every 4 issues (up to 53), and maybe sometimes for several years also, then that's a lot of columns.
Standard SQL result looks differently - instead of a few rows and many columns, there would be just a few columns and more rows. That is, not sales01, sales02, sales03 etc., but just one column "sales", and another column "sales_period" or "issue" (or maybe "issue_group", with values '1-4', '5-8' etc.). You can then manipulate this result in a second step, to get the required presentation in a table... but that could be done in some front-end tool, not necessarily via SQL.
Maybe I didn't understand the question correctly; in that case, please try to give us some sample data and the desired result.
-
I believe the most columns this query will have will be 26. the first 2 are city and outlet information and the next 24 would be sales info with the periods being 1-4, 5-8, 9-13 so 4 weeks 4 weeks and 5 weeks then the pattern repeats. and I would like to do it with the first weeks being 2004 then 2005 however I am open to just pulling all the data for one year and joing it together in access but i thought that there must be a better way to do it rather than run 12 queries for each year.
if it helps the table I am trying to pull the data from looks like this
[city_code], [outlet_code], [book_code], [issue_year], [issue], [sold_units], [active]
and hopefully the output will be
[city_code], [outlet_code], soldunits peroid 1, sold units period 2.......
thanks for all your help
-
If you had a date column you could do group by month(date).
-
well the combination of the issue_year and issue is sort of a date it points to a specific week in each year ie issue 1-4 = Jan, 5-8= Feb, 9-13=Mar
-
Not really because you have 52 weeks in a year and 12 months times 4 = 48.
Can you show us some sample data so we can find a work around?
-
I understand a bit more now... it is rather unfortunate solution to have "issue" instead of a simple datetime column. Is it the same in every year, that is, the weeks 9-13 are ALWAYS counted together? So the March for you has 5 weeks...? Sometimes the year starts on Monday, sometimes on Friday - won't that cause any changes in the pattern?
If not, and if you will use the query always for just one calendar year, then you could for instance write a CASE statement for every sales column, like this:
CASE WHEN issue BETWEEN 1 AND 4 THEN sold_units ELSE 0 as [sold01-04]
and then sum these new columns. This means you need 24 such CASE statements... not really easy to maintain such code.
Vladan
-
ok here is part of issue 1 of outlet 1 in city 101
[city_code], [outlet_code], [book_code], [issue_year], [issue], [sold_units], [active]
101, 1, FD, 2005, 1, 2, yes
101, 1, AQ, 2005, 1, 7, yes
101, 1, AK, 2005, 1, 9, yes
and yes the issue always points toward the same time each year and the way the offset year are dealt with is every third year has an extra week (issue) #53 in Dec in the years without that issue it stays blank so 2004 has an issue 53 but 2005 will not
and the way months are calculated is the the first 2 monthes have 4 weeks (issues) then the third month has 5.
-
The best solution would be to create a new ReleaseDate column (assuming you can't delete/replace the issue column). All you'd have to do is figure out the first release date of the year then dateadd weeks from the first ReleaseDate with somethine like :
Update YourTable set ReleaseDate = DateAdd(w, issue_number - 1, @FirstReleaseDate)
After that you could group by month without ever worrying about the number of weeks in the month.
-
Hmm.. I don't know, Remi... it seems they have their own business logic, with months absolutely independent on real calendar
maybe setting things right would create confusion.If we suppose that you don't want to (or can't) change anything, try this:
SELECT Q.city_code, Q.outlet_code, SUM(Q.sold0104), SUM(Q.sold0508),SUM(Q.sold0913),SUM(Q.sold1417),SUM(Q.sold1821)
FROM
(select [city_code], [outlet_code],
CASE WHEN issue BETWEEN 1 AND 4 THEN sold_units ELSE 0 END as sold0104,
CASE WHEN issue BETWEEN 5 AND 8 THEN sold_units ELSE 0 END as sold0508,
CASE WHEN issue BETWEEN 9 AND 13 THEN sold_units ELSE 0 END as sold0913,
CASE WHEN issue BETWEEN 14 AND 17 THEN sold_units ELSE 0 END as sold1417,
CASE WHEN issue BETWEEN 18 AND 21 THEN sold_units ELSE 0 END as sold1821
from [MerchDB].[dbo].[tblOutletSales]
where active = 'yes' and issue_year = 2004) AS Q
GROUP BY city_code, outlet_code
ORDER BY city_code, outlet_code
-
Well now he has 2 solutions... his choice to make. But for the sake of future requirements I'd add the releasedate column and at least try to "normalize" the query.
Viewing 15 posts - 1 through 15 (of 26 total)
You must be logged in to reply to this topic. Login to reply