Expiring Today—Celebrate National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Insert into table - into columns based upon where clause in select from

Posted on 2009-07-07
1
Medium Priority
?
320 Views
Last Modified: 2012-08-14
I have the below table - it has three seperate column "sets"/"groups" where I want to take my select code sample and where row = 1 insert into 1st column set, where row = 2 insert into 2nd. etc.


CREATE TABLE [dbo].[tblPROFEE_RVU_Mods](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [HCPCS] [varchar](20) NULL,
      [carrierLoc] [varchar](20) NULL,
      ------ First col set
      [1mod] [varchar](20) NULL,
      [1facRVU] [decimal](5, 2) NULL,
      [1nonFacRVU] [decimal](5, 2) NULL,
      [1mpRVU] [decimal](5, 2) NULL,
      [1fac] money NULL,
      [1nonFac] money NULL,
------ second col set
      [2mod] [varchar](20) NULL,
      [2facRVU] [decimal](5, 2) NULL,
      [2nonFacRVU] [decimal](5, 2) NULL,
      [2mpRVU] [decimal](5, 2) NULL,
      [2fac] money NULL,
      [2nonFac] money NULL,
------ third col set
      [3mod] [varchar](20) NULL,
      [3facRVU] [decimal](5, 2) NULL,
      [3nonFacRVU] [decimal](5, 2) NULL,
      [3mpRVU] [decimal](5, 2) NULL,
      [3fac] money NULL,
      [3nonFac] money NULL,
SELECT DISTINCT c.hcpcs, c.CarrierLoc,
                        CASE WHEN r.MOD IS NULL THEN '-'
                             ELSE r.[MOD]
                        END AS [MOD],
                        r.[FULLY IMPLEMENTED FACILITY PE RVU],
                        r.[FULLY IMPLEMENTED NON-FAC PE RVU],
                        r.[MP RVU],
                        c.Fac,
                        c.NonFac,
                        CASE WHEN c.mod IS NULL OR c.mod = '' THEN '1' WHEN c.mod = '26' then '2' ELSE '3' END AS row
              FROM      dbmasterdata.dbo.tblPROFEE AS c
                        LEFT JOIN dbmasterdata.dbo.tblPROFEE_RVU AS r ON c.HCPCS = r.HCPCS
                                                                      AND ISNULL(r.MOD, N'') = ISNULL(c.MOD, N'')
			  WHERE c.mod <> '53'

Open in new window

0
Comment
Question by:tbaseflug
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 15

Accepted Solution

by:
mcmonap earned 2000 total points
ID: 24799524
Hi
I am not sure you are approaching this in the right way (not really enough info) but I would suggest your main table is formatted more like this:
CREATE TABLE [dbo].[tblPROFEE_RVU_Mods](
      [ID] [int] IDENTITY(1,1) NOT NULL,
      [HCPCS] [varchar](20) NULL,
      [carrierLoc] [varchar](20) NULL,
      [1mod] [varchar](20) NULL,
      [1facRVU] [decimal](5, 2) NULL,
      [1nonFacRVU] [decimal](5, 2) NULL,
      [1mpRVU] [decimal](5, 2) NULL,
      [1fac] money NULL,
      [1nonFac] money NULL,
      [colset] INT
There are less "duplicated" columns and there is a colset column instead to identify which "sets"/"groups" your data relates to, you might need to work out the relationships of your data and then look at your database design.
To do what you want if I am understanding you correctly would mean that only 1  "sets"/"groups" of would ever be populated meaning that you would have a great deal of empty space in your table which is undesirable.  If you still want to do this I suggest you do it in 3 seperate quereis one for each  "sets"/"groups".  I think the snippet below for example would return the data for "sets"/"groups"  2, you would then have a seperate one for 1 and for 3 and run them independantly.

SELECT DISTINCT
	c.hcpcs
	, c.CarrierLoc
	, CASE
		WHEN r.[MOD] IS NULL THEN '-'
		ELSE r.[MOD]
		END AS [MOD]
	, r.[FULLY IMPLEMENTED FACILITY PE RVU]
	, r.[FULLY IMPLEMENTED NON-FAC PE RVU]
	, r.[MP RVU]
	, c.Fac
	, c.NonFac
	FROM
		dbmasterdata.dbo.tblPROFEE AS c
		LEFT JOIN dbmasterdata.dbo.tblPROFEE_RVU AS r ON c.HCPCS = r.HCPCS  AND ISNULL(r.[MOD], N'') = ISNULL(c.[MOD], N'')
	WHERE
		c.[mod] = '26'

Open in new window

0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

719 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