T-SQL (SS2K8) :: Aggregate Monthly Values From Interval Values?

May 14, 2014

I want to aggregate to monthly values for the reading. I want to display Reading value for Oct 2010, November 2010 likewise My question is simple and I have tried to follow the etiquette.

Currently it is displaying.....

MeterIDReadingdateReading
3969 22/10/2013 0:150
3969 22/10/2013 0:300
3969 22/10/2013 0:450
3969 22/10/2013 1:000
3969 22/10/2013 1:150
3969 22/10/2013 1:300
3969 22/10/2013 1:450
3969 22/10/2013 2:001
3969 22/10/2013 2:150
MeterId int
ReadingDate datetime
Reading real

-===== If the test table already exists, drop it

IF OBJECT_ID('TempDB..#mytable','U') IS NOT NULL
DROP TABLE #mytable

--===== Create the test table with

CREATE TABLE #mytable
(
meterID INT PRIMARY KEY,
Readingdate DATETIME,
reading real
)

--===== Setup any special required conditions especially where dates are concerned

SET DATEFORMAT DMY
SELECT '4','Oct 17 2013 12:00AM','5.1709' UNION ALL
SELECT '4','Oct 17 2013 12:15AM','5.5319' UNION ALL
SELECT '4','Nov 17 2013 12:00AM','5.5793' UNION ALL
SELECT '4','Nov 17 2013 14:00AM','5.2471' UNION ALL
SELECT '5','Nov 17 2013 12:00AM','5.1177' UNION ALL
SELECT '5','Nov 17 2013 14:00AM','5.5510' UNION ALL
SELECT '5','Dec 17 2013 15:00AM','5.5128', UNION ALL
SELECT '5','Dec 17 2013 16:00AM','5.5758' UNION ALL

Output should display as

MeterId Period Reading

4 Oct 13 10.20
4 Nov 13 10.40
5 Oct 13 10.20
5 Nov 13 10.40
4 Dec 13 11.15

View 4 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: How To Aggregate Interval Data To Hourly Values

May 12, 2014

I am using the below script and I am getting data for 15 minutes interval. I would like to aggregate this data to hourly so instead of reading for 2014-01-01 00:15:00.000 and 2014-01-01 00:30:00.000 I want all the data aggregated for 2014-01-01 00:00:00.000 and then for 2 o’clock. how should I tweak this query to sum the interval values and display it?

SELECT r.MeterId, r.ReadingDate, r.Reading
FROM MeterReading r, MeterDetail d, Building b
where r.MeterId = d.MeterId
and d.BuildingId = b.BuildingId
and b.BuildingName like '%182%'
and r.ReadingDate between '2014-01-01'and '2014-01-10'
order by r.MeterId

Current Output

MeterIdReadingDateReading
3969 1/01/2014 0:000
3969 1/01/2014 0:150
3969 1/01/2014 0:300
3969 1/01/2014 0:450
3969 1/01/2014 1:000
3969 1/01/2014 1:151
3969 1/01/2014 1:300
3969 1/01/2014 1:450
3969 1/01/2014 2:000
3969 1/01/2014 2:150
3969 1/01/2014 2:300
3969 1/01/2014 2:450
3969 1/01/2014 3:000

View 7 Replies View Related

Breaking Monthly Values Down Into Daily Values

May 18, 2007

Hi all, I'm new to MDX and am getting very confused with a script.

I'm running into problems breaking down monthly measures to daily values. If I have a monthly measure of 50, I would like to divide it by the number of days in the month to come up with daily values.

I believe I have set the proper granularity for the measurement relationship against the time dimension and have added the following script to my MDX script:

[Date].[Date].Members = [Measures].CurrentMember / [Date].[Calendar].CurrentMember.Parent.Children.Count

When I submit a query like this:

SELECT [Measures].[Measurement Objective] ON 0,
MTD([Date].[Calendar].[Date].&[20070207]) ON 1
FROM [Cube]

