I have 3 tables: DimAccounts, DimTime and FactBudget. DimAccounts example: DimTime example: FactBudget example: In FactBudget are many Accounts just with type B. I need to calculate Debit and Credit Sums where Account type is S (Sum). Columns AccountFrom and AccountTo shows B Type Accounts from where to begin summing (AccountFrom ) and where end (AccountTo). I have made solution
Tag: sql-server
Use one CTE many times
I have this, and i get an error at set total. Why can’t i access a cte many times? ALTER PROCEDURE [dbo].[GetLeaguePlayers] ( @idleague int, @pageNumber int, @pageSize int, @total int …
Altering column size in SQL Server
How to change the column size of the salary column in the employee table from numeric(18,0) to numeric(22,5) Answer
Create a trigger that inserts values into a new table when a column is updated
I’ve been looking at some previous answers on triggers on here but can’t find what I need exactly but I’m sure my question has been asked/answered before. I’m trying to keep track of any changes to …
SQL datetime needs to read 00:00:00.000
I have the following piece of SQL: select DATEADD(s,-1,DATEADD(mm, DATEDIFF(m,0,GETDATE()),0)) which comes through as this format: 2012-02-29 23:59:59.000 I need the exact piece of code with the …
Count records for every month in a year
I have a table with total no of 1000 records in it.It has the following structure: I want to calculate no of records for every month in year-2012 Is there any way that should solve my issue in a single shot? I tried: Answer
Select a column if other column is null
I need to select a field called ProgramID from a table and if the ProgramID is NULL then I need to select the value in the InterimProgramID from the same table and alias it as ProgramID. How can I …
Delete row if table exists SQL
I have a script that drops a load of tables using DROP TABLE IF EXISTS, this works. There is also a delete in this script to DELETE a row from another table that I do not manage. This table may or …
SQL: Join tables on substrings
I have a table A with the string-column a and a table B with the string-column b. a is a substring of b. Now I want to join the the two tables on a and b. Is this possible? I want something like this: Select * from A,B where A.a *”is substring of”* B.b How can I write this in
Remove duplicate data in ‘date-ranged’ rows
I have a table like the following. The date range is used to know for what time period the rest of the Attributes were valid, the problem i have is that there are several consecutive time ranges where the Attributes ramain the same, what I would like is to obtain the same data but without the duplicate rows. From the