Transact SQL :: Aggregate Last 12 Months

Sep 2, 2015

I have a table with sample data as shown in the table below. for each month end date, I need to aggregate (sum) all the quantities for each product and customer and include the current month plus the past 12 months (including current month). 

For example, for customer C123, P123 and for 7/31/2015, the query should add 7/31/2015 quantity and the quantity for 1/31/2015. 

DECLARE @TEMP TABLE (Customer VARCHAR (10), Product VARCHAR(10), Month_end_date DATE, Quantity INT)
INSERT INTO @TEMP VALUES ('C123','P123','1/31/2015',10)
INSERT INTO @TEMP VALUES ('C124','P124','2/28/2015',20)
INSERT INTO @TEMP VALUES ('C125','P125','3/31/2015',30)
INSERT INTO @TEMP VALUES ('C126','P126','4/30/2015',40)

[Code] ....

the output for the above example should be
C123 P123 7/31/2015
 70+10=80

is this possible?

View 10 Replies


ADVERTISEMENT

Transact SQL :: Getting Data For Last 3 Months

Oct 14, 2015

I have a date into format YYYYMM. Data type is int. I need to make query where every time it will return me last 3 months, but without current one. For example I have data for months below

201510
201509
201508
201507

Query should return all records for 201509, 201508, 201507.

I was trying lot of solutions founded in internet like this one:

Date_Column >= DATEADD(MONTH, -3, GETDATE())
or
DATEDIFF(MONTH, my_date_column, GETDATE()) <= 3

but it doesn't work.

View 4 Replies View Related

Transact SQL :: Pulling The Most Recent 12 Months Of Data

Jul 1, 2015

My goal is to show the most recent 12 months of data including the current month based on a patients discharge date (relative to the query execution time). There could be many years of data so I want to be sure I am pulling the most recent 12 monthsThis seems to work (on the surface anyway … not quite sure if this is the best logic to use).

DATEDIFF(month,PAT_ENC_HSP_discharged.HOSP_DISCH_TIME,
GEtDate())
< 13

View 5 Replies View Related

Transact SQL :: Number Of Distinct Users In Last 3 / 6 / 9 / 12 Months

Aug 26, 2015

I have table A(year int, month int, user varchar(20)), and I am trying to write a view that would show number of distinct users in the last 3 months, last 6 months, last 9 months and last 12 months(all from the most recent year/month) in following format:

3 months     6 months     9 months     12  months

number of distinct users        x                  y                  z                      w

View 20 Replies View Related

Transact SQL :: Last Record Not In Past 9 Months From Todays Date?

Aug 20, 2015

I would like to display Car records to retrieve last Maintenance request for all customer who did not make request in past 9 months from current date.

MC refers to Maintenance.

MCLog data:

carId: SGY12345 (repeated)
mcDate: 2010-01-30
mcDate: 2012-03-30
carId: SGX55661 (repeated)
mcDate: 2015-05-30
mcDate: 2015-06-15

[Code] .....

Here is my Table:

CREATE TABLE Car (
carId Char (20) NOT NULL,
model Char (20) NOT NULL,
importDate smalldatetime NOT NULL,
custId Varchar (50) NOT NULL,
CONSTRAINT Car_PK PRIMARY KEY (carId));

[code]....

View 6 Replies View Related

Transact SQL :: How To Convert Hours To Days And Months Correctly

Oct 13, 2015

I get a column in hours in a table and when I try to convert it to months and days its giving me incorrect results.

My timehours column in my table has hours recorded timehours value is 5832 and I get results as 11 months and 27 days.

This query is giving me wrong result -select months=(timehours %365)/30,days= (timehours % 365)%30 

View 13 Replies View Related

Transact SQL :: Get Counts Including Previous Years / Months?

May 28, 2015

I have simple query that works fine if I write it every time. I want to get this one time group by each year and week ending. here us the query:

Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/09'

Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/16'

Select count(distinct ID) from table1 where year<=2015 and WeekEnding<='05/23'

How can I get the count for each week year and each week ending? Weekending 05/16 will include ID's from weekending 05/09 as well and week ending 05/23 will include all the ID's from  05/16 and 05/09 and so on...

View 13 Replies View Related

Transact SQL :: Making Hours Into Months And Days In 2008 R2?

Aug 5, 2015

I have hours which can be like 32.5 and would like to have them in 1 month 2 Days format.

View 2 Replies View Related

Transact SQL :: Aggregate In Subquery

Oct 22, 2015

(select SUM(sales.Total) from sales where StudentHist.Curdate = max(sales.curdate))  AS 'Balance'Iam trying to write a subquery to  calculate the total amount of sales until the Curdate  in studenthist equals the Curdate in sales table..how to write this query??

View 11 Replies View Related

Transact SQL :: Group By With Non-aggregate Columns

Aug 5, 2015

How can I aggregate this result into 1 row? (I got it from a UNION ALL)

Article         Assort1    Assort2
50095811    K1             NULL
50095811    NULL          K3

I would like to have

Article         Assort1    Assort2
50095811    K1             K3

View 3 Replies View Related

Transact SQL :: Using Aggregate Function With Subquery?

Aug 6, 2015

SSMS does not like mine!  THis is the error that I receive:

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

And this is my syntax:

Select
employeeID
,COUNT(case when rehirestatus IN (select rehirestatus from regionalemptable where rtrim(storename) = 'Location1') THEN userID ELSE 0 END) +
COUNT(case when rehirestatus IN (select rehirestatus from globalemptable where rtrim(storename) = 'Location1') Then userID ELSE 0 End)
FROM production
GROUP BY employeeID
ORDER BY employeeID

View 6 Replies View Related

Transact SQL :: Working Of Aggregate In Sub-query Using Exists?

Aug 4, 2015

When i am running  below snippet execution plan is showing constant scan instead of referring subquery table.

I want to know  how this query working. and why in execution plan there is no scan /seek which will basically indicate that particular table is getting referred.

select count(*) from A  where exists (select count(1) from B where A.a=B.a)

execution plan has to show  scan or seek for subquery. Surprisingly, output is coming as expected.

View 8 Replies View Related

Transact SQL :: Create Sequential Aggregate Of A Column Using Sum?

Aug 17, 2015

I need to create a sequential aggregate of a column using sum in tsql.

I want a column that will sum up all hours work by transaction date.

for example

Monday my hours work will be 8 and therefore my total transaction hours will be 8.

Tuesday my hours work will be 8 but I want the total hours of my total transaction hours to  16.

Wed my total hours work will be 5 and I want the total hours of my total transaction hours to  21. etc.

Select myname, weekday, hours worked, Totalhours_ToDate =Sum(hours worked)from table

View 3 Replies View Related

Transact SQL :: How To GROUP BY And Get Aggregate Where A Certain Condition Is True

Jul 16, 2015

I am using SQL 2005.  I have some data from an old application that did not follow the rules for normalization.  The table is for Invoices, and the table allows for 13 purchase items per record.  So in each row of my table I have a non-unique integer field itemID, itemID1, itemID2 ... itemID12.  For each itemID I also have "lbs_total" and "line_total" (which is price * lbs_total) - so itemID, lbs_total, line_total ... itemID1, lbs_total1, line_total1 ... etc.  It's a mess, I know.Each row has a unique Customer Number ("cno") and an Invoice Date ("inv_date").  My proc needs to allow for params for the item number, and a start date and end date for BETWEEN on the inv_date.I also need to get the aggregate for the lbs_total and the line_total.

View 15 Replies View Related

Transact SQL :: Aggregate 2 Dates And Concatenate Into New String

May 21, 2015

Have this table

ACCOU  NAME      NAME TODATE                            ID     EDUCAT    EXPIRYDATE
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 2016-06-24 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL  2018-02-11 00:00:00.000

Need to get it like

ACCOU  NAME      NAME TODATE                            ID     EDUCAT    EXPIRYDATEstring
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-basis 1900-01-01 00:00:00.000 2016-06-24 00:00:00.000
011647 MILUCON Empl1 1900-01-01 00:00:00.000 9751 VCA-VOL  2018-02-11 00:00:00.000

In other words I need to Aggregate the 2 dates and concatenated into a new string col string so basically a sum with a group by but instead of a sum I need to concatenate the string. I know this should be possible using stuff and for xml path but I can't seem to get my head around it everything I try concatenates all the strings, not just the appropriate ones.

View 11 Replies View Related

Transact SQL :: Invalid Due To Not Being In Group By Or Aggregate Function

Aug 7, 2015

Well adding it to a group by or function skews the result set. How to write this query so it displays as I need it to?  This is what I have thus far, and it works as it should UNTIL I add in the line of 

cast(cte.[C] As float)/cast(sum(cte.[C]) over() as float)*100 As [Rate1],

Presents the error of:
Msg 8120, Level 16, State 1, Line 35
Column 'cte.[C]' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

This is my full on query -- with 3 CTE's involved to get me the actual result set I am after.  

;with cte as
(
select
[state],
case when exists (select 1 from table2 R where R.centername = d.centername) then 1 else 0 end as [L],
case when exists (select 1 from table3 C where C.centername = d.centername) then 1 else 0 end as [C]
FROM maintable d
),

[Code] .....

View 4 Replies View Related

Transact SQL :: Find Missing Months In A Table For The Earliest And Latest Start Dates Per ID Number?

Aug 27, 2015

I need to find the missing months in a table for the earliest and latest start dates per ID_No.  As an example:

create table #InputTable (ID_No int ,OccurMonth datetime)
insert into #InputTable (ID_No,OccurMonth) 
select 10, '2007-11-01' Union all
select 10, '2007-12-01' Union all
select 10, '2008-01-01' Union all
select 20, '2009-01-01' Union all
select 20, '2009-02-01' Union all
select 20, '2009-04-01' Union all
select 30, '2010-05-01' Union all
select 30, '2010-08-01' Union all
select 30, '2010-09-01' Union all
select 40, '2008-03-01'

For the above table, the answer should be:

ID_No OccurMonth
----- ----------
20 2009-02-01
30 2010-06-01
30 2010-07-01

1) don't include an ID column,

2) don't use the start date/end dates in the data or

3) use cursors, which are forbidden in my environment.

View 9 Replies View Related

Transact SQL :: Aggregate Function Causing Error In Complex Join

May 13, 2015

I have a few tables I am trying to join to create a report. Everything was working fine until I tried to add an aggregate Sum function to a column (MaxCap) in table ctfBarn. 

select
x.*, y.division, y.department, y.location
,(right(z.SvcMgrName,len(z.SvcMgrName)-len(left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))-2)+' '+
left(z.SvcMgrName,CHARINDEX(', ',z.SvcMgrName)-1))AS SvcMgrName
,(right(z.SrSvcName,len(z.SrSvcName)-len(left(z.SrSvcName,CHARINDEX(', ',z.SrSvcName)-1))-2)+' '+

[Code] .....

I think I probable need to include a group by but can't figure out the correct syntax.

View 12 Replies View Related

Transact SQL :: How To Turn Select Aggregate Function Statement Into A Variable

May 26, 2015

I tend to learn from example and am used to powershell. If for instance in powershell I wanted to get-something and store it in a variable I could, then use it again in the same code. In this example of a table order items where there are order_num, quantity and item_prices how could I declare ordertotal as a variable then instead of repeating it again at "having sum", instead use the variable in its place?

Any example of such a use of a variable that still lets me select the order_num, ordertotal and group them etc? I hope to simply replace in the "having section" the agg function with "ordertotal" which bombs out.

select order_num, sum(quantity*item_price) as ordertotal
from orderitems
group by order_num
having sum(quantity*item_price) >=50
order by ordertotal;

View 11 Replies View Related

Transact SQL :: Adding Case When Statement With Group By Query Doesn't Aggregate Records

Aug 28, 2015

I have a a Group By query which is working fine aggregating records by city.  Now I have a requirement to focus on one city and then group the other cities to 'Other'.  Here is the query which works:

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Kansas City 800
Columbia 700
Jefferson City 650
Joplin 300

When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':

Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' 
From [Output-MarketAnalysis]
Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active'
Group by [City]

Here is the result:

St. Louis 1000
Other Missouri City 800
Other Missouri City 700
Other Missouri City 650
Other Missouri City 300

What I would like to see is a result like:

St. Louis 1000
Other Missouri City 2450

View 5 Replies View Related

Selecting Data Within 1 Month, 6 Months, 12 Months

Mar 27, 2008



I have the following table



FeedBack Type Date

test2 positive 03/15/08

tes3 negative 03/01/08

.. ....



in my page i need to select the number of negative/positive comments within the last



1 month, 6 months, 12 months



How can I accomplish that?
thanks

View 5 Replies View Related

Cannot Perform An Aggregate Function On An Expression Containing An Aggregate Or A Subquery.

Oct 19, 2007

Can any1 tell me why i am getting an error


SELECT DISTINCT

--p.voucher,

--p.amount,

p.siteID,

b.siteID,

SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS OutStandingBalance,

SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS CashCheque,

SUM((round(b.total,2,2)) - SUM(round(p.amount,2,2))) AS Vouchers

FROM

BillingTotal b,

Payment p

--WHERE

-- s.sitename=@cmb1

--AND p.siteid = s.siteid

-- p.voucher = 0

-- p.voucher = 1

GROUP BY p.siteID,b.siteID



Msg 130, Level 15, State 1, Line 1

Cannot perform an aggregate function on an expression containing an aggregate or a subquery.

View 8 Replies View Related

Analysis :: Calculated Member From Aggregate And Non-aggregate Factors

Sep 22, 2015

I have 2 Dimensions in SSAS (see below end), I want to create a calculated member, named

This_Year_Billable_Objective_Count, with its formula = BillableLastYear*(100+ BillableObjective)/100.

The first factor,  BillableLastYear is a number, aggregated (sum) from child units.

The second factor,  BillableObjective is a percent number (for example 28 means 28%), it's not aggregate. It's an dependent value for each unit.

How can I calculate This_Year_Billable_Objective_Count for each unit?

\ able 1
SELECT [UnitKey]
      ,[UnitID]
      ,[UnitName]
      ,[Unit2Name]
      ,[Unit3Name]
      ,[Unit4Name]

[Code] .....

View 6 Replies View Related

How To Aggregate A Column That Was Itself An Aggregate

Jul 15, 2005

I have a column that has an expression with a runningvalue in it, a "Carrying Cost" for each month. I need to create another column that aggregates the monthly Cost. I can't to do a Runningvalue on the Runingvalue. I can't even do a Sum on the Runningvalue.

View 9 Replies View Related

How To Get Months

Nov 20, 2006

I need to get all the data based on the user selection of the months. So, I will provide a dropdown will all list of the months. When user pick the month, for example January, then I will show all the invoice on January. How to get the month of the year in the where clause in T-SQL?
 TIA

View 7 Replies View Related

3 Months Lag

Mar 21, 2008

I have a question i am confused very much with i am doing on MTD Average , i really need some help with code.

the logic behind the code is



3 MTD should be the past two completed months as well as the dates in the current month as well. So taking the example above – if the reporting period for Feb was 01/30/2007 to 02/27/2007, and March was 02/28/2007 to 03/27/2007, the 3 MTD should then be the average from 01/30/2007 to 04/10/2007.



This code does not actually gives me the exact value, please advise on any correction to the code , or a new way to write the code.



Avg
(
[Time].[Month].CurrentMember.Lag(3) :
[Time].[Month].CurrentMember,
[Measures].[A1_Avg]
)

View 4 Replies View Related

Months Between

Jan 23, 2008

Hi, I am trying to determine the amount of months between today and adate stored in the database. But I cannot seem to figure out get thedifference between the two dates. Thanks in advance.

View 2 Replies View Related

How To Get Date From 3 Months Ago

Oct 10, 2006

Hi all,How to get the date from the 3 months ago? So, if user enter a date (10/10/2006), how can I get to 07/10/2006.Thanks in advance...

View 3 Replies View Related

How To Get Months And Year

Nov 20, 2006

Hi All,
I provided the client with the dropdownlist which lists all the months (january - december). This input will be used to filter in the invoiceDT.
How can I write the query where clause to compare the invoiceDT (mm/dd/yyyy) into this input which is only number 1 through 12?
TIA

View 1 Replies View Related

How Do I Pass In # Of Months

Aug 3, 2007

I have a stored procedure that I want to pass in a number of months and use it in my where caluse. I need to minus the number of months that as passed in by @TaskMonths.
But it is minusing days not months.
@TaskMonths intWHERE (tblTasks.Caller = @Caller) AND (tblTasks.DueDate BETWEEN CONVERT(varchar, GETDATE() - @TaskMonths, 101) AND GETDATE()) I apprecaite any help.

View 2 Replies View Related

Retrieving By Months Only

Sep 16, 2004

I have records in a table and 1 column is in the smalldatetime format which stores the date in the format "2004-09-22",2004-09-20",2004-09-12",2004-08-04" etc etc.

Can anyone tell me how to craft an SQL statement so that i can retrieve records for a certain month.For example,if i want to retrieve records for the month of September,i would get "2004-09-22",2004-09-20",2004-09-12" in results.

View 1 Replies View Related

12 Months Back

Jan 18, 2005

SELECT K.TRANS_TYPE,K.COMPANY,K.DIVISION,K.SALES_FORCE,K.SALES_LINE,K.SALES_REGION,K.SALES_ZONE,K.FORECAST_ DATE,C.STATISTICS_PERIOD,K.PRODUCT_NUMBER,
K.CUSTOMER_CODE,K.WAREHOUSE,0,0,0,K.FORECAST_QTY
FROM KPI_SIGNOFF AS K inner join ROUNDED_CALENDAR as C on K.FORECAST_DATE
between C.START_DATE and C.END_DATE


Hi this is the code that I am using to populate a table let us call new_table.
However what I would like to do is get the max forecast date and get the statistics_period for that max forecast date.

Once I get that I would set that as my current statistics period.

Then I want to create a new column in the same new_table call stat_period-1, which would be the one period back.

Please note that the statistics period is like the months 1 – 12.
Does not go higher that 12 and does not go lower than 1.

So for stat_period-1 when statistics period is equal to 1 it would have to be set to 12.

This is what I want but do not know how to place this into the code, please help me with this.
THanks

View 10 Replies View Related

Query For Due Months

May 13, 2008

Hi All,

I need to display the rent not paid months in the year. How can I form the query?

Help me.

Kamal.

View 7 Replies View Related







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