Everything looks good. The query returns seven rows, each with a properly scaled version of the monthly measurement. However, when I write the following query to return a single MTD value:

SELECT [Measures].[Measurement Objective] ON 0
FROM [Cube]
WHERE MTD([Date].[Calendar].[Date].&[20070207])

It doesn't work. It gives me the error:

The MDX Function CURRENTMEMBER failed because the coordinate for the
'Calendar Year' attribute contains a set

I'm sure this is just a matter of me misunderstanding MDX. Any help would be appreciate.
Thanks,
Richard

View 5 Replies View Related

T-SQL (SS2K8) :: Count Number Of Values That Exist In A Row Based On Values From Array Of Numbers

Apr 16, 2014

How to count the number of values that exist in a row based on the values from an array of numbers. Basically the the array of numbers I want to look for are in row 1 of table [test 1] and I want to search for them and count the "out of" in table [test 2]. Excuse me for not using the easiest way to convey my question below. I guess in short I have 10 numbers and like to find how many of those numbers exist in each row. short example:

Table Name: test1
Columns: m1 (int), m2 (int), m3 (int) >>> etc
Array/Row1: 1 2 3 4 5 6 7 8 9 10

------
Table Name: test2
Columns: n1 (int), n2 (int), n3 (int), n4 (int), n5 (int)

Row 1: 3, 8, 18, 77, 12
Row 2: 1, 4, 5, 7,18, 21
Row 3: 2, 4, 6, 8, 10

Answer: 2 out of 5
Answer: 4 out of 5
Answer: 5 out of 5

View 2 Replies View Related

T-SQL (SS2K8) :: Dynamically Change Values In Script Based On 3 Values

Feb 27, 2014

I have a script that I use after some amount of data massaging (not shown). I would like to be able to change the

1) denominator value (the value 8 in line 32 of my code) based on how many columns are selected by the where clause:

where left(CapNumber,charindex('_', CapNumber)-1) = 1where capNumber is a value like [1_1], [1_4], [1_6]...[1_9] capNumber can be any values from [1_1]...[14_10] depending upon the specialty value (example: Allergy) and the final number after the equal sign is a number from 1 to 14)

2) I'd like to dynamically determine the series depending upon which values correspond to the specialty and run for each where: left(CapNumber,charindex('_', CapNumber)-1) = n. n is a number between 1 and 14.

3) finally I'd like to dynamically determine the columns in line 31 (4th line from the bottom)

If I do it by hand it's 23 * 14 separate runs to get separate results for each CapNumber series within specialty. The capNumber series is like [1_1], [1_2], [1_3],[1_4], [1_5], [1_6], [1_7], [1_8],[1_9]
...
[8_4],[8_7]
...
[14_1], [14_2],...[14_10]
etc.

Again, the series are usually discontinuous and specific to each specialty.

Here's the portion of the script (it's at the end) that I'm talking about:

--change values in square brackets below for each specialty as needed and change the denom number in the very last query.

if object_id('tempdb..#tempAllergy') is not null
drop table #tempAllergy
select *
into #tempAllergy
from
dbo.#temp2 T

[Code] ....

If I were to do it manually I'd uncomment each series line in turn and comment the one I just ran.

View 6 Replies View Related

How To Get Sum Of Column Values On Interval

Mar 1, 2015

I am new to this group and to Sql query also. My requirement is the following

I have a table with these values...

colacolb colc cold
16:01 1 2 3
17:01 1 3 4
18:01 5 6 8
16:01 8 2 1
17:01 9 5 1
18:01 3 5 1
16:01 4 6 1
17:01 2 3 5
18:01 8 4 3

Now I want to get the SUM of colb, colc on each interval between 16:01 to 18:01...

View 4 Replies View Related

Delete Interval Of Values Of A Certain Field From A Table...

Dec 4, 2007



How do i use delete to perform a delete action of a value from a table using an interval of values?

--


delete [table] from [field]='[value]' to '[value]'

-- or



delete [table] where [field]='[value]' to '[value]'

--
i can't delete an interval of values (from...to) from a field of a certain table...

What is the right expression to do this?

View 9 Replies View Related

SQL Server 2008 :: Monthly - Updated Values - Keep For Reference

Oct 18, 2015

I have a table with constantly changing data - stock.

I want to start monitoring the value of stock at the end of each month. I can do this with a simple query, export the results to Excel and store on the network.

My question is:

Are there better ways to do this with SQL Server?

I thought of a monthly "job" that does the query and outputs to a file. (Need to be able to look at each month separately though for trend monitoring.)

Then wondered, if I should have an extra table to store the data and write queries on that in the future?

View 1 Replies View Related

T-SQL (SS2K8) :: Create Union View To Display Current Values From Table A And All Historical Values From Table B

May 6, 2014

I have 2 identical tables one contains current settings, the other contains all historical settings.I could create a union view to display the current values from table A and all historical values from table B, butthat would also require a Variable to hold the tblid for both select statements.

Q. Can this be done with one joined or conditional select statement?

DECLARE @tblid int = 501
SELECT 1,2,3,4,'CurrentSetting'
FROM TableA ta
WHERE tblid = @tblid
UNION
SELECT 1,2,3,4,'PreviosSetting'
FROM Tableb tb
WHERE tblid = @tblid

View 9 Replies View Related

Select Daily, Monthly, Weekly, Quarterly And Yearly Values For Graph Report

May 28, 2008



Hi



I am very new to analysis services and using MDX.



I want to select data from a cube using an MDX statement and show the data on a graph report.



I want to select the daily, weekly, monthly and quarterly descriptions all in one column to make it easy to represent it on the report.



Then set the 'Date' Column to the x-axis and the Value column to the y-axis.



The user also must have the option to not show certain periods (Switch of daily and weekly)



My MDX works when I select from the SQL Management Studio but as soon as I copy the MDX over to the SSRS Report Designer is splits the daily, weekly, monthly, quarterly and yearly values into seperate columns which makes it very difficult to report on.

----
Code



SELECT NON EMPTY { ([Measures].[ValueAfterLogic])} ON COLUMNS,

NON EMPTY { [KPI Values].[KPI Name].[KPI Name].ALLMEMBERS * ORDER(

CASE 1 WHEN 1 Then [Time].[Hierarchy].[Day Of Month] ELSE NULL END +

CASE 1 WHEN 1 Then [Time].[Hierarchy].[Week Of Year Name] ELSE NULL END +

CASE 1 WHEN 1 Then [Time].[Hierarchy].[Month] ELSE NULL END +

CASE 1 WHEN 1 Then [Time].[Hierarchy].[Quarter Of Year Name] ELSE NULL END +

CASE 1 WHEN 1 Then [Time].[Hierarchy].[YEAR] ELSE NULL END,

[Measures].[ValueAfterLogic],DESC)

}

DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM

(SELECT ( {[KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}] } ) ON COLUMNS

FROM [Workplace])

WHERE ( [KPI Values].[KPI Id].&[{97754C54-AB43-403D-A2C2-21C04BDE93E3}])

CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS



The case statement will take paramter values when finished

----------------end of code portion



Is this possible or is it suppose to 'split' the columns when moving to SSRS.





Thans in advance

Dev environment - SQL 2008 Feb CTP, VS 2008

View 5 Replies View Related

Use Aggregate Values

Jun 6, 2003

Hi all,

I have a table with the following fields:

carrier,calc_date,ind_id,rcf

I need to run a query to get the following result(by carrier and for each calc_date, calculate the percentage of all individuals who have rcf greater than 0.73):

carrier,calc_date,count of ind with rcf > 0.73, count of all individual, percentage of individuals with rcf's greater than 0.73.

does anyone have an idea of how to achieve that result?

Thanx

View 4 Replies View Related

Updating With Aggregate Values

Mar 9, 2004

I have table A which has and accountid,df_date1,df_date2. The table is a demographic one which has 1 record for each account
I have a table B which I need to populate from the first df_date1 fields in table A. Table B which is normalized and has an accountid and a df_date1 field but may have several records per accountid. I need the max(date) from this table. I wanted to do an update statement like below

update A
set df_date1
= max(df_date1) from b
where a.account_id = b.account_id

I get the error message
Server: Msg 157, Level 15, State 1, Line 3
An aggregate may not appear in the set list of an UPDATE statement.

Is there another way to do this with a subselect and update?

View 2 Replies View Related

T-SQL (SS2K8) :: Stored Procedure To Truncate And Insert Values In Table 1 And Update And Insert Values In Table 2

Apr 30, 2015

table2 is intially populated (basically this will serve as historical table for view); temptable and table2 will are similar except that table2 has two extra columns which are insertdt and updatedt

process:
1. get data from an existing view and insert in temptable
2. truncate/delete contents of table1
3. insert data in table1 by comparing temptable vs table2 (values that exists in temptable but not in table2 will be inserted)
4. insert data in table2 which are not yet present (comparing ID in t2 and temptable)
5. UPDATE table2 whose field/column VALUE is not equal with temptable. (meaning UNMATCHED VALUE)

* for #5 if a value from table2 (historical table) has changed compared to temptable (new result of view) this must be updated as well as the updateddt field value.

View 2 Replies View Related

Is There An Aggregate Function To Sum A Datetime Field Values?

Jan 15, 2004

Hi all...

how I can obtain the sum of a datetime field as aggregate function?

Given a set of records I need to calculate the number of records (count (*)) and the sum of a field of type datetime.
Is this possible? how?

Thanks..

Massimo

View 7 Replies View Related

Aggregating Values That Match Aggregate Result

Sep 23, 2015

The actual schema I'm working against is proprietary and also adds more complication to the problem I'm trying to solve. So to solve this problem, I created a mock schema that is hopefully representative. See below for the mock schema, test data, my initial attempts at the query and the expected results.

-- greatly simplified schema that makes as much sense as the real schema
CREATE TABLE main (keyvalue INT NOT NULL PRIMARY KEY, otherdata VARCHAR(10));
CREATE TABLE dates (datekeyvalue INT NOT NULL, keyvalue INT NOT NULL, datevalue DATE NULL, PRIMARY KEY(datekeyvalue, keyvalue));
CREATE TABLE payments (datekeyvalue INT NOT NULL, keyvalue INT NOT NULL, paymentvalue INT NULL, PRIMARY KEY(datekeyvalue, keyvalue));

[Code] ....

Desired results:

SELECT 1 AS keyvalue, 'first row' AS otherdata, '2015-09-25' AS nextdate, 30 AS next_payment
UNION ALL
SELECT 2, 'second row', '2015-10-11', 150
UNION ALL
SELECT 3, 'third row', NULL, NULL

I know I'm doing something wrong in the last query and I believe another sub-query is needed?

Let me answer a few questions in advance:

Q: This schema looks horrible!
A: You don't know the half of it. I cleaned it up considerably for this question.

Q: Why is this schema designed like this?
A: Because it's a 3rd-party mainframe file dump being passed off as a relational database. And, no, I can't change it.

Q: I hope this isn't a frequently-run query against a large, high-activity database in which performance is mission-critical.
A: Yes, it is, and I left out the part where both the date and the amount are actually characters and have to pass through TRY_CONVERT (because I know how to do that part).

View 2 Replies View Related

Problem Finding Values With Aggregate Functions

Jul 23, 2005

Hi all!In a statement I want to find the IDENTITY-column value for a row thathas the smallest value. I have tried this, but for the result i alsowant to know the row_id for each. Can this be solved in a neat way,without using temporary tables?CREATE TABLE some_table(row_id INTEGERNOT NULLIDENTITY(1,1)PRIMARY KEY,row_value integer,row_name varchar(30))GO/* DROP TABLE some_table */insert into some_table (row_name, row_value) VALUES ('Alice', 0)insert into some_table (row_name, row_value) VALUES ('Alice', 1)insert into some_table (row_name, row_value) VALUES ('Alice', 2)insert into some_table (row_name, row_value) VALUES ('Alice', 3)insert into some_table (row_name, row_value) VALUES ('Bob', 2)insert into some_table (row_name, row_value) VALUES ('Bob', 3)insert into some_table (row_name, row_value) VALUES ('Bob', 5)insert into some_table (row_name, row_value) VALUES ('Celine', 4)insert into some_table (row_name, row_value) VALUES ('Celine', 5)insert into some_table (row_name, row_value) VALUES ('Celine', 6)select min(row_value), row_name from some_table group by row_name

View 2 Replies View Related

SQL 2012 :: How To Target Transactional Values With Aggregate Amount

Apr 27, 2015

I am trying to exclude records that have an assessed value that has been waived in an aggregation. For Example:

Here is my table:

CREATE TABLE #temptable (ReportingMonth Varchar(6), Fee_Code Varchar(20), Fee_Transaction_Amount Decimal(12,2), Fee_Transaction_Date Datetime, Fee_Transaction_Type Char)
INSERT INTO #temptable (ReportingMonth, Fee_Code, Fee_Transaction_Amount, Fee_Transaction_Date, Fee_Transaction_Type)
SELECT 'Jan-13', 'ONE TIME DRAFT FEE', '20', '01/24/2013', 'A'
UNION ALL SELECT 'Feb-13', 'LATE CHARGE', '33.6', '02/19/2013', 'A'
UNION ALL SELECT 'Mar-13', 'LATE CHARGE', '37.01', '03/18/2013', 'A'

[code]....

Here are Data Mapping Description

Reporting Month = Month - Year
Fee Code = Fee Description Name
Fee Transaction Amount = Fee Amount
Fee Transaction Date = When Fee Amount was Applied
Fee Transaction Type = "A" = Assessed Fee; "W" = Waived Fee; "P" = Paid Fee

I've also included an image with beginning data set and what I want to identify in red and what my final data set should look like after the exclusion of those 4 records are removed.

Here are the logic requirements:In the attachment what I need the logic to do is essentially identify the $20 One Time Draft Fee from the first instance using the MIN Transaction date. Since there were $80 waived for this fee code (One Time Draft Fee), I would expect to see the first 4 (highlighted in red) to be identified as the target and as you can see in the attachment the second data set had the 4 highlighted items removed. That should be my final output.

trying to loop and remove the waive amounts from the assess amounts and tied it back to remove from my base data.

View 1 Replies View Related

Reporting Services :: SSRS Matrix Aggregate Values And Rounding Error

Jun 11, 2015

I am using SSRS 2014. I'm using a matrix instead of a tablix because it allows me to have dynamic columns. In the example I'm showing, two of the columns use the sum function to get the total counts per practice. The third column contains percentages so I averaged for the total but the value is inaccurate compared to the value I would get if the divided the the two totals that are sums of the counts. Is there a way for me to specify that I want to divide the total counts numerator divided by the total counts denominator?

Here's an example report output with the percentage column averaged (inaccurate):

PCP            numerator denominator percentage
John Smith  66             104  63.46
Tom Jones    4                36    11.11
.
.
.
Jane Doe     1    1       100
Total 708          1005       72.3

So the 72.3 value is from Avg(metricvalue)

I would like to do this instead: % total = 708/1005, which equals 70.5 - a significant difference.

