Perplexed on how to use sql to give me the right data (complicated sql statement)

this is a real complicated one and i'm at a loss. I'm hoping I could get some help here. I have 4 tables(clients, claims, entries,currency). I'm trying to show a summary of the 3 tables on how much $$ was spend for a certain time period. Here's where it gets cray cray. I have to show my info based on all the clients, so:

SELECT clientID,code,cName FROM clients

Open in new window


this will list all the clients

1 aaa Alpha Corp.
2 bbb Beta LLC.
3 ccc Cappa Corp.

Open in new window


My 2nd table is connected to the clients table by the code (didn't use clientID for some stupid reason). The claims table has 5 vital pieces of information needed 1)rID (uniqueID) 2)Client (which is what links to the ClientsTable... client=code) 3) status (only need closed items) 4)dateOn 5)currency (id links to currency table to retrieve currency name).

My 3rd table is basically the children of the claims table. These are ENTRIES and entries make up a claim. The needed columns are 1) rID (associates entry to claim (parent)) 2)refundAmount 3)errorCode. Now here's where it gets crazy still.... I need to find out within the last, say 30 days, how much a company has made with differentiations in currency. Also, if it's a certain errorCode (there's 5 that go to one group, 12 that go to another group), its tallied up to a different total. So my end result looks like this:

                               Last 30 days                                Last 31 to 60
                               --------------------------------            ----------------
code   cName         Currency  #claims/error1   #claims/error2  total      #claims/error1...
AAA    Alpha Corp    USD       5/$200.00        9/$300.10       14/$500.10
AAA    Alpha Corp    YEN       3/3,000,000.00   2/998,000.00    5/3,998,000.00
BBB ...etc...

Open in new window


So you see, there has to be a differentiation by currency cause yen could be bajillion, meanwhile USD is $1. I hope i made this clear and really hope one of you experts can help. This is blowing my mind! Thanks all!
LVL 3
engineroomAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

PortletPaulfreelancerCommented:
Seems your tables look like this:
CLIENTS
clientID,code,cName

CLAIMS
rID Client status dateOn currency

CURRENCIES
currency currency_name --<< these field names are assumed

ENTRIES
rID refundAmount errorCode

so the joins are:
from clients
inner join claims on clients.code = claims.client
inner join currencies cur on claims.currency = cur.currency
inner join entries on claims.rID = entries.rid

Open in new window

And, I believe your overall query will look very much like this
declare @days30 as date, @days60 as date
set @days30 = dateadd(day,-30,getdate() )
set @days60 = dateadd(day,-60,getdate() )

select
      clients.clientID
    , clients.code
    , clients.cName
    , cur.currency_name

    , SUM(case when claims.dateon >=  @days30 and entries.errorrCode = 1 then entries.refundAmount else 0.0 end) as lst30_error_1
    , SUM(case when claims.dateon >=  @days30 and entries.errorrCode = 2 then entries.refundAmount else 0.0 end) as lst30_error_2
    , SUM(case when claims.dateon >=  @days30 and entries.errorrCode = 3 then entries.refundAmount else 0.0 end) as lst30_error_3
    , SUM(case when claims.dateon >=  @days30 and entries.errorrCode = 4 then entries.refundAmount else 0.0 end) as lst30_error_4
    , SUM(case when claims.dateon >=  @days30 and entries.errorrCode = 5 then entries.refundAmount else 0.0 end) as lst30_error_5
    , SUM(case when claims.dateon >=  @days30                             then entries.refundAmount else 0.0 end) as lst30_total

    , SUM(case when claims.dateon >=  @days60 and claims.dateon < @days30 and entries.errorrCode = 1 then entries.refundAmount else 0.0 end) as lst60_error_1
    , SUM(case when claims.dateon >=  @days60 and claims.dateon < @days30 and entries.errorrCode = 2 then entries.refundAmount else 0.0 end) as lst60_error_2
    , SUM(case when claims.dateon >=  @days60 and claims.dateon < @days30 and entries.errorrCode = 3 then entries.refundAmount else 0.0 end) as lst60_error_3
    , SUM(case when claims.dateon >=  @days60 and claims.dateon < @days30 and entries.errorrCode = 4 then entries.refundAmount else 0.0 end) as lst60_error_4
    , SUM(case when claims.dateon >=  @days60 and claims.dateon < @days30 and entries.errorrCode = 5 then entries.refundAmount else 0.0 end) as lst60_error_5
    , SUM(case when claims.dateon >=  @days60 and claims.dateon < @days30                             then entries.refundAmount else 0.0 end) as lst60_total

from clients
inner join claims on clients.code = claims.client
inner join currencies cur on claims.currency = cur.currency
inner join entries on claims.rID = entries.rid
group by
      clients.clientID
    , clients.code
    , clients.cName
    , cur.currency_name

Open in new window

as you haven't detailed the error codes I have just used 1,2,3,4,5 as an example.
Please replace with your real error codes.
0
Aaron TomoskySD-WAN SimplifiedCommented:
I'm thinking start with selecting the claims and converting the currency so it ends up like:
code-client, date, dollaramount,  errortype

then you can pivot it like so:
select * from tablename                                 -- Colums to pivot
pivot (
   sum (dollaramount)                                                    -- Pivot on this column
   for errortype in (type1, type2))         -- column and values that become more columns
   as totaldollarspererrortype                                                     -- Pivot table alias
where date >= dateadd(day, -30, getdate())                               -- Select only last 30 days

Open in new window


it;s tricky because you are trying to do error types as well as date filters. It's easier to just do it like this, and then run it a few times for the date ranges you want.

then you can wrap all that in a select * from () and join to the clients table to get the client name and whatever other informational columns you want.

Alternatively you could start with the clients and do everything in a subquery like so, but I like the pivot personally:
select 
clienname
, clientid
, blah
,(select sum(dollaramounts) from claims where code = clientid and errortype in (1,2,3,4))
,(select sum(dollaramounts) from claims where code = clientid and errortype in (5,6,7,8))
from clients 

Open in new window

there are ways with CTEs as well, but its hard to give working code without something in sqlfiddle.com
0
Vitor MontalvãoMSSQL Senior EngineerCommented:
I would like to see how your query looks like by now so I can understand how are you grouping the errors and also check for criteria that you may be using.
For now and if I understood correctly, what I have for brut records is this SELECT statement:
SELECT clients.clientID, clients.code, clients.cName, claims.rID, claims.dateOn, currency.currency, entries.refundAmount, entries.errorCode
FROM clients
	INNER JOIN claims 
		INNER JOIN currency ON claims.currency = currency.currency
		INNER JOIN entries ON entries.rID = claims.rID
	ON clients.clientID = claims.client
WHERE claims.status = 'CLOSED'

Open in new window

0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

engineroomAuthor Commented:
Thanks guys for your response... can't wait to give these a shot!!!

@PortletPaul: In regards to error codes, it's really something like, if errorCode in(1,7,23,44,45)  then that's one group, the other group is if errorcode is not in (1,7,23,44,45).  FYI, I'm printing out all this content in ASP so it's not gonna be done solely in sql. Also, looks like you have all the joins correct. Not sure if this matters but there could be many entries to 1 claimID.

@Aaron Tomosky: Not sure what a pivot table is, gonna have to research that, but you only seem to be selecting 1 table... how are the other tables incorporated?

@Vitor Montalvão: There doesn't seem to be any grouping by currency or SUMing up of monies.?

Again, thanks all for your input. I'm really excited to try these out... i was at a total loss and i'm hoping this works!!

damien
0
Aaron TomoskySD-WAN SimplifiedCommented:
You could turn the error code to a group with a case in the select
Select
Column
,(case when errorCode in(1,7,23,44,45)  then 'errorgroup1'
When errorCode in (2,3,4) then 'errorgroup2'
Else 'no error group match' end) as errorgroup
0
PortletPaulfreelancerCommented:
Yes, those clusters of error codes can be handled like this:
declare @days30 as date, @days60 as date
set @days30 = dateadd(day,-30,getdate() )
set @days60 = dateadd(day,-60,getdate() )

select
      clients.clientID
    , clients.code
    , clients.cName
    , cur.currency_name

    , SUM(case when claims.dateon >=  @days30 and entries.errorrCode     in(1,7,23,44,45)  then entries.refundAmount else 0.0 end) as lst30_error_1
    , SUM(case when claims.dateon >=  @days30 and entries.errorrCode not in (1,7,23,44,45) then entries.refundAmount else 0.0 end) as lst30_error_2
    , SUM(case when claims.dateon >=  @days30                                              then entries.refundAmount else 0.0 end) as lst30_total

    , SUM(case when claims.dateon >=  @days60 and claims.dateon < @days30 and entries.errorrCode     in(1,7,23,44,45)  then entries.refundAmount else 0.0 end) as lst60_error_1
    , SUM(case when claims.dateon >=  @days60 and claims.dateon < @days30 and entries.errorrCode not in (1,7,23,44,45) then entries.refundAmount else 0.0 end) as lst60_error_2
     , SUM(case when claims.dateon >=  @days60 and claims.dateon < @days30                                             then entries.refundAmount else 0.0 end) as lst60_total

from clients
inner join claims on clients.code = claims.client
inner join currencies cur on claims.currency = cur.currency
inner join entries on claims.rID = entries.rid
group by
      clients.clientID
    , clients.code
    , clients.cName
    , cur.currency_name

Open in new window

0
engineroomAuthor Commented:
Thanks for all your help guys!

PortletPaul: I ended up going with your solution. Here's what the final query looks like:

SELECT
	clients.clientID
	, clients.code
	, clients.cName
	, cur.currency

	, SUM(case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' AND entries.errorCode IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as payment30
	, SUM(case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate30

	, SUM(case when claims.dateon BETWEEN '8/1/2014' AND '8/31/2014' AND entries.errorCode IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as payment60
	, SUM(case when claims.dateon BETWEEN '8/1/2014' AND '8/31/2014' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate60

FROM clients
INNER JOIN claims ON clients.code = claims.client
INNER JOIN currency cur ON claims.currency = cur.currencyID
INNER JOIN entries ON claims.rID = entries.rid

WHERE claims.status = 'closed'

ORDER BY clients.cName

GROUP BY
      clients.clientID
    , clients.code
    , clients.cName
    , cur.currency

Open in new window


I got result back from this. The only thing left to do is to verify the results against the actual data. Thanks for your help. I'll keep you posted as to if everything is kosher!

damien
0
engineroomAuthor Commented:
I'm trying to show how many # of claims makes up the payment30,60, etc . I tried using this code, but it's giving me the same exact number on both the pTotal30 and rTotal30...

SELECT
	clients.clientID
	, clients.code
	, clients.cName
	, cur.currency

	, COUNT(case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' AND entries.errorCode IN('DP','RB','WP','PE') then claims.rID else 0.0 end) as pTotal30
	, SUM(case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' AND entries.errorCode IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as payment30
	, COUNT(case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' AND entries.errorCode NOT IN('DP','RB','WP','PE') then claims.rID else 0.0 end) as rTotal30
	, SUM(case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate30

	, SUM(case when claims.dateon BETWEEN '8/1/2014' AND '8/31/2014' AND entries.errorCode IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as payment60
	, SUM(case when claims.dateon BETWEEN '8/1/2014' AND '8/31/2014' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate60

FROM clients
INNER JOIN claims ON clients.code = claims.client
INNER JOIN currency cur ON claims.currency = cur.currencyID
INNER JOIN entries ON claims.rID = entries.rid

WHERE claims.status = 'closed'

GROUP BY
      clients.clientID
    , clients.code
    , clients.cName
    , cur.currency

ORDER BY clients.cName

Open in new window

0
Aaron TomoskySD-WAN SimplifiedCommented:
thats because you are counting zeros in this line
, COUNT(case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' AND entries.errorCode IN('DP','RB','WP','PE') then claims.rID else 0.0 end) as pTotal30

either do "else null" or do sum instead of count and do "then 1 else 0.0"
0
engineroomAuthor Commented:
So i did this:

	, SUM(case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' AND entries.errorCode IN('DP','RB','WP','PE') then 1 else 0 end) as pTotal30
	, SUM(case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' AND entries.errorCode IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as payment30
	, SUM(case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' AND entries.errorCode NOT IN('DP','RB','WP','PE') then 1 else 0 end) as rTotal30
	, SUM(case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate30

Open in new window


This give me a pTotal30 of 59 and a rTotal30 of 90 for code 'APLM'. To check, i ran this query on the claims table:

The problem is, when i run a query on the claims table, like so:

SELECT     rID, claimID, counter, pdf, zip, auditorID, claimSubmission, client, carrierID, transportationType, myNotes, notes, status, amended, amendedDate, cancel, cancelDate, 
                      reopenDate, consignor, consignee, dateOn, holdUntil, bu, statusComments, country, currency
FROM         Claims
WHERE     dateOn BETWEEN '9/1/2014' AND '9/30/2014' AND client = 'APLM'

Open in new window


I get a total of 120 claims. The numbers are wrong. It should be 59+90= 149. Somethings off...
0
engineroomAuthor Commented:
Yea... looks like something is off here. I've singled out the client APLM. When i run the query (not trying to count claims), I get 4 results for APLM. Here's what the data looks like:

20	APLM		Singapore Dollar	0	0.0000	0	0.0000
20	APLM		Taiwan Dollars	0	0.0000	0	0.0000
20	APLM		US Dollar	59	131068.9700	90	327719.0650
20	APLM		Turkmenistan New Manat	0	0.0000	0	0.0000

Open in new window


The big issue here is that within the time frame provided, APLM has only worked with US Dollars. There's no other currency. Where's it getting the other countries? Well, there's a countryID in the claims table and APLM indeed has used those countries but always used USD even if it was a different country. Any ideas here?
0
Aaron TomoskySD-WAN SimplifiedCommented:
look at the claims.currency field
INNER JOIN currency cur ON claims.currency = cur.currencyID
0
engineroomAuthor Commented:
That's actually right..... currency in the claims table is actually currencyID in the currency table.
0
engineroomAuthor Commented:
In regards to the count, i think i might know what's throwing it off.... 1 claim can have multiple entries, which of course can contain 3 of a certain kind and 2 of another kind, therefore counting the claim more than once. Still perplexed as to the currency thing.
0
Aaron TomoskySD-WAN SimplifiedCommented:
try running this:
select * from claims where client = 'APLM' order by currency

look for anything not usd
0
engineroomAuthor Commented:
When running this:

SELECT     rID, client, status, dateOn, country, currency
FROM         Claims
WHERE     (dateOn BETWEEN '9/1/2014' AND '9/30/2014') AND (client = 'APLM') AND (status = 'closed')
ORDER BY Currency

Open in new window


There's no differentiation in currency. When running your sql, filtering just by client (APLM), i get multiple currencies. Actually, the currencies that show 0.00 in the previous query.
0
Aaron TomoskySD-WAN SimplifiedCommented:
change the groupby , cur.currency to claims.currency

see if that helps
0
engineroomAuthor Commented:
Changed it and still the same thing, except now it shows the currencyID (claims.currency) instead of the actual currency name.
0
Aaron TomoskySD-WAN SimplifiedCommented:
thats what happens when you change the select, change the group by
0
engineroomAuthor Commented:
I tried that before and thought you wanted to change the select cause when the group by is changed, this is what happens

Column 'currency.Currency' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
0
engineroomAuthor Commented:
Just to be clear, i did this:


GROUP BY
      clients.clientID
    , clients.code
    , clients.cName
    , claims.currency

Open in new window

0
Aaron TomoskySD-WAN SimplifiedCommented:
Ive gotta let PortletPaul handle this as it's his query. I don't particularly like doing it that way as it's hard to troubleshoot. If he doesn't respond at some point, I'll try redoing it a way I can help with better.
0
engineroomAuthor Commented:
Thanks Aaron... i really appreciate your help. If there's another way to get the data, i'm all for it!

damien
0
engineroomAuthor Commented:
I looked at what you posted prior and it didn't address the different currencies issue. Hope you can help with something different.  Thanks again!
0
Aaron TomoskySD-WAN SimplifiedCommented:
Alright, this is pretty hard without having the tables in sqlfiddle to test against, but here is my first crack at a runnable query. I like to first make a cte to get the data into an easier to manage format. This also allows you to see the data without grouping and summing if you wish.

;with gooddata as (
	select
		clients.clientID as clientID
		, clients.code as clientCode
		, clients.cName as clientName
		, cur.currency as currency
		, claims.dateon as claimDate
		, datepart(m, claims.dateon) as claimMonth
		, (case when entries.errorCode IN('DP','RB','WP','PE') then 'payment' else 'rate' end) as entryType
		, entries.refundDue as amount
	FROM clients
	INNER JOIN claims ON clients.code = claims.client
	INNER JOIN currency cur ON claims.currency = cur.currencyID
	INNER JOIN entries ON claims.rID = entries.rid
	WHERE claims.status = 'closed'
)
--select * from gooddata
--uncomment above and comment out below to see the gooddata table

select clientID, clientCode, clientName, currency
, count(case when claimMonth = 9 and entryType = 'payment' then 1 else null end) as pTotal30
, sum(case when claimMonth = 9 and entryType = 'payment' then amount else 0.0 end) as payment30
, count(case when claimMonth = 9 and entryType = 'rate' then 1 else null end) as rTotal30
, sum(case when claimMonth = 9 and entryType = 'rate' then amount else 0.0 end) as rate30
from gooddata
group by clientID, clientCode, clientName, currency
ORDER BY clientName

Open in new window

0
PortletPaulfreelancerCommented:
I am getting a little lost in a bundle of details here.

There is no more difficulty in troubleshooting this query to any alternative based on the same joins.
The most substantial issue here is "are the joins good"? So test the joins by tracing their effects:
   
SELECT     claims.rID, claims.client, claims.status, claims.dateOn, claims.country, claims.currency
FROM claims
WHERE     (claims.dateOn BETWEEN '9/1/2014' AND '9/30/2014') AND (caims.client = 'APLM') AND (claims.status = 'closed')
ORDER BY claims.Currency, claims.country, claims.dateOn

Open in new window

   
SELECT  clients.clientID , clients.code , clients.cName
      , claims.rID, claims.client, claims.status, claims.dateOn, claims.country, claims.currency
FROM clients
INNER JOIN claims ON clients.code = claims.client
WHERE     (claims.dateOn BETWEEN '9/1/2014' AND '9/30/2014') AND (caims.client = 'APLM') AND (claims.status = 'closed')
ORDER BY claims.Currency, claims.country, claims.dateOn

Open in new window

   
SELECT  clients.clientID , clients.code , clients.cName
      , claims.rID, claims.client, claims.status, claims.dateOn, claims.country, claims.currency
	  , cur.currency
FROM clients
INNER JOIN claims ON clients.code = claims.client
INNER JOIN currency cur ON claims.currency = cur.currencyID
WHERE     (claims.dateOn BETWEEN '9/1/2014' AND '9/30/2014') AND (caims.client = 'APLM') AND (claims.status = 'closed')
ORDER BY claims.Currency, claims.country, claims.dateOn

Open in new window


SELECT  clients.clientID , clients.code , clients.cName
      , claims.rID, claims.client, claims.status, claims.dateOn, claims.country, claims.currency
	  , cur.currency
      , entries.refundDue
FROM clients
INNER JOIN claims ON clients.code = claims.client
INNER JOIN currency cur ON claims.currency = cur.currencyID
INNER JOIN entries ON claims.rID = entries.rid
WHERE     (claims.dateOn BETWEEN '9/1/2014' AND '9/30/2014') AND (caims.client = 'APLM') AND (claims.status = 'closed')
ORDER BY claims.Currency, claims.country, claims.dateOn

Open in new window


If these results don't help you decide if the joins are good or not perhaps you could put the results of these into an Excel file and upload it?

There might be a need for a LEFT JOIN to [entries], so try this too:


SELECT  clients.clientID , clients.code , clients.cName
      , claims.rID, claims.client, claims.status, claims.dateOn, claims.country, claims.currency
	  , cur.currency
      , entries.refundDue
FROM clients
INNER JOIN claims ON clients.code = claims.client
INNER JOIN currency cur ON claims.currency = cur.currencyID
LEFT JOIN entries ON claims.rID = entries.rid
WHERE     (claims.dateOn BETWEEN '9/1/2014' AND '9/30/2014') AND (caims.client = 'APLM') AND (claims.status = 'closed')
ORDER BY claims.Currency, claims.country, claims.dateOn

Open in new window

0
PortletPaulfreelancerCommented:
Regarding the count of claims.

There are 2 issues with adding this into the overall query.
a. COUNT() will count every NON-NULL value, so 1 or 0 will still be counted (this has been noted earlier)
b. in this query the impact of the table [entries] needs to be factored in. There may be a many to one relationship between ONE claim and several entries. So you probably need to count something distinctive from the claims table.

by the way, perhaps the join to entries needs to be a LEFT JOIN? This could change the count!


I would try this:

, COUNT(DISTINCT
  case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' AND entries.errorCode IN('DP','RB','WP','PE')

  then claims.rID  --<< MUST be this field, not 1

  else NULL end) as pTotal30

NB: You cannot achieve this by using SUM()
0
engineroomAuthor Commented:
Hey guys, thanks again for posting. Trying to wrap my head around this makes it hurt :)

@Aaron: Wow... i've never seen anything like this. I'm a novice at sql and this is waaaayyyy beyond me. Pauls sql is a little bit more manageable for me to understand.

@Paul: The data seems to be good, at least where the totals of refunds goes for the given time period. I guess the issue is that when it gives me information based on currency, so for September APLM results looks like this:

20	APLM	Singapore Dollar	0	0.0000	0	0.0000
20	APLM	Taiwan Dollars	0	0.0000	0	0.0000
20	APLM	US Dollar	60	131878.8219	90	327719.0650
20	APLM	Turkmenistan New Manat	0	0.0000	0	0.0000

Open in new window


For the month of september APLM only dealt  with US Dollars, yet it show all the currencies they've dealt with in the past, show the correct amount for september, which is 0. How do I get rid of those other currencies since they're not relevant?
0
Aaron TomoskySD-WAN SimplifiedCommented:
i just like to split up my queries with a cte to generate the data, think of it like a temp table. So you make the cte (common table expression) without doing any group by stuff, just get the data to show up correctly. Then I added the conversion from error codes to a static string so it's easier to use in the next step, same with the month field. That way you don't have to do "claims.dateOn BETWEEN '9/1/2014' AND '9/30/2014'" all the time. so much easier to just say claimMonth = 9.

It's really PortletPauls query logic, just written differently.

Once I learned about CTEs a few years ago, I use them for all my complicated stuff. you can even do things like:

with good_entries as (select * from entries where somefield != 'badstuff')
, good_claims as (select * from claims where somefield != 'badstuff')
select * from good_entries as ge left join good_claims gc on ge.fieldid = gc.fieldid

so you can clean up your tables even before joining if there is messy data you want to ignore.
0
PortletPaulfreelancerCommented:
"The data seems to be good"
great, then we can move forward

"How do I get rid of those other currencies since they're not relevant?"
I would assume that IF a third currency was used you would want to include it
So, we need to filter by the SUM(refundDue) > 0
To do that we use a HAVING clause, which allows us to filter by aggregated values. e.g.

HAVING
      SUM(case when claims.dateon BETWEEN '8/1/2014' AND '8/31/2014' then entries.refundDue else 0.0 end) > 0
   OR SUM(case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' then entries.refundDue else 0.0 end) > 0

I also noticed that the where clause wasn't limiting the data to the relevant date ranges, so that should be introduced.

So, merging advice on the count, the where clause as well as the HAVING clause, I think the query is currently at this stage:
SELECT
	  clients.clientID
	, clients.code
	, clients.cName
	, cur.currency

	, COUNT(DISTINCT case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' AND entries.errorCode IN('DP','RB','WP','PE')     then claims.rID end) as pTotal30
	, COUNT(DISTINCT case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' AND entries.errorCode NOT IN('DP','RB','WP','PE') then claims.rID end) as rTotal30

	, SUM(case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' AND entries.errorCode IN('DP','RB','WP','PE')     then entries.refundDue else 0.0 end) as payment30
	, SUM(case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate30

	, SUM(case when claims.dateon BETWEEN '8/1/2014' AND '8/31/2014' AND entries.errorCode IN('DP','RB','WP','PE')     then entries.refundDue else 0.0 end) as payment60
	, SUM(case when claims.dateon BETWEEN '8/1/2014' AND '8/31/2014' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate60

FROM clients
INNER JOIN claims ON clients.code = claims.client
INNER JOIN currency cur ON claims.currency = cur.currencyID
INNER JOIN entries ON claims.rID = entries.rid

WHERE claims.status = 'closed'
AND claims.dateon BETWEEN '8/1/2014' AND '9/30/2014'

GROUP BY
      clients.clientID
    , clients.code
    , clients.cName
    , cur.currency

HAVING
      SUM(case when claims.dateon BETWEEN '8/1/2014' AND '8/31/2014' then entries.refundDue else 0.0 end) > 0
   OR SUM(case when claims.dateon BETWEEN '9/1/2014' AND '9/30/2014' then entries.refundDue else 0.0 end) > 0

ORDER BY clients.cName
;

Open in new window


But there is one rather important point I should have raised much earlier. (sorry)
"between" is not a great method for selecting date ranges see: "Beware of Between"

Oh, and I would NOT use m/d/yyyy date strings either. YYYYMMDD is the safest in SQL Server

So, if I was writing this query for my own use I would NOT use between which guarantees we don't miss any information in August or September and it would look like this:
SELECT
	  clients.clientID
	, clients.code
	, clients.cName
	, cur.currency

	, COUNT(DISTINCT case when claims.dateon >= '20140901' AND claims.dateon < '20141001' AND entries.errorCode IN('DP','RB','WP','PE')     then claims.rID end) as pTotal30
	, COUNT(DISTINCT case when claims.dateon >= '20140901' AND claims.dateon < '20141001' AND entries.errorCode NOT IN('DP','RB','WP','PE') then claims.rID end) as rTotal30

	, SUM(case when claims.dateon >= '20140901' AND claims.dateon < '20141001' AND entries.errorCode IN('DP','RB','WP','PE')     then entries.refundDue else 0.0 end) as payment30
	, SUM(case when claims.dateon >= '20140901' AND claims.dateon < '20141001' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate30

	, SUM(case when claims.dateon >= '20140801' AND claims.dateon < '20140901' AND entries.errorCode IN('DP','RB','WP','PE')     then entries.refundDue else 0.0 end) as payment60
	, SUM(case when claims.dateon >= '20140801' AND claims.dateon < '20140901' AND entries.errorCode NOT IN('DP','RB','WP','PE') then entries.refundDue else 0.0 end) as rate60

FROM clients
INNER JOIN claims ON clients.code = claims.client
INNER JOIN currency cur ON claims.currency = cur.currencyID
INNER JOIN entries ON claims.rID = entries.rid

WHERE claims.status = 'closed'
AND ( claims.dateon >= '20140801' AND claims.dateon < '20141001'  )

GROUP BY
      clients.clientID
    , clients.code
    , clients.cName
    , cur.currency

HAVING
      SUM(case when claims.dateon >= '20140801' AND claims.dateon < '20140901' then entries.refundDue else 0.0 end) > 0
   OR SUM(case when claims.dateon >= '20140901' AND claims.dateon < '20141001' then entries.refundDue else 0.0 end) > 0

ORDER BY clients.cName
;

Open in new window


Personally I'd prefer it if we kept the query structure conventional, without using CTEs.

Common Table Expression are wonderful things, but their main advantages are:
a. you need recursion, or
b, you can avoid repeating some part of a query
Neither of those benefits apply to this query and hence I wouldn't bother with a CTE for this query.

Do hope I have't overdone the advice in one blockbuster comment.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Aaron TomoskySD-WAN SimplifiedCommented:
Paul, how do you feel about using
datepart(m, claims.dateon) = 9?
0
PortletPaulfreelancerCommented:
Aaron,

Any function that is performed on the data (to filter that data) robs the optimiser of access to an index (in the vast majority of cases). It also consumes CPU by forcing a calculation on every row.

So, I would much rather type in a few more characters of SQL and get use of an index.

Inconvenient as it may be "sargable predicates" are often a little more long winded, but they perform better.
see: http://en.wikipedia.org/wiki/Sargable

So, in my opinion I would definitively go with
claims.dateon >= '20140901' AND claims.dateon < '20141001'
in preference to
datepart(m, claims.dateon) = 9
0
Aaron TomoskySD-WAN SimplifiedCommented:
True, no indexing most of the stuff I make, but since it's for reporting purposes it usually doesn't matter if it takes 10 seconds to run. Ill performance optimize at the end if it takes too long.
0
engineroomAuthor Commented:
Wow.... I'm looking at you guys like geniuses here. My SQL knowledge goes as far as SELECTs, WHERE, INNER JOINS (and i don't necessarily know how that works. I don't even know what LEFT does).

Paul: I initially was going to say that my date's aren't stored in that particular format, but running the query seemed to work so I guess sql parses that since it's a datetime column, it knows the format you provided. Very cool. Why exactly won't you use mm/dd/yyyy?

Aaron: datepart wouldn't have worked in this case anyway. It's not doing it by month, but the last 30 days, 31-60,61-90, etc.
0
engineroomAuthor Commented:
Also, it's interesting that I just tried the query without the having portion and it still yields the same results, which are only showing the currency if it's been used within the time frame. Is having necessary then?
0
Aaron TomoskySD-WAN SimplifiedCommented:
thats because you are already doing the where up here
WHERE claims.status = 'closed'
AND ( claims.dateon >= '20140801' AND claims.dateon < '20141001'  )

and during those dates, there happens to not be any other currencies in use with a 0 that the having would get rid of.
0
Aaron TomoskySD-WAN SimplifiedCommented:
and here is a nice example of the different types of joins. Basically its how you choose the records that are returned when looking at two tables.
http://www.experts-exchange.com/Database/MS_Access/Q_20950529.html
0
engineroomAuthor Commented:
Well... I hope you're proud of me. I think i understand my joins! I think i'm gonna call this one. I'm sure i'll be opening more questions but you guys have been awesome. How do i disseminate the points?
0
PortletPaulfreelancerCommented:
>>"initially was going to say that my date's aren't stored in that particular format, "
IF the field's data type is date, smalldatetime, datetime, datetime2 or time, then the data is stored as integers
& not in a human readable format at all.

           the only time a "format" is relevant is IF the "date" is stored as a varchar (or other string data type)

"but running the query seemed to work so I guess sql parses that since it's a datetime column, it knows the format you provided. Very cool."
yes

>>"Why exactly won't you use mm/dd/yyyy?"
for a number of reasons

a.   my cultural background uses dd/mm/yyyy
b.    11/07/2014 what does this mean?  is it in July or November
       (i.e. without a number > 12 these date formats are ambiguous)
c.   YYYYMMDD (without delimiter) SQL Server will ALWAYS recognize

This is an excellent reference for date information:
The ultimate guide to the datetime datatypes

-----------
I suggest this visual guide for joins
0
engineroomAuthor Commented:
Thanks for all your help!
0
engineroomAuthor Commented:
Hey guys, don't know if you're still around. I have a similar question to this one and wondering if you're up for it? :)
0
PortletPaulfreelancerCommented:
Post the url; when it is available.

Right now I only see 2 unanswered JQuery related questions
0
engineroomAuthor Commented:
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.