0

I have two tables: costumer table and invoice table. How to calculate sum(value) for each costumer (id) when I have calculated variables in group by statement - without puting code for calculated variables in group by statement (in group by should stay only name of the variable)? Please find me the solution wrom which total sql code is as small as possible.

select
       p.id,
       p.name,
       case when
        (p.name not like ('%!!!%') or p.name not like ('%XXX%') or p.name not like ('%???%')) then 0 else 1 end as validity_name,
       p.surname,
       case when
        (p.surname not like ('%!!!%') or p.surname not like ('%*XXX%') or p.surname not like ('%???%')) then 0 else 1 end as validity_surname,
       p.adress,
       sum(pr.value) as value
from   [dbo].[_data_CRM_COSTOMER] as p inner join
       [dbo].[_data_CRM_INVOICE] as r on (r.partner_id=p.id) 
group by id, name, validity_name, surname, validity_surname, adress
1
  • 2
    "total sql code is as small as possible" - why does the size of the code matter?!
    – AakashM
    Jul 25, 2014 at 15:07

1 Answer 1

1

I think your CASE statements may be flawed as you're always going to have validity_name and validity_surname equal to 0, because the name cannot be 3 different things at once.

When do you want the validity_name to equal 1?

Regardless of that fact, I like the APPLY VALUES method of grouping on a computed column.

Using AdventureWorks as an example, this works like so:

USE AdventureWorks;
GO

SELECT
  FirstName,
  ValidName
FROM Person.Person AS p
CROSS APPLY (VALUES(CASE WHEN FirstName = 'Kim' THEN 1 ELSE 0 END)) AS a(ValidName)
GROUP BY FirstName, ValidName

When applied to your query, it should be something like the following (untested, use the example above to build your query):

select
       p.id,
       p.name,
       p.surname,
       p.adress,
       a.validity_name,
       b.validity_surname,
       sum(pr.value) as value
from   [dbo].[_data_CRM_COSTOMER] as p inner join
       [dbo].[_data_CRM_INVOICE] as r on (r.partner_id=p.id) 
        CROSS APPLY (VALUES(case when(p.name not like ('%!!!%') or p.name not like ('%XXX%') or p.name not like ('%???%')) then 0 else 1 END)) AS a(validity_name)
        CROSS APPLY (VALUES(CASE WHEN p.surname not like ('%!!!%') or p.surname not like ('%*XXX%') or p.surname not like ('%???%') then 0 else 1 END)) as b(validity_surname)
group by id, name, validity_name, surname, validity_surname, adress
4
  • Thanks Mark very much! Cross apply works. Please help me with validity_name and validity_surname variables.
    – Anja
    Jul 28, 2014 at 11:58
  • Before import costumer database for direct marketing activities (sending direct mailing) I want to check (control) if names and surnames are ok, because somethimes agents in call center istead of insering name (in name column) - insert "XXX" or "XXXXXXX" or "????" (instead of for example "Kim"). How can i "hunt" those names and surnames? So that I lates in code can say where validity_name not equal 1.
    – Anja
    Jul 28, 2014 at 12:05
  • @Anja One way of fixing what you have it to replace the OR between each NOT LIKE clause with AND. Jul 28, 2014 at 12:08
  • You could also change your CASE statements to something like CAST(CHARINDEX('!!!',p.name,1) + CHARINDEX('XXX',p.name,1) + CHARINDEX('???',p.name,1) AS BIT)
    – MickyT
    Aug 24, 2014 at 18:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.