Query Question - 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 2008 Forums

Transact-SQL (2008)

Query Question

Author

Topic

viperbyte
Posting Yak Master

132 Posts

Posted - 2013-05-23 : 21:01:30

Hello everyone.

I hope an SQL pro can tell me if this is possible or not. I'm trying to do a SQL statement that I can use with Crystal Reports that is kind of funky. I know this is not a CR forum but this is an SQL query question. I have two tables for this query in which one of them(Invoice Header) has periods which has a value that can be a number from 1 to 12. I need to be able to prompt the user for the quarter but we don't store quarters we store periods(1 - 12) in the db. If a user selects 1 then that would mean period 1, 2 or 3 needs to be part of the report. If the user selects quarter 2 then period 4, or 5 or 6 fits the bill and so on. I have an invoice header and and invoice details linked by invNumber. Can a SQL statement be written that will provide the result set structered this way? And if so can someone super please show me how or provide a link?

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2013-05-24 : 00:24:26

basically, it's between (@qtr * 3)-2 AND @qtr * 3


-- set up test table and populate --
Create Table #t ([month] int);

INSERT #t VALUES (1)
INSERT #t VALUES (2)
INSERT #t VALUES (3)
INSERT #t VALUES (4)
INSERT #t VALUES (5)
INSERT #t VALUES (6)
INSERT #t VALUES (7)
INSERT #t VALUES (8)
INSERT #t VALUES (9)
INSERT #t VALUES (10)
INSERT #t VALUES (11)
INSERT #t VALUES (12)

-- Take quarter (@qtr) as a paramater
-- and return correct months
DECLARE	@qtr int
SET	@qtr = 1

WHILE	@qtr < 5
BEGIN
	SELECT *, @qtr [quarter] FROM #T where [month] between (@qtr * 3)-2 AND @qtr * 3
	SET @qtr = @qtr + 1
END

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-24 : 00:34:56

its possible
stub will look like below


SELECT other columns...,
CASE 
WHEN Period BETWEEN 1 AND 3 THEN 1
WHEN Period BETWEEN 4 AND 6 THEN 2
WHEN Period BETWEEN 7 AND 9 THEN 3
WHEN Period BETWEEN 10 AND 12 THEN 4
END AS Qr
FROM [Invoice Header]  ih
INNER JOIN [Invoice Details] id
ON id.invNumber = ih.invNumber
..
WHERE CASE 
WHEN Period BETWEEN 1 AND 3 THEN 1
WHEN Period BETWEEN 4 AND 6 THEN 2
WHEN Period BETWEEN 7 AND 9 THEN 3
WHEN Period BETWEEN 10 AND 12 THEN 4
END = @YourQuarterParameter
AND ... other conditions

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

viperbyte
Posting Yak Master

132 Posts

Posted - 2013-05-24 : 05:58:34

Thanks for the replies and help folks. I'll try to put your help in code later in the weekend if not today. Once again, THANKS :)

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-05-25 : 09:37:42

welcome

let us know how you got on!

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources