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

General SQL Server Forums

New to SQL Server Programming

Help with Query?

Author

Topic

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-10 : 02:44:52

Hi,
I am using a Table named Details where I have columns DateReceived, LU and status.Now i want to fire a query where

datereceived + 7 days and local = L and status = P
also datereceived + 15 days for urban = U and status = P.


select (varchar(20),DateReceived ,102)as DateReceived,status,
		count (CASE WHEN DATERECEIVED > 7 AND LU = 'L'   THEN 1  END)'Within 7 Days',
		count (CASE WHEN DATERECEIVED > 15 AND LU = 'U' THEN 1   END)'Within 15 DAYS'
		from details
		WHERE STATUS = 'P'

COuld anyone plz help me with a solution..
Thanks....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 02:56:11

[code]SELECT CONVERT(VARCHAR, DateReceived, 102) DateReceived,
Status,
SUM(CASE WHEN DATEDIFF(DAY, DATERECEIVED, GETDATE()) > 7 AND LU = 'L' THEN 1 ELSE 0 END) 'Within 7 days',
SUM(CASE WHEN DATEDIFF(DAY, DATERECEIVED, GETDATE()) > 15 AND LU = 'U' THEN 1 ELSE 0 END) 'Within 15 days'
FROM Details
WHERE Status = 'P'
GROUP BY DateReceived[/code]

Peter Larsson
Helsingborg, Sweden

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-10 : 03:08:14

Thanks,
But If I want to display only values where it is 1 and not 0
then what would be the query be like?

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-10 : 03:11:21

I also would like to display only those records which match the condition.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 03:23:01

I am not sure what you want. Provide some sample data for easier understanding. Also provide the expected output for that provided sample data.

Peter Larsson
Helsingborg, Sweden

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-10 : 03:34:26

Ok,
AFTER the solution posted by u,The output which I am getting is

DATERECEIVED LU STATUS Within 7 Days Within 15 Days
2006-09-21 L P 1 0
2006-09-21 U P 0 1
2006-09-23 L P 1 0
2006-09-21 L P 1 0
2006-10-09 U P 0 0
2006-10-23 L P 0 0
2006-10-21 U P 0 0

Now I my output to be shown as

DATERECEIVED LU STATUS Within 7 Days Within 15 Days

2006-09-21 L P 1 0
2006-09-21 U P 0 1
2006-09-23 L P 1 0
2006-09-21 L P 1 0

I do not want to display the columns with no values..
hope now i have cleared my question.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 03:40:42

Use either this

SELECT		CONVERT(VARCHAR, DateReceived, 102) DateReceived,
		Status,
		SUM(CASE WHEN DATEDIFF(DAY, DATERECEIVED, GETDATE()) > 7 AND LU = 'L' THEN 1 ELSE 0 END) 'Within 7 days',
		SUM(CASE WHEN DATEDIFF(DAY, DATERECEIVED, GETDATE()) > 15 AND LU = 'U' THEN 1 ELSE 0 END) 'Within 15 days'
FROM		Details
WHERE		Status = 'P'
GROUP BY	DateReceived
HAVING		SUM(CASE WHEN DATEDIFF(DAY, DATERECEIVED, GETDATE()) > 7 AND LU = 'L' THEN 1 ELSE 0 END) > 0
		OR SUM(CASE WHEN DATEDIFF(DAY, DATERECEIVED, GETDATE()) > 15 AND LU = 'U' THEN 1 ELSE 0 END) > 0

or this

SELECT		dt.DateReceived,
		dt.Status,
		dt.opt1 'Within 7 days',
		dt.opt2 'Within 15 days'
FROM		(
			SELECT		CONVERT(VARCHAR, DateReceived, 102) DateReceived,
					Status,
					SUM(CASE WHEN DATEDIFF(DAY, DATERECEIVED, GETDATE()) > 7 AND LU = 'L' THEN 1 ELSE 0 END) opt1,
					SUM(CASE WHEN DATEDIFF(DAY, DATERECEIVED, GETDATE()) > 15 AND LU = 'U' THEN 1 ELSE 0 END) opt2
			FROM		Details
			WHERE		Status = 'P'
			GROUP BY	DateReceived
		) dt
WHERE		dt.opt1 > 0
		OR dt.opt2 > 0

Peter Larsson
Helsingborg, Sweden

roxcy
Yak Posting Veteran

58 Posts

Posted - 2006-10-10 : 05:36:04

Thanks....

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2006-10-10 : 05:38:46