Help with Sql Server Query

Question

0.00/5 (No votes)

See more:

Hey guys im just wondering if someone can help me with the following sql query

SQL

SELECT tt.TypeName, c.Name, db.BandName, p.Product, s.Service,
ra.Date, ra.Hour, ra.Calls, ra.Cost
FROM dbo.MainTable ra WITH(NOLOCK)
JOIN dbo.ChileTable1 tt WITH(NOLOCK) ON tt.TrafficTypeId = ra.TrafficTypeId
JOIN dbo.ChileTable2 c WITH(NOLOCK) ON c.CarrierId = ra.CarrierId
JOIN dbo.ChileTable3 db WITH(NOLOCK) ON db.DestinationBandId = ra.DestinationBandId
JOIN dbo.ChileTable4 p WITH(NOLOCK) ON p.ProductId = ra.ProductId
JOIN dbo.ChileTable5 s WITH(NOLOCK) ON s.ProductId = p.ProductId
Where (Date Between '01-Mar-2014' and '31-Mar-2014')

So basically the Name filed will always only two values. I need to add an extra column to the query to show the Sum(Cost) for Name 1 - Sum(Cost) for Name 2 and also group all the other fields
by tt.TypeName, c.Name, db.BandName, p.Product, s.Service,
ra.Date, ra.Hour

Any help is very much appreciated...

UPDATE

The Main table these values is already aggregated up by BandName, Product, Service, Date and hour.

So say the Name will always be Tom and Ben, so for each of these names I would like to have TypeName, BandName, Product, Service, Date, Hour, Tom's Calls, Tom's Cost, Ben's Calls, Ben's Cost all on one row.

Hope thats clear

Posted 27-Mar-14 6:48am

frostcox

Updated 27-Mar-14 22:09pm

v2

Add a Solution

Comments

Tomas Takac 27-Mar-14 18:45pm

What did you try so far? I think you should add actual and expected results to your question. This will help us and you as well. For example what aggregate do you think should be applied to Date and Hour fields? How is Calls/Cost related to TotalCalls/TotalCost?

frostcox 28-Mar-14 4:10am

Hey thanks for your reply, I have updated my qesstion, you ask what have I tried so far, I have wrote the initial query in the question I just am unsure how to get my expected results. Thanks again

frostcox 30-Mar-14 14:03pm

Yeah but I want to display the results cross tab in the same result set if that if possible?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tomas Takac · Answer 1 · 2014-03-28T09:47:00

Strange enough your query now seems to be exactly what you want. That's why I mentioned you should include a sample of results you are getting now and explain what's wrong with that.

Anyway, did you try this?

SQL

SELECT tt.TypeName, c.Name, db.BandName, p.Product, s.Service, ra.Date, ra.Hour, sum(ra.Calls) as totalcalls, sum(ra.Cost) as totalcost
FROM dbo.MainTable ra WITH(NOLOCK)
JOIN dbo.ChileTable1 tt WITH(NOLOCK) ON tt.TrafficTypeId = ra.TrafficTypeId
JOIN dbo.ChileTable2 c WITH(NOLOCK) ON c.CarrierId = ra.CarrierId
JOIN dbo.ChileTable3 db WITH(NOLOCK) ON db.DestinationBandId = ra.DestinationBandId
JOIN dbo.ChileTable4 p WITH(NOLOCK) ON p.ProductId = ra.ProductId
JOIN dbo.ChileTable5 s WITH(NOLOCK) ON s.ProductId = p.ProductId
Where (Date Between '01-Mar-2014' and '31-Mar-2014')
group by tt.TypeName, c.Name, db.BandName, p.Product, s.Service, ra.Date, ra.Hour