ISO 6346 Check Digit Calculation In T-SQL

Jul 23, 2005

create function dbo.iso6346_char_to_number
(
@char char
)
returns int
as
begin
return
case upper(@char)
when '0' then 0
when '1' then 1
when '2' then 2
when '3' then 3
when '4' then 4
when '5' then 5
when '6' then 6
when '7' then 7
when '8' then 8
when '9' then 9
when 'A' then 10
when 'B' then 12
when 'C' then 13
when 'D' then 14
when 'E' then 15
when 'F' then 16
when 'G' then 17
when 'H' then 18
when 'I' then 19
when 'J' then 20
when 'K' then 21
when 'L' then 23
when 'M' then 24
when 'N' then 25
when 'O' then 26
when 'P' then 27
when 'Q' then 28
when 'R' then 29
when 'S' then 30
when 'T' then 31
when 'U' then 32
when 'V' then 34
when 'W' then 35
when 'X' then 36
when 'Y' then 37
when 'Z' then 38
end
end


create function dbo.iso6346_check_digit
(
@string char(10)
)
returns char
as
begin
return (dbo.iso6346_char_to_number(substring(@string,1,1) )*1
+dbo.iso6346_char_to_number(substring(@string,2,1) )*2
+dbo.iso6346_char_to_number(substring(@string,3,1) )*4
+dbo.iso6346_char_to_number(substring(@string,4,1) )*8
+dbo.iso6346_char_to_number(substring(@string,5,1) )*16
+dbo.iso6346_char_to_number(substring(@string,6,1) )*32
+dbo.iso6346_char_to_number(substring(@string,7,1) )*64
+dbo.iso6346_char_to_number(substring(@string,8,1) )*128
+dbo.iso6346_char_to_number(substring(@string,9,1) )*256
+dbo.iso6346_char_to_number(substring(@string,10,1 ))*512)%11
end

View 1 Replies


ADVERTISEMENT

Check Digit Calculation

Nov 20, 2007

I'm needing help with the following check digit calculation.

I have a Table with the following columns

Newscan nchar(31)
checkdigit nchar (10)


Newscan ex value 012345670 600888081 307051111

I need to multiply each character by the following sequence starting on the left alternating the multiplyer skipping spaces
21212121

if the multiplyed number is greater than 9 I need to add the two digits together. Ex 2X9 = 18 for a saved value of 9

I then need to add the entire string together
divide that by 10 and take the right most character and subtract that from 10 to end up with a check digit number that I want to store in the column checkdigit.

I have a case statement that converts the alpha chars in the string to the correct numeric values but I'm lost after that on how to accomplish this.

Thanks in advance for any help or direction.

I tried to look at the bank routing check digit function but couldn't get to the code of it.

Bill

View 12 Replies View Related

Calculate Check Digit On Subset Of Ids

Jul 17, 2006

how do i calculate all the checkdigits for a subset of ids and return all the ids and checkdigits? basic calculation isn't the problem, just how to select the ids and utilize the ids in the calc. I am new to trying to do calculations on data.

View 7 Replies View Related

Need 2 Digit Month Returned; Not 1 Digit

Sep 5, 2001

How can I return a 2 digit month into a variable for the months Jan thru September

SELECT DATEPART(mm, GETDATE()) ---> returns 9 for September.. I need 09 returned in order to properly build my target file name...

Thx,
BT

View 2 Replies View Related

Analysis :: SSAS Calculation With Division Combined With A Time Calculation?

Sep 17, 2015

I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.

(so I can use measure security on calculations

as explained here  )

SCOPE [Measures].[C];

THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);

View 2 Replies View Related

Converting Oracle Calculation To Sql Server 2005 Calculation

Jul 19, 2007

Hi I am having to convert some oracle reports to Reporting Services. Where I am having difficulty is with the

calculations.

Oracle

TO_DATE(TO_CHAR(Visit Date+Visit Time/24/60/60,'DD-Mon-YYYY HH24:MISS'),'DD-Mon-YYYY HH24:MISS')



this is a sfar as I have got with the sql version

SQLSERVER2005

= DateAdd("s",Fields!VISIT_DATE.Value,Fields!VISIT_TIME.Value246060 )



visit_date is date datatype visit_time is number datatype. have removed : from MI(here)SS as was showing as smiley.



using:

VS 2005 BI Tools

SQLServer 2005



View 5 Replies View Related

6 Digit Number Only

Apr 10, 2008

Hi,

I am trying to figure out and hope u guys help me.

I need a only 6 digit ID only eg

55 = 000055
405 = 000405
5544 = 005544

How i can do it in SQL server and which formula/datatype i must use?

Deaf can Do anything, except hear

View 9 Replies View Related

Erasing Digit

Feb 18, 2007

Hello. I have a column in my database which is holding phone number. The problem is that although I gave the number starting with zero but it will erase it automatically. So can you help me to solve this problem? Thank you

View 3 Replies View Related

Splitting 9 Digit Zip Codes

Jul 6, 2004

hey all
i am stuck with this little problem
I have a table with people's names and addresses and i have one column for the zip codes. sometimes it includes 5 digit zip codes like '70820' and some times it includes all nine digits like '70820-4565'

is there anyway to move the last 4 digits of the long zip codes into a new column? and remove the dash?

thanks

View 6 Replies View Related

Build ID W/Incrementing Digit

Jul 23, 2005

Help, I'm stuck! I have a Customer table:ID Name-- ---------------------1234 Christopher's Surf Boards4321 Christina's NailsI have to build a Account table:ID GPID-- --------------------1234 CHRIST004321 CHRIST01I've built a function that gives me the six alphas and concatenates thetwo digits. The GPID in the account table is the first six alphas ofthe company name plus two digits to ensure uniqueness. (Don't ask why Ican't use the existing id -- the story is long and boring.) I built afunction that creates the alpha part and assigns the first 00. I builta second function that I thought would check the Account table as I wasinserting and increment the numbers if the account number alreadyexisted. It doesn't work. In the above table, both IDs come out asCHRIST00. Here's the second function:ALTER FUNCTION fnValidateID(@mString varchar(15))RETURNS varchar(15)ASBEGINDECLARE@sTemp varchar(15),@sInc varchar(2),@iInc intBEGINset @sTemp = @mStringwhile exists (select GPID from dbo.tempID where GPID = @sTemp)beginset @iInc = convert(int,right(@mString,2)) + 1set @sInc = convert(varchar(2),@iInc)if len(@sInc) < 2 set @sInc = '0' + @sIncset @sTemp = substring(@sTemp,1,len(@sTemp) - 2) + @sIncend--NEXTENDRETURN @sTempENDMy first guess is that the code is okay, so far as it goes, but that Ican't check the values in the table at the same time I'm inserting intoit. (The Account table is empty, initially.) In any case, I can'tfigure out where I've gone wrong.Thanks.

View 8 Replies View Related

Retrieving A 2 Digit Month

Nov 19, 2007



I am sure this will be an easy one for you pros, but I am having a case of the Mondays. I am trying to retrieve a date in the format 2007.01.01. In trying to do this I either end up with 1 or null when trying to create the 01 part. Below is some test code for the month portion.

DECLARE @Tst CHAR(4)
DECLARE @tst1 CHAR(2)
SET @tst = DATEPART(Month, '1/1/2008')
SET @tst1 = '00'
--SET @tst = right(@tst1 + @tst, 2)
Select @tst, @tst1, @tst1 + @tst, right(@tst +tst, 2)

Result Set:

1 00 001 [blank]

What am I doing wrong here?

TIA

View 6 Replies View Related

Need Help With GROUP BY And COUNT On 9 Digit Zipcode

Sep 3, 2007

I have a table with a column 'zipcode' that contains a 9 digit zipcode. I am am trying to figure out how I can create a query that returns the count of the most popular zipcodes based only on the first 5 digits.

This is what I have

Code:

SELECT Customers.Zipcode, Count(Customers.Zipcode) AS CountOfZipcode
FROM Customers
GROUP BY Customers.Zipcode
ORDER BY Count(Customers.Zipcode) DESC;


which does what I want it to do except it uses the whole 9 digits.

Any help would be appreciated

View 4 Replies View Related

Convert 1084313300 (Ten Digit) Value To Datetime

Jul 23, 2005

I have a field in a table that contains ten digit value representing a datetime. Is there any way to convert it to default datetime formatThanks--Message posted via http://www.sqlmonster.com

View 2 Replies View Related

Datepart (to Get 2 Digit Number For The Month)

Apr 28, 2008

I want to build a string that contains today's date (without the time).

For example, if select GETDATE() returns 2008-04-28 14:18:34.180, I want to end up with either

(1) 2008-04-28
or
(2) 20080428. (I always want the month to be represented by a 2 digit number)

Is there a datepart parameter that will give me (1) in one shot? I don't think so, since datepart returns an integer and (1) is not in the form of an integer.

So, I was trying to build (2) by using the yyyy, mm, and dd parameters to extract out the appropriate parts and casting them to a string and contatentating them. However, when I do the month portion, (using mm) it gives me the integer that represents the month, and this number can be 1 or 2 digits depending on the month. I always want it to be a 2 digit number. For example, if I'm in Apr, I want to end up with "04", not "4". This would also apply to the day portion. (although the date that I'm writing this post is on the 28th so I'm not certain what GETDATE() will return on dates that are 1 digit.)

The only way I can get the single digit month to write out as a 2 digit month is by doing a LEN() function on the string and if it is a length 1, then concatenate a "0" in front of it. I started doing this and the expression became too crazy, so I wanted to first check to see if someone can come up with something cleaner.

Thanks for looking.

View 11 Replies View Related

Transact SQL :: 4 Digit Number To Add To Table

Oct 15, 2015

I am currently working on an app and have an issue with a table in the database.  The table has 10,000 records in it and a column is added that is to use a 4 digit in sequence. The datatype for the new column is varchar since no math will ever be done on the added column.  If necessary, I can change the datatype but would prefer not to.  The 4 digit would start with 0000 at ID 1 and go to 9999 at ID 10000.  I'm thinking some type of update statement since it is updating each record but how would it be done sequentially?

View 9 Replies View Related

How To Select Data If Length = 12 Digit

Mar 29, 2008

hi everyone,

I'm looking for statement how to select if length digit = 12. as an example,

data
---------------
123456789012
4567892100

I just want to select if the length of data = 12 digit then the answer is 123456789012

Your kindness really appreciated, I really need the answer as quickly

View 3 Replies View Related

Putting Zero At Front Of A Single Digit Variable?

Sep 28, 2014

I am retrieving the current month and setting a variable accordingly.

Code:
DECLARE @currMonthNum INT
SET @currMonthNum = DATEPART(MM, GETDATE())

My challenge is that at a later point I want to use the @currMonthNum value to 'build' a date in the style yyyymmdd and the variable is only one digit for the months January to September (e.g.: 1 to 9). For the months January to September I need to pad the variable with an extra zero (e.g.: 01 to 09).

Here is my attempt to do this...!

Code:
IF @currMonthNum IN (1, 2, 3, 4, 5, 6, 7, 8, 9)
SET @currMonthNum = '0' + CAST(@currMonthNum AS nvarchar)

Unfortunately this does not give the desired result of two digits, however ironically if I add a + '0' to the end I get 10 to 90! It is like the 0 at the front is simply being ignored.

I have also tried setting the @currMonthNum variable to an nvarchar type before performing the character addition as well attempting to use the CONCAT command (however I don't think that CONCAT can be used when setting a variable).

how to display a zero in front of the months that require it?

View 4 Replies View Related

How To Select Part Of Mobile Number From Digit

Sep 12, 2014

There is column mobile_number in my table

I have to replace with star(*) to the middle six character from contact number of 10 digit.

For example

The Contact number is 9334459875

I have to display it as follows

93******75 first two digit and last two only.

View 1 Replies View Related

How To Generate Random UNIQUE 13 Digit Number?

Aug 22, 2007

Hi

I have a sql procedure. I need to create UNIQUE random 13 digit number to use for barcode.

How do I generate 13 digit UNIQUE random in sql procedure?

Advance thanks

View 20 Replies View Related

How To Generate 13 Digit Number From Two Data Colu

Aug 22, 2007

Hi

I have two below datacolumns

'code'- varchar 255 (Unique number) data : chr456Umx
'Packs'- integer data : 6

Is it posible to generate 13 digit number using the above two columns,
The reason is if I run the procedure I will get same 13 digit all the time depending on the above two colums


below is the sample procedure I am using


CREATE PROCEDURE AMZSelCen
@imglink nvarchar(255)

AS

Select code as sku,
PdtBarCode as [standard-product-id],
'EAN' as [product-id-type],

--generate 13 digit number

make+' '+model+' ' +', Price for '+cast(NumPacks as varchar(8)) +' '+'Packs' as title,
make as manufacturer,'

from tablename
where ......

Advance thanks

View 3 Replies View Related

Digit Suffix To JOINED (but Same) Column Names

Mar 20, 2008

Using SQL Analyzer or .NET 2.0 programmatically, is there a way to get Transact SQL 2000 to not append a digit at the end of an identifier name when the result set has the same identifier multiple times (from a JOINed SELECT to various tables which have names in common and where I need to get all column names/values in one fell swoop)?

SELECT A.*, B.* from TableA A
INNER JOIN TableB B on (A.Name = B.Name)

The result set will have NAME1 and NAME2, of course (or perhaps it's NAME and NAME1 -- no matter -- I wish to get rid of the numeric suffic).

Thanks much in advance.

View 3 Replies View Related

Variable To Hold Two Digit Month In A Expression

Mar 27, 2007

I'm using a file Task to rename files to include a Month and Year in the FIle. Part of the requirement in the file name is a two digit month. I have the following query built which will return a two digit month regardless if it's a one or two digit month.



SELECT REPLACE(STR(MONTH(DATEADD("Month", -1,GETDATE())), 2), ' ', '0')



This works perfect when running in T-SQL, but when attempting to define a variable in the expression

REPLACE(STR(MONTH(DATEADD("Month", -1,GETDATE())), 2), ' ', '0') expression keeps failing the evaluation?



Anyone run into a simliar issue or have a suggested work around?

View 7 Replies View Related

Two Digit Year Format - Loading Data

Apr 20, 2006

I have run into something really annoying.
If I run the following SQL into SQL Server via SSMS it will load "31-DEC-49" as 2049-12-31.
INSERT
INTO Stg_StockProductCostPrice
VALUES (0,9999,0.99,'15-NOV-75','31-DEC-49')

But if I have the same data in a file and load it via SSIS it is loading into the database as 1949-12-31, instead of 2049-12-31.

I know there is a property called "Two Digit Year Cutoff" against the server. It is defaulted to 2049. Is there anything similar within SSIS?

Thanks
Sutha

View 3 Replies View Related

Transact SQL :: How To Get 8 Digit Unique Number In Server

May 19, 2015

How to Generate the Unique 8 Digit Number in SQL SERVER.And also am trying with below methods which is not working as expected.

Methode-1:
SELECT CONVERT(VARCHAR(5),GETDATE(),112) +CONVERT(VARCHAR,DATEPART(MS,GETDATE())) AS '8DigitUniqNum'

Methode-2:
DECLARE @UniqueID uniqueidentifier
SET @UniqueID = NEWID()
SELECT LEFT(@UniqueID,8) AS '8DigitUniqNum'

View 9 Replies View Related

SQL Server 2014 :: Convert 6 Digit Letter To Month And Year

Feb 27, 2015

How to convert 6 digit number to mm/yyyy.

View 5 Replies View Related

Data Access :: How To Check All Connection Automatically During Routine Check By Using Batch File

May 20, 2015

I have multiple ODBC connection and how to check all connection automatically during routine check by using batch file.

View 5 Replies View Related

Conversion Of Date From Legacy Systems With 7 And 6 Digit Format To DD/MM/YYYY Format

Nov 19, 2014

We are migrating data from old DB2 systems to sql server 2012, the DATE FORMAT in those systems is in decimal format with 7 digits. CYYMMDD format.

I need to convert this into DD/MM/YYYY format.

View 9 Replies View Related

Where To Do The Calculation In VB.NET Or In SQL?

May 4, 2008



Hi,

My predicament is - where do I do these calculations - in my vb.net code or in an SQL stored procedure?

My manager has handed me a task of converting an excel file she uses in to a web aplication.

While it has been easy to devise what should be the screens and how to capture data, I am struggling over how to code the calculations.


The calculations in excel are pretty simple. These are just sequential calculations (about a 150 calculation for average 500 rows). Mathametical operations include sum, average, max min - regular excel stuff. Some calculations involve vlookup (equvalent to calculation based on value derived from a reference table).


So I am stil wondering - where do I do these calculations - in my vb.net code or in an SQL stored procedure?


Since these calculations are required a produce a result in an online environment, what will be faster?


I tried to do a proof of concept by creating a sample calculation in a .NET class and an in a stored procedure. The choice is still not clear. SQL code execution time was not bad. But SQL code tended to be very messy.VB.net code seemed to be a little slow. But seemed a more organised to look at.


Any views that you can offer will be very helpful.

Thanks in advance.

PMA

View 6 Replies View Related

Help W/ Calculation

Aug 3, 2007

I need to calculate the overall GPA for a student in a particular class.


YEAR SCHOOL STUDENT IDENT GRADE TEACHER CLASS GPA
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 1.6700
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.3300
2007 Snow Canyon High Student1 321649 10 Teacher1 Earth Systems 3.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.0000
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 0.6700
2007 Snow Canyon High Student1 321649 10 Teacher2 Elementary Algebra 1.0000


The problem I'm having is that a student may not taken the class for four terms (as in the Elementary Algebra example above). So I can't hard code it to sum the gpa and divide by 4; it needs to be the number of terms the student took the class.


Here's my sql:


select
trnscrpt.schyear as [Year],
school.schname as School,
rtrim(stugrp_active.lastname) + ', ' + rtrim(stugrp_active.firstname) as Student,
trnscrpt.suniq as suniq,
stugrp_active.graden as Grade,
trnscrpt.teachname as Teacher,
trnscrpt.descript as Class,
gpamarks.gpavallvl0 AS GPA

from
dbo.trnscrpt
inner join dbo.stugrp_active on trnscrpt.suniq = stugrp_active.suniq INNER JOIN
school ON stugrp_active.schoolc = school.schoolc INNER JOIN
gpamarks ON trnscrpt.marksetc1 = gpamarks.marksetc AND trnscrpt.markawd1 = gpamarks.mark

where
trnscrpt.graden >= 6 and
trnscrpt.markawd1 not in ('NC','NG','P','W','WA','WF','WI','WP') and
trnscrpt.subjectc in ('LA', 'MA', 'CP', 'CB') and
trnscrpt.schyear = 2007 and
stugrp_active.schoolc = 725

order by
school.schname,
student,
grade,
class

View 3 Replies View Related

MDX Calculation Help

May 28, 2008

Dimensions:

DimPeriod/Year-Quarter-Month
DimProduct/Category-Product
Fact:
FactInventory/PeriodKey-ProductKey-StatusCode


Period = 1
Product = 1
Status Code = InStock


Period = 1
Product = 2
Status Code = InStock



Period = 2
Product = 1
Status Code = OutOfStock

Period = 2
Product = 2
Status Code = InStock


In period = 2, status code change from InStock to OutOfStock: Product 1 (Count=1)
In period = 1, number of products with status code = InStock: product 1 and product 2 (Count=2)


The measure = 1 / 2 or 50%. TIA

View 2 Replies View Related

Calculation

Oct 9, 2007



This is a smple data in table1

sector RefDate price
pharm 22 august 2007 100.21
gap 15 august 2007 10.32
pharm 21 august 2007 99.99
pharm 9 oct 2007 100.99
pharm 2 oct 2007 98.34
pharm 8 oct 2007 96.34
...

I would like to have the result as follows:
sector RefDate price priceChangeSinceYesterday priceChangeSinceLastWeek priceChangeSinceLastMonth
pharm 9 oct 2007 100.99 100.99-96.34 100.99-98.34 100.99-lastmonth's price value

select
sector,
RefDate,
price,
priceChangeSinceYesterday??,
priceChangeSinceLastWeek???,
priceChangeSinceLastMonth??
from
table1

thanks

View 3 Replies View Related

Calculation

Apr 2, 2008



My aim is to do something like what I have explained below and I was planning on building this logic at the Database level only rather than on the frontend code.

There are certain allocations(transactions) that happen on a periodic basis and I am storing these transactions in the PurchaseTranMaster and PurchaseTranDetail table. These transactions are categorized as 'Main' type and the amount could be allocated for one or many categories in a single transaction. Below is how it will be saved in the 2 table


PurchaseTranMaster


TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 1 9-March-2008 Main


PurchaseTranDetail



TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C


Now what happens is users of my application can make purchases under all these categories only until the Balance under these categories is > than purchase amount. The Balance is calculated as sum of all transactions. It means that w.r.t the above data the balances for each category is(this is not stored in the database)

A 1300
B 3000
C 400

So lets say a user does make a purchase(Trantype is 'SUB') of 300 under A and 400 under B in a single transaction. The data would then be stored in the tables as


PurchaseTranMaster


TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 19-March-2008 Main
4 20-March-2008 SUB


PurchaseTranDetail


TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
4 300 A 1
4 400 B 1


In the PurchaseTranDetail the Debit_TranId value means that the amount has been marked against the TranID 1. This TranId is not handpicked by the user and the system should allocate it accordingly based on the amount available for a particualar category for a Main Transaction. It means that before TranId 4 was saved in the database then the system would first check whether the Total available balance for A >=300 and B>=400 (in our example above it is 1300 and 3000 resp)
Then if the Balance is > than the puchase amount then the allocation would be done by the system and this would be done against the TranID whose TranDate was the earliest, so thats why the Debit_TranId column has 1 as TranId 1 was the earliest.so logically now the balance for the categories would be (this is not saved in the database)

A 1000
B 2600
C 400

So next time again when a user would make a purchase(transaction) under A for 800 and under B for 1000 then if the balance is greater than the purchase amount(which in this case it is) the allocation would happen according to the earliest TranId and this time amount would be partly marked against TranId 1 , TranID 2 and TranID 3. The data would look like this


PurchaseTranMaster


TranID TranDate TranType
1 14-March-2008 Main
2 17-March-2008 Main
3 19-March-2008 Main
4 20-March-2008 SUB
5 21-March-2008 SUB



TranID Amount Category Debit_TranId
1 1000 A
1 1000 B
2 2000 B
3 300 A
3 400 C
4 300 A 1
4 400 B 1
5 700 A 1
5 100 A 3
5 600 B 1
5 400 B 2


I need to do the above taking into consideration that there could be multiple users making purchases(concurrency).
Also I was building my logic on doing the above whether to use cursors or loops. I just need to know how do I write my stored procedure and what would be the most efficeint way of doing the above.

The design for creating the above sample tables is below

/*CREATE TABLE PurchaseTranMaster
(
TranID int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
TranDate Datetime,
TranType varchar(30)
)


CREATE TABLE PurchaseTranDetail
(
TranID int,
Amount int,
Category Varchar(20),
Debit_TranId int
)

insert into PurchaseTranMaster values(convert(datetime,' 14-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 17-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 19-March-2008',103),'Main')
insert into PurchaseTranMaster values(convert(datetime,' 20-March-2008',103),'SUB')
insert into PurchaseTranMaster values(convert(datetime,' 21-March-2008',103),'SUB')


insert into PurchaseTranDetail values(1,1000,'A',0)
insert into PurchaseTranDetail values(1,1000,'B',0)
insert into PurchaseTranDetail values(2,2000,'B',0)
insert into PurchaseTranDetail values(3,300,'A',0)
insert into PurchaseTranDetail values(3,400,'C',0)
insert into PurchaseTranDetail values(4,300,'A',1)
insert into PurchaseTranDetail values(4,400,'B',1)
insert into PurchaseTranDetail values(5,700,'A',1)
insert into PurchaseTranDetail values(5,100,'A',3)
insert into PurchaseTranDetail values(5,600,'B',1)
insert into PurchaseTranDetail values(5,400,'B',2)*/

View 5 Replies View Related

YTD Calculation

Feb 19, 2008

I´m working on SSRS 2005 trying to calculate YTD for the total sale. This is what I got so far:





Code Snippet

WITH MEMBER [Measures].[YTD Amount] AS
'SUM(PeriodsToDate([DATE].[Year]),[Amount])'
SELECT
{[Measures].[Amount],[Measures].[YTD Amount]} ON COLUMNS,
[DATE].[Month].Members ON ROWS
FROM [SKY]
My Date hierarcy is:
Year
Month

When running this query all I get is very small number for Amount column, almost zero for all months and the YTD Amount Column is only showing (null).

View 11 Replies View Related







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