none
Update Query with case statement

    Question

  • Hi

       I am tried to write the update query with below conditions. I am not getting the exact query can anyone help me out.

    The below is the table script.

    CREATE TABLE [dbo].[tblCustomerSalesInfo](

                    [Buyer] [bigint] NULL,

                    [Email] [varchar](200) NULL,

                    [salesoffice] [varchar](50) NULL,

                    [currentyrsales] [money] NULL,

                    [DupFlag] [bit] NULL

    ) ON [PRIMARY]

     

    insert into tblCustomerSalesInfo values(1463071,'PATRICK.SOUTH@WAYEST.COM','Unmanaged Account',572.09,0)

    insert into tblCustomerSalesInfo values(707195,'PETER.MORETTI@INNOPHOS.COM','Managed',1338,0)

    insert into tblCustomerSalesInfo values(798395,'jslopnick@northernsafety.com','Managed',68.35,0)

    insert into tblCustomerSalesInfo values(1485867,'jslopnick@northernsafety.com','Unmanaged Account',4950.99,0)

    insert into tblCustomerSalesInfo values(675104,'jslopnick@northernsafety.com','Unmanaged Account',1786.05,0)

    insert into tblCustomerSalesInfo values(709878,'jslopnick@northernsafety.com','Unmanaged Account',18681,0)

    insert into tblCustomerSalesInfo values(808679,'TBISHOP@MAIL.UCH.ORG','Unmanaged Account',10935.41,0)

    insert into tblCustomerSalesInfo values(1457561,'tgarrett0@bellsouth.net','Unmanaged Account',8758.72,0)

    insert into tblCustomerSalesInfo values(728662,'VDECARLO@NORTHERNSAFETY.COM','Unmanaged Account',42.49,0)

    insert into tblCustomerSalesInfo values(489194,'DAN.PIVIN@ESPN.COM','Managed',23653.14,0)

    insert into tblCustomerSalesInfo values(778427,'DARRIN3841@AOL.COM','Unmanaged Account',3875.04,0)

    insert into tblCustomerSalesInfo values(774624,'DARYL.PUGH@INNOPHOS.COM','Managed',1338,0)

    insert into tblCustomerSalesInfo values(1461166,'DOC.ARTHUR@MASTEC.COM','Unmanaged Account',641.55,0)

    insert into tblCustomerSalesInfo values(648413,'reg030@msn.com','Managed',5852.67,0)

    insert into tblCustomerSalesInfo values(626358,'reg030@msn.com','Managed',349.68,0)

    insert into tblCustomerSalesInfo values(739409,'reg030@msn.com','Unmanaged Account',13299.34,0)

    insert into tblCustomerSalesInfo values(706579,'kelly.hooper@innophos.com','Managed',2783.31,0)

    insert into tblCustomerSalesInfo values(684392,'WILLIAM.CARTON@INNOPHOS.COM','Managed',33750,0)

    insert into tblCustomerSalesInfo values(1590555,'DWAY@CARESAFETY.COM','Unmanaged Account',4,0)

    insert into tblCustomerSalesInfo values(849232,'FRANK.SAVAGE@US.RHODIA.COM','Managed',1058.94,0)

    insert into tblCustomerSalesInfo values(649328,'MMCCULLOUGH@SANJEL.COM','Managed',1876.71,0)

    insert into tblCustomerSalesInfo values(644798,'MMCCULLOUGH@SANJEL.COM','Managed',198.51,0)

    I want the query for the below conditions

    If same Email is on more than one Buyer, we need to update the DugFlag accordingly

     

    1. 1.       If salesoffice has Managed, then DupFlag = 1 and rest of the record in that group DupFlag=0
    2. 2.       If salesoffice has multiple Managed, then hightes currentyrsales record is set DupFlag=1 and rest in the group DupFlag=0
    3. 3.       If sales office has only UnManaged, then the highest Currentyrsales record in that group DupFlag=1 and rest DupFlag=0

    Please help me out to get the query with exact result.

    Thanks

    Sree

    Monday, April 16, 2012 6:40 PM

