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.
| Author |
Topic |
|
dbutler_05
Starting Member
17 Posts |
 Posted - 2006-01-26 : 11:08:38
|
| I'm trying to join multiple views to a common view by a brand type and also by a date parameter. I'm OK as long as I only do Date = @ReportDate. But as soon as I add a join that includes Date <= @ReportDate, I start getting total garbage. Below is my script. Any suggestions would be greatly appreciated.DECLARE @ReportDate as datetimeset @ReportDate = '01/24/2006'SELECT sbg.brandGroup, SUM(fdo.FamilyDailyOrders) as FDailyOrders,SUM(sd.FamilyShipmentsDaily) as FDailyShip,SUM(sm.FamilyShipmentsMonth) as FMonthShip,SUM(bn.FamilyBacklogNext) as NextBacklogFROM dbo.summaryBrandGroup AS SBG LEFT OUTER JOIN dbo.Flash_FamilyDailyOrders AS FDO ON SBG.PTYP2 = FDO.PTYP2 AND FDO.DATE = @ReportDate LEFT JOIN dbo.Flash_FamilyShipmentsDaily as SD ON SBG.PTYP2 = SD.PTYP2 AND SD.DATE = @ReportDate LEFT JOIN dbo.Flash_FamilyShipmentsmonth as SM ON SBG.PTYP2 = SM.PTYP2 AND SM.SMONTH = Month(@ReportDate) LEFT JOIN dbo.Flash_FamilyBacklogNext as BN ON SBG.PTYP2 = BN.PTYP2 AND BN.DATE <= @ReportDateGROUP BY SBG.BRANDGROUP |
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2006-01-26 : 11:48:51
|
| Try changing the JOINS so the tables join on DATE. e.g.,FROM summaryBrandGroup SBGLEFT OUTER JOIN FDailyOrders FDSOON SBG.Type2 = FDO.Type2AND SBG.Date = FDO.DateWHERE SBG.Date = @ReportDate |
 |
|
|
dbutler_05
Starting Member
17 Posts |
Posted - 2006-01-26 : 12:00:24
|
| The SBG table doesn't have a date field associated with it. All it does is take a brand code, and group it to a brand group. I use that view to tie all my other views together by their common field(ptyp2(or brand code)). |
|
|
|
SamC
White Water Yakist
3467 Posts |
Posted - 2006-01-26 : 15:29:15
|
| Post the columns held in your tables, an example of your desired resultset and maybe an example of what you are getting now. |
|
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2006-01-26 : 16:33:09
|
| It looks like you are trying to joing transactional tables (or views) together in 1 big SELECT, which you cannot do without summarizing them by common columns first. As a result, you are probably getting all kinds of cross joins and missing data. As Sam mentions, your table structure, some sample data and desired results would greatly help us to help you. |
|
|
|
|
|
|
|
|
Help with Join to multiple views by date