Solved

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

Posted on 2009-07-07
1
291 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
1 Comment
 
LVL 15

Accepted Solution

by:
mcmonap earned 500 total points
Comment Utility
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

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.

Join & Write a Comment

Suggested Solutions

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

744 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

18 Experts available now in Live!

Get 1:1 Help Now