Answers

  • Try:

    WITH R AS (
    SELECT
    	email,
    	COUNT(DISTINCT buyer) AS cnt
    FROM
    	tblCustomerSalesInfo
    GROUP BY
    	email
    HAVING
    	COUNT(DISTINCT buyer) > 1
    ),
    S AS (
    SELECT
    	A.DupFlag,
    	ROW_NUMBER() OVER(
    	PARTITION BY A.email 
    	ORDER BY CASE WHEN A.salesoffice = 'Managed' THEN 1 ELSE 2 END, A.currentyrsales DESC, A.buyer) AS rnk
    FROM
    	tblCustomerSalesInfo AS A
    	INNER JOIN
    	R AS B
    	ON A.email = B.email
    )
    UPDATE S
    SET DupFlag = 1
    WHERE rnk = 1;
    GO
    SELECT *
    FROM tblCustomerSalesInfo
    ORDER BY email, CASE WHEN salesoffice = 'Managed' THEN 1 ELSE 2 END, currentyrsales DESC, buyer;
    GO


    AMB

    Some guidelines for posting questions...

    • Marked as answer by KJian_ Monday, April 23, 2012 6:02 AM
    Monday, April 16, 2012 7:06 PM
    Moderator

All replies

  • Try the following

    ;WITH CTE AS(
    SELECT *
    	,ROW_NUMBER() OVER(PARTITION BY Email ORDER BY salesoffice,[currentyrsales] DESC) Row
    FROM #tblCustomerSalesInfo) 
    UPDATE C
    SET DupFlag = CASE
    	WHEN salesoffice = 'Managed' AND Row = 1 THEN 1
    	WHEN salesoffice = 'UnManaged' AND Row = 1 THEN 1
    	ELSE DupFlag END
    FROM CTE C
    WHERE EXISTS(SELECT * 
    		FROM CTE 
    		WHERE Email = C.Email 
    			AND Row <> C.Row)


    Abdallah El-Chal, PMP

    Monday, April 16, 2012 6:59 PM
  • Try:

    WITH R AS (
    SELECT
    	email,
    	COUNT(DISTINCT buyer) AS cnt
    FROM
    	tblCustomerSalesInfo
    GROUP BY
    	email
    HAVING
    	COUNT(DISTINCT buyer) > 1
    ),
    S AS (
    SELECT
    	A.DupFlag,
    	ROW_NUMBER() OVER(
    	PARTITION BY A.email 
    	ORDER BY CASE WHEN A.salesoffice = 'Managed' THEN 1 ELSE 2 END, A.currentyrsales DESC, A.buyer) AS rnk
    FROM
    	tblCustomerSalesInfo AS A
    	INNER JOIN
    	R AS B
    	ON A.email = B.email
    )
    UPDATE S
    SET DupFlag = 1
    WHERE rnk = 1;
    GO
    SELECT *
    FROM tblCustomerSalesInfo
    ORDER BY email, CASE WHEN salesoffice = 'Managed' THEN 1 ELSE 2 END, currentyrsales DESC, buyer;
    GO


    AMB

    Some guidelines for posting questions...

    • Marked as answer by KJian_ Monday, April 23, 2012 6:02 AM
    Monday, April 16, 2012 7:06 PM
    Moderator
  • >> I am tried to write the update query sic with below conditions. I am not getting the exact query can anyone help me out. <<

    There is an UPDATE statement in SQL; a query is totally different. 
    Your table design is completely wrong. It has no key and never have a key. You use BIT flags just like this was assembly language. You do not know ISO-1179 naming rules. That silly “tbl” prefix makes SQL people laugh and reminds older programmers of early file system that needed meta data. T-SQL programmers know never use MONEY; besides being proprietary, it does incorrect math. Those sales office names make no sense – they are an account type spelled out in full for display, just like COBOL in the 1950. BIGINT for a buyer? Don't they have a DUNS? Where is the year for which that sales total applies? The term current year is relative and cannot be part of a data element name. Rows are not records. 

    CREATE TABLE Annual_Customer_Sales
    (buyer_duns CHAR(9) NOT NULL,
     sales_year INTEGER NOT NULL,
     PRIMARY KEY (buyer_duns, sales_year), 
     email_address VARCHAR(255) NOT NULL, 
     account_type CHAR(1) NOT NULL 
      CHECK (account_type IN ('U', 'M')), 
     sales_tot DECIMAL (12,2) NOT NULL); 

    This is still wrong; the email address has nothing to do with sales and needs to be somewhere else.  

    >> if same email is on more than one buyer, we need to update the dupflag accordingly <<

    NO, you need to discover this fact with a query or a VIEW. We put flags like that in punch cards. 
     
    >> if account_type is managed, then dupflag = 1 and rest of the record [sic] in that group dupflag=0 <<

    group? Where is the scalar value in a column that defines these group? You do not know the Information Principle. Since tables have no ordering this makes no sense! The rest of your post makes no sense in relational terms.

    >> please help me out to get the query with exact result. <<

    You need more help than you can get here. If I used this as an example in one of my books, people would think I was being absurd.  


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Tuesday, April 17, 2012 1:55 AM