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 |
|
wembleybear
Yak Posting Veteran
93 Posts |
 Posted - 2015-04-07 : 12:52:11
|
I'm using a subquery to return a delivery charge line as a column in the result set. I want to see this delivery charge only on the first line of the results for each contract. How can I do this? Code and results are below.declare @start smalldatetimedeclare @end smalldatetimeset @start = '2015-03-22 00:00' -- this should be a Sundayset @end = '2015-03-28 23:59' -- this should be the following Saturdayselect di.dticket [Contract], di.ddate [Delivered], di.item [Fleet_No], di.descr [Description], dd.min_chg [Delivery_Chg], dd.last_invc_date [Delivery_Invoiced],case when dd.last_invc_date > @start then 1 when dd.last_invc_date < @end then 1 else 0 end [Inv_in_Period]from deltickitem dileft join (select dticket, item, min_chg, last_invc_date from deltickitem where item = 'DEL') dd on dd.dticket = di.dticketwhere di.ddate between @start and @end and di.item not in ('COL','DEL')order by di.ddate, di.dticketResults:Contract Delivered Fleet_No Description Delivery_Chg Delivery_Invoiced Inv_in_Period-------------------- ----------------------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------- ----------------------- -------------HU004377 2015-03-23 08:18:00 01SK0120 TIPSKIP RED 125.00 2015-03-31 00:00:00 1HU004377 2015-03-23 08:18:00 01SK0121 TIPSKIP RED 125.00 2015-03-31 00:00:00 1HU004377 2015-03-23 08:18:00 01SK0122 TIPSKIP RED 125.00 2015-03-31 00:00:00 1HU004377 2015-03-23 08:18:00 01SK0123 TIPSKIP RED 125.00 2015-03-31 00:00:00 1HU004377 2015-03-23 08:18:00 01SK0124 TIPSKIP GREEN 125.00 2015-03-31 00:00:00 1HU004377 2015-03-23 08:18:00 01SK0125 TIPSKIP GREEN 125.00 2015-03-31 00:00:00 1HU004377 2015-03-23 08:18:00 01SK0126 TIPSKIP GREEN 125.00 2015-03-31 00:00:00 1HU004377 2015-03-23 08:18:00 01SK0127 TIPSKIP GREEN 125.00 2015-03-31 00:00:00 1 In this example, I only want to see the delivery charge of 125.00 for the first line of contract HU004377. For simplicity I have only shown the lines for 1 contract here, but there would normally be many different contracts with varying numbers of lines, and I only want to see the delivery charge once for each contract.Many thanksMartyn |
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-04-07 : 13:24:11
|
something like this might do it:SELECT Contract , Delivered , Fleet_No , Description , CASE WHEN rn = 1 then Delivery_Chg end as Delivery_Chg , last_invc_date , Inv_in_PeriodFROM( SELECT di.dticket [Contract] , di.ddate [Delivered] , di.item [Fleet_No] , di.descr [Description] , dd.min_chg [Delivery_Chg] , dd.last_invc_date [Delivery_Invoiced] , CASE WHEN dd.last_invc_date > @start THEN 1 WHEN dd.last_invc_date < @end THEN 1 ELSE 0 END [Inv_in_Period] , rn = ROW_NUMBER() OVER (PARTITION BY di.dticket ORDER BY di.ddate) FROM deltickitem di LEFT JOIN ( SELECT dticket , item , min_chg , last_invc_date FROM deltickitem WHERE item = 'DEL' ) dd ON dd.dticket = di.dticket WHERE di.ddate BETWEEN @start AND @end AND di.item NOT IN ('COL', 'DEL') ) q ORDER BY di.ddate , di.dticket |
 |
|
|
wembleybear
Yak Posting Veteran
93 Posts |
Posted - 2015-04-08 : 06:21:42
|
That's excellent, with a little tweak is returning what I want. One question though - how to change the null values to 0 when they occur in the Delivery_Chg column? I've tried every combination of isnull I can think of but to no avail.declare @start smalldatetimedeclare @end smalldatetimeset @start = '2015-03-22 00:00' -- this should be a Sundayset @end = '2015-03-28 23:59' -- this should be the following SaturdaySELECT Contract , Delivered , Fleet_No , Description , CASE WHEN rn = 1 then Delivery_Chg else 0 end Delivery_Chg , Inv_in_PeriodFROM( SELECT di.dticket [Contract] , di.ddate [Delivered] , di.item [Fleet_No] , di.descr [Description] , dd.min_chg [Delivery_Chg] , dd.last_invc_date [Delivery_Invoiced] , CASE WHEN dd.last_invc_date > @start THEN 1 WHEN dd.last_invc_date < @end THEN 1 ELSE 0 END [Inv_in_Period] , rn = ROW_NUMBER() OVER (PARTITION BY di.dticket ORDER BY di.ddate) FROM deltickitem di LEFT JOIN ( SELECT dticket , item , min_chg , last_invc_date FROM deltickitem WHERE item = 'DEL' ) dd ON dd.dticket = di.dticket WHERE di.ddate BETWEEN @start AND @end AND di.item NOT IN ('COL', 'DEL', 'REDDI') ) q ORDER BY Delivered , Contract Results with a null value showing:Contract Delivered Fleet_No Description Delivery_Chg Inv_in_Period-------------------- ----------------------- -------------------------------------------------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- --------------------- -------------HU004377 2015-03-23 08:18:00 01SK0124 TIPSKIP GREEN 125.00 1HU004377 2015-03-23 08:18:00 01SK0125 TIPSKIP GREEN 0.00 1HU004377 2015-03-23 08:18:00 01SK0126 TIPSKIP GREEN 0.00 1HU004377 2015-03-23 08:18:00 01SK0127 TIPSKIP GREEN 0.00 1HU004377 2015-03-23 08:18:00 01SK0120 TIPSKIP RED 0.00 1HU004377 2015-03-23 08:18:00 01SK0121 TIPSKIP RED 0.00 1HU004377 2015-03-23 08:18:00 01SK0122 TIPSKIP RED 0.00 1HU004377 2015-03-23 08:18:00 01SK0123 TIPSKIP RED 0.00 101-028576 2015-03-23 08:38:00 01BW0030 FUEL 2000 LITRES STATIC CUBE WESTERN BOWSER WITH ELECTRIC FUEL PUMP NULL 001-028624 2015-03-23 08:38:00 4265 HITACHI ZX29U-U MINI EXCAVATOR NO LIFTING EYE 35.00 101-028624 2015-03-23 08:38:00 24" 2.5Z - 24" 2.5Z BUCKET 0.00 101-028624 2015-03-23 08:38:00 18" 2.5Z - 18" 2.5Z BUCKET 0.00 101-028624 2015-03-23 08:38:00 12" 2.5Z - 12" 2.5Z BUCKET 0.00 1 Many thanksMartyn |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-04-08 : 08:42:15
|
| isnull(Delivery_Chg,0) |
|
|
|
wembleybear
Yak Posting Veteran
93 Posts |
Posted - 2015-04-08 : 09:27:08
|
| Yes, I tried that as a seperate column and it shows the nulls as 0, but the delivery_chg is then shown on every line of each contract which is what I was trying to avoid in the first place. Can it be used as part of the case statement for delivery_chg, and if so how?ThanksMartyn |
|
|
|
gbritton
Master Smack Fu Yak Hacker
2780 Posts |
Posted - 2015-04-08 : 09:56:23
|
| CASE WHEN rn = 1 then isnull(Delivery_Chg, 0) end as Delivery_ChgIs that what you mean? This will return 0 if Delivery_Chg is null, but only for the first occurrence. For the rest of the lines it will be null (since there's no ELSE clause to the CASE statement) |
|
|
|
wembleybear
Yak Posting Veteran
93 Posts |
Posted - 2015-04-08 : 10:31:49
|
| Yes, excellent. WIth the else clause that does the trick.Many thanks for your help once again.Martyn |
|
|
|
|
|
|
|
|
Return subquery result for only first row in resul