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.
Author |
Topic |
kuohao91
Starting Member
1 Post |
Posted - 2013-04-24 : 23:28:16
|
Hi guys,I am new to here.My code:declare @frmDate as datetimedeclare @ToDate as datetimeset @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_datetimeThis 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
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2013-04-25 : 00:19:07
|
[code]--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 DecCountfrom (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)) <= @ToDategroup by transaction_Month[/code]--Chandu |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2013-04-25 : 00:45:29
|
If you want months as rows and total against them below will sufficedeclare @frmDate as datetimedeclare @ToDate as datetimeset @frmdate = '20120101'set @todate = '20120131'insert into othertableselect 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 whereconvert(datetime,convert(varchar(10),transaction_datetime,120)) >= @frmDate and convert(datetime,convert(varchar(10),transaction_datetime,120)) < @ToDate+1group by (mm,DATEDIFF(mm,0,transaction_datetime),0) Also always pass dates in non ambiguos formats (CCYYMMDD). See the reason herehttp://visakhm.blogspot.com/2011/12/why-iso-format-is-recommended-while.html------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/https://www.facebook.com/VmBlogs |
|
|
|
|
|
|
|