Query - Summary by Day - SQL Server Forums

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 2005 Forums

Transact-SQL (2005)

Query - Summary by Day

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-08-07 : 08:33:10

I have a query that renders a summary by month for a selected year. Now I need a similar query that gives me a summary by day for a selected year/month.

I am connection to a db2/400 data base and the date format is a JDE date format (CYYDDD) and I am converting date format to be able to compare agains my selection.

Here is the code for summary by 12 month:

'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, 
''select   @@mcu as job_number,
           @@gid as group_id,
           @@dl01 as job_name,
           SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom1,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo1,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_1, 
           SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom2,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo2,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_2, 
           SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom3,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo3,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_3, 
           SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom4,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo4,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_4, 
           SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom5,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo5,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_5, 
           SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom6,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo6,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_6, 
           SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom7,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo7,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_7, 
           SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom8,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo8,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_8, 
           SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom9,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo9,121) + ''''')   THEN glu / 100 ELSE 0 END) AS quantity_month_9, 
           SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom10,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo10,121) + ''''') THEN glu / 100 ELSE 0 END) AS quantity_month_10, 
           SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom11,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo11,121) + ''''') THEN glu / 100 ELSE 0 END) AS quantity_month_11, 
           SUM(CASE WHEN ((date(digits(dec(gldgj + 1900000,7,0)))) >= ''''' + CONVERT(varchar(11),@DateFrom12,121)  + ''''' and (date(digits(dec(gldgj + 1900000,7,0)))) <= ''''' + CONVERT(varchar(11),@DateTo12,121) + ''''') THEN glu / 100 ELSE 0 END) AS quantity_month_12
from       VGIPRDDTA.F555101 as f1
inner join VGIPRDDTA.F0911 as f2 on f2.glmcu = f1.@@mcu and f2.glsub = f1.@@sub 
where   gllt  = ''''AA'''' and
        gldct = ''''JE'''' and 
        globj = '''' '''' 
group by @@mcu, @@gid, @@dl01
order by @@mcu, @@gid'')'
print @sqlstring

insert into #TempTable1 
                      (
                       job_number,
                       group_id,
                       job_name,
                       quantity_month_1,
                       quantity_month_2,
                       quantity_month_3,
                       quantity_month_4,
                       quantity_month_5,
                       quantity_month_6,
                       quantity_month_7,
                       quantity_month_8,
                       quantity_month_9,
                       quantity_month_10,
                       quantity_month_11,
                       quantity_month_12
                                         ) 
EXEC (@SQLSTRING)

Is there a way I will be able to do something similar by days for a selected year/month? Any ideas welcome, thank you.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-08-08 : 02:28:26

you mean for each day of month in a year? then you might need a calendar table for that

http://visakhm.blogspot.com/2010/02/generating-calendar-table.html

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources