I've a SSRS report with the details in regards to shipping information. Currently in my report the 'Scheduled Ship Date' is set to following Monday, if we miss the 'Requested Delivery Date'. However, I need to change this report to set shipment date('Scheduled Ship Date') to Tuesday and Friday for the shipment having 'Requested Delivery Date' for this week or next week(current week and next week).
For example- Jobs that have shipment date between 3/1/2015 - 3/7/2015 will have 'Scheduled Ship Date' as Tuesday and Friday of current week. If we miss the shipment for this week it should be scheduled for next Tuesday(3/9/2015) and Friday (3/13/2015). I need to follow this for current week(3/1/2015-3/8/2015) and following week(3/9/2015-3/15/2015) and after that all the following weeks will have 'Scheduled Ship Date' as Tuesdays.
Below is the SQL:
USE tempdb;
GO
DECLARE @Date datetime;
SET @Date = GETDATE();
--SET @Date = '2014-07-25';
DECLARE @TEST_DATA TABLE
I've a SSRS report that reports the 'ScheduledShipDate' based on 'Requested Delivery date'. Currently, on my report 'ScheduledShipDate' is set to Monday of each week since we ship every mondays. I need to change the 'ScheduledShipDate' since from now the ship date will be Wednesday, Thursday and Friday of each week. Here is my DDL.
USE tempdb; GO DECLARE @Date datetime; SET @Date = GETDATE(); --SET @Date = '2014-07-25'; DECLARE @TEST_DATA TABLE ( DT_ID INT IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED
I've a SSRS monthly sales report with the sales details for current year and last year. I've 4 paramaters in the report. StartDate1, EndDate1, StartDate2 (hidden), EndDate2 (hidden).
For
StartDate1 - Beginning of Last month - DateAdd(DateInterval.Month, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1)) (Will give me Feb 1 2015)
EndDate1 - End of Last month - DateAdd(DateInterval.Minute, -1, DateSerial(Year(Date.Now), Month(Date.Now), 1)) (Will give me Feb 28 2015)
Let's say the first row returned has StartDate = 1/1/2014 and EndDate is 1/2/2014. The next row I want the StartDate to equal the previous row EndDate so it would be 1/2/2014 as StartDate. This compounds every row basically the third row StartDate would be the second row EndDate. All in one select statement if it can be done. Using SQL2008r2.
I am trying to SUM a column of ActivityDebit with current Calendar_Month to a Column of Trial_Balance_Debit from Last Calendar_Month. I am providing Temp Table code as well as fake data.
===== IF OBJECT_ID('TempDB..#MyTrialBalance','U') IS NOT NULL DROP TABLE #MyTrialBalance CREATE TABLE #MyTrialBalance ( [Trial_Balance_ID] [int] IDENTITY(1,1) PRIMARY KEY CLUSTERED NOT NULL, [FISCALYEAR] [smallint] NULL,
[Code] ....
Here is my Query I am trying but not working. I cant figure out how to doo the dateadd for correct column.
SELECT A.Trial_Balance_ID,A.ACTIVITYDEBIT --SUM(A.ACTIVITYDEBIT + B.Last_Trail_Balance_Debit) AS New_TB FROM (SELECT [Trial_Balance_ID], [Calendar_Month],[ACTIVITYDEBIT] FROM Mytrialbalance WHERE actindx='48397' AND ACTIVITYDEBIT='820439.78000' )A INNER JOIN (SELECT [Trial_Balance_ID],DATEADD(MM, -1,Calendar_Month)AS Last_Month FROM Mytrialbalance) B ON B.Trial_Balance_ID=A.Trial_Balance_ID
I have a report parameter textbox in my SSRS report which has the parameter properties as "allow null value" and "allow blank value" checked.
How I can ensure that when the value is entered in textbox, it should accept any character ie. The SQL code I should write so that all the characters inputted via the report parameter are accepted.
I am working on reports in SSRS 2008 (not R2)... There are some reports with parameters that are hidden when the report is accessed through normal URL using ReportViewer.asx..The thing is that these hidden parameters need to be visible when the report is accessed using SSRS Report Manager.
but if my month is November and two months is added, the year does not change, it stays the same. how do I make the year change when two months are added toward the end of the year.
If I skip those objects that were deleted, is there a query that I can run off Master/MSDB that will show all database objects and when they were last modified, or create date if it has not been modified?
This is because a date of 04/16/2014 will show as 160416 in the first part of the field I need to parse it out of, thus becoming 04162014.
From there I then need to convert this "date" into a legitimate SQL datetime type, so that I can then run a DATEDIFF to compare it to when the record was actually entered, which is a separate field in the table, and already in datetime format.
When I use the below statement, I am getting the message that, "Conversion failed when converting date and/or time from character string."
I have used Aasim Abdullah's (below link) stored procedure for dynamically generate code for deletion of child tables based on parent with certain filter condition. But I am getting a output which is not proper (Query 1). I would like to have output mentioned in Query 2.
Link:
[URL]
--[Patient] is the Parent table, [Case] is child table and [ChartInstanceCase] is grand child
--When I am deleting a grand child table, it should be linked to child table first followed by Parent
--- query 1
DELETE Top(100000) FROM [dbo].[ChartInstanceCase] FROM [dbo].[Patient] INNER JOIN [dbo].[Case] ON [Patient].[PatientID] = [Case].[PatientID] INNER JOIN [dbo].[ChartInstanceCase] ON [Case].[CaseID] = [ChartInstanceCase].[CaseId] WHERE [Patient].PracticeID = '55';
--Query 2
DELETE Top(100000) [dbo].[ChartInstanceCase] FROM [dbo].[ChartInstanceCase] INNER JOIN [dbo].[Case] ON [ChartInstanceCase].[CaseId]=[Case].[CaseID] INNER JOIN [dbo].[Patient] ON [Patient].[PatientID] = [Case].[PatientID] WHERE [Patient].PracticeID = '55';
how to modify the SP 'dbo.uspCascadeDelete' to get the output as Query 2.
I want to change Set clause of Update Statement dynamically based on some condition.
Basically i have 2 Update statments having same FROM clause and same JOIN clause.
Only diff is SET clause and 1 Where condition.
So i am trying to combine 2 Update statements into 1 and trying to avoid visit to same table twice.
Update t Set CASE **WHEN Isnull(td.IsPosted, 0) = 0 THEN t.AODYD = td.ODYD** *ELSE t.DAODYD = td.ODYD* END From #ReportData As t Join @CIR AS tmp On t.RowId = tmp.Max_RowId
Is it possible to turn a server report created using report builder (with all the smdl and dsv stuff) into a report that is viewable using the Reportviewer ASP.Net control in _local_ mode? I need this because I'm trying to replace the datasource used in creating the report by another one for viewing the report.
hi, If I want to make one to many relationship, I put the pk of the parent as a fk in the child . like this customer table (cust_id, fname,lname) customer_order table(order_id,cust_id,qty,price)
I do understant this, but what about look up table, can I consider cust_id inthe customer_order table as a lookup field to the customer table?
I have new client we changed the file retention period on for log ship on SQL 2000. Now the files are not being deleted. I looked at all the sp's but could not finde the one that actually deletes the log file that was copied over. I could just rebuild log ship but this is on a prod box, was trying to figure out what and when the files get deleted, and by what sp
This one is a needle in the haystack. I have a fairly robust Dell server running WIn 2003, 16 gig of memory, SQL SP3a using log shipping failing over to a local server and then back to my location across a WAN, using a vb6 app with about 60 users, a 24X7 shop Mon-Sat. 2 times in the past month the production server has created a corrupt log ship file which gets applied to my failover server and then corrupts the failover DB to both failover servers. Of course it is my 20 gig db and the only time I can recreate the failover is Saturday night. The production DB is not corrupt and is fine. Has anyone ever run into this before. Currently working with MS and Dell, but hoping someone had experience with this !
I have 1 production server and wanto to add 1 more server.
my production server is using sql 2000 stnd edition.
I want to have an indentical database from my production server.
the problem is my second server is using sql 2005 stnd edition.
I have try to use replication but because the my production database didn't have relationship on the database so I can't use transaction replication, I can't use mirroring and log ship too because the sql use the different version.
I have a QA Deployment Date field that is being returned in a custom report I created. I also found a sample date range parameter:
What I want to accomplish:
I want to select a From and To Date and filter the report to only display the rows that have the QA Deployment Date within the selected range.
For example.. I want to select From Date (8/1/2105) and To Date (8/31/2015) and I only want to return only the results that have a QA Deployment date between that selected range.
It returns '2080-11-20 00:42:44.000'. This is dead on except for the year (which should be 2014). I was thinking maybe my bigint value of 3499288964 was milliseconds or microseconds so I adjusted the seconds value (86400) in the select statement to reflect milliseconds (86400000) with no success and microseconds (86400000000) with no success as both of those gave incorrect results. Closest I got was with the seconds (86400) which of course returns the incorrect year.
Code Block SELECT NON EMPTY { [Measures].[NC_ValorCarteira], [Measures].[CM_EBIT] } ON COLUMNS, NON EMPTY { ( [DimTime].[Hierarquia].[Dias].ALLMEMBERS * [DimStructure].[Entidade_ID].[Entidade_ID].ALLMEMBERS * [DimStructure].[Carteira_ID].[Carteira_ID].ALLMEMBERS * [InstrumentoTipo].[InstArea].[InstArea].ALLMEMBERS * [InstrumentoTipo].[InstTipo].[InstTipo].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM ( SELECT ( { [DimTime].[Dia].&[8878],[DimTime].[Dia].&[9092] } ) ON COLUMNS FROM ( SELECT ( { [DimStructure].[Carteira_ID].&[LIS_CPR] } ) ON COLUMNS FROM ( SELECT ( { [DimStructure].[Entidade_ID].&[LIS] } ) ON COLUMNS FROM [cbRentabilidade]))) WHERE ( [DimTime].[Dia].CurrentMember ) CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
I have 2 dates as input. The main date and reference date (Last date of previous year) of the main date. At the moment I manually insert the values for main date and reference date, but I only one to select the main date and automatically insert in the first column of the matrix report the reference date.
Hi, I tried many times checking "Save password" in connection manager editor. But everytime I open the editor again and I see the password field blank. I took it as normal, and deployed my IS Package, It couldnt run because of login failed for user 'sa'.
How do I force IS to save password on any ProtectionLevel ?
I have some trouble with shipping my transaction logs to the secondary database be it on
another server or within the same server to another database instance.
Im using SQL Server 2005 workGroup editions with Service Pack 2.
Here are the problems that i encountered.
I do hope someone has bumped into such a problem and willing to help me out.
I tried on 2 separate servers (not domain environment) and also between 2 separate instances (which is supposed to be simple!) on our development server,but was unsuccessful.
Between 2 separate instances on the development server - No error after configuring Log shipping though. The configuring went through and it was a success. Transaction logs was backup every minute and it got copied over to the other folder. But SQL Agent not doing its last job which is supposed to restore to the secondary database on another instance.No errors given out in SQL Agent error log files.Anywhere else im supposed to look to see if there are errors given out? Both instances SQL Agent has the same log on username and password with Administartive rights
So what went wrong?
Between the 2 servers- The transaction logs was backup every 1min on the primary server but it didn't got copied over to to the secondary database. - Error message given was:Error in restoring database to the secondary database.Network path given could not be found. Can't open the AxTest.bak file. (i am very sure i have type the correct network path,even have shared it out and i think the firewall is blocking incoming traffic since unlike our development server, which allows us to access when we use Start>Run and type in the ip address,user name and password,the primary server will only tell me no network path was found) I also believe it's because the SQL Agent on the secondary database server wasn't given permission to access the primary database folder. I've shared out the drive and folder on the secondary server and even have allowed SQL Agent to read,write and modify on both servers. For the primary and secondary SQL Agent, I configure their log on to be the same user account name and password which have administrative rights. So what went wrong?
Isit really true that both servers have to be in domain environment before you can configure log shipping,mirroring and replication?
Hope someone help me out of this predicament.Thank you in advance!
I want to convert "2007-01-27 14:13:01.223" to 20070127 (an integer representing the day). I could concatenate the calls to YEAR, MONTH DAY but then I would have to cater for the possibility that MONTH or DAY may return me one char instead of two; then I'll have to pad it manually. Is there a shorter way to write such a query?
When looking at the data some days have multiple events. Now I want to generate a new table that show all the dates in this month showing the number of running events for that specific day.