Produces Multiple Lines, Need To Populate Single Line
Mar 11, 2008
I need to, ultimately, create a flatfile for exporting insurance information to a third-party vendor. Each individual is to have no more than 1 line per file, which contains their coverage information, if any, on 4 different type of insurance. What i need is for 2 fields in a table to determine the output for multiple fields in the flatfile.
What I have so far works, to an extent. If I have insurance types 1, 2, 4 (of types 1-4) required output is (__ = 2 blank spaces):
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
Y N __ MD XX Y N __ MD XX N __ __ __ __ Y N __ DN XX
If they have coverage, A always = Y, B always = N, C always = blank(null), D is their ins. type, E is their cov. type(CASE statement). if they DON'T have that type of coverage, A always = N and the remaining field are NULL.
After a lot of work, and scouring a forum or 2, I attempted a whole lot of CASE functions. Below is an sample of code representing the 1x statements. This same code is repeated 4 times with the 1x being altered with 2x, 3x, 4x.
CASE HB.PLAN_TYPE
WHEN '10' THEN 'Y'
ELSE 'N' END AS 1A,
CASE HB.PLAN_TYPE
WHEN '10' THEN 'N'
ELSE ' ' END AS 1B,
' ' AS 1C,
CASE HB.PLAN_TYPE
WHEN '10' THEN HB.BENEFIT_PLAN
ELSE ' ' END AS 1D,
CASE HB.PLAN_TYPE
WHEN '10' THEN (CASE WHEN HB.COVRG_CD ='1' THEN 'XX'
WHEN HB.COVRG_CD ='2' THEN 'YY'
WHEN HB.COVRG_CD ='3' THEN 'ZZ'
ELSE 'WW' END)
ELSE ' ' END AS 1E,
It works to an extent. While the desires/required output it above, the output this produces (same scenario) is:
1A 1B 1C 1D 1E 2A 2B 2C 2D 2E 3A 3B 3C 3D 3E 4A 4B 4C 4D 4E
N __ __ __ __ N __ __ __ __ N __ __ __ __ Y N __ DN XX
While there is supposed to be 1 line, regardless of number of insurance types, it only produces 1 line per type. I first tried this in Access, and was able to get it to work, but it required multiple queries resulting in a crosstab, export to Excel and manually manipulate the data, export to text file to manipulate, import back into Excel to manipulate, import back into Access and finally export into a flatfile. Far too much work to produce a file which is why I'm trying to convert it to raw SQL.
Any assistance in this matter would be greatly appreciated.
SELECT ID1, ID2, type, (case when type = '1' then sum(value) else '0' end) as Value1, (case when type = '3' then sum(value) else '0' end) as Value2, (case when type <> '1' and type <> '3' then sum(value) else '0' end) as Value3 FROM table1 WHERE ID1 = 'x' and ID2= 'y' GROUP BY ID1, ID2, Type
I create a rdlc file that contains a line chart with 3 lines that will plot in it. I put the proper data field in "series" and everything plots fine, except I get a spurious line that goes from the end of one line to the start of another line (or possibly back to itself, I can't tell). In another line chart I have 3 lines but get 2 spurious lines.
Anyone know how this happens or how I can fix it? The data is ordered and I don't see anything that would cause this..
I am creating a simple line chart using SSRS 2005. The chart will display date vs. cumulative enrollment. In the line chart I want to display two lines. One line represents expected enrollment data and the other represents actual enrollment data. I would like the expected data line to be a dashed line and the actual line to be a solid line. I have a single dataset that contains data for both lines. I have added a column to the dataset that acts as a flag to indicate whether the data point is expected or actual. I have dropped the enrollment field from my dataset into "Drop data fields here". I have dropped the date field from my dataset into "Drop category fields here". I have dropped the flag field from my dataset into "Drop series fields here".
When I change the style of the series it changes the styles of both lines. How can assign a different line style to each line? Is this even possible with the way I have set up the chart? Is there another way to set up the chart to accomplish what I am trying to do?
Hello,I'm trying to execute a file containing few SQL statements.CREATE VIEW test1 AS SELECT * FROM table1;CREATE VIEW test2 AS SELECT * FROM table2;The standard SQL way is to end a statement with semi-colon.But doing that,it doesn't work in SQL Server.After changing ";" to "GO", it works fine.Is there anyway we can stick to ";" to indicate the end of statement.I don't want to create scripts which works only in SQL Server.Please comment.Thanks in advance.
How do I write a regression test for a stored proc that produces multiple rowsets via multipl e select queries? E.g. CREATE PROCEDURE myProc AS SELECT 'Some stuff', GETDATE() SELECT 'Some more stuff'
For single-select procs, I can create a temp table and INSERT #temp EXEC myProc, then evaluate the contents of the table to verify correct behavior, but that doesn't work in this case.
hi im making a page where i want a single line at a time to be pulled from my MS Database, basically at the moment i have a list of questions, but the page is displaying all the questions from my database, i only want it to pull out 1 and then if the user clicks the true button then it goes to another page and displays another question?
I am trying to sort out the member "Adams" from the committee members, and sum up the total amounts that were donated while he was the committee member.
The Table:
CREATE TABLE contribution_list (contrib_date DATE NOT NULL, donor_name VARCHAR(30) NOT NULL, amount NUMBER(8,2), program VARCHAR2(30), committee_member VARCHAR2(20) NOT NULL, PRIMARY KEY (contrib_date,donor_name,committee_member));
The Code that is giving me errors:(not a group by expression):
CREATE or REPLACE VIEW adams_conrtibution_total as select program, committee_member, sum(amount) from contribution_list where committee_member = 'Adams' group by program;
I am generating hundreds of lines of sql with a tool and wish to execute is in runtime. The sql consists of table creation, procedure creation, aswell as inserts and updates. When i try with SqlDataAdapter I get an error. Does anyone have a solution on what I can do?
I am running this stored Prcedure and getting multiple lines for the output. Below are the queries the create the temp table (its holding the data), and the query that generates the output:
/* this creates the temp table */ Select count(*) as 'Donations', d_vst_id into Donations_temp from dnr_vst_db_rec where convert(varchar(10),d_vst_date) between convert(varchar(10), @Beg_Vst_Date, 112) and convert(varchar(10), @End_Vst_Date, 112) and d_vst_status = 'DN' and d_vst_dontyp in ('E1', 'E2') group by d_vst_id
/* this query generates the output */
select distinct cast(getdate() as varchar(30)) as 'TODAY' ,CONVERT(varchar(10), @Beg_Vst_Date,101) as 'BEGDTE' ,CONVERT(varchar(10), @End_Vst_Date,101) as 'ENDDTE' ,case Donations when '1' then sum(1) else 0 end as 'ONE1' ,case Donations when '2' then sum(1) else 0 end as 'ONE2' ,case Donations when '3' then sum(1) else 0 end as 'ONE3' ,case Donations when '4' then sum(1) else 0 end as 'ONE4' ,case Donations when '5' then sum(1) else 0 end as 'ONE5' ,case Donations when '6' then sum(1) else 0 end as 'ONE6' ,case Donations when '7' then sum(1) else 0 end as 'ONE7' ,case Donations when '1' then Sum(0) when '2' then Sum(0) when '3' then Sum(0) when '4' then Sum(0) when '5' then Sum(0) when '6' then Sum(0) when '7' then Sum(0) else Sum(1) end as 'ONEA' from Donations_temp group by Donations
I know very simple SQL queries but I need help with this one. I have multiple lines in a SQL database that I need to run. Basically, I need to run this (the bracketed text and the XXX are place holders):
DELETE FROM [tableName] WHERE [columnName] = 'XXXXX'
But I need to run it around 90 times where XXXXX is a unique variable each time. I could create 90 lines similar to this one but that would take way too much time to run. Any suggestions for a noob?
Thanks,
- MT
-=<>=-=<>=-=<>=-=<>=-=<>=- Matt Torbin President Center City Philadelphia Macintosh Users Group http://www.ccpmug.org/
Hi, I have a scenario, where I have a string column from database with value as "FTW*Christopher,Lawson|FTW*Bradley,James". In my report, I need to split this column at each " | " symbol and place each substring one below the other in one row of a report as shown below .
How can I run through an array of ID's and delete the fields associated to it in a single table? I have a lot of bad data that needs to be removed. BTW, I'm using the SQL Server Management Studio to do this.
I'm currently doing by hand by using the following.
USE database; DECLARE @id integer SET @id = XXXXX DELETE FROM table WHERE id = @id;
I would like to use the same code but with a slight variation to support an array.
Just messing about with a silly little problem in SQL which has me stumped! The problem goes like this; There are lots of books. There are lots of authors. Every book has atleast one author (primary author). Some books have a secondary author too!
Books(BookId, BookName) Authors(AuthorId, AuthorName) BookAuth(BookId, AuthorId, PrimaryAuth) Where primary auth is a bit field; 1 denotes primary, 0 denotes secondary.
The problem is trying to return a single line per book - something like
BookNamePrimary AuthorSecondary Author Book 1DaveNULL Book 2JohnDave Book 3LauraJohn
The following code is the little test script I've written which you may wish to utilise
IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'Books') BEGIN DROP TABLE Books END IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'Authors') BEGIN DROP TABLE Authors END IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'BookAuth') BEGIN DROP TABLE BookAuth END
INSERT INTO Books SELECT 1, 'MS Access Book'UNION ALL SELECT 2, 'Microsoft Excel for Peanuts' UNION ALL SELECT 3, 'Some Autobiography'UNION ALL SELECT 4, 'SQL for Beginners'UNION ALL SELECT 5, 'Advanced T-SQL'UNION ALL SELECT 6, 'I can''t think of another title'
INSERT INTO Authors SELECT 1, 'Dave'UNION ALL SELECT 2, 'Steve'UNION ALL SELECT 3, 'John'UNION ALL SELECT 4, 'Joanne'UNION ALL SELECT 5, 'Christina'UNION ALL SELECT 6, 'Alex'
INSERT INTO BookAuth SELECT 1,1,1 UNION ALL SELECT 2,1,0 UNION ALL SELECT 1,2,1 UNION ALL SELECT 1,3,1 UNION ALL SELECT 3,3,0 UNION ALL SELECT 4,4,1 UNION ALL SELECT 4,5,1 UNION ALL SELECT 5,6,1 UNION ALL SELECT 6,6,0
SELECT * FROM bookauth
DROP TABLE Books DROP TABLE Authors DROP TABLE BookAuth
It should be so easy - but I can't get it so far! :D
i have a query in which i need to show all rows of same id in singel row and last column should be comma seperated .my query is :
BEGIN TRY SET NOCOUNT ON DECLARE @l_numberOfRecordsInserted INT -- Check if valid Application User ID is passed IF ( @i_AppUserId IS NULL ) OR ( @i_AppUserId <= 0 )
[code]....
so last column section name should come as comma seperated .
Hi, I am executing the folowing statements in my stored procedure, I want to ask that can all these staments will be executed and combined in a single line of code, meaning can we run all these statemnts in a single line. if not, then any other optimized way to perform this task and other way which will execute with less load on the server.
I am working on conversion of Crystal reports to SSRS. The existing report has two level of grouping. So I have included 2 list inside a third list and then done the grouping. Is there another way to do multiple grouping ? Every thing is working fine except for getting one extra blank page at the end of report with only header and footer. I have properly checked the body height and width and page margins. It has something to do with list but i cant figure out what.
When I edit package configuration files inside of Visual Studio, it puts all the XML on a single line. Does anyone know how to automatically spread the information onto multiple lines, to make comparing file differentials easier.
I am in doubt if its possible to make a select statement which enables me to consolidate multiple lines in the same table.
I have a table with a lot of companies and figures for each company.
Some of the companies owns some of the other companies in the table and in reverse, some of the companies are owned by some of the companies in the table.
I have a lot of columns, but basically the most important columns are:
Company Name, Company Mother , Company Daughter.
Each company has also a revenue column.
What I want to do is to consolidate all figures for absolute mothers e.i. companies which are not owned by another company in the list.
I therefore need a select statement which says something like:
Get the revenue of companies not owned by another company (e.i. absolute mother). Add to this, the revenue of all its daughters. Add to this the revenue of all the daughters daughters etc. until there are no daughters left.
In other words - aggregate the revenue for all the companies in the group under the name of the ultimate parent company.
I can easily select and add the revenue for the first level of direct daughters, but I dont know how many more daughters the daughters has etc.
Hi, Is it possible to populate a dataset with tables returned by a stored proc?Consider this: BEGINSELECT * FROM Table1SELECT * FROM Table2SELECT * FROM Table3END If that is my stored proc, could I call it from a page and automatically populate a dataset with all 3 tables (if yes, then how?), or would I have to make 3 seperate calls to the db for each table? Thanks
We are using a table that may give 1 to and unknown number of data elements (ie. years) . How can we break this to show only three years in each row. Since we don't know the number years we really won't know the number of rows needed. Years are stored in their own table by line.
car make year1 year2 year3 A volare 1995 1996 1997 a volare 1997 1998 1999 b toyat 1965 1966 1968
We can pivot out the first X# but we don't know how many lines so we don't know how many rows we will be creating.
Hi, I have a master/detail scenario whereby to populate a formview, the user selects a row in the gridview. The SqlDataSource used to populate the formview needs to check 2 columns from the gridview, ZRef (which is the SelectedValue of the Gridview) and ZName (a string). From searching the forums it looks like I have to programmatically assign the SelectParameters in order to get ZName from the gridview. Seems fair enough, so I set the parameters in the 'Selecting' event of the SqlDataSource. (Code shown below). However the formview never shows. In debug I can see that the values I'm setting in the SelectParameters.Add are correct; and if I set the default values for those parameters in the SqlDataSource itself, everything works fine. Can anyone point me to some sample VB code that does this - I'm sure this must be a common situation. Thanks. Protected Sub sdsOff_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Dim tmpSDS As SqlDataSource Dim tmpLBL As Label tmpSDS = CType(FormView1.FindControl("sdsOff"), SqlDataSource) If tmpSDS Is Nothing Then Server.Transfer("ErrorOnPage.htm") End If tmpLBL = Me.GridView1.Rows(GridView1.SelectedIndex).FindControl("lblName") If tmpLBL Is Nothing Then Server.Transfer("ErrorOnPage.htm") End If tmpSDS.SelectParameters.Clear() tmpSDS.SelectParameters.Add("ZRef", GridView1.SelectedValue) 'In debug, this shows 7 - which is correct tmpSDS.SelectParameters.Add("ZName", tmpLBL.Text) 'In debug, this shows 'Fred Bloggs' - which is correct End Sub
What I need to do is re-populate a unique number into multiple fields,
Let me explain, An employee can appear in the first table only once but can be in the second table multiple times with multiple employee numbers .There is a field called TFN that is unique and we are using it to create a unique id called KRid so what I have done is created 2 tables namely TEST_TBL and TEST2_TBL . In TEST_TBL I am populating a KRid with a unique no being produced by the TFN field only once i.e 12345 being the resulting unique id number. If an employee has 2 employee numbers i.e empno 1 and empno 1000,only employee no 1 will have the unique KRid created but nothing for 1000 because the record already exists , so what has me stumped is that the TFN for employee empno 1 and the TFN for empno 1000 are the same. How do I get the KRid (12345 from empno 1) to populate empno 1000 in TEST2_TBL , The second table has all records in so I can group the second table by TFN id but how do I populate employee 1000 in the second table with the KRid 12345.
Please help!!!!! Below are how the tables are set up and an example of the result.
TABLE 1
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST_TBL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TEST_TBL] GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[TEST2_TBL]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[TEST2_TBL] GO
SELECT NPE000.EmpNumber, NPET00.RecordStatus, NPE000.KR_ID, NPE000.Surname, NPE000.FirstName, NPE000.SecondName, NPE000.Class, NPE000.DateEmployed, NPE000.DateOfBirth, NPE000.HoursPerDay, NPE000.HoursPerWeek, NPE000.PassportNo, NPE000.AwardCode, NPE000.EmailPayslipTo, NPE000.Location, NPE000.Grade, NPE000.DateTerminated, NPE000.EmploymentType, NPE000.DistCode, NPE000.EmpStatus, NPET00.TaxRefNo FROM NPE000 NPE000, NPET00 NPET00 WHERE NPET00.RecordStatus = 0 and NPET00.TaxRefNo <> ' 111111111' and NPET00.TaxRefNo <> ' 000000000' AND LENGTH(NPET00.TaxRefNo) >= 9 AND LENGTH(NPE000.KR_ID) >= 0 AND NPE000.EmpNumber = NPET00.EmpNumber
Query goes as follows for table 2:
SELECT NPE000.EmpNumber, NPE000.FirstName, NPE000.Surname, NPE000.Class, NPE000.Location, NPE000.EmploymentType, NPE000.EmpStatus, NPET00.TaxRefNo, NPE000.Paypoint, NPE000.KR_ID, FROM NPE000, NPET00 WHERE Recordstatus = 0 and (EmploymentType = 1 AND EmpStatus = 1 AND NPE000.EmpNumber = NPET00.EmpNumber
From this you can see that in table 1 it will only create 1 KR_ID for only one employee number but in table 2 I am bringing through all employee records. In table 2 I can group by NPET00.TaxRefNo which will bring all NPET00.TaxRefNo's togeather. From that I would like to populate the other employee numbers with the unique KR_ID.
I tried to set double in bottom border of text file. But it turned out to be single line either Previewing in Designer or Printing or Exporting to PDF. Does anyone have any idea?
I have 5 or more tables to join to get a particular output which has to be sent to a destination table. In the 5 tables some are inner joins and some are left outer join. I am opting for stored procedure at this point. But I would like to know how can this be done in data flow transformations having multiple souce and merge joins or any other alternates. I tried using merge join, but this does not accept more than two tables.
I saw this simple post which kick started me to use ssis transformations to stored procedures. But I encounter issue. http://www.mssqltips.com/tip.asp?tip=1322 error "The destination component does not have any available inputs for use in creating a path".
Any better way to query SQL 2012 to display the code of a stored proc to a single line. I'm trying to write a script to insert the contents of the procs between my devestprod environments. So people can query a single table for any proc that is different between environments. At the moment I am using the syscomments view and the text column but the problem here is if you get a lengthy proc it cuts it up into multiple rows.
I can get around it by converting the text to a varchar(max) and outer joining the query, but as you can see by my code below I have to try and guess what the maximum number of rows I'm going to get back for my largest proc. If someone adds a new one that returns 8 rows I'm going to miss it with this query.
I am storing formatted data (including spaces and line breaks) in a single field in a table. When I run a report on that field, the preview of the report is automatically removing all the extra spaces and line breaks, making the report unreadable. When exporting the report to PDF or printing it, it shows the line breaks and spaces as expected.
Does anyone know how to make the report preview show the spaces and line breaks?
