Data from the same table - 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

Data from the same table

Author

Topic

mmouse
Starting Member

3 Posts

Posted - 2007-01-26 : 05:12:23

Greetings All,

I have a table (Tblweekly) that records the sales by product by week.

I would like to show this week sales vs. last week sales within one query.

I have partially managed to achieve this.

SELECT TblSub.SubName, TblWeekly.LineId, TblLine.LineDesc, TblWeekly.Unit,
TblWeekly_1.Unit, TblWeekly.Reg, TblWeekly_1.Reg, TblWeekly.Was,
TblWeekly_1.Was, TblWeekly.Red, TblWeekly_1.Red, TblWeekly.Con2,
TblWeekly_1.Con2

FROM TblWeekly TblWeekly_1 RIGHT OUTER JOIN TblWeekly
INNER JOIN TblLine ON TblWeekly.LineId = TblLine.LineId
INNER JOIN TblCoy ON TblLine.LineCoy = TblCoy.ID
INNER JOIN TblCat ON TblLine.LineCat = TblCat.ID
INNER JOIN TblSub ON TblLine.LineSubGroup = TblSub.Id
ON TblWeekly_1.LineId = TblWeekly.LineId AND TblWeekly_1.WEndID = TblWeekly.WEndID - 7

WHERE (TblCat.CatName = 'Company')

GROUP BY TblWeekly.LineId,TblWeekly.WEndID, TblWeekly_1.WEndID, TblWeekly.Unit, TblWeekly_1.Unit,TblWeekly.Reg, TblWeekly_1.Reg, TblWeekly.Was, TblWeekly.Red, TblWeekly.Con2,TblSub.SubName, TblLine.LineDesc, TblWeekly_1.Was, TblWeekly_1.Red,
TblWeekly_1.Con2

HAVING (TblWeekly.WEndID = CONVERT(DATETIME, '13/01/2007', 103))

ORDER BY TblSub.SubName DESC, TblWeekly.LineId

If there are no sales for last week it comes up with a <null> result as expected.
But if there and no sales for this week it does not show up in the query.

Example of expected results

LineDesc TblWeekly.Unit TblWeekly_1.Unit etc,etc
12345 100 90
54321 90 1000
56789 <null> 1000 <-this line does not show in the query
98765 100 <null>

I hope I have made sense.

Many thanks to all

Rgds

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-26 : 06:09:02

FROM TblWeekly TblWeekly_1 RIGHT OUTER JOIN TblWeekly

Only returns rows if there is a value in TblWeekly
try
SELECT TblSub.SubName, TblWeekly.LineId, TblLine.LineDesc, TblWeekly.Unit,
TblWeekly_1.Unit, TblWeekly.Reg, TblWeekly_1.Reg, TblWeekly.Was,
TblWeekly_1.Was, TblWeekly.Red, TblWeekly_1.Red, TblWeekly.Con2,
TblWeekly_1.Con2

FROM TblLine
INNER JOIN TblCoy ON TblLine.LineCoy = TblCoy.ID
INNER JOIN TblCat ON TblLine.LineCat = TblCat.ID
INNER JOIN TblSub ON TblLine.LineSubGroup = TblSub.Id
left join TblWeekly
ON TblWeekly.LineId = TblLine.LineId
left join TblWeekly TblWeekly_1
ON TblWeekly_1.LineId = TblWeekly.LineId AND TblWeekly_1.WEndID = TblWeekly.WEndID - 7
WHERE (TblCat.CatName = 'Company')
GROUP BY TblWeekly.LineId,TblWeekly.WEndID, TblWeekly_1.WEndID, TblWeekly.Unit, TblWeekly_1.Unit,TblWeekly.Reg, TblWeekly_1.Reg, TblWeekly.Was, TblWeekly.Red, TblWeekly.Con2,TblSub.SubName, TblLine.LineDesc, TblWeekly_1.Was, TblWeekly_1.Red,
TblWeekly_1.Con2

maybe add a
where (TblWeekly_1.LineId is not null or TblWeekly_1.LineId is not null)
if you don't want those with no values at all.
==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mmouse
Starting Member

3 Posts

Posted - 2007-01-26 : 06:32:33

Hi,

Thanks for your swift response, but unfortunatly the result is the same.

The results only show when there is a record refering to the date TblWeekly.WendId.
I can see where the issue is I just can't figure out to resolve it.

Shame we can't post images here - I could show you the Diagram pane from the SQL server.

Thanks for having a look.

Rgds,

nr
SQLTeam MVY

12543 Posts

Posted - 2007-01-26 : 07:08:08

>> ON TblWeekly_1.LineId = TblWeekly.LineId AND TblWeekly_1.WEndID = TblWeekly.WEndID - 7

Can't join to TblWeekly as it might not exist

also
HAVING (TblWeekly.WEndID = CONVERT(DATETIME, '13/01/2007', 103))

That is actually a where clause so
where (TblWeekly.WEndID = CONVERT(DATETIME, '13/01/2007', 103))

now you should see the problem - it turns the TblWeekly entry into an inner join.
so

SELECT TblSub.SubName, TblWeekly.LineId, TblLine.LineDesc, TblWeekly.Unit,
TblWeekly_1.Unit, TblWeekly.Reg, TblWeekly_1.Reg, TblWeekly.Was,
TblWeekly_1.Was, TblWeekly.Red, TblWeekly_1.Red, TblWeekly.Con2,
TblWeekly_1.Con2

FROM TblLine
INNER JOIN TblCoy ON TblLine.LineCoy = TblCoy.ID
INNER JOIN TblCat ON TblLine.LineCat = TblCat.ID
INNER JOIN TblSub ON TblLine.LineSubGroup = TblSub.Id
left join TblWeekly
ON TblWeekly.LineId = TblLine.LineId
and TblWeekly.WEndID = CONVERT(DATETIME, '13/01/2007', 103)
left join TblWeekly TblWeekly_1
ON TblWeekly_1.LineId = TblLine.LineId
AND TblWeekly_1.WEndID = CONVERT(DATETIME, '13/01/2007', 103) - 7
WHERE (TblCat.CatName = 'Company')
GROUP BY TblWeekly.LineId,TblWeekly.WEndID, TblWeekly_1.WEndID, TblWeekly.Unit, TblWeekly_1.Unit,TblWeekly.Reg, TblWeekly_1.Reg, TblWeekly.Was, TblWeekly.Red, TblWeekly.Con2,TblSub.SubName, TblLine.LineDesc, TblWeekly_1.Was, TblWeekly_1.Red,
TblWeekly_1.Con2

you can stil add the
where (TblWeekly_1.LineId is not null or TblWeekly_1.LineId is not null)
to get only rows with values.

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

mmouse
Starting Member

3 Posts

Posted - 2007-01-26 : 07:32:43

Big thanks for you help.........

I can work the query now :D

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources