Can you help my SQL?
September 10, 2008 5:05 AM Subscribe
SQL/MS Visual Studio help please! I have two tables. I need to present Table1 data grouped by SiteID, within time FROM and TO, on the report. Then I need to make one calculation on the report using Table 1 data totalled across all sites (and within FROM and TO) plus Table 2 data totalled between times FROM and TO.
posted by inigo2 to computers & internet (10 answers total) 1 user marked this as a favorite
I can get the data independently:
SELECT Table1.SiteID AS SiteID, SUM(Table1.Stat1) AS Stat1, SUM(Table1.Stat2) AS Stat2, SUM(Table1.Stat3) AS Stat3
WHERE (Table1.Date >= @From) AND (Table1.Date <= @To)
GROUP BY Table1.SiteID
ORDER BY Table1.SiteID;
SELECT SUM(Table2.Stat3) AS Stat3
WHERE (Table2.Date >= @From) AND (Table2.Date <= @TO)
(FROM and TO are set up as Report Parameters; I can select their values when I run the report.)
But when it comes to reports, I (frankly) have no idea what I'm doing. In theory, these two SELECTs should in one with a JOIN in there, but I'm having trouble visualizing what it should be. If I relate Table1 and Table2 by the date, I end up having things double counted it looks like?
I won't be offended if you talk to me like I'm a 2 year old; I'm picking all this up as I go (if that's not already obvious). If I've left anything out that would be helpful, please say so. I'm using Visual Studio 2005. (And for now, I'm just looking for something that'll work, doesn't have to be the best solution...)
Table info (relevant columns):
SiteID, Stat1, Stat2, Date
UnrelatedSiteID, Stat3, Date