January 2, 2013 at 10:57 am
use adventureworks
go
select
YEAR(OrderDate) as orderyear,
SUM(case MONTH(orderdate) when 1 then TotalDue end) as January,
SUM(case MONTH(orderdate) when 2 then TotalDue end) as February,
SUM(case MONTH(orderdate) when 3 then TotalDue end) as March,
SUM(case MONTH(orderdate) when 4 then TotalDue end) as April,
SUM(case MONTH(orderdate) when 5 then TotalDue end) as May,
SUM(case MONTH(orderdate) when 6 then TotalDue end) as June,
SUM(case MONTH(orderdate) when 7 then TotalDue end) as July,
SUM(case MONTH(orderdate) when 8 then TotalDue end) as August,
SUM(case MONTH(orderdate) when 9 then TotalDue end) as September,
SUM(case MONTH(orderdate) when 10 then TotalDue end) as October,
SUM(case MONTH(orderdate) when 11 then TotalDue end) as November,
SUM(case MONTH(orderdate) when 12 then TotalDue end) as December
from Sales.SalesOrderHeader
group by YEAR(OrderDate)
order by orderyear
Below query gives the total due based on the year and month wise
i want to write a query which will return results according to below
1. if a select month as February the results should only be up to January
and if i select march the query should give results up to February
and end column should give the total
how to come up with the logic
Ex
Order Year January Total
January 2, 2013 at 12:32 pm
Smash125, this is untested as I don't have AdventureWorks, but I think you will be able to gleen something from it. Also, you did not mention anything about year, but I suspect that will be an issue at some point. @year and @month will be parameters supplied by you.
SELECT
SUM(TotalDue) AS Total
FROM
(
SELECT
YEAR(orderdate) AS YR,
MONTH(orderdate) AS MO,
TotalDue
FROM Sales.SalesOrderHeader
) t1
WHERE t1.yr = @year
AND t1.mo < @month
GROUP BY t1.yr, t1.mo
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 2, 2013 at 7:11 pm
This is not the most elegant or optimized approach but this will get you the result set that you are looking for...
--Parameter
DECLARE @topMonth int=3;
--if a valid month is not selected, return all months
IF @topMonth<=0 OR @topMonth>12 SET @topMonth=12
DECLARE @x varchar(2000), @x2 varchar(1000), @i int=1,
@p1 varchar(40)='SUM(case MONTH(orderdate) when ',
@p2 varchar(50)='',
@months varchar(300)='(';
IF OBJECT_ID('tempdb..##tmp') IS NOT NULL
DROP TABLE ##tmp;
SET @x='SELECT YEAR(OrderDate) as orderyear, '+CHAR(13);
WHILE @i<=@topMonth
BEGIN
SET @x=@x+@p1+CAST(@i AS varchar(2))+' then TotalDue end) as '
+CAST(DATENAME(MONTH,(CAST(@i AS varchar(2))+'/1/2000')) AS varchar(20))
+CASE WHEN @i<>@topMonth THEN ','+CHAR(13) ELSE CHAR(13) END
SET @months=@months+CAST(DATENAME(MONTH,(CAST(@i AS varchar(2))+'/1/2000')) AS varchar(20))+
CASE WHEN @i<>@topMonth THEN '+' ELSE ')' END
SET @i=@i+1
END;
SELECT@x=@x+'INTO ##tmp FROM Sales.SalesOrderHeader GROUP BY YEAR(OrderDate) ORDER BY orderyear',
@x2 = 'SELECT *, '+@months+' AS GrandTotal FROM ##tmp ORDER BY orderyear'
EXEC(@x);
EXEC(@x2);
DROP TABLE ##tmp;
-- Itzik Ben-Gan 2001
January 3, 2013 at 10:45 am
CELKO (1/2/2013)
Since SQL is a database language, we prefer to do look ups and not calculations.
That "we" is undefined and thus meaningless. But people who have to write and run applications in the real world know that the I/O from any lookup is orders of magnitude more overhead than all but the most extreme calculations.
Apparently CELKO doesn't understand that computers perform billions of mathematical calculations per second.
Where possible, use calculations when appropriate rather than wasting extremely expensive I/O to do things that can be done with a nanosecond or microsecond calculation.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 3, 2013 at 11:38 pm
ScottPletcher (1/3/2013)
CELKO (1/2/2013)
Since SQL is a database language, we prefer to do look ups and not calculations.
That "we" is undefined and thus meaningless. But people who have to write and run applications in the real world know that the I/O from any lookup is orders of magnitude more overhead than all but the most extreme calculations.
Apparently CELKO doesn't understand that computers perform billions of mathematical calculations per second.
Where possible, use calculations when appropriate rather than wasting extremely expensive I/O to do things that can be done with a nanosecond or microsecond calculation.
I believe that was intended to be the "royal" we.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 4, 2013 at 3:50 am
dwain.c (1/3/2013) I believe that was intended to be the "royal" we.
Oh my goodness, Dwain. We laughed hard when we read that one.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
January 4, 2013 at 8:43 am
dwain.c (1/3/2013)
ScottPletcher (1/3/2013)
CELKO (1/2/2013)
Since SQL is a database language, we prefer to do look ups and not calculations.
That "we" is undefined and thus meaningless. But people who have to write and run applications in the real world know that the I/O from any lookup is orders of magnitude more overhead than all but the most extreme calculations.
Apparently CELKO doesn't understand that computers perform billions of mathematical calculations per second.
Where possible, use calculations when appropriate rather than wasting extremely expensive I/O to do things that can be done with a nanosecond or microsecond calculation.
I believe that was intended to be the "royal" we.
But as CELKO is not royalty, that doesn't apply :-).
For example, plenty of people believe they are Napolean, but of course it doesn't actually make them Napolean!
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 4, 2013 at 10:36 am
CELKO (1/4/2013)
Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise.CREATE TABLE Something_Report_Periods
(something_report_name CHAR(10) NOT NULL PRIMARY KEY
CHECK (something_report_name LIKE <pattern>),
something_report_start_date DATE NOT NULL,
something_report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (something_report_start_date <= something_report_end_date),
etc);
These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[0-3][0-9]-00'
Starting to look like "bot" posts -- the same ridiculous nonsense repeated ad infinitum.
NOTHING in that post is really true.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
January 4, 2013 at 5:50 pm
ScottPletcher (1/4/2013)
CELKO (1/4/2013)
Since SQL is a database language, we prefer to do look ups and not calculations. They can be optimized while temporal math messes up optimization. A useful idiom is a report period calendar that everyone uses so there is no way to get disagreements in the DML. The report period table gives a name to a range of dates that is common to the entire enterprise.CREATE TABLE Something_Report_Periods
(something_report_name CHAR(10) NOT NULL PRIMARY KEY
CHECK (something_report_name LIKE <pattern>),
something_report_start_date DATE NOT NULL,
something_report_end_date DATE NOT NULL,
CONSTRAINT date_ordering
CHECK (something_report_start_date <= something_report_end_date),
etc);
These report periods can overlap or have gaps. I like the MySQL convention of using double zeroes for months and years, That is 'yyyy-mm-00' for a month within a year and 'yyyy-00-00' for the whole year. The advantage is that it will sort with the ISO-8601 data format required by Standard SQL. The pattern for validation is '[12][0-9][0-9][0-9]-00-00' and '[12][0-9][0-9][0-9]-[0-3][0-9]-00'
Starting to look like "bot" posts -- the same ridiculous nonsense repeated ad infinitum.
NOTHING in that post is really true.
!!!!ROTFLMAO!!!! :hehe::-P:hehe::-P:hehe::-P
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply