SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 Date formatting
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Villanuev
Constraint Violating Yak Guru

441 Posts

Posted - 10/10/2012 :  23:15:20  Show Profile  Reply with Quote
Hi Guys,

I have date range parameter that requires 3 months coverage. dates depends on what records the user would like to pull out.
Just created 3 query separated by union all and use the date parameter to pull out records
and combine into one result set. I could not format the date that i wanted to be. it start only 2012/07/01 and the ending is 2012/07/01.
Need your help guys. your reply is very much appreciated. Thanks.



sample
Declare @strtable as table(tranmonth datetime,
		Model nvarchar(35),
		Partno nvarchar(35),
		ProduceQty int, 
		CoresQty int,
		NewPartsQty int,
		RequiredQty int,
		CUsage numeric(28,12),
		OUsage numeric(28,12),
		RUsage numeric(28,12))  
insert into @strtable values('2012-07-01','INCREDIBLE','JOV1648',3585,6535,635,7170,0.91,0.09,0.0)
insert into @strtable values('2012-08-01','INCREDIBLE','JOV1648',3083,5248,918,6166,0.85,0.15,0.0)
insert into @strtable values('2012-09-01','INCREDIBLE','JOV1648',2354,1407,993,2400,0.59,0.41,0.0)

Select * from @strtable

;With Cte as
( Select a.*, lflag=1 from @strtable a
  Where tranmonth between('2012-07-01 12:00AM') and ('2012-07-31 12:00PM')
  
  --Select DATEADD(mm, DATEDIFF(mm, 0,@StartDate), 0),
 --DATEADD(mm, DATEDIFF(mm, 0, @StartDate) + 0, 0) --2012-07-01
  
  Union all
  
  Select b.*, lflag=2 from @strtable b
  Where tranmonth between('2012-08-01 12:00AM') and ('2012-08-31 12:00PM')
  
  --Select DATEADD(mm, DATEDIFF(mm, 0,DateADd(mm,+1,@StartDate)), 0),
  --DATEADD(mm, DATEDIFF(mm, 0, DateADd(mm,+1,@StartDate)) + 0, 0) --2012-08-01 
  
  Union all
  Select c.*, lflag=1 from @strtable c
  Where tranmonth between('2012-09-01 12:00AM') and ('2012-09-30 12:00PM')
  
  --Select DATEADD(mm, DATEDIFF(mm, 0,DateADd(mm,+2,@StartDate)), 0),
  --DATEADD(mm, DATEDIFF(mm, 0, DateADd(mm,+2,@StartDate)) + 0, 0)  --2012-09-30
)
Select * from cte  

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/11/2012 :  00:07:55  Show Profile  Reply with Quote
are you asking on formatting the date for displaying on reports? whats the format you're looking at?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

Villanuev
Constraint Violating Yak Guru

441 Posts

Posted - 10/11/2012 :  02:33:05  Show Profile  Reply with Quote
Hi Visakh,

Just want to clear. in my SSRS report i have date and time parameter.
user will choose 3 months data coverage based on date range i choose.


[code]
Declare @FromDateTime datetime, @ToDateTime Datetime,
@FromTime nvarchar(8), @toTime nvarchar(8)

Set @FromDateTime='2012-07-01'
Set @ToDateTime='2012-09-30'
Set @FromTime='12:00 AM'
Set @ToTime='12:00 AM'


----

On my Query, I create CTE with 3 select statement that use date range on each query based on
the parameter.


; WITH CTE AS
(
--First Query
Select * from @strtable
Where tranmonth between ??? (should be ('2012-07-01 12:00 AM') and ( '2012-07-31 12:00 AM')

Union all

Select * from @strtable
Where tranmonth between ??? (should be ('2012-08-01 12:00 AM') and ( '2012-08-31 12:00 AM')

Union all

Select * from @strtable
Where tranmonth between ??? (should be ('2012-09-01 12:00 AM') and ( '2012-09-30 12:00 AM')
)
Select * from CTE
[code/]
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

441 Posts

Posted - 10/11/2012 :  03:02:52  Show Profile  Reply with Quote
Hi Visakh,

Here is my Sample. the only difference is that i have only one date parameter.
also how could i incorporate the 12:00 AM.
these capture the 3 months coverage for 3 squery.


[code]

Declare @strtable as table(tranmonth datetime,
Model nvarchar(35),
Partno nvarchar(35),
ProduceQty int,
CoresQty int,
NewPartsQty int,
RequiredQty int,
CUsage numeric(28,12),
OUsage numeric(28,12),
RUsage numeric(28,12))
insert into @strtable values('2012-07-01','INCREDIBLE','JOV1648',3585,6535,635,7170,0.91,0.09,0.0)
insert into @strtable values('2012-08-01','INCREDIBLE','JOV1648',3083,5248,918,6166,0.85,0.15,0.0)
insert into @strtable values('2012-09-01','INCREDIBLE','JOV1648',2354,1407,993,2400,0.59,0.41,0.0)


Declare @Startdate datetime
Set @Startdate='2012/07/09'


; WITH CTE AS
(
--First Query (First Month)
Select * from @strtable
Where tranmonth between DATEADD(mm, DATEDIFF(mm, 0,@StartDate), 0)
and CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,1,@Startdate))),DATEADD(mm,1,@Startdate)),101)

Union all

--Second Query (Second Month)
Select * from @strtable
Where tranmonth between DATEADD(mm, DATEDIFF(mm, 0, DateADd(mm,+1,@StartDate)) + 0, 0)
and CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,2,@Startdate))),DATEADD(mm,2,@Startdate)),101)

Union all

--Thirst Query ( Third Month)
Select * from @strtable
Where tranmonth between DATEADD(mm, DATEDIFF(mm, 0, DateADd(mm,+2,@StartDate)) + 0, 0)
and CONVERT(VARCHAR(25),DATEADD(dd,-(DAY(DATEADD(mm,3,@Startdate))),DATEADD(mm,3,@Startdate)),101)
)
Select * from CTE
[code/]


Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/11/2012 :  15:44:19  Show Profile  Reply with Quote
you dont need three selects

you can apply grouping based on month dates and then do required aggregation

like


Select MONTH(DATEADD(mm, DATEDIFF(mm, 0,DateField), 0)) AS MonthValue,
AVG(column1),SUM(column2),... similar aggregations on other columns depending on requirement
from @strtable 
Where tranmonth >=DATEADD(mm, DATEDIFF(mm, 0,@StartDate), 0) 
and tranmonth <DATEADD(mm,DATEDIFF(mm,0,@startDate)+3,0)-1
GROUP BY DATEADD(mm, DATEDIFF(mm, 0,DateField), 0)


see this to understand the logic

http://visakhm.blogspot.com/2012/07/generate-datetime-values-from-integers.html

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/


Edited by - visakh16 on 10/11/2012 15:45:11
Go to Top of Page

Villanuev
Constraint Violating Yak Guru

441 Posts

Posted - 10/14/2012 :  21:50:15  Show Profile  Reply with Quote
THank you very much visakh..
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 10/15/2012 :  10:39:49  Show Profile  Reply with Quote
welcome

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000