April 1, 2009 at 12:01 pm
Hello, I'm trying to write a query that for the current month shows all funds for each territory and their sales amount even if it is 0.00. Any help would be appreciated.
what I would like it to look like is:
fund | territory | sales amount | teamname | tickets
fund 1 central | 0.00 | teamA | 0
fund 2 central | 0.00 | teamA | 0
fund 1 midwest| 50000 | teamB | 1
fund 2 midwest | 0.00 | teamB | 0
below is my SQL
SELECT
r.territory,
r.teamname,
f.description,
DATEPART(yy, t.createDate) AS ActivityDateYear,
DATENAME(m, t.createDate) AS ActivityDateMonth,
ISNULL(sum(t.ticketAMT), 0.00) as [Sales Amount],
COUNT(*) AS Tickets, f.isOpen
FROM tblTransActions t, tblTerritory r, tblfunds f
where
(t.territory = r.territory) and
(DATEPART(m, t.createDate) = datepart(m,getdate())) and
(DATEPART(yy, t.createDate) = datepart(yy,getdate())) and
(f.isopen = 0) and (f.description not like '%qualified%') and (t.status NOT LIKE '%deleted%')
GROUP BY r.territory, r.teamname,f.description, DATEPART(yy, t.createDate), DATENAME(m, t.createDate), f.isopen
order by [Sales Amount] desc
April 1, 2009 at 12:12 pm
Please post table definitions and sample data. Read this to see the best way to post this to get quick responses.
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
April 1, 2009 at 12:52 pm
here are the table defs and sample data:
---------
CREATE TABLE [dbo].[tblfunds](
[numeric](18, 0) NULL,
[description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[isOpen] [int] NULL
) ON [PRIMARY]
INSERT INTO tblfunds
(code, description, isOpen)
SELECT '1','Fund1','0' UNION ALL
SELECT '2','Fund2','0' UNION ALL
-------------
CREATE TABLE [dbo].[tblTerritory](
[TerritoryID] [int] IDENTITY(1,1) NOT NULL,
[territory] [varchar](250) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[teamname] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
INSERT INTO tblTerritory
(TerritoryID, territory, teamname)
SELECT '1','Northwest','TeamA' UNION ALL
SELECT '2','Southwest','TeamB' UNION ALL
SELECT '3','Midwest','TeamC' UNION ALL
SELECT '4','Central','TeamD' UNION ALL
--------
CREATE TABLE [dbo].[tblTransActions](
[TransID] [int] IDENTITY(1,1) NOT NULL,
[fund] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[dateReceived] [datetime] NOT NULL,
[ticketAmt] [money] NOT NULL,
[territory] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_tblTransActions] PRIMARY KEY CLUSTERED
(
[TransID] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
INSERT INTO tblTransActions
(TransID, fund, dateReceived, ticketAMT, territory)
SELECT '3349','Fund1','4/15/2009 12:00:00 AM', '50000', 'Northwest' UNION ALL
SELECT '3352','Fund1','4/12/2009 12:00:00 AM', '60000', 'Northwest' UNION ALL
SELECT '3353','Fund2','4/11/2009 12:00:00 AM', '15000', 'Northwest' UNION ALL
SELECT '3353','Fund2','4/11/2009 12:00:00 AM', '19000', 'Southwest' UNION ALL
April 1, 2009 at 1:24 pm
Hi
There are some little issues in your sample but it was possible to fix...
What I don't understand:
* What is the link between the territories and the funds? I just applied every fund to every territory for now.
* How do you define your resulting "ticket" column? Was not able to determine.
* Why don't you use the territory/funds ids within your transactions table? The usage of the the descriptions takes much more space and your database grows more than needed.
Here is a start-up (and your fixed sample data):
DECLARE @tblfunds TABLE (
[numeric](18, 0) NULL,
[description] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[isOpen] [int] NULL
)
INSERT INTO @tblfunds (code, description, isOpen)
SELECT '1','Fund1','0' UNION ALL
SELECT '2','Fund2','0'
-------------
DECLARE @tblTerritory TABLE (
[TerritoryID] [int] NOT NULL,
[territory] [varchar](250) NOT NULL,
[teamname] [nvarchar](50) NULL
)
INSERT INTO @tblTerritory (TerritoryID, territory, teamname)
SELECT '1','Northwest','TeamA' UNION ALL
SELECT '2','Southwest','TeamB' UNION ALL
SELECT '3','Midwest','TeamC' UNION ALL
SELECT '4','Central','TeamD'
--------
DECLARE @tblTransActions TABLE (
[TransID] [int] NOT NULL,
[fund] [varchar](50) NOT NULL,
[dateReceived] [datetime] NOT NULL,
[ticketAmt] [money] NOT NULL,
[territory] [nvarchar](50) NOT NULL,
PRIMARY KEY CLUSTERED ( [TransID] ASC )
)
INSERT INTO @tblTransActions (TransID, fund, dateReceived, ticketAMT, territory)
SELECT '3349','Fund1','4/15/2009 12:00:00 AM ', '50000', 'Northwest' UNION ALL
SELECT '3352','Fund1','4/12/2009 12:00:00 AM ', '60000', 'Northwest' UNION ALL
SELECT '3353','Fund2','4/11/2009 12:00:00 AM ', '15000', 'Northwest' UNION ALL
SELECT '3355','Fund2','4/11/2009 12:00:00 AM ', '19000', 'Southwest'
;WITH TerritoryFundsAmount (territory, fund, amount) AS
(
SELECT territory, fund, SUM(ticketAMT)
FROM @tblTransActions
GROUP BY territory, fund
)
SELECT f.description, t.territory, ISNULL(tfa.amount, 0), t.teamname
FROM @tblTerritory t
CROSS JOIN @tblfunds f
LEFT JOIN TerritoryFundsAmount tfa ON t.territory = tfa.territory AND f.description = tfa.fund
ORDER BY t.territory, f.description
Greets
Flo
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply