Query Help - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Query Help

Author

Topic

winDows
Starting Member

9 Posts

Posted - 2008-08-13 : 17:24:22

Hi,

Trying to get the current quarter and previous quater amounts from the Modifications
table and the current and previous values are in the same coulmn QAMT in the table.
As per the below query i'm able to get the QAMT for the value of param_quarter.
i mean if it is 4 then i can get QAMT for 4th quarter.

SELECT QAMT as current
FROM Modifications
INNER JOIN SInformation ON
Modifications.SId = SInformation.SId
INNER JOIN FPeriods ON
Modifications.FYCQ = FPeriods.FYear
WHERE (FPeriods.id in (param_quarter))

But the requirement is when user selected 4 i have to get the amount for the previous quarter '1' also, if 3 then 2 .
And The quarters are assigned for 10 years,

1 to 4 for 2000
5 to 8 for 2001

upto

37 to 40 for 2010

and the previous quarter amount should also get in a different coulumn in the same select statement.

Please Help me to write this query. Thanks.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-14 : 04:33:24

Hi winDows. I'm more than a little hampered by the fact that I can't tell from your description which columns belong to which tables.

Can you post some table structure info and some sample data. Then post what you require the sample data to look like.

This is probably too simplistic for your needs but you never know


SELECT
	m.QAMT as currentPeriod
	, mPRev.QAMT AS lastPeriod
FROM
	Modifications m
	INNER JOIN SInformation si ON m.SId = si.SId
	INNER JOIN FPeriods fp ON m.FYCQ = fp.FYear

	LEFT JOIN Modifications mPrev ON mPrev.[FYCQ] = fp.[FYear] - 1
WHERE
	fp.id in (XXX.param_quarter)

(I couldn't tell which table param_quarter is a column of so I've prefixed it with XXX -- change this to the relevant alias.

NB :: This probably won't work as I don't know your table structures.

Regards,

-------------
Charlie

winDows
Starting Member

9 Posts

Posted - 2008-08-14 : 10:32:56

Thanks for your help and here few sample data,

Modifications Table

FYCQ FQCQ QAMT
2007 1 1000
2007 1 2000
2007 2 1000
2007 2 2000
2007 3 1000
2007 3 2000
2007 4 4000
2007 4 7000
2008 1 8000
2008 1 9000
2008 2 1000
2008 2 3000
2008 3 2000
2008 3 3000
2008 4 3000
2008 4 2000

Fyear Tableid

id fyear quarter

1 2007 1
2 2007 2
3 2007 3
4 2007 4
5 2008 1
6 2008 2
7 2008 3
8 2008 4

output when user enterd for year 2008 and quarter 1 , so the previous quarter wille be 2007 -Q4

QAmt Current QAmt Previous

8000 4000
9000 7000

and there will be more number of records in the output based on the 'SID' value (this will be a parameter to the query)
in the Modifications table which i didn't include in this. i'm not sure whether i have given you enough information.

Thanks.

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2008-08-14 : 10:57:55

Hi winDows.

Thanks for posting that, it's a start.

What you've posted though doesn't in any way correspond to your query. What uniquely identifies a row in the modification table for instance? At the moment what you've posted doesn't have any way to differentiate between the two quarterly entries for 2008 quarter 2 for example

2008 2 1000
2008 2 3000

Which one should you report on and how do you relate the previous quarter to it?

In your query sample you reference a column called SId but you haven't included this.

Can you please post your *complete* table deceleration for each table and complete sample data for each?

-------------
Charlie

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-14 : 14:04:48

[code]SELECT m.QAMT AS [QAmt Current],
b.QAMT AS [ QAmt Previous]
FROM Modifications m
INNER JOIN Fyear f
ON f.fyear=m.FYCQ
and f.quarter=m.FQCQ
OUTER APPLY(SELECT TOP 1 m1.QAMT FROM
Modifications m1
INNER JOIN Fyear f1
ON f1.fyear=m1.FYCQ
and f1.quarter=m1.FQCQ
WHERE f1.id<f.id
ORDER BY f1.id DESC)b
WHERE f.fyear=@year
AND f.quarter=@quarter[/code]

winDows
Starting Member

9 Posts

Posted - 2008-08-14 : 15:42:20

Hi, here the query which returns me the current quarter amout,

SELECT Modifications.PRCQ
FROM Modifications INNER JOIN
SInfo ON Modifications.SId = SInfo.SId INNER JOIN
Task ON Modifications.TaskId = Task.TaskId INNER JOIN
TCom ON SInfo.TComId = TCom.TComId INNER JOIN
SType ON SInfo.STypeId = SType.STypeId INNER JOIN
CUpType ON SInfo.CUpTypeId = CUpType.CUpTypeId INNER JOIN
FPeriods ON Modifications.FYCQ = FPeriods.FYear AND
Modifications.FQCQ = FPeriods.Quarter INNER JOIN
StatType ON SInfo.StatTypeId = StatType.StatTypeId INNER JOIN
SubTask ON Modifications.SubTaskId = SubTask.SubTaskId AND Task.TaskId = SubTask.TaskId
WHERE (FPeriods.id in (' + @cbGetQtr + ' )) AND (SInfo.SId in (' + @Sid + ')) AND (Task.TaskId in (' + @Tlist + ') )

Based on the ' @cbGetQtr ' value , i have to get the previous quarter amount and there will be multiple records for all quarters based on the Taskid and Sid -- Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources