sql server programming: Query Help Required -- sql server programming

Query Help Required

Bhaskar Parsi
9/22/2003 10:56:38 PM

Hi Satish

Try using something like this to get the desired...

SELECT TOP 4 Qtr, year, SUM(Amount) FROM tblTest
WHERE Qtr >=2 and Year>=2002
GROUP BY year,Qtr
order by year,Qtr

Bash,
DBA

[quoted text, click to view]

Query Help Required

Satish
9/22/2003 11:10:26 PM

Hi Bhaskar,
Thanks for your reply to my post. Your query does
not fetch the required results. It actually fetches
records like this:

Qtr Year Sum_Amount
2 2002 2200.0 -- This is correct
3 2002 3300.0 -- This is correct
4 2002 4400.0 -- This is correct
2 2003 6600.0 -- This is should be Qtr 1 and Year 2003

Practically speaking Qtr 4 Year 2002 is followed by Qtr 1
Year 2003, but how to implement it in the query???

Satish

[quoted text, click to view]

Re: Query Help Required

oj
9/22/2003 11:17:43 PM

not quite...

declare @qtr int, @year int
select @qtr=2, @year=2002
select top 4 qtr,year,sum(amount) totals
from tblTest
where (qtr>=@qtr and year=@year) or (qtr<@qtr and year>@year)
group by qtr,year
order by year asc,qtr asc

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

[quoted text, click to view]

Re: Query Help Required

oj
9/23/2003 10:29:56 AM

Hmmm...I don't see my posts from last night...

Anyway, here are two others that are more accurate than my previous...

declare @qtr int, @year int, @i int
select @qtr=2, @year=2002, @i=6

--#1 pick the next @i available qtr
set rowcount @i
select qtr,[year],sum(amount) totals
from tblTest
where dateadd(q,qtr,cast(cast([year] as varchar)as
datetime)-1)>=dateadd(q,@qtr,cast(cast(@year as varchar)as datetime)-1)
group by qtr,year
order by year asc,qtr asc
set rowcount 0

--#2 pick the next @i qtr
select qtr,[year],sum(amount) totals
from tblTest
where dateadd(q,qtr,cast(cast([year] as varchar)as datetime)-1) between
dateadd(q,@qtr,cast(cast(@year as varchar)as datetime)-1) and
dateadd(q,@i-1,dateadd(q,@qtr,cast(cast(@year as varchar)as datetime)))
group by qtr,year
order by year asc,qtr asc

--
-oj
RAC v2.2 & QALite!
http://www.rac4sql.net

[quoted text, click to view]

Re: Query Help Required

Vishal Parkar
9/23/2003 1:18:37 PM

Try:

declare @x int, @yr int
set @x=2 --pass quarter
set @yr = 2002 --pass year
select qtr, year, sum(amount)
from tbltest
where (((qtr between @x and 4) and @x >=2 and year = @yr)
OR ((qtr between 1 and (@x - 1)) and @x >=2 and year = (@yr + 1)))
OR (((qtr between @x and 4) and @x < 2 and year = @yr))
group by qtr, year

--
- Vishal

Re: Query Help Required

Alejandro Mesa
9/23/2003 2:27:13 PM

Try,

declare @year int, @qtr int, @num int, @rows int

set @year =3D 2002
set @qtr =3D 2
set @num =3D @year * 10 + @qtr
set @rows =3D 4

set rowcount @rows

select [year], qtr, sum(amount) as sum_amount
from tblTest
where [year] * 10 + qtr >=3D @num
group by [year], qtr
order by 1, 2
go

Re: Query Help Required

Louis Davidson
9/23/2003 11:36:54 PM

Please post DDL and an insert script to load your data. It will make it
easier for us to help you. Then we can run queries on the data and make
sure that our solution works

--
----------------------------------------------------------------------------
-----------
Louis Davidson (drsql@hotmail.com)
Compass Technology Management

Pro SQL Server 2000 Database Design
http://www.apress.com/book/bookDisplay.html?bID=266

[quoted text, click to view]

Re: Query Help Required

John Gilson
9/24/2003 3:13:25 AM

[quoted text, click to view]

CREATE TABLE QuarterlyAmounts
(
id INT NOT NULL IDENTITY PRIMARY KEY,
qtr INT NOT NULL CHECK (qtr BETWEEN 1 AND 4),
year INT NOT NULL,
amount FLOAT NOT NULL
)

INSERT INTO QuarterlyAmounts (qtr, year, amount)
VALUES (1, 2002, 100.0)
INSERT INTO QuarterlyAmounts (qtr, year, amount)
VALUES (2, 2002, 200.0)
INSERT INTO QuarterlyAmounts (qtr, year, amount)
VALUES (3, 2002, 300.0)
INSERT INTO QuarterlyAmounts (qtr, year, amount)
VALUES (4, 2002, 400.0)
INSERT INTO QuarterlyAmounts (qtr, year, amount)
VALUES (1, 2002, 1000.0)
INSERT INTO QuarterlyAmounts (qtr, year, amount)
VALUES (2, 2002, 2000.0)
INSERT INTO QuarterlyAmounts (qtr, year, amount)
VALUES (3, 2002, 3000.0)
INSERT INTO QuarterlyAmounts (qtr, year, amount)
VALUES (4, 2002, 4000.0)
INSERT INTO QuarterlyAmounts (qtr, year, amount)
VALUES (1, 2003, 500.0)
INSERT INTO QuarterlyAmounts (qtr, year, amount)
VALUES (2, 2003, 600.0)
INSERT INTO QuarterlyAmounts (qtr, year, amount)
VALUES (3, 2003, 700.0)
INSERT INTO QuarterlyAmounts (qtr, year, amount)
VALUES (4, 2003, 800.0)
INSERT INTO QuarterlyAmounts (qtr, year, amount)
VALUES (1, 2003, 5000.0)
INSERT INTO QuarterlyAmounts (qtr, year, amount)
VALUES (2, 2003, 6000.0)
INSERT INTO QuarterlyAmounts (qtr, year, amount)
VALUES (3, 2003, 7000.0)
INSERT INTO QuarterlyAmounts (qtr, year, amount)
VALUES (4, 2003, 8000.0)

CREATE VIEW OrderedQuarters (year, qtr, amount, seq)
AS
SELECT Q1.year, Q1.qtr, Q1.amount,
COUNT(*) - SUM(CASE WHEN Q2.year = Q1.year
THEN 1
ELSE 0
END) + Q1.qtr
FROM (SELECT year, qtr, SUM(amount)
FROM QuarterlyAmounts
GROUP BY year, qtr) AS Q1(year, qtr, amount)
INNER JOIN
(SELECT year, qtr, SUM(amount)
FROM QuarterlyAmounts
GROUP BY year, qtr) AS Q2(year, qtr, amount)
ON Q2.year <= Q1.year
GROUP BY Q1.year, Q1.qtr, Q1.amount

SELECT Q1.year, Q1.qtr,
SUM(Q2.amount) AS total, COUNT(*) AS num_qrts
FROM OrderedQuarters AS Q1
INNER JOIN
OrderedQuarters AS Q2
ON Q2.seq BETWEEN Q1.seq AND Q1.seq + 3
GROUP BY Q1.year, Q1.qtr
ORDER BY Q1.year, Q1.qtr

year qtr total num_qrts
2002 1 11000.0 4
2002 2 15400.0 4
2002 3 19800.0 4
2002 4 24200.0 4
2003 1 28600.0 4
2003 2 23100.0 3
2003 3 16500.0 2
2003 4 8800.0 1

Regards,
jag

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.