| Author |
 Topic  |
|
|
kuohao91
Starting Member
Malaysia
1 Posts |
 Posted - 04/24/2013 : 23:28:16
|
Hi guys,I am new to here.
My code:
declare @frmDate as datetime
declare @ToDate as datetime
set @frmdate = '2012/1/1'
set @todate = '2012/1/31'
select 1 as seq, 'Total Lot B/O' as rowdata,transaction_datetime, count(distinct lot_id) as data from (select distinct lot_id,qa_serial_no,transaction_datetime,agflag , qa_test_type
from V_EPC_RAWDATA where param_name = 'SAMPLE SIZE') a where
convert(datetime,convert(varchar(10),transaction_datetime,120)) >= @frmDate
and convert(datetime,convert(varchar(10),transaction_datetime,120)) <= @ToDate
group by transaction_datetime
This is the code that enable us to select fromdate and todate.
I wish to total the data for every month and put in into another table such as Jan xxx Feb xxx March xxx in table form.Thanks |
|
|
bandi
Flowing Fount of Yak Knowledge
India
2242 Posts |
Posted - 04/25/2013 : 00:19:07
|
--May be this?
select 1 as seq, 'Total Lot B/O' as rowdata, transaction_Month
,count(CASE WHEN transaction_Month = 1 THEN lot_id END ) as JanCount
,count(CASE WHEN transaction_Month = 2 THEN lot_id END ) as FebCount
,count(CASE WHEN transaction_Month = 3 THEN lot_id END ) as MarchCount
,count(CASE WHEN transaction_Month = 4 THEN lot_id END ) as AprilCount
,count(CASE WHEN transaction_Month = 5 THEN lot_id END ) as MayCount
,count(CASE WHEN transaction_Month = 6 THEN lot_id END ) as JuneCount
,count(CASE WHEN transaction_Month = 7 THEN lot_id END ) as JulyCount
,count(CASE WHEN transaction_Month = 8 THEN lot_id END ) as AugCount
,count(CASE WHEN transaction_Month = 9 THEN lot_id END ) as SepCount
,count(CASE WHEN transaction_Month = 10 THEN lot_id END ) as OctCount
,count(CASE WHEN transaction_Month = 11 THEN lot_id END ) as NovCount
,count(CASE WHEN transaction_Month = 12 THEN lot_id END ) as DecCount
from (select distinct lot_id,qa_serial_no,MONTH(transaction_datetime) transaction_Month,agflag , qa_test_type
from V_EPC_RAWDATA
where param_name = 'SAMPLE SIZE'
) a
where convert(datetime,convert(varchar(10),transaction_datetime,120)) >= @frmDate
and convert(datetime,convert(varchar(10),transaction_datetime,120)) <= @ToDate
group by transaction_Month
--
Chandu |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 04/25/2013 : 00:45:29
|
If you want months as rows and total against them below will suffice
declare @frmDate as datetime
declare @ToDate as datetime
set @frmdate = '20120101'
set @todate = '20120131'
insert into othertable
select 1 as seq, 'Total Lot B/O' as rowdata,
LEFT(DATENAME(mm,DATEADD(mm,DATEDIFF(mm,0,transaction_datetime),0)),3) + ' ' + DATENAME(yyyy,DATEADD(mm,DATEDIFF(mm,0,transaction_datetime),0)) AS MonthDate,
count(distinct lot_id) as data from (select distinct lot_id,qa_serial_no,transaction_datetime,agflag , qa_test_type
from V_EPC_RAWDATA where param_name = 'SAMPLE SIZE') a where
convert(datetime,convert(varchar(10),transaction_datetime,120)) >= @frmDate
and convert(datetime,convert(varchar(10),transaction_datetime,120)) < @ToDate+1
group by (mm,DATEDIFF(mm,0,transaction_datetime),0)
Also always pass dates in non ambiguos formats (CCYYMMDD). See the reason here
http://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
|
|
|
| |
Topic |
|
|
|
Change fromdate/todate to view total monthly data