| Author |
 Topic  |
|
|
Villanuev
Constraint Violating Yak Guru
311 Posts |
 Posted - 10/10/2012 : 23:15:20
|
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
47173 Posts |
Posted - 10/11/2012 : 00:07:55
|
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/
|
 |
|
|
Villanuev
Constraint Violating Yak Guru
311 Posts |
Posted - 10/11/2012 : 02:33:05
|
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/] |
|
|
|
Villanuev
Constraint Violating Yak Guru
311 Posts |
Posted - 10/11/2012 : 03:02:52
|
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/]
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 10/11/2012 : 15:44:19
|
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 |
|
|
|
Villanuev
Constraint Violating Yak Guru
311 Posts |
Posted - 10/14/2012 : 21:50:15
|
| THank you very much visakh.. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 10/15/2012 : 10:39:49
|
welcome
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Date formatting
