CREATE procedure sp_ActivityFrequencyOneUserGraph ( @date1 nvarchar(100) , @date2 nvarchar(100), @customerID int, @user int, @type nvarchar(50) ) as select Count(Page) as VisitingCount,[Time] from ( SELECT Page,Date,[user], ...
Hello, I am tracking page views within modules using the following table: id | page_id | user_id | session_id | created Every page is unique to a certain module; so page 14 only exists in module 2, for instance. I am trying to write a query that tells me when a viewing session began (GROUP BY session_id) for a particular module, but...
It's possible to map a VIEW using Fluent NHibernate? If so, how? ...
Hello, I am currently working on a VB.NET desktop application that uses .mdb (Access) database files on the backend. The .mdb files are opened and edited in the VB.NET app. Once editing is completed, the users will need to import the data into our SQL Server database. This is an easy task, until you try to introduce the dynamic filepat...
Hello All, I have a procedure in SQL that I am trying to turn into Linq: SELECT O.Id, O.Name as Organization FROM Organizations O JOIN OrganizationsHierarchy OH ON O.Id=OH.OrganizationsId where OH.Hierarchy like '%/12/%' The line I am most concerned with is: where OH.Hierarchy like '%/12/%' I have a column that stores the hierarch...
Hi all, I want to write the following function which should be used in an Excel worksheet: =GetRecField("Foo Record Key", "FooField1") ...which will connect internally through ODBC to an SQL database, execute there an SELECT FooField1 FROM MyTable WHERE KEY_FIELD='Foo Record Key'; and will return the resulting value as the result ...
Let's say I have a SQL Server 2000 table, any name will do, it's irrelevant for this topic. On this table I have a trigger that runs after update or insert. The user is able to insert and update the table on which the trigger is attached, but not on other tables that the trigger targets. If the user modifies data in the original table...
I come from the asp.net world where we'd use an objectdatasource, hooked up to data access layer, and set it's ConflictDetection property to "CompareAllValues". There is an OldValuesParameterFormatString on the ObjectDataSource that you use to identify old value parameters. The sql procedure that does an update would then require both n...
I am using migrator.net to handle database migrations for SQL Server, and I like it so far. The only issue I've run into is how best to migrate changes to views. Thus far, I've been managing each update as a separate script file (which recreates the view). This lets me go back to previous versions of the view without duplicating the c...
I'll begin by admitting that my problem is most likely the result of bad design since I can't find anything about this elsewhere. That said, let's get dirty. I have an Activities table and an ActivitySegments table. The activities table looks something like: activityid (ident) | actdate (datetime) | actduration (datetime) | ticket...
Hello all, I have searched for this, but still can't seem to get this to work for me. I have an array of Id's associated with a user (their Organization Id). These are placed in an int[] as follows: int[] OrgIds = (from oh in this.Database.OrganizationsHierarchies join o in this.Database.Organizations on oh.Org...
Suppose the following table structure: Event: id: integer start_date: datetime end_date: datetime Is there a way to query all of the events that fall on a particular day of the week? For example, I would like to find a query that would find every event that falls on a Monday. Figuring out if the start_date or end_date falls on ...
I'm making a script that goes through a table that contains all the other table names on the database. As it parses each row, it checks to see if the table is empty by select count(*) cnt from $table_name Some tables don't exist in the schema anymore and if I do that select count(*) directly into the command prompt, it returns...
I would like to produce a character list of all of the first letters of column in my database. The SQL below illistrats what I would like to return. SELECT DISTINCT first_character(name) FROM mydatabase Is there a way to do this in MySQL? EDIT What is the advantage of using SUBSTRING over LEFT and vice versa? EDIT Currently there a...
I am looking to design a database for a website where users will be able to gain points (reputation) for performing certain activities and am struggling with the database design. I am planning to keep records of the things a user does so they may have 25 points for an item they have submitted, 1 point each for 30 comments they have made...
I'm a developer and I suck at SQL:) Please help me out here. I'd like to create my own Stored Procedure that creates a Tenant in my SaaS database. In order to do this I need to create a new SQL Login for the Tenant and then add it to a predefined SQL Role. I'm already stumped just trying to create the Login. Here is what I've tried....
I have a SQL script that populates a temp column and then drops the column at the end of the script. The first time it runs, it works fine because the column exists, then it gets dropped. The script breaks the 2nd time because the column no longer exists, even though the IF statement ensures that it won't run again. How do I get around S...
I am using CodeIgniter. My database is MySQL 5. The SQL statement below works fine, but I am thinking it would not really be compatible with MSSQL, PG, et al. I am wondering if it's possible to implement the statement using the Active Record class in CI, hence making it completely cross database ? I think the "GROUP_CONCAT" is where I'l...
Background I have a dimension table that has a single record for each day. Each record has a primary key so example data would be: Dimension Table --------------- --------------------------------- | ID | DateTime | --------------------------------- | 1083 | 04/10/2008 10:02:00 PM | --------------------------------- ...
I'm designing the Fact and Dimension tables for a data warehouse currently using SQL Server, SSIS, and SSAS. Will I get any real benefit from programming the relationships between the dimensions and the fact tables into SQL? Or am I better off just defining the relationships manually when it comes time to create the cubes? It seems to b...