18

I've solved this by having a very simple calendar table - each year has one row per supported time zone, with the standard offset and the start datetime / end datetime of DST and its offset (if that time zone supports it). Then an inline, schema-bound, table-valued function that takes the source time (in UTC of course) and adds/subtracts the offset. This ...


12

Thank you for SQLfiddle and sample data! I wish more questions started this way. If you want all members regardless of whether they have an entry for that date, you want a LEFT OUTER JOIN. You were very close with this version however a little trick with outer joins is that if you add a filter to the outer table in the WHERE clause, you turn an outer join ...


12

There's a few questions you need to ask first. Are you using SQL Server Enterprise Edition? Mirroring can be done with either Standard or Enterprise, but snapshots can only be done with Enterprise. Are you already paying for licensing on the mirror? There's a lot of fine print, but you basically get the mirror for free - only as long as you're not ...


10

For readability I would rewrite the query using the more modern join syntax. This will clearly separate your join conditions from your filters. select a.myVal, b.myVal from MyTable a join MyTable b on b.id = a.id where a.OtherVal = 100 and b.Otherval = 200 For performance, ensure you have proper indexes. In this limited example, ideally you ...


9

In our shop at my previous job, we had a secondary set of servers where we tested our restores. For our busiest customers we would restore tonight's backup, mark it as read_only, and their reporting tomorrow would connect to that copy of the database for all reports from yesterday back. This offloaded about 90% of the reporting workload and doubled as a ...


8

I was wondering this running an instance of SQL Server Express would suffice for the role of a the Distributor database? Is this remotely feasible? SQL Server Express cannot serve as a Publisher or Distributor. SQL Express can only be Subscriber. Refer to : Replication Considerations (SQL Server Express) for more details. EDIT : To make my answer more ...


7

Since you have this requirement, They do not want to DELETE data from this secondary reporting database, if data gets deleted from the live database AlwaysON gets eliminated. I would suggest you to setup Transactional replication. Frequently (every 30mins) move the transaction data out of live database to another database You can schedule the log ...


7

