November 18, 2013 at 6:58 am
Hi all,
i have a trandate and tranvalue,customer,product now i need to poulate a table with trandate,tranvalue(sales amount),customer,product,py_tranvalue and i need to populate cy_WTD,cy_MTD,CY_YTD and py_WTD,PY_MTD,PY_YTD in sql server 2008 r2 usnig t-sql.
i have a calender table which consists of start date and enddate for week,month level.
My fiscal year starts on 1st jan yyyy and ends on 31st dec yyyy
and having 4-4-5 week per each quarter and week starts on saturday and ends with friday.
Can any one pls help on this to achive
Thanks & Regards,
Mahesh Babu K
November 18, 2013 at 8:04 am
maheshbabukadiyala 5816 (11/18/2013)
Hi all,i have a trandate and tranvalue,customer,product now i need to poulate a table with trandate,tranvalue(sales amount),customer,product,py_tranvalue and i need to populate cy_WTD,cy_MTD,CY_YTD and py_WTD,PY_MTD,PY_YTD in sql server 2008 r2 usnig t-sql.
i have a calender table which consists of start date and enddate for week,month level.
My fiscal year starts on 1st jan yyyy and ends on 31st dec yyyy
and having 4-4-5 week per each quarter and week starts on saturday and ends with friday.
Can any one pls help on this to achive
Thanks & Regards,
Mahesh Babu K
Hi and welcome to the forums. In order to help we will need a few things:
1. Sample DDL in the form of CREATE TABLE statements
2. Sample data in the form of INSERT INTO statements
3. Expected results based on the sample data
Please take a few minutes and read the first article in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 18, 2013 at 10:22 am
Hi,
I have a data like as follows
Trandate ProductID CustomerID TranValue
21-Nov-13 0233 GB10856 24
and i need cureent year wtd,mtd,qtd,ytd
and previous year wtd,mtd,qtd,ytd
and my year starts 1-jan-2013 and ends on 31-december-2013
with 445 fiscal year and day starts on saturday and ends on friday.
finally i need an output like this
Trandate , ProdID ,CustomerID ,TranValue ,PY_TranValue CY_WTD,CY_MTD,CY_QTD,CY_YTD,PY_WTD
21-NOV-13 0233 GB10856 24 xxxxxxxxx
How to caluclate privious year tran value using sql? and WTD,QTD,MTD,YTD
Thanks & regards,
Mahesh babu k
November 18, 2013 at 10:41 am
maheshbabukadiyala 5816 (11/18/2013)
Hi,I have a data like as follows
Trandate ProductID CustomerID TranValue
21-Nov-13 0233 GB10856 24
and i need cureent year wtd,mtd,qtd,ytd
and previous year wtd,mtd,qtd,ytd
and my year starts 1-jan-2013 and ends on 31-december-2013
with 445 fiscal year and day starts on saturday and ends on friday.
finally i need an output like this
Trandate , ProdID ,CustomerID ,TranValue ,PY_TranValue CY_WTD,CY_MTD,CY_QTD,CY_YTD,PY_WTD
21-NOV-13 0233 GB10856 24 xxxxxxxxx
How to caluclate privious year tran value using sql? and WTD,QTD,MTD,YTD
Thanks & regards,
Mahesh babu k
Did you actually read my original response? This is nothing like consumable data. Keep in mind that we can' see your screen, we are not familiar with your project, we don't know what you expect for output and we are volunteers.
Please take the time I recommended and read the article found by following the first link in my signature. Then come back and post tables and data along with the desired output.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
November 18, 2013 at 10:43 am
Please read the link again on posting questions to the forum. There still isn't enough data here. We need actual CREATE TABLE and INSERT ...VALUES() type statements.
With that being said I think you are looking at a data warehouse type problem which can be solved (I think) with a date dimension. In these dimensions you create a table with all of the information you would need to calculate the values you need.
Without more information this is only a guess.
November 22, 2013 at 3:51 am
Hi all,
My sql table waas as follows
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[Sales_Primary](
[TranDate] [datetime] NULL,
[SKU] [varchar](20) NULL,
[PriCustomerID] [varchar](10) NULL,
[DistributorID] [varchar](10) NULL,
[BusinessUnitID] [varchar](10) NULL,
[TranCode] [varchar](10) NULL,
[TranValue] [numeric](18, 2) NULL,
[TranNo] [varchar](20) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
---------------
insert into dbo.sales_primary
values('01-jan-2012','0567','100','zzz','AP01','SIV',10.00,'APAK'),
('02-jan-2012','0567','100','zzz','AP01','SIV',20.00,'APAK'),
('03-jan-2012','0567','100','zzz','AP01','SIV',10.00,'APAK')
--------------
expected out put
trandate sku pricustomerid tranvalue wtd previousyearwtd mtd
01-jan-2012 0567 100 10 10 --- 10
02-jan-2012 0567 10020 30 --- 30
03-jan-2012 0567 10010 40 --- 40
--------------
my calendar year starts on 1-jan-yyyy and ends on 31-dec-yyyy
and each quarter have 4-4-5 weeks and week starts on saturday and ends on friday
Thanks & Regards,
Mahesh babu k
November 22, 2013 at 8:36 am
I'm still not sure what exactly you are looking for since you gave us only 3 days of data, but want WTD, MTD, QTD, YTD etc. In you expected output looks you want a running total so here is a running total query:
-- Running Total
SELECT
a.TranDate
,a.SKU
,a.PriCustomerID
,a.TranValue
,SUM(b.TranValue) AS RunningTotal
FROM
Sales_Primary a
CROSS JOIN
Sales_Primary b
WHERE
b.TranDate <= a.TranDate
GROUP BY
a.TranDate
,a.SKU
,a.PriCustomerID
,a.TranValue
ORDER BY
a.TranDate
If you are looking for ways to run YTD, MTD, etc. here is a post that talks about how to do that with the AdventureWorks database:
http://www.sqlservercentral.com/Forums/FindPost786769.aspx
Like I said before I think what you really need is a date dimension table that is suited for your specific business needs:
http://arcanecode.com/2009/11/18/populating-a-kimball-date-dimension/
November 25, 2013 at 5:21 pm
Keith Tate (11/22/2013)
If you are looking for ways to run YTD, MTD, etc. here is a post that talks about how to do that with the AdventureWorks database:
Before you use a JOIN to calculate running totals, I suggest you have a look here:
The specifics of the problem are slightly different but the same techniques can be used and the performance characteristics can be expected to be more or less the same.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
November 25, 2013 at 7:15 pm
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply