SQL Server 2012 :: Running Totals For Previous X Days?
May 13, 2015
get the desired results for the following sample data set. I was able to come up with a query that returns the the expected results however only for a given day, so I'd need to union several select statements the get the desired results which is definitely not ideal. I'd like to pass a parameter in (number of days) instead of doing a unions for each select.
DECLARE @T TABLE (Id INT, Category VARCHAR(1), [Date] DATE)
INSERT INTO @T
SELECT 1 AS Id, 'A' AS Category, '2015-5-13' AS ActivationDate UNION ALL
SELECT 1, 'A', NULL UNION ALL
SELECT 1, 'A', '2015-5-13' UNION ALL
SELECT 1, 'A', NULL UNION ALL
[code]....
View 9 Replies
ADVERTISEMENT
May 28, 2015
A while back, a "quirky update" method was proposed for lightning fast running totals based on the three-part MSSQL UPDATE's SET statement and tally tables. However, some claimed this was not 100% absolutely guaranteed behavior.
How does the new OVER clause compare in terms of performance ?
DECLARE @Tbl TABLE
(
pk int not null primary key identity,
N int
)
INSERT INTO @Tbl (N) SELECT TOP 1000 1 FROM syscolumns a CROSS JOIN syscolumns b
SELECT pk, SUM(pk) OVER (ORDER BY pk )
FROM @Tbl
View 9 Replies
View Related
Feb 27, 2008
Hi all - am developing my client statement report on my accounts application...
My accounts team need the ability to print off monthly statements for clients. I have done this no probs. I end up with a table of invoices (invoiceid, invoicedate, amount) in my report table. At the bottom of this table, they require the following table:
Current: £amount
30-60 Days: £amount
60-90 Days: £amount
90 + Days: £amount
basically, Say I print the report on 15th Feb. The Current value should be a sum of invoice totals from February. 30-60 Days should be a sum of invoices which were raised in Dec and Jan. 60 - 90 Days should be a sum of invoices raised between Oct and Nov. 90 Days should be a sum of invoices raised before Oct.
I'm assuming I grab the current month from Globals!ExecutionTime but where would I go from there?
Thanks in advance as usual guys :)
Stephen.
View 9 Replies
View Related
Jul 13, 2005
On a webform, I have three button ... [7 days] [15 days] [30 days]When the user clicks one of the buttons, I want to return their orders for the past X days. The WHERE clause would include something like this (for 7 days):WHERE (Order_Date BETWEEN CONVERT(DATETIME, GETDATE() - 7, 102) AND CONVERT(DATETIME, GETDATE(), 102))How do I parameterize the number of days?Thanks,Tim
View 2 Replies
View Related
Jun 3, 2015
We carried out an in-place upgrade on our production server on Saturday - going from 2008 R2 to 2014.
We had tested this method out in dev/test and pre-production with only minor post issues to fix.
However, on production we had an issue whereby checkdb was hitting 100% CPU and caused overnight processes to hang. The checkdb statement was terminated and disabled by a colleague at 1 am.
Since then we have restored this database to a dev server and ran checkdb against it with no_infomsgs and all_errormsgs but it still hasn't finished since Monday morning!
The database is just over 800 GB and whilst checkdb was crippling the cpu, logical reads are less than one. However, sp_whoisactive is showing that it has done 56 million reads so far and this number increases periodically so it looks like it keeps going back to re-check the database with a deep dive.
Also, on a different environment, we ran check table statements and one of them took over 9 hours for a single table but came back clean (see attachment).
We need to wait for the output but the database is still in use in production and the mess will just get worse if it is indeed corrupted.
View 5 Replies
View Related
Oct 13, 2015
I am trying to write a query to calculate the running difference between data on different dates. Below is what my table of data looks like. Basically I want to calculate the difference between the total_completed for each state and date.
DateStatesTotal_Completed
08/27/15CA 19,952
09/11/15CA 26,336
10/02/15CA 35,444
10/08/15CA 38,278
08/27/15CO2797
09/11/15CO3264
10/02/15CO4270
10/08/15CO4297
below is what I am trying to achieve:
DateStatesTotal_CompletedCompleted_Difference
08/27/15CA 19,952 0
09/11/15CA 26,336 6,384
10/02/15CA 35,444 9,108
10/08/15CA 38,278 2,834
08/27/15CO27970
09/11/15CO3264467
10/02/15CO42701,006
10/08/15CO429727
below is my code (I almost have what I need) I just can't figure out how show 0 as the completed_difference for the first Date for each state since there is no prior date to calculate against.
MRR_TOTALS_WEEK_OVER_WEEK AS
(
SELECT
T1.[Date]
,T1.States
,T2.Total_Completed
,ROW_NUMBER() OVER(PARTITION BY T1.States ORDER BY T1.States,T1.[Date]) AS ORDERING
FROM TOTAL_CHARTS T1
LEFT JOIN TOTAL_COMPLETED T2 ON T1.[Date] = T2.[Date] AND T1.States = T2.States
)
[code].....
View 4 Replies
View Related
Dec 10, 2007
May I know how to use a "date" to select out previous 14 days record from the table? and find the duplicated records?
-- sort out duplicate order from tblOrder
Select * FROM tblOrder
WHERE DDay > @prmDDay("day", -14, getDate())
Group by DDay
Many thanks~~~~~
Fr New Learner
View 3 Replies
View Related
Jul 20, 2015
I have a table with date in the following format:
orderID | dateOrdered | customerIDÂ
101 | 01/01/2015 | 1
102 | 02/02/2015 | 2
103 | 05/06/2015 | 3Â
I need to create a measure that tells me how many customer's placed an order in the previous 91 days of any given date, not necessarily today.Â
So, if you imagine a PivotTable which has a series of dates down the left hand side and 'totalNoCustomersLast60Days' as the only column, I need each day to tell me how many customers ordered in the x number of days.
For example if we say 60 days my table would look like the following:
Each row in this table would be telling me that "x number of customers placed orders within the period 60 days prior to,and including, the date on the left".
I have tried numerous ways of doing this and despite thinking it should be incredible simple it eludes me.
View 12 Replies
View Related
Oct 1, 1999
I have a situation where I want to get running totals of a potentionally very large table. Say I have a million records with a table with few fields.
Table structure like this
UID value
1 3
2 9
3 4
4 7
5 2
I want to return a result set of something like this
select uid, value, (rtotal???) from table
uid value rtotal
1 3 3
2 9 12
3 4 16
4 7 23
5 2 25
This is to be used for sort of a lotery. Say I have 1 million users with a variety of points tward the lotery. I total the points, is say 5 million, determined
the winner to be say 3,234,563 Now I have to determine which uid is the winner. So I planned to to do a running total till the winning value
is less then or equal to the running total and I have my winner. I am looking for a fast way to do this on a large database.
Any Ideas?
View 1 Replies
View Related
Jan 12, 2004
Hi Folks,
I have a problem that I know that i should be able to code up but have drawn a blank due to it being monday. Anyway......
Have a table :
create table test_registrations
(
date_maint smalldatetime,
user_name1 varchar (255),
user_address1 varchar (255),
total_users int
)
go
If i have a number of registrations on a particular date then I can tell all how many users have registered in any date by :
select date_maint , count (1)
from test_registrations
group by date_maint
order by date_maint desc
go
The qestion is how can I keep a total registared users count. Say if I have 10 users join on the 1st of Jan and 15 on the 3rd then I want
the total users for the users on 1st to read 10 and total users on the 3rd to read 25.
I know i should be able to code this up but I'm being a dumb ass. Can someone show me a way to code it. Is it some sort of correlated sub query to keep a running total ?
View 3 Replies
View Related
May 23, 2008
On SQL Server 2005 at least, this works efficiently where we have an indexed row number.
It does seem to be very sensitive to the join condition in the recursive part of the CTE: changing it to the equivalent "ON T.rn - 1 = RT.rn" results in a scan of T each time instead of a seek!
DROP TABLE dbo.T
-- rn must have contiguous values for this to work:
CREATE TABLE dbo.T (rn int PRIMARY KEY, f float NOT NULL)
-- 100000 random floats between 0 and 1:
INSERT INTO dbo.T
SELECT n+1 AS rn, RAND(CAST(NEWID() AS binary(4))) AS f
FROM dbo.Numbers
GO
;WITH RT AS (
SELECT rn, f AS rt
FROM dbo.T
WHERE rn = 1
UNION ALL
SELECT T.rn, RT.rt + T.f
FROM RT
INNER JOIN dbo.T AS T
ON T.rn = RT.rn + 1
)
SELECT *
INTO dbo.TRT
FROM RT
--ORDER BY rn
OPTION (MAXRECURSION 0)
SQL Server parse and compile time:
CPU time = 0 ms, elapsed time = 1 ms.
Table 'Worktable'. Scan count 2, logical reads 600001, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'T'. Scan count 100000, logical reads 200002, physical reads 0,
read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
SQL Server Execution Times:
CPU time = 3500 ms, elapsed time = 3724 ms.
(100000 row(s) affected)
View 4 Replies
View Related
Jun 11, 2008
Now my code below brings everything i want it too, the problems comes is I need to get a running total of sales for each day. Currently it shows the sales for a store for each day and what there projections should be. I need a running total for each day so if you were to have todays date it would have the sum(sales) between today and the first or the month that im in. but still show what the total was on the 10th, 9th, and so on.
Declare @Brand as varchar(10)
DECLARE @StartDate datetime
Declare @EndDAte as Datetime
Set @Brand = 'business'
SELECT @StartDate=CAST('1/1/'+CAST(YEAR(GETDATE()) AS varchar(4)) AS datetime)
SET @EndDate =CAST('12/31/'+CAST(YEAR(GETDATE()) AS varchar(4)) AS datetime)
Select ttProjection.StoreID,S.StoreName , ttProjection.DailyProjection, ttProjection.DAYS, ISNULL(ttSales.Sales,0) as Sales
From
/**********Finds projection per day****************/
(Select StoreID, Projection, Projection/Cast(DaysInMonth as INT) as DailyProjection, DAYS
From
(Select StoreID, Projection as Projection,
Month, Day(DateAdd(m, 1,DateAdd(d,1 - Day(Month), Month))-1) As DaysInMonth
From Reporting.dbo.RetailSalesComparison_ProjectionsView
Where StoreID between 12000 and 12999
)ttTemp
Right Join
(SELECT DATEADD(dd,number,@StartDate) as DAYS
FROM
(
select number from master..spt_values
WHERE type='p'
union all
select number+256 from master..spt_values
WHERE type='p'
) as s
where DATEADD(dd,number,@StartDate)<=@EndDate)ttDays on Month(ttTemp.Month) = Month(ttDays.DAYS))ttProjection
Left Join
(Select Date, StoreID, Sum(Sales) as Sales
From Reporting.dbo.RetailSales_byStore_byDay
Group By Date, StoreID)ttSales
on ttProjection.StoreID = ttSales.StoreID
and ttProjection.DAYS = ttSales.Date
Inner Join DelSolNet2.dbo.Store S on ttProjection.StoreID = S.StoreID
Where Month(Days) = Month(getdate())
Order By Days, ttProjection.StoreID
View 3 Replies
View Related
Jan 19, 2007
We are trying to create a report that shows a Week to Date, Month to Date, and Year to Date
Week to Date Month to Date Year to Date
Item Number
I've tried using an if statement (if date = current week, Qty, 0) and then sum the data but I get an error message that reportitems can only be summed in the page header and footer. I've also tried running totals, but it doesn't like the iif statement. Any ideas on how to do this?
Nancy
View 6 Replies
View Related
Apr 8, 2008
I have a table for which I need a “special� running total. More specifically there should be a close communication between the credit and the debit column. The scenario should be in the following order
“Credit�“Debit�“Balance credit�“Balance debit�
6 0 6 0
5 0 11 0
0 4 7 0
0 9 0 2
3 0 1 0
0 5 0 4
0 2 0 6
I have to point out that this is done in a grouped form, where the CustormerID is the grouped clause. Now I have achieved this to a point where the calculations work only if the credit column is bigger than 0, but when the account starts with debit I get only sums of the column not the needed subtractions. What am I missing? Thanks in advance.
DECLARE @PrevRunBal MONEY --Overall running total
SET @PrevRunBal = 0
DECLARE @PrevGrpBal MONEY --Running total resets when account changes
SET @PrevGrpBal = 0
DECLARE @PrevRunCnt INT --Overall running count (ordinal rank)
SET @PrevRunCnt = 0
DECLARE @PrevGrpCnt INT --Running count resets when account changes
SET @PrevGrpCnt = 0
DECLARE @PrevAcctID INT --The "anchor" and "account change detector"
SET @PrevAcctID = 0
changes
SET @PrevGrpBalP = 0
update Temp
SET --===== Running Total
@PrevRunBal = RunBal = @PrevRunBal + dolguva,
-- @iznos =RunBal= dolguva - pobaruva + @sdol -@spob,
--===== Grouped Running Total (Reset when account changes)
@PrevGrpBal = Bcredit = CASE
WHEN CustomerID = @PrevAcctID
THEN CASE
WHEN CREDIt > DEBIT or CREDIT = 0
THEN (@PrevGrpBal+CREDIT)-(DEBIT)
WHEN DEBIT>CREDIT or DEBIT =0
THEN (@PrevGrpBal+DEBIT)-DEBIt
END
ELSE CASE
WHEN CREDIT >DEBIT
THEN (CREDIT)
WHEN DEBIT > CREDIT
THEN DEBIT -- restarts from 0 if only 1 rec.
END
END,
--===== Running Count (Ordinal Rank)
@PrevRunCnt = RunCnt = @PrevRunCnt + 1,
--===== Grouped Running Total (Ordinal Rank, Reset when account changes)
@PrevGrpCnt = GrpCnt = CASE
WHEN CustomerID = @PrevAcctID
THEN @PrevGrpCnt + 1
ELSE 1 -- Restarts count at "1"
END,
--===== "Anchor" and provides for "account change detection"
@PrevAcctID = CustomerID
Apologies if I'm posting in a wrong forum
Nik
View 2 Replies
View Related
Apr 22, 2008
Hi There,
I have the following query:
SELECT NATNLACCT, IDCUST, TEXTSNAM, AMTBALDUEH
FROM
VIEW_ARCUS
where amtbaldueh != .000
order by NATNLACCT
but i want to display the data something similar as below. How do I create a column to display with the natinlacct name(I have many) concatenated to Total and then sum amtbaldueh?
Natinlacct idcust textsnam amtbaldueh
Doda 1234 abcd $101
Doda 5678 efgh $200
Doda 9876 ijkl $300
Doda Total $601
Nava 5847 jhgf $230
Nava 5487 lfde $130
Nava 3587 lrsd $100
Nava Total $460
Thanks
Rhonda
View 5 Replies
View Related
Apr 8, 2008
I have a table for which I need a €œspecial€? running total. More specifically there should be a close communication between the credit and the debit column. The scenario should be in the following order
Credit
Debit
Balance credit
Balance debit
6
0
6
0
5
0
11
0
0
4
7
0
0
9
0
2
3
0
1
0
0
5
0
4
0
2
0
6
I have to point out that this is done in a grouped way where the CustormerID is the grouped clause. Now I have achieved this to a point where the calculations work only if the credit column is bigger than 0, but when the account starts with debit I get only sums of the column not the needed subtractions. What am I missing? Thanks in advance.
DECLARE @PrevRunBal MONEY --Overall running total
SET @PrevRunBal = 0
DECLARE @PrevGrpBal MONEY --Running total resets when account changes
SET @PrevGrpBal = 0
DECLARE @PrevRunCnt INT --Overall running count (ordinal rank)
SET @PrevRunCnt = 0
DECLARE @PrevGrpCnt INT --Running count resets when account changes
SET @PrevGrpCnt = 0
DECLARE @PrevAcctID INT --The "anchor" and "account change detector"
SET @PrevAcctID = 0
changes
SET @PrevGrpBalP = 0
update Temp
SET --===== Running Total
@PrevRunBal = RunBal = @PrevRunBal + Credit,
--===== Grouped Running Total (Reset when account changes)
@PrevGrpBal = Bcredit = CASE
WHEN CustomerID = @PrevAcctID
THEN CASE
WHEN CREDIt > DEBIT or CREDIT = 0
THEN (@PrevGrpBal+CREDIT)-(DEBIT)
WHEN DEBIT>CREDIT or DEBIT =0
THEN (@PrevGrpBal+DEBIT)-DEBIt
END
ELSE CASE
WHEN CREDIT >DEBIT
THEN (CREDIT)
WHEN DEBIT > CREDIT
THEN DEBIT -- restarts from 0 if only 1 rec.
END
END,
--===== Running Count (Ordinal Rank)
@PrevRunCnt = RunCnt = @PrevRunCnt + 1,
--===== Grouped Running Total (Ordinal Rank, Reset when account changes)
@PrevGrpCnt = GrpCnt = CASE
WHEN CustomerID = @PrevAcctID
THEN @PrevGrpCnt + 1
ELSE 1 -- Restarts count at "1"
END,
--===== "Anchor" and provides for "account change detection"
@PrevAcctID = CustomerID
View 13 Replies
View Related
Jul 8, 2013
I'm trying to adapt a formula for running totals to my Query, have some difficulties in declaring the tables correctly.
Formula: SELECT Ordinal, QTY, QTY+COALESCE((SELECT SUM(QTY)
FROM Table b
WHERE b.Ordinal < a.Ordinal),0) AS RunningTotal
FROM Table a
ORDER BY Ordinal
Now my Table is rather lengthy and is described by this query:
Select X.Ord, X.QTY
From (Select.... )X
Now in order to adapt the formula for running totals I did:Select X.Ord, X.QTY, X.QTY+COALESCE((SELECT SUM(X.QTY)
FROM X b
WHERE b.Ord < a.Ord),0) as RunningTotal
From (Select....)X a
Order by X.Ord
It gives my an ncorrect syntax error near a. I guess I miss the (double) declaration of the Table.
View 4 Replies
View Related
Jul 13, 2006
Hi I have a table with the following structure:
UserID__DateJoined
I want to produce a query that shows on a month/year bases the number of users joined for each month AND show a running total of membership, I'm almost there....just got a problem getting the running totals:
this is what I have so far:
SELECT DATEPART(MONTH, DateJoined) AS Month, DATEPART(YEAR, DateJoined) AS Year, COUNT(UserID) AS NuAccounts
FROM Accounts_Users
GROUP BY DATEPART(MONTH, DateJoined), DATEPART(YEAR, DateJoined)
ORDER BY DATEPART(YEAR, DateJoined), DATEPART(MONTH, DateJoined)
How do I produce a column that shows the running total of accounts?
thanks for any help
View 6 Replies
View Related
Sep 14, 2015
I have a ms-sql 2012 for this task. I read that running totals / sum over is better supported here.
There are customers with an account where they can insert or withdraw cash. When they insert, the inserted amount will get a date where it expires. After the expiration date, the cash becomes unavailable.
I'm not sure how to design the tables to meet my needs, but this is my best guess. And below is the first problem in the view.
Table: deposit    This table will hold all deposits
customerId    (string)
balanceType    (string)
transactionDate   (date)Â
expiresOnDate   (date)
amount     (decimal)
transactionText   (string)
The data set for the deposit table could be:
1 Bonus 01-05-2015 30-04-2016  500  Inserted 500
2 Bonus 01-05-2015 30-04-2016  500  Inserted 500
3Â Bonus2Â 01-01-2015Â 31-12-2015Â Â 100Â Â Inserted 100
2Â Bonus2Â 01-01-2015Â 31-12-2015Â Â 100Â Â Inserted 100
Table: withdrawal  This table will hold all withdrawals
customerId    (string)
balanceType    (string)
transactionDate   (date)Â
amount     (decimal)
transactionText   (string)
The data set for the withdrawal table could be:
2Â Bonus2Â 01-04-2015Â -100Â Â Needed 100
2Â Bonus2Â 02-04-2015Â -100Â Â Needed 100
2Â Bonus2Â 03-01-2015Â -100Â Â Needed 100
3Â Bonus2Â 10-04-2015Â -50Â Â Â Took out 50
3Â Bonus2Â 11-04-2015Â -100Â Â Took out 100
[Code] .....
Now I need to combine the two tables in a view with a running total, ordered by customerId, balanceType and transactionDate
customerId
balanceType
transactionDate
amount
Total
Text
The view must show this, when selecting all records:
1 Bonus 01-05-2015 500  500  Inserted 500
1 Bonus 01-07-2015 -100 400  Yes, got 100
1 Bonus 02-07-2015 -100 300  Yes, got 100
1 Bonus 03-07-2015 -100 200  Yes, got 100
[Code] ....
And a last view that will show distinct totals for each customer and balanceType
customerId
balanceType
totalBalance
1 Bonus -200
2 Bonus -300
2Â Bonus2Â -200
3Â Bonus2Â -550
View 8 Replies
View Related
Jan 7, 2014
CREATE TABLE #MyTable
(
Teams VARCHAR(10),
StartDate DATETIME,
Count INT
)
INSERT INTO #MyTable (Teams,StartDate,Count)
SELECT 'Team A', '01/01/2014',10
[Code] ....
'Team A' Has No records for '01/02/2014' and '01/03/2014' so I need to insert values of '01/01/2014' StartDate for Team A for the missing dates
So 'Team A' will now have 3 records
Team A2014-01-01 00:00:00.00010
Team A2014-01-02 00:00:00.00010
Team A2014-01-03 00:00:00.00010
'Team B' Has No records for '01/03/2014' so I need to insert values of '01/02/2014' StartDate for Team B for the missing date
So 'Team B' will now have 3 records
Team B2014-01-01 00:00:00.00030
Team B2014-01-02 00:00:00.00040
Team B2014-01-03 00:00:00.00040
As for 'Team C' we have values for all 3 dates, no inserts needed.
View 3 Replies
View Related
Jan 6, 2015
Given this table;
DECLARE @table TABLE (HolidayDate DATE, HolidayName NVARCHAR(50))
INSERT INTO @table
( HolidayDate, HolidayName )
VALUES ('2012-01-01','New Years Day'),
('2012-01-16', 'MLK Day'),
('2012-02-20', 'Presidents Day'),
[code]....
How to get a result set that shows a new column called PreviousHolidayDate with the corresponding holidays last years date?
HolidayDateHolidayNamePreviousHolidayDate
1/1/2012New Years DayNULL
1/16/2012MLK DayNULL
2/20/2012Presidents DayNULL
4/6/2012Good FridayNULL
5/28/2012Memorial DayNULL
[code]....
View 4 Replies
View Related
Mar 3, 2015
I am working with a stored procedure that needs to roll up a week number column once a week - columns are numbered 1-10, 1 being this week, 2 being last week and so forth
Once a week the 10th column is deleted, the 9th becomes 10, the 8th becomes the 9th and so forth and the 1st is calculated the week numbers are getting all screwed up - and we think it's because one statement starts before the one before it completes the statements go like this:
delete theTable where week_num=10;
update theTable set weeknum=10 where weeknum=9;
update theTable set weeknum=9 where weeknum=8;
and so forth
is that the reason? is there any way not to start one statement until the one before it finishes?
View 2 Replies
View Related
Mar 9, 2015
I am looking to update a record from a previous row. So if there is a value of total goods in week 1, i want that value to carry forward to the value of goods in week 2. Is there any SQL as an example of the best way to accomplish this? I can query it using lag() which works great but i need the source data itself to update as the end-users are accessing the data via lightswitch, so when they save a change, i want the trigger (or whatever you recommend) to update the source table.
View 9 Replies
View Related
Dec 1, 2014
I'm trying to write a query that returns last 30 days data and sums the amount by day. However I need to do it for every year not just the current one(I need to go back as far as 2000).
declare @t table (id int identity(1,1), dt datetime, amt MONEY)
insert into @t (dt, amt)
select '2014-11-30 23:39:35.717' as dt, 66 as amt UNION ALL
select '2014-11-30 23:29:16.747' as dt, 5 as amt UNION ALL
select '2014-11-22 23:25:33.780' as dt, 62 as amt UNION ALL
[Code] ....
--expected output
select '2014-11-30' AS dt, 71 AS Amt UNION ALL
select '2014-11-22' AS dt, 62 AS Amt UNION ALL
select '2014-11-20' AS dt, 66 AS Amt UNION ALL
select '2014-11-18' AS dt, 102 AS Amt UNION ALL
[Code] ....
View 8 Replies
View Related
Jun 2, 2014
Having below data. SHCOMP (Customer ID), CBLNAM ( Customer name), SHDESC (service description), SHAMT (service charge), SHTYPE (F-> From T-> To)
CREATE TABLE #Temp(
[SHCOMP] [numeric](7, 0) NOT NULL,
[SHDESC] [char](35) NOT NULL,
[SHTYPE] [char](1) NOT NULL,
[SHAMT] [numeric](9, 2) NOT NULL,
[Code] ....
I need to remove records that have the same ID, Name, Description and Amount summary = 0, For example
SHCOMP CBLNAM SHDESC SHAMT SHTYPE
123 cust1 desc1 45 F remove
123 cust1 desc1 -45 T remove
123 cust1 desc1 45 F remove
123 cust1 desc1 -45 T remove
123 cust1 desc1 45 F
[Code] ....
Results
SHCOMP CBLNAM SHDESC SHAMT SHTYPE
123 cust1 desc1 45 F
123 cust1 desc1 -35 T
234 cust3 desc2 30 F
here is what I did but didn't work because sum is <> from 0 so is leaving all records
select SHCUST, SHDESC, ABS(SHAMT) SHAMT
into #t1
FROM #Temp
group by SHCUST, SHDESC, ABS(SHAMT)
having SUM(SHAMT)=0
order by SHCUST
delete S from #Temp S
join #t1 T on T.SHCUST = S.SHCUST and T.SHDESC = S.SHDESC
View 5 Replies
View Related
Jul 7, 2015
I have a data that with month values ranging from jan 2012 till july 2013 with some values associated with it.
I want to generate months automatically after july 2013 till december 2013 in sql something like the below one:
Is there a way in sql to do this?
View 2 Replies
View Related
Sep 23, 2015
I have table named #t1 and three columns. CODE, Column1, and Column2.
create table #t1 (
CODE NVARCHAR(20),
COLUMN1 NUMERIC(18,2),
COLUMN2 NUMERIC(18,2)
)
And i have some data:
INSERT INTO #t1 (CODE,COLUMN1,COLUMN2)
VALUES ('432', 0,100),
('TOTAL FOR 432',0,100),
('4320001',0,250),
('4320001',50,0),
('4320001',0,140),
('4320001',300,0),
('TOTAL FOR 4320001',350,390),
('432002',200,0),
('432002',0,100),
('TOTAL FOR 432002',200,100)
drop table #t1
I want to have 4 column (named BALANCE). Balance must be column that represent running totals between two columns (Column1 - Column2) for each group of data. For each group total must start from zero.after total 432 it starts to count again for total 4320001 and again for total 432002. I'm using MS SQL SERVER 2014.
View 9 Replies
View Related
Mar 9, 2006
I've created a Report Builder Column chart with months as the x-axis
(category) and revenue as the y-axis (value). This correctly displays
the revenue for each month, but I'd like to display a running total of
the revenue for the year.
I'm currently displaying:
Jan $2
Feb $1
Mar $4
I want to display:
Jan $2
Feb $3
Mar $7
I know this is possibly using the RunningTotal formula in Reporting
Services, but I'd like to do this in Report Builder so the users can
change and create their own graphs with running totals. Is there a way to do this in Report Builder?
Thanks.
View 1 Replies
View Related
Aug 5, 2014
i am using the followig query :
select count (*) from MEMBERS,dbo.MEMBER_PROFILE
where MEMBER_PROFILE.member_no = members.member_no
AND JOIN_DATE between '07-01-2013 00:01' and '07-31-2013 11:59'
and email <> 'selfbuy_customer@cafepress.com'
and ROOT_FOLDER_NO is not null
and email not like '%bvt.bvt'
This returns the count for the month but I want to see what the total each day was.
View 5 Replies
View Related
Dec 12, 2013
I have my sql tables and query as shown below :
CREATE TABLE #ABC([Year] INT, [Month] INT, Stores INT);
CREATE TABLE #DEF([Year] INT, [Month] INT, SalesStores INT);
CREATE TABLE #GHI([Year] INT, [Month] INT, Products INT);
INSERT #ABC VALUES (2013,1,1);
INSERT #ABC VALUES (2013,1,2);
[code]....
I have @Year and @Month as parameters , both integers , example @Year = '2013' , @Month = '11'
SELECT T.[Year],
T.[Month]
-- select the sum for each year/month combination using a correlated subquery (each result from the main query causes another data retrieval operation to be run)
,
(SELECT SUM(Stores)
FROM #ABC
WHERE [Year] = T.[Year]
AND [Month] = T.[Month]) AS [Sum_Stores],
(SELECT SUM(SalesStores)
[code]....
What I want to do is to add more columns to the query which show the difference from the last month. as shown below. Example : The Diff beside the Sum_Stores shows the difference in the Sum_Stores from last month to this month.
Something like this :
+------+-------+------------+-----------------+-----|-----|---+-----------------
| Year | Month | Sum_Stores |Diff | Sum_SalesStores |Diff | Sum_Products |Diff|
+------+-------+------------+-----|------------+----|---- |----+--------------|
| 2013 | | | | | | | |
| 2013 | | | | | | | |
| 2013 | | | | | | | |
+------+-------+------------+-----|------------+--- |-----|----+---------| ----
View 3 Replies
View Related
Jan 2, 2015
So I am trying to work out the difference between today's date (GETDATE()) and a Target Date in a specific table (targetdate)
When I use the DATEDIFF function it is including non working days in the calculation (weekends and bank holidays). Although our date calandar table provided to us from a third party supplier will tell you the weekends, it does not tell you the bank holidays.
Luckily there is another table in the database called - ih_non_work_days.
The format of the date is "2014-12-25 00:00:00.000" for example in that table.
How do I using my "targetdate" and today's date calculate in days the differance - excluding the dates that exist in the ih_non_work_days database?
So for now my basic script looks like -
SELECT com.comm_reference AS 'Referance'
,com.current_task_target_date AS 'TargetDate'
, DATEDIFF(D,com.current_task_target_date,GETDATE()) AS 'Incorrect Date Calculation'
FROM [dbo].[em_communication] as com
View 2 Replies
View Related
May 12, 2015
I have a date that I need to add 'n' number of business days to. I have a calendar table that has a 'IsBusinessDay' flag, so it would be good if I was able to use this to get what I need. I've tried to use the 'LEAD' function in the following way;
SELECT A. Date, B.DatePlus3BusinessDays
FROM TableA A
LEFT JOIN (Select DateKey, LEAD(DateKey,3) OVER (ORDER BY datekey) AS DatePlus3BusinessDays FROM Calendar WHERE IsBusinessDay = 1) B ON A.DateKey = B.DateKey
Problem with this is that because I am filtering the Calendar for business days only, when there is a date that is not a business day in TableA, a NULL is being returned.
Is there any way to do a conditional LEAD, so it skips rows that are not business days? Or do I have do go with a completely different approach?
View 9 Replies
View Related
Nov 28, 2012
I have a sales tables which looks as below.
DEPARTMENT
Barnd_Name
Item_Group
   S_DATE
     S_AMOUNT
Administration
IBM
[code]....
Now i need Month Wise Running Totals.but i should check the following group as show below i that order
1) DEPARTMENT
1) Brand
3) Item Group
4) Month
View 12 Replies
View Related
