Query Problem - 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 2000 Forums

Transact-SQL (2000)

Query Problem

Author

Topic

Leigh79
Starting Member

28 Posts

Posted - 2008-11-19 : 05:16:16

Hi Guys

I have a table with the following information:

ID | Reference | TransactionDate | Passengers | Code | Dept
001 | 123456 | 01/11/2008 | 2 | SSU | 1
002 | 123456 | 03/11/2008 | 0 | SSU | 1
003 | 123456 | 10/11/2008 | 0 | CXD | 1
004 | 654321 | 10/11/2008 | 3 | WSU | 2
005 | 654321 | 11/11/2008 | 1 | WSU | 2
006 | 654321 | 11/11/2008 | 0 | CXD | 2

The table holds booking information on a transactional basis, each booking 'reference' can have several rows depending on what changes have been made to the booking.

What I am trying to achieve is to pull out a report for any cancelled bookings (identified by code 'CXD') by the date they were cancelled on displaying the Department Number and the number of cancelled bookings.

Can anyone help I'm having real problems?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 05:26:32

What is the expected output from the above sample data?

E 12°55'05.63"
N 56°04'39.26"

Leigh79
Starting Member

28 Posts

Posted - 2008-11-19 : 05:44:23

What I would like to achieve is:

Dept | Cancelled Bookings | Pax
1 | 1 | 2
2 | 1 | 4

This would be based on a search of Transaction Date From: 10/11/2008 Transaction Date To: 11/11/2008

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 05:56:27

[code]DECLARE @Sample TABLE
(
ID CHAR(3),
Reference INT,
TransactionDate DATETIME,
Passengers INT,
Code CHAR(3),
Dept INT
)

INSERT @Sample
SELECT '001', 123456, '01/11/2008', 2, 'SSU', 1 UNION ALL
SELECT '002', 123456, '03/11/2008', 0, 'SSU', 1 UNION ALL
SELECT '003', 123456, '10/11/2008', 0, 'CXD', 1 UNION ALL
SELECT '004', 654321, '10/11/2008', 3, 'WSU', 2 UNION ALL
SELECT '005', 654321, '11/11/2008', 1, 'WSU', 2 UNION ALL
SELECT '006', 654321, '11/11/2008', 0, 'CXD', 2

SELECT Dept,
SUM(CASE WHEN Code = 'CXD' THEN 1 ELSE 0 END) AS [Cancelled Bookings],
SUM(Passengers) AS Pax
FROM @Sample
WHERE TransactionDate BETWEEN '10/11/2008' AND '11/11/2008'
GROUP BY Dept
ORDER BY Dept[/code]

E 12°55'05.63"
N 56°04'39.26"

Leigh79
Starting Member

28 Posts

Posted - 2008-11-19 : 06:00:51

Hi Peso

Thanks for getting back to me, how do I query this by Transaction Date? I need to group the information as described above but the different dates cause an issue when grouping.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 06:03:30

I added a WHERE clause. Try again.

E 12°55'05.63"
N 56°04'39.26"

Leigh79
Starting Member

28 Posts

Posted - 2008-11-19 : 06:12:12

The results are:

Dept | Cancelled Bookings | Pax
1 | 1 | 0
2 | 1 | 4

The passengers for the first result haven't been picked up.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-11-19 : 06:35:15

Then Remove Where clause.

Leigh79
Starting Member

28 Posts

Posted - 2008-11-19 : 06:37:23

I need to retain the where clause. The information I have given is just a sample - I need to be able to pull cancellations between 2 dates.

Is there a way that the cancellation Transaction Date can be applied as the Transaction Date accross all bookings? i.e. Via Maxdate?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-11-19 : 07:05:38

This?

SELECT		Dept,
		SUM(CASE WHEN Code = 'CXD' AND TransactionDate BETWEEN '10/11/2008' AND '11/11/2008' THEN 1 ELSE 0 END) AS [Cancelled Bookings],
		SUM(Passengers) AS Pax
FROM		@Sample
GROUP BY	Dept
ORDER BY	Dept

Things would be su much easier if you explained your business rules.

E 12°55'05.63"
N 56°04'39.26"

Leigh79
Starting Member

28 Posts

Posted - 2008-11-19 : 07:27:57

Hi Peso

Thanks once again for your help, sorry for not being clearer in my first post. There is another issue - the Code CXD can appear more than once for a File Reference, and therefore skews the results using the query above.

I think I have managed to fix this using:

SELECT Branch, SUM(PAX), SUM(CNT) FROM (
SELECT t1.Dept AS Branch, PAX, 1 AS CNT
FROM @Sample t1
INNER JOIN (SELECT Reference, MAX(TransactionDate) AS CXDDATE, SUM(Passengers) AS PAX FROM @Sample GROUP BY Reference) t2
ON t2.Reference = t1.Reference AND t2.CXDDATE = t1.TransactionDate
WHERE (t1.TransactionDate = '17/11/2008') AND (t1.Dept <> 50) AND (t1.Code = 'CXD')
GROUP BY t1.Dept, t1.Reference, t1.TransactionDate, t1.Code, PAX) t3
GROUP BY Branch

I don't know if this is the best way to achieve what I am trying to do, but it is pulling through the correct results.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources