[Resolved] Tricky query - need help

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)

[Resolved] Tricky query - need help

Author

Topic

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-08-03 : 14:19:58

I have following query (against db2/400 data base using linked server).

SET @SQLSTRING = 
'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, 
''SELECT 
        glmcu as business_unit,
        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 quantity1 
        from    VGIPRDDTA.F0911
where   glmcu = ''''     1000131'''' and
        (glsub = ''''00200   '''' or glsub = ''''00315   '''') and 
        gllt = ''''AA'''' and
        gldct = ''''JE'''' and 
        globj = '''' '''' and
        glum = ''''CY''''
group by glmcu
order by glmcu'')'

The query works fine but I need to modify my selection for fields 'glmcu' (BusinessUnit) and 'glsub' (CostCenter). Instead of hard coding the selection I will need to retrieve the selection (and grouping) from another table.

Table layout:


BusinessUnit  Group  CostCenter
1000131          1     00200
1000131          1     00315
1000131          1     00320

1000164          1     00200
1000164          1     00400

1000131          2     04000
1000131          2     04400

So with the above table example the query should render 3 records.

Anyone that may help??? Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-03 : 15:26:52

You can do this in two steps.
Insert the intermediate result into a temporary table in SQL Server, and then use ROW_NUMBER() to get your records?

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

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-08-03 : 15:45:26

I was hoping to be able to (with help from the forum) to construct a sub query where I would read the new table (BusinessUnits, Group and CostCenter) and use "In (Select...)".

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-08-04 : 08:13:51

Peso,

Any chance you can elaborate what you suggest (with code sample?), not sure if I understand. Thank you.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 08:48:24

[code]DECLARE @IntermediateTable TABLE
(
BusinessUnit INT,
[Group] TINYINT,
CostCenter CHAR(5)
)

INSERT @IntermediateTable
SELECT 1000131, 1, '00200' UNION ALL
SELECT 1000131, 1, '00315' UNION ALL
SELECT 1000131, 1, '00320' UNION ALL
SELECT 1000164, 1, '00200' UNION ALL
SELECT 1000164, 1, '00400' UNION ALL
SELECT 1000131, 2, '04000' UNION ALL
SELECT 1000131, 2, '04400'

;WITH cteYak(BusinessUnit, [Group], CostCenter, recID)
AS (
SELECT BusinessUnit,
[Group],
CostCenter,
ROW_NUMBER() OVER (PARTITION BY BusinessUnit, [Group] ORDER BY CostCenter) AS recID
FROM @IntermediateTable
)
SELECT BusinessUnit,
[Group],
CostCenter
FROM cteYak
WHERE recID = 1[/code]

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

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-08-04 : 10:05:36

Peso, I was working with following prior to your post:

declare @SQLSTRING varchar(4000)

SET @SQLSTRING = 
'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, 
''select @@mcu, @@gid, @@sub, @@dl01
from VGIPRDDTA.F555101
WHERE EXISTS (SELECT 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 quantity1 
              FROM VGIPRDDTA.F0911 WHERE glmcu = @@mcu and glsub = @@sub)'')'

EXEC (@SQLSTRING)

Result:

   
     1000131	1	00200   	Deer Run Mine                 
     1000131	1	00210   	Deer Run Mine                 
     1000131	1	00220   	Deer Run Mine                 
     1000131	1	00221   	Deer Run Mine                 
     1000131	1	00222   	Deer Run Mine                 
     1000131	1	00223   	Deer Run Mine                 
     1000138	2	00205   	Sugar Camp East

Now I need to add up quantities for each line and show that as part of the result. I am trying to do that in "where exists...."

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-08-04 : 10:09:38

quote:

Originally posted by snufse

   
     1000131	1	00200   	Deer Run Mine                 
     1000131	1	00210   	Deer Run Mine                 
     1000131	1	00220   	Deer Run Mine                 
     1000131	1	00221   	Deer Run Mine                 
     1000131	1	00222   	Deer Run Mine                 
     1000131	1	00223   	Deer Run Mine                 
     1000138	2	00205   	Sugar Camp East

So what is the expected output based on the quoted sample data above?

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

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-08-04 : 10:23:13

Scope is:

In addition to the output shown I need to use BusinessUnit (@@mcu) and CostCenter (@@sub) to get sum of quantities for a specific month (see my "SUM(Case...") from a second table (F0911)

The output should be:

BusinessUnit  Group   CostCenter     Quantity

     1000131	1	00200   	              
     1000131	1	00210   	  
     1000131	1	00220

snufse
Constraint Violating Yak Guru

469 Posts

Posted - 2010-08-04 : 12:55:09

Got it working like this:

SET @SQLSTRING = 
'SELECT * FROM OPENQUERY( AS400SRV_MSDASQL, 
''select   @@mcu,
           @@gid,
           @@sub,
           @@dl01,
           glmcu,
           glsub,
           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 quantity1, 
           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 quantity2, 
           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 quantity3, 
           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 quantity4, 
           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 quantity5, 
           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 quantity6, 
           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 quantity7, 
           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 quantity8, 
           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 quantity9, 
           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 quantity10, 
           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 quantity11, 
           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 quantity12 
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, @@sub, @@dl01, glmcu, glsub
order by @@mcu, @@gid, @@sub'')'

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources