LEFT OUTER JOIN HELL!!
-
Hi Folks,
I will be crazy please help..
I have a database table named TMPSALESTARGET. This table's columns are:
-------------------------------------
- ProductID
- StoreID
- SalesDate
-SalesQuantity
-SalesAmount
------------------------------------
How can I implement a select operation that will consist sum of sales quantity and sales amount in a month.I mean I would also like the NULL values (sum value is Null) into the resultset.
I have found a silly solution but this will not be enough.I have created a static Month table and have joined these two tables like this:Month table stores values from 1 to 31 (Days of a month)
select (tblMonth.month),(Sum(tblTempTargets.SalesQuantity)),(Sum(tblTempTargets.SalesAmoun)) from tblTemptargets,tblMonth
group by tblMonth.month,tblTempTargets.SalesQuantity,tblTempTargets.SalesAmount
order by tblMonth.month
Please HELPPPPP
-
You mean something like this?
SELECT DATEPART(month,t.SalesDate) AS Month,SUM(t.SalesQuantity),SUM(t.SalesAmt)
FROM tmpsalestarget t
GROUP BY DATEPART(month,t.SalesDate)
ORDER BY Month
Or are you trying to get them by day (1-31)?
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Yes..You are exactly right.I am trying to get them by day..(including NULL values)
Thank you for your attention
-
CREATE TABLE #tblMonth (MatchDate smalldatetime) DECLARE @Day smalldatetime SET @Day = STR(Year(getdate())) + '0101' WHILE YEAR(getdate()) = YEAR(@Day) BEGIN INSERT INTO #tblMonth VALUES (@Day) SET @Day = DATEADD(d, 1, @Day) END
SELECT DATENAME(mm, #tblMonth.MatchDate) AS Month, DATENAME(d, #tblMonth.MatchDate) AS Day, SUM(tblTempTargets.SalesQuantity) AS SalesQuantity, SUM(tblTempTargets.SalesAmount) AS SalesAmount FROM tblMonth LEFT JOIN tblTemptargets ON tblMonth.MatchDate = t.SalesDate GROUP BY tblMonth.MatchDate ORDER BY tblMonth.MatchDate
DROP #tblMonth
I used this for filling a query for the entire current year. You can adjust filling the temp table #tblMonth as you need for your query. If you only want the current month, change the initial setting of @Day to STR(Year(getdate())0 + STR(Month(getdate())) + '01', and the WHILE expression to (Month(getdate()) = Month(@Day)).
Hope this helps
Mark -
If you don't have Numbers and Dates tables yet, you can create them now.
CREATE TABLE Numbers
( Number INT NOT NULL,
CONSTRAINT PK_Numbers
PRIMARY KEY CLUSTERED (Number)
WITH FILLFACTOR = 100)
INSERT INTO Numbers
SELECT (a.Number * 256) + b.Number AS Number
FROM master..spt_values a,
master..spt_values b
WHERE a.Type = 'p' AND b.Type = 'p'
GO
CREATE TABLE Dates (number INT, date_char CHAR(10) NOT NULL, date_dt datetime
CONSTRAINT PK_cislo PRIMARY KEY CLUSTERED (number)
WITH FILLFACTOR = 100)
INSERT INTO Dates (number, date_char, date_dt)
SELECT n.number, convert(char(10),convert(datetime,n.Number),104), convert(datetime,n.Number)
FROM dbo.numbers n
ORDER BY n.number
CREATE UNIQUE INDEX idx_date_dt ON Dates(date_dt)
CREATE UNIQUE INDEX idx_date_char ON Dates(date_char)
Well, and now you can join to the Dates table.
SELECT d.date_dt, ISNULL(Q.qty_sum,0), ISNULL(Q.amount_sum,0)
FROM Dates d
LEFT JOIN
(select DATEADD(d, DATEDIFF(d, 0, SalesDate), 0) as sale_date,
SUM(tmp.SalesQuantity) as qty_sum,
SUM(tmp.SalesAmount) as amount_sum
from TMPSALESTARGET tmp
/*here specify other conditions for summing, if any*/
group by DATEADD(d, DATEDIFF(d, 0, SalesDate), 0)) as Q ON Q.sale_date=d.date_dt
/*here specify month and year - WHERE.....*/
The final SQL is untested, and needs some tweaking - depending on how the report should look. I hope it will work.
-
Hello Again...
Both solutions work like a well designed machine!!!!! Thank you very much.
-
Hello Again,
When I try to add another where criteria then the query does not select the NULL values.
I have changed the code as below:
----------------------------------------------
SELECT TOP 100 PERCENT DATENAME(mm, dbo.MyMonth2.MatchDate) AS Month, DATENAME(d, dbo.MyMonth2.MatchDate) AS Day,
SUM(dbo.TBLTEMPTARGETS.SatisMik) AS SatisMik, SUM(dbo.TBLTEMPTARGETS.SatisTut) AS SatisTut, dbo.TBLTEMPTARGETS.MagazaID
FROM dbo.MyMonth2 LEFT OUTER JOIN
dbo.TBLTEMPTARGETS ON dbo.MyMonth2.MatchDate = dbo.TBLTEMPTARGETS.Tarih
GROUP BY dbo.MyMonth2.MatchDate, dbo.TBLTEMPTARGETS.MagazaID
ORDER BY dbo.MyMonth2.MatchDate
---------------------------------------------
and output is like below:
For example there are two rows for January 3rd.But these values are for different MagazaID s...How can implement a WHERE MagazaID on this resultset??
-
You have to make the condition part of LEFT JOIN clause. Then the LEFT JOIN (i.e. NULLs are included) applies also to this condition, whereas if you put the condition into WHERE, NULLs are filtered out:
SELECT DATENAME(mm, dbo.MyMonth2.MatchDate) AS Month,
DATENAME(d, dbo.MyMonth2.MatchDate) AS Day,
SUM(dbo.TBLTEMPTARGETS.SatisMik) AS SatisMik,
SUM(dbo.TBLTEMPTARGETS.SatisTut) AS SatisTut,
dbo.TBLTEMPTARGETS.MagazaID
FROM dbo.MyMonth2
LEFT OUTER JOIN dbo.TBLTEMPTARGETS
ON dbo.MyMonth2.MatchDate = dbo.TBLTEMPTARGETS.Tarih AND dbo.TBLTEMPTARGETS.MagazaID = @something
GROUP BY dbo.MyMonth2.MatchDate, dbo.TBLTEMPTARGETS.MagazaID
ORDER BY dbo.MyMonth2.MatchDate
I have deleted the TOP 100 PERCENT, because it isn't necessary. It was there probably because you are creating a view and want to order it... but you shouldn't order inside a view. It can severely affect performance. Apply the ordering when selecting from view (SELECT col1, col2 FROM myview ORDER BY col1).
-
Your question is how to do WHERE clause for magazaid. I assume you want to run the query for one particular magazaID, and have null row for days that magazineid had no sales. In this case, the where clause will want to get your desired MagazaID and also if the MagazaID is Null.
WHERE dbo.TBLTEMPTARGETS.MagazaID = 1 OR dbo.TBLTEMPTARGETS.MagazaID IS NULL
Or if you have multiple MagazaID you want:
WHERE dbo.TBLTEMPTARGETS.MagazaID IN (1,2,3) OR dbo.TBLTEMPTARGETS.MagazaID IS NULL
Hope this helps
Mark -
Thank you very much..That's it...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply