Ytd, Weekly, Monthly and Quarterly data

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.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Ytd, Weekly, Monthly and Quarterly data

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2013-06-09 : 21:38:33

hi Guys,

I have a requirements to pullout data and show the result for Ytd, Weekly, Monthly and Quaterly. this is a combination of 3 requirements with different table use.
number 1 query, im using sample table #invtable and #invtrans, #invtdim, need to get those itemid with prefix of (-IR, -R, -S and those with out prefix itemid will be OEM). filter those records which the location is equal to "Market" and count the qty. and the result should be in Ytd, weekly and so on.
number 2 query im using the same table but diff requirements, i need to pullout all itemid that start with "COR" and validate if the location start in "PDS" and count the qty, need also the result for Ytd, weekly, month and qtrly.
for number 3, im using diff table #prtable, #invtdim,need to get all those location is equal to "FG-L" and count the ESN as Qty
this is what i did.

i created 3 CTE's for each CTE created 4 diff scripts (ytd,weekly, monthly and qtrly) the result set i insert into temp table. need your input guys.

[code]--for OEM, -IR, -R, -S
with cte as ??
(select from
where date between ?? for ytd
union all
select from where date between?? for weekly
union all
select from where date between?? for monthly
union all
select from where date between?? for quaterly
)
insert into temp table1

--for COR
with cte as ??
(select from
where date between ?? for ytd
union all
select from where date between?? for weekly
union all
select from where date between?? for monthly
union all
select from where date between?? for quaterly
)
insert into temp table2

--FOR FG
with cte as ??
(select from
where date between ?? for ytd
union all
select from where date between?? for weekly
union all
select from where date between?? for monthly
union all
select from where date between?? for quaterly
)
insert into temp table3

final result:
----------------------
with cte as
(select from table1 union all
select from table2 union all
select from table union all
)select ????

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2013-06-10 : 01:50:38

[code]
DECLARE @FromDateWeekly DATETIME, @ToDateWeekly DATETIME

SELECT @FromDateWeekly = DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000101'),
@ToDateWeekly = DATEADD(DAY, DATEDIFF(DAY, '19000101', GETDATE()) / 7 * 7, '19000108'),
@FromDateMonthly = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000101'),
@ToDateMonthly = DATEADD(MONTH, DATEDIFF(MONTH, '19000101', GETDATE()), '19000201'),
@FromDateQuarterly = DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', GETDATE()), '19000101'),
@ToDateQuarterly = DATEADD(QUARTER, DATEDIFF(QUARTER, '19000101', GETDATE()), '19000401')

SELECT * FROM dbo.Table1 WHERE theDateColumn >= @FromDateWeekly AND theDateColumn < @ToDateWeekly
UNION ALL
SELECT * FROM dbo.Table1 WHERE theDateColumn >= @FromDateMonthly AND theDateColumn < @ToDateMonthly
UNION ALL
SELECT * FROM dbo.Table1 WHERE theDateColumn >= @FromDateQuarterly AND theDateColumn < @ToDateQuarterly
[/code]

N 56°04'39.26"
E 12°55'05.63"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources