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 |
|
tranquilraven
Starting Member
19 Posts |
 Posted - 2012-06-06 : 09:44:09
|
I am trying to make the date distinct (for example all 5/30/2012 grouped together as well as the productCode to be distinct (so it doesn't repeat). When I put in the distinct keyword I get an error, any help would be appreciated.JohnSELECT convert(varchar(10),o.OrderDate,101) AS 'Date', od.ProductCode AS 'Item Number', od.ProductName AS 'Item Description', '$' + CONVERT(varchar(12), od.ProductPrice , 1)AS 'Item Price', '$' + CONVERT(varchar(12), o.salestax1 , 1) AS Tax, '$' + CONVERT(varchar(12), o.TotalShippingCost, 1) AS 'Shipping and Handling', '$' + CONVERT(varchar(12),od.ProductPrice+o.salestax1+o.TotalShippingCost, 1) AS 'Net Invoice Amount' FROM Orders AS oINNER JOIN OrderDetails AS od ON o.OrderID = od.OrderID |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-06 : 09:50:36
|
| SELECT convert(varchar(10),o.OrderDate,101) AS [Date], od.ProductCode AS [Item Number], max(od.ProductName) AS [Item Description], max('$' + CONVERT(varchar(12), od.ProductPrice , 1))AS [Item Price], max('$' + CONVERT(varchar(12), o.salestax1 , 1)) AS Tax, max('$' + CONVERT(varchar(12), o.TotalShippingCost, 1)) AS [Shipping and Handling], max('$' + CONVERT(varchar(12),od.ProductPrice+o.salestax1+o.TotalShippingCost, 1)) AS [Net Invoice Amount] FROM Orders AS oINNER JOIN OrderDetails AS od ON o.OrderID = od.OrderIDgroup by convert(varchar(10),o.OrderDate,101), od.ProductCode==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
tranquilraven
Starting Member
19 Posts |
Posted - 2012-06-06 : 10:09:23
|
| Works. Thank you. I think I worded my request wrong. I am trying to group all the 06/06/2012 dates and Product Codes into one Row with the totals. I am fairly new at this so I don't know how to word my question accurately. Thank you. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2012-06-06 : 10:17:31
|
| Can you post some sample data and the result you expect.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
tranquilraven
Starting Member
19 Posts |
Posted - 2012-06-06 : 10:28:16
|
quote:
Originally posted by nigelrivett
Can you post some sample data and the result you expect.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
The specs are to break the report down by day, so they can pull this information on a daily basis and see the data. What I would like to do is have all the same days on one row with totals for that day. Then on another sql query I will try to pull data by the month with totals for the month.Thank you so much for your help and teaching.Day - Item Number - Item Description - Item Price - Tax - Shipping - Net AmountHere is sample data coming out of the query now:date item number item description item price tax shipping and handling net invoice amount6/6/2012 063-060010 1.5''x 3ft hose（1.5'') $5.99 $0.00 $18.40 $24.39 6/6/2012 078-110110 Pump Retaining Nut 078-110110 $2.99 $0.00 $8.28 $11.27 6/6/2012 078-110114 RP1000 Filter Canister ONLY! $0.00 $0.00 $0.00 $0.00 6/6/2012 078-110123 Suction Fitting Gasket - Part #: 078-110123 $1.99 $0.00 $6.11 $8.10 6/6/2012 078-110124 Suction fitting thrust washers - Part #: 078-110124 $1.99 $0.00 $6.21 $8.20 6/6/2012 078-110125 Suction fitting nut - Part #: 078-110125 $1.99 $0.00 $6.11 $8.10 6/6/2012 078-110126 Service Plug - Part #:078-110126 $1.99 $2.46 $12.18 $16.63 6/6/2012 078-110143 Pump Retaining Nut - Part #: 078-110143 $2.99 $0.00 $15.05 $18.04 6/6/2012 078-110218 SFS2000 Filter Canister $12.99 $2.46 $25.12 $38.11 6/6/2012 078-110219 SFS1000 Skimmer Canister 078-110219 $12.99 $0.84 $11.71 $25.54 6/6/2012 078-110223 Return Fitting for F600C, F700C, F1000C, F1500C, F2000C pumps. Fits all RP and SFS pumps. Part #: 078-110223 $1.99 $0.00 $6.21 $8.20 6/6/2012 078-110224 Gasket for F600C, F700C, F1000C, F1500C, F2000C pumps. (Black) Part #: 078-110224 $1.99 $0.00 $6.21 $8.20 6/6/2012 078-110225 Suction fitting thrust washers for RP800,RP1000 & RP2000 filter systems. Part #: 078-110124 $1.99 $2.46 $8.18 $16.63 6/6/2012 078-110226 1.50" 90 Degree Elbow (Requires O Ring - 090-130030) $2.99 $0.00 $6.21 $9.20 6/6/2012 078-110227 DIVERTER FITTING (For Ring and Frame Pools) $0.25 $0.00 $6.21 $6.46 6/6/2012 078-110228 SFS LOCK RING $0.25 $0.00 $6.21 $6.46 6/6/2012 078-110229 Gasket for F600C, F700C, F1000C, F1500C, F2000C pumps. Part #: 078-110229 $1.99 $2.46 $15.05 $17.04 6/6/2012 078-110230 Face Plate for all SFS Skimmer Cannisters. Part #: 078-110230 $2.99 $2.46 $12.18 $17.63 6/6/2012 078-110231 Weir for all Metal Round Frame Pools. $1.99 $2.46 $15.05 $17.04 6/6/2012 078-110250 1.25" Elbow 90° angle sfs600 $1.99 $0.00 $6.21 $8.20 6/6/2012 078-110283 Suction Fitting for RP400 & 600 Filter systems ONLY! - FITS 1.25 DIAMETER HOSES ONLY! Part #: 078-110283 $2.99 $0.00 $6.11 $9.10 6/6/2012 090-020047 Nut for F600C, F700C, F1000C, F1500C, F2000C pumps Part #: 090-020047 $1.99 $0.00 $8.18 $8.20 6/6/2012 090-130012 O-Ring (On Volute Housing) for F600C, F700C, F1000C, F1500C, F2000C Pumps Part # 090-130012 $1.99 $0.00 $15.05 $17.04 6/6/2012 090-130028 Gasket for RS1500(RS1000/F1500C) Part #090-130028 $1.99 $0.00 $18.40 $20.39 6/6/2012 090-130030 O-Ring (Hose Connection) for F700C, F1000C, F1500C, F2000C pumps Part # 090-130030 $1.99 $0.00 $6.11 $8.10 6/6/2012 090-380012 48" Vertical Leg for 15'x48'',16'x48'',17'x48'',18'x48''& 20'x48'', 24'x48" Metal Frame Round Pools. Part #:090-380012 $10.99 $0.00 $16.57 $27.56 6/6/2012 090-380060 9 X 18 X 52 Horizontal Beams - 090-380060 $0.00 $0.00 $0.00 $0.00 6/6/2012 096-050607 F2000C GFCI Pump $89.99 $6.30 $8.84 $98.83 6/6/2012 096-050608 F1500C GFCI Pump $69.99 $6.65 $8.84 $88.39 6/6/2012 096-050609 F1000C GFCI Pump $39.99 $2.70 $9.98 $57.34 6/6/2012 096-050610 F700C GFCI Pump $35.99 $0.00 $7.68 $49.38 6/6/2012 096-050611 F600C GFCI Pump $29.99 $0.00 $7.49 $42.53 6/6/2012 096-050620 SFS1000 Assembled Canister with Pump $49.99 $0.00 $19.42 $69.41 6/6/2012 097-040039 Deluxe Maintenance Kit $29.99 $2.17 $12.96 $45.12 6/6/2012 097-080030 Pin with Grommet for Metal Frame Round Pools 14' and up. (10 Ea. per Bag) Part #: 097-080030 $5.99 $0.00 $19.42 $25.41 6/6/2012 Account General Consumer Account $0.00 $0.00 $0.00 $0.00 6/6/2012 P53-0005 Repair patch for pool (4''x4'') ONE PATCH INCLUDED $2.99 $0.00 $5.96 $8.95 6/6/2012 R-P10-1700F 17' frame pool cover $19.99 $0.00 $16.78 $36.77 6/6/2012 R-P20-1030 10' X 30" Metal Frame Pool Liner (P20 - Blue Liner) $99.99 $0.00 $19.15 $119.14 6/6/2012 R-P21-1230 12' X 30" Ring Pool Liner $69.99 $0.00 $25.42 $95.41 6/6/2012 R-P21-1339 13' X 39" Ring Pool Liner $99.99 $0.00 $29.44 $129.43 6/6/2012 R-P52-0002 8''x 4.13'' A/C Type Filter Cartridge - 4-Pack $24.99 $6.65 $8.28 $42.86 6/6/2012 R-PS20-1648 16' X 48" Pro Series Liner $229.99 $0.00 $17.14 $247.13 6/5/2012 063-060010 1.5''x 3ft hose（1.5'') $5.99 $1.32 $9.56 $5.99 6/5/2012 063-060032 3' Return Hose (1.25") 063-060032 $2.99 $0.00 $5.96 $8.95 |
|
|
|
tranquilraven
Starting Member
19 Posts |
Posted - 2012-06-06 : 10:46:32
|
After further review, it seems the data is doing exactly what I wanted. It first breaks it down by date and then by part and gives the total for each individual part.Thank you so much for your help....it is greatly appreciated.quote:
Originally posted by nigelrivett
Can you post some sample data and the result you expect.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
|
|
|
|
|
|
|
|
|
Trying to make data distinct