So what I am looking for is a way to store a large number of activities that have almost no fields in common in a way that makes reporting easier. Not enough rep to comment first, so here we go! If the primary purpose is reporting and you have a DW (even if it isn't star schema) I'd recommend attempting to get this into a star schema. The benefits are ...


7

Yes, more concise ways of pivoting exist for your scenario. Option 1 For instance, you could use the PIVOT operator: SELECT * FROM ( SELECT road_id, insp_year, condition FROM road_insp ) derived PIVOT ( MAX(condition) FOR insp_year IN ( 2009 AS condition_2009, 2010 AS condition_2010, ...


6

Well...my solution is as follows: I used a dynamic pivot table in a stored procedure. The stored procedure called a View that I created which denormalizes the data. I think used MS Query to hook the stored procedure up to Excel 2010. This loads it up into a nicely formatted table that allows the end user to sort and filter to their heart's content. With ...


6

You need to use CONVERT(VARCHAR(xx), ColumnName) on all columns if you want the column to appear shorter in text output view. Convert your query into something like: SELECT [Server] = CONVERT(VARCHAR(30), SERVERPROPERTY('Servername')) , DatabaseName = CONVERT(VARCHAR(30), '''' + bs.database_name + '''') , LastDatabaseBackupDate = CONVERT(VARCHAR(30),...


5

EAV is a nightmare for BI tools. I've found a few places that build automated processes that generate a "pivoted" view of the EAV table, as an ETL process daily which drops & recreates the table, with columns for each key. However, depending on how your BI tool works, you will still have to manually add the new attributes that are created by the ...


5

You can use expressions for this type of conditional work. To do this click on the image and select the properties tab in BIDS. Find the expression button (fx) and click it. Enter something like the below to get what you want: =Iif(Fields!Gender.Value = "Female", "Female.jpg", "Male.jpg") This is assuming that you have have set up the images to be ...


5

I know that when a write is happening to a SQL Server database table, a read to that table will have to wait. This isn't necessarily true. Proper indexing can help mitigate blocking waits by giving queries adequate paths to data, and helping modification queries find the data they're going to be modifying. I know that it is always best to perform ...


4

You could use grouping and conditional aggregating, like this: SELECT id, MAX(CASE OtherVal WHEN 100 THEN MyVal END) AS MyVal1, MAX(CASE OtherVal WHEN 200 THEN MyVal END) AS MyVal2 FROM MyTable WHERE OtherVal IN (100, 200) GROUP BY id ;


4

Yes, that is a good and popular approach to offloading reporting, especially since you already have database mirroring in place. Please see this BOL reference for more specifics: http://msdn.microsoft.com/en-us/library/ms175511.aspx As for when you should create the snapshots, that is completely up to you and your business requirements, depending on how ...


4

When I've been faced with this type of problem in the past, I have created a "numbers" table to help deal with the missing rows. I created my numbers table specifically to deal with dates as so: CREATE TABLE Dates ( dDate DATETIME NOT NULL CONSTRAINT PK_Dates PRIMARY KEY CLUSTERED ); INSERT INTO Dates (dDate) SELECT TOP(73049) DATEADD(d, -1, ...


4

If your SSIS package runs with the appropriate file system permissions, you can create a template Excel file with the desired formatting and copy that file to a new one (using a file system or script task) where the data will be exported. If the sheet will contain a variable number of cells that must be formatted, you can use conditional formatting in the ...


4

DECLARE @yesterday SMALLDATETIME; SET @yesterday = DATEADD(DAY,-1,CONVERT(SMALLDATETIME, CONVERT(DATE, GETDATE()))); SELECT ... WHERE X.stage = 'Approval' AND X.T_ExitTime >= DATEADD(HOUR, 6, @yesterday) AND X.T_ExitTime < DATEADD(HOUR, 17, @yesterday) ORDER BY x.T_ExitTime; Don't think you want BETWEEN for datetime ranges, unless you really want to ...


4

If you want something quick and easy, and can accept all column widths being the same, then try the -Y option of SQLCMD.exe: C:\>SQLCMD -Y 3 -Q "SELECT name, name, name from sys.objects;" nam nam nam --- --- --- sys sys sys pla pla pla spt spt spt fai fai fai MSr MSr MSr sp_ sp_ sp_   OR, since the goal here is to email the output as a report, you ...


3

I tried both, sudo apt-get install sqlite and building from source but problem persists. Then I tried this command and it works. May be it helps someone else. sudo apt-get install libsqlite3-dev


3

I would do something like this: period_start_on date period_duration interval position int -- position of this row in the year, -- e.g. 1 for first Monday of Oct, -- 2 for 2nd Monday, etc. values ... primary key(period_start_on, period_duration) check(position >= 1 and position < 40) Then if you want ...


3

I'm not sure how you're going to be reporting this data back to said farmer, here's a quick way of doing what you're requesting: declare @test table ( sd datetime, id int, st nvarchar(100) ); insert @test (sd,id,st) values ('05/25/2012',1,'Shipment to Customer'); insert @test (sd,id,st) values ('05/26/2012',1,'Shipment to Customer'); insert @...


3

Since I can't see your table structure and I don't know the size of your data, here's something that will return the right answer but may or may not need to be tweaked for performance. You can write a query that groups both created and closed tickets together by date. Although you could just write two queries and do a full outer join, I would guess you want ...


3

I have more than 8 databases and all are in a SQL Server environment. I need to extract data from specific tables (more than 50 tables) of each database and dump into a central database for reporting and analysis purposes (a one way transfer). Table structures are the same in all databases. REPLICATION : If the objects (tables) you are replicating are ...


3

Per the article you provided, give this a shot for another potential workaround: On the Reporting Services server, modify the RecycleTime value of the rsreportserver.config file, and time this out so a 'recycle' at the app server level of RS occurs just before the start of the regular business day every day - like an hour or so before any production RS ...


3

This is a "known" issue/feature according to Microsoft. Descriptions, parameter defaults, subscriptions, etc. all fall under report "meta data" and are maintained separately on the Report Manager server. The only way to get these to change on the Report Manager is to either manipulate them manually or delete the report and upload anew. The links below ...


3

You can use Power BI service and Power BI Mobile Apps to share reports with others as explained here. Take a look here at the Publish to Web feature in Power BI. Here you can see how easy is to create a report from SQL Azure data on Power BI. Hope this helps.


Only top voted, non community-wiki answers of a minimum length are eligible