Transact SQL :: CTE To Peg Char Values To Int Values In Radio-style Chart
Aug 26, 2015
I developed the following T-SQL query that runs successfully, but I was looking for a more efficient and concise way to do this. Is there a CTE that can replace all of these case statements? I've updated my query as below. Although this sample query works, it's not working for my real data. Instead, I get an error. At the bottom is the error part of my real query.I copied all of the tables from the first query block below. But when I wrote the bottom query block, it underlined in red the words "answer" and "question." It says "Invalid column name".Â
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#hard_values')
) DROP TABLE #hard_values;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#dummy_data')
) DROP TABLE #dummy_data;
if exists (select * from tempdb.dbo.sysobjects o where o.xtype in ('U') and o.id = object_id(N'tempdb..#temp')
I have to use the above comma separated values into a SQL Search query whose datatype is integer. How would i do this Search query in the IN Operator of SQL Server. My query is :
declare @id varchar(50) set @id= '3,4,6,7' set @id=(select replace(@id,'''',''))-- in below select query Id is of Integer datatype select *from ehsservice where id in(@id)
But this query throws following error message:
Conversion failed when converting the varchar value '3,4,6,7' to data type int.
I am trying to audit data quality based on some defined data quality rules. The rules are stored in tables and processed using stored procedures. I am facing a problem while generating audits. Let's say I am trying to audit data in OrderDetail table. The table design is mentioned below...I inserted some sample data into the table using RedGate data generator.The audit table output I am expecting is as mentioned in the screenshot below
Its the PrimaryKeyAttributeValues column I am facing problems with. I am using STUFF function within a dynamic SQL query to get the primary key's as a list of comma separated values.
I have a table with 2 columns and my source data looks like this..
PolicyNum  InsCode   Â
1ABC12Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â 1002Â Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â 1003Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â 1004Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â 1005Â Â Â Â Â Â Â Â
[Code] ....
My output should look like this..I need T-sql to get below output.
PolicyNum  InsCode1  InsCode2   Â
1ABC12Â Â Â Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â 1005Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â Â Â Â 1002Â Â Â Â Â Â 1006Â Â Â Â Â Â Â Â 1ABC12Â Â Â Â Â Â Â Â Â Â Â 1003Â Â Â Â Â Â 1004Â Â Â Â Â Â Â 1ABC20Â Â Â Â Â Â Â Â Â Â Â Â 1001Â Â Â Â Â Â 1005Â Â Â Â Â Â Â Â
[Code] ...
Basically it's converting certain row values to new column. Every PloicyNum will have 1001 to 1006 Fixed InsCode values as a group.
Rule-1: InsCode value 1001 should always mapped to 1005Â Â Â Â Â Â Â Â Â Â Â Â Â InsCode value 1002 should always mapped to 1006 Â Â Â Â Â Â Â Â Â Â Â InsCode value 1003 should always mapped to 1004Â
Rule-2: For a policyNum, If any Inscode value is missed from the group values 1001 to 1006, still need to mapped with corresponding values as shown in Rule-1
In the above sample data..
for PolicyNum - 1ABC20 , group values 1003,1006 are missing for PolicyNum - 1ABC25 , group values 1002,1003,1004,1005,1006 are missing
Create Table sampleDate (PolicyNum varchar(10) not null, InsCode Varchar(4) not null) Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1001')Â Â Â Â Â Â Â Â
Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1002')Â Â Â Â Â Â Â Insert into Sample Date(PolicyNum, InsCode) Values ('1ABC12','1003')Â Â Â Â Â Â
I have a stored procedure that selects the unique Name of an item from one table.Â
SELECT DISTINCT ChainName from Chains
For each ChainName, there exists 0 or more StoreNames in the Stores. I want to return the result of this select as the second field in each row of the result set.
SELECT DISTINCT StoreName FROM Stores WHERE Stores.ChainName = ChainName
Each row of the result set returned by the stored procedure would contain:
ChainName, Array of StoreNames (or comma separated strings or whatever)
I´m working with chart type bar (Simple Bar) and i received four values from stored procedure, all values are negative numbers. The Reporting Service shows the values in the chart, but, doesn't show the "bars".... If one of four values will be positive, the report shows the values and the "bars" but if all values will be negative, the report shows just de values.
What do I do for the bars appear with four negative values ?
I am facing some problems in displaying data in a chart on a report. Let me give you a background on the report
The report has 8 parameters, Industry,CType,PType, S#, ECode, Start Date, End Date and Trend.
The layout of the report has a table and a chart.
Both the table and the chart need to display Normalized Value ( count of ECode / count of PType that are closed in the date range selected) and the Trend ( where the trend can be Weekly, Monthly, Yearly or Quarterly).
Both the chart and the table should display the data for the entire date range i.e if the Trend is Month and the Date Range is Jan 2007 Jan 2008, then the table and the chart should display months from Jan 2007, Feb 2007 .... Jan 2008 irrespective of whether or not there are error codes present for that month.
In order to satisfy the point 4 mentioned above, we have created the main dataset in such a way that it would have one row for each day between Jan 2007 to Jan 2008. Any fields that do not have data corresponding to a date will come up as NULL in the dataset.
Now, we need to display a chart in the report which would be a Trend v/s the Normalized Value chart for each ECode. So, we have put in the Normalized value in the 'Value' field of the chart, Trend group in the 'Category' field of the chart and ECode in the 'Series' group of the chart. The chart displays fine except for one extra series for the NULL values in the Error Code ( the one in green below). Is there any way in which we can do away with this NULL series without changing the dataset? I tried using filters for the 'Series' but it doesn't work ( used filters like <>NULL, <> "" , <>Nothing, cstr(ECode) <> NULL/"" etc ).
Hi to everyone.I have this problem in my chart control in report.I am retrieving datas from my database.My x-axis is consist of dates of the year,y-axis is the hit counts.My problem here is when i only have hits in only one month(ex december) and almost over 20 hits are retrieved,the bars of my graph become so overcrowded and overlayed which make the value unclear to see.And also another thing is my legend repeats the color when there are too much data.is there anyway to fix this or this is just a ReportingService bug??Hope not..hehe.. Thanx
Hi everyone!I am working with Delphi v7 and MS SQLServer.I am trying to insert data in a table with a SQL sentence. Some of thefields of my table are type char or varchar, and they can have nullvalues.¿What do i have to write in the SQL sentence to insert a null value inthose fields?I tried with '', an empty String, but it doesnt work, the tablesstores an empty String (logical :-)).In the SQLServer GUI you have to press CTRL + 0 to insert a NULLvalue, but how can i tell this to the SQLServer through a SQLSentence?Well, thank you very much.
I have a report I'm writing and have got a problem that has stumped me.
The data the report is based in is in this format:
Date Type
1/1/08 A
1/3/08 B
3/1/08 C
3/5/08 B
3/10/08 A
3/12/08 C
3/20/08 A
Management wants a report showing a line chart that summarizes the data by Month and by Type. So, there are 2 series depending on the Type and data values are based on the count of each Type. So, in the example above the x-axis group would be month, the values would be count(Type) and there would be 2 series €“ Series 1. A, B and Series 2. C
I€™ve defined the x-axis to be Month(Fields!DateOccured.Value). However, they want the x-axis to show every month for the entire year not just what is in the database. So, I defined the x-axis to have a scale of 1 to 12 and the major interval is set to 1. This displays 1 through 12 on the x-axis.
The problem is when there is no data (null) for a particular month. Instead of showing 0, the line chart does not plot anything and the line is drawn to the next displayed point. So, for example on the data above the line chart would plot:
January
Series 1 €“ Qty. 2 Series 2 €“ Qty. 0
February
Null
March
Series 1 €“ Qty. 3 Series 2 €“ Qty. 2
The line chart would draw a line from January to March skipping February. I need to display the null values as 0 and not null indicating no occurrences for the month rather than no data present.
I can't figure out how to get my line chart to break when there isn't a value. For example, I have a trend line over 4 time periods. The 3rd time period is missing a value. Instead of the line ending at the 2nd period and picking up again at the 4th time period, it's connecting the line 2nd to the 4th period. I'd like it to break and for there to be no line appearing in the 3rd period. I bet that's as clear as mud, but let me know if you have any questions.
Currently I report our monthly fees broken down into 4 weeks per month by using 4 separate datasets with the following code
SELECT SUM(Practice.ibvSalesByJob.JobBilledExVAT) AS Sum_JobBilledExVAT FROM Practice.ibvSalesByJob INNER JOIN Practice.idvJobType
[Code] ....
The second dataset then has the date code changed toÂ
AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/08/' + Cast(@DateYear AS char(4)) + ' 00:00:01' AND Practice.ibvSalesByJob.[Date] <= Cast(@Month AS char(2)) + '/16/' + Cast(@DateYear AS char(4)) + ' 00:00:00'
The third
AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/16/' + Cast(@DateYear AS char(4)) + ' 00:00:01' AND Practice.ibvSalesByJob.[Date] <= Cast(@Month AS char(2)) + '/23/' + Cast(@DateYear AS char(4)) + ' 00:00:00'
The fourth
AND Practice.ibvSalesByJob.[Date] >= Cast(@Month AS char(2)) + '/23/' + Cast(@DateYear AS char(4)) + ' 00:00:01'
Now I was hoping so that I could report the above data in one chart and do an expression on the category and group the dates so I would just have one dataset like below but four separate columns saying Week 1, 2 3 and 4 and then the sum filtered in line.
SELECT SUM(Practice.ibvSalesByJob.JobBilledExVAT) AS Sum_JobBilledExVAT FROM Practice.ibvSalesByJob INNER JOIN Practice.idvJobType
[Code] ....
If I could somehow with SQL tie in all 4 separate datasets and display them as Week 1, 2, 3 and 4 underneath the dataset and selectable.An even simpler solution maybe just understanding how charts work, I can get it so I display the 4 separate weeks in the chart however I can't get the bottom line (Category Group) to display Week 1, 2, 3 and 4.
Hello, I need to know what means are available to change the Minimum and Maximum Y-Axis scale values rather than leave them simply as they are in the properties. Right now whatever min/max values I enter into the scale for the Y-Axis are unchangeable. I need to be able to change the min/max scale values based on the value of the data at run time.
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?
I am using the below expression for hidding the zero data labels in ssrs chart.=IIF(Fields!Name.Value=0,False,True). But this expression is working for some other charts. It is not wrking for only charts. I could not find the solution for that. How to hide the zero values.
The above chart has three Values in it (slightly late in pink, rather late in hot pink and very late in red). Because of the ordering of these values the red very late values can't be seen. When I shuffle the order around slightly they are indeed there:
...This report runs dynamically for one of a number of activities the user can select, as such I have no way of knowing which of these series will be at the lower end of the scale and which will be at the higher end. Is it possible to set the Values/Series to be transparent so I can be assured that all will be visible to the user at runtime?
I have a line chart which works great except when the x axis displays duplicate months. I have tried format codes of MM-YYYY and MMM and have the "numeric or time-scale values" checkbox checked. The data displays fine but the X axis in the case of the MMM format code displays as:
Sep Oct Oct Nov Nov
leaving the user to wonder where exactly does October and November start...
I've googled around but don't see off-hand what I am doing wrong. I am display 3 simultaneous lines if that matters...
In a bar chart, we are using the LargeCheckerBoard as the pattern style in series properties > fill. It is showing correctly on the browser but if we export to PDF, this style(checks) are getting small and different from what shown on the browser.
Hi every body! I created a report project by SQL Server Business Intelligence Development.I use toolbox chart to display report.But I want to edit format of catagory fields when it appear.That mean I have a report have two catagory(category1 and category2),one is parent of another.I want to edit style of catagory( such as catagory1 will have lines to separate with another category1......) Please help me! Thank you very much.
I can not modify DDL of the table, so creating a trigger etc is not an option.  This is my syntax that I am using, and each #Testing.abcd has different possible options. Â
For example, when #Testing.abcd = Test1 then #Data.field2 okay values are 'Yes', 'No', 'Maybe', but for #Testing.abcd = Test3 then #Data.field2 okay values are 'Follow-Up', 'Contact Requested'
How can I set-up "acceptable values" for one field, but different based off abcd in my syntax?
I have a query I am currently attempting to optimise. Â The query joins in on a sub table (queries the same table) to garner a maximum date value for each row to display. Â For example:
SELECT Column1 ,Column2 ,DateColumn FROM Table1 T INNER JOIN ( SELECT Column1 + Column2 AS ConcatColumn, Column3, MAX(DateColumn) AS dt FROM Table1 GROUP BY Column1 + Column2 ) X ON X.ConcatColumn = T.Column1 + Column2 AND X.dt = T.DateColumn
Is there any way I can write this another way (preferably more cleaner/optimised)?
Select a.uid , a.name , mewp.data As Association from Asset a Join wshhistory wsh on a.uid = wsh.assetid Join worksheet w on wsh.wshid = w.uid left join
[code]...
When I return the data how do I eliminate the name's that are in bold but keep the association record as this is a relationship between joining tables..
I am using Visual Web Developer Express 2005 and SQL Server Express 2005. I have set up a trigger that fires when an update has been performed on a specific table. The trigger checks to see if specific columns have been updated. If they have, then the trigger code is executed. This part works fine. I am now trying to find a way to check if null values exist in either one of two field from the same table. If either field contains a null value, then I don't want the trigger code to be executed. How can I check for null values and skip a block of code within my Transact Sql trigger. Thanks.....
I have [TableAccount.AccountType] field which can store these values:
Value C S E
How do I list all available values for a field? Using Distinct can only list all entries in the db, but does not list all values available for the field.
The following stored procedure works well as far as it goes but I need to amend it.
   @AE decimal (18,2) outputAS BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- Insert statements for procedure here
declare@odds decimal (18,2) (SELECT @odds= sum (1/ (RacingMaster.RM_SP) ) FROM RacingMaster where RM_Draw='1')
[Code] .....
The column RM_Draw holds integer values from 1 through to 20. provide a way so that when the procedure has produced a result based on RM_Draw='1'' it will produce a separate result for RM_Draw=2 and so all the way to 20 so that the resultant figures can be outputted to my vb.net app.
