How to write this SQL 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

SQL Server 2000 Forums

Transact-SQL (2000)

How to write this SQL query

Author

Topic

lcpx
Yak Posting Veteran

54 Posts

Posted - 2005-11-11 : 09:15:56

I have a table, structure like this, 3 columns: (I am using SQL server 2000)

Date Qty Status
Day1 100 Good
Day1 50 Better
Day2 250 Better
Day3 200 best

Is there any way I can write an SQL to display like below:

Date Qty Status
Day1 100 Good
Day1 50 Better
Day1 0 Best
Day2 0 Good
Day2 250 Better
Day2 0 Best
Day3 0 Good
Day3 0 Better
Day3 200 Best

What I want is displaying the 0 for the NULL data. Do I have to use PL/SQL. Thanks very much for any though.

lcpx
Yak Posting Veteran

54 Posts

Posted - 2005-11-11 : 09:20:22

I think I may need anther dimension table like status, which only holds the data Good, Better and Best

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-11-11 : 09:32:51

Yes. You need to have seperate table

Madhivanan

Failing to plan is Planning to fail

lcpx
Yak Posting Veteran

54 Posts

Posted - 2005-11-11 : 10:22:29

Cheers! this is resolved, I'll post the anwser later today as I am very busy at the moment. Thanks Madhivanan!

lcpx
Yak Posting Veteran

54 Posts

Posted - 2005-11-11 : 12:10:43

This is the table I mentioned, I named it as Orders (I: invoiced, D: Despatched)

Date Status Qty
1 I 150
1 I 200
1 I 100
2 I 80
2 D 90
2 I 120
3 D 70
3 D 260

To summarize the qty based on Status and Date, we must have a dimension table for the Status, so I create another table called Status: (I: invoiced, D: Despatched)

Status
I
D

Then I used right outer join on above tables to make sure each date has both ‘I’ and ‘D’ status.

We can use following SQL statement to get the desired result:

SELECT t2.date,
t2.status,
CASE WHEN SUM(t1.qty) IS NULL
THEN 0
ELSE SUM(t1.qty)
END AS Qty
FROM Orders t1 RIGHT OUTER JOIN
(SELECT DISTINCT Orders.date,
Status.status
FROM Orders, Status) t2
ON t1.status = t2.status
AND t1.date = t2.date
GROUP BY t2.date,
t2.status
ORDER BY t2.date

Here is the output:

Date Status Qty
1 D 0
1 I 350
2 D 90
2 I 200
3 D 330
3 I 0

Cheers!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources