July 3, 2011 at 11:32 pm
Hi frds,
I want Difference between Sum of Current reporting period amount value and SUM previous reporting period amount value
Here quary like this
SELECT T1.sm as ID,
DATEADD(s,0,DATEADD(dd, DATEDIFF(d,0,getdate()),0)) as Curreny_Reporting_Period,
SUM(t2.amount) AS Current_Reporting_amount,
DATEADD(s,0,DATEADD(dd, DATEDIFF(d,0,getdate()-1),0)) as Previous_Reporting_Period,
SUM(t2.amount) AS Previous_Reporting_amount,
Min (SUM(t2.amount) getdate()) and (SUM(t2.amount)(getdate()-1)))
FROM t1
INNER JOIN t2
ON t1.nr=t2.nr
Group BY t1.sm,t2.amount,t1.last_modified
Plz fds let me guide me Adv tkq....
July 4, 2011 at 2:06 am
You could try something like this:
SELECT
T1.sm as ID,
CAST(t1.last_modified) AS DATE) as Curreny_Reporting_Period,
SUM(t1.amount) AS Current_Reporting_amount,
CAST(t2.last_modified AS DATE) as Previous_Reporting_Period,
SUM(t2.amount) AS Previous_Reporting_amount,
SUM(t1.amount) - ISNULL(SUM(t2.amount),0) as Amount_Difference
FROM t1
LEFT OUTER JOIN t2
ON t1.nr = t2.nr
AND CAST(t2.last_modified) AS DATE) = CAST(DATEADD(d,-1,t1.last_modified) AS DATE)
Group BY t1.sm,t1.last_modified, t2.last_modified
In SQL 2008, CAST AS DATE will perform the same rounding-off function as your calculation.
The key to get your query working, is to include the date difference in the JOIN conditions.
This might not be the best way to do it, but it should work.
July 4, 2011 at 2:06 am
Please provide table def and sample data in a ready to use format as described in the first link in my signature.
Also, please include your expected result based on your sample data.
July 4, 2011 at 2:43 am
Hi pieterlu,
There is such column Amount in t2......Amount column is in only t1
I need variance between Current and previous reporting day amount
July 4, 2011 at 3:01 am
Sorry, I was making assumptions, and we all know where that leads...
In my example t1 and t2 are aliases to the same table, but the principle can be adapted to use a dataset or subquery, instead of a table.
Alternatively, you could perhaps retain your query structure of
FROM t1
INNER JOIN t2
and add another join to t2 as
LEFT OUTER JOIN t2 AS t21
Examples of your table structures, and data (as stated by LutzM) would however be helpful, in order to give you a more specific reply.
July 4, 2011 at 3:35 am
Hi pieterlu,
Here is the quary,
SELECT t_facility.source_system as System_ID,
DATEADD(s,0,DATEADD(dd, DATEDIFF(d,0,getdate()),0)) as Curreny_Reporting_Period,
SUM(t_facility_valuation.amount) AS Current_Actual_currency,
DATEADD(s,0,DATEADD(dd, DATEDIFF(d,0,getdate()-1),0)) as Previous_Reporting_Period,
SUM(t_facility_valuation.amount) AS Previous_Actual_currency
I need variance between Current_Actual_currency - Previous_Actual_currencyFROM t_facility_valuation
INNER JOIN t_facility
ON t_facility.facility_nr=t_facility_valuation.facility_nr
Group BY t_facility.source_system,t_facility_valuation.amount,t_facility.last_modified
July 4, 2011 at 3:54 am
Not exactly what I had in mind...
Is there a date column in your t_facility_valuation table, and would it contain yesterday and today's dates?
July 4, 2011 at 5:54 am
If my assumption is correct, the following code should work...
SELECT t_facility.source_system as System_ID,t_facility.last_modified,t_facility_valuation.amount
INTO #TEMP
FROM t_facility_valuation
INNER JOIN t_facility ON t_facility.facility_nr=t_facility_valuation.facility_nr
SELECT C.System_ID,C.last_modified
, SUM(C.amount) CurrentAmount
, SUM(P.amount) PreviousAmount
,SUM(C.amount)-SUM(P.amount) VarianceAmount
FROM #TEMP C
INNER JOIN #TEMP P ON P.System_ID=C.System_ID AND P.last_modified= C.DATEADD(d,-1,last_modified)
Group BY C.System_ID,C.last_modified
Note:
I assume last modified date as DATE only (not DATE TIME). If it is DATETIME we have slice the time part before joining.
July 4, 2011 at 6:17 am
What we're [all] still waiting for is ddl / sample data to give you the correct answer.
SELECT SUM(CASE WHEN DateCol = DATEADD(D, 0, DATEDIFF(D, 0, GETDATE())) THEN Value ELSE 0 END) AS Today
SUM(CASE WHEN DateCol = DATEADD(D, -1, DATEDIFF(D, 0, GETDATE())) THEN Value ELSE 0 END) AS Yesterday
FROM Table WHERE Date BETWEEN @Start AND @End
Of course this won't work correctly because I have nothing to test it with. I'm betting you'll have to do the dateadd trick on both sides of the equation or use a between instead just for clarity!
July 4, 2011 at 6:56 am
Hi frd's,
here the tables
In table t1
columns : sys_id (char)= abc , abc
product_code (char)=0.000 , 0.000
last_modified (date_field)=2011-04-20 00:03:35.247 , 2011-04-20 00:03:35.247
deal_id : sg123 , sg124
In table t2
Columns:gl_balance -54644.2400 , 2070495.0000
deal_id : sg123 , sg 124
Now my requirement is :
The data is grouped by Sys_ID and Product_Code to calculate, Find the sum of GL_Balance for respective Reporting date & previous Reporting date. Then find the cumulative value of the Gross GL Balance in between the current reporting day and the previous reporting dayMy code is like this
nw i need is sum of GL_Balance for respective Reporting date & previous Reporting date
I codded like this:
SELECT t1.Sys_ID,t1.Product_Code,
DATEADD(s,0,DATEADD(dd, DATEDIFF(d,0,getdate()),0)) As Current_reporting_Period,
SUM(t2.gl_balance) AS CurrentGL_Balance,
DATEADD(s,0,DATEADD(dd, DATEDIFF(d,0,getdate()-1),0)) As Previous_reporting_Period,
SUM(t2.gl_balance) AS PreviousGL_Balance
FROM t1
INNER JOIN t2
ON t1.deal_id=t2.deal_id
GROUP BY t1.sys_id,t1.product_code,t1.last_modified
adv tkq,
July 4, 2011 at 6:58 am
See my sample code and adapt it.
CASE(SUM...) will work perfectly for you. If you want a fully tested solution post the ddl, sample data (insert statements) and required output from that data.
Good luck!.. not that you need it.
July 4, 2011 at 8:06 am
bhargava.sumansiram (7/4/2011)
Hi frd's,here the tables
In table t1
columns : sys_id (char)= abc , abc
product_code (char)=0.000 , 0.000
last_modified (date_field)=2011-04-20 00:03:35.247 , 2011-04-20 00:03:35.247
deal_id : sg123 , sg124
In table t2
Columns:gl_balance -54644.2400 , 2070495.0000
deal_id : sg123 , sg 124
Now my requirement is :
The data is grouped by Sys_ID and Product_Code to calculate, Find the sum of GL_Balance for respective Reporting date & previous Reporting date. Then find the cumulative value of the Gross GL Balance in between the current reporting day and the previous reporting dayMy code is like this
nw i need is sum of GL_Balance for respective Reporting date & previous Reporting date
I codded like this:
SELECT t1.Sys_ID,t1.Product_Code,
DATEADD(s,0,DATEADD(dd, DATEDIFF(d,0,getdate()),0)) As Current_reporting_Period,
SUM(t2.gl_balance) AS CurrentGL_Balance,
DATEADD(s,0,DATEADD(dd, DATEDIFF(d,0,getdate()-1),0)) As Previous_reporting_Period,
SUM(t2.gl_balance) AS PreviousGL_Balance
FROM t1
INNER JOIN t2
ON t1.deal_id=t2.deal_id
GROUP BY t1.sys_id,t1.product_code,t1.last_modified
adv tkq,
Please read the article at the first link in my signature line below for future posts. You'll get much better, tested answers much more quickly. 😉
--Jeff Moden
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply