January 8, 2014 at 10:31 pm
Dear Friends,
i ve the table like
create table invoice
(
invoic_no varchar(25),
item_no int,
invoic_qty int,
inv_date datetime
)
insert into invoice(invoic_no,item_no,invoic_qty,inv_date)
values('tn/0002/13-14','A12','25','03-04-2013')
insert into invoice(invoic_no,item_no,invoic_qty,inv_date)
values('tn/0002/13-14','A13','04','03-04-2013')
insert into invoice(invoic_no,item_no,invoic_qty,inv_date)
values('tn/0004/13-14','A13','25','03-04-2013')
insert into invoice(invoic_no,item_no,invoic_qty,inv_date)
values('tn/0004/13-14','A15','04','03-04-2013')
Here I need The O/P like:
Invoic_no Item_no Sales Free
tn/0004/13-14 A13 25 0
tn/0004/13-14 A15 0 4
tn/0002/13-14 A12 25 0
tn/0002/13-14 A13 0 4
MY expecting O/p is both item are billed in same invoice where the max qty is to display on'SALES'(I.e tn/0004/13-14 A13 25 )
Minimum Value is to display on Free?
How To make a code?
January 9, 2014 at 2:11 am
I'd have to ask why you would need it in this format, but i think the below should get what you want
DECLARE @Invoice TABLE
(
invoic_noVARCHAR(25),
item_noCHAR(3),
invoic_qtyINT,
inv_dateDATETIME
)
INSERT INTO @Invoice (invoic_no,item_no,invoic_qty,inv_date)
VALUES('tn/0002/13-14','A12',25,'03-04-2013'),
('tn/0002/13-14','A13',04,'03-04-2013'),
('tn/0004/13-14','A13',25,'03-04-2013'),
('tn/0004/13-14','A15',04,'03-04-2013'),
('tn/0005/13-14','A16',07,'03-04-2013'),
('tn/0005/13-14','A17',04,'03-04-2013')
SELECTI.invoic_no,
I.item_no,
--MAX(I.invoic_qty) OVER (PARTITION BY I.Invoic_no) AS Sales1,
--MIN(I.invoic_qty) OVER (PARTITION BY I.Invoic_no) AS Free1,
CASE
WHEN MAX(I.invoic_qty) OVER (PARTITION BY I.Invoic_no) = MAX(I.invoic_qty) OVER (PARTITION BY I.Invoic_no,I.Item_No)
THEN MAX(I.invoic_qty) OVER (PARTITION BY I.Invoic_no)
ELSE 0
END AS Sales,
CASE
WHEN MIN(I.invoic_qty) OVER (PARTITION BY I.Invoic_no) = MIN(I.invoic_qty) OVER (PARTITION BY I.Invoic_no,I.Item_No)
THEN MIN(I.invoic_qty) OVER (PARTITION BY I.Invoic_no)
ELSE 0
END AS Free
FROM@Invoice AS I
January 9, 2014 at 2:12 am
Although, question would be what would happen if you have 3 or more item No on one invoice.
January 9, 2014 at 2:17 am
How write the same code in sql server 2000
January 9, 2014 at 2:30 am
I'd like to point out this is a SQL 2008 forum, but the following should work with the same caveats as before. No 2000 environment to test on though.
DECLARE @Invoice TABLE
(
invoic_noVARCHAR(25),
item_noCHAR(3),
invoic_qtyINT,
inv_dateDATETIME
)
INSERT INTO @Invoice (invoic_no,item_no,invoic_qty,inv_date)
SELECT'tn/0002/13-14','A12',25,'03-04-2013' UNION ALL
SELECT'tn/0002/13-14','A13',04,'03-04-2013' UNION ALL
SELECT'tn/0004/13-14','A13',25,'03-04-2013' UNION ALL
SELECT'tn/0004/13-14','A15',04,'03-04-2013' UNION ALL
SELECT'tn/0005/13-14','A16',07,'03-04-2013' UNION ALL
SELECT'tn/0005/13-14','A17',04,'03-04-2013'
SELECTI.invoic_no,
I.item_no,
CASE
WHEN SQ1.InvMax = SQ2.InvItemMax THEN SQ1.InvMax
ELSE 0
END AS Sales,
CASE
WHEN SQ1.InvMin = SQ2.InvItemMin THEN SQ1.InvMin
ELSE 0
END AS Free
FROM@Invoice AS I
INNER
JOIN(
SELECTI1.Invoic_no,
MAX(I1.invoic_qty) AS InvMax,
MIN(I1.invoic_qty) AS InvMin
FROM@Invoice AS I1
GROUP
BYI1.Invoic_no
) AS SQ1
ON I.invoic_no = SQ1.Invoic_no
INNER
JOIN(
SELECTI2.Invoic_no,
I2.item_no,
MAX(I2.invoic_qty) AS InvItemMax,
MIN(I2.invoic_qty) AS InvItemMin
FROM@Invoice AS I2
GROUP
BYI2.Invoic_no,
I2.item_no
) AS SQ2
ONI.invoic_no = SQ2.Invoic_no
AND I.item_no = SQ2.Item_no;
January 9, 2014 at 3:16 am
Hi,
Thanks For Sharing Ur Knowledge.Its Working GOOd
January 9, 2014 at 5:45 pm
Using Dohsan's sample data, here is another solution that should work in SQL 2000 that is a bit more concise:
SELECT a.invoic_no, a.item_no
,Sales=CASE WHEN item1 = a.item_no THEN a.invoic_qty ELSE 0 END
,Free=CASE WHEN item2 = c.item_no THEN c.invoic_qty ELSE 0 END
FROM @Invoice a
LEFT JOIN
(
SELECT invoic_no, item1=MIN(item_no), item2=MAX(item_no)
FROM @Invoice
GROUP BY invoic_no
) b ON a.invoic_no = b.invoic_no AND item1 = a.item_no
LEFT JOIN @Invoice c ON a.invoic_no = c.invoic_no AND item2 = c.item_no;
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
January 9, 2014 at 6:05 pm
If the intention is to simplify, here's another option.
SELECT a.invoic_no, a.item_no
,Sales= CASE WHEN item1 = a.item_no THEN a.invoic_qty ELSE 0 END
,Free= CASE WHEN item2 = a.item_no THEN a.invoic_qty ELSE 0 END
FROM @Invoice a
JOIN
(
SELECT invoic_no, item1=MIN(item_no), item2=MAX(item_no)
FROM @Invoice
GROUP BY invoic_no
) b ON a.invoic_no = b.invoic_no AND a.item_no IN ( item1, item2);
January 9, 2014 at 6:21 pm
Luis Cazares (1/9/2014)
If the intention is to simplify, here's another option.
SELECT a.invoic_no, a.item_no
,Sales= CASE WHEN item1 = a.item_no THEN a.invoic_qty ELSE 0 END
,Free= CASE WHEN item2 = a.item_no THEN a.invoic_qty ELSE 0 END
FROM @Invoice a
JOIN
(
SELECT invoic_no, item1=MIN(item_no), item2=MAX(item_no)
FROM @Invoice
GROUP BY invoic_no
) b ON a.invoic_no = b.invoic_no AND a.item_no IN ( item1, item2);
+1 Very nice! Wish I'd thought of that.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply