Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tantcu
Yak Posting Veteran
58 Posts |
 Posted - 2012-07-27 : 14:31:28
|
| Hi,I'm working on the project that require to compute YTD sales and LYTD sales. I'm struggling with finding the LYTD sales. This is what I have done so far for LYTD date.Today date of last year: CAST(LEFT(CONVERT(CHAR(8), DATEADD(MM, -12, getdate()),112), 6) AS INT);Can you guys help me to find the code that show the first date of 2011 year. FYI my database does not work with DATEDIFF so if you can find the code with only DATEADD it would be perfect for me. Because I want it updated automatically everyday so I need the date match with the first day of last year. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-27 : 15:23:04
|
this will give you first date for 2011..dateadd(yy,@year-1900,0).. dont understand why datediff is not working...whats error you're getting?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
|
|
tantcu
Yak Posting Veteran
58 Posts |
Posted - 2012-07-27 : 15:41:11
|
| It is not working. This is how I tried so far.DECLARE @Current_Date_2011 INT;DECLARE @Jan_2011 INT;SET @Jan_2011 = CAST(CONVERT(CHAR(8), DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GETDATE())), 0),112) AS INT)SET @Current_Date_2011 = CAST(LEFT(CONVERT(CHAR(8), DATEADD(MM, -12, getdate()),112), 6) AS INT);SELECTSUM(CASE WHEN [p21_sales_history_report_view].year_and_period Between @Jan_2011 and @Current_Date_2011 THEN [detail_price] ELSE 0 END) as LYTD_cogsThe result is giving me 0 value on every item. May be the table year_and_period does not like the date format |
|
|
|
tantcu
Yak Posting Veteran
58 Posts |
Posted - 2012-07-27 : 15:41:24
|
| It is not working. This is how I tried so far.DECLARE @Current_Date_2011 INT;DECLARE @Jan_2011 INT;SET @Jan_2011 = CAST(CONVERT(CHAR(8), DATEADD(YEAR, DATEDIFF(YEAR, 0, DATEADD(YEAR, -1, GETDATE())), 0),112) AS INT)SET @Current_Date_2011 = CAST(LEFT(CONVERT(CHAR(8), DATEADD(MM, -12, getdate()),112), 6) AS INT);SELECTSUM(CASE WHEN [p21_sales_history_report_view].year_and_period Between @Jan_2011 and @Current_Date_2011 THEN [detail_price] ELSE 0 END) as LYTD_cogsThe result is giving me 0 value on every item. May be the table year_and_period does not like the date format |
|
|
|
tantcu
Yak Posting Veteran
58 Posts |
Posted - 2012-07-27 : 15:43:12
|
| FYI the table year_and_period I believe is the result from CONCAT year_for_period and period table |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-27 : 16:05:32
|
quote:
Originally posted by tantcu
FYI the table year_and_period I believe is the result from CONCAT year_for_period and period table
please see the example in the linki've used integer values. if your format is different post how values are coming for you------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
|
|
Help with formatting the date in SQL