Date Calculation
Nov 12, 1999Hi All!
I need a query to find all dates from today to one-year back.
If I start from today day I need find all dates until 11/12/98.
Thanks a lot.
Greg.
ADVERTISEMENT
Date Calculation
Oct 12, 2004I need to create a user defined function to calculation the difference between today and a future date. The result needs to be in days, hours, and minutes formatted as per the following example: 1d / 4h / 30m. I have a moderate level of SQL exprience. however, I would appreciate some expert advice as the best way to approach this.
View 4 Replies View RelatedDate Calculation
Mar 14, 2001hello everyone,
I have a problem of calculating a date, for example, how do i find out the begining date of the week and ending date of the week for certain date,
and how do i find out the beginning date of the month and end date of the month for a certain date,
thanks
SQl Date Calculation
Jan 10, 2006I need to use the first day of previous month, can anybody help me with that please?
Thanks in advance!!
Date Calculation
May 20, 2008I want all dates from DATEADD(DAY,-90,GETDATE()).
1/1/2008
1/2/2008
1/3/2008 .....
and so on. This is probably easy, but I can't figure it out. Any help would be appreciated. Thanks.
Date Calculation
Feb 4, 2008
I have an issue with a query.
How to calculate the last day of a previous month
that is for example
2007-09-30 23:59:59 is passed then how to calculate last date of a previous month as 2007-08-31 23:59:59
OR 2007-10-31 23:59:59 as 2007-09-30 23:59:59
Thanks for your Help.
Sri
Help: Date/time In Between And Calculation
Aug 23, 2005I have a problem with date/time:
1. I have data something like this:
start 07.30
end 16.00
How can i count how many hours and minutes from start to end?
2. Another data
start: 20050805 -> August 5, 2005
end: 20050810
How can I return value that when i insert 20050809 it is between start and end, and if 20050811 it will say false that the date is between start and end
thx
Detailed Date Calculation
Jul 25, 2007Hello,
if a customer DOB is 1933-06-30 and a date of service is on 2007-06-21
that makes them 73 and not 74. How do I calculate an accurate years of age?
Thanks in advance.
Really Complex Date Calculation
Jan 8, 2008Hi,
Can someone please guide me how to extract this particular data. It is quite complex, the raw data is not in a good shape and I am not sure if it is even possible. For simplification, I am copying a sample of only 3 columns from my table.
ID FromDate ToDate
01 06/09/2006 06/09/2006
02 06/09/2006 06/12/2006
03 06/09/2006 06/12/2006
04 06/09/2006 06/12/2006
05 06/09/2006 06/12/2006
06 06/09/2006 06/12/2006
07 06/10/2006 06/10/2006
08 06/12/2006 06/12/2006
09 08/30/2006 09/05/2006
10 08/30/2006 09/05/2006
11 08/30/2006 09/05/2006
12 09/13/2006 09/17/2006
13 09/13/2006 09/26/2006
14 09/13/2006 09/26/2006
15 10/05/2006 10/10/2006
16 10/05/2006 10/10/2006
17 10/10/2006 10/15/2006
18 10/11/2006 10/11/2006
19 10/16/2006 10/16/2006
20 10/20/2006 10/22/2006
21 10/23/2006 10/25/2006
From and to date represent a duration of an event, but in some instances the data has multiple lines but they are all to be considered one event. For example all the from dates (row 1 to 8) in Oct 06 are covered by ID/row 05 because 6/9/06 to 6/12/06 is one event, all other rows of June 06 data are just redundant and not needed in the resultset. There isn't a unique event name either so comparing and picking row 05 just based on the above data is an issue.
Another problem is for example the dates in October (10/5 - 10-16) all represent 1 event but the date span is spread all over. Multiple events are also possible in one month, so gettign MIN and MAX based on the month will not work.
The desired result set need to have duration of each unique event for example (1 in June, 1 in August, 1 in Sept, and 1 in Oct) and also need to have the number of events after that date and the calculated from and to date of that event:
ID FromDate ToDate Events after this date NextEventFromDate NextEventToDate
05 06/09/2006 06/12/2006 4 08/30/2006 09/05/2006
09 08/30/2006 09/05/2006 3 0913/2006 09/26/2006
13 09/13/2006 09/26/2006 2 10/05/2006 10/16/2006
? 10/05/2006 10/16/2006 1 10/20/2006 10/25/2006
? 10/20/2006 10/25/2006 0
Urgent Help On Date Calculation
Jan 11, 2007I have a table that hold a couple of fields.
STARTDATE - The day the service starts
FREQTYPE - This field tells me how often a schedule occurs. Here are the vaules -1=oncall, 0=days, 1=weeks, 2=months
FREQPERIOD - This is the number of days, weeks, or months between services. a value of -1 would indicate being oncall.
DayOfWeek, Day of week for week or month-based frequencies 0=sunday 6=saturday -1=if on call
WEEKOFMONTH - week of month for month based frequencies 1-4, value of 5 if it the last week of the month,
So what I need is for this report to look at the start date then figure out what the frequency is (days, weeks, months) then look at the frequency period (how many days are between services) what day of the week it falls on and list each service date on a report. There is an example at the bottom
I have tried numerous things and I can't get it and I need this asap today. I can provide what ever anybody needs, Many thanks.
Here is an example of the output that I need.
Service Frequency Every 12 weeks or 84 Days
Pickup Starting Point 1/4/2007
Pickup Schedule
3/29/2007
6/21/2007
9/13/2007
12/6/2007
2/28/2008
5/22/2008
8/14/2008
11/6/2008
1/29/2009
4/23/2009
7/16/2009
10/8/2009
12/31/2009
Here is sample data with the relationships
http://www.balzoutonline.com/reporting/database.jpg
Here is the service table schema
http://www.balzoutonline.com/reporting/service.jpg
Here is the serviceinfo table schema
http://www.balzoutonline.com/reporting/serviceinfo.jpg
Here is the serviceschedule table schema
http://www.balzoutonline.com/report...iceschedule.jpg
And here is the site table schema
http://www.balzoutonline.com/reporting/site.pdf
Here is something that was created in excel wich is pretty easy but a manual process. This is what I would like to replicate in crystal
http://www.balzoutonline.com/reporting/Schedule.xls
Edit Year To Date Calculation
May 29, 2008Hi -
I created a Year To Date Calculation via the Buisness Intellligence Wizard. The Script is as follows:
( [Acctg Date].[Acctg Time Acctg Date Calculations].[Year to Date],
[Acctg Date].[AcctgYear].[AcctgYear].Members ) =
Aggregate(
{ [Acctg Date].[Acctg Time Acctg Date Calculations].DefaultMember } *
PeriodsToDate(
[Acctg Date].[Acctg Time].[AcctgYear],
[Acctg Date].[Acctg Time].CurrentMember
)
)
Now I'd like to edit it so that it produces "Inception To Date" to date ("Inception To Date" picks up all amounts from the beginning which in my case is 2002, as opposed to Year To Date which is from the beginning of the year).
To change to Inception To Date can I change the PeriodsToDate function to:
PeriodsToDate(
[Acctg Date].[Acctg Time].[2002],
[Acctg Date].[Acctg Time].CurrentMember
)
By the way, I'm very new to SSAS so the code is foreign somewhat foreign at this point.
Thanks,
Mark
Dateadd Date Calculation Anomaly
Apr 3, 2008I am doing monthly reporting for whole months, all starting on the 1st of each month and finishing on the last day of the month
I use dateAdd to calculate the end of the month - so I add 1 month and subtract 1 second (start time is always 00:00:00).
--dFrom is Jan 01 2008 00:00:00
select @dTo=dateadd(ss,-1,dateadd(mm,1, @dFrom))
when I print dTo it is not Jan 31 2008 23:59:59 but Feb 01 2008.
Why is this?
Only starts to behave when I set the number of seconds I am adding from -1 to -31.
Time of date is unimportant: as storing all dates as midday (any data type which ONLY supports date? not interested in the time really).
Subtracting one minute from midnight works fine, but that said: I am curious to understand why I am getting the funny behaviour above.
Edited Version: Very Urgent Date Calculation
Nov 13, 2007Hi, How can i calculate the date out? i am using vb sql server database...db saved my date as dd/MM/yyyy, form display my date as dd/MM/yyyy too
1.I have a selected date by user from calendar in tb_dop.text
2.creditDate.text for user to enter number of days to add to tb_dop.text date
3.dDate.text to display the calculated date Private Sub Calendar1_SelectionChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Calendar1.SelectionChanged
'display selected date from calendar
tb_dop.Text = Calendar1.SelectedDate()
End SubPrivate Sub btn_add2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btn_add2.ClickDim myDate As New DateTime
myDate = tb_dop.Text
Dim i As Integer
i = creditDate.TextDim dDate As New DateTime
dDate = myDate.AddDays(i)
dueDate.Text = dDate
ERROR
The conversion of char data type to smalldatetime data type resulted in an out-of-range smalldatetime value.The statement has been terminated.
I know it is becos diff format of date calculation, there4, how can i change it to calculate in mm/dd/yyyy format? Funny rite? i can saved and display in dd/MM/yyyy but i cant calculate using this format
Note: i cant change my form display format to mm/dd/yyyy cos i need it to be user friendly in my country ty (URGENT)
Once again thanks
Mdx/analysis/reporting Service For Date Calculation
Apr 8, 2008Hi,
I have a cube with the following
DimStartDate
DimEndDate
DimArea
FactDataCount
I need to output the following report
Area 5days 10days 30days
Miami 4 2 1
Memphis 3 3 2
1)Where do I do the dateDiff calculation, at report or cube level?
2)How do I work out which dates belong to the above groups? I'm assuming i have to check if the dateDiff lies between those numbers?
thanks
SQL Server 2012 :: Date Range Calculation
Feb 3, 2015I have some location assignment data that I need to convert. I need to know how long each account spent in a certain location for each month of it's overall startdate/enddate period.
E.g.
Account 1 stayed in USA for 31 days in January, and 15 days in February.
Account 1 stayed in UK for 13 days in February and 26 days in March.
Etc.
create table #temp(account int, loc varchar(10), startdate datetime, enddate datetime)
insert into #temp select 1,'USA','2014-01-01','2014-02-15'
insert into #temp select 1,'UK','2014-02-16','2014-03-26'
insert into #temp select 1,'AU','2014-03-27','2014-06-07'
insert into #temp select 2,'UK','2014-08-15','2014-09-01'
insert into #temp select 2,'AU','2014-09-02','2014-10-17'
select * from #temp
drop table #temp
Help Needed For Date Field (Age) Calculation In SQl Query
Aug 30, 2007Hi experts,I am working on SQL server 2005 reporting services and i am getting aproblem in writting a query.Situation is given below.There is one table in database Named ChildNow i have to find the All childrens whoes Age is 13 years Base onSome given parameter.If User select Augus 2007 then It has to calculate the Childs who bornin August 1994 And if he select September Then queryshould show only those child Who born in September 1994 and soon..... And use can select another year month also likeAugust 2009 ...I am writting the following querySelect Child_Name, DOb from Childwhere ((CONVERT(DateTime, A.Date_Of_Birth, 103) >= @ Parameter1And (CONVERT(DateTime, A.Date_Of_Birth, 103) <= @Parameter2)If i know already month and year then i can write easily parameter1and parameter2 But since these are comming from user so i m notfinding how to handle this.Now please suggest me what i have to write in Where statement I thinka lot but not getting any idea about it.Any help wil be appriciated.RegardsDinesh
View 2 Replies View RelatedPower Pivot :: How To Do Nested Date Calculation With DAX
Aug 9, 2015I have a CSV file that looks like below
JobOrderId CandidateId From_StatusID From_StatusDate To_StatusID
1234 5000 1 07/01/2015 2
1234 5000 2 08/01/2015
I've read this file in PowerPivot and I need to calculate the time in weeks between the two dates for the same JO, Candidate and when the to_StatusId = From_StatusID. In other words, the number of weeks that took from going From Status "1" to Status "2".
Is it possible to do something like this using DAX? Do I need to create a calculated Column?
Power Pivot :: Prior Month To Date Calculation With DAX
Feb 9, 2013I’m using DAX to calculate the prior MTD count of a specific column. My data ends on 2/8/2013 and that day's PriorMTD is incorrectly corresponding to 1/31/2013. Whereas, the previous 7 days in February correctly match their corresponding January dates..Below is an image of my pivot table and I have outlined the values in red that are in question.Below are my DAX formulas used each column visible in my image:
Distinct Count of Events:=DISTINCTCOUNT([EventID])CurrentMTD:=CALCULATE([Distinct Count of Events], DATESMTD(Events[EventDate]), ALL (dimDate) )PriorMTD:=CALCULATE([Distinct Count of Events], DATEADD(DATESMTD(Events[EventDate]), -1, MONTH), all(dimDate)) ParallelMonth:= CALCULATE ([Distinct Count of Events], ParallelPeriod(Events[EventDate], -1, MONTH), ALL(dimDate))
Power Pivot :: Budget Month-to-date Calculation
Aug 19, 2015I have a fact table with sales data at line item level, meaning lots of rows with sales orders, revenue etc. per product group pr month. Then I have a budget table with the budgetted revenue pr product group pr month, meaning a lot less detailed. I followed the method at daxpatterns.com/budget-patterns/Â with the headline "Complete pattern" to make it work. Now I have a working data model where I can see actual and budget as seperate values.
I then need to calculate the actual revenue month to date and the budgetted revenue month to date. For actual it has worked fine by doing:
=CALCULATE(
sum(Sales[Revenue]);
(DatesMTD(Time[date]))
)
The problem occurs for me doing the same on the budget figure. Since I don't have a budget per day, but only at "YearMonth" level, I have calculated the amount of workdays per month and then used the DatesMTD formula to get the cumulative workdays during the month. Furthermore I calculated the total amount of workdays in the month and made a ratio between this figure and the cumulative workdays so I get a figure in percent that tells me per day how much of the month that has went by (0-100 %). I would then multiply this percentage with the budget and get the budget month to date. This does not work since my budget figure is not at day level.
How I can get it to work. My desired result will be all the dates during a month in my rows and then actual revenue Month to date in values and a corrosponding figure for the budget.
Transact SQL :: Date Calculation - 7 Days Prior To Specified Day Of Last Week
Sep 8, 2015I need to run a select on Mondays to pull data for 7 days prior to the Thursday of last week; i.e. Friday - Thursday inclusive. I'm sure this is simple, but I work with dates so infrequently that I need a refressher.Â
View 7 Replies View RelatedSQL Server 2012 :: Day Wise And Date Range Calculation With Looping Or Dynamic Data?
May 14, 2015I am using Sql Server 2012.
This is how I calculate the ratio of failures in an order:
31 Days Table 1 query
sum(CASE
WHEN (datediff(dd,serDATE,'2015-01-21')) >= 31 THEN 31
WHEN (datediff(dd,serDATE,'2015-01-21')) < 0 THEN 0
ELSE (datediff(dd,serDATE,'2015-01-21'))END) as 31days1 .
How do i loop and pass dates dynamically in the Datediff?
31 Failures Table 2 query
SUM(Case when sometable.FAILUREDATE BETWEEN dateadd(DAY,-31,CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102))
AND CONVERT(DATETIME, '2015-01-21 23:59:00.0', 102)Then 1 Else 0 END) As Failures31,31 Day Cal(Formula) combining both Table 1 and Table 2
((365*(Convert(decimal (8,1),T2.Failures31)/T1.31day))) [31dayCal]This works fine when done for a specific order.
I want a similar kind of calculation done for day wise and month wise.
2. what approach should I be using to achieve day wise and month wise calculation?
I do also have a table called Calender with the list of dates that i can use.
Analysis :: SSAS Calculation With Division Combined With A Time Calculation?
Sep 17, 2015I 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]);
Converting Oracle Calculation To Sql Server 2005 Calculation
Jul 19, 2007Hi 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
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
Help W/ Calculation
Aug 3, 2007I 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
MDX Calculation Help
May 28, 2008Dimensions:
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
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
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)*/
YTD Calculation
Feb 19, 2008I´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).
Query With A Calculation
Mar 1, 2007Hello Friends
I have 3 tables
1) Product Id, ShortName
2) IncomingStockId, ProductId, Quantity, InDate
3) OutGoingStock Id, OutDate, ProductId, Quantity
I need to get the results like thisProduct name, quantity in stock today
the "quantity in stock today" = sum (quantity recieved) -sum (quantity sent)
Thank you for your timeSara
Calculation Of Values
Mar 3, 2008Hi.
i have the code :
cmd = New SqlCommand("SELECT sales,country,year FROM salesTable WHERE (country = " & (CountryBlk) & " AND branch = " & (NameSnif) & " AND datepart(yyyy,year)=" & (YearBlk) & ") order by datepart(mm,year) ", cnn) cnn.Open() rdr = cmd.ExecuteReader( _ CommandBehavior.CloseConnection)
While x < 12 If rdr.Read = Nothing Then Exit While End If varcount(x) = rdr("sales")
TempMonth = rdr("year") MonthNumber(x)=datepart(DateInterval.Month,TempMonth) x = x + 1 End While
i want to calculate and put into the varcount(x) value all sales of the same month
thanks
Bandwidth Calculation
Aug 18, 2003I have scenerio that find out the Bandwitdh size between clint and server.
I wanted find out howmuch size of data recieveing from server at a time.
Any advice regarding this.
Thanks,
Ravi
Age Calculation For Less Than 1 Year?
Oct 7, 2003HI,
I have the below logic for age calcuation for more than a year.
But I need age calculation for lessthan year.
Note for MAK: As per our previous post that day calucations didn't work.
DECLARE @birthday datetime, @d datetime
SELECT @birthday = '12/31/1998', @d = '12/30/1999'
SELECT datediff(yy, @birthday, @d) -
(case WHEN (datepart(m, @birthday) > datepart(m, @d)) OR
(datepart(m, @birthday) = datepart(m, @d) AND
datepart(d, @birthday) > datepart(d, @d))
THEN 1
ELSE 0
end) AS Age1