Added line is this color
Deleted line is this color
- -<p>DECLARE @Date datetime<br> -SET @Date = '2001/08/31'<br> -SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 1),@Date) AS 'First day of the -week'<br> -SELECT DATEADD(dd,-(DATEPART(dw, @Date) - 7),@Date) AS 'Last day of the -week'<br> -SELECT DAY(DATEADD(d, -DAY(DATEADD(m,1,@Date)),DATEADD(m,1,@Date))) AS 'Last -day of the month'</p> -<p> -==============================================================================================================</p> -<p>--To get the first day of month and day name by any date<br> -Declare @pInputDate Datetime<br> -set @pInputDate=getdate()- 40<br> -set @pInputDate= CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +<br> - CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/01' AS DATETIME)<br> ---Will give First date of the month</p> -<p>--Will give day of the date<br> -select @pInputDate AS First_Day_of_mth, datename(dw,@pInputDate) AS -Day_Name<br> -==============================================================================================================<br> -Hi,</p> -<p> here is the Table:</p> -<p>Id Saledate Sales<br> -1 2 Jan 2006 100<br> -1 13 Jan 2006 200<br> -1 14 Feb 2006 300<br> -2 3 Feb 2006 150<br> -2 4 Feb 2006 200<br> -3 5 Jan 2006 300<br> -3 13 Feb 2006 400</p> -<p> I need the following output?</p> -<p>Id Jan Feb Mar Apr<br> -1 300 300 0 0<br> -2 0 350 0 0<br> -3 300 400 0 0</p> -<p><br> ---Here is the result:</p> -<p>SELECT ID, SUM(CASE WHEN MONTH(saledate) = 1 THEN sales ELSE 0 END) AS -'Jan',<br> - SUM(CASE WHEN MONTH(saledate) = 2 THEN ISNULL(sales,0) ELSE 0 END) AS -'Feb',<br> - SUM(CASE WHEN MONTH(saledate) = 3 THEN ISNULL(sales,0) ELSE 0 END) AS -'Mar',<br> - SUM(CASE WHEN MONTH(saledate) = 4 THEN ISNULL(sales,0) ELSE 0 END) AS -'Apr'<br> -FROM sale<br> -GROUP BY ID<br> ---Here it is shown only for 4 months. You can extend this query for complete -year.</p> -<p><br> --- It gives maximum sales in each month<br> -select id as ID ,<br> -MAX(case when month(saledate) =1 then isnull(sales,0) else 0 end) as 'Jan',<br> -MAX(case when month(saledate) = 2 then isnull(sales,0) else 0 end) as -'Feb',<br> -Max(case when month(saledate) = 3 then isnull(sales,0) else 0 end) as -'Mar',<br> -Max(case when month(saledate) = 4 then isnull(sales,0) else 0 end) as -'Apr',<br> -Max(case when month(saledate) = 5 then isnull(sales,0) else 0 end) as -'May',<br> -Max(case when month(saledate) = 6 then isnull(sales,0) else 0 end) as -'June',<br> -Max(case when month(saledate) = 7 then isnull(sales,0) else 0 end) as -'July',<br> -Max(case when month(saledate) = 8 then isnull(sales,0) else 0 end) as -'Aug',<br> -Max(case when month(saledate) = 9 then isnull(sales,0) else 0 end) as -'Sept',<br> -Max(case when month(saledate) = 10 then isnull(sales,0) else 0 end) as -'Oct',<br> -Max(case when month(saledate) = 11 then isnull(sales,0) else 0 end) as -'Nov',<br> -Max(case when month(saledate) = 12 then isnull(sales,0) else 0 end) as -'Dec'<br> -from sale<br> -group by month(saledate),id</p> -<p> </p> -<p><br> -SELECT<br> - DAY('5/5/2007'),<br> - MONTH('5/1/2007'),<br> - YEAR('5/1/2007'),<br> - DATEPART(DAY, '1/5/2007'),<br> - DATEPART(MONTH, '5/1/2007'), <br> - DATEPART(YEAR, '1/1/2007')</p> -<p>Day MOnth year datepart_Day datepart_MOnth -datepart_year<br> -======-- ======-- ======-- ======--- ======----- ======----<br> -5 5 2007 5 5 2007</p> -<p><br> -SELECT<br> - GETDATE() AS local_date,<br> - GETUTCDATE() AS UTC_date</p> -<p>local_date UTC_date<br> -============----- ============-----<br> -2007-05-08 19:12:50.687 2007-05-08 13:42:50.687</p> -<p><br> -SELECT DATEADD(MONTH, 6, GETDATE())AS '6_months_from_now'</p> -<p><br> -6_months_from_now<br> -============-----<br> -2007-11-08 19:12:50.687<br> -==============================================================================================================<br> -declare @datevar datetime<br> -select @datevar = getdate()</p> -<p>/*Example for getdate() : getting current datetime*/<br> -select getdate() [Current Datetime]</p> -<p>/*Example for dateadd : getting date 7 days from current datetime*/<br> -select dateadd(dd, 7, @datevar) [Date 7 days from now]</p> -<p>/*Example for datediff : getting no of days passed since 01-01-2004*/<br> -select datediff(dd,'20040101',@datevar) [No of days since 01-01-2004]</p> -<p>/*Example for datename : getting month name*/<br> -select datename(mm, @datevar) [Month Name]</p> -<p>/*Example for datepart : getting week from date*/<br> -select datepart(wk, @datevar ) [Week No]</p> -<p>/*Example for day : getting day part of date*/<br> -select day (@datevar) [Day]</p> -<p>/*Example for month : getting month part of date*/<br> -select month(@datevar) [Month]</p> -<p>/*Example for year : getting year part of date*/<br> -select year(@datevar) [Year]</p> -<p>/* Getting the Day Name like monday tuesday... */<br> -SELECT DATENAME(dw, GETDATE())<br> --- or<br> -/* 0-monday,1-tuesday,2-wednesday ....7-monday,8-tuesday... */<br> -SELECT DATENAME(dw, 0)</p> -<p> -==============================================================================================================</p> - +Hi webmaster!