The metricvalue column is what is the value for every number above (Because it's a matrix).

View 2 Replies View Related

T-SQL (SS2K8) :: BETWEEN Values Conundrum

Oct 23, 2014

I have a problem with trying to pull postcodes from a table when I have a lookup table which provides me with a StartPostCode and an EndPostCode.

For instance if, in the input table I have a postcode BETWEEN two values eg: CV1 and CV10 I want to get a third value from the look up table.My problem is my query seems to bring two values back even if the postcode is between the ranges specified.To reproduce the problem first create the tables and populate with the data.

USE [CTSStaging]
GO
/****** Object: Table [dbo].[st_StobartPostCode] Script Date: 10/23/2014 12:42:25 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON

[code]....

View 6 Replies View Related

T-SQL (SS2K8) :: Joining Where Values Do Not Equal One Another

Apr 23, 2014

I have two select statements; one for open purchase orders, one for open customer orders. I would like to be able to combine the query based on i.item in the top statement joined with c.item from the bottom statement. The i.item is related to a specific c.item, but they do not have the same values. In this case I want to join based on.

p.item=i.item where
1001099548=1001099550
84162359=84198545
84532300=84532293
47547523=47547951
305545A3=87433653
87444977=87444975

left side coming from p.item = right side coming from c.item.

Here are my statements.

--#1 OPEN PO's
SELECT p.item
,(p.qty_ordered-p.qty_received) as POQtyRemaining
,i.item
,i.qty_on_hand
,p.po_num

[Code] ....

View 8 Replies View Related

T-SQL (SS2K8) :: Select One Of Three Values That Are Not NULL?

May 6, 2014

I am working on some data that is JOINing to another table. Not a big thing. In the child table, there are different values for a single ID. I want to be able to select the Max ColorID that is Not Null, for each distinct CarID. The CarID is what I am joining the other table with. I need selecting the distinct row with the Max ColorID that is not Null. All this data is made up, so nothing looks logical in the design.

DECLARE @ColorList TABLE
(
CarID float
, ColorID int
)
INSERT INTO @ColorList
SELECT 1.55948815793043E+15, 9 UNION ALL
SELECT 1.55948815793043E+15, 27 UNION ALL

[code]....

So this would be the resultset:

1.55948815793043E+15, 27
1.62851796905743E+15, 27
1.51964586107807E+15, 9
1.55948815793043E+15, 27
1.47514023011517E+15, 5
1.64967408641916E+15, 27
1.51964586107807E+15, 9
1.56103326128036E+15, 27
1.49856249351719E+15, 9
1.5736407022847E+15, 6
1.64664602022073E+15, 27
1.51964244007807E+15, 27

View 3 Replies View Related

T-SQL (SS2K8) :: How To Make Sum Of Values For The Month

May 19, 2014

I've the table like

create table expense
(
bill_date datetime,
travel int,
fixed int,
food int,
lodge int
)

insert into expense values('01-04-2014',1200,250,0,0)
('02-04-2014','0',0,500,600)
('0-04-2014','800',300,0,0)

Like I've 30th onwards.....

Expecting o/p:

month Travel Fixed Food Lodge
apr-14 200 550 500 600

These cum column values how to make a code ?????

View 5 Replies View Related

T-SQL (SS2K8) :: Assign Next Available Column Values?

Sep 29, 2014

find below object and data:

create table #StuDetails(City varchar(25),StuStatus varchar(25), currentValue int,Week1 int,week2 int,week3 int,week4 int)
insert into #StuDetails values('A','new',13,10,0,0,12)
insert into #StuDetails values('B','Old',10,10,41,0,12)
insert into #StuDetails values('C','Fail',10,9,0,0,5)
select * from #StuDetails

Output of above is display as:

CityStuStatuscurrentValueWeek1week2week3week4
Anew 13 10 0 0 12
BOld 10 10 41 0 12
CFail 10 9 0 0 5

Now for columns Week1 to week3 if value is 0 then i want to display by searching next week value, if it is also 0 then go for next week and if value found there then display instead of zero. so my output would be as below instead of above.

CityStuStatuscurrentValueWeek1week2week3week4
Anew 13 10 12 12 12
BOld 10 10 41 12 12
CFail 10 9 5 5 5

View 2 Replies View Related

T-SQL (SS2K8) :: Set Current Row Using Values In Previous Row

Feb 25, 2015

I've tried all sorts of code i.e. cross apply, running totals, etc. Cannot get this to work. I am trying to add a previous row value but only doing it for each group.

Source records
DECLARE @tbl table (Item int, Sequence int, StartTime datetime, Duration int)
INSERT INTO @tbl (Item,Sequence,StartTime, Duration) VALUES (1,1,'2/25/2015 12:00 am',10),(1,2,null,20),(1,3, null,22),(2,1,'2/25/2015 1:00 am',15),(2,2,null,30),(2,3, null,45),(2,4, null,5)
select * from @tbl

ItemSequenceStartTimeDuration
1102/25/15 0:0010
12null 20
13null 22
2102/25/15 1:0015
22null 30
23null 45
2 4 null 5

I would like to set the start time of the next row to be equal to the previous row time + duration. I know the start time of each group of 'Items' when the 'Sequence' number = 1. The last 'duration' value in the group would be ignored.

My expected output would be:

ItemSequenceStartTimeDuration
1102/25/15 0:0010
1202/25/15 0:1020
1302/25/15 0:3022
2102/25/15 1:0015
2202/25/15 1:1530
2302/25/15 1:4545
2402/25/15 2:305

View 7 Replies View Related

T-SQL (SS2K8) :: SUM OVER Coming Up With Different Values Than CTE Query

Mar 6, 2015

I am currently reading through Itzik Ben-Gan's "Microsoft SQL Server 2012 High-Performance T-SQL using Windows Functions." In attempt to test the SUM OVER() function in SQL 2008 because that's what I've got. I do not currently have sample data (trying to generate it has become a major PITA), but I have some pseudocode.

My current code (actual production code) pulls a bunch of ITD (inception to date) contracts then calculates a certain dollar amount based on monthly changes. Not all contracts have values during a given month, so here's what I cobbled together a few months ago. (Per our finance team, these numbers ARE accurate).

WITH MonthlyVals AS
(SELECT ContractID, SUM(Col1 - (Col2 + Col3 + Col4 + Col5)) AS MyTotal
FROM MyTable
WHERE MyDate >= @ThisMonthStartDate AND MyDate <= @ThisMonthEndDate
AND StatementType IN (8,4,2)

[code]....

To test the totals, I also added a COMPUTE SUM(MyTotal) to the end of each query. (Yes, I know COMPUTE is deprecated. Just wanted a quick check.). The difference between the two bits of code was over 68k, with the SUM OVER() code coming up with a total higher than the CTE code. I know CTE code is correct for a fact. It went through extensive testing before getting put in Production. Is it the way I joined the table for the SUM OVER()? Or is it the use of PARITION BY?

View 5 Replies View Related

T-SQL (SS2K8) :: Using Like To Identify Value That Contain Alphanumeric Values Other Than A-Z

May 11, 2015

Trying to use LIKE / NOT LIKE to identify values that contain any alphanumeric characters outside of A-Z e.g £%$^&*_-{[@ etc etc

The field should contain only values between A-G with a numberic e.g ABCD1234567... but some rows have characters such as above, some have spaces (weeps) , and some have letters outside the A-G range ....

View 7 Replies View Related

T-SQL (SS2K8) :: How To Select Columns That Have Some Values Only

Jun 1, 2015

I have event table that containing multiple events, and many of them are empty. I'd like to select only the columns that have values in them.

Here is an example:

IF OBJECT_ID('tempdb..#events') IS NOT NULL
DROP TABLE #events
create table #events (eventId int,
Category varchar(250),
events1 varchar(250),

[Code] .....

In this case, I'd like to run a query like this one(skip Column Event3):

Select eventId,Category,events1,events2,events4,events5 From #events

View 4 Replies View Related

T-SQL (SS2K8) :: Return All Values From Table

Oct 1, 2015

I have a small problem with a join clause, because i need to return all values from my table BL:

my code is:

SELECT cast(0 as bit) as 'Escolha',data, contado , ollocal ,origem, ousrdata,ousrhora
FROM
(
SELECT noconta,banco, u_area
FROM BL

[code]....

In fact, i need to return 2 accounts (16,35) - x.NOCONTA IN (16,35), but I know that the problem is on the WHERE clause.How can do that, because i need all the condition on WHERE clause regarding my table OL, but also, i need to return my two accounts (16,35).

View 2 Replies View Related

T-SQL (SS2K8) :: Get Column Names Where Values Are Not Matching

May 20, 2014

We have 2 tables (table a and b)

select a.* from table a inner join table b
on a.col1<> b.col2

I would like to have column names where the values are not matching.

View 4 Replies View Related

T-SQL (SS2K8) :: Concatenate Specific Values From A Column

May 20, 2014

Give this data set;

declare @BO table (Col1 varchar(50), Col2 varchar(50))
insert into @BO values
('01','009920140516102116'),
('071710811019600001000005',''),
('070534524264000001000005',''),
('001806505517000001000005',''),

[Code] ....

select * from @BO

The 2 digit number that appears on line 1, 7 and 13 (only in this example) i need to have added to the begin of each value below it until the next 2 digit number is encountered. The desired result set would look like:

declare @BOD table (Col1 varchar(50), Col2 varchar(50), col3 varchar(50))
insert into @BOD values
('01','009920140516102116',''),
('071710811019600001000005','','01071710811019600001000005'),
('070534524264000001000005','','01070534524264000001000005'),

[Code] ....

View 7 Replies View Related

T-SQL (SS2K8) :: How To Cut Certain Values In A String To Separate Columns

Jun 5, 2014

I have a column containing values for different languages. I want to cut out the values per languate in a seperat column.

The syntax is a 2 letter country code followed by : the value is contained in double quotes. each languate is separated by a ; (except for the last one)

EX ur English, Dutch and Swedish:US:"Project/Prescription sale";NL:"Project/specificatie";SW:"Objektsförsäljning"

The result would Be
column header US
with value Project/Prescription sale

next column header NL
with value Project/specificatie etc.

Here are table examples:

IF OBJECT_ID('[#SALETYPE]','U') IS NOT NULL
DROP TABLE [#SALETYPE]

CREATE TABLE [#SALETYPE](
[SaleType_Id] [int] NOT NULL,
[name] [nvarchar](239) NOT NULL,

[Code] ....

View 9 Replies View Related

T-SQL (SS2K8) :: Comparing Column Values In Same Table

Jun 16, 2014

How to resolve the below task

create table #temp ( idx int identity(1,1), col1 int, col2 int )

Here i want a flag success or fail on basis of below conditions.

I need to take all the col1 values and then i need to compare to each other.

if any difference found, i need to check difference more than 30, then it should raise the flag as "Failure".

if all the col1 values are ok , then we need to check Col2 values same as above.

--case 1

insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16582,18522

--select * from #temp

--truncate table #temp

Because of difference in col1 values . the value of flag should be fail.

--case 2

insert into #temp(col1,col2)
select 16522,18522
union all
select 16522,18522
union all
select 16522,17522

Here also the col1 is ok but col2 values have difference so it should be Fail.

Otherwise it should be success.

View 6 Replies View Related

T-SQL (SS2K8) :: How To Calculate Total Sum Of Values Of Table

Jul 11, 2014

I've the table like

create table accutn_det
(
fs_locn char(50),
fs_accno varchar(100),
fs_cost_center varchar(100),
fs_tran_type char(50)

[Code] .....

Like all location details stored from all months in these table

here Dr=debit,Cr=Credit Formula= 'Dr-Cr' to find the salary wavges of amount

so i made the query to find the amount for may

select
fs_locn,
fs_accno,
amount=sum(case when fs_accno like 'E%' and fs_tran_type='Dr' then fs_amount
when fs_accno like 'E%' and fs_tran_type='Cr' then fs_amount * -1
end
)
from
accutn_det where fs_trans_date between '01-may-2014' and '31-may-2014'
groupby fs_locn,fs_accno

now i need the sum values of all costcenter for the particular account.how to do that?

View 9 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved