Transact SQL :: Query For Month Wise Running Totals Of Sales Amount?

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


ADVERTISEMENT

Transact SQL :: Displaying Sales Data In A Month By Month Grid

Aug 11, 2015

Most of the data is in one table. 

Company 1-Jan 1-Feb 1-Mar 1-Apr
RSP RSP RSP RSP
NON-RELO $295 1 $0 0 $1,400 7 $0 0 $1,195 4 $0 0 $4,700 8 $0 0
AMERICAN ESCROW & CL//AECC $2,650 4 $0 0 $3,720 8 $0 0 $2,339 4 $0 0 $2,460 2 $0 0
American Internation//AIRCO $9,131 30 $2,340 9 $10,927 35 $2,340 9 $9,142 31 $2,600 10 $18,406 54 $3,900 15
American Internation//AIR $20,611 63 $1,820 8 $23,892 75 $1,040 4 $35,038 111 $3,120 12 $3,778 16 $1,560 6
American Internation//Ab $64,248 206 $6,240 24 $59,800 187 $5,200 20 $87,115 264

I did something similar doing just record counts but this is far more complicated. I'm at a loss that this is even possible.

 SUM(CASE datepart(month, tbFile.openedDate) WHEN 1 THEN 1 ELSE 0 END) AS 'January', 

View 2 Replies View Related

Transact SQL :: Show (0) Amount For A Month If No Data Exists In The Table For That Month?

Nov 9, 2015

I have two tables Costtable (Id,ResourceId, Amount,Date) and ResourceTable (ResourceId,Name) which shows output as below.

I want to show 0 amount for rest of the name in case of September. For e.g. if rest of the Resources does not appear in cost table they should appear 0 in amount

My Desired output

My current query

SELECT
RG.Id AS Id,
RG.Name AS Name,
ISNULL(SUM(AC.Amount), 0) AS Amount,
RIGHT(CONVERT(varchar(10), AC.[Date], 105), 7) AS [YearMonth]

[Code] ....

View 6 Replies View Related

Transact SQL :: Count Of Records By Month Wise

Oct 7, 2015

I am trying to get count of  records by month wise when they select year .It was showing  the out put correctly but its showing months arer in numbers,but I want to display Jan,Feb ...

SELECT DISTINCT Standard, COUNT(Standard) AS Total,month(ReportDate) Month
FROM CPTable where
year(ReportDate) = '2015'
GROUP BY Standard, Standard ,
month(ReportDate)

Output

Standard Total Month
NULL 0 1 //Jan
NULL 0 2 //Feb
NULL 0 3
NULL 0 4
NULL 0 5
OSHA 18001, 1 5
NULL 0 6
NULL 07
NULL 08
OSHA 18001,158
TL 9000,18
NULL 09
OSHA 18001,139

View 4 Replies View Related

Fiscal Year Totals - Calculating Sales By Month And Current Year

Sep 18, 2013

I have the following script that calculates Sales by month and current year.

We run a Fiscal year from April 1st thru March 31st.

So April 2012 sales are considered Fiscal Year 2013.

Is there a way I can alter this script to get Fiscal Year Totals?

select ClassificationId, YEAR(inv_dt) as Year, cus_no,
isnull(sum(case when month(inv_dt) = 4 then salesamt end),0) as 'Apr',
isnull(sum(case when month(inv_dt) = 5 then salesamt end),0) as 'May',
isnull(sum(case when month(inv_dt) = 6 then salesamt end),0) as 'Jun',
isnull(sum(case when month(inv_dt) = 7 then salesamt end),0) as 'Jul',

[Code] ....

Data returned looks like the following.

ClassificationID Year Cus_no Apr May June ....
100 2012 100 $23 $30 $400
100 2013 100 $40 $45 $600

What I would need is anything greater than or equal to April to show in the next years row.

View 2 Replies View Related

Transact SQL :: Split Date Range Into Monthly Wise And Loop Through Each Month To Perform Some Operation

Oct 20, 2015

Let's say if the date is 01/01/2015 till 01/01/2016

I want split these dates monthly format wise and then use them in variable in cursors to loop

For an example Monthly date should be 01/01/2015 and 01/31/2015 and use these two values in 2 variables and make a loop until it never ends whole date range which is 01/01/2016

View 2 Replies View Related

Transact SQL :: Running Totals In A View

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

SQL Server 2012 :: Query To Get The Month Wise Data?

Sep 13, 2015

I have the data in the below format.

Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201406 xxxxxx 27 1

In the above data, no record exist for 201403,201404,201405, query I wrote will give only the data for which there LeftCount exists, but I am looking for a query which get the data in the below format.

Month(YYYYMM) | Department | TotalCount | LeftCount
201401 xxxxxx 30 0
201402 xxxxxx 28 2
201403 xxxxxx 28 0
201404 xxxxxx 28 0
201405 xxxxxx 28 0
201406 xxxxxx 27 1

View 6 Replies View Related

Transact SQL :: Establishing Unique Sales ID To Future Sales IDs From Original Sale

May 19, 2015

i am trying to find a way to link an 'initial' Sale ID of a product to 'future' Sale IDs of products that will trace back to the original Sale ID.For example, if I call the original sale , 'Sale ID #123',  how can i link future Sale ID's (child[ren]) and all future sales to the original Sale ID #123? Can I use a Surrogate Key or similar function?

Parent:Sale ID #123
Children: Sale ID # 456,
Sale ID #789,
Sale ID #.....

how I can link the original Sales ID (Parent) to Sale ID's (child[ren]) of future purchases currently existing and in the future going forward?

View 4 Replies View Related

Adapting Running Totals To Query

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

Transact SQL :: Query To Show Totals

Aug 28, 2015

This is a query that produces a table with garbage data, but (I think) will get the point across of what I need. SQL Server 2008

Create Table SanitationGarbage
(
saleid int
,projectname varchar(200)
,typeofsale varchar(200)

[code]....

Now my select query below does not group the data like I need it to, nor does it show a total row like I need it to.  How does this need to be written so the data is displayed in the proper formatting?

Select
projectname
,Count(saleID) As [Total Sales]
,Count(case when typeofsale = 'Final' then saleID else null end) As [Final Sales]
,Count(case when typeofsale = 'Pending' then saleID else null end) As [Pending Sales]
FROM SanitationGarbage
GROUP BY projectname
order by projectname asc

[code]....

View 4 Replies View Related

SQL Server 2008 :: Find Locations With MAX Sales Amount Per Customer

Feb 13, 2015

I have an invoice table with customer, date, sales location, and invoice total. I want to total the invoices by location by customer and see what location sells the most to each customer. My table looks like this.

CustDate LocAmt
A1/1/2014 1520
A1/1/2014 1560
A1/1/2014 2575
A1/1/2014 3580
B1/30/2014 15100
B1/30/2014 15200
B1/30/2014 2575
B1/30/2014 25150
B1/30/2014 35250

I want to get this, because for customer A, location 35 had the largest SUM of sales and for customer B, location 15 had the largest SUM.

CustLoc
A35
B15

If I have to use CTEs to get there, I can do that too. Just a little lost on how to get only the one location per customer.

View 2 Replies View Related

Month Wise Employee Count

Apr 26, 2007

Hi Folks,

Please help me in writing a query. I need to get current strength of employee's, monthwise (till each month).

Table Structure
----------------
Employee_Master

ED_EmpId ED_DOJ ED_Name
-------- ------- ---------
2006-01-02 SRINIVASA
2006-01-02 KAVITA
2006-01-02 VIVEK
2006-02-20 CHANDRA
2006-02-25 PARIMAL
2006-02-30 SATISH
........
2007-01-10SANJEEV SHARMA
2007-01-14 JITENDRA PRATAP SINGH
2007-03-15 SANDEEP
2007-03-02 SUNIL KUMAR SHARMA


I require the data in the below format..

For the year '2007-2008'

Month Count
----- -----
April(07) 10
May . 10
June . 10
July . 10
August . 10
Sept 10
Oct 10
Nov 10
Dec 10
Jan (08) 10
Feb . 10
March . 10



Thanks.

View 2 Replies View Related

Month Wise Date On Column Address

Nov 20, 2007

could u help me out to get the month wise date on column address

this is the query

SELECT[ID],
SUM(CASE WHEN week_no = 1 THEN Qty ELSE 0 END) AS week1,
SUM(CASE WHEN week_no = 2 THEN Qty ELSE 0 END) AS week2,
SUM(CASE WHEN week_no = 3 THEN Qty ELSE 0 END) AS week3,
SUM(CASE WHEN week_no = 4 THEN Qty ELSE 0 END) AS week4,
SUM(CASE WHEN week_no = 5 THEN Qty ELSE 0 END) AS week5
FROM
(
SELECT[ID],week_no = DATEPART(week, [Date]) - DATEPART(week, DATEADD(MONTH, DATEDIFF(MONTH, 0, @input_date), 0)) + 1,Qty
FROM@sample
WHERE[Date]>= DATEADD(MONTH, DATEDIFF(MONTH, 0, @input_date), 0)
AND[Date]< DATEADD(MONTH, DATEDIFF(MONTH, 0, @input_date) + 1, 0)
) d
GROUP BY [ID]

for this query the output will be

/*
ID week1 week2 week3 week4 week5
----- ----------- ----------- ----------- ----------- -----------
st001 114 38 0 0 0
st002 110 110 0 0 0


but i need date wise split up to be displayed for particular month

ID week1 week2 week3 week4 week5
(1-7) (8-14) (15-21) (22-28) (29-31)
----- ----------- ----------- ----------- ----------- -----------
st001 114 38 0 0 0
st002 110 110 0 0 0

pls help me out.






Shiney

View 6 Replies View Related

Sum Of Sales - Last Day Of The Month

Nov 15, 2014

I need to get the sum of sales for the last date of each month group by custom and by month(fecha)

for example, for custom ='q' in month=8 I have 3 items in the last day of the month ='2014-08-15' totalling 13 and so on

DECLARE @sales TABLE
(custom VARCHAR(10) NOT NULL,
fecha DATE NOT NULL,
sales NUMERIC(10, 2) NOT NULL);
INSERT INTO @sales(custom, fecha, sales)
VALUES ('q', '20140708', 51),

[Code] .....

View 2 Replies View Related

Top 5 Sales EACH Month

Jun 7, 2006

I have a table tblSales ( DollarAmount,DateSold, Barcode --- ) in SQL MSDE 2000.

What I want is the Top 5 sales for EACH month:

Month TotalSales Barcode

2006-05 Top01 Barcode01

2006-05 Top02 Barcode02

2006-05 Top03 Barcode03

2006-05 Top04 Barcode04

2006-05 Top05 Barcode05

2006-04 Top11 Barcode11

2006-04 Top12 Barcode12

2006-04 Top13 Barcode13

2006-04 Top14 Barcode14

2006-04 Top15 Barcode15

2006-03 Top21 Barcode21

2006-03 Top22 Barcode22

---- --- ----

--- ---- ---

TopNN is SUM(DollarAmount).

I created a table AAAA and use a while loop to insert the data into AAAA.

What I did is:

declare @StartDate and @EndDate, set @StartDate and @EndDate

Delete AAAA

Begin

While (@EndDate<GetDate())

Begin

Insert into AAAA(sales, Month, Barcode)

Select TOP 5 SUM(DollarAmount) AS sales,

LEFT(CONVERT(CHAR(8),DateSold,112),4)+'-'+RIGHT(LEFT(CONVERT(CHAR(8),DateSold,112),6),2) AS Month,

Barcode

from tblSales

where something

group by something

order by sales1 DESC

increase @StartDate and @EndDate by a month

End

Select * from AAAA



It works fine. My question is: Can I get rid of table AAAA?

How can I use T-SQL only to get the correct sets?

Thanks in advance.

Long

View 4 Replies View Related

Pls Help W/ A Query To Return Running Balances From The Previous Rolling 3 Month Period.

Oct 18, 2007

Please refer to this table in this discussion:






Charges

Date


1

1/1/07


2

2/1/07


3

3/1/07


4

4/1/07


5

5/1/07


6

6/1/07


7

7/1/07


8

8/1/07


9

9/1/07


10

10/1/07


11

11/1/07


12

12/1/07
What i'm trying to do is return a result with total charges from 3 months previous based on a date parameter or just as a whole for that matter. For example:

If my date parameter is 6/14/07, i'd like my total charges to be 15 (6+5+4).
If my date parameter is 7/10/07, the charges would be 18 (7+6+5)

I hope that makes sense of where i'm going with this. I've played with this using the "Guru's Guide" solution by Ken Henderson, but still to no avail yet. Here's his code syntax:





Code Block

SELECT a.DayCount, a.Sales, SUM(b.Sales)
FROM Sales a CROSS JOIN Sales b
WHERE (b.DayCount <= a.DayCount) AS RunningTotal
GROUP BY a.DayCount,a.Sales
ORDER BY a.DayCount,a.Sales
Here is the result set i'm looking for:






Charges

Date


1

1/1/07


2

2/1/07


6

3/1/07


9

4/1/07


12

5/1/07


15

6/1/07


18

7/1/07


21

8/1/07


24

9/1/07


27

10/1/07


30

11/1/07


33

12/1/07
Each date's charges are a culmination of 3 months worth of charges.

Any help would be greatly appreciated. I'll be trying to figure this one out concurrently and if i do, i'll post back!

Thanks in advance!

View 6 Replies View Related

Transact SQL :: Query To Get Last Available Data Given Month And Year?

Jul 21, 2015

I am in seach of a query where in I can provide month, year and client name and fetch last available comments from the table.

Client,Month,Year and Comments are columns in that table.

For Ex: If i pass client as A, month as 7 and year as 2015, I should get comments for client A, month July and year 2015 if available.

If data not available, it must go to June month and so on until it finds comments.Also when month is Jan, if query is going back, year also should get changed.

View 10 Replies View Related

Dividing The Sale Amount By Month

Jul 3, 2006

Hello,

I want to process a row from a source table by dividing the sales amount in that row over the period of the sale by month.

For instance if an item is sold for 500$ and it's duration is 5 months from 1/15/2004 till 6/15/2004, I want to divide the sale amount by month as follows:

Month 1: 50$

Month 2: 100$

Month 3: 100$

Month 4: 100$

Month 5: 100$

Month 6: 50$

I know I can create a script component and do the calculation for each month and insert 6 records in the fact table for each row in the source table, where each record holds the amount for the corresponding month. However I was wondering if there is another technique that utilizes the components of SSIS to do it more efficiently.

Thanks,

Grace

View 4 Replies View Related

Power Pivot :: Measure Returning Blank (Month Sales)

Jun 25, 2015

The following measure returns Month Sales: (blank)

Month Sales:=TotalMTD(Sum([Sales Amount]),'Date'[Full Date])

I have a relationship between ResellerSales-OrderDateKey and Date-DateKey

The relationship works fine when I use it in Access 

View 3 Replies View Related

SQL Server 2012 :: Find Trend How Employee Makes Sales Every Month?

Jun 7, 2015

How do I find sales trend of an employee via comparing current month and previous month sales?

I got so far query upto following,

;WITH SalesOrderHeader As
(
SELECT ROW_NUMBER() OVER (ORDER BY SUM(H.SUBTOTAL)) AS ROWNUMBER, SUM(H.SUBTOTAL),H.SALESPERSONID,

[Code]....

I am getting following error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

View 3 Replies View Related

Variables And Grouping - Calculate Net Sales And Margin Percentage For Each Month Of Current Year

Mar 11, 2014

I need to calculate “NET_SALES” and “MARGIN_PERCENT” for each month of the current year … the following returns the same values for each month in the list, which are for the current month. Taking out the GROUP BY line works fine for an overall number.

SALES_MONTH, NET_SALES, MARGIN_PERCENT
January, 1246627.69, 24
February, 1246627.69, 24
March, 1246627.69, 24
-------------------------------------------------

DECLARE @NetSales DECIMAL(18,6)
DECLARE @Cost DECIMAL(18,6)

SELECT
@NetSales = sum(IL.MERCHANDISE+IL.TAX)
,@Cost = sum(IL.COST)
FROM INVOICELINE IL

[Code] .....

View 2 Replies View Related

Transact SQL :: Get Query By Selecting Month From Dropdown List And Display Records?

Oct 8, 2015

Im trying to get query by selecting the month from dropdownlist and display the records .by using the below query I need to enter the date in tecxtboc then it will show the output

select Standard, Total, MonthName
from (SELECT Standard, COUNT(Standard) AS Total,
datename(month, ReportDate) as [MonthName]
FROM CPTable where
ReportDate >= @ReportDate

[Code] .....

View 5 Replies View Related

SQL Server 2012 :: Split Total Days And Amount Between Start And End Month

Mar 21, 2015

I have the table below and like to create a view to show the no of days the property was vacant or void and rent loss per month. The below explanation will describe output required

For example we have a property (house/unit/apartment) and the tenant vacates on 06/09/2014. Lets say we fill the property back on 15/10/2014. From this we know the property was empty or void for 39 days. Now we need to calculate the rent loss. Based on the Market Rent of the property we can get this. Lets say the market rent for our property is $349/pw. So the rent loss for 39 days is 349/7*39 = $1944.43/-.

Now the tricky part and what im trying to achieve. Since the property was void or empty between 2 months, I want to know how many days the property was empty in the first month and the rent loss in that month and how many days the property was empty in the second month and the rent loss incurred in that month. Most of the properties are filled in the same month and only in few cases the property is empty between two months.

As shown below we are splitting the period 06/09/2014 - 15/10/2014 and then calculating the void days and rent loss per month

Period No of Void Days Rent Loss
06/09/2014 - 30/09/2014 24 349/7*24 = 1196.57
01/10/2014 - 15/10/2014 15 349/7*15 = 747.85

I have uploaded a screenshot of how the result on this link: [URL] ....

Declare @void Table
(
PropCode VARCHAR(10)
,VoidStartDate date
,LetDate date
,Market_Rent Money

[Code].....

View 4 Replies View Related

Running Totals With SQL

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

Running Totals

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

Running Totals

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

Running Totals

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

Running Totals

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

Transact SQL :: Time Column With Order Wise

Nov 17, 2015

I have a table like below

CREATE table #TempTable (ID integer,CTime time(7),CType Varchar(1))
insert into #TempTable VALUES (1001,'16:50:05.0000000','I')
insert into #TempTable VALUES (1001,'13:27:49.0000000','O')
insert into #TempTable VALUES (1001,'20:44:00.0000000','O')
insert into #TempTable VALUES (1001,'21:12:00.0000000','O')

I need result like below screen shot 

here 'I' stands for In,and 'O' for Out, in this example first In time not available

View 7 Replies View Related

Transact SQL :: Passing Value In Where Condition Loop Wise

Jun 1, 2015

I want to calculate year to month data for month wise . Ihave start range and end range table like below

Start range and end range table

Start dateEnd date
01-04-201401-11-2014
01-04-201401-12-2014
01-04-201401-01-2015
01-04-201401-02-2015

[Code] ....

Then I want to pass this value one by one in below table for get value month wise

Main table

Branchamountperiod
Branch110201103
Branch22201104
Branch33201401
Branch44201402
Branch58201403
Branch610201404

[Code] ....

The below query is for november month: (Apr to Nov)

select * from maintable
where period between '01-04-2014' and '01-11-2014'
then for December month : (Apr to Dec)

So I want to pass second row

select * from maintable
where period between '01-04-2014' and '01-12-2014'
.....
....
select * from maintable
where period between '01-04-2015' and '01-12-2015'

Like wise I want to get month wise data of YTM data.

My expected output is

201411
201412
201501
201502
201503
201504
201505

[Code] ....

View 10 Replies View Related

Transact SQL :: Query On Running Value (difference)

Jun 18, 2015

I have a table that will be loaded over night everyday and I need to write a query on running value difference ?

List of Columns (ID, Branch ,Group, Date, Value)

ID    Branch   Group   Date                   Value
1        A           C      2015-06-01            10
2        A          C       2015-06-02            15
3        A          C       2015-06-03            25
4        A          C       2015-06-04            20
5        B          D       2015-06-01            20
6        B          D       2015-06-02            25
7        B          D       2015-06-03            10
8        B          D       2015-06-04            20

I want the Output like below with a Running value difference in comparison to previous day.

ID    Branch   Group   Date          Value    Running Value
1        A           C      2015-06-01            10         10
2        A          C       2015-06-02            15         05
3        A          C       2015-06-03            25         10
4        A          C       2015-06-04            20         -5
5        B          D       2015-06-01            20         20
6        B          D       2015-06-02            25         05
7        B          D       2015-06-03            10        -15
8        B          D       2015-06-04            20         10

Basically I need to compare the previous day and show the difference. How can I do this in SQL 2008 r2?

View 6 Replies View Related

SQL Server 2012 :: Create View To Allocate Amount Per Month - Financial Year

Mar 21, 2015

I like to create an SQL view to divide amount 300,000 between 12 month starting from Month July 2014 to June 2015 as shown below

Amount Month Year
25,000 July 2014
25,000 August 2014
25,000 September 2014
25,000 October 2014
25,000 November 2014
25,000 December 2014
25,000 January 2015
25,000 February 2015
.
.
.
.

25,000 June 2015

View 7 Replies View Related







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