Solved

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

Posted on 2014-09-26
43
135 Views
Last Modified: 2014-10-28
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!
0
Comment
Question by:engineroom
  • 20
  • 14
  • 8
  • +1
43 Comments
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
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
 
LVL 45

Expert Comment

by:Vitor Montalvão
Comment Utility
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
 
LVL 3

Author Comment

by:engineroom
Comment Utility
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
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 
LVL 3

Author Comment

by:engineroom
Comment Utility
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
 
LVL 3

Author Comment

by:engineroom
Comment Utility
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
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
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
 
LVL 3

Author Comment

by:engineroom
Comment Utility
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
 
LVL 3

Author Comment

by:engineroom
Comment Utility
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
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
look at the claims.currency field
INNER JOIN currency cur ON claims.currency = cur.currencyID
0
 
LVL 3

Author Comment

by:engineroom
Comment Utility
That's actually right..... currency in the claims table is actually currencyID in the currency table.
0
 
LVL 3

Author Comment

by:engineroom
Comment Utility
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
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
try running this:
select * from claims where client = 'APLM' order by currency

look for anything not usd
0
 
LVL 3

Author Comment

by:engineroom
Comment Utility
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
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
change the groupby , cur.currency to claims.currency

see if that helps
0
 
LVL 3

Author Comment

by:engineroom
Comment Utility
Changed it and still the same thing, except now it shows the currencyID (claims.currency) instead of the actual currency name.
0
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
thats what happens when you change the select, change the group by
0
 
LVL 3

Author Comment

by:engineroom
Comment Utility
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
 
LVL 3

Author Comment

by:engineroom
Comment Utility
Just to be clear, i did this:


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

Open in new window

0
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
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
 
LVL 3

Author Comment

by:engineroom
Comment Utility
Thanks Aaron... i really appreciate your help. If there's another way to get the data, i'm all for it!

damien
0
 
LVL 3

Author Comment

by:engineroom
Comment Utility
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
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 
LVL 3

Author Comment

by:engineroom
Comment Utility
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
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
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
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
Comment Utility
"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
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
Paul, how do you feel about using
datepart(m, claims.dateon) = 9?
0
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
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
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
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
 
LVL 3

Author Comment

by:engineroom
Comment Utility
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
 
LVL 3

Author Comment

by:engineroom
Comment Utility
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
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
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
 
LVL 38

Expert Comment

by:Aaron Tomosky
Comment Utility
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
 
LVL 3

Author Comment

by:engineroom
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
>>"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
 
LVL 3

Author Closing Comment

by:engineroom
Comment Utility
Thanks for all your help!
0
 
LVL 3

Author Comment

by:engineroom
Comment Utility
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
 
LVL 48

Expert Comment

by:PortletPaul
Comment Utility
Post the url; when it is available.

Right now I only see 2 unanswered JQuery related questions
0
 
LVL 3

Author Comment

by:engineroom
Comment Utility
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

6 Experts available now in Live!

Get 1:1 Help Now