Select statement - 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

General SQL Server Forums

New to SQL Server Programming

Select statement

Author

Topic

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-03-15 : 21:37:46

Guys,

Kindly please help to evaluate those sample table. i have 2 different sample table but the derived result is the same.
please choose which of this table is easy to pullout data

1. I would like to get the 2 records of itemid in separate column in one row for ThreeMonths and SixMths
2. evaluate and pull data if the record of either each column have > 60 avg leadtime

I tried 2 diff script but i could not get the correct combination of the 2 records per itemid.

[code]1.
Select
t1.Itemid, t1.ThreeMonths,
(select SixMonths from #table2 where t1.itemid = itemid) as Six
from #table1 t1
order by t1.itemid

2.
Select itemid,
Case when flag=3 then AVGLeadtime else 0 end as 'ThreeMth',
Case when flag=6 then AVGLeadtime else 0 end as 'SixMth'
From #Table
Group by Itemid

Create table #table1
(Itemid nvarchar(35), ThreeMonths int)
Insert into #table1 (Itemid, ThreeMonths) values ('HTC1012',24)
Insert into #table1 (Itemid, ThreeMonths) values ('HTC1017',49)
Insert into #table1 (Itemid, ThreeMonths) values ('HTC1019',65)
Insert into #table1 (Itemid, ThreeMonths) values ('HTC1020',65)

Create table #table2
(Itemid nvarchar(35), SixMths int)
Insert into #table1 (Itemid, SixMths) values ('HTC1012',24)
Insert into #table1 (Itemid, SixMths) values ('HTC1017',61)
Insert into #table1 (Itemid, SixMths) values ('HTC1019',75)
Insert into #table1 (Itemid, SixMths) values ('HTC1016',30)

Here is the different format of my SAMPLE table. derive result is the same.

Create table #Table
(Itemid nvarchar(35), Flag int, AVGLeadtime int)
Insert into #Table (Itemid,Flag,AVGLeadtime) values('HTC1012',3,24)
Insert into #Table (Itemid,Flag,AVGLeadtime) values('HTC1012',6,24)
Insert into #Table (Itemid,Flag,AVGLeadtime) values('HTC1016',3,30)
Insert into #Table (Itemid,Flag,AVGLeadtime) values('HTC1017',3,49)
Insert into #Table (Itemid,Flag,AVGLeadtime) values('HTC1017',6,61)
Insert into #Table (Itemid,Flag,AVGLeadtime) values('HTC1019',3,65)
Insert into #Table (Itemid,Flag,AVGLeadtime) values('HTC1019',6,75)
Insert into #Table (Itemid,Flag,AVGLeadtime) values('HTC1019',3,65)

--SAMPLE DERIVED RESULT
1. Combination of records per itemid
ItemiD--Threemonths--Sixmths
----------------------------
HTC1012--24-----------24
HTC1017--49-----------61
HTC1019--65-----------75
HTC1016--0-----------30
htc1020--65----------0

2. Based on the result above i have to validate and display only which ever of the 2 column have >=60 leadtimeAVg
ItemiD--Threemonths--Sixmths
-----------------------------
HTC1017--49-----------61
HTC1019--65-----------75
HTC1020--65-----------0[\code]

Help is very much appreciated..
Thank you in advance..
joV

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2012-03-15 : 22:35:11

If I understand your requirement correctly, from the sample derived result, using group by and having clause, you will the final result, somehow like this :

select itemid, sum(ThreeMth) t, sum(SixMth) s
from
(
Select itemid,
Case when flag=3 then AVGLeadtime else 0 end as 'ThreeMth',
Case when flag=6 then AVGLeadtime else 0 end as 'SixMth'
From #Table
) temp group by itemid having sum(ThreeMth) + sum(SixMth) >=60

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 22:36:13

if its first way it should be


SELECT Itemid,AVG(SixMonths) AS SixMonths, AVG(ThreeMonths) AS ThreeMonths
FROM
(
SELECT Itemid,CAST(0 AS int) AS SixMonths, ThreeMonths
FROM table1
UNION ALL
SELECT ItemId,SixMonths,0
FROM table2
)t
GROUP BY ItemId

if second way use

SELECT ItemId,
AVG(CASE WHEN Flag=3 THEN AVGLeadtime END) AS Sixmnths,
AVG(CASE WHEN Flag=6 THEN AVGLeadtime END) AS ThreeMonths
FROM table
GROUP BY ItemId

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-03-15 : 23:14:10

Hi Visakh16

Thank you very much.. its working..you save my day...
I modify it and use the CTEs. i get it from your previous sample... thanks..

I already got the the result ,combination of column per itemid
another concern is how could i validate and display only those record which ever the 2 column has >= 60 leadtimeAVg

expected Result
ItemiD--Threemonths--Sixmths
-----------------------------
HTC1017--49-----------61
HTC1019--65-----------75
HTC1020--65-----------0[\code]

;WITH LeadTable
AS
(
Select Itemid, AVG(AVGLeadtime) as 'ThreeMonths',
3 as lvl
from #Leadtime where Flag = 3 group by Itemid
UNION ALL

Select Itemid, AVG(AVGLeadtime) as 'SixMonths',
6 as lvl
from #LeadTime where Flag=6 Group by Itemid
)
SELECT
Itemid,
AVG(CASE WHEN lvl=3 THEN ThreeMonths END) AS ThreeMonths,
AVG(CASE WHEN lvl=6 THEN SixMonths END) AS Sixmnths
From LeadTable
Group by itemid
order by itemid

Thanks again...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 23:16:35

[code]
;WITH LeadTable
AS
(
Select Itemid, AVG(AVGLeadtime) as 'ThreeMonths',
3 as lvl
from #Leadtime where Flag = 3 group by Itemid
UNION ALL

Select Itemid, AVG(AVGLeadtime) as 'SixMonths',
6 as lvl
from #LeadTime where Flag=6 Group by Itemid
)
SELECT
Itemid,
AVG(CASE WHEN lvl=3 THEN ThreeMonths END) AS ThreeMonths,
AVG(CASE WHEN lvl=6 THEN SixMonths END) AS Sixmnths
From LeadTable
Group by itemid
having AVG(CASE WHEN lvl=3 THEN ThreeMonths END)>= 60
OR AVG(CASE WHEN lvl=6 THEN ThreeMonths END)>=60
order by itemid
[/code]

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-03-15 : 23:27:53

Thank you very very much visakh16...

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-15 : 23:36:26

welcome

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-03-18 : 03:08:52

Hi Visak16,

I have another requirements based on my previous sample but I have added additional column the createddate and openqty.

My objective is to get the 3 month and 6 months proportional leadtime by itemID and createddate.
For the 3 and 6 month proportional lead time, multiply the AVGleadtime by OpenQty and summarize the total leadtime then summarize also the openqty to get the total open qty and then divide total leadtime by total openqty to get the proportional lead time.
Btw, the flag means i identified if those records in 3 months or 6 months.

I could not figure out to formulate and include in query.

[code]
Sample Data:

Create table #Table
(Itemid nvarchar(35), Flag int, Datecreated datetime, openqty int, AVGLeadtime int)
Insert into #Table (Itemid,Flag,Datecreated,AVGLeadtime,Openqty) values('HTC1012',6,’9/2/2011’,110,2)
Insert into #Table (Itemid,Flag,Datecreated,AVGLeadtime,Openqty) values('HTC1012',6,’9/2/2011’,30,148)
insert into #Table (Itemid,Flag,Datecreated,AVGLeadtime,Openqty) values('HTC1012',3,’2/9/2012’,15,200)

Itemid--OpenQty--AVGLeadtime
----------------------------
HTC1012---2------------110------110x2 =220
HTC1012---148----------30-------30x148=4440
HTC1012---200----------15-------15x200=3000

formula to Proportional leadtime for 3 & 6 months.
but how to make it into a script.

3 Mo proporional LT
----------------------------------------------
3000/200=15--this is the leadtime for 3 months

6 Mo proporional LT
----------------------------------------------
7660/350=22---this is the leadtime for 6 months

Derive result should like this:
Itemid----3 Mo Proportional LT-------------6 Mo Proportional LT
------------------------------------------------------------------------
HTC1012-----------15--------------------------------22

QUERY:
SELECT
Itemid,
AVG(CASE WHEN flag=3 THEN ThreeMonths END) AS ThreeMonths,
AVG(CASE WHEN flag=6 THEN SixMonths END) AS Sixmnths
From LeadTable
Group by itemid
having AVG(CASE WHEN flag=3 THEN ThreeMonths END)>= 60
OR AVG(CASE WHEN flag=6 THEN ThreeMonths END)>=60
[\code]

THANK YOU in ADVANCE..

JOV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-18 : 17:20:40

can you explain how records with flag=3 also got added up to 6month figures?

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-03-18 : 20:59:20

It's working now Visakh16. Thanks for your reply..

I have 2 query inside the CTE LeadTable.
Each query have this commands and i used the union all.

--for 3 months
,3 as lvl
,Total=DATEDIFF(Day, p.createddatetime, v.DELIVERYDATE) * v.ORDERED

--for 6 months
,3 as lvl
,Total=DATEDIFF(Day, p.createddatetime, v.DELIVERYDATE) * v.ORDERED

--Here is my query after the CTE
Select
v.ITEMID,
i.ITEMNAME,
ROUND(SUM(CASE WHEN v.lvl=3 THEN v.Total END) / SUM(CASE WHEN v.lvl=3 THEN v.OrderQty END),0) AS ThreeProportionalLT,
ROUND(SUM(CASE WHEN v.lvl=6 THEN v.Total END) / SUM(CASE WHEN v.lvl=6 THEN v.OrderQty END),0) AS SixProportionalLT
From LeadTable v
left Outer Join Dynamics.dbo.INVENTTABLE i
On v.ITEMID = i.ITEMID
Group by v.ITEMID, i.ITEMNAME
order by v.ITEMID

BTW, I have 46 ITEMID that should be excluded in the resultset.
i have only itemid as my reference ( see below) how would i add this to my query. thanks..

sample ITEMID--- more than 46 itemids
'HTC1006',
'HTC1076',
'HTC1079',
'HTC1146',
'HTC1201',
'HTC1202',
'HTC1203',

JOV

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-03-19 : 00:19:18

ok...Glad that you sorted it out!

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

Villanuev
Constraint Violating Yak Guru

478 Posts

Posted - 2012-03-19 : 02:31:29

Btw, I have 46 records that should not be excluded in the result.
the data i have is only itemid. I would like to validate and include in the script that all this itemid should not be included in the result set

---sample 46 records of itemid..
'HTC1006',
'HTC1076',
'HTC1079',
'HTC1146',
'HTC1201',
'HTC1202',
'HTC1203',

Select
v.ITEMID,
i.ITEMNAME,
ROUND(SUM(CASE WHEN v.lvl=3 THEN v.Total END) / SUM(CASE WHEN v.lvl=3 THEN v.OrderQty END),0) AS ThreeProportionalLT,
ROUND(SUM(CASE WHEN v.lvl=6 THEN v.Total END) / SUM(CASE WHEN v.lvl=6 THEN v.OrderQty END),0) AS SixProportionalLT
From LeadTable v
left Outer Join Dynamics.dbo.INVENTTABLE i
On v.ITEMID = i.ITEMID
Group by v.ITEMID, i.ITEMNAME
order by v.ITEMID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources