I have a report with a date type parameter. Depending on the value return by this date type parameter the dataset will return either the credit, deposit or process date. How do I go about coding it so that it will dynamically select the right column in my query for my dataset?
Sincerely appreciate all the help I can get.
Thanks in advance.
I have inherited a query which currently returns multiple instances of each work order because of the joined tables. The code is here and I've detailed the criteria needed below but need the best way to accomplish this:
Select h.worknumber, h.itemcode, h.descr, h.task_descr, h.qty, h.itemised,
h.serialnum, h.manufacturer, h.model_id, h.depot, h.date_in, h.date_approved,
h.est_complete_date, h.actual_complete_date, h.meterstart, h.meterstop,
h.custnum, h.name cust_name, h.addr1, h.addr2, h.town, h.county, h.postcode,
h.country_id, h.contact, h.sitename, h.siteaddr1, h.siteaddr2, h.sitetown,
[Code] ....
Each work order should only be returned once, and with the following additional criteria:
1. i.meter - this should return only the lowest number from that file.
2. sm.next_calendar_date - this should return only the most recent date out of those selected for the certificates on this piece of equipment
3. wh.meterstop as [Last Service Hours],
wh.date_created as [Last Service] - this should return the number from wh.meterstop at the most recent wh.date_created for that piece of equipment.
Currently I'm trying to add a column based on certain criteria based on the following data:
CallID GroupName CustomerPending
------ ----------- ------------
00500588FollowupN
00500588FollowupN
00500588Server N
00500588Service DeskN
00500588Service DeskN
Basically I'm trying to add an extra column, so that whenever the GroupName is "Followup", then a 'Y' will appear in the CustomerPending column for all instances of that CallID. I tried with the following, but it only provides a 'Y' in the rows (not the CallId's) where "Followup" is found.
--------------------------------------
UPDATE dbo.Asgnmnt
SET CustomerPending = 'Y'
FROM dbo.Asgnmnt
WHERE dbo.Asgnmnt.GroupName IN ('SD Followup')
ALTER Table Asgnmnt
ALTER column CustomerPending varchar(1)
UPDATE dbo.Asgnmnt
SET CustomerPending = 'N'
FROM dbo.Asgnmnt
WHERE dbo.Asgnmnt.GroupName NOT IN ('SD Followup')
ALTER Table Asgnmnt
ALTER column CustomerPending varchar(1)
---------------------------------------
Any assistance appreciated.
I have an SSRS Report that I created. I'd like to set up a job that will run every hour, and what the job will do is send an SSRS report to a select group of recipients when a certain criteria is met. How do I go about doing this? My SSRS knowledge is very limited.
Additional Info:I created the report with a one time schedule to a certain email address. I do see the report schedule in the reportserver DB. Because I already have the schedule and the subscription id, I think all I have to do is call that subscription, but that's where I'm lost.
Hi,
I would like to get some advises on how creating some kind of reports with Report builder
Lets say i have these two tables with these columns
Table Product
ProductName
Table ProductInstance
Product ID
CreationDate
CompletedDate
I would like to be able to create with report builder a report that looks this
Start Date : 2008/10/01 - End Date : 2008/10/30
Total Total Total
In Out OutStanding
ProductName1 10 0 3
ProductName2 5 5 2
ProductName3 8 8 5
ProductName4 12 5 6
Total In : total number of product created between StartDate and EndDate
Total Out : Total number of product completed between Start Date and End Date
Total OutStanding : Total number of product that have not yet been completed.
Any suggestion and advise on how to achieve this ?
I am using report builder 3.0.
I have a report parameter called para1 which is a drop-down list and what I want to do is display another report parameter based on the para1 selection.
So for example, para1 contains a, b, c choices. if a user selects b, I would like para2 to display but if the user selects a or c, I don't want the para2 to display.
I have to select rows from a table
if the first 2 characters of a 12 char column are
'GB'
Select BFKEYC from table where
I have a hokey way of doing it but it looks embarrassing:
BFKEYC GT 'GA9999999999'
AND BFKEYC LT 'GC'
I have a report that has 2 report parameters. The first paramter is a string type that allows null. It is supposed to allow the user to enter a memberId or just select all members (if the NULL is checked). The second report parameter is a multi valued selection that contains a list of country codes populated from a query.
What I like to have is when the user decides to enter a memberid (uncheck the NULL) to have the country codes report parameters disabled.
Is there a way to do that?
Thanks,
Elie
Hi guys,
I would need to add a monthly based parameter to my reports. In my actual scenario i have 2 datetime parameters : "start date" - "end date", that denotes the time interval in days. Is there any standard way to customize these parameters to set an interval between months?
In other words, i would need 2 parameters like these : "start month" (eg. august 2005) - "end month" (eg. march 2006), with the report filtering data among this given interval of months.
Thanks in advance for any suggestion.
Claudio
Hi there,
Is there a quick way to select all the tables in the DB that don't have certain column in the table, so for example getting a list of all tables that don't have columns: A, B or C?
Thanks
S
Hi All,
I have a question regarding how to use report prompts in SSRS reports that are based on SQL queries.
When I added the prompt into the query for use as a filter value, it says that there is an error.
It does not recognise the '!' inside the parameter prompt string. Example is parameter!month_prompt.Value, which the ! is not recognised.
Your help is much appreciated.
Thanks & Regards,
Mohd Fadzli
Hi All,
I'm having trouoble with changing my report name when I swtich to different parameter values.
I use the following expression.
IIf(Parameters!GroupVar1.Label = "CBank", "CBank", IIf(Parameters!GroupVar1.Value ="DTC","DTC","Total"))
This works fine when I clear Mutli-Value box in paramter properties. but when I use multi-value, this does not work.
Is there any solution for this?
Thanks
I've got a RS2000 report from Analysis services 2000. This report is working fine but after the upgrade to SQL2K5, i'm having issues with changes to parameters.
Basically i'm passing DateQuarter, CustomersLevel as parameters to the following mdx script. I've tried to change mdx script in RS2005 but no luck yet. I don't much about the parameter level changes in sql2k5. So can anyone suggest what is wrong in the script.
In sql2000 the script looks like this...
="with " &
"member [Measures].[Ex Price] as 'coalesceempty([Measures].[Ex Price After Trade Discount - Detail],0.00)' " &
"Member [Measures].[CustomerUniqueName] as '[Customers By Class].currentmember.UniqueName' " &
"Member [Measures].[CustomerDisplayName] as '[Customers By Class].Currentmember.Name' " &
"SELECT " &
"{[Measures].[CustomerUniqueName],[Measures].[CustomerDisplayName]} on Columns, " &
"filter({descendants(" & Parameters!CustomerClass.value & ", [Customers by Class].[" & Parameters!CustomersLevel.value & "], SELF)}, " &
" [Measures].[Ex Price] > 0) on rows " &
"from Sales "
i've changed the script in sql2005 but not working yet..
with
member [Measures].[Ex Price] as 'coalesceempty([Measures].[Ex Price After Trade Discount - Detail],0.00)'
Member [Measures].[CustomerUniqueName] as '[Customers By Class].currentmember.UniqueName'
Member [Measures].[CustomerDisplayName] as '[Customers By Class].Currentmember.Name'
SELECT
{[Measures].[CustomerUniqueName],[Measures].[CustomerDisplayName]} on Columns,
filter({descendants( STRTOMEMBER(@CustomerClass), "[Customer Class]." + STRTOMEMBER(@CustomersLevel), SELF)},
[Measures].[Ex Price] > 0) on rows
from Sales
Thanks in advance..
Vivek
Hello all.
I am on the verge of being able to do exactly what I want, but just can't seem to find the right combination of things to do it. I'm sure all of you wonderful folks will be able to point it out to me immediately, but I've been looking at it too long or something....
I have a record of individual sales with the state, and quarter of the sale.
sale_id state quarter
001 NY 2005Q1
003 WI 2006Q2
etc.
I create a report with a matrix to show count(sale_id) with Quarter as the column group and State as the row group. This works fine.
Now what I want to do is to get percentages based on quarterly sales. In other words, what percent of sales for 2005Q1 in NY vs. all sales in 2005Q1. So I create a second dataset (called total) with an SQL query like so:
SELECT count(sale_id)
FROM data_table
WHERE quarter = @QueryQuarter
Now, back in the matrix I want to use the column that we're in (2005Q1, 2005Q2, etc.) as the value that is passed to this query.
This is a simple concept, but I can't seem to figure out the correct call to pass the column group to the query as the parameter.
Thank you for any pointers you might be able to give. As I said, I'm right on the verge and just can't quite get it.
cmk
Hi,
I have created a report using Report designer (Visual Studio, using Data Model as a data source), in the report I had created few datasets (with single filed) to populate the report parameters, lets say I have created a multi valued Parameter CustomerName and assigned field from a dataset,
result are coming correctly and combo box is getting populated but the customers are not in alphabetical order!
I want to sort it and need to specify it in report (please note that I am using Report Model as a data source and I cant sort the source table in the data base to get the result sorted)
Please let me know if anybody has done that or forward me if know some link which talks about it.
Thanks in advance.
Regards,
Jayant Jape
I have a report parameter named "Schools" which display a list of schools. For example, Alo elementary school, Balo middle school and Calo high school.
When "Alo elementary" is selected the report only display students from that school along with other assessments data fields. same goes for other schools too. But I want to display different data fields for "Calo high school" when it is selected. It is not currently possilbe becasue I am using the same template for all types of schools. There some fields only should be displayed for "Calo high school" but not for any other type of schools.
I can accomplish this by creating two separate report, one for "Calo high school" and the other for other schools. But I want to accomplish this just by creating one report. So when "Alo elementary school" is selected it displays report with certain fields and when "Calo high school" is selected then it displays same fields as "Alo elementary school" but as well as some other fields too in the report. Is this possible? Need help.
Hi All,Sorry if the subject line is too obscure -- I couldn't think of a wayof describing this request.I have a table that contains approximately 1 million records.I want to be able to be able to select the top x records out of thistable matching variable criteria.Pseudo table records:custid, category, segment1,1,12,1,13,1,14,1,15,1,26,1,27,1,28,1,29,2,110,2,111,2,112,2,113,2,214,2,215,2,216,2,217,2,318,2,319,2,320,2,3So, what I'm trying to do is return a recordset, for example, thatcontains the top 2 of each variation of category and segment.ie:1,1,12,1,15,1,26,1,29,2,110,2,113,2,214,2,217,2,318,2,3The only way I can think to achieve this is in a while statement,performing individual selects against each combination, feeding thewhere criteria by variables that I automatically increment.I can't help thinking there's a much more graceful way of achievingthis?If anyone can give me any insight into this I'd be incrediblyappreciative!Many thanks in advance!Much warmth,Murray
View 1 Replies View RelatedHi All,
I have requirement where first I need to show only one report
parameter. Based on user selection I need to prompt or show the user
another report parameter.
Say suppose I have 3 parameters. User selects first value in first
parameter I should not show the other 2 parameters. If user selects
second value in first parameter I should show second parameter and
hide third parameter. There is no relationship between these 2
parameters except user selection. Similarly if user third value in
first parameter then I should show third parameter and hide second
parameter.
Is this possible? I can not see any Visible property for report
parameters.
If yes, how to achieve this functionality?
Appreciate your help.
Regards,
Raghu
I created the report, which has 4 pages. Each page contains bunch of tables and charts, which display data in scope of dataset, created for each page and reflecting certain subject. Each page also has Header and Footer.
My users requested, the report has possibility to include or not include data on Page #4.
Other words, report should display 3 or 4 pages based on the entered parameter.
1. I created parameter named “Include #### metrics”, which contains options: Y, N
2. For each object on the page 4 I added dependency on the selected parameter in the visibility option.
Now if user select parameter Not include Page #4, report displays 3 pages as expected and page #4 is empty, but with Header and Footer.
The problem: Their preference is to not to show page 4 at all if someone chooses to not to include #### metrics.
My question is how to force the report to display just 3 selected pages.
i have below table in DB
DB Table
ID
Column
Row data
1
Supplier CODE
1001
2
Supplier Name
ACB
3
Product
7K7
4
Price
1000
now I create one report parameter order1 IF I will give order1.value=1,2,3 then Report will come like this :--
Suppliercode
Supplier Name
Product
1001
ACB
7K7
IF I will give order1.value=3,2,1 then Report will come like this :--
Product
Supplier Name
Suppliercode
7K7
ACB
1001
IF I will give order1.value=1,3 then Report will come like this :--
Suppliercode
Product
1001
7K7
Hi I hope i make sense this time around, I have a page in which a customer can either add a new product and its rate as well as update an existing product. What i am trying to achieve is get the live rate of the product, when a user goes to order the product. Each existing product can be updated twice in a year either in period 1 or period 2, therefore there is a possibility that a rate hasnt been updated which means the price should be the alst updated rate. Therefore the following are the possible rates which the product can have;
dbo.tblRateSchedule.Rate - This will be the rate when a product is new and has just been added, therefore no previous rates.
dbo.tblHistoricalRateSchedule.Rate2007Period2- Most existing products are still having this rate as their latest rate.
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate - This is the rate when a rate has been updated in 2008 period 1
dbo.tblRateSchedule.RateScheduleYear2008Period2Rate - This is the rate when a rate has been updated in 2008 period 2
What i am trying to do is get the latest rate as it stands in the system, based on the above scenarios. This what I have so far, i tried to use the ISNULL, however that didnt work for probably because there are four instances. The following is the "view" which returns the rates.
Code:
SELECT dbo.tblWorkSchedule.Survey_ID, dbo.tblWorkSchedule.WorkSchedule_ID, dbo.tblWorkSchedule.WorkScheduleType_ID,
dbo.tblWorkSchedule.ScheduleStatus_ID, dbo.tblWorkSchedule.Qty, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description,
dbo.tblRateScheduleType.Type, dbo.tblWorkSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblSurvey.PropertyYear_ID,
dbo.tblSurvey.PropertyPeriod_ID, ISNULL(dbo.tblWorkSchedule.Valuation, 0) AS Valuation, dbo.tblSurvey.WorkScheduleOverallStatus_ID,
dbo.tblSurvey.VariationOverallStatus_ID, dbo.tblWorkSchedule.WorkScheduleLocation_ID, dbo.tblWorkSchedule.Inserted_DateTime,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN dbo.tblRateSchedule.DESCRIPTION ELSE dbo.tblWorkSchedule.Figure_Description END AS DESCRIPTION,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN tblRateSchedule.SWT ELSE tblWorkSchedule.WorkScheduleLocation_ID END AS SWT, dbo.tblCategory.Category,
dbo.tblScheduleStatus.Schedule_Status, CASE isnull(dbo.tblWorkSchedule.Rate, 0)
WHEN 0 THEN dbo.tblRateSchedule.Rate ELSE tblWorkSchedule.Rate END AS Rate, dbo.tblRateSchedule.WorkType_ID,
dbo.tblWorkSchedule.UpliftedRate AS UPLIFTED_RATE,
CASE dbo.tblWorkSchedule.WorkScheduleType_ID WHEN 1 THEN CASE IsNull(dbo.tblSurvey.WorkScheduleOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID)
END WHEN 2 THEN CASE IsNull(dbo.tblSurvey.VariationOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END END AS UpliftedRate,
CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN CONVERT(decimal(18, 2), IsNull(dbo.tblRateSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) ELSE CONVERT(decimal(18, 2), IsNull(dbo.tblWorkSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) END AS Total, dbo.tblCompany.IsContractor, dbo.tblCompany.Percentage AS Constructor_Percentage,
dbo.tblCompany.Percentage AS Contractor_Percentage, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) ELSE ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) END AS After_Lift, ISNULL(dbo.tblSurvey.Survey_Completed, 0)
AS Survey_Completed, LTRIM(RTRIM(dbo.tblUser.User_Title)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Forename))
+ ' ' + LTRIM(RTRIM(dbo.tblUser.User_Surname)) AS Inserted_By, dbo.tblWorkSchedule.Inserted_By AS InsertedBy_ID,
ISNULL(dbo.tblUploadedFile.File_Name, '') AS File_Name, dbo.tblWorkSchedule.Variation_ID,
dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2006Period2, dbo.tblHistoricalRateSchedule.Rate2007Period1,
dbo.tblHistoricalRateSchedule.Rate2007Period2, dbo.tblHistoricalRateSchedule.Rate2008Period1,
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate, dbo.tblRateSchedule.RateScheduleYear2008Period2Rate
FROM
Hi this is my first post i am hoping its the write place. I have a page in which a customer can either add a new product and its rate as well as update an existing product. What i am trying to achieve is get the live rate of the product, when a user goes to order the product. Each existing product can be updated twice in a year either in period 1 or period 2, therefore there is a possibility that a rate hasnt been updated which means the price should be the alst updated rate. Therefore the following are the possible rates which the product can have;
dbo.tblRateSchedule.Rate - This will be the rate when a product is new and has just been added, therefore no previous rates.
dbo.tblHistoricalRateSchedule.Rate2007Period2- Most existing products are still having this rate as their latest rate.
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate - This is the rate when a rate has been updated in 2008 period 1
dbo.tblRateSchedule.RateScheduleYear2008Period2Rate - This is the rate when a rate has been updated in 2008 period 2
What i am trying to do is get the latest rate as it stands in the system, based on the above scenarios. This what I have so far, i tried to use the ISNULL, however that didnt work for probably because there are four instances. The following is the "view" which returns the rates.
SELECT dbo.tblWorkSchedule.Survey_ID, dbo.tblWorkSchedule.WorkSchedule_ID, dbo.tblWorkSchedule.WorkScheduleType_ID,
dbo.tblWorkSchedule.ScheduleStatus_ID, dbo.tblWorkSchedule.Qty, dbo.tblRateScheduleUnit.Unit, dbo.tblWorkType.Work_Type_Description,
dbo.tblRateScheduleType.Type, dbo.tblWorkSchedule.MA_Code, dbo.tblRateSchedule.SOR_Code, dbo.tblSurvey.PropertyYear_ID,
dbo.tblSurvey.PropertyPeriod_ID, ISNULL(dbo.tblWorkSchedule.Valuation, 0) AS Valuation, dbo.tblSurvey.WorkScheduleOverallStatus_ID,
dbo.tblSurvey.VariationOverallStatus_ID, dbo.tblWorkSchedule.WorkScheduleLocation_ID, dbo.tblWorkSchedule.Inserted_DateTime,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN dbo.tblRateSchedule.DESCRIPTION ELSE dbo.tblWorkSchedule.Figure_Description END AS DESCRIPTION,
CASE IsNull(CONVERT(varchar, dbo.tblWorkSchedule.Figure_Description), '')
WHEN '' THEN tblRateSchedule.SWT ELSE tblWorkSchedule.WorkScheduleLocation_ID END AS SWT, dbo.tblCategory.Category,
dbo.tblScheduleStatus.Schedule_Status, CASE isnull(dbo.tblWorkSchedule.Rate, 0)
WHEN 0 THEN dbo.tblRateSchedule.Rate ELSE tblWorkSchedule.Rate END AS Rate, dbo.tblRateSchedule.WorkType_ID,
dbo.tblWorkSchedule.UpliftedRate AS UPLIFTED_RATE,
CASE dbo.tblWorkSchedule.WorkScheduleType_ID WHEN 1 THEN CASE IsNull(dbo.tblSurvey.WorkScheduleOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID)
END WHEN 2 THEN CASE IsNull(dbo.tblSurvey.VariationOverallStatus_ID, 0)
WHEN 4 THEN dbo.tblWorkSchedule.UpliftedRate ELSE dbo.GetSWT_PropertyYearPeriodRate(IsNull(tblRateSchedule.WorkType_ID, 0),
tblWorkSchedule.WorkSchedule_ID, tblSurvey.PropertyYear_ID, tblSurvey.PropertyPeriod_ID) END END AS UpliftedRate,
CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN CONVERT(decimal(18, 2), IsNull(dbo.tblRateSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) ELSE CONVERT(decimal(18, 2), IsNull(dbo.tblWorkSchedule.Rate, 0)) * CONVERT(decimal(19, 2),
IsNull(dbo.tblWorkSchedule.Qty, 0)) END AS Total, dbo.tblCompany.IsContractor, dbo.tblCompany.Percentage AS Constructor_Percentage,
dbo.tblCompany.Percentage AS Contractor_Percentage, CASE IsNull(dbo.tblWorkSchedule.Rate, 0) WHEN 0 THEN ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblRateSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) ELSE ((IsNull(tblCompany.Percentage, 0)
/ 100 * (CONVERT(decimal(18, 2), dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) + (CONVERT(decimal(18, 2),
dbo.tblWorkSchedule.Rate) * CONVERT(decimal(19, 2), dbo.tblWorkSchedule.Qty))) END AS After_Lift, ISNULL(dbo.tblSurvey.Survey_Completed, 0)
AS Survey_Completed, LTRIM(RTRIM(dbo.tblUser.User_Title)) + ' ' + LTRIM(RTRIM(dbo.tblUser.User_Forename))
+ ' ' + LTRIM(RTRIM(dbo.tblUser.User_Surname)) AS Inserted_By, dbo.tblWorkSchedule.Inserted_By AS InsertedBy_ID,
ISNULL(dbo.tblUploadedFile.File_Name, '') AS File_Name, dbo.tblWorkSchedule.Variation_ID,
dbo.tblHistoricalRateSchedule.Rate2006Period1, dbo.tblHistoricalRateSchedule.Rate2006Period2, dbo.tblHistoricalRateSchedule.Rate2007Period1,
dbo.tblHistoricalRateSchedule.Rate2007Period2, dbo.tblHistoricalRateSchedule.Rate2008Period1,
dbo.tblRateSchedule.RateScheduleYear2008Period1Rate, dbo.tblRateSchedule.RateScheduleYear2008Period2Rate
FROM
I have to produce a report, but not sure of the best way to get the required results
Aim - to count how many [FDMSAccountNo] there are per given [Month_end_date], and then do a case when on the[Retail_amount]
E.g.
10 Fdmsaccountno in Jan
Those 10 Fdmsaccountno vary in [Retail_amount]
I have 5 fdmsaccountno which are between %0 & £5
2fdmaccounno beterrn £6& £10
3 fdmsaccountno £10>
My query is
SELECT [FDMSAccountNo]
,[Month_end_date]
,[Retail_amount]
FROM [FDMS].[dbo].[Fact_Fee_History]
where [Fee_Sequence_Number] = '32r'
and Month_end_date between '2013-01-01' and '2013-12-01'
Hi
I would like to get records from a table and present a result set based upon the search fields
the search fields could be any of the following: PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3,
they could enter any combination of these however if they dont enter any of the above then the search should not retrieve any thing. the table colunms are listed below and asample data set is also shown below.
Currently the only way i think it can be done is by writing multiple queries with different queries to be executed based upon the search field that have been filled? can it be done in a stored prcedure? and can it be done using non-dynamic sql?
Name, PNo, Year, JNo, C1No6, C2No3, C3No3, C4No3, RefImage
adam, 01, 1999, 099, 3yh333, 888, 989, 999, ref1999099.jpg
Brian, 01, 2005, 029, 3yh323, 828, 929, 929, ref1929099.jpg
sid, 04, 1989, 039, 3yh343, 838, 939, 939, ref1993399.jpg
jack, 06, 1996, 069, 3yh633, 868, 969, 969, ref1669099.jpg
I am trying to accomplish the following.
There are two tables. the second one is a table that contains fields based on which the exclusion logic has to be written. It has 5 fields and there may be data in all of them or only in some of them.
The first and second table have one field in common - the Account number.(ACCT_NUM)
the exclusion is based in steps..
1) if it has all the fields in the second table for that account number, then compare all of them..
2) if only 4 of them are present,compare and check for null for the rest
3) if only 3 of them are present,compare and check for null for the rest
4) if only 2 of them are present,compare and check for null for the rest.
how do i do this... pls suggest
Thanks
Hi,
I want to delete all files in a folder where creation date is an year older. I created a For Each Loop container to go over the whole folder and then execute the following Script Task
Dim FileArchive As Int16
Dim dteArchive As Date
Dim FilePath As String
FileArchive = CType(Dts.Variables("FileArchive").Value, Int16) ' One Year FileArchive = 365
dteArchive = DateAdd("d", -FileArchive, Now.Date)
FilePath = Dts.Variables("FilePath").Value.ToString
If File.GetCreationTime(FilePath) <= dteArchive Then
Try
File.Delete(FilePath)
Catch ex As Exception
'Do Nothing
End Try
End If
Dts.TaskResult = Dts.Results.Success
Is there a better approach to this? As most of the time job may have to delete 10 or 15 files but he has to read each file and then check the date if it matches the criteria and it delete.
Hello again,
Another combining multiple rows teaser, during a few routines I made a mistake and I would like to combine my efforts. Here is my data:
Code:
Table A
ID DSN VN AX Diag
1111296.54
3212318.00
Both DSNs share the same Patient_id in a seperate table which holds the DSN numbers and their corresponding patients.
Code:
Table B
DSN Patient_id
100000001
200000001
So what I need to do is maintain their unique 'ID' number in Table A but update their DSN numbers to reflect the first instance in Table B. So my data would look like this in both tables.
Code:
Table A
ID DSN VN AX Diag
1111296.54
3112318.00
Note: The second rows DSN changed to 1 from 2
Code:
Table B
DSN Patient_id
100000001
(Duplicate row removed with same patient_id)
The result would look like the above but as you noticed I need to remove the duplicate row that had the different DSN in Table B so that only one DSN remains that can map to multiple rows (IDs) in Table A.
Table A:
DSN can map to multiple rows (IDs)
IDs must be unique (aka kept to what they are currently)
Table B:
Second row with same DSN must be removed.
Any takes, ideas? I need to do this on a couple thousand rows....
Thanks, and im happy to clarify if needed.
***** SQL Server 2005 ********
I have a table that needs to be updated with a sequential number based on criteria.
I am trying to update the SeqID and LinkSeqID with the same sequential number if the ProductID and StoreID are in the same group. For instance the 1st three rows below are in the same group 752534 and 4, therefore the SeqID and LinkSeqID should be 1,2,3 and restart at 1 once the grouping of ProductID and StoreID changes. Please look at the examples below.
SALES Table as IS:
======================================
ProductID StoreID DBRowID SeqID LinkSeqID
======================================
752534 4 1
752534 4 2
752534 4 3
896784 2 4
896784 2 5
896784 4 6
898874 2 7
898968 2 8
This is what the table should look like after the update in complete.
SALES after UPDATE:
======================================
ProductID StoreID DBRowID SeqID LinkSeqID
======================================
752534 4 1 1 1
752534 4 2 2 2
752534 4 3 3 3
896784 2 4 1 1
896784 2 5 2 2
896784 4 6 1 1
898874 2 7 1 1
898968 2 8 1 1
Can anyone HELP me please?
I have a scenario where i need to get the starting and ending date time based on the crieteria. The criteria is I always have my start date as NS or GS in the data column and my end date as GX so i need NS or GS to be my strart date based on ts Ascending and my end date as GX to be displayed in the same columns .
Create Table Test
(Tsq INT IDENTITY (1,1),
Data Varchar (150),
ts datetime,
Tpkt_type int)
insert into test values ('GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2')
[code]....
Expected Output
---------Data----------------- ts as starttime--------------tpkt_type------data-----------------------ts as endtime--------tpkttype-
'GS,000020,000021,000022,000023','2013-11-13 09:47:35.963','2' 'GX,1,0000000000000000000000000','2013-11-13 09:47:37.007','4'
'GS,000020,000021,000022,000023','2013-11-13 09:50:25.987','2', 'GX,1,0000000000000000000000000','2013-11-13 09:50:40.920','4'
'GS,000020,000021,000022,000023','2013-11-13 09:51:28.330','2', 'GX,1,0000000000000000000000000','2013-11-13 09:51:43.257','4'
'NS,000020,000021,000022,000023','2013-12-17 16:51:09.063','18', 'GX,1,0000000000000000000000000','2013-12-17 16:51:15.257','4'
Hello,
I need to create a query that will count new cases based on the create date(create_date) and criteria for the groups(The only way to distinguish between the 2 major groups mts and bnb is area!= 'bnb" because everything else is MTS). The sample report I need to create below shows how it needs to be counted weekly, for a 4 month period, for the groups under MTS and BNB. The totals and grand totals can be achieved in the report tool. I want to create variables for the new cases (mts_newcases_sales, mts_newcases_salesd, bnb_newcases_salesd etc)
Ex. MTS sales : (status = 'Calculated' OR status = 'REJECTED') and errorsource != 'marketing' and accountns is null and area != 'BNB'(everything else is MTS)
MTS salesd ; Credit >= '1001' and (status = 'REJECTEDV' or status = 'ACCEPTEDS') and errorsource != 'marketing' and accountnr is null
BNB creditr: Credit < 101 and (status = 'SUBMITTED' OR status = 'REJECTEDS' OR status = 'REJECTEDA' OR STATUS = 'ACCEPTEDC')
12-Jan
19-Jan
26-Jan
2-Feb
9-Feb
16-Feb
MTS
New Cases Received
85
84
79
98
79
95
Sales
30
32
27
40
42
38
SalesD
47
34
37
23
23
37
CreditR
44
29
26
35
55
54
CreditB
6
12
9
5
7
13
CreditS
-
-
-
-
3
-
CreditP
10
11
11
24
17
7
MTS Subtotal
140
125
110
144
151
150
BNB
New Cases Received
12
13
14
14
6
11
Sales
-
-
-
-
-
-
SalesD
-
-
-
-
-
-
CreditR
12
11
12
10
5
9
CreditB
8
13
9
17
16
6
CreditS
-
-
2
-
-
-
CreditP
1
1
1
1
4
3
BNB Subtotal
21
25
24
28
26
19
Total
New Cases Received
97
97
93
112
85
106
Sales
30
32
27
40
42
38
SalesD
47
34
37
23
23
37
CreditR
56
40
38
45
60
63
CreditB
14
25
18
22
23
19
CreditS
-
-
2
-
3
-
CreditP
11
12
12
25
21
10
Grand Total
161
150
134
172
177
169
This is just a very brief bit of code
SELECT MTS_new_cases_sales, mts_new_cases_salesd .
FROM vwCreditN
WHERE mts_sales_new_cases = ( )...
and (status = 'Calculated' OR status = 'REJECTED')...
Can you please show me how to accomplish this?
Thank you in advance for your effort,
Rhonda
I am trying to add a calculated field / column in Report Builder when working with a Report Model built from anAnalysis Services Cube. I can create the calculated Field/Columns, but I get an error whenever I try to use it in a report.
Is there a way to create a report builder calculated column on report models built from a SSAS cube? Is this supported?
Thanks,
Code:
SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS +
AUG_CURR_CREDITS + AUG_CURR_DEBITS +
SEP_CURR_CREDITS + SEP_CURR_DEBITS +
OCT_CURR_CREDITS + OCT_CURR_DEBITS +
NOV_CURR_CREDITS + NOV_CURR_DEBITS +
DEC_CURR_CREDITS + DEC_CURR_DEBITS +
JAN_CURR_CREDITS + JAN_CURR_DEBITS +
FEB_CURR_CREDITS + FEB_CURR_DEBITS +
MAR_CURR_CREDITS + MAR_CURR_DEBITS +
APR_CURR_CREDITS + APR_CURR_DEBITS +
MAY_CURR_CREDITS + MAY_CURR_DEBITS +
JUN_CURR_CREDITS + JUN_CURR_DEBITS) as CURR_AMT
I need to sum these amounts running from July to the month prior to whatever the current month is. So if it was August, it would only be
Code:
SELECT (JUL_CURR_CREDITS + JUL_CURR_DEBITS) as CURR_AMT
Is there a cleaner (shorter) way to iterate through the twelve months than either writing the query 12 times in an IF statement, or 12 CASE statements? This is only part of a query that joins several tables (not shown).
Any suggestions on the best way to write this would be valued.
Hi! I'm new to SQL and have a question...
I'm writing a script that gathers a few variables from an outside source, then queries a table and looks for a record that has the exact values of those variables. If the record is not found, a new record is added. If the record is found, nothing happens.
Basically my SELECT statement looks something like this, then is followed by an If... Else statement
SELECT * FROM TableName
WHERE LastName = varLastName
AND FirstName = varFirstName
AND Address = varAddress
If RecordSet.EOF = True Then
'Item Not Found, add new record
'code to add new record......
Else
'Item Found, do nothing
End If
RecordSet.Update
RecordSet.Close
Even when I try to delete the If.. statement and simply display the records, it comes up as blank. Is the syntax correct for my SELECT statement??