How do I get two sets of rows in a single output? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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

General SQL Server Forums

New to SQL Server Programming

How do I get two sets of rows in a single output?

Author

Topic

rahamanf
Starting Member

15 Posts

Posted - 2008-04-11 : 16:38:09

Hello,

I want two different set of rows in a single output. For example - the query gets records from the same tables, but first condition is a date range of 60 days and value = '1'
then the second condition is a date range of 180 days and value = '2'

Is it possible?

Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 16:42:03

Yes.

SELECT SUM(CASE WHEN Date >= DATEADD(DAY, -60, GETDATE()) AND Value = 1 THEN 1 ELSE 0 END),
SUM(CASE WHEN Date >= DATEADD(DAY, -180, GETDATE()) AND Value = 2 THEN 1 ELSE 0 END)
FROM Table1

E 12°55'05.25"
N 56°04'39.16"

rahamanf
Starting Member

15 Posts

Posted - 2008-04-11 : 16:57:08

SUM here is not adding anything is it? Sorry for the stupid question.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-04-11 : 17:02:16

You didn't specify what you want, so I just picked a query.

SELECT *
FROM Table1
WHERE (Date >= DATEADD(DAY, -60, GETDATE()) AND Value = 1)
OR (Date >= DATEADD(DAY, -180, GETDATE()) AND Value = 2)

E 12°55'05.25"
N 56°04'39.16"

rahamanf
Starting Member

15 Posts

Posted - 2008-04-11 : 17:07:54

Great!..it will be very helpful.
Thanks, Peso.

rahamanf
Starting Member

15 Posts

Posted - 2008-04-11 : 18:09:43

I have a join in the query, and it happens to be the column that I am using in the WHERE clause, so I am getting repeating rows with wrong value for that same column. Here's the statement:
SELECT SUBSTATUS_DESC,FILE_NAME,DATE,SUBMITTEDBY,STATION_ID,
SUBMIT_ID FROM SUBMITHISTORY_INFO SI,SUBMITSTATUS_CODES SC
WHERE SI.STATUS_ID = SC.SUBSTATUS_CODE AND (SUBSTATUS_DESC = 'Failed Prevalidation') OR (SUBSTATUS_DESC = 'Failed Validation (minor)')
OR (SUBSTATUS_DESC = 'Failed Validation (major)')OR (SUBSTATUS_DESC = 'Calcelled') OR (SUBSTATUS_DESC = 'Deleted') OR ((Date >= DATEADD(DAY, -60, GETDATE()) AND SUBSTATUS_DESC = 'Passed Prevalidation') OR (DATE >= DATEADD(DAY, -60, GETDATE()) AND SUBSTATUS_DESC = 'Passed Validation') OR (DATE >= DATEADD(DAY, -60, GETDATE()) AND SUBSTATUS_DESC = 'Passed') OR (DATE >= DATEADD(DAY, -60, GETDATE()) AND SUBSTATUS_DESC = 'Currently Validating'));

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-04-12 : 01:39:57

I would have written it like this:-

SELECT SUBSTATUS_DESC,FILE_NAME,DATE,SUBMITTEDBY,STATION_ID,
SUBMIT_ID 
FROM SUBMITHISTORY_INFO SI
INNER JOIN SUBMITSTATUS_CODES SC 
ON SI.STATUS_ID = SC.SUBSTATUS_CODE 
WHERE  SUBSTATUS_DESC IN( 'Failed Prevalidation','Failed Validation (minor)','Failed Validation (major)','Calcelled','Deleted') 
OR 
(Date >= DATEADD(DAY, -60, GETDATE()) AND SUBSTATUS_DESC IN( 'Passed Prevalidation','Passed Validation','Passed','Currently Validating'));

Now your problem. Why do you think your are getting wrong values. Can you make it clear with some sample data of your tables?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources