Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

Posted on 2009-07-07
1
Medium Priority
?
327 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 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

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

I have a large data set and a SSIS package. How can I load this file in multi threading?
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

782 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